How to Configure MySQL Replication on Debian

Valic —  July 31, 2011 — Leave a comment

1. Configure The Master
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

Furthermorewe have to tell MySQL for which database it should write logs,which log file it should use, and we have to specify that this MySQLserver is the master. We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf:

log-bin = /var/log/mysql/mysql-bin.log

# what database you want to replication

binlog-do-db=exampledb

server-id=1

Then we restart MySQL:

/etc/init.d/mysql restart

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

mysql -u root -p
Enter password:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘<your_password>’;
FLUSH PRIVILEGES;

Next do this:

USE exampledb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

Now leave the MySQL shell:

quit;

There are two possibilities to get the existing tables and data from exapledb from the master to the slave.
The first one is to make a database dump and  the second one is to use the LOAD DATA FROM MASTER; command on the slave.
The latter has the disadvantage the the database on the master will be locked during this operation, so if you have a large database on a high-trafficproduction system, this is not what you want.

However, the latter method is very fast, so I will describe both here:

If you want to follow the first method, then do this:

This will create an SQL dump of exampledb in the file exampledb.sql.

Transfer this file to your slave server! If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.

mysqldump -u root -p<password> –opt exampledb > exampledb.sql 

Finally we have to unlock the tables in exampledb:

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

quit;

Now the configuration on the master is finished!

2 Configure The Slave

On the slave we first have to create the database exampledb:

mysql -u root -p
Enter password:
CREATE DATABASE exampledb;

quit;

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

mysql -u root -p<password> exampledb < /path/to/exampledb.sql

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

server-id=2
master-host=192.168.0.100
master-user=slave_usermaster
password=secret

#what database you want to replicate
# replicate-do-db=exampledb
#or what wildcard table you want to replicate (for replicate many table) 
# replicate-wild-do-table=exampledb.%

Then we restart MySQL:

/etc/init.d/mysql restart

2.2 If you want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:

mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;

quit;

If you have phpMyAdmin ( you can use this tutorial )  installed on the slave you can now check if all tables/data from the master exampledb is also available on the slave exampledb.

mysql > LOAD DATA FROM MASTER;

ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation
TYPE THIS IN MASTER SERVER
mysql> grant RELOAD,SUPER,REPLICATION CLIENT on *.* to [email protected]’%’ identified by ‘your_password’;

Finally, we must do this:

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

In the next command you have to replace the values appropriately:

CHANGE MASTER TO MASTER_HOST=’192.168.4.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’<your_password>‘, MASTER_LOG_FILE=’mysql-bin.006′, MASTER_LOG_POS=183;

START SLAVE;
quit;

Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave.

Enjoy!

Valic

Posts Twitter Facebook

Editor in Chief at Debian-Tutorials, Linux enthusiast.

No Comments

Be the first to start the conversation.

Leave a Reply