第二步，备份多个库
| [root@serv01 databackup]# mysqldump -uroot -p123456 --databases larrydb game > larrydb_game.sql [root@serv01 databackup]# ll larrydb_game.sql -rw-r--r--. 1 root root 6159 Sep 10 19:05 larrydb_game.sql |
第三步，模拟数据丢失。
| mysql> drop database game; Query OK, 3 rows affected (0.01 sec) mysql> drop database larrydb; Query OK, 2 rows affected (0.00 sec) mysql> use crm; Database changed mysql> show tables; +---------------+ | Tables_in_crm | +---------------+ | test | +---------------+ 1 row in set (0.00 sec) mysql> select * from test; Empty set (0.00 sec) mysql> drop database crm; Query OK, 1 row affected (0.00 sec) |
第四步，恢复数据
| [root@serv01 databackup]# mysql -uroot -p123456 < larrydb_game.sql |
第五步，查看数据是否正常
| [root@serv01 data]# mysql -uroot -p123456 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 5.5.29-log Source distribution Copyright (c) 2000, 2012, 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 | | game | | hello | | larrydb | | mnt | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.00 sec) mysql> use game; Database changed mysql> select * from country; +-----+---------+----------+ | cno | cname | location | +-----+---------+----------+ | 10 | caowei | luoyang | | 20 | shuhan | chengdou | | 30 | sunwu | nanjing | | 40 | houhan | luoyang | | 50 | beisong | kaifeng | | 60 | 魏国 | 洛阳 | +-----+---------+----------+ 6 rows in set (0.00 sec) mysql> use larrydb; Database changed mysql> select * from class; +------+--------+ | cid | cname | +------+--------+ | 1 | linux | | 2 | oracle | +------+--------+ 2 rows in set (0.00 sec) |
备份所有的库
| [root@serv01 databackup]# mysqldump --help | grep all-database OR mysqldump [OPTIONS] --all-databases [OPTIONS] -A, --all-databases Dump all the databases. This will be same as --databases --databases= or --all-databases), the logs will be --all-databases or --databases is given. all-databases FALSE [root@serv01 databackup]# mysqldump -uroot -p123456 --all-databases > all_databases.sql [root@serv01 databackup]# ll all_databases.sql -h -rw-r--r--. 1 root root 506K Sep 10 19:16 all_databases.sql |
备份某张表或者某几张表
第一步，备份某张表和某几张表
| [root@serv01 databackup]# mysqldump game hero country -uroot -p123456 > game_hero_country.sql [root@serv01 databackup]# ll game_hero_country.sql -rw-r--r—. 1 root root 3955 Sep 10 19:11 game_hero_country.sql |
