如题所示,本文会记录一些关于通过【mysqldump】备份与恢复MySQL数据库的样例。
首先查看当前我的环境中的MySQL的现状:
[root@mysql-1 mysql]# rpm -qa | grep MySQL --color MySQL-shared-compat-5.5.37-1.rhel5.x86_64 MySQL-client-5.5.37-1.rhel5.x86_64 MySQL-test-5.5.37-1.rhel5.x86_64 MySQL-server-5.5.37-1.rhel5.x86_64 MySQL-embedded-5.5.37-1.rhel5.x86_64 MySQL-devel-5.5.37-1.rhel5.x86_64 MySQL-shared-5.5.37-1.rhel5.x86_64 [root@mysql-1 mysql]# [root@mysql-1 mysql]# rpm -ql MySQL-server | grep --color ".cnf" /etc/my.cnf /usr/share/doc/MySQL-server-5.5.37/my-huge.cnf /usr/share/doc/MySQL-server-5.5.37/my-innodb-heavy-4G.cnf /usr/share/doc/MySQL-server-5.5.37/my-large.cnf /usr/share/doc/MySQL-server-5.5.37/my-medium.cnf /usr/share/doc/MySQL-server-5.5.37/my-small.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf [root@mysql-1 mysql]#
查看当前MySQL状态:
[root@mysql-1 software]# service mysql status MySQL running (33961)[ OK ] [root@mysql-1 software]# [root@mysql-1 software]# ps faux | grep --color mysql root 34009 0.0 0.0 103316 828 pts/3 S+ 04:15 0:00 | \_ grep --color mysql root 33870 0.0 0.0 11344 1388 pts/3 S 04:14 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql-1.pid mysql 33961 0.1 2.1 422688 40168 pts/3 Sl 04:14 0:00 \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-1.err --pid-file=/var/lib/mysql/mysql-1.pid [root@mysql-1 software]#
可以看到:
这个环境中的MySQL的数据目录位于:/var/lib/mysql
日志文件:/var/lib/mysql/mysql-1.err(在MySQL中,错误日志一般是:【hostname】.error)
在我的环境中,我的备份策略设定是这样的:
[root@mysql-1 software]# env | grep --color LANG LANG=en_US.UTF-8 [root@mysql-1 software]# export LANG=en_US [root@mysql-1 software]# [root@mysql-1 software]# tree /backup/ /backup/ `-- db `-- mysql |-- dumpfile | |-- MySQL_db_mysql_after_adamhuan.backup | `-- MySQL_db_mysql.backup `-- tarfile 4 directories, 2 files [root@mysql-1 software]#
其中:
dumpfile,存放通过mysqldump导出的备份文件。
tarfile,存放通过tar压缩的MySQL数据目录。(该文件可用的前提是,MySQL停机状态下的冷备份。)
一、冷备:数据目录
先停库,做一个冷备:
[root@mysql-1 software]# service mysql status MySQL running (33961)[ OK ] [root@mysql-1 software]# [root@mysql-1 software]# service mysql stop Shutting down MySQL.[ OK ] [root@mysql-1 software]# [root@mysql-1 software]# ps -ef | grep mysql root 34083 9675 0 04:22 pts/3 00:00:00 grep mysql [root@mysql-1 software]# [root@mysql-1 software]# netstat -tupln | grep mysql [root@mysql-1 software]# [root@mysql-1 tarfile]# pwd /backup/db/mysql/tarfile [root@mysql-1 tarfile]# [root@mysql-1 tarfile]# ls -ltr total 0 [root@mysql-1 tarfile]# [root@mysql-1 tarfile]# ls -ltr /var/lib/mysql/ total 233524 drwxr-xr-x. 2 mysql mysql 4096 Aug 23 01:03 test drwx------. 2 mysql mysql 4096 Aug 23 01:03 performance_schema drwx------. 2 mysql mysql 4096 Aug 23 03:10 kohler_run_minisite_bj drwx------. 2 mysql mysql 4096 Aug 23 03:10 web_kohler_wx drwx------. 2 mysql mysql 4096 Aug 23 03:10 web_kohler_wx_admin drwx------. 2 mysql mysql 12288 Aug 23 03:10 chinawebsite drwx--x--x. 2 mysql mysql 4096 Aug 23 03:19 mysql -rw-r--r--. 1 mysql mysql 500 Aug 23 04:13 RPM_UPGRADE_MARKER-LAST -rw-r--r--. 1 mysql mysql 613 Aug 23 04:13 RPM_UPGRADE_HISTORY -rw-rw----. 1 mysql mysql 5242880 Aug 23 04:14 ib_logfile1 -rw-rw----. 1 mysql mysql 228589568 Aug 23 04:22 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Aug 23 04:22 ib_logfile0 -rw-r-----. 1 mysql mysql 6816 Aug 23 04:22 mysql-1.err [root@mysql-1 tarfile]# [root@mysql-1 tarfile]# du -sh /var/lib/mysql/ 232M /var/lib/mysql/ [root@mysql-1 tarfile]#
不同的打包(压缩)策略的差异:
tar -cf
[root@mysql-1 tarfile]# tar -cf MySQL_coldbackup_datadir_20160822_2254.tar /var/lib/mysql tar: Removing leading `/' from member names [root@mysql-1 tarfile]# ls MySQL_coldbackup_datadir_20160822_2254.tar [root@mysql-1 tarfile]# [root@mysql-1 tarfile]# du -sh MySQL_coldbackup_datadir_20160822_2254.tar 232M MySQL_coldbackup_datadir_20160822_2254.tar [root@mysql-1 tarfile]#
tar -cjf
[root@mysql-1 tarfile]# tar -cjf MySQL_coldbackup_datadir_20160822_2254.tar.bz2 /var/lib/mysql tar: Removing leading `/' from member names [root@mysql-1 tarfile]# [root@mysql-1 tarfile]# du -sh * 232M MySQL_coldbackup_datadir_20160822_2254.tar 52M MySQL_coldbackup_datadir_20160822_2254.tar.bz2 [root@mysql-1 tarfile]#
tar -czf
[root@mysql-1 tarfile]# tar -czf MySQL_coldbackup_datadir_20160822_2254.tar.gz /var/lib/mysql tar: Removing leading `/' from member names [root@mysql-1 tarfile]# [root@mysql-1 tarfile]# du -sh * 232M MySQL_coldbackup_datadir_20160822_2254.tar 52M MySQL_coldbackup_datadir_20160822_2254.tar.bz2 64M MySQL_coldbackup_datadir_20160822_2254.tar.gz [root@mysql-1 tarfile]#
可以看到,通过bzip2压缩的压缩率是最高的。
二、热备:mysqldump导出
查看当前的数据库状态:
[root@mysql-1 software]# mysql -uroot -p'abcd1234' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.37 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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 | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> mysql> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | | root | mysql-1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | mysql-1 | | +------+-----------+-------------------------------------------+ 6 rows in set (0.00 sec) mysql> mysql> exit Bye [root@mysql-1 software]#
备份当前的数据库状态:
[root@mysql-1 ~]# cd /backup/db/mysql/dumpfile/ [root@mysql-1 dumpfile]# ls -ltr total 0 [root@mysql-1 dumpfile]#
备份全库:
[root@mysql-1 ~]# cd /backup/db/mysql/dumpfile/ [root@mysql-1 dumpfile]# ls -ltr total 0 [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --all-databases > MySQL_all_db.dmp [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# ls -ltr total 528 -rw-r--r-- 1 root root 538861 Aug 23 01:15 MySQL_all_db.dmp [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# du -sh * 528K MySQL_all_db.dmp [root@mysql-1 dumpfile]#
备份某个特定的库:
[root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events mysql > MySQL_db_mysql.dmp [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# ls -ltr total 1056 -rw-r--r-- 1 root root 538861 Aug 23 01:15 MySQL_all_db.dmp -rw-r--r-- 1 root root 538540 Aug 23 01:16 MySQL_db_mysql.dmp [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# du -sh * 528K MySQL_all_db.dmp 528K MySQL_db_mysql.dmp [root@mysql-1 dumpfile]#
注意,上面备份中都添加了“–events”目的是为了过滤mysql.events的数据,否则,导出的过程中,会出现这样的警告(warning):
[root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --all-databases > MySQL_all_db.dmp -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. [root@mysql-1 dumpfile]#
对于普通的库来说,mysqldump导出的时候,是不需要添加“–events”选项的。
向数据库中添加一些新的数据库,并建表,写入一些数据:
[root@mysql-1 software]# mysql -uroot -p'abcd1234' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.37 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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 | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> mysql> create database world; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | world | +--------------------+ 5 rows in set (0.00 sec) mysql> use world; Database changed mysql> show tables; Empty set (0.00 sec) mysql> mysql> create table people( uid int, uname varchar(100), uage int, uloc varchar(100) ); Query OK, 0 rows affected (0.03 sec) mysql> mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | people | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> desc people; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | uid | int(11) | YES | | NULL | | | uname | varchar(100) | YES | | NULL | | | uage | int(11) | YES | | NULL | | | uloc | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> mysql> select count(*) from people; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> mysql> insert into people values (0,'adamhuan',28,'china'); Query OK, 1 row affected (0.00 sec) mysql> insert into people values (1,'allah',28,'china'); Query OK, 1 row affected (0.00 sec) mysql> insert into people values (2,'allah',28,'china'); Query OK, 1 row affected (0.00 sec) mysql> insert into people values (3,'jean',28,'usa'); Query OK, 1 row affected (0.02 sec) mysql> mysql> select * from people; +------+----------+------+-------+ | uid | uname | uage | uloc | +------+----------+------+-------+ | 0 | adamhuan | 28 | china | | 1 | allah | 28 | china | | 2 | allah | 28 | china | | 3 | jean | 28 | usa | +------+----------+------+-------+ 4 rows in set (0.00 sec) mysql>
备份:
全库:
[root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --all-databases > MySQL_db_all_after_create_insert.dmp [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# du -sh * 528K MySQL_all_db.dmp 528K MySQL_db_all_after_create_insert.dmp 528K MySQL_db_mysql.dmp [root@mysql-1 dumpfile]#
单独备份库:world。
[root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --databases world > MySQL_db_world_after_create_insert.dmp [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# du -sh * 528K MySQL_all_db.dmp 528K MySQL_db_all_after_create_insert.dmp 528K MySQL_db_mysql.dmp 4.0K MySQL_db_world_after_create_insert.dmp [root@mysql-1 dumpfile]#
创建用户,并赋予对数据库world的权限。
mysql> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | | root | mysql-1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | mysql-1 | | +------+-----------+-------------------------------------------+ 6 rows in set (0.00 sec) mysql> mysql> grant all privileges on world.* to god@'192.168.111.%' identified by 'abcd1234'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password from mysql.user; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | | root | mysql-1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | mysql-1 | | | god | 192.168.111.% | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | +------+---------------+-------------------------------------------+ 7 rows in set (0.00 sec) mysql> show grants for god@'192.168.111.%'; +----------------------------------------------------------------------------------------------------------------+ | Grants for god@192.168.111.% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'god'@'192.168.111.%' IDENTIFIED BY PASSWORD '*4AD47E08DAE2BD4F0977EED5D23DC901359DF617' | | GRANT ALL PRIVILEGES ON `world`.* TO 'god'@'192.168.111.%' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
备份这时候的数据库:
全库:
[root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --all-databases > MySQL_db_all_after_user.dmp [root@mysql-1 dumpfile]# du -sh * 528K MySQL_all_db.dmp 528K MySQL_db_all_after_create_insert.dmp 528K MySQL_db_all_after_user.dmp 528K MySQL_db_mysql.dmp 4.0K MySQL_db_world_after_create_insert.dmp [root@mysql-1 dumpfile]#
单库:world
[root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --databases world > MySQL_db_world_after_user.dmp [root@mysql-1 dumpfile]# [root@mysql-1 dumpfile]# du -sh * 528K MySQL_all_db.dmp 528K MySQL_db_all_after_create_insert.dmp 528K MySQL_db_all_after_user.dmp 528K MySQL_db_mysql.dmp 4.0K MySQL_db_world_after_create_insert.dmp 4.0K MySQL_db_world_after_user.dmp [root@mysql-1 dumpfile]#
单库:mysql
[root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --databases mysql > MySQL_db_mysql_after_user.dmp [root@mysql-1 dumpfile]# du -sh * 528K MySQL_all_db.dmp 528K MySQL_db_all_after_create_insert.dmp 528K MySQL_db_all_after_user.dmp 528K MySQL_db_mysql_after_user.dmp 528K MySQL_db_mysql.dmp 4.0K MySQL_db_world_after_create_insert.dmp 4.0K MySQL_db_world_after_user.dmp [root@mysql-1 dumpfile]#
三、MySQL恢复
现有的备份文件阶段:
[root@mysql-1 dumpfile]# ls -ltr * -rw-r--r-- 1 root root 538861 Aug 23 01:15 MySQL_all_db.dmp -rw-r--r-- 1 root root 538540 Aug 23 01:16 MySQL_db_mysql.dmp -rw-r--r-- 1 root root 539788 Aug 23 01:27 MySQL_db_all_after_create_insert.dmp -rw-r--r-- 1 root root 2127 Aug 23 01:28 MySQL_db_world_after_create_insert.dmp -rw-r--r-- 1 root root 540108 Aug 23 01:31 MySQL_db_all_after_user.dmp -rw-r--r-- 1 root root 2127 Aug 23 01:32 MySQL_db_world_after_user.dmp -rw-r--r-- 1 root root 539002 Aug 23 01:33 MySQL_db_mysql_after_user.dmp [root@mysql-1 dumpfile]#
最初,MySQL初始状态:
全:MySQL_all_db.dmp
单:MySQL_db_mysql.dmp
然后,创建world,并建表、插入数据的状态:
全:MySQL_db_all_after_create_insert.dmp
单:MySQL_db_world_after_create_insert.dmp
最后,创建用户,并分配权限后的状态:
全:MySQL_db_all_after_user.dmp
单-mysql:MySQL_db_mysql_after_user.dmp
单-world:MySQL_db_world_after_user.dmp
从上边的变化阶段你可以知道:
1. 三个阶段的全库导出文件肯定不同
2. 三个阶段的单库文件有可能不同:
相同的部分:
— 第一阶段到第二阶段,只是创建了库,所以mysql的单库状态没有发生变化
— 第二阶段到第三阶段,只是新建了用户,所以world的单库状态没有发生变化
不同的部分:
— 第二阶段到第三阶段,单库mysql的状态肯定发生了变化
恢复:
将全库恢复到第二阶段:MySQL_db_all_after_create_insert.dmp
这个过程中,变化的应该是mysql.user表。
[root@mysql-1 software]# mysql -uroot -p'abcd1234' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.5.37 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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 | | test | | world | +--------------------+ 5 rows in set (0.00 sec) mysql> select user,host,password from mysql.user; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | | root | mysql-1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | mysql-1 | | | god | 192.168.111.% | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | +------+---------------+-------------------------------------------+ 7 rows in set (0.00 sec) mysql> mysql> exit Bye [root@mysql-1 software]# [root@mysql-1 software]# mysql -uroot -p'abcd1234' < /backup/db/mysql/dumpfile/MySQL_db_all_after_create_insert.dmp [root@mysql-1 software]# [root@mysql-1 software]# mysql -uroot -p'abcd1234' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.5.37 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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 | | test | | world | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | | root | mysql-1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | mysql-1 | | +------+-----------+-------------------------------------------+ 6 rows in set (0.00 sec) mysql> exit Bye [root@mysql-1 software]#
可以看到,全库备份也备份了mysql,所以mysql.user的登陆信息的表也被更改了。
单库恢复第三阶段mysql:MySQL_db_mysql_after_user.dmp。
这一步操作,会看到mysql.user发生变化,第三阶段中新增的用户god,会恢复到mysql.user中。
由于是单库恢复,所以,不会影响其他的数据库状态。
[root@mysql-1 software]# mysql -uroot -p'abcd1234' < /backup/db/mysql/dumpfile/MySQL_db_mysql_after_user.dmp [root@mysql-1 software]# [root@mysql-1 software]# mysql -uroot -p'abcd1234' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.5.37 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> select user,host,password from mysql.user; +------+---------------+-------------------------------------------+ | user | host | password | +------+---------------+-------------------------------------------+ | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | | root | mysql-1 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | mysql-1 | | | god | 192.168.111.% | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 | +------+---------------+-------------------------------------------+ 7 rows in set (0.00 sec) mysql> show grants for god@'192.168.111.%'; +----------------------------------------------------------------------------------------------------------------+ | Grants for god@192.168.111.% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'god'@'192.168.111.%' IDENTIFIED BY PASSWORD '*4AD47E08DAE2BD4F0977EED5D23DC901359DF617' | | GRANT ALL PRIVILEGES ON `world`.* TO 'god'@'192.168.111.%' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> exit Bye [root@mysql-1 software]#
四、终了
MySQL的导出文件,并不像Oracle的导出文件那样,可以导出多个表空间,然后恢复的时候可以指定具体恢复哪一个表空间以及恢复到哪个Schema下。
MySQL的导出文件,如果包含了实例下的多个数据库,则恢复的时候会同时恢复。这对于一些需要只恢复导出文件中的某一部分库的场景是不太适用的。
此外,如果MySQL的导出文件包含了mysql库,恢复的时候只想恢复数据而不希望影响MySQL的登陆权限,则也是比较麻烦的,很容易就覆盖了之前的账户信息,造成丢失。
所以,MySQL的备份,如果采用mysqldump,最好分开备份mysql和非mysql单库。
——————————
Done。