前言
之前的章节中，我们源码编译安装并初始化了mysql数据库，今天我们将详细学习mysql数据库在lamp架构中的主要应用。
主要内容包括mysql的主从复制、通过gtid实现的主从复制（一主一从、一主多从）、mysql半同步复制、mysql组复制。
一、mysql主从复制
主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。你想，假设是单机，读写都在一台MySQL上面完成，性能肯定不高。如果有三台MySQL，一台mater只负责写操作，两台salve只负责读操作，性能不就能大大提高了吗？
主从复制的原理
①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。
②salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。
③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。
④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。
⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。
那么如何操作呢？
我们需要准备三台虚拟机：server1是master主机
/etc/init.d/mysqld start
接着就需要编辑配置文件了：
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#
character-set-server = utf8
collation-server = utf8_general_ci
server-id=1
log-bin=mysql-bin
修改文件之后重启mysql数据库！
/etc/init.d/mysqld restart
进入数据库：mysql -p
查看主机状态
查看主机状态
show master status;
结果如下：
接下来需要创mysql的用户授权了
CREATE USER 'repl'@'%' IDENTIFIED BY 'westos'; #创建用户repl可以通过任何方式登陆，密码为westos
查看用户
select * from mysql.user\G;
配置server1之后，需要配置server2,3，
先对于2来看：
需要安装mysql（直接把server1中的mysql scp到server2中的/usr/local）这样免去了源码编译麻烦的步骤！！
这里需要使用rsync复制目录！
所以在server1,2上执行：yum install rsync
在server1
cd /usr/local
rsync -az mysql server2:/usr/local
在server2上
cd /usr/local/mysql
du -sh 会发现mysql文件大小不断变大，因为在复制中！！
将mysql添加到环境变量中
vim ~/.bash_profile
source ~/.bash_profile
接着在server2上添加mysql数据目录
[root@server2 tomcat]# cd /usr/local/mysql/
[root@server2 mysql]# useradd -M -d /data/mysql -s /sbin/nologin mysql
[root@server2 mysql]# mkdir -p /data/mysql
[root@server2 mysql]# chown mysql.mysql /data/mysql
[root@server2 mysql]# chmod 750 /data/mysql
vim /etc/my.cnf编辑配置文件：
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
character-set-server = utf8
collation-server = utf8_general_ci
mysqld --initialize --user=mysql生成密码记得待会安全初始化要用：
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start #开启mysql
vim /etc/my.cnf ,添加设置从server-id=2
/etc/init.d/mysqld restart
安全初始化数据库
mysql_secure_installation
登陆验证：
mysql -p
server2:
连接主机ip的mysql
测试是否能连接server1的数据库，连接后，不要动数据库，看一眼即可！
mysql -h 172.25.1.1 -u repl -p
接下来在server1上的数据库创建数据：
create database westos; #创建库
mysql> create table westos.user(
-> username varchar(10));
INSERT INTO westos.user VALUES('user1'); #插入数据
select * from user; #查看数据
操作前，将主机被操作的库备份，并传给server2:因为slave同步 master数据的时候，需要两边数据相同才行！！！！！
mysqldump -pwestos westos > dbdump.db
scp dbdump.db server2:~
在server2中：
先打开数据库创建表westos
mysql -p
create database westos;
然后导入数据：
mysql -pwestos westos < dbdump.db
进入数据库查看是否存在数据：
mysql -p
show databases;
use westos
show tables;
开启slave并查看状态
下面这行代码最后的数据，需要在server1上查看show master status;查到的内容是多少就写多少！
CHANGE MASTER TO MASTER_HOST='172.25.1.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=968;
start slave;
show slave status\G;
当茶看到如下两行都是yes就表示成功，不然就失败了！
这时候主机master中的所有操作都可以成功通过二进制文件复制到从机slave中。
比如：
在server1中插入数据
INSERT INTO westos.user VALUES('user2');
INSERT INTO westos.user VALUES('user3');
select * from westos.user;
然后在server2中直接查看就可以看到同步成功！
select * from westos.user;
二、Gtid实现主从复制
在传统的复制里面，当发生故障需要主从切换时，服务器需要找到binlog和pos点，然后将其设定为新的主节点开启复制。相对来说比较麻烦，也容易出错。在MySQL 5.6里面，MySQL会通过内部机制自动匹配GTID断点，不再寻找binlog和pos点。我们只需要知道主节点的ip，端口，以及账号密码就可以自动复制。
目的在于master挂掉之后，可通过gtid_next 来恢复信息。
一主一从
特点：
高可用性、主备、热备份
server1master,server2是slave;
首先需要在master上打开gtid模式
编辑配置文件
vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=ON
重启mysql
/etc/init.d/mysqld restart
当然在slave主机也编辑主配置文件打开gtid模式
vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=ON
/etc/init.d/mysqld restart 重启服务
slave主机关闭slave，需要重新连接master,配置连接参数：
mysql -pwestos
stop slave;
CHANGE MASTER TO MASTER_HOST = '172.25.1.1', MASTER_USER='repl', MASTER_PASSWORD = 'westos', MASTER_AUTO_POSITION=1;
start slave;
在master上添加数据：
INSERT INTO westos.user VALUES (‘user4’);
在slave上查看是否添加成功：
select * from westos.user;
一主多从
通常使用在读作用大于写作用
server1是master,server2既是master又是slave,server3是slave；
通过server3连接server2
在server2上：
vim /etc/my.cnf,使得二进制文件能够被读取并且开启slave更新开关
server-id=2
log-bin=mysql-bin
log_slave_updates=ON
gtid_mode=ON
enforce-gtid-consistency=ON
重启数据库服务：
/etc/init.d/mysqld restart
和前面一主一从一样需要备份数据库：
mysqldump -pwestos --set-gtid-purged=OFF westos > dump.sql
scp dump.sql server3:~
授权repl用户用于slave服务
mysql -pwestos
grant replication slave on *.* to repl@'%' identified by 'westos';
reset master;
在server3操作：
和前面2上一样，把数据库拷贝过来：
yum install -y rsync
在server2上拷贝
rsync -az mysql 172.25.1.3:/usr/local/
然后在server3上等待
cd
vim .bash_profile
source .bash_profile
在server1上：
[root@server1 ~]# scp /etc/init.d/mysqld root@172.25.1.3:/etc/init.d/
在server3上：
[root@server3 init.d]# vim /etc/my.cnf
[root@server3 init.d]# mkdir -p /data/mysql
[root@server3 init.d]# useradd -M -d /data/mysql/ -s /sbin/nologin mysql
[root@server3 init.d]# chown mysql.mysql /data/mysql/
vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
source .bash_profile
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
mysqld --initialize --user=mysql
mysql_secure_installation
在server2上：
mysql -pwestos
grant replication slave on *.* to repl@'%' identified by 'westos';
接着在server3上：
mysqladmin -pwestos create westos
mysql -pwestos westos < dump.sql
vim /etc/my.cnf
server-id=3
gtid_mode=ON
enforce-gtid-consistency=ON
重启服务
/etc/init.d/mysqld restart
登陆数据库
mysql -pwestos;
stop slave;
CHANGE MASTER TO MASTER_HOST = '172.25.1.2', MASTER_USER='repl', MASTER_PASSWORD = 'westos', MASTER_AUTO_POSITION=1;
start slave;
show slave status\G;
select * from westos.user;
接着测试能否同步数据：
在server1上：
use westos
INSERT INTO westos.user VALUES ('user5');
然后在server3上查看数据：
同步过来了，成功！！
三.mysql半同步复制
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能， 主库把binlog日志发送给从库，这一动作就结束了，并不会验证从库是否接收完毕，这一过程，有可能出现当主服务器或从服务器端发生故障的时候，有可能从服务器没有接收到主服务器发送过来的binlog日志，会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。
为了解决上述可能发生的错误，MySQL 5.5 引入了一种半同步复制模式。该模式可以确保从服务器接收完主服务器发送的binlog日志文件并写入到自己的中继日志relay log里，然后会给主服务器一个反馈，告诉主服务器已经接收完毕，这时主服务线程才返回给当前session告知操作完成。
当出现超时情况是，主服务器会暂时切换到异步复制模式，直到至少有一个从服务器从及时收到信息为止。
在server1上：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安装master模块
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; #查询模块状态
SET GLOBAL rpl_semi_sync_master_enabled =1; #启动模块
show variables like 'rpl%'; #查看复制
第二行数据显示等待时间为10000：
插入数据，默认等待最长响应时间为10s
INSERT INTO westos.user VALUES ('user2');
show status like 'rpl%'; 查看半同步复制工作工作状态
在server2上操作：
单纯的测试：
stop slave io_thread;
show slave status\G; #IO进程被关掉，slave无法同步信息
start slave io_thread; #启动后可同步信息
当然在server2上也需要安装相应的模块：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #server2既是master主机又是slave主机 需要安装两个模块
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%semi%';
和server1一样，需要启动模块：
因为server2既是server又是master,都需要开启
SET GLOBAL rpl_semi_sync_slave_enabled =1; #启动模块
SET GLOBAL rpl_semi_sync_master_enabled =1;
show variables like 'rpl%';
show status like 'rpl%';
然后在server3上与前面1，2一样：
安装模块：
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%semi%';
开启模块：
SET GLOBAL rpl_semi_sync_slave_enabled =1;
stop slave io_thread;
start slave io_thread;
select * from westos.user;
四.mysql的组复制
组由多个服务器构成，通过传递消息进行交互，通信层保证原子消息传递。MGR构建于此通信层抽象之上，并实现了多主更新复制协议。组中的每个服务器独立地执行事务，但是所有读写事务只有在得到组的批准后才会提交。只读事务在组内不需要协调，因此立即提交。对于任何读写事务，当事务准备好在始发服务器处提交时，服务器以原子方式广播写入值（更改的行）和对应的写入集（更新的行的唯一标识符），然后将该事务加入全局事务列表。最终所有服务器都以相同的顺序接收并应用相同的事务集，所以它们在组内保持一致。
组复制协议：
首先对于server1：
初始化mysql
先关闭运行的mysql
/etc/init.d/mysqld stop
rm -fr /data/mysql/*
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql -p
初始化成功并进入mysql
修改密码
alter user root@localhost identified by 'westos';
修改配置文件：
vim /etc/my.cnf
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= "172.25.1.1:33061"
group_replication_group_seeds= "172.25.1.1:33061,172.25.1.2:33061,172.25.1.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.1.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
重启服务
/etc/init.d/mysqld restart
安装模块并查看
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;
关闭二进制文件写入，创建用户并且授权，完成后代开二进制文件写入
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos'
FOR CHANNEL 'group_replication_recovery';
FLUSH PRIVILEGES;
紧接着开始master主机的启动：
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF; # server1启动必须三步
关键一部：查看模块状态，若显示online才表示成功！
SELECT * FROM performance_schema.replication_group_members;
server1完成，那么对于server2和server3:和前面server1一样
先关闭mysql
将server1刚写好的配置my.cnf直接扔到server2上去：
scp /etc/my.cnf server2:/etc/
scp /etc/my.cnf server3:/etc/
然后把文件修改一下和server1修改方式一样，ip即可
vim /etc/my.cnf
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= "172.25.1.2:33061"
group_replication_group_seeds= "172.25.1.1:33061,172.25.1.2:33061,172.25.1.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.1.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
接着和前面一样，初始化数据库
/etc/init.d/mysqld stop
rm -fr /data/mysql/*
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql -p
alter user root@localhost identified by 'westos';
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos'
FOR CHANNEL 'group_replication_recovery';
开启模块
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members; # 查看
如果看到的server2不是online而是recovering
那么需要在server2：
直接重置master,slave
STOP GROUP_REPLICATION;
reset master;
reset slave;
START GROUP_REPLICATION;
在server1中：
STOP GROUP_REPLICATION;
reset master;
reset slave;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
在server1如下：
在server2操作如下：
再次查看
SELECT * FROM performance_schema.replication_group_members;
server3的操作与server2原理相同：
vim /etc/my.cnf
/etc/init.d/mysqld stop
rm -fr /data/mysql/*
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql -p
alter user root@localhost identified by 'westos';
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos'
FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members; # 查看
若是出问题，上面reset的方式不能解决
一般server2都能通过reset的方式解决！
不能解决的唯一原因就是前面指令敲错了，或者/etc/my.cnf：：ip记得写对！！
不然就是server3出问题，1,2都可以online，但是文件都写对了！
那么需要在server3执行：
SET GLOBAL group_replication_bootstrap_group=ON;
然后
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
现在在server3上查看只有一个server3是online！！
让其他的server1,2加入进来即可
server2
stop GROUP_REPLICATION;
reset master;
reset slave;
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
然后发现只有1，3在线
然后在把server3
stop GROUP_REPLICATION;
reset master;
reset slave;
START GROUP_REPLICATION;
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> INSERT INTO t1 VALUES (1, 'Luis');
就可以在任意三个虚拟机中一个看到数据：
show databases;
use test;
show tables;
那么我们可以再三台虚拟机任意一台中做增删改查，三台机子都同步完成！！！
五.mysql路由器
这里需要再准备一台虚拟机server4
qemu-img create -f qcow2 -b base.qcow2 vm4
ls
server4:
yum install -y mysql-router-community-8.0.21-1.el7.x86_64.rpm
安装成功后，配置主配置文件
vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations=172.25.1.1:3306,172.25.1.2:3306,172.25.1.3:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations=172.25.1.3:3306,172.25.1.2:3306,172.25.1.1:3306
routing_strategy = first-available
启动服务并且查看端口
systemctl start mysqlrouter.service
netstat -antlp # 查看7001端口和7001端口是否已经打开
server1中授权用户sxm用于查看test库的所有表文件
grant all on test.* to sxm@'%' identified by 'westos';
真实主机连接server4 通过server4 连接7001 和 7002端口
当访问7001
server1:
yum install -y lsof
lsof -i :3306
当访问7002
server3
yum install -y lsof
lsof -i :3306
247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
