mysql数据库备份恢复与多实例部署

霉逝

已于 2022-07-31 17:57:02 修改

阅读量341

点赞数 1

文章标签：数据库 mysql 服务器

于 2022-07-28 23:13:18 首次发布

本文链接：https://blog.csdn.net/qq_65441164/article/details/126046073

版权

本文详细介绍了MySQL数据库的备份与恢复，包括全量、增量和差异备份及恢复方法，重点讲解了mysqldump工具的使用。此外，还阐述了如何进行多实例部署，包括创建用户、解压安装、配置环境变量、初始化数据库、配置不同实例等步骤，确保每个实例的独立运行。

摘要由CSDN通过智能技术生成

mysql数据库备份恢复与多实例部署

文章目录

mysql数据库备份恢复与多实例部署

1. mysql数据库备份与恢复

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

//备份整个数据库(全备)

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@150 ~]# ls
anaconda-ks.cfg
[root@150 ~]# mysqldump -uroot -p123456 --all-databases > all-20220728.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@150 ~]# ls
all-20220728.sql  anaconda-ks.cfg
[root@150 ~]#

//备份yunwei库的tb_course表和tb_students_info表

[root@150 ~]# mysqldump -uroot -p123456 yunwei tb_course tb_students_info > tb.mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@150 ~]# ls
all-20220728.sql  anaconda-ks.cfg  tb.mysql
[root@150 ~]#

//备份yunwei库

[root@150 ~]# mysqldump -uroot -p123456 --databases yunwei > yunwei.mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@150 ~]# ls
all-20220728.sql  anaconda-ks.cfg  tb.mysql  yunwei.mysql
[root@150 ~]#

//模拟误删yunwei数据库

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

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

mysql>

1.3 mysql数据恢复

//恢复yunwei数据库

[root@150 ~]# ls
all-20220728.sql  anaconda-ks.cfg  tb.mysql  yunwei.mysql
[root@150 ~]# mysql -uroot -p123456  < yunwei.mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@150 ~]# mysql -uroot -p123456  -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
[root@150 ~]#

//恢复wangqingge数据库的tb_students_info表和tb_course表

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> source tb.mysql;
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.00 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 (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, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0
...

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

mysql>

//模拟删除整个数据库

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.01 sec)

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

mysql>

//恢复整个数据库

[root@150 ~]# mysql -uroot -p123456 < all-20220728.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@150 ~]# mysql -uroot -p123456 -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yunwei             |
+--------------------+
[root@150 ~]#

1.4 差异备份与恢复

1.4.1 mysql差异备份

开启MySQL服务器的二进制日志功能

[root@150 ~]# vim /etc/my.cnf
[root@150 ~]# 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=10
log-bin=mysql_bin
[root@150 ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@150 ~]#

对数据库进行完全备份

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

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

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

mysql> select * from yunwei.tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
| 11 | LiMing |   22 | 男   |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

mysql>

//完全备份

[root@150 ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20220728.sql
mysqldump: [Warning] Using a p