Linux运维学习笔记之二十一： MySQL主从复制

第三十二章 MySQL主从复制

一、MySQL主从复制的应用场景

1、MySQL主从复制的作用

MySQL主从复制有利于数据库架构的健壮性、提升访问速度和易于维护管理。主要有主从服务器互为备份、主从服务器读写分离分担网站压力、三个应用场景

2、场景1：主从服务器互为备份

主从服务器架构的设置，可以大大的加强数据库架构的健壮性。当主服务器出现问题后，可以人工或自动的切换到从服务器上继续提供服务。类似于nfs存储通过inotify +rsync同步到备份nfs 上，只不过MySQL的同步，是其自带的工具。对非人为的硬件、服务故障，人为的执行drop、delete 无能为力。

3、场景2：读写分离

主从服务器架构可以通过程序（PHP、Java等）或代理软件（mysql-proxy、amoeba等）对用户（客户端）的请求实现读写分离，即通过在从服务器上仅仅处理用户的Select查询请求，降低用户查询响应时间及读写同时在主服务器带来的压力。对于更新的数据（Update、Insert、Delete）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

在MySQL实现主从同步后，企业读写分离实现方式：

中大型公司：通过PHP、Java等程序

测试环境： mysql-proxy、amoeba等代理软件

门户网站：分布式dbproxy实现读写分离、hash负载均衡、健康检查（百度、阿里都自行开发）

4、场景3：根据业务进行拆分，分担压力

可以把几个不同的从服务器，根据企业业务进行拆分。如有提供查询的从服务器、有DBA备份的从服务器、还有提供内部人员访问的后台、脚本、日志分析及开发人员服务的从服务器。可以有效的减轻主服务器的压力的同时，使各个业务之间互不影响。

二、MySQL主从复制的原理和步骤

1、主从形式

（1）一主一从

（2）主主复制

（3）一主多从---扩展系统读取的性能，因为读是在从库读取的；

（4）多主一从---5.7开始支持

（5）联级复制

2、主从部署必要条件

（1）主库开启binlog日志（设置log-bin参数）

（2）主从server-id不同

（3）从库服务器能连通主库

3、主从复制原理

（1）主库（Master）

配置好从库复制所需的帐户（需replication slave权限）。

必须开启binlog日志，如有数据更新，则会在将数据写入数据库的同时，将更新结果也写入binlog文件中。

会生成一个I/O线程，用来接收从库I/O线程的请求，并给从库I/O线程传binlog。

（2）从库（Slave）

必须先通过CHANGE MASTER TO命令配置Master信息：将Master的host、user、password、port、binlog文件名以及位置点进行配置，生成master.inof文件。以便从库读取，并连接上主库。

如果在备份命令中加了--master-data=1，则备份文件中会带有binglog文件名和位置点信息，在master.info文件中就不需要指定文件名以及位置点了。

开启同步的开关：start slave。此时，生成两个线程，一个I/O线程，一个SQL线程。I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中。SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。

4、主从复制的过程

在Master服务器开启binlog和配置好从库复制所需的帐户，Slave服务器配置好master.info文件后，MySQL Replication的复制过程步骤如下：

（1）Slave服务器的执行start slave，开启主从复制开关，产生IO Thread和SQL Thread线程。

（2）此时，Slave服务器的IO线程会根据 master.info文件的内容请求连接Master服务器，并请求Master服务器发送指定binlog日志文件的指定位置点之后的binlog日志。

（3）Master服务器接收到Slave服务器的IO线程的请求后，Master服务器首先进行验证，验证通过后负责复制的IO线程根据Slave的IO线程请求读取指定的binlog日志文件的指定位置之后的binlog日志信息，连同本次返回日志内容后在Master服务器上的新的binlog文件名以及下一个指定更新的位置点一起发送给Slave服务器的IO线程。

（4）Slave服务器的IO线程接收到信息后，将binlog日志内容依次写入到中继日志（Relay Log）文件的最末端（MySQL-relay-bin.xxxxxx），将将新的binlog文件名和位置记录到master.info文件中，以便一下次从新的位置读取。

（5）Slave服务器的SQL线程会实时检测到本地Relay Log中新增加的日志内容，如有更新，则及时把Relay Log文件中的内容解析为在Master上执行过的SQL语句，按顺序在Slave服务器上执行。并在应用完毕后清理应用过的日志。

（6）经过上面步骤后，一般可以确保在Master和Slave上执行同样的SQL语句。在正常情况下可以保证Master和Slave上的数据完全一样。

5、级联主从复制

一般情况下Slave服务器在应用复制过来的binlog日志信息时是不写入Slave端的binlog日志文件的。但如果是级联复制的话，Slave服务器还要将数据复制到下一级Slave服务器，则需要将应用的binlog日志内容写入自身的binlog文件中，以便进行复制。此时，需在第一级Slave服务器上修改my.cnf文件，启用binlog日志，并打开log_slave_update参数，则可将应用的日志内容写入binlog文件中。

6、配置主从复制步骤

（1）启动2台MySQL库（单、多实例均可）。

（2）配置my.cnf文件（配置参数后要重启生效）。主库配置bin-log和server-id参数，从库配置server-id，从库除了再做备份或的主从的主库外，一般不会开启bin-log。

（3）在主库上增加用于从库连接的帐户，并授replication slave的权限。

（4）在主库，整库锁表（窗口关闭后就失效，超时参数到了也失效）。通过showmaster status查看binlog的位置状态。

（5）在主库新开窗口进行备份，并将备份拷贝到从库。如果数据量很大且允许停机，则可以停机打包，而不用mysqldump。

（6）解锁主库：unlock tables;

（7）在从库进行还原操作。

（8）根据主库的show master status查看binlog的位置状态，在从库执行change master to语句

（9）在从库开启同步开关：start slave;

（10）在从库检查同步状态：show slave status\G; 并在主库进行更新测试。

7、主从复制原理要点

（1）异步方式同步

（2）逻辑同步模式（binlog有三种模式：SQL、混合、rowlevel），默认是通过SQL语句执行

（3）主库通过记录binlog实现对从库的同步

（4）主库1个线程（IO线程），从库2个线程（IO和SQL）来完成

（5）从库的关键文件：master.info、relay-log、relay-info

（6）如果从库还想级联从库，需打开log-bin和log-slave-updates参数

8、生产场景快速配置MySQL主从复制的方案

（1）安装好要配置从库的数据库，配置好log-bin和server-id参数。

（2）无需配置主库的my.cnf文件，主库的log-bin和server-id参数默认就是配置好的。

（3）登陆主库增加用于从库连接的帐户，并授replication slave的权限。

（4）半夜使用mysqldump带--master-data=1参数全备主库，并在从库进行恢复。

（5）在从库执行change master to 语句，无需binlog文件及对应位置点。

（6）在从库开启同步开关start slave。

（7）从库show slave status\G，检查同步状态，并在主库进行更新测试。

9、生产场景主从库同步配置注意事项

（1）主从库服务器的配置差距不要太大。

（2）撰写方案文档和实施步骤。如可能需要停机维护，需要事先申请停机维护时间。

10、查看主从同步的3个线程状态信息

（1）在主库查看IO线程信息

mysql> show processlist;

+----+------+-------------------+------+-------------+-------+-------------------

| Id | User | Host              | db   | Command     | Time | State    | Info             |

+----+------+-------------------+------+-------------+-------+------------------

| 2 | rep |192.168.1.2:13798|NULL|BinlogDump| 84015 | Masterhas sent all binlog to slave; waiting for binlog to be updated |NULL             |

| 3 | root | localhost | NULL | Query |  0 | NULL          | showprocesslist |

+----+------+-------------------+------+-------------+-------+------------------

（2）在从库查看IO和SQL线程信息

mysql> show processlist;

+----+-------------+-----------+------+---------+--------+-------------------

| Id | User    | Host  | db  | Command | Time   | State        | Info             |

+----+-------------+-----------+------+---------+--------+-------------------

|6|system user| |NULL|Connect|172446| Waiting for masterto send event |NULL|

|7| system user|  | NULL | Connect |  84468 | Slave has read all relay log; waiting for the slave I/Othread to update it | NULL             |

|8| root | localhost | NULL | Query   |     0 | NULL     show processlist |

+----+-------------+-----------+------+---------+--------+-------------------

（3）主库IO线程常见状态

主库的IO线程（Command列为BinlogDump）的State列常见有以下4种状态，如果没有看见任何Binlog Dump线程，这说明在主库上的复制没有运行。目前也没有连接任何从服务器。

a、Sending binlog eventto slave

表示线程已经从二进制日志读取了一个事件并且正将它发送到从库。

b、Finished readingon binlog; switching to next binlog

表示线程已经读完二进制文件，并且正打开下一个要发送到从库的日志文件

c、Has sent allbinlog to slave;waiting for binlog to be updated

表示线程已从二进制日志读取了所有主要的更新并已发送到从库。线程正在空闲，等待主库上新的更新日志。

d、Waiting tofinalize termination

表示线程停止时发生的一个很简单的状态。

（4）从库IO线程常见状态

从库IO线程的State列常见有下面5种状态，该状态也出现在Slave_IO_State列，由show slave status显示。

a、Connecting tomaster

表示线程正试图连接主库。

b、Checking masterversion

表示线程检查主库版本。

c、Registering slaveon master

表示线程在主库注册

d、Requesting binlogdump

表示线程请示binlog dump。线程向主库发送一条请求，索取从请求的二进制日志文件名和位置开始的日志内容。

e、Waiting toreconnect after a failed binlog dump request

表示线程请求失败，进入睡眠状态，等待请求重连。可以通过--master-connect-retry选项指定重试的间隔。

f、Reconnectingafter a failed binlog dump request

表示线程正在请求重连主库。

g、Waiting formaster to send event

表示线程等待主库发送日志。

h、Queueing masterevent to the relay log

表示线程已读取到一个日志，正将其复制到中继日志供SQL线程来处理。

i、Waiting reconnectafter a failed master event read

表示线程读取时，由于没有连接而出现错误，线程将睡眠指定的秒数。

j、Reconnectingafter a failed master event read

表示线程正在尝试重连主库，当连上后状态将变为Waiting for master to send event。

k、Waiting for theslave SQL thread to free enough relay log space

表示线程正使用一个非零relay_log_space_limit值，中继日志已增长到其指定大小，IO线程正等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放空间。

l、Waiting for slavemutex on exit

表示线程停止时发生的一个很简单的状态。

（5）从库SQL线程常见状态

a、Reading eventfrom the relay log

表示线程已经从中继日志读取了一个事件，可以对事件进行处理了。

b、Has read allrelay log; waiting for the slave I/O thread to update it

表示线程已经处理了中继日志文件中所有事件，正等待IO线程 将新事件写入中继日志。

c、Waiting for slavemutex on exit

表示线程停止时发生的一个很简单的状态。

（6）查看线程状态信息的作用

主要是通过MySQL线程同步状态来查看数据库同步是否完成，用于主库宕机或人工数据库主从切换迁移等。主库宕机选择最快的从库提升为主，就需要查看，当然也可以利用MySQL的半同步功能，选择固定的库提升为主。

三、MySQL主从复制实战

1、演示环境

Host	OS	MySQL Version	Port	Path	Master/Slave
192.168.1.2	CentOS6.5_32	5.5.32	3306	/data/3306	Master
192.168.1.2	CentOS6.5_32	5.5.32	3307	/data/3307	Slave

2、配置Master的my.cnf，打开binlog（一定要在mysqld模块下）

vi /data/3306/my.cnf

log-bin =/data/3306/mysql-bin

3、确保每个服务器的server-id不一样

grep "server-id" /data/3306/my.cnf

server-id = 1

grep "server-id" /data/3307/my.cnf

server-id = 3

4、确认Master上的binlog已打开并生效

（1）方法一：

ll /data/3306/

-rw-rw---- 1mysql mysql  107 Apr 16 15:48mysql-bin.000003

-rw-rw---- 1mysql mysql   28 Apr 16 15:48mysql-bin.index

（2）方法二：

mysql -uroot -p'123456' -S /data/3306/mysql.sock -e "showvariables like 'log_bin';"

+---------------+-------+

| Variable_name| Value |

+---------------+-------+

| log_bin       | ON   |

+---------------+-------+

5、在Master上创建用于从库访问的账户

mysql -uroot -p'123456' -S /data/3306/mysql.sock

grant replication slave on *.* to 'rep'@'%' identified by 'test123';

flush privileges;

6、在Master上对数据库进行全备

（1）官方文档给出的方法：

a、给主库加上读锁（备份结束前，该会话不能关闭，否则binlog的文件名或位置点有可能会变化）

MySQL5.5：

flush table withread lock; 

MySQL5.1：

flush tables with read lock;

同时，这个锁表命令的时间，在不同的引擎，会由下面的参数控制。在锁表后，如果超过设置的时间不操作，将会自动解锁

interactive_timeout=60

wait_timeout=60

 

mysql> showvariables like '%timeout%';

+----------------------------+----------+

|Variable_name              | Value    |

+----------------------------+----------+

|connect_timeout            | 10       |

|delayed_insert_timeout     | 300      |

|innodb_lock_wait_timeout   | 120      |

|innodb_rollback_on_timeout | OFF      |

|interactive_timeout        | 28800    |

|lock_wait_timeout          | 31536000 |

|net_read_timeout           | 30       |

|net_write_timeout          | 60       |

|slave_net_timeout          | 3600     |

|wait_timeout               | 28800    |

+----------------------------+----------+

b、查看Master上当前binlog信息（当前文件名和位置点）

show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

|mysql-bin.000003 |      324 |              |                  |

+------------------+----------+--------------+------------------+

show master logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

|mysql-bin.000003 |       324 |

+------------------+-----------+

c、新开一个窗口进行全库备份

mysqldump -uroot -p'123456' -S /data/3306/mysql.sock -A -B --events> /mnt/3306_all.sql

d、解锁

unlock tables;

（2）简便方法：

在备份命令中使用-x参数自动加锁、解锁，使用--master-data参数在备份文件中自动添加当前binlog文件名和位置点。

--master-data=1时，binlog文件名和位置点直接写在备份文件中，也没有被注释，所在以恢复的时候会自动应用binlog文件

--master-data=2时，binlog文件名和位置点在备份文件中是被注释状态，可以查看后，将相应数据写入master.info文件中。

a、操作命令

mysqldump -uroot -p'123456' -S /data/3306/mysql.sock -A -B --events-x --master-data=2 > /mnt/3306_all.sql

b、查看3306_all.sql

。。。

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=324;

。。。

7、将Master上的全备文件传给Slave（测试，同一台主机省略传送）

8、在Slave上恢复

mysql -uroot -p'123456' -S /data/3307/mysql.sock </mnt/3306_all.sql

9、在Slave上配置master.info

（1）登陆Slave上的MySQL

mysql -uroot -p'123456' -S /data/3307/mysql.sock

（2）配置master.info

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.2',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='test123',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=324;

（3）查看生成的master.info（Slave上MySQL的data目录下）

cat /data/3307/data/master.info

18

mysql-bin.000003

324

192.168.1.2

rep

test123

3306

60    #出问题后的重试秒数。

0

 

 

 

 

 

0

1800.000

 

0

10、在Slave上的MySQL中启动主从复制

mysql> start slave;

11、在Slave上检查主从复制是否开启成功

（1）查看Slave上的IO和SQL线程是否运行

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State:Waiting for master to send event

                  Master_Host:192.168.158.228

                  Master_User:rep

                  Master_Port:3306

                Connect_Retry: 60

              Master_Log_File:mysql-bin.000003

          Read_Master_Log_Pos:324

               Relay_Log_File:relay-bin.000002

                Relay_Log_Pos:253

        Relay_Master_Log_File:mysql-bin.000003

             Slave_IO_Running:Yes

            Slave_SQL_Running:Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:mysql

           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:324

              Relay_Log_Space:403

              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

（2）相关参数说明

Slave_IO_Running：Slave上的IO线程是否运行

Slave_SQL_Running：Slave上的SQL线程是否运行

Seconds_Behind_Master：表示落后Master的秒数，0表示不落后（正常）

（3）查看Slave上的relay-log是否生成

ll /data/3307/relay-bin*

-rw-rw---- 1mysql mysql 150 Apr 16 17:38 /data/3307/relay-bin.000001

-rw-rw---- 1mysql mysql 253 Apr 16 17:38 /data/3307/relay-bin.000002

-rw-rw---- 1mysql mysql  56 Apr 16 17:38/data/3307/relay-bin.index

12、测试在Master上的更新操作是否能同步到Slave上

（1）在Master上创建数据库mtosdb

create database mtosdb;

（2）在Slave上查看数据库

show databases;

+--------------------+

| Database           |

+--------------------+

|information_schema |

| d3306              |

| d3307              |

| mtosdb             |

| mysql              |

|performance_schema |

| test               |

+--------------------+

（3）在Slave上查看relay-log

ls /data/3307/relay-bin.0*

/data/3307/relay-bin.000001  /data/3307/relay-bin.000002

mysqlbinlog relay-bin.000002

...

# at 253

#170416 17:49:56server id 1  end_log_pos 411   Query  thread_id=5     exec_time=0     error_code=0

SETTIMESTAMP=1492336196/*!*/;

SET@@session.pseudo_thread_id=5/*!*/;

SET@@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,@@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET@@session.sql_mode=0/*!*/;

SET@@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8*//*!*/;

SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET@@session.lc_time_names=0/*!*/;

SET@@session.collation_database=DEFAULT/*!*/;

create database mtosdb

/*!*/;

...

（4）在Slave上查看relay-bin.index

cat relay-bin.index

/data/3307/relay-bin.000001

/data/3307/relay-bin.000002

（5）在Slave上查看relay-log.info

cat relay-log.info

/data/3307/relay-bin.000002   #SQL线程向数据写时，读的relay-log的文件名

340                                   #SQL线程向数据写时，读的relay-log的位置点

mysql-bin.000003                 #IO线程获取Master的binlog文件名

411                               #IO线程获取Master的binlog的位置点

四、MySQL主从复制实战故障案例

1、故障1：主库MySQL命令行下执行show master stauts没结果

（1）现象

mysql> show master status;

Empty set (0.00sec)

（2）原因

主库的binlog功能开关没开或没生效。

（3）排查

a、查看配置文件是否配置

egrep "server-id|log-bin" /data/3306/my.cnf

log-bin =/data/3306/mysql-bin#注意是中划线

server-id = 1    #注意是中划线

b、重启MySQL

/data/3306/mysqlrestart

c、进入MySQL命令行，查看配置是否生效

show variableslike 'server_id';   #注意是下划线

+---------------+-------+

| Variable_name| Value |

+---------------+-------+

| server_id     | 1    |

+---------------+-------+

mysql> showvariables like 'log_bin';  #注意是下划线

+---------------+-------+

| Variable_name| Value |

+---------------+-------+

| log_bin       | ON   |

+---------------+-------+

d、提示

配置文件my.cnf中的参数和showvariables里的参数是不一样的，配置文件中的参数是中划线，show variables里的参数是下划线。

2、故障1：从库报Last_IO_Error错

（1）现象

Error: Last_IO_Error: Got fatal error 1236 from master when readingdata from binary log:'Could not find first log file name in binary log indexfile'

（2）原因

CHANGE MASTER时，MASTER_LOG_FILE='mysql-bin.000003 '的文件名前后有空格，导至在查找不到文件。

（3）排查

检查从库上的master.info文件内容是否有错误，在MySQL命令行下，调用以前的配置命令，看是否有错。

cat /data/3307/data/master.info

18

 mysql-bin.000003

324

192.168.1.2

rep

test123

3306

60  

0

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.2',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='test123',

MASTER_LOG_FILE=' mysql-bin.000003 ',

MASTER_LOG_POS=324;

3、故障3：多实例数据库无法启动

（1）现象

多实现数据库在数据库进程已kill扣，通过/data/3306/mysql strart启动时，显示mysql is running，无法正常启动

（2）原因

是自定义的mysql启动脚本的bug，在kill所有mysql进程后，但相关的pid文件mysqld.pid没有删除，导到启动时，检查到有pid文件，认为mysql已启动

（3）排查

检查pid文件是否存在

ll /data/3306/*.pid

-rw-rw---- 1mysql mysql 5 Mar 29 17:05 /data/3306/mysqld.pid

删除pid文件

rm -f /data/3306/mysqld.pid

4、故障4：主库锁表备份时没锁住，导致备份时，数据被修改，binlog文件名或位置点被改变，备份失效

（1）现象

主库锁表备份时没锁住，导致备份时，数据被修改，binlog文件名或位置点被改变，备份失效

（2）原因

MySQL在锁表后，锁表命令的时间，在不同的引擎，会主要由参数interactive_timeout和wait_timeout

控制。在锁表后，如果超过设置的时间不操作，将会自动解锁

mysql> showvariables like '%timeout%';

+----------------------------+----------+

|Variable_name              | Value    |

+----------------------------+----------+

|connect_timeout            | 10       |

|delayed_insert_timeout     | 300      |

|innodb_lock_wait_timeout   | 120      |

| innodb_rollback_on_timeout| OFF      |

| interactive_timeout       | 28800    |

|lock_wait_timeout          | 31536000 |

|net_read_timeout           | 30       |

|net_write_timeout          | 60       |

|slave_net_timeout          | 3600     |

| wait_timeout              | 28800    |

+----------------------------+----------+

 

（3）故障模拟

a、使用默认时间（28800秒=8小时）

（i）锁表

flush table withread lock; 

（ii）新开一窗口，执行建库语句

create databasetest001;

（iii）命令一直处于等待状态

b、将锁表超时时间改为10秒

（i）设置超时参数

mysql> setglobal interactive_timeout=10;

mysql> setglobal wait_timeout=10;

（ii）检查是否生效（发现未生效）

mysql> showvariables like '%timeout%';

+----------------------------+----------+

|Variable_name              | Value    |

+----------------------------+----------+

| connect_timeout            | 10       |

|delayed_insert_timeout     | 300      |

|innodb_lock_wait_timeout   | 120      |

|innodb_rollback_on_timeout | OFF      |

| interactive_timeout        | 28800    |

|lock_wait_timeout          | 31536000 |

| net_read_timeout           | 30       |

|net_write_timeout          | 60       |

|slave_net_timeout          | 3600     |

| wait_timeout               | 28800    |

+----------------------------+----------+

（iii）退出，重新登陆MySQL

mysql> exit

mysql -uroot -p123456-S /data/3306/mysql.sock

（iv）检查是否生效（已生效）

mysql> showvariables like '%timeout%';

+----------------------------+----------+

|Variable_name              | Value    |

+----------------------------+----------+

|connect_timeout            | 10       |

| delayed_insert_timeout     | 300     |

|innodb_lock_wait_timeout   | 120      |

|innodb_rollback_on_timeout | OFF      |

| interactive_timeout        | 10      |

|lock_wait_timeout          | 31536000 |

|net_read_timeout           | 30       |

| net_write_timeout          | 60       |

|slave_net_timeout          | 3600     |

| wait_timeout               | 10       |

+----------------------------+----------+

（v）锁表

flush table withread lock; 

（vi）新开一窗口，执行建库语句

create databasetest001;

（vii）命令等待7秒后，自动解锁，执行成功

mysql> createdatabase test001;

Query OK, 1 rowaffected (7.31 sec)

5、故障5：从库数据冲突导致同步停止

（1）现象

从库的show slave status;报错，IO线程还在工作：Slave_IO_Running:Yes，但SQL线程已经停止：Slave_SQL_Running：NO。从库还在不停获取日志，但已无法将数据写入数据库，导致从库数据陈旧，主从库数据不一致。

（2）原因

由于从库数据中已有主库同步过来的数据，产生冲突，同步的数据无法写入从库，导入SQL线程报错，停止运行。

（3）故障模拟

a、在从库创建新的数据库abc

create databaseabc;

b、查看从库状态，正常

show slavestatus\G;

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

a、在主库创建新的数据库abc

create databaseabc;

d、再次查看从库状态，报错

show slavestatus\G;

Slave_IO_Running:Yes

Slave_SQL_Running:No

Last_Errno: 1007

Last_Error:Error 'Can't create database 'abc'; database exists' on query. Defaultdatabase: 'abc'. Query: 'create database abc'

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno:1007

Last_SQL_Error:Error 'Can't create database 'abc'; database exists' on query. Defaultdatabase: 'abc'. Query: 'create database abc'

（4）解决办法

a、方法一：忽略错误，让从库SQL线程执行操作的指针向下走一步，跳过该操作

（i）操作命令

mysql> stopslave;

mysql> setglobal sql_slave_skip_counter=1; #n>0,表示忽略执行n个更新，本次n=1就行了。

mysql> startslave;

（ii）查看从库状态，正常

show slavestatus\G;

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

（iii）应用场景

对于普通的互联网业务，忽略问题不是很大，当然，要确认在不影响公司业务的前提下。

如果是主从同步要更重要一些，首先是解决主从同步，然后是主从不一致，主从不一致找一个时间点恢复一下从库就行了。如果是数据不一致更重要的话，那就是保持主从同步的持续状态更重要。这个要根据业务进行选择。

（iv）查看从库状态的关键点参数

Slave_IO_Running：Yes

Slave_SQL_Running：Yes

Seconds_Behind_Master：是否为0。0表示已经是同步状态

（v）globalsql_slave_skip_counter参数说明：

set globalsql_slave_skip_counter=n; n的取值>0,表示忽略执行n个更新。

set globalsql_slave_skip_counter=1032,1062,1007;表示根据错误号跳过指定的错误。一般情况下由于入库重复导致的失败就可以忽略。错

也可以在配置文件my.cnf中设置slave-skip-errors=1032,1062,1007来忽略指定的错误。

 

 

 

 

 

 

 

 

 

五、快速备份和恢复脚本

1、一键备份脚本

vi /server/scripts/mysql_bak.sh

#!/bin/sh

MYUSER=root

MYPASS='123456'

MYSOCK=/data/3306/mysql.sock

MAIN_PATH=/server/backup

DATA_PATH=/server/backup

 

LOG_FILE=${DATA_PATH}/mysqllogs_`date+%F`.log

DATA_FILE=${DATA_PATH}/mysql_backup_`date+%F`.sql.gz

MYSQL_PATH=/application/mysql/bin

MYSQL_CMD="$MYSQL_PATH/mysql-u$MYUSER -p$MYPASS -S $MYSOCK"

MYSQL_DUMP="$MYSQL_PATH/mysql-u$MYUSER -p$MYPASS -S $MYSOCK -A -B --master-data=1 --single-transaction -e"

${MYSQL_DUMP} |gzip > $DATA_FILE

2、不停主库的一键批量创建从库脚本

vi /server/scripts/mysql_create_slave.sh

#!/bin/sh

MYUSER=root

MYPASS='123456'

MYSOCK=/data/3306/mysql.sock

MAIN_PATH=/server/backup

DATA_PATH=/server/backup

 

LOG_FILE=${DATA_PATH}/mysqllogs_`date+%F`.log

DATA_FILE=${DATA_PATH}/mysql_backup_`date+%F`.sql.gz

MYSQL_PATH=/application/mysql/bin

MYSQL_CMD="$MYSQL_PATH/mysql-u$MYUSER -p$MYPASS -S $MYSOCK"

#Recover

cd ${DATA_PATH}

gzip -d mysql_backup_`date+%F`.sql.gz

$MYSQL_CMD <mysql_backup_`date +%F`.sql

 

#Config Slave

cat |$MYSQL_CMD<< EOF

CHANGE MASTER TO

MASTER_HOST='192.168.1.2',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='test123',

EOF

$MYSQL_CMD -e"start slave;"

$MYSQL_CMD -e"show slave status\G" | egrep "IO_Running|SQL_Running" >$LOG_FILE

mail -s"mysql slave result" aaa@abc.com< LOG_FILE

六、生产场景实战之一：MySQL主从复制写分离授权

1、生产场景主从复制读写分离授权方案

（1）方案一：主从同用户不同权限

主库：WEB应用用户访问，授Select、Insert、Delete、Update权限。

从库：主库的WEB应用用户同步到从库后，回收Insert、Delete、Update权限。也可以不回收用户权限，但将从库设置为read-only，确保从库只读。

（2）方案二：主从不同用户不同权限

主库：WEB应用用户1访问，授Select、Insert、Delete、Update权限。

从库：用户2访问，授Select权限。

风险：由于是主从同步，WEB应用用户1也同步到从库，有可能开发人员用用户1连接从库。开发人员要使用多套密码登陆， 不专业。

（3）方案三：不同步mysql等系统库或相关系统表（推荐方案）

在主从同步的时候通过指定参数，不同步mysql等系统库或用户表，主从库分别建立同户并授不同权限。

主库：WEB应用用户访问，授Select、Insert、Delete、Update权限。

从库：创建与主库相同的WEB应用用户后，授Select权限。

风险：在从库切换为主库的时候，由于用户权限不同，存在连接用户权限的问题。

解决办法：使用一主多从方式，并专门保留一个从库为接替主库做准备，用户权限与主库一样。（百度作法）

2、忽略授权表指定同步/不同步库或表的方式实现主从同步（针对方案三）

（1）在主库的配置文件my.cnf中配置binlog-ignore-db参数，指定不需要同步的数据库。

vi /data/3306/my.cnf

replicate-ignore-db=mysql

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=information_schema

（2）重启主库

/data/3306/mysql restart

（3）在主库创建新的用户和数据库，

create user test002@'%' identified by '123456';

create database test003;

（4）检查是否同步到从库

select name,host from mysql.user;

show databases;

发现用户没有同步到从库，但数据库已同步到从库。说明设置成功，指定的mysql库没有同步。

（5）说明

网上有贴子说（网址：http://www.linuxidc.com/Linux/2012-09/71431.htm）：mysql master使用了binlog_ignore_db一个库以后，使用mysql -e 执行的所有语句就不写binlog了？询问了他的情况，他是想在主从复制时，有一个库不复制，查了他的my.cnf配置，binlog格式化为row，跟他要了当时的语句，如下：mysql-e "create table db.tb like db.tb1"。到底是什么原因引起的呢？那就是没有使用use 库名导致的，如果使用了，就可以记录binlog，所以，如果想在Slave上忽略一个库的复制，最好不要用binlog_ignore_db这个参数，使用replicate-ignore-db = dbname取代之。

（6）忽略授权表相关参数说明

a、Master端参数：

--binlog-do-db：需要在日志中记录的数据库，多个数据库用逗号","分隔

--binlog-ignore-db：在日志中需忽略的，不需要记录的数据库，多个数据库用逗号","分隔

b、Slave端参数：

--replication-do-db：设定需要复制的数据库，多个数据库用逗号","分隔

--replication-ignore-db：设定忽略的数据库，多个数据库用逗号","分隔

--replication-do-table：设定需要复制的表，多个表用逗号","分隔

--replication-ignore-table：设定忽略的表，多个表用逗号","分隔

--replication-wild-do-table：同--replication-do-table，但可使用通配符

--replication-wild-ignore-table：同--replication-ignore-table，但可使用通配符

3、通过read-only参数防止数据写从库的方法（针对方案一）

除了在从库仅做select授权外，还可能在从库上的配置文件my.cnf或MySQL启动选项上加read-only参数来确保从库只读。当然授权用户和read-only参数同时使用效果更佳，这是生产环境中使用的方案。

read-only参数可以让slave从库中允许来自从库服务器线程或具有SUPER权限的用户（如root）更新。可以确保从库服务器不接收来自普通用户的更新。

（1）方法一：启动时，增加read-only参数

/data/3307/mysql start

mysql> set global read_only=1;  #0为读写

（2）方法二：在配置文件my.cnf中增加read-only参数

[mysqld]

read-only

（3）实战演示

a、修改从库3307配置文件

vi /data/3307/my.cnf

[mysqld]

read-only

b、重启从库3307

/data/3307/mysql stop

/data/3307/mysql start

c、登陆数据库后查看主从库中的数据库，发现主从库均为mtosdb库

show databases;

+--------------------+

| Database           |

+--------------------+

| mtosdb             |

| mysql              |

+--------------------+

d、在主库删除mtosdb库

drop database mtosdb;

e、查看从库，mtosdb库已删除，说明主从复制没有问题

f、在从库创建表并插入记录，插入成功，说明read-only参数对Super用户无效

use test001

Database changed

show tables;

Empty set (0.00sec)

create table t (id int);

Query OK, 0 rowsaffected (0.00 sec)

insert into t values(1);

Query OK, 1 rowaffected (0.00 sec)

g、在从库创建新用户

grant select,insert,update,delete on *.* to 't1'@'localhost'identified by '123456';

h、刷新权限

flush privileges;

i、换t1用户连接从库

mysql -ut1 -p'123456' -S /data/3307/mysql.sock

j、查看当前连接用户

select user();

+--------------+

| user()       |

+--------------+

| t1@localhost |

+--------------+

k、在从库向t表插入记录，插入失败，说明read-only参数对普通用户有效

use test001

Database changed

insert into t values(2);

ERROR 1290(HY000): The MySQL server is running with the --read-only option so it cannotexecute this statement

（4）结论

a、从库read-only，不会影响主从同步。

b、在MySQL5.1.62、5.5.32版本中

（i）my.cnf中加入read-only后，需重启服务参数才能生效

（ii）root用户及all权限用户可以插入、删除数据，不受read-only参数影响

（iii）只具备insert、update、delete等权限的用户有受read-only参数影响

c、在早期5.0.56版本中

（i）my.cnf中加入read-only后，需重启服务参数才能生效

（ii）对用户授权时，不能指定有super或all privileges权限

七、生产场景实战之二：MySQL从库记录BinLog

1、从库需记录binlog的情况

（1）当前的从库还要做为其它从库的主库

（2）把从库做为备份服务器时，需开启binlog

2、在从库配置文件my.cnf中设置binlog相关参数

（1）配置参数

vi /data/3307/my.cnf

log-bin=/data/3307/mysql-bin

log-slave-updates

expire_logs_days=7

（2）参数说明

log-bin：开启binlog记录

log-slave-updates：用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的

expire_logs_days：binlog日志过期天数，超过后自动删除

（3）重新从库，设置生效

八、生产场景实战之三：MySQL主从复制（一主多从）主库宕机后主从切换

1、现象：

主库Master宕机。分为数据库宕机和服务器宕机2种，不管哪种都需要进行主从切换。

2、登陆从库检查IO线程和SQL线程状态

mysql> show processlist\G;

***************************1. row ***************************

     Id: 1

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 32

  State: Slave has read all relay log; waiting forthe slave I/O thread to update it

   Info: NULL

***************************2. row ***************************

     Id: 2

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 32

  State: Waiting for master to send event

   Info: NULL

说明：显示如下，说明从库日志已同步完成，处于等待日志传过来的状态

State: Slave has readall relay log; waiting for the slave I/O thread to update it

State: Waiting for master to sendevent

3、登陆所有从库检查master.info信息，查看哪个从库应用的日志最新，就选哪个从库切换到主库（或利用半同步功能，直接选择做了实时同步的从库为主库）

cat /data/3307/data/master.info

cat /data/3308/data/master.info

4、确保所有relay log全部更新完毕

如果主库只是数据库宕机，服务器还在运行，则可以把binlog拉取到从库进行应用，补全需提升为主库的从库。

在每个从库上执行stop slave io_thread;showprocesslist;命令，直到看到有Slave has read all relay log;出现才表示从库更新都执行完毕。

5、登陆要切换为主库的从库，进行切换操作

mysql -uroot -p'123456' -S /data/3307/mysql.sock

stop slave;

retset master;

quit;

6、进入要切换的从库数据目录，删除master.info和relay-log.info文件，并检查授权表，read-only等参数

cd /data/3307/data

rm -f master.info relay-log.info

检查授权表，read-only等参数

7、修改配置文件，开启binlog，注释从库参数

vi /data/3307/my.cnf

#开启binlog

log-bin=/data/3307/mysql-bin

#注释log-slave-updates和read-only等参数

#log-slave-updates

#read-only

8、对同步用户进行提权，保证权限与主库用户权限一样

9、重启数据库，提升为主库

/data/3307/mysql restart

10、其它从库操作

（1）检查运行环境和用户

（2）停止从库，修改master信息

mysql -uroot -p -S /data/3308/mysql.sock

stop slave;

change master to master_host='192.168.1.3';  #如果不同步，就还指定位置点

（3）启动从库同步，查看同步状态

start slave;

show slave status\G;

11、修改Web程序的连接配置，、从原主库指向新主库

12、维护损坏的主库，完成后作为从库使用，或切换回来。

13、如果主库没有宕机，只是想按计划切换一下主库，就非常简单

（1）主库锁表

（2）登陆所有从库查看同步状态，是否完成同步

（3）其它按上面步骤进行切换。

九、生产场景实战之四：MySQL主从同步之从库宕机处理

1、现象：从库Slave宕机。

2、处理方法：重做Slave

3、操作步骤

（1）停止Slave

mysql> stop slave

（2）导入备份数据

mysql -uroot -p'123456' -S /data/3307/mysql.sock <bak_mysql2017.sql &

（3）配置master.info

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.2',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='test123',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=324;

（4）启动Slave

mysql> start slave;

（5）查看从库状态

mysql> show slave status\G

十、生产场景实战之五：MySQL主主（互为主从，多主设置方式一样）同步实战

1、解决主建自增长变量冲突

（1）Master1：自增列形成1、3、5...序列

auto_increment_increment = 2 #自增ID间隔为2，如1、3、5.。。

auto_increment_offset     =1  # ID的初始值

（2）Master2：自增列形成2、4、6...序列

auto_increment_increment = 2 

auto_increment_offset     =2 

（3）问题是ID是不连续的

如果Master1自增列到了1，3，5，则Master2则从6，8，10，Master1接着从11开始。

2、在所有服务器上my.cnf中设置互为主从参数

log-bin=/data/330{6,7}/mysql-bin

log-slave-updates                        #开启从库binlog日志

binlog-ignore-db=information_schema      #不记录binlog日志

binlog-ignore-db=mysql                   #不记录binlog日志

skip-slave-start                         #启动时忽略从库启动

replicate-same-server-id=0

3、实战演示

（1）模拟环境

Master1：/data/3306   192.168.1.3

Master2：/data/3307   192.168.1.3

（2）在Master1和Master2上开启binlog，并设置server-id值

a、Master1：

vi /data/3306/my.cnf

[mysqld]

auto_increment_increment= 2

auto_increment_offset     = 1

log-slave-updates

log-bin =mysql3306-bin

relay-log =/data/3306/relay3306-bin

expire_logs_days= 7

server-id = 11

b、Master2：

vi /data/3307/my.cnf

[mysqld]

auto_increment_increment= 2

auto_increment_offset     = 2

log-slave-updates

log-bin =mysql3307-bin

relay-log =/data/3307/relay3307-bin

expire_logs_days= 7

server-id = 22

c、重启Master1和Master2：

/data/3306/mysql restart

/data/3307/mysql restart

（3）在Master1上创建用于同步的帐号：rep

mysql -uroot -p -S /data/3306/mysql.sock

grant replication slave on *.* to 'rep'@'%' identified by 'test123';

（4）在Master1上锁表只读（窗口不能关闭）

flush table with read lock;

（5）在Master1上查看当前日志状态

show master status

（6）在Master1上新开一个窗口，备份数据库

mysqldump -uroot -p'123456' -S /data/3306/mysql.sock -A -B >/bak/bkall_3306.sql

（7）在Master1上解锁数据库，恢复可写

unlock tables;

（8）在Master2恢复数据库

mysql  -uroot -p -S/data/3307/mysql.sock < /bak/bkall_3306.sql

（9）登陆Master2，建立Master.info文件

mysql  -uroot -p -S/data/3307/mysql.sock

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.3',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='test123',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=324;

（10）在Master2启动同步

slave start

（11）在Master2检查同步状态

show processlist\G;

（13）登陆Master1，建立Master.info文件

mysql  -uroot -p -S/data/3306/mysql.sock

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.3',

MASTER_PORT=3307,

MASTER_USER='rep',

MASTER_PASSWORD='test123',

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=324;

（14）在Master1启动同步

slave start

（15）在Master1检查同步状态

show processlist\G;

（16）检验同步是否正常

a、在Master1上创建数据库test002

create database test002;

b、在Master1上的数据库test002中创建表student

use test002;

create tables student(

    id int(4) not nullauto_increment,

    name char(20) not null,

    primary key(id)

);

c、在Master1上向表student中插入数据

insert into student(name) values('a1');

insert into student(name) values('a2');

insert into student(name) values('a3');

d、在Master1上查看数据

select * from student;

e、在Master2上向表student中插入数据

insert into student(name) values('b1');

insert into student(name) values('b2');

insert into student(name) values('b3');

f、在Master2上查看数据

select * from student;