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