mysqldump 备份数据库

1.单库备份

查看数据库

[root@leanote ~]# mysql -uroot -p -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+

备份数据库

[root@leanote ~]# mysqldump -uroot -p test_db >test_db.sql
Enter password: 
[root@leanote ~]# ls
公共  模板  视频  图片  文档  下载  音乐  桌面  anaconda-ks.cfg  initial-setup-ks.cfg  test_db.sql
[root@leanote ~]# 

注意：在恢复上述备份文件时必须事先建立数据库，否则无法恢复数据，解决方法是执行备份操作时加上 “-B” 参数。

mysqldump -uroot -p -B test_db >test_db.sql

多库备份

mysqldump -uroot -p -B test_db mysql >data_db.sql

分库备份

分库备份是为了解决多库备份在同一个备份文件造成的问题，分库备份实质也是针对单库进行备份，由脚本实现

#!/bin/sh
#This scripts is for auto backup databases
#create by spf at 2021-09-10
MYSQL_CMD=/usr/bin/mysqldump
MYSQL_USER=root
MYSQL_PWD=!QAZ2wsx
DATA=`date +%F`
DBname=`mysql -u${MYSQL_USER} -p'!QAZ@WSX' -e "show databases;" | sed '1,5d'`

for DBname in ${DBname}
do
        ${MYSQL_CMD} -u${MYSQL_USER} -p'${MYSQL_PWD}' --compact -B ${DBname} | gzip >/backup/${DBname}_${DATA}.sql.gz
done

表备份

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| table1            |
+-------------------+
1 row in set (0.00 sec)

[root@leanote ~]# mysqldump -uroot -p test_db table1 > /backup/test_db_table1_bak.sql
Enter password:
[root@leanote ~]# ll /backup/
总用量 8
-rw-r--r--. 1 root root  298 9月  10 11:27 test_db.sql.gz
-rw-r--r--. 1 root root 1842 9月  10 13:47 test_db_table1_bak.sql

表结构备份

[root@leanote ~]# mysqldump -uroot -p -d test_db table1 >/backup/table1.sql
Enter password: 
[root@leanote ~]# ll /backup/
总用量 12
-rw-r--r--. 1 root root 1663 9月  10 13:50 table1.sql
-rw-r--r--. 1 root root  298 9月  10 11:27 test_db.sql.gz
-rw-r--r--. 1 root root 1842 9月  10 13:47 test_db_table1_bak.sql

不同数据库引擎备份注意事项

MyISAM引擎

由于MyISAM引擎为表级锁，因此，为了防止数据写入造成数据不一致的情况，需要在备份时使用“--lock-all-tables”参数进行锁表操作。

[root@leanote ~]# mysqldump -uroot -p --lock-all-tables --compact test_db|gzip >test_db.sql.gz

InnoDB引擎

由于InnoDB引擎为行锁，因此进行数据库备份时可以不对库执行锁操作，可以使用“--single-transaction”参数来保持数据一致性。

[root@leanote ~]# mysqldump -uroot -p --single-transaction -B -A -F test_db|gzip >test_db.sql.gz

MySQL数据库恢复

使用source命令

使用source命令恢复，首先需要正常登录数据库，然后通过命令行进行恢复操作。

mysql> source ~/data_db.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命令

[root@leanote ~]# mysql -uroot -p <data_db.sql