MySQL Replication
This post will walk you through setting up replication for a MySQL database which has existing data.
Why Replication?
- Backups. As part of a backup strategy, replication avoids shutting down the master if backups are made from the slave.
- Scale-out. Replication can allow the distribution of reads over multiple replication slaves.
- Reliability. Replication can provide a ready backup in the event of a failure.
This post will only address the use of replication in a backup strategy, but the setup steps are valid for other uses as well.
Assumptions:
- The master database has existing data.
- The master and slave have different IP addresses.
- The master database to be replicated is named
exampledb. - The database
exampledbexists on the master but not on the slave. - The database uses MyISAM tables.
- The master and slave run on Fedora. This should only affect the directory structure.
- The master and slave use port 3306. This affects firewall setup and Master configuration. To use a different port, set
--master-portin theCHANGE MASTER TOcommand.
Preliminary steps.
Configure the firewall to allow the master and slave to communicate.
You have a firewall, right? Assuming the master server is 2xx.xx.xx.01 and the slave is 2xx.xx.xx.02 corresponding to server_id=1 for the master and server_id=2 for the slave.
On the master add the following to /etc/sysconfig/iptables
# Allow incoming and outgoing traffic on port 3306 for MySQL slave server -A INPUT -p tcp -s 20x.xx.xx.02 --sport 1024:65535 -d 2xx.xx.xx.01 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT -A OUTPUT -p tcp -s 2xx.xx.xx.01 --sport 3306 -d 2xx.xx.xx.02 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
On the slave add the following to /etc/sysconfig/iptables
# Allow incoming and outgoing traffic on port 3306 for MySQL slave server -A INPUT -p tcp -s 2xx.xx.xx.01 --sport 1024:65535 -d 2xx.xx.xx.02 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT -A OUTPUT -p tcp -s 2xx.xx.xx.02 --sport 3306 -d 2xx.xx.xx.01 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
Restart the firewall
# /sbin/service iptables restart
Create a replication user, repl on the slave server.
# /usr/sbin/useradd -r repl # /usb/bin/passwd repl
Grant privileges to the replication user on the master server.
mysql> GRANT FILE, REPLICATION SLAVE ON *.*
-> TO 'repl'@'%' IDENTIFIED BY 'password';
Prepare the Master
In order to prepare the master, it will have to be locked for a short time. While it’s locked we will create a dump file, and edit the configuration in /etc/my.cnf.
You must enable binary logging so we’ll start by configuring the master server binary logging and restarting the master.
Each server in the replication group must have a unique id. The master server is typically assigned an id of 1.
The master server must allow networking, so ensure that --skip-networking and --bind-address are commented out if present.
While your at it, check that the pemissions of the file my.cnf
are 0400 or 0600.
Edit the master database’s configuration in the file /etc/my.conf. Add the lines under the [mysqld] section.
[mysqld] # enable networking and listen on all IP addresses # by commenting out the following two lines if they exist #skip-networking #bind-address = 127.0.0.1 # The following line will create the log file # /var/lib/mysql/mysql-bin.00000x log-bin=mysql-bin # set expire_logs_days no lower than the number of days the slave is behind expire_logs_days=3 server-id=1 # substitue 'hostname' with the actual name of your host relay-log='hostname'-relay-bin # duplicate the following line for every database that needs to be replicated # substitute 'exampledb' with actual name of the database to be replicated binlog-do-db='exampledb'
Restart the master
# /etc/init.d/mysqld --report-host restart
The --report-host option will make checking the replication status easier.
Check the error log at /var/log/mysqld.log after restarting mysqld.
Next we will lock the master database, record the binary log file’s position, and create a database dump that we’ll use to initialize the slave database.
Lock the database
# mysql -p -u root mysql> use exampledb; mysql> FLUSH TABLES WITH READ LOCK;
Obtain status of the binary log on the master.
mysql > SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 98 | exampledb | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Record File and Position, you’ll need it to synchronize the slave.
Leave this client running while the dump file is created.
Create a dump backup file
# mysqldump -u root -p exampledb > exampledb.sql
On master, unlock the tables
mysql > UNLOCK TABLES;
Setting up the slave
Edit the slave’s configuration in the file /etc/my.cnf
[mysqld] log-bin=mysql-bin master-host=master-hostname master-user=slave-user master-password=slave-password server-id=2 # duplicate the following line for every database that needs to be replicated # substitute 'exampledb' with actual name of the database to be replicated replicate-do-db='exampledb' report-host='slave-hostname'
Since the master.info file overides settings in my.cnf, you may prefer to set up the slave using the CHANGE MASTER TO command. As a minimum, you need to use the CHANGE MASTER TO command to set the values
of MASTER_LOG_FILE and MASTER_LOG_POS
Change the option values to the actual values for your servers
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS='recorded_log_position';
Start the slave
$ /etc/init.d/mysqld --skip-slave start
The --skip-slave option tells the server not to start the slave threads. We’re using this option because we aren’t quite ready to replicate.
Create the database
# mysql -u root -p mysql> create database exampledb; mysql> grant all on exampledb.* to 'repl'@'localhost';
Import the dump file
# mysql -u root -p exampledb < full_dump_file.sql
Start the slave threads
$ mysql> START SLAVE;
Checking the status of the master and slave
On slave
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: fluidrails.com
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 98
Relay_Log_File: hakota-relay-bin.000073
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: garden
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
ERROR:
No query specified
On master
mysql> SHOW MASTER STATUS\G; *************************** 1. row *************************** File: mysql-bin.000004 Position: 98 Binlog_Do_DB: garden Binlog_Ignore_DB: 1 row in set (0.00 sec) ERROR: No query specified
mysql> SHOW PROCESSLIST\G; +------+------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+ | 6235 | repl | www.hakota.com:43033 | NULL | Binlog Dump | 3250 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | +------+------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
mysql> SHOW SLAVE HOSTS; +-----------+------------+------+-------------------+-----------+ | Server_id | Host | Port | Rpl_recovery_rank | Master_id | +-----------+------------+------+-------------------+-----------+ | 2 | hakota.com | 3306 | 0 | 1 | +-----------+------------+------+-------------------+-----------+ 1 row in set (0.00 sec)
Logs and Backups
Now that we’ve enabled binary logging we need to implement a process to manage the logs.
One approach is to use SHOW SLAVE STATUS (on slave) and SHOW BINARY LOGS (on master) to determine which logs are in use. Suppose the slave is using Master_Log_File: master-bin.000004 Make sure your backups contain the logs about to be deleted, those prior to the target log master-bin.000004, then
mysql> PURGE MASTER LOGS TO 'master-bin.000004';
will delete all logs prior to master-bin.000004
In addition, now that we’ve implemented replication, our backup strategy needs to include the master.info and relay-log.info status files as well as the binary logs.
Before executing mysqldump on the slave, replication should be temporarily stopped.
# mysqladmin -u root -p stop-slave # mysqldump -u root -p exampledb > exampledb.sql # mysqladmin -u root -p start-slave
