MySQL 8.0备份数据库、数据恢复

使用MySQLdump命令备份

语法格式

mysqldump -u user -h host -ppassword dbname[tbname,[tbname...]] > filename.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test_db;
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_tb           |
| user_tb           |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from user_tb;
+---------+-------+------+------+
| user_id | name  | part | age  |
+---------+-------+------+------+
|       1 | zhang | it   | 20   |
|       2 | wang  | it   | 21   |
|       3 | li    | it   | 22   |
+---------+-------+------+------+
3 rows in set (0.00 sec)

备份数据库test_db中的所有表

mysqldump -u root -p test_db > bak.sql

或者直接在命令行输入密码也行

mysqldump -u root -h 10.112.37.100  -p'Lab123!@#' test_db > bak1.sql

备份数据库test_db中的表user_db

mysqldump -u root -h 10.112.37.100  -p'Lab123!@#' test_db user_tb > bak_user_tb.sql

数据恢复

语法格式

mysql -u user -p dbname < filename.sql

恢复数据库

mysql -u root -p test_db < bak.sql

恢复数据表

mysql -u root -p test_db < bak_user_tb.sql

如果已经登陆到MySQL服务器，可以使用source导入本地的备份sql文件,必须使用use语句选择数据库

mysql> use test_db;
Database changed
mysql> source /home/bak_user_tb.sql
Query OK, 3 rows affected (0.00 sec)