mysql数据库备份与恢复-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_58733916/article/details/126053099

本文详细介绍了MySQL数据库的备份与恢复策略，包括冷备份、温备份和热备份的概念，重点讲解了使用mysqldump工具进行全量备份、增量备份和差异备份的方法。此外，还阐述了差异备份的实现过程及恢复步骤，并提到了多实例部署的初步操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql数据库备份与恢复

文章目录

mysql数据库备份与恢复

1.数据库备份与恢复

冷备份: 数据库服务关闭，把数据库对应的数据目录下的数据文件拷贝一份物理备份不建议使用
温备份: 温备份介于热备份与冷备份之间，温备份允许MySQL服务实例继续运行，备份数据期间，温备份借助读锁机制保证备份期间，没有新的数据写入。
热备份: 数据库服务正常运行的时候，直接对数据库备份

冷备份

[root@localhost ~]# mkdir /tmp/mysql-back
[root@localhost ~]# mv /opt/data/* /tmp/mysql-back
[root@localhost ~]# ls /opt/data/
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mv /tmp/mysql-back/* /opt/data/
[root@localhost ~]# ls /opt/data/
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  localhost.localdomain.err  mysql.pid           private_key.pem  server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mysql                      performance_schema  public_key.pem   server-key.pem   yunwei
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.00 sec)

数据库备份的方式手段：

全量备份
增量备份
差异备份

1.1 数据库常用备份方案

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

1.2 mysql备份工具mysqldump

//语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

//常用的OPTIONS：

-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

–databases：指定要备份的数据库
–all-databases：备份 mysql 服务器上的所有数据库

[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use yunwei;
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_yunwei |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot -p123456 yunwei tb_course > tb_course.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[mysqldump]
user=root
password=123456
[root@localhost ~]# mysqldump --databases yunwei > yunwei.sql
[root@localhost ~]# ls
anaconda-ks.cfg  a.txt  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  password  tb_course.sql  yunwei.sql
[root@localhost ~]# mysqldump --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
[root@localhost ~]# ls
all-20220729103039.sql  anaconda-ks.cfg  a.txt  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  password  tb_course.sql  yunwei.sql
[root@localhost ~]# mysql -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use yunwei;
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_yunwei |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)
mysql> delete from tb_course where id = 1 or id = 6;
Query OK, 2 rows affected (0.03 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
4 rows in set (0.00 sec)
[root@localhost ~]# mysql -uroot -p123456 yunwei < tb_course.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> delete from tb_course where id = 1 or id = 6;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
4 rows in set (0.01 sec)

mysql> source tb_course.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database yunwei;
Query OK, 2 rows affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> source yunwei.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database yunwei;
Query OK, 2 rows affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@localhost ~]# mysql -uroot -p123456 <  yunwei.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# mysql -uroot -p123456 <  all-20220729103039.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| yunwei             |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop database yunwei;
Query OK, 2 rows affected (0.01 sec)

mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.08 sec)

mysql> source all-20220729103039.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| yunwei             |
+--------------------+
4 rows in set (0.00 sec)

1.3 差异备份与恢复

1.3.1 mysql差异备份

修改 mysql 的配置文件

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=20                    //设置服务器标识
log-bin=mysql_bin              //开启二进制日志功能
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost ~]# ss -antl
State               Recv-Q               Send-Q                             Local Address:Port                             Peer Address:Port              Process              
LISTEN              0                    128                                      0.0.0.0:22                                    0.0.0.0:*                                      
LISTEN              0                    80                                             *:3306                                        *:*                                      
LISTEN              0                    128                                         [::]:22                                       [::]:

全量备份

[root@localhost ~]# mysql_upgrade -uroot -p123456 --force
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
yunwei.tb_course                                   OK
yunwei.tb_students_info                            OK
Upgrade process completed successfully.
Checking if update is needed.
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y').sql
[root@localhost ~]# ls
all-20220729103039.sql  all-2022.sql  anaconda-ks.cfg  a.txt  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  password  tb_course.sql  yunwei.sql

增加新内容

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: yunwei

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
5 rows in set (0.01 sec)

mysql> use yunwei;
Database changed
mysql> show tables;
+------------------+
| Tables_in_yunwei |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> insert tb_course(course_name) value('biology');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
|  7 | biology     |
+----+-------------+
7 rows in set (0.00 sec)

1.3.2. mysql差异备份恢复

模拟误删数据

mysql> delete from tb_course where id = 6;
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  7 | biology     |
+----+-------------+
6 rows in set (0.01 sec)

刷新创建新的二进制日志

[root@localhost ~]# cd /opt/data
[root@localhost data]# ll
total 123000
-rw-r-----. 1 mysql mysql       56 Jul 26 22:29 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 22:29 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 22:29 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 client-key.pem
-rw-r-----. 1 mysql mysql      832 Jul 29 11:41 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:50 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:50 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 22:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:43 ibtmp1
-rw-r-----. 1 mysql mysql    36507 Jul 29 11:41 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:41 mysql
-rw-r-----. 1 mysql mysql      701 Jul 29 11:50 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 Jul 29 11:42 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Jul 29 11:41 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:41 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:41 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 26 22:29 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 22:29 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:41 sys
drwxr-x---. 2 mysql mysql      118 Jul 29 11:03 yunwei
[root@localhost data]# cat mysql_bin.index
./mysql_bin.000003
[root@localhost data]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ll
total 123004
-rw-r-----. 1 mysql mysql       56 Jul 26 22:29 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 22:29 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 22:29 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 client-key.pem
-rw-r-----. 1 mysql mysql      832 Jul 29 11:41 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:50 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:50 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 26 22:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:43 ibtmp1
-rw-r-----. 1 mysql mysql    36507 Jul 29 11:41 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:41 mysql
-rw-r-----. 1 mysql mysql      748 Jul 29 11:52 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 Jul 29 11:52 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 29 11:52 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Jul 29 11:41 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:41 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:41 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 26 22:29 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 26 22:29 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 26 22:29 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:41 sys
drwxr-x---. 2 mysql mysql      118 Jul 29 11:03 yunwei
[root@localhost data]# cat mysql_bin.index
./mysql_bin.000003
./mysql_bin.000004

恢复全量备份

mysql -uroot -p123456 < /root/all-2022.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.01 sec)

恢复差异备份

mysql> show binlog events in './mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |        20 |         123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |        20 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |        20 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |        20 |         293 | BEGIN                                 |
| mysql_bin.000003 | 293 | Table_map      |        20 |         350 | table_id: 140 (yunwei.tb_course)      |
| mysql_bin.000003 | 350 | Write_rows     |        20 |         398 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000003 | 398 | Xid            |        20 |         429 | COMMIT /* xid=483 */                  |
| mysql_bin.000003 | 429 | Anonymous_Gtid |        20 |         494 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 494 | Query          |        20 |         568 | BEGIN                                 |
| mysql_bin.000003 | 568 | Table_map      |        20 |         625 | table_id: 140 (yunwei.tb_course)      |
| mysql_bin.000003 | 625 | Delete_rows    |        20 |         670 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000003 | 670 | Xid            |        20 |         701 | COMMIT /* xid=485 */                  |
| mysql_bin.000003 | 701 | Rotate         |        20 |         748 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)
或
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003 > /opt/mysql_bin003.txt
[root@localhost data]# less /opt/mysql_bin003.txt
[root@localhost data]# mysqlbinlog --stop-position=680 /opt/data/mysql_bin.000003 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  7 | biology     |
+----+-------------+
6 rows in set (0.00 sec)

2. mysql多实例部署

//软件下载
下载二进制格式的mysql软件包

[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
--2022-07-29 01:17:19--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.2.135.207, 2600:1406:4200:287::2e31, 2600:1406:4200:297::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.2.135.207|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz [following]
--2022-07-29 01:17:22--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 23.198.216.230
Connecting to cdn.mysql.com (cdn.mysql.com)|23.198.216.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 674830866 (644M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’

mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  100%[==========================================================================================>] 643.57M  3.37MB/s    in 6m 23s  

2022-07-29 01:23:46 (1.68 MB/s) - ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’ saved [674830866/674830866]

//配置用户和组并解压二进制程序至/usr/local下
创建用户和组

[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql

解压软件至/usr/local/

[root@localhost src]# ls
debug  kernels  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/
[root@localhost src]# cd
[root@localhost ~]# ls /usr/local/
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.38-linux-glibc2.12-x86_64  sbin  share  src
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 19  2020 bin
drwxr-xr-x. 2 root root   6 May 19  2020 etc
drwxr-xr-x. 2 root root   6 May 19  2020 games
drwxr-xr-x. 2 root root   6 May 19  2020 include
drwxr-xr-x. 2 root root   6 May 19  2020 lib
drwxr-xr-x. 3 root root  17 Jun 27 15:02 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
lrwxrwxrwx. 1 root root  36 Jul 29 01:31 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 29 01:29 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Jun 27 15:02 share
drwxr-xr-x. 2 root root   6 May 19  2020 src

//修改目录/usr/local/mysql的属主属组

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll  /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 29 01:31 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/

//配置环境变量

[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# . /etc/profile.d/mysql.sh
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//创建各实例数据存放的目录

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}

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

[root@localhost ~]# ll /opt/data/
总用量 0
drwxr-xr-x 2 mysql mysql 6 5月   9 21:24 3306
drwxr-xr-x 2 mysql mysql 6 5月   9 21:24 3307
drwxr-xr-x 2 mysql mysql 6 5月   9 21:24 3308

//初始化各实例
初始化3306实例

[root@localhost local]# mysqld --initialize --user=mysql  --datadir=/opt/data/3306
2022-07-28T17:46:45.120110Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T17:46:45.452720Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T17:46:45.499471Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T17:46:45.557825Z 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: 3f740dac-0e9d-11ed-884a-000c291bd2af.
2022-07-28T17:46:45.560679Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T17:46:46.038790Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T17:46:46.038815Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T17:46:46.039569Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T17:46:46.079231Z 1 [Note] A temporary password is generated for root@localhost: C4>%s:b(Wwzz
[root@localhost local]# echo 'C4>%s:b(Wwzz' > 3306_pass

初始化3307实例

[root@localhost local]# mysqld --initialize  --user=mysql  --datadir=/opt/data/3307
2022-07-28T18:01:55.948031Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T18:01:56.346057Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T18:01:56.394940Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T18:01:56.455749Z 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: 5e640bab-0e9f-11ed-9dcf-000c291bd2af.
2022-07-28T18:01:56.457738Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T18:01:56.669641Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T18:01:56.669661Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T18:01:56.670173Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T18:01:56.696212Z 1 [Note] A temporary password is generated for root@localhost: Crf;.O8p9Ps<
[root@localhost local]# echo 'Crf;.O8p9Ps<' 3307_pass
Crf;.O8p9Ps< 3307_pass

初始化3308实例

[root@localhost local]# mysqld --initialize  --user=mysql  --datadir=/opt/data/3308
2022-07-28T18:03:21.168829Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T18:03:21.476778Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T18:03:21.540813Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T18:03:21.597809Z 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: 9123b1e3-0e9f-11ed-a008-000c291bd2af.
2022-07-28T18:03:21.599215Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T18:03:21.913592Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T18:03:21.913619Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T18:03:21.914190Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T18:03:21.974193Z 1 [Note] A temporary password is generated for root@localhost: (lWMq1q3e;OF
[root@localhost local]# echo '(lWMq1q3e;OF' 3308_pass
(lWMq1q3e;OF 3308_pass

//安装perl

[root@localhost local]# yum -y install perl
Last metadata expiration check: 0:57:02 ago on Fri 29 Jul 2022 01:10:04 AM CST.
Dependencies resolved.
......
Complete!

//配置配置文件/etc/my.cnf

[root@localhost local]# vim /etc/my.cnf
[root@localhost local]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

//启动各实例

[root@localhost local]# mysqld_multi start 3306
[root@localhost local]# mysqld_multi start 3307
[root@localhost local]# mysqld_multi start 3308
[root@localhost local]# ss -antl
State               Recv-Q               Send-Q                             Local Address:Port                             Peer Address:Port              Process              
LISTEN              0                    128                                      0.0.0.0:22                                    0.0.0.0:*                                      
LISTEN              0                    80                                             *:3306                                        *:*                                      
LISTEN              0                    80                                             *:3307                                        *:*                                      
LISTEN              0                    80                                             *:3308                                        *:*                                      
LISTEN              0                    128                                         [::]:22

//初始化密码

[root@localhost local]# cat 3306
C4>%s:b(Wwzz
[root@localhost local]# [root@localhost ~]# yum -y install ncurses-compat-libs
Last metadata expiration check: 8:21:14 ago on Fri 29 Jul 2022 01:10:04 AM CST.
Dependencies resolved.
......
Installed:
  ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                                                                                                                
Complete!
[root@localhost local]# mysql -uroot -p'C4>%s:b(Wwzz'  -S /tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38

Copyright (c) 2000, 2022, 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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

[root@localhost local]# cat 3307
Crf;.O8p9Ps<
[root@localhost ~]# mysql -p'Crf;.O8p9Ps<' -S /tmp/mysql3307.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38

Copyright (c) 2000, 2022, 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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit;
Bye

[root@localhost ~]# cat 3308
(lWMq1q3e;OF
[root@localhost ~]# mysql -p'(lWMq1q3e;OF' -S /tmp/mysql3308.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38

Copyright (c) 2000, 2022, 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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit;
Bye