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.logbinlog-do-db=exampledbserver-id=1
Then we restart MySQL:
[root@host]# /etc/init.d/mysql restart
Then we log into the MySQL database as root and create a user with replication privileges:
[root@host]# mysql -uroot -p
Enter password:
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ”;
mysql> FLUSH PRIVILEGES;
mysql> USE exampledb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Write down this information, we will need it later on the slave!
Then leave the MySQL shell:
mysql> 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:
[root@host]# mysqldump -u root -p –opt exampledb > exampledb.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:
[root@host]# mysql -u root -p
Enter password:
mysql> UNLOCK TABLES;
mysql> quit;
Now the configuration on the master is finished.
Configure The Slave
On the slave we first have to create the database exampledb:
[root@host]# mysql -u root -p
Enter password:
mysql> CREATE DATABASE exampledb;
mysql> 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:
[root@host]# mysql -u root -p exampledb < /path/to/exampledb.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
Then we restart MySQL:
[root@host]# /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:
[root@host]# mysql -u root -p
Enter password:
mysql> LOAD DATA FROM MASTER;
mysql> 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:
[root@host]# mysql -u root -p
Enter password:
mysql> SLAVE STOP;
In the next command (still on the MySQL shell) you have to replace the values appropriately:
mysql> 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.
Now all that is left to do is start the slave. Still on the MySQL shell we run
mysql> START SLAVE; mysql> quit;
That’s it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!
Here are two examples of the my.cnf file on the master and slave servers:
On the Master:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=512000000
open-files-limit=5000
table_cache=2000
max_connections=1000
key_buffer_size=2048M
sort_buffer_size=24M
query-cache-type=1
query-cache-size=512M
sort_buffer=24M
read_rnd_buffer_size=3M
read_buffer_size=1M
tmp_table_size=64M
interactive_timeout=288000
log-bin
server-id=83
ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/stopwords.txt
myisam_max_sort_file_size=16G
myisam_max_extra_sort_file_size=16G
myisam_sort_buffer_size=24M
max_binlog_size=256M
log-slow-queries = /var/log/mysql_slow.log
long_query_time = 1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 1
[mysql.server]
user=mysql
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[myisamchk]
ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/stopwords.txt
On the Slave:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=512000000
open-files-limit=5000
table_cache=2000
sort_buffer_size=4M
key_buffer_size=2048M
query-cache-type=1
query-cache-size=512M
sort_buffer=4M
read_rnd_buffer_size=3M
tmp_table_size=64M
max_connections=500
interactive_timeout=288000
server-id=84
replicate-wild-ignore-table=%.indexTasks
replicate-wild-ignore-table=%.indexClusterTasks
replicate-wild-ignore-table=%.indexPages
replicate-wild-ignore-table=%.adRequestsRollup
replicate-wild-ignore-table=%.textAdsRollup
replicate-wild-ignore-table=%.%Log%
replicate-wild-ignore-table=%.%Archive%
replicate-wild-ignore-table=%.tmp%
replicate-wild-ignore-table=%.pageContents%
master-host=db10m.int
master-user=replicationuser
master-password=3y9nR16k
ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/stopwords.txt
set-variable = myisam_max_sort_file_size=16G
set-variable = myisam_max_extra_sort_file_size=16G
set-variable = sort_buffer_size=4M
set-variable = myisam_sort_buffer_size=4M
slave-skip-errors=1062
read-only
max_binlog_size=256M
log-slow-queries = /var/log/mysql_slow.log
long_query_time = 1
[mysql.server]
user=mysql
[safe_mysqld] err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[myisamchk] ft_min_word_len=2
ft_stopword_file=/var/lib/mysql/stopwords.txt
