企业运维实战LAMP架构--mysql主从复制
前言
在之前的章节中，我们源码编译安装并初始化了mysql数据库，今天我们将详细学习mysql数据库在lamp架构中的主要应用。
主要内容包括mysql的主从复制、通过gtid实现的主从复制（一主一从、一主多从）、mysql半同步复制、mysql组复制。
一、mysql主从复制
master主机vm1：
开启mysql数据库
/etc/init.d/mysqld start
编辑mysql主配置文件，给定主机server id
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
#
# #default-character-set = utf8
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
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; #授权
查看所以用户
select * from mysql.user\G;
slave主机vm2:
需要安装mysql（可以将server1中的mysql scp到vm2中的/usr/local）
将mysql添加到环境变量中
vim ~/.bash_profile
source ~/.bash_profile 重新读取文件
创建用户mysql和数据目录/data/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
生成临时密码用于安全初始化mysql
设置脚本启动mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start #开启mysql
安全初始化mysql
mysql_secure_installation
登陆验证：
mysql -p
vim /etc/my.cnf ,添加设置从server id
重启mysql
/etc/init.d/mysqld restart
测试，
server2:
连接主机ip的mysql
CHANGE MASTER TO MASTER_HOST='172.25.9.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
mysql -h 172.25.9.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
mysqldump -p westos > dbdump.db
scp dbdump.db server2:~
server2:
导入备份文件。
mysql -pwestos westos < dbdump.db
开启slave并查看状态
start slave;
show slave status\G;
select * from westos.user;
主机master中的操作已经成功通过二进制文件复制到从机slave中。
二、Gtid实现主从复制
gtid工作原理：
若主机玛斯图尔down掉，可通过gtid_next 来恢复信息。
一主一从
特点：
高可用性、主备、热备份
操作：
master主机通过主配置文件打开gtid模式
重启mysql
/etc/init.d/mysqld restart
slave主机也编辑主配置文件打开gtid模式
/etc/init.d/mysqld restart 重启服务
slave主机关闭slave，配置连接参数，
CHANGE MASTER TO MASTER_HOST = '172.25.9.1', MASTER_USER='repl', MASTER_PASSWORD = 'westos', MASTER_AUTO_POSITION=1;
测试：
master:
INSERT INTO westos.user VALUES ('user2');
INSERT INTO westos.user VALUES ('user3');
slave:
select * from westos.user;
一主多从
通常使用与读作用大于写作用
通过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
备份westos
mysqldump -pwestos --set-gtid-purged=OFF westos > dump.sql
授权repl用户用于slave服务
grant replication slave on *.* to repl@'%' identified by 'westos';
reset master;
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
stop slave;
CHANGE MASTER TO MASTER_HOST = '172.25.9.2', MASTER_USER='repl', MASTER_PASSWORD = 'westos', MASTER_AUTO_POSITION=1;
start slave;
show slave status\G;
测试：
server1：
INSERT INTO westos.user VALUES (‘user3’);
server2、server3:
select * from westos.user;
一主二从配置成功。
三、mysql半同步复制
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%'; #查看复制
插入数据，默认等待最长响应时间为10s
INSERT INTO westos.user VALUES ('user6');
查看半同步复制工作工作状态
show status like 'rpl%'; 查看半同步复制工作工作状态
server2:
stop slave io_thread;
show slave status\G; #IO进程被关掉，slave无法同步信息
start slave io_thread; #启动后可同步信息
安装半同步复制相应模块
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%';
启动模块
SET GLOBAL rpl_semi_sync_slave_enabled =1; #启动模块
SET GLOBAL rpl_semi_sync_master_enabled =1;
show variables like 'rpl%';
show status like 'rpl%';
select * from westos.user;
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组复制
vm1：
初始化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';
编辑主配置文件
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= "172.25.4.3:33061"
group_replication_group_seeds= "172.25.4.1:33061,172.25.4.2:33061,172.25.4.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.4.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
以下操作可参考官方文档网页：https://dev.mysql.com/doc/refman/5.7/en/group-replication-launching.html
重启服务
安装模块并查看
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; # 查看
vm2、vm3：
先关闭mysql
scp server2:/etc/my.cnf /etc/
vim /etc/my.cnf
#default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
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.9.2:33061"
group_replication_group_seeds= "172.25.9.1:33061,172.25.9.2:33061,172.25.9.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.9.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
同样初始化mysql
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;
在server1中查看
SELECT * FROM performance_schema.replication_group_members; # 查看
如果recovering
server2中
STOP GROUP_REPLICATION;
reset master;
reset slave;
START GROUP_REPLICATION;
server1中：
STOP GROUP_REPLICATION;
reset master;
reset slave;
# 启动三部曲启动master组复制模块
SET GLOBAL group_replication_bootstrap_group=ON; #
START GROUP_REPLICATION; #
SET GLOBAL group_replication_bootstrap_group=OFF;
再次查看
SELECT * FROM performance_schema.replication_group_members;
server3的操作与server2相同：
最终效果：
测试：
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');
server1中创建库
server2、3中可查到
server中创建表，其余两部分能看到
server3中插入数据、其余两部分可查看到
五、mysql路由器
重新打开一台虚拟机vm4进行查询
安装查询模块
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.9.1:3306,172.25.9.2:3306,172.25.9.3:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations=172.25.9.3:3306,172.25.9.2:3306,172.25.9.1:3306
routing_strategy = first-available
启动服务并且查看端口
systemctl start mysqlrouter.service
netstat -antlp # 查看7001端口和7001端口是否已经打开
server1中授权用户hyl用于查看test库的所有表文件
真实主机连接server4 通过server4 连接7001 和 7002端口
当访问7001
server1
yum install -y lsof
lsof -i :3306
当访问7002
server3
yum install -y lsof
lsof -i :3306
246
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
