MySQL replication

More brain dumping, this time after setting up MySQL on my test server to replicate offsite as the data on there is becoming important.

Both machines are running fully updated and upgraded Debian Etch and MySQL 5 as a Debian package with identical copies of the MySQL databases in /var/lib/mysql/.

On the master I:

I ensured that bind-address was set to the machine’s IP address as by default it is bound to localhost (therefore not allowing external connections) in /etc/mysql/my.conf

Checked that the server-id and log_bin were set in /etc/mysql/my.conf

Issued the MySQL command GRANT REPLICATION SLAVE ON *.* TO 'USERNAME'@'SLAVE_IP' IDENTIFIED BY 'PASSWORD';

Issued the MySQL command FLUSH TABLES WITH READ LOCK; and then SHOW MASTER STATUS;, noting down the values of File and Position.

On the slave I:

I checked that the server-id and log_bin were set in /etc/mysql/my.conf (server-id being different to that of the master)

Issued the mysql command:

CHANGE MASTER TO MASTER_HOST='MASTER_IP',
MASTER_USER='USERNAME',
MASTER_PASSWORD='PASSWORD',
MASTER_LOG_FILE='FILE_NOTED_EARLIER',
MASTER_LOG_POS=POSITION_NOTED_EARLIER;

To check it was all working I used a combination of the MySQL command SHOW SLAVE STATUS; ensuring that all looked OK. I simply made changes on the master and saw them appear on the slave.

If the slave is constantly showing “trying to connect” to the master when you show status, then to debug, make sure that it can telnet to 3306 on the master. If not then you have to ensure that there is network access between the machines and re-check the bind-address on the master.