当前位置：首页 > article >正文

续：MySQL的并行复制

article 2024/9/21 8:45:33

【示例】

如果数据复制的慢的话，就会导致主从数据不一致性；

有的企业需要数据保持强一致性；比如银行等；

日志回放默认是单线程；

#

mysql> show processlist;
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
| 6 | system user | | NULL | Connect | 170990 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 23369 | Slave has read all relay log; waiting for more updates | NULL |
| 12 | root | localhost | NULL | Sleep | 24065 | | NULL |
| 13 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

#做日志的多线程回放；加快回访速度；

#上一个实验我们在slave2上做了延迟；这一个实验就不要在设定了SQL_Delay: 60延迟的slave上做，这样毫无意义！

master slave1

slave1上：编辑主配置文件：

多线程上需要把gtid打开

[root@mysql2 ~]# vim /etc/my.cnf
[root@mysql2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
super_read_only=on
gtid_mode=on
enforce-gtid-consistency=on

slave-parallel-type=LOGICAL_CLOCK #基于组提交
slave-parallel-workers=16 #开启线程数量
master_info_repository=TABLE #master信息在表中记录，默认记录在/data/mysql//master.info
relay_log_info_repository=TABLE #回放日志信息在表中记录，默认记录在/data/mysql/relay-log.info #多线程回放，日志会成为瓶颈，设置这两条将日志记录在数据表里，而不是文件里
relay_log_recovery=ON #日志回放恢复功能开启

[root@mysql2 ~]# cd /data/mysql/
[root@mysql2 mysql]# ls
auto.cnf folian ibtmp1 mysql2.pid mysql.sock relay-log.info
ca-key.pem ib_buffer_pool master.info mysql2-relay-bin.000002 mysql.sock.lock server-cert.pem
ca.pem ibdata1 mysql mysql2-relay-bin.000003 performance_schema server-key.pem
client-cert.pem ib_logfile0 mysql1.err mysql2-relay-bin.index private_key.pem sys
client-key.pem ib_logfile1 mysql2.err mysql-bin.index public_key.pem
#重启数据库

[root@mysql2 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!

#查看进程：这里刚开始出现了问题

[root@mysql2 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
[root@mysql2 ~]# 2024-08-27T02:41:31.233896Z mysqld_safe A mysqld process already exists

#这里出错了我们并没有查看到应有的效果
[root@mysql2 ~]# mysql -uroot -predhat

mysql> show processlist;
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
| 6 | system user | | NULL | Connect | 172796 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 25175 | Slave has read all relay log; waiting for more updates | NULL |
| 12 | root | localhost | NULL | Sleep | 25871 | | NULL |
| 14 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+--------+--------------------------------------------------------+------------------+
4 rows in set (0.01 sec)

mysql> exit;
Bye
[root@mysql2 ~]# ps aux | grep mysql
avahi 793 0.0 0.1 62268 2276 ? Ss Aug26 0:11 avahi-daemon: running [mysql1.local]
root 17093 0.0 0.0 113412 1608 ? S 00:47 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql2.pid
mysql 17234 0.6 10.6 1603764 198588 ? Sl 00:47 3:45 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=mysql2.err --pid-file=/data/mysql/mysql2.pid --socket=/data/mysql/mysql.sock
root 22394 0.0 0.2 163396 4172 pts/3 S+ 09:01 0:00 mysql -px xxxx
root 23902 0.0 0.0 112812 968 pts/1 S+ 10:53 0:00 grep --color=auto mysql
[root@mysql2 ~]# kill 17093
[root@mysql2 ~]# kill 17234
[root@mysql2 ~]# kill 22394
[root@mysql2 ~]# ps aux | grep mysql
avahi 793 0.0 0.1 62268 2276 ? Ss Aug26 0:11 avahi-daemon: running [mysql1.local]
root 23918 0.0 0.0 112812 968 pts/1 S+ 10:54 0:00 grep --color=auto mysql

[root@mysql2 ~]# /etc/init.d/mysqld start
Starting MySQL...... SUCCESS!
#成功解决并查看
[root@mysql2 ~]# mysql -uroot -predhat

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 9 | Slave has read all relay log; waiting for more updates | NULL |
| 3 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 11 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 10 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 10 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 10 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 10 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 9 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 9 | Waiting for an event from Coordinator | NULL |
| 20 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
18 rows in set (0.00 sec)

#从上我们可以看出有16个SQL进程来做日志回放，但是最终还是要落到CPU性能上。

#多线程：使主从的数据差异尽可能减小，（数据库优化）