学习预览:
(一)mysql 运维基础篇(Linux云计算从入门到精通)
(三)mysql 触发器、存储过程和函数(数据库运维基础补充)
(五)mysql数据备份—物理备份(完备+lvm快照+xtrabackup)+逻辑备份(mysqldump+导入导出)
(六)mysql复制技术—M-S主从配置(传统+GTID)+M-M-S-S主从配置(GTID)
(七)mysql中间件mycat配置和部署(基于M-M-S-S)
(八) 数据库集群技术—Galera Cluster安装与配置
(九)数据库集群技术Galera+mycat(数据库运维学习终章)
CONTEND
6.1 MySQL复制技术概述
●Primary-Secondary Replication(主从复制)
●Group Replication
(1) 注:复制技术不等于备份,它可以备份,但它主要用于灾备,负载均衡,主要功能如下:
- 实时同步
- 机械故障
- 远程灾备
- 用于备份
- 高可用HA
- 负载均衡、读写分离、分布式数据库
(2) 常用的复制技术一主一从,一主多从,多主多从。
- M
- M-S
- M-S-S
- M-M
- M-M-S-S多源复制
(3)复制原理
1.在主库上把数据更改( DDL DML DCL)记录到二进制日志( BinaryLog)中。
2.备库I/O线程将主库上的日志复制到自己的中继日志( RelayLog)中。
3.备库SQL线程读取中继日志中的事件, 将其重放到备库数据库之上。
6.2 M-S主从配置(传统)
我们需要两台机器,一主一从,我的机器情况如下,前期我们要给两台机器进行初始化,安装mysql,开放防火墙mysql端口(内网其实可以直接关掉防火墙的),配置主机名以及添加主机解释,这个我就不做说明了哦!进入重点!
主机解释文件也放一下把:
vim /etc/hosts
192.168.119.159 master1
192.168.119.160 master2
(1)修改my.cnf文件,修改完后重启mysqld。
#master1 ,要配置binlog日志,他们配置server-id主要为了区别他两,防止master1更新,master2更新,导致master1接着更新
vim /etc/my.cnf
log_bin
server-id=59
#master2
vim /etc/my.cnf
server-id=60
转存失败重新上传取消
(2)master1配置
#先创建数据库、表格并插入数据
mysql> create database testdb;
mysql> create table testdb.t1(id int,name varchar(50));
mysql> insert into testdb.t1 values (1,'jack');
mysql> insert into testdb.t1 values (2,'tom');
mysql> insert into testdb.t1 values (3,'lucy');
#将现有的数据库全区备份并传到master2上
[root@master1 ~]# mysqldump -pRoot@123 --all-databases --single-transaction --master-data=1 --flush-logs > `date +%F`-mysql-all.sql
[root@master1 ~]# scp -r 2020-04-18-mysql-all.sql master2:/root
#重新插入数据检查后面slave是否生效
mysql> insert into testdb.t1 values (4,'danie');
mysql> insert into testdb.t1 values (5,'tony');
mysql> insert into testdb.t1 values (6,'lucyine');
#下面三天是重点
mysql> create user 'master2'@'192.168.119.160' identified with mysql_native_password by 'Root@123';
mysql> grant replication slave on *.* to 'master2'@'192.168.119.160';
mysql> flush privileges;
转存失败重新上传取消
(3)master2配置
#先恢复数据,与master1保持同步
mysql> set sql_log_bin=0;
mysql> source /root/2020-04-18-mysql-all.sql
mysql> select * from testdb.t1; #这是查看只有三行数据
+------+---------+
| id | name |
+------+---------+
| 1 | jack |
| 2 | tom |
| 3 | lucy |
+------+---------+
##这边master1是master1的主机名,也可以是IP或者域名,master2是用户名而已,binlog可以通过show master status查看,也可以在之前备份的SQL文件中查看
mysql> change master to master_host='master1',master_user='master2',master_password='Root@123',MASTER_LOG_FILE='master1-bin.000003',MASTER_LOG_POS=155;
mysql> show variables like 'server_id'; #查看serverid是否与my.cnf一致且与master1不同
mysql> start slave; #开启slave
mysql> show slave status; #查看slave状态,最后两栏都是yes才行
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: master2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000003
Read_Master_Log_Pos: 2843
Relay_Log_File: master2-relay-bin.000002
Relay_Log_Pos: 3012
Relay_Master_Log_File: master1-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> select * from testdb.t1; #配置好slave之后数据就同步了
---------+
| id | name |
+------+---------+
| 1 | jack |
| 2 | tom |
| 3 | lucy |
| 4 | danie |
| 5 | tony |
| 6 | lucyine |
+------+---------+
转存失败重新上传取消
6.3 M-S主从配置(GTID)
GTID (Global Transaction Identifiers)是对于一个已提交事务的编号,事务的唯一编号,并且是一个全局唯一的编号。GTID 和事务会记录到 binlog 中,用来标识事务。GTID 是用来替代以前 传统的复制方法,MySQL-5.6.2 开始支持 GTID,在 MySQL-5.6.10 后完善。有了 GTID,一个事务在集群中就不再孤单,在每一个节点中,都存在具有相同标识符的兄弟们和它作伴,可以避免同一个事务,在同一个节点中出现多次的情况。GTID 的出现,最直接的效果就是,每一个事务在集群中具有了唯一性的意义,这在运维方面具有更大的意义,因为使用 GTID 后再也不需要为了不断地找点而烦恼了,给 DBA 带来了很大的便利性。
下面我们开始配置M-S流程GTID:
(1)依旧还是前面的两台主机,我直接初始化,初始化的过程就不细说了,依旧是从干净的环境开始做起。
下面是两个主机的my.cnf文件的配置:
#master1
vim /etc/my.cnf
log_bin
server-id=59
gtid_mode=on
enforce_gtid_consistency=1
#master2,最后两个根据需要添加,我就不用了。
vim /etc/my.cnf
log-bin
server-id=60
gtid_mode=on
enforce_gtid_consistency=1
#master-info-repository=table
#relay-log-info-repository=table
转存失败重新上传取消
(2)mater1上面进行的操作:先创建数据,插入数据,然后备份并复制到master2上,保持两边表的结构一致
#master1先创建数据库、表格并插入数据
mysql> create database testdb;
mysql> create table testdb.t1(id int,name varchar(50));
mysql> insert into testdb.t1 values (1,'jack');
mysql> insert into testdb.t1 values (2,'tom');
mysql> insert into testdb.t1 values (3,'lucy');
#将现有的数据库全区备份并传到master2上
[root@master1 ~]# mysqldump -pRoot@123 --all-databases --single-transaction --master-data=1 --flush-logs > `date +%F`-mysql-all.sql
[root@master1 ~]# scp -r 2020-04-18-mysql-all.sql master2:/root
#mater1的slave的主要配置
mysql> create user 'master2'@'192.168.119.160' identified with mysql_native_password by 'Root@123';
mysql> grant replication slave on *.* to 'master2'@'192.168.119.160';
mysql> flush privileges;
转存失败重新上传取消
(3)master2上进行的操作:
#测试能否登陆并查看用户权限
mysql -hmaster1 -umaster2 -pRoot@123
mysql> show grants;
+---------------------------------------------------------------+
| Grants for master2@192.168.119.160 |
+---------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `master2`@`192.168.119.160` |
+---------------------------------------------------------------+
#开始恢复,与master1保持初始状态同步
mysql -pRoot@123 < 2020-04-18-mysql-all.sql
mysql -pRoot@123 #登陆上去
#下面是master2的slave的主要配置
mysql> change master to
-> master_host='master1', #这边master1是master1的主机名,也可以是IP或者域名
-> master_user='master2', #这边master2是用户名而已
-> master_password='Root@123',
-> master_auto_position=1;
mysql> start slave;
mysql> show slave status\G #最后两栏是yes就OK咯
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: master2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000004
Read_Master_Log_Pos: 899
Relay_Log_File: master2-relay-bin.000002
Relay_Log_Pos: 1077
Relay_Master_Log_File: master1-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
转存失败重新上传取消
6.4 M-M-S-S主从配置(GTID)
接着我们来进行M-M-S-S实验。两主两从,简单拓扑如下,slave可多台,实际生产环境根据需要设计,我这边就用四台虚拟机来模拟。刚开始准备四台虚拟机,都安装了同一个版本的mysql,我的是mysql8。初始化mysql环境,以便于实验进行。(防火墙可直接关闭,selinux设置disabled)
#还是提醒下,每个主机都要添加主机解释文件,如果有dns的话就另算了,还是主机解释方便
vim /etc/hosts
192.168.119.163 master1
192.168.119.160 master2
192.168.119.161 slave1
192.168.119.162 slave2
转存失败重新上传取消
(1)master1
master1上面进行操作最多,要修改my.cnf,还要授权账号还有和master2互为备份:
#master1先创建数据库、表格并插入数据(模拟数据库已经运行一段时间)
mysql> create database testdb;
mysql> create table testdb.t1(id int,name varchar(50));
mysql> insert into testdb.t1 values (1,'jack');
mysql> insert into testdb.t1 values (2,'tom');
mysql> insert into testdb.t1 values (3,'lucy');
#清空日志,每台机器上都要做保持日志同步,这个很重要的!
mysql> reset master;
#开始备份并传送到其他三台主机
mysqldump -pRoot@123 --all-databases --single-transaction --master-data=1 --flush-logs > `date +%F`-mysql-all.sql
scp -r 2020-04-19-mysql-all.sql master2:/root
scp -r 2020-04-19-mysql-all.sql slave1:/root
scp -r 2020-04-19-mysql-all.sql slave2:/root
#创建账号并授权,包括自己的哦
mysql> create user 'rep'@'192.168.119.163' identified with mysql_native_password by 'Master@123';
mysql> create user 'rep'@'192.168.119.160' identified with mysql_native_password by 'Master@123';
mysql> create user 'rep'@'192.168.119.161' identified with mysql_native_password by 'Slave@123';
mysql> create user 'rep'@'192.168.119.162' identified with mysql_native_password by 'Slave@123';
mysql> grant replication slave on *.* to 'rep'@'192.168.119.163';
mysql> grant replication slave on *.* to 'rep'@'192.168.119.160';
mysql> grant replication slave on *.* to 'rep'@'192.168.119.161';
mysql> grant replication slave on *.* to 'rep'@'192.168.119.162';
#配置与master2互主
mysql> change master to master_host='master2',master_user='rep',master_password='Master@123',master_auto_position=1;
#开启slave
mysql> start slave;
#查看状态,master2还没配置当然就还没起来,配置好master2再重新start slave就好了
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
转存失败重新上传取消
(2)master2
vim /etc/my.cnf
log_bin
server-id=60
gtid_mode=on
enforce_gtid_consistency=1
#先导入master1原有的数据库,保持数据库的一致性
mysql -pRoot@123 < 2020-04-19-mysql-all.sql
mysql -pRoot@123
#清空日志
mysql> reset master;
#配置与master1互备
mysql> change master to master_host='master1',master_user='rep',master_password='Master@123',master_auto_position=1;
#开启slave
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
转存失败重新上传取消
(3)slave1
vim /etc/my.cnf
server-id=61
gtid_mode=on
enforce_gtid_consistency=1
master-info-repository=table
relay-log-info-repository=table
#同步数据
mysql -pRoot@123 < 2020-04-19-mysql-all.sql
mysql -pRoot@123
#清空日志
mysql> reset master;
#设置master,需要设置master1和master2,channel频道后面是自己设置的名字
mysql> change master to master_host='master1',master_user='rep',master_password='Slave@123',master_auto_position=1 for channel 'slave_master1';
mysql> change master to master_host='master2',master_user='rep',master_password='Slave@123',master_auto_position=1 for channel 'slave_master2';
#开启slave
mysql> start slave;
mysql> show slave status\G
转存失败重新上传取消
(4)slave2
vim /etc/my.cnf
server-id=62
gtid_mode=on
enforce_gtid_consistency=1
master-info-repository=table
relay-log-info-repository=table
#同步数据
mysql -pRoot@123 < 2020-04-19-mysql-all.sql
mysql -pRoot@123
#清空日志
mysql> reset master;
#设置master,需要设置master1和master2
mysql> change master to master_host='master1',master_user='rep',master_password='Slave@123',master_auto_position=1 for channel 'slave_master1';
mysql> change master to master_host='master2',master_user='rep',master_password='Slave@123',master_auto_position=1 for channel 'slave_master2';
mysql> start slave;
mysql> show slave status\G
转存失败重新上传取消
(5)测试一下M-M-S-S是否生效
#在master1上面插入数据
mysql> insert into testdb.t1 value (4,'master1');
#在master2上面插入数据
mysql> insert into testdb.t1 value (5,'master2');
#在四台机器上查看testdb.t1表,如果都有如下数据,则成功
mysql> select * from testdb.t1;
+------+---------+
| id | name |
+------+---------+
| 1 | jack |
| 2 | tom |
| 3 | lucy |
| 4 | master1 |
| 5 | master2 |
+------+---------+
5 rows in set (0.00 sec)
转存失败重新上传取消
(6)问题总结
如果出现Slave_IO_Running: No(connecting) Slave_SQL_Running: No(Connecting),根据error信息排查。具体排错步骤:
- 检查防火墙有没有关掉;
- host解释是否正确(是否与IP地址对应,可以ping看看);
- 查看binlog是否都一致,show master status;如果不一致可以重新设置,具体网上也有教程,因为我这个是新环境,就直接reset master了;
- 查看授权账号和密码是否输入有误,可以直接用授权账号登录测试,能登上去则没问题;
- 上述都没有问题的建议stop slave; reset slave; start slave; 在重新看一下slave的状态哦!