MySQL与gtid的主从配置！

最新推荐文章于 2023-06-28 17:53:03 发布

重生之我竟重生

最新推荐文章于 2023-06-28 17:53:03 发布

阅读量158

点赞数

本文链接：https://blog.csdn.net/weixin_57720070/article/details/116664546

版权

1. MySQL的一主两从模式配置！

1.1 环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.180.100	centos7/redhat7 mysql-5.7	有数据
从数据库	192.168.180.129	centos7/redhat7 mysql-5.7	无数据
从数据库	192.168.180.131	centos7/redhat7 mysql-5.7	无数据

1.2 MySQL的安装！按照上述环境的要求，安装MySQL-5.7版本！（我安装完成了，就仅仅提供安装命令，提供不了过程截图！）

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

2. 开始主从配置！

2.1 因为主数据库上有数据，所以我们要先备份数据库并还原到从数据库中，保证主从数据一样！

（查看主数据库里面存在的数据！！！！     192.168.180.100）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lzz                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from lzz.lp;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
+----+-------+------+
4 rows in set (0.00 sec)



（查看从数据库里面存在的数据！！！！      192.168.180.129 ）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


 （查看从数据库里面存在的数据！！！！      192.168.180.131）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

2.2 全备主数据库，再备份的同时再次新开一个终端，给数据上锁，再备份完成之后退出解锁！

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> 

[root@lzz ~]#  mysqldump -uroot -p123liuZEzheng! --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
[root@lzz ~]#

2.3 将主数据库的备份内容传到从数据库上！

[root@lzz ~]# scp /root/all.sql root@192.168.180.129:/opt
root@192.168.180.129's password: 
all.sql                                                100%  853KB  30.5MB/s   00:00  

[root@lzz ~]# scp /root/all.sql root@192.168.180.131:/opt
The authenticity of host '192.168.180.131 (192.168.180.131)' can't be established.
ECDSA key fingerprint is SHA256:MY9S8r/GzYnf3JOQlPTeWKTnR36jcDwfckYHvvBj92Q.
Are you sure you want to continue connecting (yes/no/[fingerprint])? y
Please type 'yes', 'no' or the fingerprint: yes
Warning: Permanently added '192.168.180.131' (ECDSA) to the list of known hosts.
root@192.168.180.131's password: 
all.sql                                                100%  853KB  41.3MB/s   00:00

2.4 查看两个从数据库，查看备份过去的是否和主数据库一致！

[root@localhost opt]# ls
all.sql  data
[root@localhost opt]# mysql -uroot -p123liuZEzheng! < /opt/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lzz                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost opt]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
+----+-------+------+
[root@localhost opt]# 







[root@cong2 local]# cd /opt
[root@cong2 opt]# ls
all.sql  data
[root@cong2 opt]# mysql -uroot -p123liuZEzheng! < /opt/all.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cong2 opt]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lzz                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@cong2 opt]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
+----+-------+------+

2.5 在主数据库创建一个同步帐号授权给从数据库！

mysql> create user 'ljj'@'192.168.180.129' IDENTIFIED BY 'ljj123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'ljj'@'192.168.180.129';
Query OK, 0 rows affected (0.00 sec)



mysql> create user 'syz'@'192.168.180.131' IDENTIFIED BY 'syz123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'syz'@'192.168.180.131';
Query OK, 0 rows affected (0.00 sec)

mysql>

2.6 配置主数据库！

[root@lzz ~]# vim /etc/my.cnf 
[root@lzz ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin
server-id=1

symbolic-links=0

log-error=/var/log/mysqld.log
 
[root@lzz ~]# systemctl restart mysqld
[root@lzz ~]# 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@lzz ~]# 





mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

2.7 配置第一个从数据库！

[root@localhost ~]# vim /etc/my.cnf 
[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=2
relay-log=mysql-relay-bin 
symbolic-links=0
log-error=/var/log/mysqld.log


[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State     Recv-Q    Send-Q       Local Address:Port       Peer Address:Port   Process    
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                0.0.0.0:111             0.0.0.0:*                 
LISTEN    0         128                   [::]:22                 [::]:*                 
LISTEN    0         5                    [::1]:631                [::]:*                 
LISTEN    0         80                       *:3306                  *:*                 
LISTEN    0         128                   [::]:111                [::]:*  

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.180.100',
    -> MASTER_USER='ljj',
    -> MASTER_PASSWORD='ljj123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.33 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.180.100
                  Master_User: ljj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000004
                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:

2.8 配置第二个从数据库！

[root@cong2 opt]# vim /etc/my.cnf 
[root@cong2 opt]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=10
relay-log=mysql-relay-bin 

symbolic-links=0

log-error=/var/log/mysqld.log
[root@cong2 opt]# systemctl restart mysqld
[root@cong2 opt]# 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         80                       *:3306                  *:*                 
LISTEN    0         128                   [::]:111                [::]:*                 
LISTEN    0         128                   [::]:22                 [::]:*                 
LISTEN    0         5                    [::1]:631                [::]:*


mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.180.100',
    -> MASTER_USER='syz',
    -> MASTER_PASSWORD='szy123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start SLAVE IO_THREAD; 
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.180.100
                  Master_User: syz
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table:

2.9 测试实验!!

mysql> select * from lp;      (在主数据库里面添加东西！)
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
+----+-------+------+
4 rows in set (0.00 sec)

mysql> insert into lp(name,age) values('hu',30),('xi',24);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from lp;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
|  5 | hu    |   30 |
|  6 | xi    |   24 |
+----+-------+------+
6 rows in set (0.00 sec)


[root@localhost ~]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |         （第一个从数据库！！！！    数据相同！）
|  3 | xie   |   23 |
|  4 | yebai |   27 |
|  5 | hu    |   30 |
|  6 | xi    |   24 |
+----+-------+------+
[root@localhost ~]# 




[root@cong2 opt]#  mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |         （第二个从数据库！！！！！ 数据也更新了！！）
|  5 | hu    |   30 |
|  6 | xi    |   24 |
+----+-------+------+
[root@cong2 opt]#

3. gtid的主从配置！

3.1 环境说明！

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.180.100	centos7/redhat7 mysql-5.7	有数据
从数据库	192.168.180.129	centos7/redhat7 mysql-5.7	无数据
从数据库	192.168.180.131	centos7/redhat7 mysql-5.7	无数据

3.2 查看数据库，保证数据的统一，若不统一，则全备主数据库，然后传到从数据库！

主！！！

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

从数据库1！！！！！

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

从数据库2！！！

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

3.3 创建远程连接并且授权！

mysql> create user 'laolin'@'192.168.180.129' IDENTIFIED BY 'll123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'ljj'@'192.168.180.129';
Query OK, 0 rows affected (0.00 sec)



mysql> create user 'laolin1'@'192.168.180.131' IDENTIFIED BY 'll123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'syz'@'192.168.180.131';
Query OK, 0 rows affected (0.00 sec)

3.3 配置主数据库！！

[root@lzz ~]# vim /etc/my.cnf 
[root@lzz ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=1
gtid_mode=on
enforce_gitd_consistency=o
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1


[root@lzz ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS



mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| master-binlog.000001 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.4 配置第一个从数据库！！

[root@localhost ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
gtid_mode=on
enforce_gtid_consistency=on
server_id=2
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

CHANGE MASTER TO
     MASTER_HOST='192.168.180.100',
     MASTER_USER='laolin',
     MASTER_PASSWORD='ll123.',
     MASTER_PORT=3306,
     MASTER_AUTO_POSITION = 1;



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.180.100
                  Master_User: laolin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Master_Log_File: master-binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table:

3.5 配置第二个从数据库！

[root@cong2 ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
gtid_mode=on
enforce_gtid_consistency=on
server_id=10
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

CHANGE MASTER TO
     MASTER_HOST='192.168.180.100',
     MASTER_USER='laolin1',
     MASTER_PASSWORD='ll123.',
     MASTER_PORT=3306,
     MASTER_AUTO_POSITION = 1;



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.180.100
                  Master_User: laolin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Master_Log_File: master-binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table:

3.6 在主数据库上插入数据查看从数据库是否更新！

主数据库！

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lzz                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from lzz.lp;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
|  5 | hu    |   30 |
|  6 | xi    |   24 |
+----+-------+------+
6 rows in set (0.01 sec)

第一个从数据库！

mysql> select * from lzz.lp;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
|  5 | hu    |   30 |
|  6 | xi    |   24 |
+----+-------+------+
6 rows in set (0.01 sec)

第二个从数据库！

mysql> select * from lzz.lp;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | gj    |   20 |
|  2 | zh    |   25 |
|  3 | xie   |   23 |
|  4 | yebai |   27 |
|  5 | hu    |   30 |
|  6 | xi    |   24 |
+----+-------+------+
6 rows in set (0.01 sec)

传统主从和gtid主从的区别
传统主从
传统主从复制主要是基于二进制日志文件位置的复制，因此主必须启动二进制日志记录并建立唯一的服务器ID，复制组中的每个服务器都必须配置唯一的服务器ID。如果您省略server-id（或者明确地将其设置为其默认值0），则主设备将拒绝来自从设备的任何连接。

gtid主从
MySQL 5.6 的新特性之一，全局事务标识符（GTID）是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致

重生之我竟重生

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL与gtid的主从配置！

1. MySQL的一主两从模式配置！ 1.1 环境说明：数据库角色 IP 应用与系统版本有无数据主数据库 192.168.180.100 centos7/redhat7 mysql-5.7 有数据从数据库 192.168.180.129 centos7/redhat7 mysql-5.7 无数据从数据库 centos7/redhat7 mysql-5.7 无数据 ...
复制链接

扫一扫