mysql主从复制、并行同步、半同步

最新推荐文章于 2023-12-14 09:05:02 发布

Morganite

最新推荐文章于 2023-12-14 09:05:02 发布

阅读量532

点赞数

文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/Morganite/article/details/122033474

版权

本文详细介绍了MySQL的主从复制过程，包括GTID版主从复制的配置和测试，半同步复制的设置与验证，以及手动延迟和并行复制的实现，确保数据库的一致性和性能优化。

摘要由CSDN通过智能技术生成

复制源码编译的mysql到另一台服务器

[root@server1 local]# cd /usr/local/
[root@server1 local]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server1 local]# scp -rp mysql server2:/usr/local
[root@server1 init.d]# scp mysqld server2:/etc/init.d/
root@server2's password: 
mysqld                                        100%   10KB   8.1MB/s   00:00  
[root@server1 init.d]# scp /etc/my.cnf server2:/etc/
root@server2's password: 
my.cnf                                        100%  167   102.8KB/s   00:00

在server2中设置mysql

在server2中建立id一样的用户
[root@server2 ~]# groupadd -g 1001 mysql
[root@server2 ~]# useradd -u 1001 -g mysql -M -d /data/mysql -s /sbin/nologin mysql
[root@server2 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server2 ~]# ll /etc/init.d/mysqld ##要有执行权限
-rwxr-xr-x 1 root root 10566 Dec 15 11:03 /etc/init.d/mysqld
[root@server2 ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid



[root@server2 ~]# vim .bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

[root@server2 ~]# source .bash_profile

[root@server2 ~]# mkdir /data/mysql -p

[root@server2 ~]# chown mysql.mysql /data/mysql/

[root@server2 ~]# mysqld --initialize --user=mysql ##初始化之后，data/mysql里有文件
[root@server2 ~]# cd /data/mysql
[root@server2 mysql]# ls
auto.cnf         client-key.pem  ib_logfile1         private_key.pem  sys
ca-key.pem       ib_buffer_pool  mysql               public_key.pem
ca.pem           ibdata1         mysqld.log          server-cert.pem
client-cert.pem  ib_logfile0     performance_schema  server-key.pem

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

[root@server2 mysql]# cat mysqld.log

2021-12-15T03:13:48.634734Z 1 [Note] A temporary password is generated for root@localhost: >5*n4LyKfk16

[root@server2 mysql]# mysql_secure_installation 

[root@server2 mysql]# mysql -pwestos

一、主从复制

[root@server1 init.d]# vim /etc/my.cnf

log-bin=mysql-bin
server-id=1

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

主机mysql中建立一个用户
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

从机可以全程登陆，代表权限设置成功
[root@server2 mysql]# mysql -h 172.25.6.1 -u repl -p

主机中
mysql> SHOW MASTER STATUS;

mysql> CREATE DATABASE westos;建立一个库

主从数据库应该保持一致，所以我们要先备份给2
[root@server1 mysql]# mysqldump -pwestos westos > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@server1 mysql]# vim dump.sql
[root@server1 mysql]# scp dump.sql server2:
root@server2's password: 
dump.sql                                      100% 1251     1.4MB/s   00:00

server2中

[root@server2 ~]# mysqladmin -pwestos create westos
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@server2 ~]# mysql -pwestos westos < dump.sql
mysql: [Warning] Using a password on the command line interface can be insecure.



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

server-id=2

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


mysql> CHANGE MASTER TO MASTER_HOST='172.25.6.1', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=760;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
mysql> show slave status\G;

有两个yes表示成功，如果IO不对可能是用户和网络（火墙）问题，如果sql不对可能是两个主机上的sql不一致

测试：

server1中建立表

mysql> use westos
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE user (
    -> username varchar(6) not null,
    -> password varchar(30) not null
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user values ('user1','111');
Query OK, 1 row affected (0.01 sec)

server2中可以查看到

二、mysql的gtid版主从复制

[root@server1 mysql]# vim /etc/my.cnf

gtid_mode=ON ##强制打开gtid
enforce-gtid-consistency=ON

[root@server1 mysql]# /etc/init.d/mysqld restart

root@server2 ~]# vim /etc/my.cnf

gtid_mode=ON
enforce-gtid-consistency=ON

[root@server2 ~]# /etc/init.d/mysqld restart
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='172.25.6.1', master_user='repl', master_password='westos', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

测试：

server1中添加

mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into user values ('user2','222');
Query OK, 1 row affected (0.00 sec)

去2中查看,可以看到同步

server1中

server2中

三、半同步

下载地址：MySQL :: MySQL 5.7 Reference Manual :: 16.3.9.2 Semisynchronous Replication Installation and Configuration

server1中

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

server2中

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

server2中

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 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        |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

server1中

rpl_semi_sync_master_enabled = 1
mysql> show variables like 'rpl%';

server2中

rpl_semi_sync_slave_enabled = 1

mysql> show variables like 'rpl%';
mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)


mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

server1中

在server2中查看

关闭
mysql> STOP SLAVE IO_THREAD;

在server1中

mysql> insert into user values ('user4','444');
会等待10秒后写入，但2中并未同步，所以主从不一致，要想保持数据库强一致性，需要把等待时间设置为无穷
mysql> show status like 'rpl%';

server2中重新打开，数据同步

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from westos.user;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user3    | 333      |
| user4    | 444      |
+----------+----------+
4 rows in set (0.00 sec)

四、手动延迟

server中

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_DELAY = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql>  show slave status\G

在1中插入
mysql> use westos
Database changed
mysql> insert into user values ('user5','555');
Query OK, 1 row affected (0.00 sec)
可以看到2中
延迟时间

五、并行复制

参考：MySQL 5.7新特性：并行复制原理（MTS）_总有些事，值得你去努力-CSDN博客_并行复制

server2中

[root@server2 mysql]# vim /etc/my.cnf

master_info_repository=TABLE
slave_parallel_workers=16
slave-parallel-type=LOGICAL_CLOCK
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@server2 mysql]# /etc/init.d/mysqld restart

mysql> show processlist;

复制server2中的数据库到server3
注意/etc/my.cnf id =3

server1中备份数据库并传给server3

[root@server1 ~]# mysqldump -pwestos westos > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@server1 ~]# scp dump.sql server3:

server3中

[root@server3 ~]# mysqladmin -pwestos create westos
[root@server3 ~]# mysql -pwestos westos < dump.sql
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@server3 ~]# mysql -pwestos
mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user3    | 333      |
| user4    | 444      |
| user5    | 555      |
+----------+----------+
5 rows in set (0.00 sec)

server2中


[root@server2 init.d]# vim /etc/my.cnf
log-bin=mysql-bin
log-slave-updates=ON

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

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> flush privileges;  刷新
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='172.25.6.1', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

测试：

在server1中

mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into user values ('user6','666');
Query OK, 1 row affected (0.01 sec)

server3中