我家小花儿

这个屌丝很懒，什么也没留下！

热门标签

GTID主从配置（一从一从，腾讯T3亲自讲解

作者：我家小花儿 | 2024-08-10 16:37:44

踩

gtid主从

配置主库文件

//在主数据库里创建一个同步账号授权给从数据库使用

mysql> grant replication slave on . to ‘hhr’@‘192.168.129.135’ identified by ‘hhr123!’; #无需提前创建用户，执行授权的时候会自动创建用户

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

[root@master ~]# vim /etc/my.cnf

server-id = 10 #主库的server_id必须小于从库

gtid-mode = on #开启gtid

enforce-gtid-consistency = on #强制gtid一致性

log-bin= mysql_bin #开启二进制

binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性

log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志

skip-slave-start = 1 #跳过slave复制线程

//重启MySQL服务

[root@master ~]# service mysqld restart

Shutting down MySQL… SUCCESS!

Starting MySQL. SUCCESS!

//检查gtid模式状态

mysql> show variables like ‘%gtid%’;

±---------------------------------±----------+

| Variable_name | Value |

±---------------------------------±----------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

±---------------------------------±----------+

8 rows in set (0.00 sec)

配置从库文件

[root@slave ~]# vim /etc/my.cnf

server-id = 20 #从库的server_id必须大于主库

gtid-mode = on #开启gtid

enforce-gtid-consistency = on #强制gtid一致性

log-bin = mysql_bin #开启二进制

binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性

log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志

skip-slave-start = 1 #跳过slave复制线程

//重启MySQL服务

[root@slave ~]# service mysqld restart

Shutting down MySQL… SUCCESS!

Starting MySQL. SUCCESS!

//检查gtid模式状态

mysql> show variables like ‘%gtid%’;

±---------------------------------±----------+

| Variable_name | Value |

±---------------------------------±----------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

±---------------------------------±----------+

8 rows in set (0.00 sec)

//配置并启动主从复制

mysql> change master to

-> master_host=‘192.168.129.133’,

-> master_user=‘hhr’,

-> master_password=‘hhr123!’,

-> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.01 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.129.133

Master_User: hhr

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql_bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 367

Relay_Master_Log_File: mysql_bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 154

Relay_Log_Space: 574

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 10

Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c

Master_Info_File: /opt/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

测试

//主服务器创建数据库

mysql> create database hhr;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| hhr |

| mysql |

| performance_schema |

| sys |

±-------------------+

5 rows in set (0.00 sec)

mysql> use hhr;

Database changed

mysql> create table student (id int not null primary key auto_increment,name varchar(50) not null,age tinyint);

Query OK, 0 rows affected (0.01 sec)

mysql> insert student (name,age) values(‘tom’,20),(‘jerry’,20),(‘wnagermazi’,15),(‘zhangsan’,19),(‘lisi’,25);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from student;

±—±-----------±-----+

| id | name | age |

±—±-----------±-----+

| 1 | tom | 20 |

| 2 | jerry | 20 |

| 3 | wnagermazi | 15 |

| 4 | zhangsan | 19 |

| 5 | lisi | 25 |

±—±-----------±-----+

5 rows in set (0.01 sec)

mysql> show master status;

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

1 row in set (0.00 sec)

//查看从库中的数据是否同步成功

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| hhr |

| mysql |

| performance_schema |

| sys |

±-------------------+

5 rows in set (0.01 sec)

mysql> select * from hhr.student;

±—±-----------±-----+

| id | name | age |

±—±-----------±-----+

| 1 | tom | 20 |

| 2 | jerry | 20 |

| 3 | wnagermazi | 15 |

| 4 | zhangsan | 19 |

| 5 | lisi | 25 |

±—±-----------±-----+

5 rows in set (0.00 sec)

mysql> show master status;

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

1 row in set (0.00 sec)

停止

//从库停止服务

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

//主库

mysql> insert student (name,age) values(‘qq’,20),(‘ww’,20),(‘ee’,15),(‘rr’,19),(‘lisi’,25);

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from student;

±—±-----------±-----+

| id | name | age |

±—±-----------±-----+

| 1 | tom | 20 |

| 2 | jerry | 20 |

| 3 | wnagermazi | 15 |

| 4 | zhangsan | 19 |

| 5 | lisi | 25 |

| 6 | qq | 20 |

| 7 | ww | 20 |

| 8 | ee | 15 |

| 9 | rr | 19 |

| 10 | lisi | 25 |

±—±-----------±-----+

10 rows in set (0.00 sec)

mysql> show master status;

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

1 row in set (0.00 sec)

//从库开启服务

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from hhr.student;

±—±-----------±-----+

| id | name | age |

±—±-----------±-----+

| 1 | tom | 20 |

| 2 | jerry | 20 |

| 3 | wnagermazi | 15 |

| 4 | zhangsan | 19 |

| 5 | lisi | 25 |

| 6 | qq | 20 |

| 7 | ww | 20 |

| 8 | ee | 15 |

| 9 | rr | 19 |

| 10 | lisi | 25 |

±—±-----------±-----+

10 rows in set (0.00 sec)

mysql> show master status;

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

1 row in set (0.00 sec)

一主多从

===================================================================

需求

搭建三台MySQL服务器，一台作为主服务器，一台作为从服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

| 数据库角色 | IP地址 | 应用与系统版本 |

| — | — | — |

| 主：master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |

| 从：slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |

| 从：slave2 | 192.168.129.250 | Redhat 7/ MySQL 5.7 |

安装MySQL

master

略

slave

//在slave将mysql-5.7包传输到slave2上

[root@slave ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 192.168.129.137:/usr/src/

The authenticity of host ‘192.168.129.137 (192.168.129.137)’ can’t be established.

ECDSA key fingerprint is SHA256:u29tZ8NW9v2uYAf2bAK7FOZwaxtjsxa0NVjgDTcaNTg.

Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

Warning: Permanently added ‘192.168.129.137’ (ECDSA) to the list of known hosts.

root@192.168.129.137’s password:

mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 100% 635MB 134.5MB/s 00:04

[root@slave2 ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

[root@slave2 ~]# ls /usr/local/

bin games lib libexec sbin src

etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share

[root@slave2 ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql

[root@slave2 ~]# ll -d /usr/local/mysql*

lrwxrwxrwx. 1 root root 46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

drwxr-xr-x. 9 root root 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

[root@slave2 ~]# chown -R mysql.mysql /usr/local/mysql*

[root@slave2 ~]# ll -d /usr/local/mysql*

lrwxrwxrwx. 1 mysql mysql 46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

drwxr-xr-x. 9 mysql mysql 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

root@slave2 ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH’ > /etc/profile.d/mysql.sh

[root@slave2 ~]# source /etc/profile.d/mysql.sh

[root@slave2 ~]# echo $PATH

/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root@slave2 ~]# mkdir -p /opt/data/

[root@slave2 ~]# chown -R mysql.mysql /opt/data/

[root@slave2 ~]# ll /opt/data/

total 0

[root@slave2 ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql

2021-08-31T09:15:31.711025Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2021-08-31T09:15:31.866033Z 0 [Warning] InnoDB: New log files created, LSN=45790

2021-08-31T09:15:31.889223Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2021-08-31T09:15:31.945429Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fdd2fc8c-0a3b-11ec-82ba-000c29209bda.

2021-08-31T09:15:31.947661Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.

2021-08-31T09:15:32.598937Z 0 [Warning] CA certificate ca.pem is self signed.

2021-08-31T09:15:32.652206Z 1 [Note] A temporary password is generated for root@localhost: Qq73AjyWw2%/

[root@slave2 ~]# echo ‘Qq73AjyWw2%/’ > pass

//安装依赖包

[root@slave2 ~]# yum -y install ncurses-compat-libs

//位置文件

[root@slave2 ~]# vim /etc/my.cnf

[mysqld]

port = 3306

datadir = /opt/data/

basedir = /usr/local/mysql

socket = /tmp/mysql.sock

pid-file = /opt/data/mysql.pid

user = mysql

skip-name-resolve

//配置服务启动脚本

[root@slave2 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@slave2 ~]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql

datadir=/opt/data

//启动mysql服务

[root@slave2 ~]# service mysqld start

Starting MySQL. SUCCESS!

[root@slave2 ~]# ss -antl|grep 3306

LISTEN 0 80 *:3306 :

//设置新密码

[root@slave2 ~]# mysql -uroot -p’Qq73AjyWw2%/’

mysql> set password = password(‘redhat123!’);

Query OK, 0 rows affected, 1 warning (0.00 sec)

关闭防火墙和selinux

master、slave

略

配置主库文件

略

配置从库文件

[root@slave2 ~]# vim /etc/my.cnf

server-id = 21 #从库的server_id必须大于主库

gtid-mode = on #开启gtid

enforce-gtid-consistency = on #强制gtid一致性

log-bin = mysql_bin #开启二进制

binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性

log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志

skip-slave-start = 1 #跳过slave复制线程

//重启MySQL服务

[root@slave2 ~]# service mysqld restart

Shutting down MySQL… SUCCESS!

Starting MySQL. SUCCESS!

//检查gtid模式状态

mysql> show variables like ‘%gtid%’;

±---------------------------------±----------+

| Variable_name | Value |

±---------------------------------±----------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

±---------------------------------±----------+

8 rows in set (0.01 sec)

//配置并启动主从复制

mysql> change master to

-> master_host=‘192.168.129.133’,

-> master_user=‘hhr’,

-> master_password=‘hhr123!’,

-> 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.129.133

Master_User: hhr

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql_bin.000003

Read_Master_Log_Pos: 1618

Relay_Log_File: slave2-relay-bin.000002

Relay_Log_Pos: 1831

Relay_Master_Log_File: mysql_bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1618

Relay_Log_Space: 2039

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 10

Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c

Master_Info_File: /opt/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 57d51369-0a25-11ec-8346-000c2931864c:1-6

Executed_Gtid_Set: 57d51369-0a25-11ec-8346-000c2931864c:1-6

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

测试

//从库查看

mysql> show master status;

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

±-----------------±---------±-------------±-----------------±-----------------------------------------+

1 row in set (0.00 sec)

mysql> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| hhr |

| mysql |

| performance_schema |

| sys |

±-------------------+

5 rows in set (0.00 sec)

mysql> use hhr;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from student;

±—±-----------±-----+

| id | name | age |

±—±-----------±-----+

| 1 | tom | 20 |

| 2 | jerry | 20 |

| 3 | wnagermazi | 15 |

| 4 | zhangsan | 19 |

| 5 | lisi | 25 |

| 6 | qq | 20 |

| 7 | ww | 20 |

| 8 | ee | 15 |

| 9 | rr | 19 |

| 10 | lisi | 25 |

±—±-----------±-----+

10 rows in set (0.00 sec)

多主一从

===================================================================

需求

=================================================================

搭建三台MySQL服务器，两台作为主服务器，一台作为从服务器，从服务器进行读操作

| 数据库角色 | IP地址 | 应用与系统版本 |

| — | — | — |

| 主：master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |

| 主：slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）

| 3 | wnagermazi | 15 |

| 4 | zhangsan | 19 |

| 5 | lisi | 25 |

| 6 | qq | 20 |

| 7 | ww | 20 |

| 8 | ee | 15 |

| 9 | rr | 19 |

| 10 | lisi | 25 |

±—±-----------±-----+

10 rows in set (0.00 sec)

多主一从

===================================================================

需求

=================================================================

搭建三台MySQL服务器，两台作为主服务器，一台作为从服务器，从服务器进行读操作

| 数据库角色 | IP地址 | 应用与系统版本 |

| — | — | — |

| 主：master | 192.168.129.133 | Redhat 8/ MySQL 5.7 |

| 主：slave | 192.168.129.135 | Redhat 7/ MySQL 5.7 |

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-L6EzyFJY-1710785922665)]
[外链图片转存中…(img-NfZFqYfJ-1710785922666)]
[外链图片转存中…(img-1mpBimLp-1710785922666)]

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
[外链图片转存中…(img-GpkFV1th-1710785922666)]

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/959382