MySQL数据库备份、还原操作

Read

再《修改、删除数据库表数据》已经操作做，此处单独操作：
备份单个数据库：

[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gbk                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.02 sec)
[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' gbk >/backup/gbk$(date +%F).sql     #备份gbk数据库
[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B gbk >/backup/gbk-B-$(date +%F).sql    #注意对比还原
[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' gbk|gzip >/backup/gbk$(date +%F).sql.gz    #压缩备份
Warning: Using a password on the command line interface can be insecure.
[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' --default-character-set=gbk -B gbk|gzip >/backup/gbk$(date +%F).sql.gz    #指定字符集
[root@hk ~]# ll /backup/
-rw-r--r--. 1 root root 1982 Jun  4 00:51 gbk-B-2014-06-04.sql
-rw-r--r--. 1 root root  740 Jun  4 00:57 gbk2014-06-04.sql.gz

[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin'

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| gbk |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.02 sec)

[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' gbk >/backup/gbk$(date +%F).sql #备份gbk数据库

[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B gbk >/backup/gbk-B-$(date +%F).sql #注意对比还原

[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' gbk|gzip >/backup/gbk$(date +%F).sql.gz #压缩备份

Warning: Using a password on the command line interface can be insecure.

[root@hk ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' --default-character-set=gbk -B gbk|gzip >/backup/gbk$(date +%F).sql.gz #指定字符集

[root@hk ~]# ll /backup/

-rw-r--r--. 1 root root 1982 Jun 4 00:51 gbk-B-2014-06-04.sql

-rw-r--r--. 1 root root 740 Jun 4 00:57 gbk2014-06-04.sql.gz

还原数据库：

mysql> drop database gbk;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' gbk < /backup/gbk2014-06-03.sql 
Warning: Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'gbk'
mysql> create database gbk default character set gbk collate gbk_chinese_ci;
[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' gbk < /backup/gbk2014-06-03.sql 
Warning: Using a password on the command line interface can be insecure.
mysql> drop database gbk;
[root@hk ~]# /usr/local/mysql/bin/mysql -uroot -p'admin123' (gbk)< /backup/gbk-B-2014-06-04.sql    #(内容可加可不加)
mysql> use gbk
mysql> show tables;
+---------------+
| Tables_in_gbk |
+---------------+
| world         |
+---------------+
1 row in set (0.00 sec)