mysql备份恢复
可以直接将数据库的目录备份也是一种方法
一、使用mysqldump备份数据库。
1、查看已存在的数据库
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ar |
| mysql |
| test |
| zhaoyun |
+--------------------+
5 rows in set (0.00 sec)
+--------------------+
| Database |
+--------------------+
| information_schema |
| ar |
| mysql |
| test |
| zhaoyun |
+--------------------+
5 rows in set (0.00 sec)
mysql> use ar
Database changed
mysql> show tables ;
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
| admin |
| blog |
+----------------+
3 rows in set (0.00 sec)
Database changed
mysql> show tables ;
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
| admin |
| blog |
+----------------+
3 rows in set (0.00 sec)
2、使用mysqldump命令备份数据库，使用mysqldump备份数据库备份的是sql语句，所以用mysqldump命令备份的文件可以在不同平台进行恢复。
#创建备份目录
[root@zhaoyun ~]# mkdir mysqlback
#备份ar数据库，将ar数据库备份并将备份的sql保存在ar.sql文件
[root@zhaoyun ~]# mysqldump -uroot -p123456 ar > mysqlback/ar.sql
#备份所有数据库
[root@zhaoyun ~]# mysqldump -uroot -p123456 --all-database > mysqlback/all.sql
#备份ar数据库的admin和blog俩张表
[root@zhaoyun ~]# mysqldump -uroot -p123456 ar admin blog > mysqlback/ar-admin-blog.sql
#备份ar和mysql俩个数据库
[root@zhaoyun ~]# mysqldump -uroot -p123456 -B ar mysql > mysqlback/ardb-mysqldb.sql
[root@zhaoyun ~]# mkdir mysqlback
#备份ar数据库，将ar数据库备份并将备份的sql保存在ar.sql文件
[root@zhaoyun ~]# mysqldump -uroot -p123456 ar > mysqlback/ar.sql
#备份所有数据库
[root@zhaoyun ~]# mysqldump -uroot -p123456 --all-database > mysqlback/all.sql
#备份ar数据库的admin和blog俩张表
[root@zhaoyun ~]# mysqldump -uroot -p123456 ar admin blog > mysqlback/ar-admin-blog.sql
#备份ar和mysql俩个数据库
[root@zhaoyun ~]# mysqldump -uroot -p123456 -B ar mysql > mysqlback/ardb-mysqldb.sql
3、恢复，使用mysql命令进行数据库恢复。
1 、/× 删除ar数据库
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ar |
| mysql |
| test |
| zhaoyun |
+--------------------+
5 rows in set (0.00 sec)
+--------------------+
| Database |
+--------------------+
| information_schema |
| ar |
| mysql |
| test |
| zhaoyun |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database ar ;
Query OK, 13 rows affected (0.13 sec)
Query OK, 13 rows affected (0.13 sec)
mysql> show databases; /×ar数据库已经不存在了。
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| zhaoyun |
+--------------------+
4 rows in set (0.00 sec)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| zhaoyun |
+--------------------+
4 rows in set (0.00 sec)
2、使用备份文件进行恢复
\*先创建一个ar的数据库
mysql> create database ar ;
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
[root@zhaoyun ~]# mysql -uroot -p123456 ar < mysqlback/ar.sql
[root@zhaoyun ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.77 Source distribution
[root@zhaoyun ~]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ar |
| mysql |
| test |
| zhaoyun |
+--------------------+
5 rows in set (0.00 sec)
+--------------------+
| Database |
+--------------------+
| information_schema |
| ar |
| mysql |
| test |
| zhaoyun |
+--------------------+
5 rows in set (0.00 sec)
mysql> use ar
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
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_ar |
+----------------+
| actionlog |
| admin |
| blog |
3 rows in set (0.00 sec)
mysql> show tables ;
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
| admin |
| blog |
3 rows in set (0.00 sec)
可以看到ar数据库已经成功恢复了。
恢复备份的admin和blog表。先将俩个表删除
mysql> use ar
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
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_ar |
+----------------+
| actionlog |
| admin |
| blog |
3 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
| admin |
| blog |
3 rows in set (0.00 sec)
mysql> drop table admin ;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> drop table blog ;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables ;
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
+----------------+
1 rows in set (0.00 sec)
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
+----------------+
1 rows in set (0.00 sec)
mysql> source /root/mysqlback/ar-admin-blog.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
........................................................
mysql> show tables ;
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
| admin |
| blog |
+----------------+
13 rows in set (0.00 sec)
+----------------+
| Tables_in_ar |
+----------------+
| actionlog |
| admin |
| blog |
+----------------+
13 rows in set (0.00 sec)
mysql> select * from admin limit 1 ;
+----+---------------+----------------------------------+------------------+-------------+---------------------+
| id | userName | password | email | telephone | addTime |
+----+---------------+----------------------------------+------------------+-------------+---------------------+
| 1 | Administrator | 21231F2DFA57A5A743894A0E4A801FC3 | admin@system.com | 18392992922 | 2010-08-12 12:23:11 |
+----+---------------+----------------------------------+------------------+-------------+---------------------+
+----+---------------+----------------------------------+------------------+-------------+---------------------+
| id | userName | password | email | telephone | addTime |
+----+---------------+----------------------------------+------------------+-------------+---------------------+
| 1 | Administrator | 21231F2DFA57A5A743894A0E4A801FC3 | admin@system.com | 18392992922 | 2010-08-12 12:23:11 |
+----+---------------+----------------------------------+------------------+-------------+---------------------+
可以看到俩个表已经恢复回来了。
3、使用计划任务备份数据库
写一个脚本文件，然后在任务计划中增加执行这个脚本文件的时间。
备份数据库，并按日期时间命名。
mysqldump -uroot -p123456 --all-database > "`date +%Y-%m-%d-%H:%M:%S`.sql"
本文转自zhaoyun00 51CTO博客，原文链接:http://blog.51cto.com/zhaoyun/732756