本文内容基于《高性能MySQL》第三版，宁海元、周振兴、彭立勋、翟卫祥等译。
1. 概述
可以通过为服务器配置一个或多个备库的方式来进行数据同步，复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。
复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
MySQL支持两种复制方法：基于行的复制和基于语句的复制。这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。这意味着，在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。
MySQL复制大部分是向后兼容的，新版本的服务器可以作为老版本服务器的备库，但反过来，将老版本作为新版本服务器的备库通常是不可行的，因为它可能无法解析新版本所采用的新的特性或语法，另外所使用的二进制文件的格式也可能不相同。
复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，但出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。除此之外，每个备库也会对主库增加一些负载（例如网络I/O开销），另外锁竞争也可能阻碍事务的提交。
1.1 复制解决的问题
1.1.1 数据分布
可以随意地停止或开始复制，并在不同的地理位置来分布数据备份，例如不同的数据中心。
1.1.2 负载均衡
通过MySQL复制可以将读操作分布到多个服务器上，实现对读密集型应用的优化，并且实现很方便，通过简单的代码修改就能实现基本的负载均衡。对于小规模的应用，可以简单地对机器名做硬编码或使用DNS轮询（将一个机器名指向多个IP地址）。
1.1.3 备份
对于备份来说，复制是一项很有意义的技术补充，但复制既不是备份也不能够取代备份。
1.1.4 高可用性和故障切换
复制能够帮助应用程序避免MySQL单点失败，一个包含复制的设计良好的故障切换系统能够显著地缩短宕机时间。
1.1.5 MySQL升级测试
使用一个更高版本的MySQL作为备库，保证在升级全部实例前，查询能够在备库按照预期执行。
1.2 复制如何工作
MySQL复制数据的三个步骤：
- 在主库上把数据更改记录到二进制日志中（二进制日志事件）；
- 备库将主库上的日志复制到自己的中继日志中；
- 备库读取中继日志中的事件，将其重放到备库数据之上。
2. 配置复制
假设主库和备库都是全新安装并且拥有同样的数据，服务器yeta-1（192.168.31.11）和服务器yeta-2（192.168.31.12）。
2.1 创建复制账号
在主库和备库都创建账号：
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO repl@'192.168.31.%' IDENTIFIED BY 'repl';这个账号限制在本地网络，因为这是一个特权账号，尽管该账号无法执行select或修改数据，但仍然能从二进制日志中获得一些数据。
复制账号只需要有主库上的REPLICATION SLAVE权限，加上REPLICATION CLIENT权限的原因是：
- 用来监控和管理复制账号需要REPLICATION CLIENT权限，并且针对这两种目的使用同一个账号更加容易；
- 如果在主库上建立了账号，然后从主库将数据克隆到备库上时，备库也就设置好了--变成主库所需要的配置，这样后续又需要可以方便地交换主备库的角色。
2.2 配置主库和备库
在主库的my.cnf文件中增加或修改：
# 打开二进制日志
log_bin = mysql-bin
# 指定一个独一无二的服务器ID
# 必须明确地指定一个唯一的服务器ID，一般可以使用服务器IP地址的末8位
server_id = 11
# MySQL每次在提交事务前会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件
sync_binlog = 1重启MySQL后确定二进制日志文件是否已经在主库上创建：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
备库也需要在my.cnf中增加类似的配置，并且重启：
# 打开二进制日志
log_bin = mysql-bin
# 指定一个独一无二的服务器ID
server_id = 11
# 指定中继日志的位置和命名
relay_log = /var/lib/mysql/mysql-relay-bin
# 允许备库将其重放的事件也记录到自身的二进制日志中
log_slave_updates = 1
# 阻止任何没有特权权限的线程修改数据
read_only = 1
# 阻止备库在崩溃后自动启动复制
skip_slave_start = 1【注】Ubuntu + MySQL的配置文件位置：
yeta@yeta-1:/etc/mysql$ ls
conf.d debian.cnf debian-start my.cnf my.cnf.fallback mysql.cnf mysql.conf.dyeta@yeta-1:/etc/mysql$ cat my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/yeta@yeta-1:/etc/mysql$ cd mysql.conf.dyeta@yeta-1:/etc/mysql/mysql.conf.d$ ls
mysqld.cnf mysqld_safe_syslog.cnfyeta@yeta-1:/etc/mysql/mysql.conf.d$ cat mysqld.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server_id = 11
log_bin = /var/log/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem2.3 启动复制
告诉备库如何连接到主库并重放其二进制日志，这一步不要通过my.cnf来配置，而是使用CHANGE MASTER TO语句，该语句完全替代了my.cnf中相应的设置，并且允许以后指向别的主库时无须重启备库：
CHANGE MASTER TO MASTER_HOST='yeta-1',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;可以通过以下语句来检查复制是否正确执行：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: yeta-1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: yeta-2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 4
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)开始复制：
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)再查看状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: yeta-1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: yeta-2-relay-bin.000002
Relay_Log_Pos: 367
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: 154
Relay_Log_Space: 575
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: 11
Master_UUID: d035fd92-e6a6-11e9-a4e1-493017e32d37
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)可以看到I/O线程和SQL线程都已经开始运行。
主库上查看线程列表：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 6
User: repl
Host: yeta-2:47742
db: NULL
Command: Binlog Dump
Time: 204
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
2 rows in set (0.00 sec)备库上查看线程列表：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 177
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 177
State: Slave has read all relay log; waiting for more updates
Info: NULL
3 rows in set (0.00 sec)
测试一下：
主库：
mysql> create database test;
Query OK, 1 row affected (0.05 sec)
mysql> use test;
Database changed
mysql> create table user(
-> id int primary key,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.44 sec)
mysql> insert into user(id, name) values(1, 'yeta');
Query OK, 1 row affected (0.12 sec)从库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | yeta |
+----+------+
1 row in set (0.00 sec)2.4 从另一个服务器开始复制
大多数情况下有一个已经运行了一段时间的主库，然后用一台新安装的备库与之同步，此时这台备库还没有数据。
2.4.1 使用冷备份
关闭主库，把数据复制到备库，重启主库后会使 用一个新的二进制日志文件，在备库通过执行CHANGE MASTER TO指向这个文件的起始处。
mysql数据文件默认在/var/lib/mysql目录下，由于普通用户对这么目录没有权限，所有先发送到该用户自己的工作目录下，然后可以拷贝过去。
- 方法1
root@yeta-1:/var/lib/mysql/test# gzip -c class.frm > class.frm.gzroot@yeta-1:/var/lib/mysql/test# scp class.frm.gz yeta@yeta-2:/home/yeta
yeta@yeta-2's password:
class.frm.gz方法总结：简单但效率不高，因为涉及压缩、复制和解压缩等串行化的步骤。
- 方法2
root@yeta-1:/var/lib/mysql/test# gzip -c class.ibd | ssh yeta@yeta-2 "gunzip -c - > /home/yeta/class.ibd"
yeta@yeta-2's password:方法总结：极大地降低了磁盘I/O，还可以给gzip增加选项-l来提高压缩速度，当然也可以换其他的压缩方法。
- 方法3
yeta@yeta-2:~$ nc -l -p 12345 | tar xvzf -
user.ibdroot@yeta-1:/var/lib/mysql/test# tar cvzf - user.ibd | nc -q 1 yeta-2 12345
user.ibd方法总结：tar直接把文件名字通过网络发送了过去，避免了错误，并自动将文件写到正确的位置。这种方法避免了SSH的加解密的系统开销。
2.4.2 使用热备份
如果是MyISAM表，可以在主库运行时使用mysqlhotcopy或rsync来复制数据。
2.4.3 使用mysqldump
如果是InnoDB表，可以使用以下命令来转储主库数据并将其加载到备库，然后设置相应的二进制日志坐标：
root@yeta-1:/var/lib# mysqldump -hyeta-1 -uroot -proot --single-transaction --all-databases --master-data=1 | mysql -hyeta-2 -uroot -proot
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.选项--single-transaction使得转储的数据为事务开始前的数据，非事务型表可以使用--lock-all-tables选项来获得所有表的一致性转储。
选项--master-data=1的意思是mysqldump出来的文件会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。
2.4.4 使用快照或备份
只要知道对应的二进制日志坐标，就可以使用主库的快照或者备份来初始化备库，只需要把备份或快照恢复到备库，然后使用CHANGE MASTER TO指定二进制日志的坐标。
2.4.5 使用Percona Xtrabackup
开源的热备份工具。
2.4.6 使用另外的备库
可以使用任何一种提及的克隆或者拷贝技术来从任意一台备库上将数据克隆到另外一台服务器。
482
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
