Posts tagged Mysql

Howtoforge.com does it well, but it took me some time, and in particular I feel one command was left out. Start by removing the bind address in /etc/mysql/my.cnf:

#skip-networking
#bind-address            = 127.0.0.1

Then add the following to /etc/mysql/my.cnf. Particularly to the mysqld section!!:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1

Exampledb is the database that you intend to replicate. Server-id should be 1 being that we are dealing with the master server. Make sure that you have the log directory /var/log/mysql. On the server login as root:

mysql -u root -p

Enter the password you configure when you setup mysql. If you don’t know the password you can reset it by skipping the grant tables:

sudo /etc/init.d/mysql stop
mysql --skip-grant-tables

Then login as root:

mysql -u root

Then change the root password:

UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';

Then restart mysql:

sudo /etc/init.d/mysql restart

Then login as root:

mysql -u root -p

The latter, resetting the root password, was a slight digression that may be helpful. Now add a new user with replication privileges:

GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%' IDENTIFIED BY 'replicate';

The previous command will create a user replicate with password replicate. Your slave server will connect to the master as user replicate.

Then flush privileges:

FLUSH PRIVILEGES;
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
UNLOCK TABLES;
quit;

Show master status displays information about the log file mysql generates. It will give you the log position which is necessary to synchronize the slave.

Now onto the salve. Login to the slave mysql server:

mysql -u root -p

Create a database of the same name that is on the master:

CREATE DATABASE exampledb;
quit;

Edit the slave’s /etc/mysql/my.cnf file, add:

server-id=2
master-host=192.168.0.100
master-user=replicate
master-password=replicate
master-connect-retry=60
replicate-do-db=exampledb

Set the master host ip address to that of the master; kinda logical, don’t ya think… Log back into mysql on the slave:

Login to mysql:

mysql -u root -p

Issue the following:

STOP SLAVE;
RESET SLAVE;
LOAD DATA FROM MASTER;


The following will set the slave accordingly:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='replicate', MASTER_PASSWORD='replicate', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

Make sure the master log file is what is printer on the slave when you issue master status. The same goes for the position of the log!!!!! If you don’t have that screen up anymore issue on the master mysql server:

SHOW MASTER STATUS;

Then on the slave issue:

START SLAVE;

Quit mysql and your good to go…. Give me an email and I’ll help you out…