本文详细介绍了MySQL的一主多从配置，包括全备主从配置和GTID主从配置。通过设置环境、创建同步账号、配置主从数据库、进行数据备份与恢复等步骤，确保主从数据一致性。此外，还探讨了GTID主从复制的工作原理及其与传统主从复制的区别，强调了GTID在数据一致性、安全性和易用性上的优势。
mysql一主多从配置>id主从配置
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 |
+--------------------+
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
在在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@'192.168.147.20' identified by 'repl123!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.147.20';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'repl'@'192.168.147.30' identified by 'repl123!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.147.30';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置主数据库
[root@localhost ~]# vim /etc/my.cnf
在[mysql]这段后面加一下两行内容
log-bin=mysql-bin
server-id=10 //数据库服务器唯一标识符，从库的server-id值必须比主库的大
//重启服务
[root@localhost ~]# pkill mysql //杀死mysql服务，因为我做的时候停止不了mysql，所以就直接杀死mysql服务，如果可以就用systemctl restart mysqld直接重启mysql服务
[root@localhost ~]# ps -ef |grep mysql
root 232592 111089 0 19:29 pts/3 00:00:00 grep --color=auto mysql
[root@localhost ~]# service mysqld start
Starting MySQL SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)
配置从数据库
[root@localhost ~]# vim /etc/my.cnf
在[mysql]下添加以下两行
relay-log=mysql-relay-bin
server-id=20
重启从库的MySQL服务
[root@localhost ~]# pkill mysql
[root@localhost ~]# ps -ef |grep mysql
root 243680 7886 0 07:37 pts/1 00:00:00 grep --color=auto mysql
[root@localhost ~]# service mysqld start
Starting MySQL..... SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
配置并启动主从复制
在配置之前三台主机的防火墙需关闭
[root@localhost ~]# systemctl stop firewalld
mysql> change master to
-> master_host='192.168.147.10',
-> master_user='repl',
-> master_password='repl123!',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> stop slave; //停止从库
Query OK, 0 rows affected (0.02 sec)
mysql> start slave; //启动从库
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G //查看从服务器状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.147.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //这里两行都是 yes 才算成功
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
[root@localhost ~]# vim .my.cnf
在[mysql]下添加下列内容
server-id=30
relay-log=mysql-relay-bin
[root@localhost ~]# pkill mysql
[root@localhost ~]# ps -ef |grep mysql
root 285051 58117 0 07:57 pts/3 00:00:00 grep --color=auto mysql
[root@localhost ~]# service mysqld start
Starting MySQL.. SUCCESS!
//配置主从复制
mysql> change master to
-> master_host='192.168.147.10',
-> master_user='repl',
-> master_password='repl123!',
-> master_log_file='mysql-bin.000001',
->
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status \G //查看从服务器状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.147.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //这俩行显示yes才算成功
Slave_SQL_Running: Yes
Replicate_Do_DB:
测试验证
查看主从服务器数据是否同步
在主库中创建了一个名为yh的数据库
mysql> create database yh;
Query OK, 1 row affected (0.33 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yh |
+--------------------+
5 rows in set (0.00 sec)
//从库1中可以看到有yh的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yh |
+--------------------+
5 rows in set (0.14 sec)
在从库2中可以看到有yh的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yh |
+--------------------+
5 rows in set (0.20 sec)
gtid主从的配置
在主库里创一个同步账号并授权给从库
mysql> CREATE USER 'repl'@'192.168.147.20' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.147.20';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
备份之前先锁库
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
主库全备
[root@localhost ~]# mysqldump --all-databases > all.sql
[root@localhost ~]# ls
all.sql
将主库的数据传给从库
[root@localhost ~]# scp all.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:hnmkFkk7xO1t+qw9duFdJvxGY8W4Z4JcdKvbgK89IwE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.147.20' (ECDSA) to the list of known hosts.
root@192.168.147.20's password:
all.sql 100% 623KB 43.2MB/s 00:00
在从库中恢复全库的数据
[root@localhost ~]# mysql < /opt/all.sql
配置主库
[root@localhost ~]# vim /etc/my.cnf
server_id=10
gtid_mode=on
enforce_gtid_consistency=on
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
配置从库
[root@localhost ~]# vim /etc/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
server_id=20
log-bin=mysql_bin
log-slave-updates=1
binlog_format=row
skip_slave_start=1
//重启从库
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
配置主从复制
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.147.10',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl123',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.147.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes 这两行出现yes才算成功
Slave_SQL_Running: Yes
测试验证
查看主从数据库里的数据是否一致
mysql> create database yh; //在主库里创建一个名为yh的数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.00 sec)
在从库里显示有yh的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.00 sec)
gtid的主从复制
1、当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
2、binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
6、在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描
传统主从与GTID主从的区别
GTID是基于mysql生成的事务ID，由服务器ID和事务ID组成。这个ID在主库和从库上都是唯一的，这个特性可以让mysql的主从复制更加简单，一致性更可靠。
传统主从在进行主从复制时需要寻找到master_log_file和master _log_posision，而gtid主从在进行主从复制时是不用去寻找的它只需要知道端口号以及设置自动寻找就可以了。
传统主从会有延迟，可能会发生数据丢失，主从数据一致性不高。
gtid主从是连续的，不会中断，主从数据一致性高，不会丢失数据。
gtid搭建主从更简单，比传统主从更安全。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
