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
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
exampledb, so we put the following lines into /etc/mysql/my.cnf:
log-bin = /var/log/mysql/mysql-bin.log
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:
Now we are on the MySQL shell.
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;
Next (still on the MySQL shell) do this:
USE exampledb;
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 | exampledb | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Write down this information, we will need it later on the slave!
Then leave the MySQL shell:
quit;
There are two possibilities to get the existing tables and data from
exampledb from the master to the slave. The first one is to make a
database dump, 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-traffic production system, this is not what
you want, and I recommend to follow the first method in this case.
However, the latter method is very fast, so I will describe both here.
If you want to follow the first method, then do this:
mysqldump –-all-database -–master-data=1 >> dump.sql
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.
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.
Configure The Slave
On the slave we first have to create the database exampledb:
mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;
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/dump.sql
If you want to go the LOAD DATA FROM MASTER; way then there is
nothing you must do right now.
Now we have to tell MySQL on the slave that it is the slave, that the
master is 192.168.0.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_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb
Alternatively, instead of putting the slave configuration in my.cnf one can run the following command:
change master to master_host='66.111.111.109',master_user='slave_user', master_password='p4ssw0rd',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=27900;
The MASTER_LOG_FILE and MASTER_LOG_POS can either be found on top of
the dump.sql or 'show master status' command on the master node.
Then we restart MySQL:
/etc/init.d/mysql restart
If you have not imported the master exampledb with the help of an
SQL dump, but 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 installed on the slave you can now check if
all tables/data from the master exampledb is also available on the
slave exampledb
Finally, we must do this:
mysql -u root -p
Enter password:
SLAVE STOP;
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='<some_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.
Now all that is left to do is start the slave. Still on the MySQL shell we run
START SLAVE;
quit;
That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!
Fixing Replication
Just to be sure not to loose any data, make a dump of the slave. So on the slave server run.
mysqldump --all-databases >>backupfile.sql
Make a dump of the master. run the following on the master database.
mysqldump --all-databases --master-data=1 >> master_data.sql
move master_data.sql to the slave server and run the following on the slave server.
mysql < master_data.sql
Then just start the slave process
start slave;
check the status
show slave status\G
If your setup is a one way replication, you are done. For circular
replication you have to now update the user information of the
replication user that the other database is trying to login with and
adjust binary log position.
So on the master run
show master status\G
using the information from master, on the slave run
stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4;
You can update the password of the replication user on the slave by running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
start slave;
On the master server make sure the password and host are set correctly.
SET PASSWORD FOR 'replication_user'@'<IP>' = PASSWORD('new3cret');
update user set host='<IP of slave>' where user='replication_user';
Promote a Slave to a Master
STOP SLAVE;
RESET MASTER;
CHANGE MASTER TO MASTER_HOST='';
Demote a Master to Slave
modify my.cnf with:
[mysqld]
server-id=1
# master server settings
master-host=10.2.14.50
master-user=replication
master-password=PYQTSXg6vW
master-connect-retry=60
replicate-ignore-db=mysql
start restart mysql and import a dump of master that was done with 'master-status' (see above) into the server.
Fix replication by skipping a duplicate entry (determine if this is safe first)
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;