mysql主从表_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-bin

max_binlog_size=100M

sync_binlog=0binlog-format=MIXED

binlog-ignore-db=test

replicate-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-bin

server-id=7

max_binlog_size = 100M

sync_binlog=0

binlog-format=MIXED

binlog-ignore-db=test

replicate-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

注意线程3中的Time字段：该时间表示上次执行的语句在主库二进制文件中记录的时间和更新到从库的时候的当前时间的时间差，如果主库更新非常频繁而从库又跟不上主库更新的速度的时候该时间差值会增大(影响的因素有：从库的硬件和主库的差距、网络传输、早期版本的从库sql线程是单线程写而主库应用前端的写的多线程并发写)。

测试

在主库上执行

#在主库上创建repltest数据库并在数据库下创建test表插入数据

create database repltest;

use repltest

create table test(idint);

insert into test() values(1),(2);

#测试在已有的test数据库下创建norepl表并插入数据

use test

create table norepl(idint);

insert into norepl() values(1);

在从库执行

select * from repltest.test;

select * from test.norepl;

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

补充

从服务器启动参数

–read_only

该选项让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新(ALL PRIVILEGES权限的用户也不行，必须是超级用户)。可以确保从服务器不接受来自客户的更新。

–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线程当语句返回任何选项值中所列的错误时继续复制

log_slave_updates

配置从库的更新操作是否写二进制日志，默认从库读取主库过来的二进制日志只写入中继日志文件中(mysqld-relay-bin.000001)文件中不会写入从库的二进制文件中，无论从库的二进制文件是否开启，如果你需要主库传递过来的二进制日志写入从库的二进制文件中就必须开启此参数，该参数不能在线开启，只能修改配置文件“log_slave_updates=1”，默认不打开，除非你的从库还需要作为其它从库的主库，如果开启次参数需要和bin-log一起开启。

master_connect_retry配置从库和主库连接中断后重试连接的时间间隔，默认是60S

复制线程状态

通过show processlist \G可以查看复制线程状态。常见的线程状态有：

(1)主服务器Binlog Dump线程

Has sentall binlog to slave; waiting for binlog tobe updated

线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件。

(2)从服务器I/O线程状态

Waitingfor master tosend event

线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发生超时。此时，线程认为连接被中断并企图重新连接。

(3)从服务器SQL线程状态

Reading eventfrom the relay log线程已经从中继日志读取一个事件，可以对事件进行处理了。

Hasread all relay log; waiting for the slave I/O thread to updateit

线程已经处理了中继日志文件中的所有事件，现在正等待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: Waitingfor master tosend event

Master_Host:192.168.1.6Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000047Read_Master_Log_Pos:763952Relay_Log_File: localhost-relay-bin.000003Relay_Log_Pos:764115Relay_Master_Log_File: mysql-bin.000047Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:763952Relay_Log_Space:764455Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_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:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:6Master_UUID: d58e2793-8534-11e5-b224-000c2908cc04

Master_Info_File:/var/lib/mysql/master.info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State: Slave hasread all relay log; waiting for the slave I/O thread to updateit

Master_Retry_Count:86400Master_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: 主服务器ip

Master_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

否则NO

Master_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: No

Last_IO_Errno: 最近的IO线程错误代码，其中2003代表I/o线程无法连接主服务器

Last_IO_Error: 最近的IO线程错误信息(例如：error reconnectingto 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: 主服务器的服务器ID

Master_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，Seconds_Behind_Master

解决主从延时的方案

从前面总结的延时导致的原因主要有可能来自以下三个原因：

网络传输：对应网络传输这块只能从硬件方面解决，避免千兆网关用百兆网线、百兆网卡等情况，主从在同一网段。

硬件问题：对应因为主从的硬件相差非常大的情况，可以适当提高从库的硬件。

并发写的问题：在5.6版本之后从库的sql线程改成了并发写，有助于提高从库的写延时的问题；

查询从库配置的多个SQL线程

show variableslike '%slave_parallel_workers%';

还有一种方案是对于一主多从的方案可以将主库的复制数据库或者表分散到每个从库上面，例如每个从库复制一个数据库来减少从库的压力，但是这种方案对于主宕机之后由于每个从库只有单个数据库的数据如果需要组合多个从库的数据会有点麻烦。

总结

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

备注：

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。

《欢迎交流讨论》