sql笔记(数据库的备份和恢复)

一、图形界面操作

1. 转储sql文件
2. 新建数据库
3. 运行sql文件
   通过以上三个操作即可完成数据库的迁移

二、终端备份

需要退出

2.1 Linux环境下

数据库的备份

lzying@lzying-virtual-machine:~$ mysqldump -uroot -p Python1806 > ~/Desktop/Python1806.sql
Enter password: 

数据库的恢复

mysql> create database beifen charset=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
lzying@lzying-virtual-machine:~$ mysql -uroot -p beifen < ~/Desktop/Python1806.sql
Enter password: 
lzying@lzying-virtual-machine:~$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, 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> use beifen;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_beifen |
+------------------+
| Student          |
| Winner           |
+------------------+
2 rows in set (0.00 sec)

2.2 windows环境下

C:\Users\lizheying>mysqldump -uroot -p python_test_1 > C:\Users\lizheying\Desktop\python_beifen.sql
Enter password: *********

C:\Users\lizheying>mysql -uroot -p beifen < "C:\Users\lizheying\Desktop\python_beifen.sql"
Enter password: *********

C:\Users\lizheying>mysql -uroot -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 343
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> use beifen;
Database changed
mysql> show tables;
+------------------+
| Tables_in_beifen |
+------------------+
| area             |
| classes          |
| info             |
| students         |
+------------------+
4 rows in set (0.00 sec)

命令都是一样,只是不同系统的路径表示方式有所不同.