一站式mysql主从备份读写分离详细操作---gtid方式

一站式mysql主从备份读写分离---gtid方式

ps：直接使用黄色字体命令操作即可

关键字：

在Rocky Linux release 9.3 (Blue Onyx)服务器上使用 MySQL 8.0.36 版本进行主从备份并使用GTID方式的详细操作步骤：

查看用户SELECT user, host FROM mysql.user;

删除用户DROP USER 'repli_new'@'10.30.21.37';

热重载  mysqladmin -u root -p reload

看是否为readonly   SHOW VARIABLES LIKE 'read_only';

看从服务器是否成功  SHOW SLAVE STATUS\G

主从备份只需要reload

读写分离需要重启

用repli_36来登录测试读写分离 mysql -u repli_36  -h 10.30.21.35 -p

Enter password:

一，服务器方面操作

1,在主服务器上开启GTID：

编辑MySQL配置文件  vi   /etc/my.cnf，添加以下配置：

#

# This group is read both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

server_id=1  # 主服务器的唯一ID

enforce_gtid_consistency=on  # 启用GTID模式

log-bin=mysql-bin  # 启用二进制日志

gtid_mode=on    # 开启GTID模式

# 读写分离配置

read_only=0  # 主服务器允许读写操作

2,在从服务器上配置GTID：

编辑MySQL配置文件vi /etc/my.cnf，添加以下配置：

#

# This group is read both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

server_id=2  # 主服务器的唯一ID

enforce_gtid_consistency=on  # 启用GTID模式

gtid_mode=on    # 开启GTID模式

# 读写分离配置

read_only=1  # 从服务器只许读操作

3,重启MySQL服务：

在主服务器和从服务器上分别执行以下命令，使配置生效：

复制sudo systemctl restart mysqld

请确保在执行命令时替换相应的IP地址、用户名、密码等信息。这些操作应该可以在您的Rocky Linux release 9.3 (Blue Onyx)服务器上的MySQL 8.0.36版本中正常运行

systemctl reload mysql

mysqladmin reload

mysqladmin -u root -p reload

二，mysql内操作

1，在主服务器上创建复制用户：

1.1登录主服务器的MySQL：

复制mysql -u root -p

1.2创建一个新的复制用户并为从服务器授予适当的权限：

CREATE USER 'repli_49'@'10.30.21.49' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repli_49'@'10.30.21.49';

flush privileges;

CREATE USER 'repli_36'@'10.30.21.36' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repli_36'@'10.30.21.36';

flush privileges;

2,获取主服务器的GTID信息：

在主服务器上执行以下命令，获取主服务器的GTID信息：

复制SHOW MASTER STATUS;

3,配置从服务器：

登录从服务器的MySQL：

复制mysql -u root -p

配置从服务器连接到主服务器进行复制，并使用GTID方式：

复制

CHANGE MASTER TO

MASTER_HOST='10.30.21.44',

MASTER_USER='repli_49',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1;

flush privileges;

#注意master的ip

CHANGE MASTER TO

MASTER_HOST='10.30.21.35',

MASTER_USER='repli_36',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1;

flush privileges;

4,启动复制进程：

在从服务器上执行以下命令，启动复制进程：

复制START SLAVE;

5,检查复制状态：

在从服务器上执行以下命令，检查复制状态是否正常：

复制SHOW SLAVE STATUS\G

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

这两个是yes代表主从备份成功

等一会儿让他复制完了，再测试数据库同步，需要一定时间

验证读写分离

SHOW VARIABLES LIKE 'read_only';

为on则带代表读写分离成功

6,实例验证主mysql上验证主从备份

主mysql

USE mysql;

CREATE TABLE CCCCC (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    salary DECIMAL(10, 2)

);

INSERT INTO CCCCC (id, name, salary) VALUES (1, 'Alice', 50000);

从mysql

USE mysql;

SHOW TABLES;

也出现 CCCCC即使代表成功

验证读写分离

用repli_36来登录测试读写分离 mysql -u repli_36  -h 10.30.21.35 -p

Enter password:

从mysql也写入

USE mysql;

SHOW TABLES;

INSERT INTO CCCCC (id, name, salary) VALUES (1, 'Alice', 50000);

常见报错

mysql主从备份提示errorUUID相同---master and slave have equal MySQL server UUIDs

一，mysql内生成新的uuid
MySQL []> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 5fcb144e-1745-11ef-a0bd-fefcfe4d203c |
+--------------------------------------+
1 row in set (0.001 sec)

二.服务器上更改mysql配置中的uuid后重启数据库

[root@crm-redis mysql]# vi/var/lib/mysql/auto.cnf
[auto]
#server-uuid=7c17581d-f15b-11ee-a540-fefcfe7fcbbb
server-uuid=5fcb144e-1745-11ef-a0bd-fefcfe4d203c

三，重启数据库
systemctl restart mysqld
 

辅助文件（可不看）

1，

MASTER_LOG_FILE='mysql-bin.000005',

MASTER_LOG_POS=1049;这两个参数

用于从特定位置开始复制

不加这俩参数可以自动从最新位置复制

1. /etc/my.cnf中的gtid_mode = on参数

在mysql8.0版本之后可以不加因为是自动开启gtid模式的

1，在主数据库服务器修改/etc/my.cnf配置文件

[root@docker1 prometheus-2.45.4.linux-amd64]# cat /etc/my.cnf

[client-server]

!includedir /etc/my.cnf.d

[mysqld]

#GTID

server-id=23  #主服务器id（可以修改成自己的主机，方便区分）

gtid-mode=on    #开启gtid模式

enforce-gtid-consistency=on  #强制gtid一致性，开启后对于特定create table不被支持

#binlog

log_bin=master-binlog

log-slave-updates=1

binlog_format=row  #强烈建议，其他格式可能导致数据不一致

#relay log

skip_slave_start=1

# 读写分离配置

read_only = 0  # 主服务器允许读写操作

2，重启mysql

systemctl restart mysqld

或者热重载  mysqladmin -u root -p reload

1. 在主数据库上创建一个同步账号授权给从数据库使用

CREATE USER 'read'@'10.30.21.49' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repli_49'@'10.30.21.49';

flush privileges;

CREATE USER 'read'@'10.30.21.36' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repli_49'@'10.30.21.36';

flush privileges;

4,重新开一个终端，给主数据库上读锁（避免备份途中与其他人在写入导致数据同步的不一致）

flush tables with read lock;

CHANGE MASTER TO

MASTER_HOST='10.30.21.44',

MASTER_USER='repli_49',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1;

CHANGE MASTER TO

MASTER_HOST='10.30.21.35',

MASTER_USER='read',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1;

start slave;

mysql> slave stop;                 

mysql>CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=0;  

mysql> slave start;                               

mysql> show slave status\G      

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=0;  

配置文件版本

第一代

第一代主

[root@docker1 prometheus-2.45.4.linux-amd64]# cat  /etc/my.cnf

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

#server-id = 1  # 设置主服务器的唯一ID

#log_bin = mysql-bin  # 启用二进制日志，记录所有更改操作

#binlog_format = ROW  # 选择ROW格式，记录每个更改的行数据

#gtid_mode = ON  # 启用GTID模式

#enforce-gtid-consistency = ON  # 强制GTID一致性

[mysqld]

server-id=1

log-bin = mysql-bin

gtid_mode = on

enforce_gtid_consistency=1

第一代从

[root@docker2 ~]# cat /etc/my.cnf

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

server-id=2

gtid_mode = ON

enforce_gtid_consistency=1

master-info-repository=TABLE

relay-log-info-repository=TABLE

第二代主

[root@docker1 prometheus-2.45.4.linux-amd64]# cat  /etc/my.cnf

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

#server-id = 1  # 设置主服务器的唯一ID

#log_bin = mysql-bin  # 启用二进制日志，记录所有更改操作

#binlog_format = ROW  # 选择ROW格式，记录每个更改的行数据

#gtid_mode = ON  # 启用GTID模式

#enforce-gtid-consistency = ON  # 强制GTID一致性

[mysqld]

server-id=1

log-bin = mysql-bin

gtid_mode = on

enforce_gtid_consistency=1

[root@docker1 prometheus-2.45.4.linux-amd64]# cat /etc/my.cnfba2

cat: /etc/my.cnfba2: No such file or directory

[root@docker1 prometheus-2.45.4.linux-amd64]# cat /etc/my.cnfbak2

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

#server-id = 1  # 设置主服务器的唯一ID

#log_bin = mysql-bin  # 启用二进制日志，记录所有更改操作

#binlog_format = ROW  # 选择ROW格式，记录每个更改的行数据

#gtid_mode = ON  # 启用GTID模式

#enforce-gtid-consistency = ON  # 强制GTID一致性

#[mysqld]

#server-id=1

#log-bin = mysql-bin

#gtid_mode = on

#enforce_gtid_consistency=1

[mysqld]

server-id = 1  # 主服务器的唯一ID

log_bin = /var/log/mysql/mysql-bin.log # 启用二进制日志

enforce-gtid-consistency = 1  # 启用GTID模式

gtid_mode = ON  # 启用GTID模式

# 读写分离配置

read_only = 0  # 主服务器允许读写操作

第二代从

[root@docker2 ~]#  cat /etc/my.cnfbak2

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

#[mysqld]

#server-id=2

#gtid_mode = ON

#enforce_gtid_consistency=1

#master-info-repository=TABLE

#relay-log-info-repository=TABLE

[mysqld]

server-id = 2 # 服务器的唯一ID

enforce-gtid-consistency = 1  # 启用GTID模式

relay-log = mysql-relay-bin  #指定中继日志文件的名称为mysql-relay-bin，用于记录从服务器接收到的主服务器的更改操作。

gtid_mode = ON  # 启用GTID模式

# 读写分离配置

read_only = 1 #设置从服务器为只读状态，禁止执行写操作。

第三代主

[root@docker1 prometheus-2.45.4.linux-amd64]# cat /etc/my.cnfbakwangshang

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

#GTID

server-id=23  #主服务器id（可以修改成自己的主机，方便区分）

gtid-mode=on    #开启gtid模式

enforce-gtid-consistency=on  #强制gtid一致性，开启后对于特定create table不被支持

#binlog

#log_bin=master-binlog

log-bin = mysql-bin

log-slave-updates=1

binlog_format=row  #强烈建议，其他格式可能导致数据不一致

#relay log

skip_slave_start=1

# 读写分离配置

read_only = 0  # 主服务器允许读写操作

第三代从

[root@docker2 ~]# cat /etc/my.cnfbakwangshang

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

[mysqld]

#GTID 添加以下内容

gtid_mode=on    # 控制开启/关闭GTID模式

enforce_gtid_consistency=on

server_id=30

#binlog

log-bin=mysql-bin

log-slave-updates=1

binlog-format=row  # 强制建议，其他格式可能导致数据不一致

#relay log

# 读写分离配置

read_only = 1 # 设置从服务器为只读状态，禁止执行写操作。