MySQL主从原理详解及架构搭建

mysql

主从复制原理

1、slave上的IO线程拿着master.info文件里面的信息去连接master,并请求从binlog日志文件的指定位置(或者从最开始的位置)往后的所有内容。
2、master验证slave发过来的master.info后,通过负责复制的IO线程返回相应的内容给slave。返回的内容包括slave请求的日志内容和本次响应在master服务器上的binlog文件的名称以及最新的binlog指定位置。
3、slave的IO线程接收到数据后,将接收到的日志内容按顺序追加进relaylog文件(实际文件名为mysql-relay-bin.xxxxxx),并将同时返回的master服务器上的binlog的文件名和新的位置点记录到master.info文件中,以便在下一次发送请求的时候能够清楚的指明需要从哪binlog文件的哪个位置点开始。
4、slave上的SQL线程检测到relaylog中有新增内容后,开始解析新内容,转换成原先在master实际执行的SQL语句,并执行。这样,实际上就是master和slave上执行了同样的SQL语句,所以两边的数据是完全一样的。

配置文件

[mysqld]
server-id = 1 # show global variables like '%server%';
log-bin = mysql-bin # 开启二进制日志,并指定文件名(二进制日志可以用mysqlbinlog转换成文本文件查看) show global variables like '%log%';
log-bin-index = mysql-bin # 开启二进制日志,并指定索引的文件名

slave 配置文件

[mysqld]
server-id = 2 # 一般只改slave上id,master上用默认的1
relay-log = relay-bin # 开启中继日志,并指定文件名 show global variables like '%log%';
relay-log-index = relay-bin
log-slave-updates # slave上的二进制日志输出,主从复制也能叫做主从备份,主挂掉的时候,从顶上去,不输出二进制日志怎么行
relay_log_recovery = 1 # 当slave宕机后,假设relay-log损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从master上获取日志,这样就保证了
relay-log的完整性。 (MySQL 5.5中的新参数)
slave_skip_errors=1023,1062 # 自动跳过1023错误(记录没找到)、1062(主键重复)错误。show variables like 'slave_skip%';

mysqldump参数

-A
转储所有数据库中的所有表。
--master-data
该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限,并且必须启用二进制日志。如果该选项值等于1,位置和文件名被写入CHANGE MASTER语句形式的转储输出,
如果你使用该SQL转储主服务器以设置从服务器,从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2,CHANGE MASTER语句被写成SQL注释。
--single-transaction
InnoDB 表在备份时,通常启用选项 --single-transaction 来保证备份的一致性
--single-transaction --master-data
在启用binlog的场景下,如果需要备份innodb,就需要2个参数一起用才能保证备份数据一致性并且记录当时的binlog信息。
1 mysqldump导出数据主要有两种控制:一种是导出的全过程都加锁 lock-all-tables, 另一种则是不加。前者会在导出开始时执行 FLUSH TABLES WITH READ LOCK; 也就是加全局读锁,会阻塞其它写操作,以保证导出是一致性的;因此只有在导出测试数据时或导出时没有业务连接操作时可不加 lock-all-tables .
至于说一致性导出的另一种方式 single-transaction, 则是有适用范围的,见下边。

2 single-transaction 选项和 lock-all-tables 选项是二选一的,前者是在导出开始时设置事务隔离状态并使用一致性快照开始事务,而后马上unlock tables,然后执行导出,导出过程不影响其它事务或业务连接,但只支持类似innodb多版本特性的引擎,因为必须保证即使导出期间其它操作(事务点t2)改变了数据,而导出时仍能取出导出开始的事务点t1时的数据。而lock-all-tables则一开始就 FLUSH TABLES WITH READ LOCK; 加全局读锁,直到dump完毕。
-- 关于一致性快照,简单地说,就是通过回滚段能记录不同的事务点的各版本数据
-- single-transaction 的流程如下:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
SHOW MASTER STATUS -- 这一步就是取出 binlog index and position
UNLOCK TABLES
...dump...

3 master_data 选项开启时默认会打开lock-all-tables,因此同时实现了两个功能,一个是加锁,一个是取得log信息。
master_data取1和取2的区别,只是后者把 change master ... 命令注释起来了,没多大实际区别;

4 当master_data和 single_transaction 同时使用时,先加全局读锁,然后设置事务一致性和使用一致性快照开始事务,然后马上就取消锁,然后执行导出。过程如下
FLUSH TABLES WITH READ LOCK
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
SHOW MASTER STATUS -- 这一步就是取出 binlog index and position
UNLOCK TABLES
...dump...

5 总结,了解了这些选项作用后,使用起来就明确了.
如果需要binlog信息则使用 master_data;
如果不想阻塞同时表是innodb引擎可使用 single_transaction 取得一致性快照(取出的数据是导出开始时刻事务点的状态)
如果表不支持多版本特性,则只能使用 lock-all-tables 阻塞方式来保证一致性的导出数据。
当然,如果能保证导出期间没有任何写操作,可不加或关闭 lock-all-tables

-q --quick 可以理解为快速备份,特别是在数据量大的时候 一定记得加上这个选项

MySQL 5.5里,新增了一个重要的参数,--dump-slave,使用该参数可在slave端dump数据,建立新的slave,其目的是为了防止对主库造成过大的压力。
mysqldump -A --dump-slave=2 -q --single-transaction > all.sql

注意事项

主从同步,最好主和从的MySQL版本一致,如果不一致,也要保证master是低版本,slave是高版本。(如果主是高版本,从是低版本,主支持了新的字符集,同步到从上面不就出错了吗)
大家所追求的主从零延迟其实是一把双刃剑,主从延时小,数据一致性高,从库能有效的分担数据库读操作的压力。但是,主从毫秒级的同步会有一个安全隐患,如果主上执行了错误的SQL语句,从库上也会马上执行,所以,我们要在一台专门用于备份的从库上启用延时同步,
从 MySQL 5.6开始支持延时备份,增加了新参数MASTER_DELAY:

change master to master_delay = N;
N为从库延迟主库的秒数,默认是0。如果想要从库延迟一小时,则可:

stop slave;
change master to master_delay = 3600;
start slave;
一小时后从库才会开始同步数据。
通过延迟备份的方法很好的解决了主的误操作影响到从,前提是从允许延迟一定的时间。那如何解决呢?当在主上执行了误操作之后,回到从上执行 stop slave;
再执行:

CHANGE MASTER TO MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos;
# Pos通过mysqlbinlog查看二进制日志获得。

这样就跳过了错误,最后通过切换主从或则把从的表覆盖到主上(需要关闭应用)让主的误操作的数据恢复,和通过binlog和备份比大大减少了恢复时间。

tips

在专门用于备份的从库上,执行备份操作的时候,可以先把SQL_THREAD线程停止,等到备份成功后,恢复该线程,而IO_THREAD还是继续问主服务器下载二进制变更信息。
这样备份能很好的保证数据完整性,而且不用锁表之类的操作。

与binlog日志不同的是,当不需要的时候,relaylog日志会被SQL线程自动删除,不需要手动删除
binlog文件的删除,不能直接在文件系统上删除,得 purge master logs to 'mysql-bin.000003',执行此语句会留下mysql-bin.000003,而删除更早的mysql-bin.000002和mysql-bin.000001
。reset master会删除所有的二进制文件,慎用。

搭建同步结构

为保证主从复制的速度和稳定性,这里单独拿了一块网卡用来专门做主从数据传输,若主从服务器在同一机架,而且只有一台从的情况,可以用千兆网线直连两台服务器,这样就算接入层的交换机挂了,你主从都不会出问题,这也是预防一些高可用软件脑裂的一个方法,比如说三张网卡,一张用来提供服务,一张交叉线直连做心跳,剩下的插到同一个交换机上做心跳。

网络情况

master的IP 192.168.2.4 10.0.0.4
slave的IP 192.168.2.6 10.0.0.6
10.0.0.0网段专门用作同步数据

修改配置文件

无需配置主库的my.cnf文件,主库的log-bin和server-id参数默认就是配置好的。
从库my.cnf配置:

[mysqld]
server-id = 8 # 不能和主库相同,推荐设成ip地址最后一位
log-bin = mysql-bin 
log-bin-index = mysql-bin # 开启二进制日志,并指定日志的文件名及索引的文件名。
relay-log = relay-bin 
relay-log-index = relay-bin  # 开启中继日志并指定中继日志的文件名。
log-slave-updates 
# 设定了slave的二进制日志输出,有两个用处,级联同步的时候,一定得开启这个选项;第二,MySQL的同步操作中master必须输出二进制日志,为了使slave升格为master能顺利进行,事先对slave设定好二进制日志输出是较好的选择。

建立同步专用的用户

grant replication slave on *.* to rep@'10.0.0.%' identified by '123456';
flush privileges;

同步开始前的数据准备

这个数据准备其实就是slave的初始数据的准备,在能停止master的mysqld的情况下,直接用tar打包复制包含有MyISAM和InnoDB数据文件的MySQL数据目录,或者使用LVM的快照功能,减少花费的时间。使用tar --exclude选项排除不必要的文件(二进制文件),从而缩短复制同步的时间。
在不能停止mysqld的情况下,只好通过计划任务在凌晨来导出数据了。
先在主库上导出一份全量数据
mysqldump -uroot -p -A --events -B -x --master-data=1 |gzip > $(date +%F).all.sql
--master-data=1 参数会在备份数据里增加如下语句:
-- Position to start replication or point-in-time recovery from
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-.000013',MASTER_LOG_POS=120;

然后用scp拷贝到slave上
scp all.sql root@192.168.2.6:/root/
数据量大的话,可以考虑用rsync或者搭建FTP,在slave端用多线程下载。
在从库上把数据导入:
登录到MySQL里面,执行 source /root/all.sql;

确认master配置文件

查看二进制日志log_bin是否开启了

mysql> show global variables like 'log%';
+----------------------------------------+--------------------------------------------+
| Variable_name                          | Value                                      |
+----------------------------------------+--------------------------------------------+
| log_bin                                | ON                                         |
| log_bin_basename                       | /alidata/server/mysql/data/mysql-bin       |
| log_bin_index                          | /alidata/server/mysql/data/mysql-bin.index |
+----------------------------------------+--------------------------------------------+

查看server-id

mysql> show global variables like 'server%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 1                                    |
| server_id_bits | 32                                   |
| server_uuid    | cc9fb410-4655-11e6-9d80-000c2959b65a |
+----------------+--------------------------------------+

确认slave配置文件

查看中继日志是否开启

mysql> show global variables like 'relay%';
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| relay_log                 | relay-bin                                  |
| relay_log_basename        | /alidata/server/mysql/data/relay-bin       |
| relay_log_index           | /alidata/server/mysql/data/relay-bin.index |
| relay_log_info_file       | relay-log.info                             |
| relay_log_info_repository | FILE                                       |
| relay_log_purge           | ON                                         |
| relay_log_recovery        | OFF                                        |
| relay_log_space_limit     | 0                                          |
+---------------------------+--------------------------------------------+

查看server-id是否和其他机器冲突

mysql> show global variables like 'server%';
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 12                                   |
| server_id_bits | 32                                   |
| server_uuid    | 5f8acc91-47f6-11e6-a81c-000c2935ef78 |
+----------------+--------------------------------------+

启动同步

CHANGE MASTER TO
MASTER_HOST='10.0.0.4', master的ip
MASTER_PORT='3306',
MASTER_USER='rep', master上专用于同步的帐号
MASTER_PASSWORD='123456', rep帐号的密码
MASTER_LOG_FILE='mysql-bin.000013', 在master上执行show master status\G;得到的位置点信息
MASTER_LOG_POS=120;
start slave
这里可以无需指定binlog文件名和pos点信息,因为刚刚备份的时候加了--master-data=1 参数,还原数据库的时候,位置点信息已经在SQL语句里面了。
执行这条命令其实本质是在更新master.info文件,master.info文件本来是没有的,因为我们在主库上用mysqldump备份数据的时候加了--master_data参数,在从库上导入数据的时候,会生成一个简略的master.info文件。

查看同步状态

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.4
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 403
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 566
Relay_Master_Log_File: mysql-bin.000013
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: 403
Relay_Log_Space: 733
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: 1
Master_UUID: cc9fb410-4655-11e6-9d80-000c2959b65a
Master_Info_File: /alidata/server/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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

加粗的重要参数解释:

IO线程

Master_Log_File: mysql-bin.000013 正在读取master上binlog日志名
Read_Master_Log_Pos: 403 正在读取master上当前binlog日志的POS点。

SQL线程

Relay_Master_Log_File: mysql-bin.000013 正在同步master上的binlog日志名
Exec_Master_Log_Pos: 403 正在同步当前binlog日志的POS点。

遇到的错误

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
解决方法
在数据存放目录里面,删除以前的中继日志
\rm relay*

附表:
biaoge

原创文章,转载请注明: 转载自笛声

本文链接地址: MySQL主从原理详解及架构搭建

3 条评论

  • dige 2017年3月1日 回复 作者

    mysql 5.6复制新特性下主从复制配置[基于GTID]:http://634871.blog.51cto.com/624871/1330643

  • Logan 2017年3月3日 回复

    MySQL 5.6 复制介绍:http://blog.itpub.net/24945919/viewspace-765032/

  • baidu 2017年7月26日 回复

    hao

发表评论

电子邮件地址不会被公开。 必填项已用*标注