主从架构和复制演变（replication）

1、介绍

1.1 多实例创建

MySQL软件包　　1份

配置文件　　　　3份 ： /data/330{7..9}/my.cnf

数据目录　　　　3份 ： /data/330{7..9}

初始化数据　　　3份

日志目录　　　　3份 ： /binlog/330{7..9}

端口　　　　　　3份 ： port=3307,3308,3309

server_id　　 3份 ： server_id=7,8,9

socket　　 　　3份 ： /tmp/mysql330{7..9}.sock

2 配置过程

（1） 创建需要目录

mkdir -p /data/330{7..9}/data

mkdir -p /binlog/330{7..9}

（2） 创建配置文件

cat > /data/3307/my.cnf <<EOF

[mysqld]

basedir=/app/database/mysql

datadir=/data/3307/data

socket=/tmp/mysql3307.sock

log_error=/data/3307/mysql.log

port=3307

server_id=7

log_bin=/binlog/3307/mysql-bin

EOF

cat > /data/3308/my.cnf <<EOF

[mysqld]

basedir=/app/database/mysql

datadir=/data/3308/data

socket=/tmp/mysql3308.sock

log_error=/data/3308/mysql.log

port=3308

server_id=8

log_bin=/binlog/3308/mysql-bin

EOF

cat > /data/3309/my.cnf <<EOF

[mysqld]

basedir=/app/database/mysql

datadir=/data/3309/data

socket=/tmp/mysql3309.sock

log_error=/data/3309/mysql.log

port=3309

server_id=9

log_bin=/binlog/3309/mysql-bin

EOF

3 初始化数据

chown -R mysql.mysql /data /binlog

mv /etc/my.cnf /etc/my.cnf.bak

mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql

mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql

mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql

4 准备启动脚本

cat > /etc/systemd/system/mysqld3307.service <<EOF

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

LimitNOFILE = 5000

EOF

cat > /etc/systemd/system/mysqld3308.service <<EOF

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

LimitNOFILE = 5000

EOF

cat > /etc/systemd/system/mysqld3309.service <<EOF

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

LimitNOFILE = 5000

EOF

5 启动多实例

[root@master ~]# systemctl start mysqld3307

[root@master ~]# systemctl start mysqld3308

[root@master ~]# systemctl start mysqld3309

[root@master ~]# netstat -tulnp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1384/sshd

tcp6 0 0 :::3306 :::* LISTEN 21384/mysqld

tcp6 0 0 :::3307 :::* LISTEN 21775/mysqld

tcp6 0 0 :::3308 :::* LISTEN 21809/mysqld

tcp6 0 0 :::3309 :::* LISTEN 21843/mysqld

tcp6 0 0 :::22 :::* LISTEN 1384/sshd

1.介绍主从复制（replication）

1.两台或以上数据库实例,通过二进制日志，实现数据的同步关系。

2.主从复制前提。（搭建过程）

大家提到的:

# 时间同步

# 至少2台以上实例，要有不同角色还分，server_id

# 主从开启binlog

# 网络通畅

# 专门开启一个复制用户

# 开启专用复制线程。

# "补课"

# 确认复制起点

总结

#1.需要两台以上数据库实例，时间同步，网络通畅，Server_id不同，区分不同角色（主库，从库）

#2.主库开启binlog，建立专用复制用户

#3.从库需要提前"补课"

#4.从库:主库的链接信息，确认复制起点。

#5.从库:开启专用的复制线程。

3.搭建

3.1 实例准备

systemctl start mysqld3307

systemctl start mysqld3308

systemctl start mysqld3309

3.2 检查server_id

[root@localhost /]# mysql -S /tmp/mysql3307.sock -e "select @@server_id"

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

| @@server_id |

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

| 7 |

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

[root@localhost /]# mysql -S /tmp/mysql3308.sock -e "select @@server_id"

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

| @@server_id |

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

| 8 |

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

[root@localhost /]# mysql -S /tmp/mysql3309.sock -e "select @@server_id"

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

| @@server_id |

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

| 9 |

3.3 主库binlog

[root@localhost /]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin";

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

| @@log_bin |

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

| 1 |

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

3.4 主库建立复制用户

mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '666'";

mysql -S /tmp/mysql3307.sock -e "select user,host from mysql.user;"

3.5 主库复制恢复到从库

# mysqldump -S /tmp/mysql3307.sock -A --master-data=2 --single-transaction >/tmp/all.sql

# mysql -S /tmp/mysql3308.sock >/tmp/all.sql

# mysql -S /tmp/mysql3309.sock >/tmp/all.sql

3.6 告知从库复制消息

help change master to

CHANGE MASTER TO

MASTER_HOST='10.0.0.%',

MASTER_USER='repl',

MASTER_PASSWORD='666',

MASTER_PORT=3307,

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=444,

MASTER_CONNECT_RETRY=10;

[root@localhost ~]# grep "\-- CHANGE MASTER TO" /tmp/all.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=444;

过滤关键字

mysql -S /tmp/mysql3308.sock（从库）

mysql> CHANGE MASTER TO

-> MASTER_HOST='10.0.0.%',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='666',

-> MASTER_PORT=3307,

-> MASTER_LOG_FILE='mysql-bin.000002',

-> MASTER_LOG_POS=444,

-> MASTER_CONNECT_RETRY=10;

mysql -S /tmp/mysql3309.sock（从库）

mysql> CHANGE MASTER TO

-> MASTER_HOST='10.0.0.%',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='666',

-> MASTER_PORT=3307,

-> MASTER_LOG_FILE='mysql-bin.000002',

-> MASTER_LOG_POS=444,

-> MASTER_CONNECT_RETRY=10;

3.7 从库中开启专用复制线程

mysql -S /tmp/mysql3308.sock

start slave;

3.8 验证主从状态

mysql -S /tmp/mysql3308.sock -e "show slave status \G" |grep Running:slave_IO_Running:yes

mysql -S /tmp/mysql3309.sock -e "show slave status \G" |grep Running:slave_IO_Running:yes

3.9 如果搭建不成功，可以执行一下命令，从3.1-3，8步骤重新来过。

mysql -S /tmp/mysql3308.sock -e "stop slave;reset slave all;"

mysql -S /tmp/mysql3309.sock -e "stop slave;reset slave all;"

4.主从复制的原理

4.1 主从中涉及的资源

4.1.1 文件

（1）主库: binlog文件

（2）从库:

#relay-log文件:

作用:

存储接受的binlog

位置:

默认从库的数据目录下。

手工定义方法:

relay_log_basename=/data/3309/data/localhost-relay-bin

# master.info :

作用：

连接主库的信息，已经接收binlog位置点信息。

位置：

默认存储在从库的数据路径下。

手工定义：

master_info_repository=FILE/TABLE

# relay.info:

作用:

记录从库会放到relay-log的位置点。

位置:

默认在从库的数据路径下。

relay-log.info

手工定义:

relay_log_info_repository=FILE/TABLE

4.1.2 线程

（1）主库:

Binlog_dump_Thread:

作用:用来接收从库请求，并且投递binlog给从库。

mysql> show processlist;

（2）从库:

IO线程：请求binlog，接收binlog日志

SQL：回放relay日志。

4.2 主从复制原理

文字说明:

1.S:change master to IP,Port,user,binlog位置信息写入到M.info中，执行start slave（启动SQL，IO）。

2.S:连接主库

3.M:分配Dump_T,专门和S_IO通信。

show processlist;

4.S:IO线程:IO线程请求日志。

5.M:Dump_T 接收请求，截取日志，返回给S——IO

6.S:IO线程接收到binlog，日志放在TCP/IP，

此时网络层面返回ACK给主库。主库工作完成。

7.S:IO将binlog最终写入到relaylog中,并更新M.info。IO线程结束。

8.S:SQL线程读R.info,获取上次执行到的位置点。

9.S:SQL线程向后执行新的relay-log，在此更新R.info。

小细节:

1.S:realy-log 参数:relay log_purge=ON,定期删除应用过的relay-log

2.M:Dump线程实时监控主库中的binlog，如果有新变化，发信号给从库。

5.主从监控

5.1 主库方面

show processlist;

show slave hosts;

5.2 从库方面

主库相关信息，来自于M.info

Master_Host: 10.10.10.88

Master_User: repl

Master_Port: 3307

Connect_Retry: 10

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 1391

从库的relay-log的执行情况，来自于R.info,一般用做判断主从延时

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 495

Relay_Master_Log_File: mysql-bin.000004

Exec_Master_Log_Pos: 1391

Seconds_Behind_Master: 0

从库线程状态，具体报错信息

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

过滤复制相关信息

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

延时从库的配置信息

SQL_Delay: 0

SQL_Remaining_Delay: NULL

GTID相关复制信息

Retrieved_Gtid_Set:

Executed_Gtid_Set:

4.3主从故障分析及处理

4.3.1 监控方法

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

4.3.2 IO线程

正常状态:

Slave_IO_Running: Yes

4.3.3 非正常状态:

# (1) 网络,端口，防火墙

# (2) 用户，密码，授权

# (3) 主库连接数上限

max_connections | 151

# (4) 版本不统一 5.7native，8.0 sha2

2.故障模拟：

主从中的线程管理:

start slave; # 启动所有线程

stop slave; # 关闭所有线程

start slave sql_thread;单独启动SQL线程

start slave io_thread; 单独启动IO线程

stop slave sql_thread;

stop slave io_thread;

解除从库身份:

stop slave;

reset slave all;

5.请求日志，接收日志。

故障原因：

主库二进制日志不完整：损坏，不连续.....

从库请求的起点问题...

主从的server_id (server_uuid) 相同。

relaylog 问题

模拟故障：

主库：reset master；

生产中如果要 reset master;

1.找业务不繁忙期间，停业务5分钟。

2.等待从库重放完所有主库日志。

3.主库 reset master;

4.从库重新同步主库日志。

mysql> stop slave;

mysql> reset slave all;

CHANGE MASTER TO

MASTER_HOST='master2.example.com',

MASTER_USER='replication',

MASTER_PASSWORD='password',

MASTER_PORT=3306,

MASTER_LOG_FILE='master2-bin.001',

MASTER_LOG_POS=4,

MASTER_CONNECT_RETRY=10;

start slave;

模拟故障：

主库： reset master;

注意:切记不要在业务繁忙期间做，有可能会导致数据库hang。

如果要恢复主从。需要重新搭建主从。

第三方工具：pt工具

5.3 SQL现场故障

5.3.1 主要做什么工作？

回放relay-log中的日志。可以理解为执行relay-log SQL .

5.3.2 SQL线程故障本质？

为什么SQL线程执行不了的SQL语句。

5.3.3 原因整理。

创建的对象存在。

需要操作的对象不存在。

结束冲突。

以上问题：大几率出现在从库写入或者双主结构中容易出现。

参数，版本。

5.3.4 故障模拟:

(1) 先在从库 create database repl;

(2) 在主库 create database repl;

(3) 检查从库SQL线程状态

5.3.5 故障处理

(1)思路：一切以主库为准

将从库进行反操作一下。重启线程。

mysql>drop database repl;

mysql>start slave;

(2)思路2：以从库为准，跳过此次复制错误

方法一：

stop slave；

set global sql_slave_skip_counter = 1;

# 将同步指针向下移动一个，如果多次不同步，可以重复操作。

start slave;

方法二:暴力方法，遇到自动跳过。

/etc/my.cnf

slave-skip-errors = 1032，1062，1007

常见错误代码：

1007：对象已存在

1032：无法执行DML

1062：主键冲突，或约束冲突

（3）思路3：

重新搭建主从：备份恢复+ 重新构建。

拓展：

1.从库只读

mysql>select @@read_only; #普通用户只读。

mysql>select @@super_read_only # 普通管理员只读。

2.中间件

6.主从延时问题原因分析及处理

6.1 什么是主从延时？

主库发生了操作，从库"很久"才跟上来。

6.2 主从延时怎么监控？

show slave status \G

粗略:

Seconds_Behind_Master:0

准确:

日志量：主库binlog位置点 到 从relay执行得位置点。

6.3 如何计算延时的日志量

主:show master status;

从:cat /data/3308/data/relay-log.info

 

6.4 主从复制延时原因？

主库:

外部: 网络， 硬件配置，主库业务繁忙，从库太多。

主库业务繁忙:

1.拆分业务（分布式）:组件分离，垂直，水平。

2.大事务的拆分。比如，1000w业务 拆分为20次执行。

内部:

1.二进制日志更新问题:

解决方案:

sync_binlog=1

2.问题: 5.7之前的版本，没有开GTID之前，主库可以并发事务，但是dump传输时串行。所以会导致，事务量，大事务时会出现比较严重延时。

解决方案：

5.6+ 版本,手工开启GTID，事务在主从的全局范围内就有了唯一性标志。

5.7+ 版本，无需手工开启，系统会自动生成匿名的GTID信息

有了GTID之后，就可以实现并发传输binlog。

但是，即使有这么多的优秀特性，我们依然需要尽可能的减少大事务，以及锁影响。

6.5 怎么判断是主库传输不及时？

1.seconds_behind_master

2.主库:show master status; show slave status \G

从库:

   外部：网络，从库配置低，参数设定。

   内部：

        IO线程：

       写relay-log -->IO 性能。

        SQL线程：   回放SQL默认在GTID模式下是串行的。

 解决方案：

1.开启GTID

2.串行改并行

5.6+ GTID:database级别，基于库级别SQL线程并发。

5.7+ GTID: Logic clock 逻辑时钟。保证了同库级别下的事务顺序问题。所以可以理解为基于事务级别的并发回放。MTS。即使有以上的自带的优化机制，我们还是要注意对于大事务的处理的问题。锁的问题。