MySQL数据库备份实战-mysqldump

前言

MySQL数据库自带的一个很好用的备份命令。是逻辑备份，导出 的是SQL语句。也就是把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程。

1. 备份整个数据库

[root@hecs-x-medium-2-linux-20200611091300 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3512
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 |
| redmine_production |
| sys                |
| xuqy               |
+--------------------+
6 rows in set (0.01 sec)

现在我们要备份的数据库有xuqy数据库，mysql数据库

进入到保存备份数据的目录

mkdir -p /backup/
cd /backup

开始备份

mysqldump -uroot -p xuqy >xuqy202008011126.sql
mysqldump -uroot -p mysql > mysql202008011126.sql

查看备份好的数据：

[root@hecs-x-medium-2-linux-20200611091300 backup]# ll
total 1228
-rw-r--r-- 1 root root 1251172 Aug  1 11:38 mysql202008011126.sql
-rw-r--r-- 1 root root    1887 Aug  1 11:39 xuqy202008011126.sql

2. 备份多个数据库

mysqldump -uroot -p --databases xuqy  mysql > back20200801.sql

3. 备份所有数据库

 mysqldump -uroot -p --all-databases > databaseall-20200801all.sql

查看备份

[root@hecs-x-medium-2-linux-20200611091300 backup]# ll -h
total 3.4M
-rw-r--r-- 1 root root 1.2M Aug  1 11:42 back20200801.sql
-rw-r--r-- 1 root root 975K Aug  1 11:43 databaseall-20200801all.sql
-rw-r--r-- 1 root root 1.2M Aug  1 11:38 mysql202008011126.sql
-rw-r--r-- 1 root root 1.9K Aug  1 11:39 xuqy202008011126.sql

4.备份恢复

#登陆新库创建新的空库xuqytest
create database xuqytest;
#导入
mysql -uroot -p123456 xuqytest < xuqy20210606.sql;