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 SilkInnFeatGear
In 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-bin
server_id
has value 10
log_bin
has value ON
On 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.sql
Now 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.sql
The 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 1
server_id
has value 20
log_bin
has value ON
log_slave_updates
has value ON
relay_log
has value ON
read_only
has value ON
Import 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 shown
important
important.stuff
Configure 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.com
MASTER_USER
has value replicator
Start 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 Yes
SHOW SLAVE STATUS
on the slave shows Slave_SQL_Running
is Yes
Now, 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