一、主从复制及主主复制的实现
1.主从复制
1.1 环境搭建
选择两台服务器安装相同版本号的MySQL数据库
[root@master ~]# yum info mysql-server.x86_64
Last metadata expiration check: 0:02:30 ago on Sun 31 Oct 2021 10:20:58 AM CST.
Installed Packages
Name : mysql-server
Version : 8.0.26
Release : 1.module_el8.4.0+915+de215114
Architecture : x86_64
Size : 121 M
Source : mysql-8.0.26-1.module_el8.4.0+915+de215114.src.rpm
Repository : @System
From repo : appstream
Summary : The MySQL server and related files
URL : http://www.mysql.com
License : GPLv2 with exceptions and LGPLv2 and BSD
Description : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
: client/server implementation consisting of a server daemon (mysqld)
: and many different client programs and libraries. This package contains
: the MySQL server and some accompanying files and directories.
[root@slave ~]# yum info mysql-server.x86_64
Last metadata expiration check: 0:03:16 ago on Sun 31 Oct 2021 10:20:58 AM CST.
Installed Packages
Name : mysql-server
Version : 8.0.26
Release : 1.module_el8.4.0+915+de215114
Architecture : x86_64
Size : 121 M
Source : mysql-8.0.26-1.module_el8.4.0+915+de215114.src.rpm
Repository : @System
From repo : appstream
Summary : The MySQL server and related files
URL : http://www.mysql.com
License : GPLv2 with exceptions and LGPLv2 and BSD
Description : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
: client/server implementation consisting of a server daemon (mysqld)
: and many different client programs and libraries. This package contains
: the MySQL server and some accompanying files and directories.
1.2 主节点配置
在启动服务前,对主从数据库服务器进行配置
[root@master ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld] #(主服务器选项,永久生效)
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=8 #当前节点设置一个全局惟一的ID号
log-bin #启用二进制日志
启动服务,进入MySQL数据库
[root@master ~]# systemctl start mysqld.service
[root@master ~]# mysql
记录最新二进制文件及初始位置
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.000001 | 179 | No |
| master-bin.000002 | 156 | No | #从服务器备份初始位置
+-------------------+-----------+-----------+
2 rows in set (0.00 sec)
创建从服务器复制账号并赋予其相对应权限
mysql> create user repluser@'10.0.0.%'identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
从数据库服务器配置
[root@slave ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=18
log-bin
read-only #从服务器设置只读属性,确保主从数据库服务器之间数据准确性
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='master-bin.000002',
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
指定主数据库服务器,使用有复制权限的用户账号连接至主服务器,并启动复制线程
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='master-bin.000002',
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 8 warnings (0.02 sec)
启动slave进程并验证
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G #查看slave 状态
主从验证
主服务器创建库db2
从服务器读取到主服务器新增的库
2.主主复制
定义自动增长id字段的数值范围各为奇偶,确保数据id具有唯一性,遵循数据库第二范式规则。
2.1主服务器master1
[root@master1 ~]# cat /etc/my.cnf.d/mysql-server.cnf #配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=8
log-bin
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
重启数据库服务,添加复制账号
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication on *.* to repluser@'10.0.0.%';
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.18',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='master2-bin.000001',
-> MASTER_LOG_POS=705; #记录另一主服务器启动时二进制日志文件位置
mysql> start slave;
2.2主服务器2 master2
[root@master2 ~]# cat /etc/my.cnf.d/mysql-server.cnf #配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=18
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
重启数据库服务,登录数据库
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='master1-bin.000001',
-> MASTER_LOG_POS=156;
mysql> start slave; #启用服务与master1同步,自动同步获取在master1上建立的复制账号
mysql> show master logs;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| master2-bin.000001 | 705 | No |
+--------------------+-----------+-----------+
1 row in set (0.00 sec)
2.3性能效率验证
2.3.1 master1 创建库,新增表
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1
Database changed
mysql> create table t1 (id int ,name char(20));
mysql> insert t1 (name) values ('user1');
master2查询
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| NULL | user1 |
+------+-------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 18 |
+-------------+
1 row in set (0.00 sec)
2.3.2 master2 在t1表中插入数据,master1查询
mysql> select @@server_id; #master2
+-------------+
| @@server_id |
+-------------+
| 18 |
+-------------+
1 row in set (0.00 sec)
mysql> insert t1(name) values ('user2');
mysql> select * from t1; #master1
+------+-------+
| id | name |
+------+-------+
| NULL | user1 |
| NULL | user2 |
+------+-------+
2 rows in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 8 |
+-------------+
1 row in set (0.00 sec)
2.2.3 master1和master2同时插入数据
insert t1 (name) values('userX');
mysql> select * from t1; #master1和master2中t1表数据
+------+-------+
| id | name |
+------+-------+
| NULL | user1 |
| NULL | user2 |
| NULL | userX |
| NULL | userX |
+------+-------+
4 rows in set (0.00 sec)
两个节点同时插入数据,发生数据重复
二、xtrabackup实现全量+增量+binlog恢复库
准备两台服务器
数据库服务器 MySQL
备份数据库服务器:Backup_server
rpm包:percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm,选择yum安装,解决rpm包的依赖性。
2.1 全量备份
数据库服务器
mysql> show databases; #未删库前所有库
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
创建备份目录,并完全备份
[root@msyql mysql]# mkdir /backup
[root@msyql mysql]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base #父目录需提前创建
MySQL备份文件
[root@msyql mysql]# ll /backup/base/
total 60464
-rw-r----- 1 root root 475 Oct 31 16:04 backup-my.cnf
-rw-r----- 1 root root 155 Oct 31 16:04 binlog.000013
-rw-r----- 1 root root 16 Oct 31 16:04 binlog.index
drwxr-x--- 2 root root 132 Oct 31 16:04 hellodb
-rw-r----- 1 root root 3701 Oct 31 16:04 ib_buffer_pool
-rw-r