1、安装主 Mysql 优化命令创建数据和日志存储目录
1）安装 Mysql
root@centos05 ~]# tar zxf mysql-8.0.32-el7-x86_64.tar.gz -C /usr/src/
[root@centos05 ~]# mv /usr/src/mysql-8.0.32-el7-x86_64/ /usr/local/mysql
2）创建管理 Mysql 用户
[root@centos05 ~]# groupadd mysql
[root@centos05 ~]# useradd -M -s /sbin/nologin -g mysql mysql
3）优化 Mysql 命令
[root@centos05 ~]# echo "PATH=$PATH:/usr/local/mysql/bin/" >> /etc/profile
[root@centos05 ~]# source /etc/profile
4）创建数据和日志存储目录
[root@centos05 ~]# mkdir /usr/local/mysql/date
[root@centos05 ~]# mkdir /usr/local/mysql/mysql-bin/
[root@centos05 ~]# chown -R mysql:mysql /usr/local/mysql/
5）配置 hosts 文件
[root@centos05 ~]# vim /etc/hosts
192.168.100.50 centos05
192.168.100.60 centos06
6）挂载系统光盘 yum 安装时间服务器
[root@centos05 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
[root@centos05 ~]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos05 ~]# cat /etc/yum.repos.d/local.repo
[local]
name=centos
baseurl=file:///mnt
enabled=1
gpgcheck=0
[root@centos05 ~]# yum -y install ntp
4）修改时间服务器配置文件
[root@centos05 ~]# vim /etc/ntp.conf
18 restrict 192.168.100.0 mask 255.255.255.0 nomodify notrap
19 server 127.127.1.0
20 fudge 127.127.1.0 stratum 85）重新启动服务设置开启自动启动
[root@centos05 ~]# systemctl restart ntpd
[root@centos05 ~]# systemctl enable ntpd
6）从库和延时从库同步时间
[root@centos06 ~]# ntpdate 192.168.100.50
2 Mar 17:15:21 ntpdate[9744]: adjust time server 192.168.100.50 offset -0.010884
sec
[root@centos07 ~]# ntpdate 192.168.100.50
2 Mar 17:15:28 ntpdate[3350]: adjust time server 192.168.100.50 offset -0.007429
sec
2、配置生成 Mysql 服务
1）生成 mysql 服务
[root@centos05 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos05 ~]# chmod +x /etc/init.d/mysqld
2）添加系统服务设置开机自动启动
[root@centos05 ~]# chkconfig --add mysqld
[root@centos05 ~]# chkconfig --level 35 mysqld on
3、初始化主 Mysql 启动 Mysql 服务
1）修改主 Mysql 数据库配置文件
[root@centos05 ~]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/log-bin=/usr/local/mysql/mysql-bin/mysql-bin
//二进制日志格式文件
socket=/tmp/mysql.sock
port=3306
secure-file-priv=/tmp
binlog_format=row //二进制日志格式
gtid-mode=on //开启GTID数据复制
enforce-gtid-consistency=true //强制GTID一致性
log_replica_updates=1
server-id=10
2）初始化 Mysql 数据库
[root@centos05 ~]# mysqld --initialize-insecure
2023-02-28T19:22:05.391704Z 0 [System] [MY-013169] [Server]
/usr/local/mysql/bin/mysqld (mysqld 8.0.32) initializing of server in progress as
process 3760
2023-02-28T19:22:05.409568Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization
has started.
2023-02-28T19:22:06.394948Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization
has ended.
2023-02-28T19:22:08.151516Z 6 [Warning] [MY-010453] [Server] root@localhost is
created with an empty password ! Please consider switching off the --initialize
insecure option.
3）启动 Mysql 服务
[root@centos05 ~]# systemctl start mysqld
[root@centos05 ~]# netstat -anptu | grep 3306
tcp6 0 0 :::33060 :::* LISTEN 4549/mysqldtcp6 0 0 :::3306 :::* LISTEN 4549/mysqld
二、配置备份 Mysql
1、安装主 Mysql 优化命令创建数据和日志存储目录
1）安装 Mysql
[root@centos06 ~]# tar zxf mysql-8.0.32-el7-x86_64.tar.gz -C /usr/src/
[root@centos06 ~]# mv /usr/src/mysql-8.0.32-el7-x86_64/ /usr/local/mysql
2）创建管理 Mysql 用户
[root@centos06 ~]# groupadd mysql
[root@centos06 ~]# useradd -M -s /sbin/nologin -g mysql mysql
3）优化 Mysql 命令
[root@centos06 ~]# echo "PATH=$PATH:/usr/local/mysql/bin/" >> /etc/profile
[root@centos06 ~]# source /etc/profile
4）创建数据和日志存储目录
[root@centos06 ~]# mkdir /usr/local/mysql/date
[root@centos06 ~]# mkdir /usr/local/mysql/mysql-bin/
[root@centos06 ~]# chown -R mysql:mysql /usr/local/mysq
l/
5）配置 hosts 文件
[root@centos06 ~]# vim /etc/hosts
192.168.100.50 centos05
192.168.100.60 centos06
2、配置生成 Mysql 服务
1）生成 mysql 服务
[root@centos06 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos06 ~]# chmod +x /etc/init.d/mysqld
2）添加系统服务设置开机自动启动
[root@centos06 ~]# chkconfig --add mysqld
[root@centos06 ~]# chkconfig --level 35 mysqld on
3、初始化主 Mysql 启动 Mysql 服务
1）修改主 Mysql 数据库配置文件
[root@centos06 ~]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
log-bin=/usr/local/mysql/mysql-bin/mysql-bin
socket=/tmp/mysql.sock
port=3306
secure-file-priv=/tmp
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log_replica_updates=1
server-id=20
2）初始化 Mysql 数据库
[root@centos06 ~]# mysqld --initialize-insecure
2023-02-28T19:22:05.391704Z 0 [System] [MY-013169] [Server]
/usr/local/mysql/bin/mysqld (mysqld 8.0.32) initializing of server in progress as
process 37602023-02-28T19:22:05.409568Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization
has started.
2023-02-28T19:22:06.394948Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization
has ended.
2023-02-28T19:22:08.151516Z 6 [Warning] [MY-010453] [Server] root@localhost is
created with an empty password ! Please consider switching off the --initialize
insecure option.
3）启动 Mysql 服务
[root@centos06 ~]# systemctl start mysqld
[root@centos05 ~]# netstat -anptu | grep 3306
tcp6 0 0 :::33060 :::* LISTEN 4549/mysqld
tcp6 0 0 :::3306 :::* LISTEN 4549/mysqld
三、配置 Mysql 主从复制
1、查看主 Mysql 配置创建主从复制账户
1）查看主 Mysql 状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------
+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------
+
| mysql-bin.000002 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------
+1 row in set (0.00 sec)
2）创建主从复制账户 repl 密码为 pwd@123，允许 192.168.100.0 网络进行主从复制
mysql> create user repl@'192.168.100.%' identified with mysql_native_password by
'pwd@123';
Query OK, 0 rows affected (0.02 sec)
3）授权主从复制
mysql> grant replication slave on *.* to repl@'192.168.100.%';
Query OK, 0 rows affected (0.01 sec)
4）授权客户端监控访问 Mysql
mysql> grant replication client on *.* to repl@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)
2、从 Mysql 配置从主数据库复制数据
1）配置从主 Mysql 复制数据
mysql> change master to
master_host='192.168.100.50',master_user='repl',master_password='pwd@123',master_log
_file='mysql
bin.000002',master_log_pos=326,master_connect_retry=10,master_port=3306;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
2）启动主从复制
mysql> start replica;
Query OK, 0 rows affected, 1 warning (0.01 sec)
3）查看主从复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.100.50 Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1105
Relay_Log_File: centos06-relay-bin.000002
Relay_Log_Pos: 1274
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Server_Id: 10
Master_UUID: 479cb48a-b8db-11ed-99c9-000c29fd17be
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more
updates
Master_Retry_Count: 86400
3、验证主从复制
1）主 Mysql 创建数据
[root@centos05 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database benet;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| accp |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)mysql>
2）查看从 Mysql 复制的数据信息
[root@centos06 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| accp |
| information_schema |
| mysql |
| performance_schema |
| sys |+--------------------+
5 rows in set (0.00 sec)
mysql>
4、配置延迟从 Mysql 库
1）修改主配置文件
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
log-bin=/usr/local/mysql/mysql-bin/mysql-bin
socket=/tmp/mysql.sock
port=3306
binlog_format=row
secure-file-priv=/tmp
server-id=30
2）初始化从 Mysql
[root@centos07 ~]# mysqld --initialize-insecure
2023-03-01T21:36:33.776639Z 0 [System] [MY-013169] [Server]
/usr/local/mysql/bin/mysqld (mysqld 8.0.32) initializing of server in progress as
process 2639
2023-03-01T21:36:33.795699Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization
has started.2023-03-01T21:36:34.734804Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization
has ended.
2023-03-01T21:36:36.508271Z 6 [Warning] [MY-010453] [Server] root@localhost is
created with an empty password ! Please consider switching off the --initialize
insecure option.
3）启动服务监听端口
[root@centos07 ~]# systemctl start mysqld
[root@centos07 ~]# netstat -anptu | grep 3306
tcp6 0 0 :::33060 :::* LISTEN 2897/mysqld
tcp6 0 0 :::3306 :::* LISTEN 2897/mysqld
4）设置延迟数据库等待时间 600 秒
mysql> change master to master_delay=600;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
5）配置同步主 Mysql
mysql> change master to
master_host='192.168.100.50',master_user='repl',master_password='pwd@123',master_log
_file='mysql
bin.000003',master_log_pos=326,master_connect_retry=10,master_port=3306;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
mysql> start replica;
Query OK, 0 rows affected (0.01 sec)
6）查看延迟数据库配置
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.100.50 Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1105
Relay_Log_File: centos07-relay-bin.000003
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Server_Id: 10
Master_UUID: 479cb48a-b8db-11ed-99c9-000c29fd17be
Master_Info_File: mysql.slave_master_info
SQL_Delay: 600
SQL_Remaining_Delay: 570
Slave_SQL_Running_State: Waiting until SOURCE_DELAY seconds after source
executed event
Master_Retry_Count: 86400
7）查看没有复制到数据库等十分钟后
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| accp |
| information_schema || mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
四、传统复制修改为基于 GTID 的数据复制
1、查看数据库是否支持 GTID 复制
1）查看 GTID 模式复制是否开启
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF |
+----------------------------+
1 row in set (0.00 sec)
2）查看是否开启 GTID 复制
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF |
+-------------+
1 row in set (0.00 sec)2、配置支持 GTID 复制
1）查看主从 Mysql 数据库是否违反有一致性检查冲突，需要主从全部开启异常检查
mysql> set global enforce_gtid_consistency=warn;
2）主从库没有检查异常，设置为强制一致性，主从库必须全部开启
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| ON |
+----------------------------+
1 row in set (0.00 sec)
3）生成新事物为匿名事物，允许事物复制匿名 GTID，主从 Mysql 数据库必须全部开启
mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
4）生成新的事物日志使用 GTID，允许复制匿名的 GTID 信息，主从 Mysql 必须全部开启
mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@gtid_mode;
+---------------+
| @@gtid_mode |
+---------------+| ON_PERMISSIVE |
+---------------+
1 row in set (0.00 sec)
5）检查剩余事务信息，剩余日志 ID 为 0 继续下一步，生成新的日志，主从都配置
mysql> show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
1 row in set (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec
6）主从 Mysql 启动 GTID 复制
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.01 sec)
3、从传统复制模式修改为 GTID 复制模式
1）从库停止复制
mysql> stop replica;
Query OK, 0 rows affected (0.02 sec)
2）重新复制
mysql> change master to master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
3）启动复制
mysql> start replica;Query OK, 0 rows affected (0.01 sec)
4、验证 GTID 复制
1）主库创建数据库查看状态
mysql> create database aa;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000009 | 336 | | | fa030c45-b7aa-11ed-963f-000c29fd17be:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
2）查看从 Mysql 数据库复制的信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| accp |
| aa |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql>
3）查看从 Mysql 数据库信息
mysql> show slave status\G;*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.100.50
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 336
Relay_Log_File: centos06-relay-bin.000002
Relay_Log_Pos: 552
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Server_Id: 10
Master_UUID: fa030c45-b7aa-11ed-963f-000c29fd17be
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more
updates
Master_Retry_Count: 86400
Retrieved_Gtid_Set: fa030c45-b7aa-11ed-963f-000c29fd17be:1
Executed_Gtid_Set: fa030c45-b7aa-11ed-963f-000c29fd17b
e:15、模拟主数据库通过延迟数据库恢复数据
1）主库创建表插入数据
mysql> alter database accp charset utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> create table accp.student (姓名char(4),性别char(3),身份证号码
char(18),primary key (身份证号码));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into accp.student values ('bob','男','123456789043213567');
Query OK, 1 row affected (0.03 sec)
mysql> insert into accp.student values ('tom','男','123456789089765567');
Query OK, 1 row affected (0.01 sec)
2）从库查看数据
[root@centos06 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> mysql> stop replica;
+--------+--------+--------------------+
| 姓名 | 性别 | 身份证号码 |
+--------+--------+--------------------+
| bob | 男 | 123456789043213567 |
| tom | 男 | 123456789089765567 |
+--------+--------+--------------------+
2 rows in set (0.00 sec)
3）查看延迟库状态刚过去 286 秒
mysql> show slave status\G;标签：主从复制,Mysql,sec,usr,mysql,Master,root,GTID From： https://blog.51cto.com/u_15947624/6105486
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.100.50
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2192
Relay_Log_File: centos07-relay-bin.000003
Relay_Log_Pos: 514
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes Exec_Master_Log_Pos: 1105
Relay_Log_Space: 1983
Until_Condition: None
Master_Server_Id: 10
Master_UUID: 479cb48a-b8db-11ed-99c9-000c29fd17be
Master_Info_File: mysql.slave_master_info
SQL_Delay: 600
SQL_Remaining_Delay: 286
Slave_SQL_Running_State: Waiting until SOURCE_DELAY seconds after source
executed event
Master_Retry_Count: 86400
4）模拟主库误删除数据
mysql> drop database accp;
Query OK, 1 row affected (0.03 sec)
5）停止延迟库复制数据查看回放日志
mysql> stop replica;
Query OK, 0 rows affected (0.02 sec)
mysql> show relaylog events in 'centos07-relay-bin.000002';
6）将延迟库数据设置为 0
mysql> change master to master_delay=0;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
7）通过延迟库的日志恢复数据
mysql> start slave until sql_before_gtids='479cb48a-b8db-11ed-99c9-000c29fd17be:8';
Query OK, 0 rows affected, 1 warning (0.03 sec)
8）查看恢复数据
mysql> show databases;+--------------------+
| Database |
+--------------------+
| accp |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> desc accp.student;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------- -+----------+------+-----+---------+-------+
| 姓名 | char(4) | YES | | NULL | |
| 性别 | char(3) | YES | | NULL | |
| 身份证号码| char(18) | NO | PRI | NULL | |
+------------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from accp.student;
+---------+------+---------------------+
| 姓名 | 性别 | 身份证号 |
+---------+------+---------------------+| bob | 男 | 123456789043213567 |
| tom | 男 | 123456789089765567 |
+---------+------+---------------------+
2 rows in set (0.00 sec)