MySQL 复制

标签：主从

概述  

本篇文章主要介绍mysql主从的搭建过程和中间涉及的一些概念知识，希望能最全面的将mysql主从所涉及到的知识都概况进来；环境已经安装好了mysql,这里就不介绍mysql的安装方法。

测试环境：

主：mysql（5.6.21），linux:redhat 6.0,ip:192.168.1.6

从：mysql（5.6.21），linux:redhat 6.0,ip:192.168.1.7

原理和概念

主从复制原理

1)主库在事务提交时会把变更作为事件记录（Events）到二进制文件（Binlog）当中

2)主库将二进制文件中的事件推送到从库的中继日志文件中（Relay-bin）,从库根据中继日志中事件做变更操作。

线程

 Binlog Dump线程：该线程运行在主库上，当主从都配置好后，从库运行START SLAVE启动复制后，会在主库上生成一个                                                                        Binlog Dump线程，该线程的主要作用就是读取主库Binlog事件发送到从库（从库的I/O线程）。

 I/O线程：该线程运行在从库上，I/O线程的作用是向主数据库要数据并且将主库发送过来的变更事件写入到从库的中继日志中。

 SQL线程：该线程运行在从库上，该线程的主要作用是读取中继日志中的变更事件并更新从库。

 该图来自深入浅出mysql数据库开发这本书中。

步骤

主库

在这里主库是运行的，主库的配置文件也是已经配置好了的。

1.配置my.cnf

server-id=6log-bin=/var/lib/mysql/mysql-binmax_binlog_size = 100Msync_binlog=0binlog-format=MIXEDbinlog-ignore-db=testreplicate-ignore-db=test

server-id必须是唯一的，默认设置当前IP主机

log-bin是开启binlog且配置路径，默认是不开启的

max_binlog_size设置binlog文件的最大值，这里设置最大为100M，当达到这个值会自动生成一个新的binlog文件，当然生成环境会设置的比这个大一点。

sync_binlog:配置是否每次事务提交都需要刷新binlog到磁盘，默认0是不每次刷新，有文件系统自己控制，如果设置为1默认每次事务提交都会刷新binlog到磁盘，这样的好处是当系统突然down掉了系统损伤的会少一点，因为binlog也有缓存，默认事务提交是先写缓存这样当系统突然down掉了就有可能会丢失缓存中的记录，但是如果每次事务提交都写磁盘会对性能造成影响，可以通过半同步复制解决因系统突然down掉导致binlog缓存数据丢失的问题。

binlog-format:二进制日志记录的方法，有三种方式：row(记录每一行的变更操作，优点：对复制的兼容性高，缺点：日志记录量大，对IO的影响也很大，也不容易用来做分析)，STATEMENT(记录操作的sql语句，这也是默认的格式，优点：日志量小，便于用来做分析，IO影响小，缺点：可能会导致复制出错例如有时候使用的某些函数)，MIXED(混合了上面两种格式,默认采用STATEMENT记录，当出现不确定函数时就采取row记录例如curret_user(),now()等)

binlog-ignore-db:不记录指定数据库的binlog，如果指定多个数据库可以在配置文件中重复多行。反过来如果配置了binglog-do-db那么久只记录指定的这一关数据库的binlog其它的数据库都不记录。

replicate-ignore-db:不复制指定的数据库的binlog

2.创建复制用户

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.7' IDENTIFIED BY 'repl';

在主库上执行，授予192.168.1.7服务器使用用户repl的REPLICATION SLAVE权限。

3.刷新表并设置数据库只读

FLUSH TABLES WITH READ LOCK;

当前主库只能读不允许更新操作

4.记录主库二进制文件名和偏移量

SHOW MASTER STATUS;

记录日志名和便宜量的目的是为了后面从库用的

 5.备份主库

停止主库服务，需要将主库的数据库备份还原到从库中去

service mysql stop

备份的方法有很多种：1.如果主库是在线不能停止服务，可以通过热备份方式，使用dump、ibbackup、xtrabackup等热备份工具备份数据库然后到从库还原

2.如果主库允许停止服务那么可以直接cp主库数据目录下的所有文件到从库的路径下，可以使用xftp工具比较方便。

做好了这步就可以重启主库服务了。

从库

这里从库的服务是停止的。

1.配置my.cnf

log-bin=/var/lib/mysql/mysql-binserver-id=7

max_binlog_size = 100Msync_binlog=0

binlog-format=MIXEDbinlog-ignore-db=testreplicate-ignore-db=test

注意server-id不能和主相同

2.使用--skip-slave-start方式启动从库服务

使用--skip-slave-start启动的目的是为了不立即启动从服务器上面的复制进程，方便对后面的配置操作。

mysqld_safe --skip-slave-start &

3.登入mysql

mysql -uroot -p123456

登入mysql后执行以下操作

change master to master_host = '192.168.1.6', master_user = 'repl', master_password = 'repl', master_log_file = 'mysql-bin.000046', master_log_pos = 211991;

 启动从库slave线程

start slave

4.检查

 在从库上执行

show processlist\G;

 表明已经连接上面了master

测试

在主库上执行

#在主库上创建repltest数据库并在数据库下创建test表插入数据create database repltest;use repltestcreate table test(id int);insert into test() values(1),(2);#测试在已有的test数据库下创建norepl表并插入数据use testcreate table norepl(id int);insert into norepl() values(1);

在从库执行

select * from repltest.test;

select * from test.norepl;

结果除了test数据库的操作不会被复制以外其他的数据操作都会被复制到从库。这也正符合前面的设置。

补充

 从服务器启动参数

–read_only该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新。可以确保从服务器不接受来自客户的更新。–replicate_do_db=db_name告诉从服务器只做默认数据库(由USE所选择)为db_name的语句的复制。要指定多个数据库，应多次使用该选项，每个数据库使用一次。请注意不复制跨数据库的语句–replicate_do_table=db_name.tbl_name告诉从服务器线程只做对指定表的复制。要指定多个表，应多次使用该选项，每个表使用一次。同–replicate-do-db对比，允许跨数据库更新。–replicate_ignore_db=db_name告诉从服务器不要复制默认数据库(由USE所选择)为db_name的语句。要想忽略多个数据库，应多次使用该选项，每个数据库使用一次。–replicate-ignore-table=db_name.tbl_name告诉从服务器线程不要复制更新指定表的任何语句(即使该语句可能更新其它的表)。要想忽略多个表，应多次使用该选项，每个表使用一次。–replicate_wild_do_table=db_name.tbl_name告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。模式可以包含‘%'和‘_'通配符，与LIKE模式匹配操作符具有相同的含义。要指定多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。–replicate_wild_ignore_table=db_name.tbl_name告诉从服务器线程不要复制表匹配给出的通配符模式的语句。要想忽略多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。–replicate_rewrite_db=from_name->to_name告诉从服务器如果默认数据库(由USE所选择)为主服务器上的from_name，则翻译为to_name。只影响含有表的语句–report_host=slave_name从服务器注册过程中报告给主服务器的主机名或IP地址。该值出现在主服务器上SHOW SLAVE HOSTS的输出中。如果不想让从服务器自己在主服务器上注册，则不设置该值。–report_port=slave_port连接从服务器的TCP/IP端口号，从服务器注册过程中报告给主服务器。–skip_slave_start告诉从服务器当服务器启动时不启动从服务器线程。使用START SLAVE语句在以后启动线程。–slave_skip_errors=[err_code1,err_code2,… | all]通常情况，当出现错误时复制停止，这样给你一个机会手动解决数据中的不一致性问题。该选项告诉从服务器SQL线程当语句返回任何选项值中所列的错误时继续复制

复制线程状态

通过show processlist \G可以查看复制线程状态。常见的线程状态有：（1）主服务器Binlog Dump线程Has sent all binlog to slave; waiting for binlog to be updated线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件。（2）从服务器I/O线程状态Waiting for master to send event线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发生超时。此时，线程认为连接被中断并企图重新连接。（3）从服务器SQL线程状态Reading event from the relay log线程已经从中继日志读取一个事件，可以对事件进行处理了。Has read all relay log; waiting for the slave I/O thread to update it线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

文件

在从库的数据库路径下会发现生成了三个文件：master.info,relay-log.info,relay-bin

master.info:用来记录从库的I/O线程当前读取到主库的binglog的位置。

relay-log.info：用来记录从库的SQL线程当前读取到中继日志(relay-bin)的位置。

relay-bin:中继日志，中继日志记录的格式和主库的二进制日志是一样的，但是中继日志在SQL线程执行完当前中继日志中的事件之后会删除中继日志中的内容。

从库复制状态

可以在从库上通过show slave status \G查看

*************************** 1. row ***************************        Slave_IO_State: Waiting for master to send event         Master_Host: 192.168.1.6         Master_User: repl         Master_Port: 3306        Connect_Retry: 60       Master_Log_File: mysql-bin.000047     Read_Master_Log_Pos: 763952        Relay_Log_File: localhost-relay-bin.000003        Relay_Log_Pos: 764115    Relay_Master_Log_File: mysql-bin.000047       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: 763952       Relay_Log_Space: 764455       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: 0Master_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: 6         Master_UUID: d58e2793-8534-11e5-b224-000c2908cc04       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 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

Slave_IO_State: 线程已经连接上主服务器，正等待二进制日志事件到达Master_Host: 主服务器ipMaster_User: 连接主服务器使用的用户Master_Port: 主服务器的端口Connect_Retry: 当重新建立主从连接时，如果连接建立失败，间隔多久后重试，默认60s。Master_Log_File: I/O线程当前正在读取的主服务器二进制日志文件的名称Read_Master_Log_Pos: 在当前的主服务器二进制日志中，I/O线程已经读取的位置。Relay_Log_File: SQL线程当前正在读取和执行的中继日志文件的名称Relay_Log_Pos: SQL线程在当前的中继日志中已读取和执行的位置。Relay_Master_Log_File: SQL线程执行的主服务器二进制文件Slave_IO_Running: I/O线程是否运行并成功地连接到主服务器上。Slave_SQL_Running: SQL线程是否运行。Replicate_Do_DB:用于复制的数据库，必须在配置文件中配置了Replicate_Ignore_DB:不用来复制的数据库Replicate_Do_Table:复制表Replicate_Ignore_Table:不复制的表Replicate_Wild_Do_Table: 限制复制更新的表匹配指定的数据库和表名模式的语句Replicate_Wild_Ignore_Table: 不要复制表匹配给出的通配符模式的语句Last_Errno:错误代码Last_Error:错误信息Skip_Counter: SQL_SLAVE_SKIP_COUNTER的值Exec_Master_Log_Pos: 主服务器上一个被执行的位置Relay_Log_Space: 中继日志文件大小Until_Condition: 在START SLAVE语句的UNTIL子句中指定的值Until_Log_File: 用于指示日志文件名Until_Log_Pos: 位置值Master_SSL_Allowed: 如果允许对主服务器进行SSL连接，则值为Yes否则NOMaster_SSL_CA_File:下面的这些都是SSL连接的一些信息Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 本字段是从属服务器落后多少的一个指示(这个状态是一个很重要的性能指标，正常为0，如果从服务器的I/O线程无法连接主服务器显示null)Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 最近的IO线程错误代码，其中2003代表I/o线程无法连接主服务器Last_IO_Error: 最近的IO线程错误信息（例如：error reconnecting to master 'repl@192.168.1.6:3306' - retry-time: 60 retries: 3）Last_SQL_Errno: 最近的SQL线程错误代码Last_SQL_Error: 最近的SQL线程错误信息Replicate_Ignore_Server_Ids:Master_Server_Id: 主服务器的服务器IDMaster_UUID: 主服务器的UUID值Master_Info_File: 从服务器的master.info文件路径SQL_Delay: 正数表明slave有延迟了SQL_Remaining_Delay: 整数表明延迟时间Slave_SQL_Running_State: SQL线程运行状态（SQL线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。）Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:最近的I/O线程错误时间Last_SQL_Error_Timestamp:最近的SQL线程报错时间Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0

其中需要注意比较重要的状态：Slave_SQL_Running之前的这十几个状态再加上Last_IO_Errno，Last_IO_Error，Last_SQL_Errno，Last_SQL_Error，SQL_Delay，SQL_Remaining_Delay，Slave_SQL_Running_State，Last_IO_Error_Timestamp，Last_SQL_Error_Timestamp

总结

在企业的环境中主从复制是最基础也是非常普遍的一种形式，相对来说配置管理也比较简单。上面讲的是一主一从，在这基础上只要再添加从服务器就可以做到一主多从的结构，但是这种结构对于主服务器的可靠性要求很高，这就又有了主主复制，后面会花点时间写一篇关于主主复制的文章，欢迎关注。

---恢复内容结束---

原标题：MySQL 复制

关键词：MYSQL