Content added Content deleted
imported>Hundfred No edit summary |
No edit summary |
||
(5 intermediate revisions by one other user not shown) | |||
Line 18: | Line 18: | ||
server_id=1 |
server_id=1 |
||
</pre> |
</pre> |
||
(there are loads of options, it is also possible to encrypt the connection with ssl.) |
(there are loads of options, it is also possible to '''encrypt the connection with ssl'''.) |
||
'''slave:''' |
'''slave:''' |
||
Line 28: | Line 28: | ||
master-passwort=xxxxx #can be given here, or later, if mysqld is started |
master-passwort=xxxxx #can be given here, or later, if mysqld is started |
||
master-connect-retry=60 #in seconds |
master-connect-retry=60 #in seconds |
||
</pre> |
|||
then we start the master, and check with the "show master status" command, which bin-log |
|||
file is acutally used: |
|||
<pre> |
|||
mysql> show master status; |
|||
+--------------------+----------+--------------+------------------+ |
|||
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
|||
+--------------------+----------+--------------+------------------+ |
|||
| KURS006-bin.000006 | 106 | testdbsm | mysql | |
|||
+--------------------+----------+--------------+------------------+ |
|||
1 row in set (0.00 sec) |
|||
mysql> |
|||
</pre> |
|||
then the slave has to be started and execute following command: |
|||
<pre> |
|||
change master to |
|||
master_host='<hostname>', |
|||
master_user='<repuser>', |
|||
master_password=' |
|||
master_log_file='KURS006-bin.000006' |
|||
<pre> |
|||
test, if connection exists |
|||
<pre> |
|||
mysql> show processlist; |
|||
+----+------+----------------+------+---------+------+-------+------------------+ |
|||
| Id | User | Host | db | Command | Time | State | Info | |
|||
+----+------+----------------+------+---------+------+-------+------------------+ |
|||
| 1 | root | localhost:1224 | NULL | Sleep | 0 | | NULL | |
|||
| 3 | root | localhost:1226 | NULL | Query | 0 | NULL | show processlist | |
|||
+----+------+----------------+------+---------+------+-------+------------------+ |
|||
2 rows in set (0.00 sec) |
|||
</pre> |
|||
if it works you should see a funny messages like this: |
|||
<pre> |
|||
mysql> show processlist; |
|||
| 12 | repuser | KURS007:1394 | NULL | Binlog Dump | 9 | Has sent all binlog to slave; waiting for binlog to be updated |
|||
</pre> |
</pre> |
Latest revision as of 08:59, 24 June 2009
mysql-master-slave-replication[edit]
1)on the master and the slave a new "replication" user has to be added
mysql> create user repuser@kurs007 identified by 'xxxxx'; Query OK, 0 rows affected (0.00 sec)
2) set some rights
mysql> create user repuser@kurs007 identified by 'xxxxx'; Query OK, 0 rows affected (0.00 sec)
now we have to edit the my.ini on the master and the slave
master:
server_id=1
(there are loads of options, it is also possible to encrypt the connection with ssl.)
slave:
server-id=2 master-host=<ip_address or fqdn> master-port=3306 master-user=repuer #username master-passwort=xxxxx #can be given here, or later, if mysqld is started master-connect-retry=60 #in seconds
then we start the master, and check with the "show master status" command, which bin-log file is acutally used:
mysql> show master status; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | KURS006-bin.000006 | 106 | testdbsm | mysql | +--------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>
then the slave has to be started and execute following command:
change master to master_host='<hostname>', master_user='<repuser>', master_password=' master_log_file='KURS006-bin.000006' <pre> test, if connection exists <pre> mysql> show processlist; +----+------+----------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------+------+---------+------+-------+------------------+ | 1 | root | localhost:1224 | NULL | Sleep | 0 | | NULL | | 3 | root | localhost:1226 | NULL | Query | 0 | NULL | show processlist | +----+------+----------------+------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)
if it works you should see a funny messages like this:
mysql> show processlist; | 12 | repuser | KURS007:1394 | NULL | Binlog Dump | 9 | Has sent all binlog to slave; waiting for binlog to be updated