Data backup and recovery
Environmental requirements:
1. Logical backup tool mysqldump
two。 Use mysql to restore the database
* * step 1: use mysqldump for logical backup
1) back up all libraries on the MySQL server
Back up all libraries as mysql-all.sql files:
[root@MySQL50] # mysqldump-uroot-p-all-databases > / root/alldb.sql
Enter password:
Back up the db1 and db5 libraries at the same time, and save them as data/db.sql files:
[root@MySQL50] # mysqldump-u root-p-B db1 db5 > mydata/db.sql
Enter password:
View parts of the backup file userdb.sql:
[root@MySQL50 ~] # grep'^ CREATE DATA' mydata/db.sql
CREATE DATABASE /! 32312 IF NOT EXISTS/ db1 /! 40100 DEFAULT CHARACTER SET latin1 /
CREATE DATABASE /! 32312 IF NOT EXISTS/ db5 /! 40100 DEFAULT CHARACTER SET latin1 /
Step 2: use the mysql command to restore databases and tables from a backup
1) create a new library named db1cp
Mysql > create database db1cp
Query OK, 1 row affected (0.09 sec)
[root@MySQL50 ~] # mysqldump-uroot-p db1 > mydata/dbcp1.sql
Enter password:
2) Import backup files and rebuild tables and data in the new library
[root@MySQL50] # mysql-uroot-p db1cp
< mydata/dbcp1.sql Enter password: 3）确认新库正常，启用新库 mysql>Show databases
+-+
| | Database |
+-+
| | information_schema |
| | db1 |
| | db1cp |
| | db5 |
| | mysql |
| | performance_schema |
| | sys |
+-+
7 rows in set (0.00 sec)
Mysql > show tables
+-+
| | Tables_in_db1cp |
+-+
| | T1 |
+-+
1 row in set (0.00 sec)
Mysql > select * from T1
+-+
| | id | name | age | |
+-+
| | 1 | sky | 19 | |
| | 2 | tom | 20 | |
+-+
2 rows in set (0.00 sec)
* * 4) discard or delete old libraries
Ysql > drop database db1
Query OK, 1 row affected (0.18 sec)
