MySQL复制详解

43524

目录：
       1、简介
       2、原理
       3、常见复制架构
       4、一主一丛异步复制演示
       5、测试结果
       6、额外的配置参数
       7、提升备库成为主库
              7.1计划内的提升
              7.2计划外的提升
       8、半同步复制配置演示
       9、双主双写配置演示
       10、处理可以忽略的错误
       11、总结
1、简介：MySQL内建的复制功能是构建基于MySQL的大规模，高性能应用的基础。复制就是让一台服务器的数据和其它服务器保持同步，一台主库可以同步到多台备库上面，备库也可以作为另一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
2、原理： 在主库上记录二进制日志，在备库重放日志的方式实现异步数据复制。
总的来说，复制有三个步骤：

1)、主库记录二进制日志，每次准备提交事物完成数据库更新前，先记录二进制日志，记录二进制日志后，主库会告诉存储引擎可以提交事物了
2)、备库将主库的二进制日志复制到本地的中继日志中，首先，备库会先启动一个工作进程，称为IO工作线程，负责和主库建立一个普通的客户端连接。如果该进程追赶上了主库，它将进入睡眠状态，直到主库有新的事件产生通知它，他才会被唤醒，将接收到的事件记录到中继日志中。
3)、备库的SQL线程执行最后一步，该线程重中继日志中读取事件并且在备库执行，当SQL线程赶上IO线程的时候，中继日志通常记录在系统缓存中，所以中继日志的开销很低。SQL线程也可以根据配置选项来决定是否写入其自己的二进制日志中。
3、常见复制架构
3.1 一主多从
3.2主主
3.3环形复制 等等，下面这张图片包含了常见复制架构



4、一主一从实验演示，本实验都使用多实例演示。
1)、安装MySQL

1 2 3

mkdir -p /data/{3306,3307}/data /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/3306/data/ --basedir=/usr/local/mysql/ /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/3307/data/ --basedir=/usr/local/mysql/

2)、为主服务器提供配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

cp /usr/local/mysql/support-files/my-small.cnf /data/3306/my.cnf vim /data/3306/my.cnf   [client] #password       = your_password port            = 3306 socket          = /tmp/mysql.sock1 # The MySQL server [mysqld] port            = 3306 socket          = /tmp/mysql.sock1 skip-external-locking key_buffer_size = 16K max_allowed_packet = 1M table_open_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 128K datadir = /data/3306/data    #指定数据存放位置 server-id       = 3306     #指定一个唯一的server id，可以使用ip后8位，本机采用端口 log-bin=/data/3306/data/mysql-bin  #二进制日志的位置 binlog_format=mixed            #二进制日志的格式 sync_binlog=1              #MySQL在每次提交事物前会把二进制日志同步到磁盘上面。

3)、为备服务器提供配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

cp /usr/local/mysql/support-files/my-small.cnf /data/3307/my.cnf vim /data/3307/my.cnf [client] #password       = your_password port            = 3307 socket          = /tmp/mysql.sock2 # The MySQL server [mysqld] port            = 3307 socket          = /tmp/mysql.sock2 skip-external-locking key_buffer_size = 16K max_allowed_packet = 1M table_open_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 128K datadir = /data/3307/data  #指定数据存放位置 server-id       = 3307   #为备服务器提供唯一的server id relay-log = /data/3307/data/relay-log    #指定中继日志的位置和名称 log_bin = /data/3307/data/mysql-bin    #指定二进制日志的位置和名称 log_slave_updates = 1    #允许备库将其重放的事件也记录到自身的二进制日志中。 read_only = 1   #该选项会阻止任何没有特权权限的线程修改数据。 skip_slave_start #该选项能够阻止备库在崩溃后自动启动复制，崩溃后启动复制，数据可能不一致 #sysnc_master_info = 1 #sync_relay_log = 1 #sync_relay_log_info = 1

4)、主库创建复制账号

1 2 3 4 5 6 7

mysql -S /tmp/mysql.sock1   #登录主服务器 mysql> grant replication slave on *.* to 'rep'@'192.168.198.%' identified by '123456';  #创建并授权复制账号 Query OK, 0 rows affected (0.05 sec) mysql>flush privileges;  #刷新权限 Query OK, 0 rows affected (0.05 sec)

5)、为备库提供数据，备库和主库数据不一致会出现错误，生产场景下，大多数也是主库已经运行了一段时间了。这里给主库做个全备，然后恢复到备库上面。

1 2	mysqldump -S /tmp/mysql.sock1 -A -B -x --events --master-data=1 > /opt/data.sql    #做一次完全备份 mysql -S /tmp/mysql.sock2 < /opt/data.sql     #备库恢复数据

6)、备库连接主库

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql -S /tmp/mysql.sock2  #登录备库 mysql> CHANGE MASTER TO     -> MASTER_HOST='192.168.198.139',     -> MASTER_PORT=3306,     -> MASTER_USER='rep',     -> MASTER_PASSWORD='123456';    #注意：因为备份的时候指定了--master-data=1,里面已经告诉了备库，主库复制的二进制的位置，假如没有指定--master-data=1 ; mysql> show master status;   #在主库上，查看当前二进制日志的位置 +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 |      402 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) #备库上面，执行change master 的时候需要如下指定指定 mysql> CHANGE MASTER TO     -> MASTER_HOST='192.168.198.139',     -> MASTER_PORT=3306,     -> MASTER_USER='rep',     -> MASTER_PASSWORD='123456',     ->MASTER_ LOG_FILE='mysql-bin.000006',   #指定二进制日志的名称     ->MASTER_LOG_POS=402;   # 指定二进制日志位置

7)、启动复制

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

mysql> start slave;   Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.198.139                   Master_User: rep                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000006           Read_Master_Log_Pos: 402                Relay_Log_File: relay-log.000003                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000006              Slave_IO_Running: Yes        #IO线程状态             Slave_SQL_Running: Yes             #SQL线程状态               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: 402               Relay_Log_Space: 549               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: 3306 1 row in set (0.00 sec)

8)、查看主库和备库进程状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

mysql> show processlist\G    #这是主库 *************************** 1. row ***************************      Id: 9    User: root    Host: localhost      db: NULL Command: Query    Time: 0   State: NULL    Info: show processlist *************************** 2. row ***************************      Id: 10    User: rep    Host: 192.168.198.139:37230      db: NULL Command: Binlog Dump    Time: 348   State: Master has sent all binlog to slave; waiting for binlog to be updated   #线程状态    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: NULL    Info: show processlist *************************** 2. row ***************************      Id: 12    User: system user    Host:      db: NULL Command: Connect    Time: 395   State: Waiting for master to send event     #从库IO线程状态    Info: NULL *************************** 3. row ***************************      Id: 13    User: system user    Host:      db: NULL Command: Connect    Time: 395   State: Slave has read all relay log; waiting for the slave I/O thread to update it   #从库SQL线程状态    Info: NULL 3 rows in set (0.00 sec)

关于复制线程状态，参考：http://blog.itpub.net/7970627/viewspace-717664/
9)、复制文件信息
mysql-bin.index  #二进制文件索引，不可以被删除，立面每一行包含一个二进制文件的文件名，如果被删除，MySQL将识别不了二进制文件

1 2 3 4 5 6 7

cat /data/3306/data/mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 /data/3306/data/mysql-bin.000003 /data/3306/data/mysql-bin.000004 /data/3306/data/mysql-bin.000005 /data/3306/data/mysql-bin.000006

mysql-relay-bin-index  #中继日志的索引文件，和mysql-bin.index 作用类似

1 2 3

cat /data/3307/data/relay-log.index /data/3307/data/relay-log.000002 /data/3307/data/relay-log.000003

master.info #保存备库连接到主库的信息，格式为纯文本，该文件不能被删除，否则备库无法连接到主库  
master.info内容  #请自行查看
relay-log.info #包含当前备库复制的二进制日志和中继日志坐标，不可删除，否则备库不知道从哪个位置开始复制
行 描述  

1 2 3 4 5 6

cat /data/3307/data/relay-log.info /data/3307/data/relay-log.000003    # Relay_Log_File 253                                 # Relay_Log_Pos mysql-bin.000006                    # Master_Log_File 402                                 # Exec_Master_Log_Pos 967                             #####

5、测试结果
主库：

1 2 3 4 5 6 7 8 9

mysql> use test; Database changed mysql> create table student (                     #在主库上创建表，并且插入数据     -> id int(2) AUTO_INCREMENT PRIMARY KEY,     -> names char(20)); Query OK, 0 rows affected (0.19 sec) mysql> insert into student(names) values('yun zhongehe'); Query OK, 1 row affected (0.03 sec)

从库：

1 2 3 4 5 6 7

mysql> select * from test.student;    #在从库上查看主库的数据是否同步过来 +----+--------------+ | id | names        | +----+--------------+ |  1 | yun zhongehe | +----+--------------+ 1 row in set (0.00 sec)

可以看出，从库会自动复制主库的数据。
6、额外的配置参数
6.1、推荐的复制选项

1 2 3

skip_slave_errors = xxx,xxx  #从服务器再复制过程中忽略的错误类型，xxx为mysql错误代码。 skip_slave_start  #从服务器崩溃之后，重新启动，不会自动复制 log_slave_updates=1  #可以把S变成M，让从服务器把自身复制的事件和记录都写到自己的二进制日志里 read_only=1   #从机会阻止没有特殊权限的线程更改数据，即就是从机只能读，不能写. sync_binlog = 1 #主机每次提交事务的时候把二进制日志的内容同步到磁盘上，所以即使服务器崩溃，也会把事件写入日志中。

6.2复制过滤选项

1 2 3 4

binlog_do_db   #只复制指定的数据库(主服务器配置文件里设置) binlog_ignore_db   #不复制指定的数据库(主服务器配置文件里设置) replicate_do_db  #从服务器复制指定的数据库 replicate_ignore_db #从服务器不复制指定的数据库    replicate_ignore_table  #不复制指定的表(从服务器配置文件里设置) replicate_wild_ignore_table  #使用wild匹配来不复制的指定表(从服务器配置文件里设置),比如参数设为abc.%,表示不复制abc的所有表。

7、提升备库成为主库
有些情况需要将备库提升为主库，一种情况是计划内的，另一种是计划外的，如主库宕机。
7.1计划内的提升
1)、停止当前主库上所有的写操作，如果可以，最好将所有的客户端程序关闭，如果正在使用虚拟IP地址，也可以简单的关闭虚拟IP，然后断开所有的客户端连接以关闭其打开的事物。
2)、通过Flush tables with read lock; 在主库停止所有活跃的写入，这一步是可选的。这时刻应该禁止向即将被替换的主库写入，因为写入的任何数据意味着丢失。为了更好的保证这一点，可以kill所有打开的事物，这会真正的结束所有写入。
3)、选择一个备库作为主库，并确保它执行完所有从主库获得的中继日志。
4)、确保新主库和旧主库的数据是一致的。
5)、新主库执行stop slave;
6)、在新主库执行CHANGE MASTER TO MASTER_HOST='';，然后执行RESET SLAVE，使其断开与老主库的连接，并丢弃master.info 里面的信息。如果连接信息保存在my.cnf里面，会无法正确工作
7)、执行SHOW MASTER STATUS；记录新主库的二进制日志坐标
8)、确保其它备库已经赶上。
9)、关闭旧主库
10)、在MySQL 5.1 以及以上的版本，如果需要，激活新主库的事件
11)、将客户端连接到新主库
12)、在每台备库上面，执行CHANGE MASTER TO语句使用之前通过SHOW MASTER STATUS获得的二进制坐标日志，来指向新的主库。
7.2计划外的提升
1)、确定那台备库的数据最新，检查每台备库上SHOW SLAVE STATUS；命令的输出，选择值最新的哪一个
2)、让所有的被执行完所有其从崩溃前的旧主库获得的中继日志，如果再未完成前修改备库的主库，它会抛弃剩下的日志事件，从而无法获得该备库在什么地方停止。
3)、执行计划内提升的5~7步。
4)、比较每台备库和新主库的MASTER_LOG_FILE/Read_Master_Log_Pos的值。
5)、执行计划内提升的的10~12步。
8、mysql的半同步复制。
8.1半同步复制在提交过程中增加了一个延迟：当提交事物时，在客户端接收到查询结束反馈前必须保证二进制日志已经传输到至少一台备库上。主库将事物提交到磁盘之后会增加一些延迟。同样的也增加了客户端的延迟。
主库和从库必须同时配置半同步插件才可以生效，否则还是异步的方式。
8.2临时生效的配置：

1 2	ls /usr/local/mysql/lib/plugin/semisync_*    #查看相关的半同步插件 /usr/local/mysql/lib/plugin/semisync_master.so  /usr/local/mysql/lib/plugin/semisync_slave.so

主库：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';   #安装插件 Query OK, 0 rows affected (0.10 sec) mysql> show global variables like 'rpl%';   #查看插件状态变量 +------------------------------------+-------+ | Variable_name                      | Value | +------------------------------------+-------+ | rpl_recovery_rank                  | 0     | | rpl_semi_sync_master_enabled       | OFF   | | rpl_semi_sync_master_timeout       | 10000 | | rpl_semi_sync_master_trace_level   | 32    | | rpl_semi_sync_master_wait_no_slave | ON    | +------------------------------------+-------+ 5 rows in set (0.00 sec) mysql> set global rpl_semi_sync_master_enabled = 1;  #启用插件 Query OK, 0 rows affected (0.00 sec) mysql> set global rpl_semi_sync_master_timeout = 1000;   #设置超时时长 Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'rpl%'; +------------------------------------+-------+ | Variable_name                      | Value | +------------------------------------+-------+ | rpl_recovery_rank                  | 0     | | rpl_semi_sync_master_enabled       | ON    | | rpl_semi_sync_master_timeout       | 1000  | | rpl_semi_sync_master_trace_level   | 32    | | rpl_semi_sync_master_wait_no_slave | ON    | +------------------------------------+-------+ 5 rows in set (0.00 sec)

从库：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装从库半同步插件 Query OK, 0 rows affected (0.08 sec) mysql> show global variables like 'rpl%'; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | rpl_recovery_rank               | 0     | | rpl_semi_sync_slave_enabled     | OFF   | | rpl_semi_sync_slave_trace_level | 32    | +---------------------------------+-------+ 3 rows in set (0.00 sec) mysql> set global rpl_semi_sync_slave_enabled = 1;   Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'rpl%'; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | rpl_recovery_rank               | 0     | | rpl_semi_sync_slave_enabled     | ON    | | rpl_semi_sync_slave_trace_level | 32    | +---------------------------------+-------+ 3 rows in set (0.00 sec) mysql> stop slave io_thread; Query OK, 0 rows affected (0.02 sec) mysql> start slave io_thread;      #重启进程让其生效 Query OK, 0 rows affected (0.00 sec)

8.2半同步永久有效方式
主库配置文件：

1 2 3

[mysqld]   rpl_semi_sync_master_enabled=1   rpl_semi_sync_master_timeout=1000   #此单位是毫秒

从库

1 2	[mysqld]   rpl_semi_sync_slave_enabled=1

然后重新启动服务即可。
9、配置双写主-主模型。
这种配置产生的问题比较多，一般不建议这样配置。只需要设置auto_increment_incremnt和auto_increment_offset ，即可完成配置，比较简单。
9.1配置
主库:

1 2 3 4 5

[myqld] #rpl_semi_sync_master_enabled=1     #注释掉半同步复制 #rpl_semi_sync_master_timeout=1000   #注释掉 auto_increment_increment = 2          #在刚开始的文件加入此选项，此选项设置 为相同，数字一般为主服务器的数量 auto_increment_offset = 1           #偏移量设置为不同

从库，即将提升为主库

1 2 3 4 5 6 7 8

[mysqld] #read_only = 1   #注释掉 #skip_slave_start   #注释 log-bin=/data/3307/data/mysql-bin   #开启二进制文件 binlog_format=mixed auto_increment_increment = 2     #和上面的主库一样 auto_increment_offset = 2      #自增偏移量。 #rpl_semi_sync_slave_enabled=1   #注释

9.2重启两个实例
9.3在3307端口，也就是刚才的从库上面

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> start slave;   Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 |      107 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> select user,host from mysql.user where user='rep'; +------+---------------+ | user | host          | +------+---------------+ | rep  | 192.168.198.% | +------+---------------+ 1 row in set (0.00 sec)

在主库上执行：

1 2 3 4 5 6 7 8 9 10 11

mysql> CHANGE MASTER TO     -> MASTER_HOST='192.168.198.139',     -> MASTER_PORT=3307,    #注意端口     -> MASTER_USER='rep',     -> MASTER_PASSWORD='123456',     -> MASTER_LOG_FILE='mysql-bin.000005',   #即上面看到的日志     -> MASTER_LOG_POS=107; Query OK, 0 rows affected (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)

9.4测试
主库：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> use test; Database changed mysql> insert into student(names) values ('yun zhonghe1'); Query OK, 1 row affected (0.02 sec) mysql> insert into student(names) values ('yun zhonghe2'),('yun zhonghe3'); Query OK, 2 rows affected (0.02 sec) Records: 2  Duplicates: 0  Warnings: 0 mysql> select * from student; +----+--------------+ | id | names        | +----+--------------+ |  1 | yun zhongehe | |  3 | yun zhonghe1 | |  5 | yun zhonghe2 | |  7 | yun zhonghe3 | +----+--------------+ 4 rows in set (0.00 sec)

从库：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> insert into student(names) values ('huang yaoshi2'),('huang yaoshi3'); Query OK, 2 rows affected (0.01 sec) Records: 2  Duplicates: 0  Warnings: 0 mysql> select * from student; +----+---------------+ | id | names         | +----+---------------+ |  1 | yun zhongehe | |  3 | yun zhonghe1 | |  5 | yun zhonghe2 | |  7 | yun zhonghe3 | |  8 | huang yaoshi2 | |  10| huang yaoshi3 | +----+---------------+ 5 rows in set (0.00 sec)

大致就是这个样子，中间还是可能会出现一些问题，建议不要这样使用。
10、如何处理可以忽略的错误
有时候，测试数据会弄错，但是又不想太麻烦，当前的错误可以忽略，则可以通过下面的命令实现。

1 2 3 4 5 6 7 8

mysql> stop slave; Query OK, 0 rows affected (0.03 sec) mysql> set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec)

然后就会忽略当前的错误了。，
11、总结：
       1)、复制是个大的话题，建议自己多去搜集些资料以供后来参考
       2)、实际中，不要使用mysql的超级用户登录，这样在配置从服务器容易出错。使用一般的用户即可。
       3)、这篇博文还有很多未涉及到的东西，以后可能会补充。另外就是如果有错误，欢迎指出。