MySQL主从复制

# 线程
    dump线程：主库上的线程，从binlog中取出数据交给从库的IO线程
    IO线程：从库上的线程，连接主库dump线程取数据，取到数据写入缓存（relay log中）
    SQL线程：从库上的线程，执行relay log中的SQL语句到数据库中
    
# 文件
    binlog日志：主库上的文件，记录所有更改库表的语句
    master.info：从库上的文件，记录主库的binlog名字和位置点，IO线程更新/读取
    relay-log.info：从库上的文件，记录relay-log里的位置点，上一次SQL线程读取到哪里了，SQL线程更新/读取
    relay-log：从库上的文件，记录从主库binlog拿来的新数据

# 修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server_id=1

# 重启
[root@db01 ~]# systemctl restart mysqld

# 给主库做全备
[root@db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --singletransaction|gzip > /opt/full.sql.gz

# 将全备拷贝到从库
[root@db01 ~]# scp /opt/full.sql.gz 172.16.1.52:/opt/

# 创建一个主从复制用户
mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

# 查看主库master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 326 | | | |
+------------------+----------+--------------+------------------+-------------------+

# 启停SQL线程
mysql> stop slave sql_thread;
mysql> start slave sql_thread;

# 启停IO线程
mysql> stop slave io_thread;
mysql> start slave io_thread;

# 修改从库配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2

# 重启数据库
[root@db02 ~]# systemctl restart mysqld

# 导入数据库
[root@db02 ~]# zcat /opt/full.sql.gz |mysql -uroot -p

# 查找位置点
[root@db02 ~]# zcat /opt/full.sql.gz |head -25

# 执行change master to 语句
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=40783;

# 开启主从复制
mysql> start slave;

# 查看主从复制状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# 开启主从复制
start slave;

# 关闭主从复制:
stop slave;

# 重置主从复制：
stop slave;
reset slave all;

# 查看状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# ID线程故障
mysql> show slave status\G
Slave_IO_Running: No
Slave_SQL_Running: Yes

Ip错了
用户名错了
密码错了
文件名错了

CHANGE MASTER TO
MASTER_HOST='172.16.1.61',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=40783; // 不影响IO线程

# 排查流程
# IP错了：
[root@db02 ~]# ping 172.16.1.61

# 用户名错了 和 密码错了：
[root@db02 ~]# mysql -urep -p123 -h172.16.1.61

# 报错
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'rep'@'db01' (using password: YES)

# 解决办法（反向域名解析）
vim /etc/my.cnf
[mysqld]
skip_name_resolve

/etc/init.d/mysqld restart

# 文件名错了
zcat /opt/full.sql.gz |head -25
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=40783;

# 解决方案：
stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=40783;
start slave;

# 2.SQL线程故障
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No

# 主库从库数据不一致导致
# 查看状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1049
Last_Error: Error 'Unknown database 'test_sql_thread'' on query. Default database:
'test_sql_thread'. Query: 'create table tb1(id int)'

# 解决方案一：（治标不治本）
1.停止从库的主从复制
mysql> stop slave;

2.跳过一次错误
mysql> set global sql_slave_skip_counter=1;

3.开启主从复制
mysql> start slave;

# 解决方案二：（治标不治本）
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
slave-skip-errors=1032,1062,1007,1049,1146

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

# 解决方案三
1.重新备份数据库，恢复到从库
2.给从库设置为只读
# 在命令行临时设置
set global read_only=1;
# 在配置文件中永久生效
read_only=1

# 解决方案四
# 临时停止同步
mysql> stop slave;

# 将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;

# 开启同步
mysql> start slave;

# 先停止slave
mysql> stop slave;

# 设置主从延时
change master to master_delay=120;

# 3.开启主从复制
mysql> start slave;

# 4.查看状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
SQL_Delay: 120

# 5.没有主从时，配置
CHANGE MASTER TO
MASTER_HOST='172.16.1.51',
MASTER_USER='rep',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=189016,
MASTER_DELAY=120;

# 6.验证延时情况
mysql> show slave status\G
SQL_Remaining_Delay: 93

# 1.是否动态支持半同步复制
mysql> show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+

# 2.插件的路径
[root@db02 plugin]# pwd
/application/mysql/lib/plugin

-rwxr-xr-x 1 mysql mysql 436234 Sep 23  2020 semisync_master.so
-rwxr-xr-x 1 mysql mysql 259318 Sep 23  2020 semisync_slave.so


# 3.主库安装半同步插件
mysql> install plugin rpl_semi_sync_master soname'semisync_master.so';

# 4.启动插件
mysql> set global rpl_semi_sync_master_enabled = 1;

# 5.设置超时时间
mysql> set global rpl_semi_sync_master_timeout = 1000;

# 6.修改配置文件
[root@m01 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

# 7.检查安装
mysql> show variables like'rpl%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled       | ON       |
| rpl_semi_sync_master_timeout       | 1000     |
| rpl_semi_sync_master_trace_level   | 32       |
| rpl_semi_sync_master_wait_no_slave | ON       |
| rpl_stop_slave_timeout             | 31536000 |
+------------------------------------+----------+

# 8.查看半同步的信息
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| 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      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+


### 从库操作
# 1.安装从库半同步插件
mysql> install plugin rpl_semi_sync_slave soname'semisync_slave.so';

# 2.开启插件
mysql> set global rpl_semi_sync_slave_enabled = 1;

# 3.重启IO线程
mysql> stop slave io_thread;

mysql> start slave io_thread;

# 4.修改配置文件
[root@db02 plugin]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled =1

# 白名单（binlog-ignore-db）
	主库拒绝从库，复制指定库的数据
	
# 原理
	不记录指定的数据库到binlog日志中

# 主库配置文件
[root@m01 ~]# vim /etc/my.cnf
[mysqld]
binlog-do-db=lol

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 |      154 | qq           |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 配置多个库
binlog-do-db=lol
binlog-do-db=cf

# 实例
# 进入qq库，不然数据还是会同步过去
mysql> use qq

# 创建表
mysql> create table tb1(id int);

# 在从库上查看数据
mysql> use qq

mysql> show tables;
+--------------+
| Tables_in_qq |
+--------------+
| tb1          |
+--------------+
1 row in set (0.00 sec)

# 黑名单
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000005 |      154 |              | wc               |                   |
+------------------+----------+--------------+------------------+-------------------+

# 创建表
mysql> use wc

mysql> create table wc(id int);

mysql> show tables;
+--------------+
| Tables_in_wc |
+--------------+
| aa           |
| payment      |
| qq           |
| score        |
| student      |
| student1     |
| wc           |
+--------------+
7 rows in set (0.00 sec

# 从库上查看表
mysql> use wc

mysql> show tables;
+--------------+
| Tables_in_wc |
+--------------+
| aa           |
| payment      |
| score        |
| student      |
| student1     |
+--------------+

# 黑名单：从库SQL线程，不拿relay log中的指定库语句
	replicate-ignore-db=[库名]
	replicate-ignore-table=[库名.表名]
	replicate-wild-ignore-table=mysql.t*（模糊匹配）
	
# 白名单：从库SQL线程，只拿relay log中的指定库语句
	replicate-ignore-db=[库名]
	replicate-ignore-table=[库名.表名]
	replicate-wild-ignore-table=mysql.t*（模糊匹配）

关注我的微信公众号【DB哥】，免费学MySQL高级课程，快学起来吧