将源码编译装好的mysql数据库发送至两个纯净的实验环境
安装过程:https://editor.csdn.net/md/?articleId=109447109
rsync -r /usr/local/mysql/ root@xxx.xxx.xxx.xxxx:/usr/local/
rsync -r /usr/local/mysql/ root@xxx.xxx.xxx.xxxx:/usr/local/
修改配置文件
在主库
# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=1
log-bin=mysql-bin
在从库
禁止在slave从库修改mysql数据库设置以外的数据
# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=2
删除编译好的data目录下的文件
rm -rf /usr/local/mysql/data/*
建立mysql用户
useradd -s /sbin/nologin/ -M -d /usr/local/mysql/data/ mysql
复制mysql启动文件
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
初始化并安全初始化
mysqld --initialize --user=mysql
/etc/init.d/mysql start
mysql_secure_installation
创建异步同步用户并授予权限
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'westos';
测试登陆
mysql -u repl -p -h 172.25.10.5
在主库端查看二进制日志状态
mysql> show master status;
在从库声名主库
mysql> CHANGE MASTER TO MASTER_HOST='172.25.10.5', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=517;
打开slave
mysql> start slave;
查看是否成功
mysql> show slave status\G;
测试
在主库操作看是否可以同步到slave从库
mysql> create database westos;
在从库查看
mysql> show databases;
基于GTID自动定位复制
关闭slave从库
mysql> stop slave;
修改主库和从库的配置文件
# 主库 vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
#从库 vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
重启mysql数据库
/etc/init.d/mysqld restart
进入从库配置slave
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='172.25.10.5', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G;
测试
改变master端的数据库内容,slave端也会改变
# 在master
mysql> DROP DATABASE westos;
# 在slave
show databases;
半同步复制
# 在master端
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled=1;
# 在slave端
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> stop slave IO_THREAD;
mysql> tart slave IO_THREAD;
#查看slave的半同步复制是否打开
mysql> show status like 'rpl%';
测试
在master操作,查看master半同步状态
mysql> create database westos;
mysql> create table user_tb (
-> name varchar(25) not null,
-> password varchar(25) not null);
mysql> insert into user_tb values ('user1','123');
mysql> insert into user_tb values ('user2','234');
mysql> show status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 4 |
| 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 | 469 |
| Rpl_semi_sync_master_tx_wait_time | 1879 |
| Rpl_semi_sync_master_tx_waits | 4 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 4 |
+--------------------------------------------+-------+
查看slave
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
mysql> use westos;
mysql> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| user_tb |
+------------------+
mysql> select * from user_tb;
+-------+----------+
| name | password |
+-------+----------+
| user1 | 123 |
| user2 | 234 |
+-------+----------+
延迟复制
# 在slave
mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO MASTER_DELAY = 10;
mysql> START SLAVE SQL_THREAD;
测试
# 在master
insert into user_tb values ('user3','345');
# 在slave
mysql> select * from user_tb;
+-------+----------+
| name | password |
+-------+----------+
| user1 | 123 |
| user2 | 234 |
+-------+----------+
# 等待10s
mysql> select * from user_tb;
+-------+----------+
| name | password |
+-------+----------+
| user1 | 123 |
| user2 | 234 |
| user3 | 345 |
+-------+----------+
如果不是特别需要做完实验关闭延时
mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> START SLAVE SQL_THREAD;
单主模式
修改配置文件
# 在master
# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log-bin=mysql-bin
slave_parallel_workers=10
slave_parallel_type=LOGICAL_CLOCK
relay_log_recovery=ON
# /etc/init.d/mysqld restart
# 在slave
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log-bin=mysql-bin
slave_parallel_workers=10
slave_parallel_type=LOGICAL_CLOCK
relay_log_recovery=ON
# /etc/init.d/mysqld restart
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'westos';
mysql> flush privileges;
mysql> set global rpl_semi_sync_slave_enabled=ON;
mysql> stop slave IO_THREAD;
mysql> start slave IO_THREAD;
mysql> show variables like 'rpl%';
mysql> show status like 'rpl%';
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled=1;
建立一个新的虚拟机
# 将mysql目录发送
rsync -a /usr/loal/mysql server2:/usr/local
rm -rf mysql.sock
/etc/init.d/mysqld start
# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=3
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
/etc/init.d/mysqld restart
mysql> CHANGE MASTER TO MASTER_HOST='172.25.10.5', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status;
mysql> select * from westos.user_tb;
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> stop slave io_thread;
mysql> start slave io_thread;
mysql> show status like 'rpl%';
mysql> select * from westos.user_tb;
测试
# 在master端修改数据
mysql> insert into westos.user_tb values ('user4','346');
# 在server1 master查看
mysql> select * from westos.user_tb;
# 在selver查看
mysql> select * from westos.user_tb;
组复制(多主模式,每个mysql服务器都是master)
在第一个mysql服务器
#vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
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= "172.25.10.5:33061"
group_replication_group_seeds= "172.25.10.5:33061,172.25.10.1:33061,172.25.10.2:33061"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_ip_whitelist="172.25.10.0/24,127.0.0.1/8"
group_replication_allow_local_disjoint_gtids_join=on
# /etc/init.d/mysqld stop
# rm -rf /usr/local/mysql/data/*
# mysqld --initialize --user=mysql
# /etc/init.d/mysqld start
# mysql -p
mysql> alter user root@'localhost' identified by 'westos';
mysql> set sql_log_bin=0;
mysql> create user rpl_user@'%' identified by 'westos';
mysql> grant replication slave on *.* to rpl_user@'%';
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;
mysql> set global group_replication_bootstrap_group=OFF;
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 9f1598da-337d-11eb-8489-52540096cba8 | server5 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
在第二个mysql服务器
# rsync -a server5:/etc/my.cnf /etc/my.cnf
# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
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= "172.25.10.2:33061"
group_replication_group_seeds= "172.25.10.5:33061,172.25.10.1:33061,172.25.10.2:33061"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_ip_whitelist="172.25.10.0/24,127.0.0.1/8"
group_replication_allow_local_disjoint_gtids_join=on
# /etc/init.d/mysqld stop
# rm -rf /usr/local/mysql/data/*
# mysqld --initialize --user=mysql
# /etc/init.d/mysqld start
# mysql -p
mysql> alter user root@'localhost' identified by 'westos';
mysql> set sql_log_bin=0;
mysql> create user rpl_user@'%' identified by 'westos';
mysql> grant replication slave on *.* to rpl_user@'%';
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;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 9f1598da-337d-11eb-8489-52540096cba8 | server5 | 3306 | ONLINE |
| group_replication_applier | a269812f-3381-11eb-bab1-52540099ec4d | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
用同样的方法再同步一个组
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 248f0416-3383-11eb-962c-5254003cc2e3 | localhost.localdomain | 3306 | ONLINE |
| group_replication_applier | 9f1598da-337d-11eb-8489-52540096cba8 | server5 | 3306 | ONLINE |
| group_replication_applier | a269812f-3381-11eb-bab1-52540099ec4d | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
测试
在第一台mysql服务器建立一个库
mysql> create database westos;
在第二台mysql服务器进入库中建立一个表
mysql> use westos;
mysql> show tables;
mysql> create table tb (c1 int primary key, c2 text not null);
在第三台mysql服务器在建立的表中加入数据
mysql> use westos;
mysql> insert into tb values ('1','linux');
# 查看
mysql> select * from westos.tb
+----+-------+
| c1 | c2 |
+----+-------+
| 1 | linux |
+----+-------+
测试结果:每台mysql服务器的组都对数据库具备读写权限