lamp架构--mysql主从复制（延迟复制，并行复制，多主复制构建集群）

于 2021-04-13 16:09:13 发布
阅读量194 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_45777669/article/details/115669928
版权

文章目录

1. 延迟复制

延迟复制是指SQL线程，先把数据保存下来，然后慢慢回放。在部分场景下可能会用到延迟复制，例如如果将主库文件误删，加入此时设置的有延时复制则可以回滚主库上的数据

server12
mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO MASTER_DELAY = 30; ##延迟30s
mysql> START SLAVE SQL_THREAD;
mysql> show slave status\G ;

master server11 插入数据
mysql> insert into user_tb values ('user9','9999');

slave server12
mysql> show slave status\G ;

缓慢执行到29s
在这里插入图片描述
执行完毕
在这里插入图片描述

2. 并行复制

[root@server12 data]# vim /etc/my.cnf  ##在配置文件中添加相关参数,更改后要重启mysql
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
validate_password=OFF
gtid_mode=ON
enforce-gtid-consistency=ON

rpl_semi_sync_master_enabled=1 ##优化IO线程（半同步）
rpl_semi_sync_slave_enabled=1

slave-parallel-type=LOGICAL_CLOCK    ##优化SQL线程 
slave-parallel-workers=16         ##设置并行复制（开启16个worker），此时SQL线程退化为协调进程，分发16个worker

master_info_repository=TABLE      ##将更改以表的形式存储到数据库中，提高效率
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server12 data]# /etc/init.d/mysqld restart
[root@server12 data]# mysql -pwestos
mysql> show slave status\G ;
mysql> show variables like 'slave%';
mysql> show variables like '%info%';
+--------------------------------+----------------+
| Variable_name                  | Value          |
+--------------------------------+----------------+
| master_info_repository         | TABLE          | ##将每次的更改以表的形式写到数据库中
| relay_log_info_file            | relay-log.info |
| relay_log_info_repository      | TABLE          |
| session_track_transaction_info | OFF            |
| sync_master_info               | 10000          |
| sync_relay_log_info            | 10000          |
+--------------------------------+----------------+
6 rows in set (0.00 sec)
mysql> use mysql;
mysql> select * from slave_master_info;

并行复制开启的woker数为16
在这里插入图片描述在这里插入图片描述

3. 多主复制构建集群

3.1 整体部署

server11

[root@server11 ~]# /etc/init.d/mysqld stop
[root@server12 ~]# /etc/init.d/mysqld stop
[root@server13 ~]# /etc/init.d/mysqld stop

[root@server11 ~]# cd /usr/local/mysql/data/
[root@server11 data]# rm -fr *
[root@server11 data]# mysqld --initialize --user=mysql
[root@server11 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
[root@server11 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/server11.err'.
 SUCCESS!

[root@server11 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.0.11:33061"
group_replication_group_seeds= "192.168.0.11:33061,192.168.0.12:33061,192.168.0.13:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.0.0/24,127.0.0.1/8"  
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

[root@server11 data]# /etc/init.d/mysqld restart
[root@server11 data]# mysql -p'q&6UzZk5YMq3'
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER root@localhost identified by 'westos'; ##修改用户密码
mysql> show databases;
mysql> SET SQL_LOG_BIN=0;  ##不记录日志
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';  ##创建远程登录用户及密码
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; ##授权
mysql> FLUSH PRIVILEGES;  ##刷新授权表
mysql> SET SQL_LOG_BIN=1; ##记录日志
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group = ON; ##先激活组复制，只需要在该引导节点上做即可
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.54 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF; ##只需要在引导节点上做
mysql> SELECT * FROM performance_schema.replication_group_members; ##查看集群中成员

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
server12

[root@server12 data]# pwd
/usr/local/mysql/data
[root@server12 data]# rm -fr *
[root@server12 data]# ls
[root@server12 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
[root@server12 data]# mysqld --initialize --user=mysql
[root@server12 data]# /etc/init.d/mysqld start
[root@server12 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.0.12:33061"
group_replication_group_seeds= "192.168.0.11:33061,192.168.0.12:33061,192.168.0.13:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.0.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON  ##不加这行会报错，无法加入集群

[root@server12 data]# cat server12.err 
[root@server12 data]# /etc/init.d/mysqld restart
[root@server12 data]# mysql -p
mysql> ALTER USER root@localhost identified by 'westos';
mysql> show databases;
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> STOP GROUP_REPLICATION;
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
server13

[root@server13 data]# pwd
/usr/local/mysql/data
[root@server13 data]# rm -fr *
[root@server13 data]# ls
[root@server13 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
[root@server13 data]# mysqld --initialize --user=mysql
[root@server13 data]# /etc/init.d/mysqld start
[root@server13 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.0.13:33061"
group_replication_group_seeds= "192.168.0.11:33061,192.168.0.12:33061,192.168.0.13:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.0.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON

[client]
default-character-set=utf8
socket=/usr/local/mysql/data/mysql.sock
[mysql]
default-character-set=utf8
socket=/usr/local/mysql/data/mysql.sock

[root@server12 data]# /etc/init.d/mysqld restart
[root@server12 data]# mysql -p
mysql> ALTER USER root@localhost identified by 'westos';
mysql> show databases;
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;

在这里插入图片描述
在这里插入图片描述

3.2 测试

上述操作完成后在server1上先检查server2和server3是否都加入集群，然后创建表并测试。

server11
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> CREATE DATABASE test;
mysql> use test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> desc t1;

在这里插入图片描述
server12上可以查看到在server11上创建的表并且也可以为表添加新的内容：

server12
mysql> use test
mysql> select * from t1;
mysql> insert into t1 values (2, 'westos');
mysql> select * from t1;

在这里插入图片描述
server13也可以查看到在server11和server12上创建的表并且也可以为表添加新的内容：

MySQL [(none)]> use test
MySQL [test]> select * from t1;
MySQL [test]> insert into t1 values (3, 'linux');
MySQL [test]> select * from t1;

在这里插入图片描述
server11上关闭数据库进行测试：server12 server13数据库能够正常同步

[root@server11 data]# /etc/init.d/mysqld stop 
Shutting down MySQL............ SUCCESS!
 
server12
mysql> insert into t1 values (4, 'redhat');

server13
MySQL [test]> select * from t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | luis   |
|  2 | westos |
|  3 | linux  |
|  4 | redhat |
+----+--------+

MySQL [test]> insert into t1 values (5, 'linux');
mysql> select * from t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | luis   |
|  2 | westos |
|  3 | linux  |
|  4 | redhat |
|  5 | linux  |
+----+--------+

server12
mysql> select * from t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | luis   |
|  2 | westos |
|  3 | linux  |
|  4 | redhat |
|  5 | linux  |
+----+--------+

server12
在这里插入图片描述
server13
在这里插入图片描述

[root@server11 data]# /etc/init.d/mysqld start
[root@server11 data]# mysql -pwestos
mysql> use test;
mysql> select * from t1;
mysql> SELECT * FROM performance_schema.replication_group_members;

mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> select * from t1;

在这里插入图片描述
在这里插入图片描述

dandan-99
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值