一、MySQL 的多种数据备份机制
mysqldump：这是 MySQL 自带的备份工具，通过导出 SQL 语句来备份数据库。它可以备份整个数据库、特定表或特定数据。使用命令行执行 mysqldump 命令可以生成 SQL 文件，然后可以使用该文件还原备份数据。备份命令：
# 单个数据库
mysqldump -h hostname -u username -p dbname > backup.sql
# 多个数据库
mysqldump -h hostname -u username -p --databases dbname1 dbname2 > backup.sqlMySQL Enterprise Backup：这是 MySQL 官方提供的高级备份工具【商用收费工具】，可用于备份大型数据库。它支持增量备份和并行备份，可以在运行时备份数据库，减少备份期间的停机时间。它以block级别进行并行备份，性能大大优于逻辑备份工具如mysqldump。
mysqlbackup --host=hostname --user=username --password=password --backup-dir=/path/to/backupdir backupMySQL Workbench：MySQL Workbench是一种图形化MySQL管理工具，可以进行逻辑备份和还原，支持导出SQL脚本、CSV文件和XML文件等。
物理备份：这种备份方法直接复制数据库文件，包括数据文件、日志文件等。可以使用文件系统级别的工具，如 rsync 或者文件系统快照功能来备份。
复制（主从复制 / 多主复制）：
MySQL主从复制用于将一个MySQL服务器（称为主服务器）上的数据变更同步到其他MySQL服 务器（称为从服务器）。主从复制提供了数据备份、读写分离和负载均衡等功能，以提高系统的可用性和性能。
MySQL多主复制允许在多个数据库实例之间进行双向数据同步。它的工作原理是每个数据库实例都可以充当主服务器和从服务器，可以同时接收和发送数据变更。
第三方备份工具：还有一些第三方工具可用于备份 MySQL 数据库，例如 Percona XtraBackup、MariaDB Backup 等。这些工具提供了更多高级特性，如并行备份、压缩备份等。
MySQL 提供的多种数据备份机制各有优缺点和适应场景，复制（主从复制 / 多主复制）适合我们的应用场景，多主复制比主从复制会更为复杂一些，需要考虑数据冲突等问题。在实际使用过程中，主主复制存在很多数据冲突的问题需要解决，所以这里我们选择使用主从备份机制。
二、MySQL 主从复制原理
MySQL 主从复制是一种基于日志的复制机制，用于将主服务器（Master）上的数据实时复制到一个或多个从服务器（Slave）。主从复制的原理如下：
- 主服务器将所有修改操作记录在二进制日志（Binary Log）中。这些修改可以是插入、更新或删除数据的操作。
- 从服务器连接到主服务器，并发送一个请求，请求成为主服务器的从服务器。主服务器收到请求后，将记录从服务器的信息，并开始与从服务器建立复制连接。
- 主服务器将二进制日志中的内容发送给从服务器。从服务器接收并执行这些日志中的操作，将数据修改操作反映到自己的数据库上。
- 从服务器还会定期向主服务器发送心跳信息以维持连接。如果主服务器长时间没有收到从服务器的心跳信息，就认为从服务器宕机，不再向其发送日志。
- 如果主服务器发生故障，导致无法提供服务，可以将一个从服务器提升为新的主服务器，以继续提供服务。此时，其他从服务器将切换到新的主服务器上进行复制。
通过主从复制，可以实现数据的实时复制和分布式读取，提高数据库的可用性和读取性能。此外，主从复制还可以用于备份数据，当主服务器发生故障时，可以快速切换到从服务器，减少服务停机时间。
需要注意的是，主从复制是异步的，从服务器的数据可能稍有延迟。而且主从复制只复制数据修改操作，不复制表结构的变更。如果需要同步表结构的变更，可以使用主从复制搭配其他工具，如 GTID（Global Transaction Identifier）或者基于触发器的解决方案。
三、MySQL 主主复制原理
MySQL主主复制是一种数据同步和高可用性解决方案，它能够保持多个MySQL服务器之间的数据一致性。主主复制的原理如下：
- 配置双向复制：在两台MySQL服务器上分别配置主从复制，使每台服务器都可以同时充当主服务器和从服务器。
- 生成二进制日志：当有数据更新操作（如插入、更新、删除）时，MySQL服务器会将这些操作记录在二进制日志中。
- 传递二进制日志：每台服务器将自己的二进制日志传递给另一台服务器。这可以通过网络连接实现，通常使用基于TCP/IP的复制协议。
- 应用二进制日志：每台服务器接收到对方的二进制日志后，会将这些日志应用到自己的数据库中，从而使两台服务器的数据保持一致。
- 处理冲突：在主主复制中，由于两台服务器都可以接收写操作，可能会出现冲突。为了处理冲突，MySQL提供了自动事务回滚和主键冲突检测等机制。
三、使用Docker Compose安装MySQL 主从服务器
1、环境准备
首先准备至少2台Linux服务器，一台作为MySQL主服务器，一台或者多台作为MySQL从服务器。我们这里准备两台服务器分别为：
- 192.168.0.210 (MySQL主服务器)
- 192.168.0.195 (MySQL从服务器)
2、准备MySQL文件存放目录
- 准备数据库存储目录，在两台主从服务器上分别执行一下命令。
mkdir -p /opt/container/mysql/data /opt/container/mysql/config /opt/container/mysql/slave/mysql-files
chmod -R 777 /opt/container/mysql/data /opt/container/mysql/config /opt/container/mysql/slave/mysql-files/opt/container/mysql/data 用于存放MySQL数据文件。
/opt/container/mysql/config 用于存放MySQL配置文件。
/opt/container/mysql/slave/mysql-files 用于存放MySQL数据导入/导出的数据文件存放目录。
3、MySQL主、从服务器docker-compose-mysql.yml文件
version: '3'
services:
mysql:
environment:
## root账号的密码
MYSQL_ROOT_PASSWORD: root密码
TZ: Asia/Shanghai
## 新建mysql账号
MYSQL_USER: 'mysql_user'
MYSQL_PASSWORD: mysql_user密码
MYSQL_DATABASE: 'mysql_db'
image: "docker.io/mysql:latest"
container_name: mysql
restart: always
## 映射挂载
volumes:
## 数据目录，要确保先创建好
- "/opt/container/mysql/data:/var/lib/mysql"
- "/opt/container/mysql/config/my.cnf:/etc/mysql/my.cnf"
- "/opt/container/mysql/slave/mysql-files:/var/lib/mysql-files"
- "/etc/localtime:/etc/localtime"
- "/usr/share/zoneinfo/Asia/Shanghai:/etc/timezone"
## 初始化的脚本，初始化我们存放的init.sql文件
- "./mysql:/docker-entrypoint-initdb.d/"
ports:
- "3306:3306"
command:
--max_connections=1000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--default-authentication-plugin=mysql_native_password4、在MySQL主服务器上新增配置文件
在/opt/container/mysql/config目录下新增my.cnf文件， 配置文件内容：
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
!includedir /etc/mysql/conf.d/
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
server-id=150
log-bin=/var/lib/mysql/mysql-bin
expire_logs_days=60
binlog-format=mixed
max_allowed_packet=256M
relay-log=mysql-relay
log-slave-updates
auto_increment_increment=2 #表示自增长字段每次递增的量
auto_increment_offset=1 #表示自增长字段从那个数开始5、在MySQL从服务器上新增配置文件
在/opt/container/mysql/config目录下新增my.cnf文件， 配置文件内容：
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
!includedir /etc/mysql/conf.d/
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
server-id=200
# 设置只读模式
# read_only = 1
log-bin=/var/lib/mysql/mysql-bin
expire_logs_days=60
binlog-format=mixed
max_allowed_packet=256M
relay-log=mysql-relay
log-slave-updates
auto_increment_increment=2 #表示自增长字段每次递增的量
auto_increment_offset=1 #表示自增长字段从那个数开始6、在两台MySQL主备服务器上分别执行docker-compose安装启动命令
将docker-compose-mysql.yml上传至/opt/software目录，这个目录可以自己选择，然后到目录下执行安装启动命令
docker-compose -f docker-compose-mysql.yml up -d[root@localhost software]# docker-compose -f docker-compose-mysql.yml up -d
[+] Running 13/13
⠿ mysql Pulled 40.4s
⠿ 72a69066d2fe Pull complete 14.2s
⠿ 93619dbc5b36 Pull complete 14.2s
⠿ 99da31dd6142 Pull complete 14.6s
⠿ 626033c43d70 Pull complete 14.7s
⠿ 37d5d7efb64e Pull complete 14.7s
⠿ ac563158d721 Pull complete 16.2s
⠿ d2ba16033dad Pull complete 16.2s
⠿ 688ba7d5c01a Pull complete 16.2s
⠿ 00e060b6d11d Pull complete 24.5s
⠿ 1c04857f594f Pull complete 24.5s
⠿ 4d7cfa90e6ea Pull complete 24.6s
⠿ e0431212d27d Pull complete 24.6s
WARN[0040] Found orphan containers ([nginx]) for this project. If you removed or renamed this service in your compose file, you can run this command with the --remove-orphans flag to clean it up.
[+] Running 1/1
⠿ Container mysql Started 0.3s通过docker ps命令可以看到mysql已经安装并启动成功。
[root@localhost software]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bf4e482dbc71 mysql:latest "docker-entrypoint.s…" 21 minutes ago Up 21 minutes 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql四、MySQL 主从复制配置及测试
1、登录主MySQL服务器查看配置数据
- 进入docker容器。
[root@localhost software]# docker exec -it bf4e482dbc71 bash- 通过用户名密码登录mysql账户。
root@bf4e482dbc71:/# mysql -uroot -p密码- 查看需同步的主服务器数据 show master status，在MySQL从服务器上配置时需要用到File和Position的值。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 156 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)2、登录从MySQL服务器，并配置数据同步
- 进入docker容器。
[root@localhost software]# docker exec -it b168db7981c0 bash- 通过用户名密码登录mysql账户。
root@bf4e482dbc71:/# mysql -uroot -p密码- 登录成功后，执行从主数据库同步的配置命令。
CHANGE MASTER TO master_host = '192.168.0.210',
master_port = 3306,
master_user = 'root',
master_password = '密码',
master_log_file = 'mysql-bin.000003',
master_log_pos = 156;- 启动从服务器。
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)- 使用命令 show slave status\G 查看从服务器状态, 以下两项显示Yes，表示配置成功：
Slave_IO_Running: YesSlave_SQL_Running: Yes。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.0.210
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 156
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 324
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: 156
Relay_Log_Space: 529
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: 150
Master_UUID: ce0ecbd8-667b-11ee-98e5-0242ac120003
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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)3、主从数据库复制测试
- 在MySQL主服务器，新建一个数据库my_test。
mysql> create database my_test;
Query OK, 1 row affected (0.01 sec)- 在MySQL从服务器，执行查看数据库命令，可以看到my_test数据库已经同步到MySQL从服务器。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dbwl |
| information_schema |
| my_test |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)- 在MySQL主服务器，新建一个表t_test并新增一条数据。
mysql> use my_test;
Database changed
mysql> create table t_test(id int UNSIGNED NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t_test(name) values('Test');
Query OK, 1 row affected (0.02 sec)- 在MySQL从服务器查询数据库和表数据是否同步，如果配置正常，我们可以看到，在MySQL主服务器新增的数据库和表数据，在从服务器也存在。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dbwl |
| information_schema |
| my_test |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use my_test;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_my_test |
+-------------------+
| t_test |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from t_test;
+----+------+
| id | name |
+----+------+
| 1 | Test |
+----+------+
1 row in set (0.00 sec)五、MySQL 主主复制配置及测试
MySQL 主主复制的配置和主从复制基本一样，只是需要将原本在从服务器执行的配置命令在主服务器上再执行一遍。
- 在从服务器上查看状态 show master status，在MySQL从服务器上配置时需要用到File和Position的值
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2581 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)- 在主服务器上执行同步配置命令。
CHANGE MASTER TO master_host = '192.168.0.195',
master_port = 3306,
master_user = 'root',
master_password = '密码',
master_log_file = 'mysql-bin.000003',
master_log_pos = 2581;- 在主服务器上执行start slave。
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)- 使用命令 show slave status\G 查看从服务器状态, 以下两项显示Yes，表示配置成功：
Slave_IO_Running: YesSlave_SQL_Running: Yes。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.0.195
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2581
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 324
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: 2581
Relay_Log_Space: 529
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: 200
Master_UUID: c1d65f95-667e-11ee-bc7f-0242ac120003
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
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)- 在MySQL从服务器，新建一个数据库my_test_slave
mysql> create database my_test_slave;
Query OK, 1 row affected (0.01 sec)- 在MySQL主服务器，执行查看数据库命令，可以看到my_test_slave数据库已经同步到MySQL主服务器
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dbwl |
| information_schema |
| my_test |
| my_test_slave |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)- 在MySQL从服务器的my_test_slave数据库，新建一个表t_test并新增一条数据
mysql> use my_test_slave;
Database changed
mysql> create table t_test(id int UNSIGNED NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t_test(name) values('Test');
Query OK, 1 row affected (0.02 sec)- 在MySQL主服务器查询数据库和表数据是否同步，如果配置正常，我们可以看到，在MySQL从服务器新增的数据库和表数据，在主服务器也存在。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dbwl |
| information_schema |
| my_test |
| my_test_slave |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> use my_test_slave;
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> show tables;
+-------------------------+
| Tables_in_my_test_slave |
+-------------------------+
| t_test |
+-------------------------+
1 row in set (0.00 sec)
mysql> select * from t_test;
+----+------+
| id | name |
+----+------+
| 1 | Test |
+----+------+
1 row in set (0.00 sec)- 测试在主数据库插入一条数据，然后在从数据库查询，查看是否能够同步
mysql> insert into t_test(name) values('TestMaster');
Query OK, 1 row affected (0.02 sec)- 在从数据库执行查询命令，可以看到数据也同步过来了，说明主主复制生效。
mysql> select * from t_test;
+----+------------+
| id | name |
+----+------------+
| 1 | Test |
| 3 | TestMaster |
+----+------------+
2 rows in set (0.00 sec)很多业务场景中，大多数人使用主主复制+keepalived来实现MySQL服务器的高可用，但是存在很大的问题是处理数据冲突问题，可以通过my.cnf中配置，id自增来解决：
auto_increment_increment=2 #表示自增长字段每次递增的量
auto_increment_offset=1 #表示自增长字段从那个数开始在实际业务处理中会更加复杂，所以在数据库到底是使用主从复制还是主主复制，需要根据自己的业务场景选择。
