Setting up Mysql Replication

Configure The Master :

1. First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):
#skip-networking
#bind-address = 127.0.0.1

2. Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database testDB, so we put the following lines into /etc/mysql/my.cnf:

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

Note: write these under [mysqld]

3. Then we restart MySQL:

/etc/init.d/mysql restart

4. Then we log into the MySQL database as root and create a user with replication privileges:

mysql -u root -p
Enter password:

Now we are on the MySQL shell.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '';
Note : (Replace with a real password!)
FLUSH PRIVILEGES;

5. Next (still on the MySQL shell) do this:

USE testDB;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
The last command will show something like this:
+---------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183 | testDB | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

6. Write down this information, we will need it later on the slave!

Then leave the MySQL shell:
quit;

7. Take a dump of the database need to replicate(testDB)

mysqldump -u root -p --opt testDB > testDB.sql
Note : (Replace with the real password for the MySQL user root! Important: There is no space between -p and !)

This will create an SQL dump of testDB in the file testDB.sql. Transfer this file to your slave server!

8. Finally we have to unlock the tables in exampledb:

mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;


Configure The Slave :

1. On the slave we first have to create the database testDB.
mysql -u root -p
Enter password:
CREATE DATABASE testDB;
quit;

2. If you have made an SQL dump of testDB on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created testDB on the slave:

mysql -u root -p testDB < /path/to/testDB.sql (Replace with the real password for the MySQL user root)

3. Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.1.23, and that the master database to watch is testDB. Therefore we add the following lines to /etc/mysql/my.cnf:

server-id=2
master-host=192.168.1.23
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=testDB

4. Then we restart MySQL:

/etc/init.d/mysql restart

5. Finally, we must do this:

mysql -u root -p
Enter password:
SLAVE STOP;

6. In the next command (still on the MySQL shell) you have to replace the values appropriately:

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

* MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
* MASTER_USER is the user we granted replication privileges on the master.
* MASTER_PASSWORD is the password of MASTER_USER on the master.
* MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
* MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Note: These information you can get it by running "SHOW MASTER STATUS" on master mysql.

7. Now all that is left to do is start the slave. Still on the MySQL shell we run

START SLAVE;
quit;

If The Slave needs to be configured as a Master for some other Slave :

For doing this just one variable needs to be set in the /etc/mysql/my.cnf file

log-slave-updates