linux mysql停止主从_linux系统mysql主从复制（2）

1.什么是主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

2.主从复制的作用

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

3.主从复制的原理

从库I/O线程连接主库，请求主库中更新的bin-log。I/O线程接收到主库binlog dump 进程发来的更新之后，保存在本地relay-log中。

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

主从复制的前提

1)两台或两台以上的数据库实例

2)主库要开启二进制日志

3)主库要有复制用户

4)主库的server_id和从库不同

5)从库需要在开启复制功能前，要获取到主库之前的数据(主库备份，并且记录binlog当时位置)

6)从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos

7)从库要开启相关线程：IO、SQL

8)从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志

9)从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来

主从复制涉及到的文件和线程

主库：

1)主库binlog：记录主库发生过的修改事件

2)dump thread：给从库传送(TP)二进制日志线程

从库：

1)relay-log(中继日志)：存储所有主库TP过来的binlog事件

2)master.info：存储复制用户信息，上次请求到的主库binlog位置点

3)IO thread：接收主库发来的binlog日志，也是从库请求主库的线程

4)SQL thread：执行主库TP过来的日志

主从复制原理

1)通过change master to语句告诉从库主库的ip，port，user，password，file，pos

2)从库通过start slave命令开启复制必要的IO线程和SQL线程

3)从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性

4)从库连接成功后，会根据binlog的pos问主库，有没有比这个更新的

5)主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程

6)从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info

7)将TCP/IP缓存中的内容存到relay-log中

8)SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info

4.主从复制搭建

主库操作：

修改主库配置文件

[root@db01 ~]# vim /etc/my.cnf

[mysqld]

server_id = 1

log-bin=/usr/local/mysql/data/mysql-bin

binlog_format=row

#server-id：用来标识一个唯一的实例，要保证主库唯一

#log_bin：二进制日志文件的路径，mysql用户对该路径必须具有读写权限

#binlog_format：表示二进制日志内容的记录方式，row记录sql的执行过程

#重启mysql

[root@db01 ~]# /etc/init.d/mysqld restart

创建主从复制用户

#登录数据库

[root@db01 ~]# mysql -uroot -p123

#创建主从复制用户

mysql> grant replication slave on *.* to wyf@'10.0.0.%' identified by '123';

查看主库的二进制日志文件及位置点

mysql> show master status \G

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

File: mysql-bin.000007

Position: 317

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

全备主库数据拷贝到从库

[root@db01 ~]# mysqldump -uroot -p123 -A --single-transaction --master-data=2 > /backup/full.sql

[root@db01 ~]# scp /backup/full.sql 10.0.0.51:/tmp/full.sql

从库操作：

修改从库配置文件

[root@db02 ~]# vim /etc/my.cnf

[mysqld]

server_id =5

relay_log=/usr/local/mysql/data/relay-log

relay_log_index = /usr/local/mysql/data/relay-log-index

#server_id：主库server-id为1，从库不等于1

#relay_log：中继日志位置

#重启mysql

[root@db02 ~]# /etc/init.d/mysqld restart

将主库数据导入从库

[root@db02 ~]# mysql -uroot -p123

登录从库执行change master to 语句

[root@db02 ~]# mysql -uroot -p123

mysql> change master to

-> master_host='10.0.0.50',

-> master_port=3306,

-> master_user='wyf',

-> master_password='123',

-> master_log_file='mysql-bin.000007',

-> master_log_pos=317;

#master_host:主库的主机名或者IP地址

#master_port:主库的端口号，必须为整数，不能加引号，否则会提示错误

#master_user:在主库上添加的复制用户名称

#master_password:在主库上添加的复制用户密码

#master_log_file:主库当前的二进制日志文件名称

#master_log_pos:主库当前的二进制文件位置点，整数，不可加引号，否则会提示错误

启动主从同步

mysql> start slave;

查看同步状况

mysql> show slave status\G

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.50

Master_User: wyf

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 327

Relay_Log_File: db02-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

注意：上述结果中Slave_IO_Running和Slave_SQL_Running都为Yes表示主从同步成功，如果为Connecting...，可以等待一会再次查看，如果为No，表示同步失败；

开启主从的另外一种方法是分别开启SQL线程和IO线程，如下：

mysql> start slave IO_THREAD;

mysql> start slave SQL_THREAD;

5.主从复制基本故障处理

IO线程

从库的IO线程无法连接，通过"show slave status\G"可以查看到具体的错误信息

原因1：在主库上创建的用户授权错误，导致从库无法远程连接主库

解决办法1：在主库上通过"show grants for 'user'@'ip';"查看授权是否正确，如果错误，重新授权即可

原因2：可能网络问题，ping不通等，例如由于主库的防火墙拦截导致从库无法连接主库

解决办法2：关闭主库的防火墙，或者在主库所在服务器添加防火墙规则，允许从库的tcp连接

原因3：执行change master to语句时执行错误，比如主库ip，用户，密码，端口等。

解决方法：mysql> stop slave;停止主从复制，删除之前执行的语句mysql> reset slave all ;重新执行change master to语句。

SQL线程

在从库上执行了创建库或者表的操作，然后在主库上又执行了一遍，导致同步错误，如下：

Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'

原因：从库上创建了库，主库上再次创建，从库会将主库上的创建过程再次应用到从库，导致从库上创建同名的库，发生错误

解决办法：停止从库，然后设置sql_slave_skip_count，使其跳过同步主库创建库的操作，从下一个操作开始同步，如下：

方法一：

#临时停止从库同步

mysql> stop slave;

#将同步指针向下移动一个(可重复操作)

mysql> set global sql_slave_skip_counter=1;

#重新开启同步

mysql> start slave;

方法二：

#编辑配置文件

[root@db01 ~]# vim /etc/my.cnf

#在[mysqld]标签下添加以下参数

slave-skip-errors=1032,1062,1007

方法三：

给从库设置只读

#在命令行临时设置

set global read_only=1;

#在配置文件中永久生效

[root@db01 ~]# vim /etc/my.cnf

read_only=1

方法四：重新备份数据库，恢复到从库

6.延时从库

6.1 延时从库的作用

1.用来在数据库误操作后，快速的恢复数据。比如有人误操作的表，那么这个操作在延迟时间内，从库的数据并没有发生变化，可以用从库的数据进行快速恢复。

2.用来做延迟测试，比如你做好的数据库读写分离，把从库作为读库，那么你想知道当数据产生延迟的时候到底会发生什么。那么你就可以使用这个特性也模拟延迟。

3.用于老数据的查询等需求，比如你经常需要查看某天前一个表或者字段的数值，你可能需要把备份恢复后进行查看，如果有延迟从库，比如延迟一周，那么就可以解决这样类似的需求。

6.2 延时从库的设置方法

#停止主从复制

mysql>stop slave;

#设置延时为180秒。单位为多少秒，该语句设置从数据库延时N秒后，再与主数据库进行数据同步复制。

mysql>CHANGE MASTER TO MASTER_DELAY = 180;

#开启主从复制

mysql>start slave;

#查看状态

mysql> show slave status \G

SQL_Delay: 60

3.延时从库停止方法

#停止主从复制

mysql> stop slave;

#设置延时为0

mysql> CHANGE MASTER TO MASTER_DELAY = 0;

#开启主从复制

mysql> start slave;

6.3 利用延时从库恢复数据

模拟故障

先关闭从库延时：

mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_DELAY = 0;

mysql> start slave;

模拟主库数据：

mysql> create database aaa;

mysql> \u aaa

mysql> mysql> create table bbb(id int);

mysql> insert into bbb values(1),(2),(3);

开启从库延时500秒：

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 500;

mysql>start slave;

模拟主库产生数据并出现删库故障：

mysql> insert into bbb values(4),(5),(6);

mysql> drop database aaa;

从库关闭SQL线程：

mysql> stop slave sql_thread;

截取relay-log：

起点：

[root@db02 ~]# cd /usr/local/mysql/data/

[root@db02 data]# cat relay-log.info

mysql-bin.000008

592

终点：

[root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv db02-relay-bin.000002

# at 480

mysqlbinlog --start-position=283 --stop-position=480 /usr/local/mysql/data/db02-relay-bin.000002 >/tmp/relay.sql

将截取的relay-log传至主库

scp /tmp/relay.sql 172.16.1.50:/tmp/

把主库被删的库导出传至主库

mysqldump -uroot -p123 -B aaa -R --triggers --single-transaction>/tmp/aaa.sql

在主库上把从库导出的数据导入

mysql> source /tmp/aaa.sql

source /tmp/relay.sql

查看主库数据未丢失

mysql> select * from bbb;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

+------+

最后从库开启SQL线程即可

mysql> start slave sql_thread;

7.半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能， 主库把binlog日志发送给从库，这一动作就结束了，并不会验证从库是否接收完毕，这一过程，也就意味着有可能出现当主服务器或从服务器端发生故障的时候，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。

简介：

从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步(asynchronous)的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash(例如宕机)，这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。

半同步复制(Semi synchronous Replication)则一定程度上保证提交的事务已经传给了至少一个备库。

出发点是保证主从数据一致性问题，安全的考虑。

5.5 出现概念，但是不建议使用，性能太差

5.6出现group commit 组提交功能，来提升开启半同步复制的性能

5.7更加完善了，在group commit基础上出现了MGR

5.7的增强半同步复制的新特性：after commit； after sync；

半同步复制开启方法

主库操作：

#登录数据库

[root@db01 ~]# mysql -uroot -poldboy123

#查看是否有动态支持

mysql> show global variables like 'have_dynamic_loading';

#安装自带插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';

#启动插件

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

#设置超时

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

#修改配置文件

[root@db01 ~]# vim /etc/my.cnf

#在[mysqld]标签下添加如下内容(不用重启库)

[mysqld]

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000

检查安装：

mysql> show variables like'rpl%';

mysql> show global status like 'rpl_semi%';

从库操作：

#登录数据库

[root@mysql-db02 ~]# mysql -uroot -poldboy123

#安装slave半同步插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

#启动插件

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

#重启io线程使其生效

mysql> stop slave io_thread;

mysql> start slave io_thread;

#编辑配置文件(不需要重启数据库)

[root@mysql-db02 ~]# vim /etc/my.cnf

#在[mysqld]标签下添加如下内容

[mysqld]

rpl_semi_sync_slave_enabled =1

相关参数说明

rpl_semi_sync_master_timeout=milliseconds

设置此参数值(ms),为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。

rpl_semi_sync_master_wait_no_slave={ON|OFF}

如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。

可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。

测试半同步

#主库创建两个数据库，test1和test2

mysql> create database test1;

Query OK, 1 row affected (0.04 sec)

mysql> create database test2;

Query OK, 1 row affected (0.00 sec)

#查看复制状态

mysql> show global status like 'rpl_semi%';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 768 |

| Rpl_semi_sync_master_net_wait_time | 1497 |

| Rpl_semi_sync_master_net_waits | 2 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 884 |

| Rpl_semi_sync_master_tx_wait_time | 1769 |

| Rpl_semi_sync_master_tx_waits | 2 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

#此行显示2，表示刚才创建的两个库执行了半同步

| Rpl_semi_sync_master_yes_tx | 2 |

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

14 rows in set (0.06 sec)

#从库查看

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test1 |

| test2 |

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

#关闭半同步(1:开启 0:关闭)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;

#查看半同步状态

mysql> show global status like 'rpl_semi%';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 768 |

| Rpl_semi_sync_master_net_wait_time | 1497 |

| Rpl_semi_sync_master_net_waits | 2 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | OFF | #状态为关闭

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 884 |

| Rpl_semi_sync_master_tx_wait_time | 1769 |

| Rpl_semi_sync_master_tx_waits | 2 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 2 |

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

14 rows in set (0.00 sec)

#再一次创建两个库

mysql> create database test3;

Query OK, 1 row affected (0.00 sec)

mysql> create database test4;

Query OK, 1 row affected (0.00 sec)

#再一次查看半同步状态

mysql> show global status like 'rpl_semi%';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 768 |

| Rpl_semi_sync_master_net_wait_time | 1497 |

| Rpl_semi_sync_master_net_waits | 2 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | OFF |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 884 |

| Rpl_semi_sync_master_tx_wait_time | 1769 |

| Rpl_semi_sync_master_tx_waits | 2 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

#此行还是显示2，则证明，刚才的那两条并没有执行半同步否则应该是4

| Rpl_semi_sync_master_yes_tx | 2 |

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

14 rows in set (0.00 sec)

注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有

8.复制过滤

主库：

白名单:只记录白名单中列出的库的二进制日志

binlog-do-db

黑名单：不记录黑名单列出的库的二进制日志

binlog-ignore-db

从库：

白名单：只执行白名单中列出的库或者表的中继日志

replicate-do-db=test

replicate-do-table=test.t1#只执行test库中的t1表的中继日志

replicate-wild-do-table=test.t*#执行test库中的以t开头的表的中继日志

黑名单：不执行黑名单中列出的库或者表的中继日志

replicate-ignore-db

replicate-ignore-table

replicate-wild-ignore-table

配置复制过滤

[root@db02 ~]# vim /etc/my.cnf

#在[mysqld]标签下添加

replicate-do-db=test #从库只执行对test库的中继日志

#重启MySQL

[root@db02 ~]# /etc/init.d/mysqld restart