MySQL-10-主从复制网站首页 学无止境

MySQL-10-主从复制

陆先生。 2024-07-16 00:01:02

简介MySQL-10-主从复制

主库

mysql> show processlist;
+----+----------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User     | Host                 | db   | Command     | Time | State                                                         | Info             |
+----+----------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  3 | root     | localhost            | NULL | Query       |    0 | starting                                                      | show processlist |
|  5 | repluser | 192.168.168.51:39978 | NULL | Binlog Dump | 1160 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  7 | repluser | 192.168.168.51:39982 | NULL | Sleep       |    6 |                                                               | NULL             |
+----+----------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+

主库binlog dump线程告诉从库（51）binlog有更新，使从库执行io线程

查看MySQL连的是那台主机

mysql> select @@hostname

补充

relay-log与binlog日志格式一样，都可用mysqlbinlog查看

一、主从同步基本配置

1、原理：3个线程

主库：启用binlog dump线程，告诉从库binlog有更新，使从库执行io线程
从库：io线程和sql线程

2、master主库配置

2.1、改配置文件，启用binlog日志

server_id=主机IP
log_bin=日志路径/日志名
binlog_format="mixed"
max_binlog_size=200M

2.2、添加slave授权用户（只读权限）

mysql> grant replication slave on *.* to 用户名@地址 identified by '密码';

2.3、对所有库（默认不允许对单个库）有同步权限

3、slave从库配置

3.1、改配置文件（主需开启server_id）

server_id=主机名

3.2、指定主库

mysql> change master to
master_host="",
master_user="",
master_password="",
master_log_file="binlog.000001",
master_log_pos="354";   #log_file与log_pos需要在主库上查看

3.3、查看主库的binlog信息

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      354 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.4、启动slave线程

mysqsl> start slave;/stop slave;

mysql> show slave status G;

...
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
...

3.5、中继日志relay-log

master.info,主库信息
relay-log.info,中继日志信息,
本机主机名-relay-bin.xx,中继日志
本机主机名-relay-bin.index，索引文件

3.6、中继日志错误

删除所有的中继日志信息，重启mysql

4、主从同步结构

一主一次
主从从（log_slave_updates）
互为主从
一主多从

5、主从同步模式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sGj5hir4-1685459395924)(null)]
异步复制
全同步复制
半同步复制

5.1、设置同步模式：

1：查看是否能动态加载插件（默认允许）

mysql> show variables like "have_dynamic_loading";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)

2、查看是否加载模块

mysql> select plugin_name,plugin_status from information_schema.plugins 
	where plugin_name like "%semi%";
Empty set (0.00 sec)

3、命令行安装插件（需要super权限）

主库安装插件：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.06 sec)

从库安装插件：
mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";
Query OK, 0 rows affected (0.04 sec)

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
+---------------------+---------------+
| plugin_name         | plugin_status |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+

4、永久启用半同步复制

主库
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

从库
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

4、补充

show processlist;
select @@hostname; #查看连接的是那台主机的MySQL
mysql的serverid(/var/lib/mysql/auto.cnf)，克隆服务器时，serverid不能重复

mysql> show processlist;
+----+----------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User     | Host                 | db   | Command     | Time | State                                                         | Info             |
+----+----------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  3 | root     | localhost            | db1  | Query       |    0 | starting                                                      | show processlist |
|  5 | repluser | 192.168.168.51:39978 | NULL | Binlog Dump | 4157 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  8 | jim      | 192.168.168.20:58568 | db1  | Sleep       | 1542 |                                                               | NULL             |
|  9 | repluser | 192.168.168.51:39984 | NULL | Sleep       |   78 |                                                               | NULL             |
+----+----------+----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  3 | root        | localhost | db1  | Query   |    0 | starting                                               | show processlist |
|  4 | system user |           | NULL | Connect | 4893 | Waiting for master to send event                       | NULL             |
|  5 | system user |           | NULL | Connect | 2278 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| 50         |
+------------+
1 row in set (0.00 sec)

[root@59 ~]# cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=3eea1665-598b-11eb-a58e-0050568a81b7

二、相关参数

log_slave_updates			#主从从-级联复制参数
binlog_do_db=db1,db2		#主库配置只同步指定表
binlog_ignore_db=db1,db2	#主库配置不同步指定表（指定与不指定只能选一个）
replicate_do_db=db1,db2	#从库配置
replicate_ignore_db=db1,db2	#从库配置