mysql主从复制 ack_Mysql主从复制

1.主从复制简介

复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。

1)主服务器将所有数据和结构更改记录到二进制日志中。

2)从属服务器从主服务器请求该二进制日志并在本地应用其内容。

3)IO：请求主库，获取上一次执行过的新的事件，并存放到relaylog

4)SQL：从relaylog中将sql语句翻译给从库执行

2.主从复制原理

1.主从复制的前提

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

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

3)主库要有复制用户

4)主库的server_id和从库不同

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

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

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

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

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

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

++主库：++

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

3.主从复制搭建实战

主库操作

修改主库的配置文件

vim /etc/my.cnf

#在[mysqld]标签下开启binlog和server_id

log_bin=mysql-bin

server_id =1

重启数据库(两种方式)后进入

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

[root@db01 ~]# systemctl restart mysqld

查看日志状态

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 | 120 | | | |

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

1 row in set (0.00 sec)

创建一个复制用户

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

Query OK, 0 rows affected (0.00 sec)

mysql> show master status; #binlog pos 变成了324

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 | 324 | | | |

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

1 row in set (0.00 sec)

从库操作

修改从库的配置文件

vim /etc/my.cnf

#在[mysqld]标签下开启server_id 从库server_id不可与主库相同，从库之间可以一致

server_id =2

重启数据库后进入执行change master to 语句

mysql> change master to

master_host='10.0.0.51', #主库ip

master_user='rep', #主从复制用户

master_password='123', #主从复制用户密码

master_log_file='mysql-bin.000003', #binlog

master_log_pos=324, #binlog 起始点

master_port=3306; #数据库端口

开启slave

mysql> start slave;

查看slave状态(yes开启成功)

mysql> show slave status\G

4.验证主从复制

查看主库上的库

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.00 sec)

创建一个新库zeq

mysql> create database zeq;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| zeq |

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

5 rows in set (0.00 sec)

查看从库

mysql> show databases; #从库上已经复制过来了

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| zeq |

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

5 rows in set (0.01 sec)

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

IO线程

++连接主库++

1)user password ip port

2)网络：不通，延时高，防火墙

++请求binlog++

1)binlog不存在或者损坏

++更新relay-log和master.info++

SQL线程

1)relay-log出现问题

2)从库做写入了

操作对象已存在(create)

操作对象不存在(insert update delete drop truncate alter)

约束问题、数据类型、列属性

处理方法一：

#临时停止同步

mysql> stop slave;

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

mysql> set global sql_slave_skip_counter=1;

#开启同步

mysql> start slave;

处理方法二：

#编辑配置文件

vim /etc/my.cnf

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

slave-skip-errors=1032,1062,1007

但是以上操作都是有风险存在的

处理方法三：

1)重新备份数据库，恢复到从库

2)给从库设置为只读

#在命令行临时设置

mysql> set global read_only=1;

#在配置文件中永久生效/etc/my.cnf

read_only=1

6.延时从库

企业中一般会延时3-6小时

延时从库配置方法

从库操作

#停止主从

mysql>stop slave;

#设置延时为180秒

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;

关闭半同步：

主库执行

SET GLOBAL rpl_semi_sync_master_enabled = 0;

SET GLOBAL rpl_semi_sync_master_wait_no_slave = 0;

从库执行：

SET GLOBAL rpl_semi_sync_slave_enabled = 0;