mysql主从复制sql_delay_十三、MySQL主从复制、延时从库、半同步复制、过滤复制、基于GTID的主从复制...

一、mysql主从复制

1.主从复制主库的操作

1.主库配置server_id

2.主库开启binlog

3.授权主从用户

4.主库查看binlog信息

2.主从复制从库的操作

1.配置server_id，与主库不同

2.先同步主库数据

3.配置主从

change master to

master_host=172.16.1.51,

master_user=rep,

master_password=123,

master_port=3306,

master_log_file=mysql-bin.000001,

master_log_pos=120;

4.开启主从

3.主从复制图解

4.文字描述

1.主库配置server_id和binlog

2.主库授权主从用户

3.主库查看binlog信息

4.从库配置server_id，只要跟主库不同就可以(只做主从的情况下)

5.从库执行change master to语句

6.从库开启IO和sql线程(start slave)

7.从库的IO线程会去连接主库的dump线程，问问是否有比从库中master.info记录的更新的位置点

8.主库dump线程被询问，dump线程会去查询binlog

9.dump线程会将查到的新数据从binlog中截取出来交给从库的IO线程

10.IO线程拿到新的binlog数据，会将内容存储到TCP/IP缓存中

11.TCP/IP缓存会返回给IO线程一个ACK

12.IO线程会更新master.info，将新的位置点更新到文件中，然后继续去主库获取数据

13.SQL线程会去读取relay-log.info中的位置点，去对比relay-log的数据，如果有比relay-log.info中的位置点新的数据，则取出并执行，如果没有则不执行

14.SQL线程执行完操作，会将新的位置点更新到relay-log.info中

5.主从复制涉及到的文件

1)主库

1.主库的binlog：记录的是数据库的操作

2.dump线程：对比binlog，取出新binlog中的数据，并将数据给到IO线程

2)从库

1.IO线程：连接主库，接收主发送过来的新binlog数据

2.sql线程：执行relay-log中主库传过来的新数据

3.relay-log(中继日志)：存储所有主库传过来的新数据

4.master.info：记录上一次请求到主库返回的binlog信息，下一才从该位置点读取对比数据

5.relay-log.info：记录上一次sql线程执行到relay-log中的位置点，下一次从该位置点执行

二、主从复制的搭建

1.主库操作

1)配置主库

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

[mysqld]

server_id=1

log_bin=mysql-bin

2)授权一个主从用户

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

​

#grant replication slave 是一个全局授权，不能指定单个库授权，如果想同步单个库使用过滤复制

3)查看主库的binlog信息

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      326 |             |                 |                   |

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

1 row in set (0.00 sec)

2.从库操作

1)配置从库

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

[mysqld]

server_id=2

2)配置主从复制

change master to

master_host='172.16.1.51',

master_user='rep',

master_password='123',

master_port=3306,

master_log_file='mysql-bin.000001',

master_log_pos=326;

3)开启IO线程和sql线程

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

4)查看主从状态

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.1.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 326

Relay_Log_File: db02-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

三、主从复制故障

1.IO线程故障

#Slave_IO_Running: No 或者 Connecting

​

1.检测网络

[root@db02 ~]# ping 172.16.1.51

​

2.检测端口

[root@db02 ~]# telnet 172.16.1.51 3306

​

3.防火墙是否开启

​

4.主从的用户名或者密码错误

#测试使用主从用户的用户名和密码连接主库

[root@db02 ~]# mysql -urep -p123 -h172.16.1.51

​

5.反向解析

[root@db01 ~]# mysql -uroot -p123 -h172.16.1.51

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)

#解决

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

[mysqld]

skip_name_resolve

2.sql线程故障

#Slave_SQL_Running: No 或者 Connecting

​

1.主库有的数据，从库没有

2.从库有的数据，主库没有

3.主库数据与从库不一致

1)解决办法一：(不认)

1.停止主从复制

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

​

2.跳过一个错误

mysql> set GLOBAL sql_slave_skip_counter=1;

Query OK, 0 rows affected (0.00 sec)

​

3.开启主从

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

2)解决办法二：

1.停止主从复制

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

​

2.清空主从复制的信息

mysql> reset slave all;

Query OK, 0 rows affected (0.00 sec)

​

3.同步主库所有数据

​

4.重新配置主从复制

3.在数据库运行过程中添加从库

1.准备从库

2.主库导出数据

#打点备份不注释

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=1 --single-transaction > /tmp/full.sql

#打点备份注释

[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full2.sql

3.将数据推送至从库

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

[root