MySQL Replication
MySQL Replication works only on servers where MySQL 5.x is installed
Create user on Server 1
- Username: rep2
- Password: rep2
- Host: 222.222.222.222
- Grant all privileges to this user (on database mor)
Create user on Server 2
- Username: rep1
- Password: rep1
- Host: 111.111.111.111
- Grant all privileges to this user (on database mor)
Server 1 my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1
bind-address = 0.0.0.0
binlog-do-db = mor server-id = 10 auto_increment_increment = 10 auto_increment_offset = 1 master-host = 222.222.222.222 master-user = rep1 master-password = rep1 master-port = 3306 master-connect-retry = 60 replicate-do-db = mor log-bin = /var/lib/mysql/mysql-bin.log log-bin-index = /var/lib/mysql/master-log-bin.index relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index
max_allowed_packet = 64M
[mysql.server] user=mysql basedir=/var/lib
[mysqld_safe] log-error=/var/lib/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
NOTE: if you have file /etc/mysql/debian.cnf make sure you have correct socket location in it:
socket=/var/lib/mysql/mysql.sock
Or change correct location into mysql.cnf file (e.g. socket=/var/run/mysqld/mysqld.sock)
Server 2 my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1
bind-address = 0.0.0.0
binlog-do-db = mor server-id = 20 auto_increment_increment = 20 auto_increment_offset = 2 master-host = 111.111.111.111 master-user = rep2 master-password = rep2 master-port = 3306 master-connect-retry = 60 replicate-do-db = mor log-bin = /var/lib/mysql/mysql-bin.log log-bin-index = /var/lib/mysql/master-log-bin.index relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index max_allowed_packet = 64M
[mysql.server] user=mysql basedir=/var/lib
[mysqld_safe] log-error=/var/lib/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
NOTE: if you have file /etc/mysql/debian.cnf make sure you have correct socket location in it:
socket=/var/lib/mysql/mysql.sock
Or change correct location into mysql.cnf file (e.g. socket=/var/run/mysqld/mysqld.sock
Server 2
Make sure data on Server 2 = data on Server 1. E.g. MySQL table structure and data are identical on both machines.
Server 1
/etc/init.d/mysql restart mysql -u root -p
USE mor; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Remember File and Position.
UNLOCK TABLES; quit;
Server2
/etc/init.d/mysqld restart
mysql -u root -p
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='111.111.111.111', MASTER_USER='rep2', MASTER_PASSWORD='rep2', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1183703;
NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.
START SLAVE;
USE mor; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Remember File and Position.
UNLOCK TABLES; quit;
Server 1
mysql -u root -p
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='222.222.222.222', MASTER_USER='rep1', MASTER_PASSWORD='rep1', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=98;
NOTE: Change MASTER_HOST, MASTER_LOG_FILE and MASTER_LOG_POS accordingly.
START SLAVE; quit;
Log files
In order for MySQL Replication to function properly you need to check log files in /var/lib/mysql
Open master-log-bin.index, it should contain names of the files which really exist in same folder. If there's some mismatch - edit this file: enter missing names for files or delete file names which does not represent real files in this folder.
Same with file slave-relay-log.index
Testing
Login to MySQL console on both servers:
mysql -u root -p
Then:
SHOW SLAVE STATUS\G
If everything is ok you should see such lines between others on both servers:
Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes
If Replication is off you will see:
Slave_IO_State: Slave_IO_Running: No
Troubleshooting
Check file: /var/lib/mysql/mysqld.log
'Could not find first log file name in binary log index file' from master when reading data from binary log
We go to the master and check /var/lib/mysql/master-log-bin.index file.
For example it can show:
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.000003 /var/lib/mysql/mysql-bin.000005
In /var/lib/mysql/ we also have file mysql-bin.000004, so its' name is missing in index file, lets add it, so master-log-bin.index looks like:
/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002 /var/lib/mysql/mysql-bin.000003 /var/lib/mysql/mysql-bin.000004 /var/lib/mysql/mysql-bin.000005
Restart mysql on this server and try with slave again.
This is just an example - some other file's records can be missing. Make sure file has all related file's records which are in same folder.
- Default mysql.cnf - can be usefull to go back if something goes wrong and no backup is done
