Replication continuously copies changes from one server (the master) to another (the slave).
MySQL Replication is commonly used to:
Survive Failures - Replication gives you an up-to-the-moment copy of the master database, on a server that's ready to take over when the inevitable crash happens. It is typically much faster to restore service using a slave than restoring from a backup.
Scale Reads - Many modern applications have a write-once, read-many access pattern. For example, a blog post will be inserted once and read many times. You can use replication to spread the much higher SELECT load over many read-slaves. The smaller write load (INSERTs, UPDATEs, DELETES) still has to run on the master.
Distribute Data - A master can replicate to slaves anywhere on the globe. You can use slaves to improve network latency serving customers. You can also use a distant replicated slave as a Business Continuity Plan in case the entire location housing the master server is offline.
Run Intensive Reports - Some queries take a huge amount of processor, memory, or disk to execute. Dedicated read slaves can distribute the impact of heavy reports.
The master server is an existing database that we want to replicate to a new slave.
To get started, connect to master.example.com.
Add a service account named replicator on the master and grant it replication privileges. The slave will use this account to authenticate to the master.
master ~ $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.29 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'SilkInnFeatGear'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql>
replicator can log into MySQL on the master from the slave with password SilkInnFeatGearIn order for this server to be a replication master, it needs log_bin enabled, and a server_id that is not the default (0).
Set those values by editing /etc/my.cnf:
mysql> exit; Bye master ~ $ sudoedit /etc/my.cnf
and add these two lines directly under [mysqld]
/etc/my.cnf[mysqld] log_bin = mysql-bin server_id = 10 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ......some content not shown
Now restart MySQL:
master ~ $ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
Log in to MySQL and check that those changes took effect.
master ~ $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW variables LIKE 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 10 | +---------------+-------+ 1 row in set (0.01 sec) mysql> SHOW variables LIKE 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql>
/etc/my.cnf sets server_id to 10/etc/my.cnf sets log_bin to mysql-binserver_id has value 10log_bin has value ONOn the master, make a complete backup that we can use to get the slave quickly up to speed.
mysql> exit master ~ $ mysqldump -u root --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. master ~ $
/tmp/master_backup.sqlNow we'll copy the backup file to the slave using SCP.
master ~ $ scp /tmp/master_backup.sql slave.example.com:/tmp/ The authenticity of host 'slave.example.com (10.242.58.189)' can't be established. RSA key fingerprint is 0f:47:42:f4:71:51:4c:a3:70:94:db:83:03:4c:d2:48. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'slave.example.com,10.242.58.189' (RSA) to the list of known hosts. you@slave.example.com's password: (input your password) master_backup.sql 100% 501KB 501.3KB/s 00:00 master ~ $
You can also see where the backup file includes replication information. When the slave applies this backup then starts replicating, it will know to look on the master in a specific binary log, after a specific position for changes newer than this backup.
master ~ $ egrep "^CHANGE MASTER" /tmp/master_backup.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; master ~ $
/tmp/master_backup.sqlThe slave server is a brand-new server with MySQL installed but no data. We'll set it up to replicate data from the master.
Connect to slave.example.com
Now configure the slave to take part in replication, first by customizing /etc/my.cnf, similar to the master.
Set server_id to something different than the master, and not zero.
To make the slave easily promoted to a master, set a name for log_bin. Also turn on log_slave_updates and rename the relay_log.
We also want to make sure that no changes accidentally happen on the slave, since they wouldn't be returned to the master, and we'd end up with an inconsistent replication group. So we set this server to be read_only, for now.
slave ~ $ sudoedit /etc/my.cnf
Add these five lines to directly under [mysqld]
/etc/my.cnf[mysqld] server_id = 20 log_bin = mysql-bin log_slave_updates = 1 relay_log = mysql-relay-bin read_only = 1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ......some content not shown
Now restart MySQL:
slave ~ $ sudo service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
Now, in MySQL, check that the changes took effect:
slave ~ $ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW variables WHERE Variable_Name IN ("server_id", "log_bin", "relay_log", "log_slave_updates", "read_only");
+-------------------+-----------------+
| Variable_name | Value |
+-------------------+-----------------+
| log_bin | ON |
| log_slave_updates | ON |
| read_only | ON |
| relay_log | mysql-relay-bin |
| server_id | 20 |
+-------------------+-----------------+
5 rows in set (0.00 sec)
mysql>/etc/my.cnf sets server_id to 20/etc/my.cnf sets log_bin to mysql-bin/etc/my.cnf sets log_slave_updates to 1/etc/my.cnf sets relay_log to mysql-relay-bin/etc/my.cnf sets read_only to 1server_id has value 20log_bin has value ONlog_slave_updates has value ONrelay_log has value ONread_only has value ONImport the backup on the slave. You will get dozens of lines of status messages. Scan them briefly to make sure there are no errors.
mysql> source /tmp/master_backup.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 508 rows affected (0.02 sec)
Records: 508 Duplicates: 0 Warnings: 0
......some content not shownimportantimportant.stuffConfigure the slave with the network name of the master and the username and password for the replicator account.
This information, combined with the log file and position in the backup file, are all the slave needs to establish replication with the master.
mysql> CHANGE MASTER TO MASTER_HOST='master.example.com', MASTER_USER='replicator', MASTER_PASSWORD='SilkInnFeatGear'; Query OK, 0 rows affected (0.05 sec) mysql>
MASTER_HOST has value master.example.comMASTER_USER has value replicatorStart replication on the slave, then ask MySQL for replication status.
mysql> SLAVE START;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master.example.com
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 960
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1106
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 960
Relay_Log_Space: 1262
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
mysql>SHOW SLAVE STATUS on the slave shows Slave_IO_Running is YesSHOW SLAVE STATUS on the slave shows Slave_SQL_Running is YesNow, insert some new data on the master server.
master ~ $ mysql -u root Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> INSERT INTO important.stuff SET details='Replication is running'; Query OK, 1 row affected (0.04 sec) mysql>
The row you inserted should show up on the slave server, almost immediately.
mysql> SELECT * FROM important.stuff; +----+------------------------+---------------------+ | id | details | happened | +----+------------------------+---------------------+ | 1 | Replication is running | 2013-03-12 19:29:33 | +----+------------------------+---------------------+ 1 row in set (0.00 sec) mysql>
important.stuff