再《修改、删除数据库表数据》已经操作做,此处单独操作:
备份单个数据库:
[MySQL]
[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]
还原数据库:
[mysql]
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)
[/mysql]
申明:本文由BYRD原创(基于Mysql5.6.16),未经许可禁止转载!
SourceByrd's Weblog-https://note.t4x.org/database/mysql-reduction-backup/
MySQL数据库备份、还原操作
申明:除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
Trackbacks