MySQL Master / Slave Replication
Source: Uptime Made Easy
Master Slave MySQL Replication Summary
Master / Slave replication in MySQL is a great way to store an exact replica of your database on another machine in another location as part of a disaster recovery plan. Before setting up Master / Slave replication there are a few things to remember.
- Writes – Writes to the master database should make it to the slave. But writes to the slave will not make it to the master. If you do write records to the slave database directly, be prepared to have to either recreate the records or back them up separately and recover them if the replication breaks. Many times the only way to get the databases to replicate again is to backup the master and recover it over the top of the slave deleting anything that was in the slave database before.
- Broken Replication – Writes made directly to the slave can cause the replication to break due to duplicate key rows, etc.. Always write to the master.
- Reads – Reads should be possible from either server. Many organizations will use replication so as to create another database to read from thereby taking the load of all of their select statements and reports off the master server.
Master Slave MySQL Replication
Steps to Setup MySQL Master / Slave Replication
Prerequisites
We will be assuming that the following prerequisites are done prior to beginning the steps listed below:
- MySQL has been installed on both the master and the slave servers
- The slave server is able to communicate directly to the mysqld port (typically 3306) on the master server, meaning that there is no firewall, routing, NAT or other problems preventing communication.
- You have an administrator MySQL user that can create users on both the master and the slave machines.
- You have permissions to edit the /etc/my.cnf files on both machines and enough privileges to restart mysql.
That should be it! Let’s begin setting it up.
Steps to Setup MySQL Master / Slave Replication
Step 1 – Create a user on the master with replication slave rights for the slave user to login and get the logged updates.
mysql> grant replication slave on *.* to ‘repslave’@'<ipaddress-of-slave-machine>’ identified by ‘password’;
Step 2 – On the master server, edit the /etc/my.cnf file, restart mysqld, and verify the status of the master server:
Edit the /etc/my.cnf file and add these lines under the [mysqld] region:
server-id=1 log-bin=slave_1log binlog-do-db=myimportantdb
restart the mysqld service:
# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
Verify the status of the master server:
mysql> show master status; +———————————————————+——————————+———————————————+——————————————————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +———————————————————+——————————+———————————————+——————————————————+ | slave_1log.000002 | 106 | myimportantdb | | +———————————————————+——————————+———————————————+——————————————————+ 1 row in set (0.00 sec)
Step 3 – Create the database on the master server. If the database already exists, then create a backup and copy it to the slave server.
mysql> create database myimportantdb; Query OK, 1 row affected (0.00 sec)
Dump the database if it existed:
mysqldump -uroot -p –routines –singletransaction myimportantdb > myimportantdb.sql
Copy the database to the slave machine:
scp myimportantdb.sql <userid>@<mysqlslave-ip-addresss>:~/
Step 4 – Create the database on the slave server and restore the database if we backed it up in step 3 above.
create the database
mysql> create database myimportantdb; Query OK, 1 row affected (0.00 sec)
restore the database if we backed it up in step 3 above:
mysql -uroot -p myimportantdb < ./myimportantdb.sql
Step 5 – On the slave server, edit the /etc/my.cnf file and restart mysqld, and verify the status of the slave server:
Edit the /etc/my.cnf file on the slave server and put the following under [mysqld] section:
server-id=2 master-host=<put master server’s FQDN or IP here> master-user=repslave master-password=<put the repslave’s mysql password here>
Restart the mysqld on the slave server:
# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
Check the slave status:
mysql> show slave status; +———————————-+——————–+————-+————-+—————+——————-+———————+————————-+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +———————————-+——————–+————-+————-+—————+——————-+———————+————————-+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+ | Waiting for master to send event | uptimemadeeasy.com | repslave | 3306 | 60 | slave_1log.000001 | 106 | mysqld-relay-bin.000004 | 252 | slave_1log.000001 | Yes | Yes | | | | | | | 0 | | 0 | 106 | 408 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | +———————————-+——————–+————-+————-+—————+——————-+———————+————————-+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+ 1 row in set (0.00 sec)
If the slave status reported an error like the following, then there is likely a problem with your firewall(s) or other connectivity issue blocking the slaves communication to the master:
error connecting to master ‘repslave@<slavehostname.com>:3306’ – retry-time: 60 retries: 86400
If there were no errors, then we should be replicating just fine and We are UP!!
Test the MySQL Master / Slave Replication
To test the replication, we will create a test table and some data in the table and see if they make it to the slave server. On the master server do:
mysql> create table myimportanttable ( importantnum int, importantchar varchar(20) ); Query OK, 0 rows affected (0.01 sec)mysql> insert myimportanttable values ( 1, ‘some text here’ ); Query OK, 1 row affected (0.00 sec)
Now, lets verify that the table and data were replicated. On the slave server do:
mysql> select * from myimportanttable; +——————————————+————————————————+ | importantnum | importantchar | +——————————————+————————————————+ | 1 | some text here | +——————————————+————————————————+ 1 row in set (0.00 sec)
You can see that our table and data was replicated without any issues. Our MySQL Master / Slave Replication is complete.