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

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

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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                |
+--------------------+

[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搭建主从更简单,比传统主从更安全。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值