mysql一主多从的配置&gtid主从配置

最新推荐文章于 2024-08-16 17:13:19 发布

itxiaoyu_lang

最新推荐文章于 2024-08-16 17:13:19 发布

阅读量457

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/itxiaoyu_lang/article/details/116664907

版权

本文详细介绍了MySQL的一主多从配置，包括全备主从配置和GTID主从配置。通过设置环境、创建同步账号、配置主从数据库、进行数据备份与恢复等步骤，确保主从数据一致性。此外，还探讨了GTID主从复制的工作原理及其与传统主从复制的区别，强调了GTID在数据一致性、安全性和易用性上的优势。

摘要由CSDN通过智能技术生成

mysql一主多从配置&gtid主从配置

mysql一主多从的配置

环境：

主数据库

                                  centOS8
                                  IP=192.168.147.10

从数据库

                          从库1：   centOS8
                                          IP=192.168.147.20
                          从库2：   centOS8
                                           IP=192.168.147.30

确保主从数据库里的数据一致

在三个数据库中都编辑下.my.cnf配置文件,这样方便后续操作，不用每次登录mysql服务都要输入密码，只需打mysql即可。

[root@localhost ~]# vim .my.cnf

[client]
user=root
password=ZHANGde12+Jun

//查看主数据库里有哪些库
[root@localhost ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

查看从数据库里有哪些库
[root@localhost ~]# mysql -uroot -pZHANGde12+Jun -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@localhost ~]#  mysql -uroot -pZHANGde12+Jun -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

全备主库

在全备主库时需要另外打开一个终端，给数据库加锁，避免在备份的时候他人在继续往数据库里写入数据导致备份数据不一致

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
//必须在备份完后才能退出

备份主库

[root@localhost ~]# mysqldump -uroot  --all-databases > /opt/all-20210511.sql
[root@localhost ~]#  ls /opt/
all-20210511.sql  data

//将备份文件传送到从库
[root@localhost ~]# scp /opt/all-20210511.sql  root@192.168.147.20:/opt
The authenticity of host '192.168.147.20 (192.168.147.20)' can't be established.
ECDSA key fingerprint is SHA256:aW4ybFR3n/ksAmnX6A6j819WTKiMgvnk8xKYsZSaT4U.
Please type 'yes', 'no' or the fingerprint: yes
Warning: Permanently added '192.168.147.20' (ECDSA) to the list of known hosts.
root@192.168.147.20's password: 
all-20210511.sql                              100%  852KB  36.8MB/s   00:00  


[root@localhost ~]# scp /opt/all-20210511.sql  root@192.168.147.30:/opt
The authenticity of host '192.168.147.30 (192.168.147.30)' can't be established.
ECDSA key fingerprint is SHA256:TT/MVdj1mkQEbg+3ycOiGV6FDB9u7p5NhGFDdmKD0vQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.147.30' (ECDSA) to the list of known hosts.
root@192.168.147.30's password: 
all-20210511.sql                              100%  852KB   4.3MB/s   00:00

在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致

[root@localhost ~]# mysql  < /opt/all-20210511.sql   （因为在文章前写了配置文件这里就可以不用写入-uroot -p密码了）
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--