整库数据备份也叫SQL数据备份,备份的结果都是SQL指令,在mysql中提供了一个专门用于备份SQL的客户端:mysqldump.exe(在计算机领域,dump一般译作转储)。
应用场景
SQL备份是一种mysql非常常见的备份与还原方式,SQL备份不只是备份数据,还备份对应的SQL指令(表结构),即便是数据库遭到毁灭性的破坏(数据库被删),那么利用SQL备份依然可以实现数据还原。
SQL备份因为需要备份结构,因此产生的备份文件特别大,因此不适合特大型数据备份,也不适合数据变换频繁型数据库备份。
应用方案
SQL备份用到的是专门的备份客户端,因此还没与数据库服务器进行连接。
基本语法:mysqldump/mysqldump.exe -hPup 数据库名字[[表1] [表2]]>备份文件地址
备份可以有三种形式:
1、整库备份(只需要提供数据库名字)
2、单表备份,数据库后面跟一张表
3、多表备份,数据库后面跟多张表
数据备份
1、备份一个数据库
基本语法:mysqldump/mysqldump.exe -u username -p dbname table1 table2...>backupname.sql(table1和table2等表示参数名称,没有参数时将表示整个数据库)
C:\Users\47876> mysqldump -u root -p mydatabase1 > e:/data_bakeup/mydb1.sql
Enter password: ****
C:\Users\47876>
2、备份多个数据库
基本语法:mysqldump/mysqldump.exe -u username -p --databases dbname1 dbname2...>backupname.sql
C:\Users\47876> mysqldump -u root -p --databases mydatabase1 mydatabase2 > e:/data_bakeup/mydb1.sql
Enter password: ****
C:\Users\47876>
3、备份所有数据库
基本语法:mysqldump/mysqldump.exe -u username -p --all-databases>backupname.sql
-- 视图部分无法备份,至于原因待以后解决,该备份语法是没问题的
C:\Users\47876> mysqldump -u root -p --all-databases > e:/data_bakeup/mydb1.sql
Enter password: ****
mysqldump: Couldn't execute 'show create table `exa07_view`': View 'example.exa07_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
C:\Users\47876>
单/多表备份(同1)
基本语法:mysqldump/mysqldump.exe -u username -p dbname table1 table2...>backupname.sql
备注:dbname数据库名,table1、2..代表表名,backupname备份名,其中文件名前面可以加上一个绝对路径.通常将数据名备份成一个后缀名为sql的文件(当然备份成其他格式文件也是可以的),因为该后缀名给人的第一感觉就是与数据库有关的文件.
数据还原
mysql提供了多种方式来实现:
mysqldump备份的数据中没有关于数据库本身的操作,都是针对表级别的操作,当进行数据(SQL还原),必须指定数据库
1、利用mysql.exe客户端:没有登录之前,可以直接用该客户端进行数据还原
mysql.exe -hPup 数据库 < 文件位置
-- 数据备份
C:\Users\47876> mysqldump -u root -p mydatabase1 > e:/data_bakeup/mydb1.sql
Enter password: ****
-- 数据还原,注意数据库mydatabase1也可以命名为其他,但是必须是 “存在的数据库” !!!我之所以还命名为原来的名字,是因为我又创建了该同名的数据库,如下所示:
C:\Users\47876>mysql -u root -p mydatabase1 < e://data_bakeup/mydb1.sql
Enter password: ****
C:\Users\47876>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example |
| job |
| mydatabase |
| mydatabase1 |
| mydatabase2 |
| mydatabases |
| mysql |
| performance_schema |
| test |
+--------------------+
10 rows in set (0.00 sec)
mysql> use mydatabase1;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_mydatabase1 |
+-----------------------+
| class |
| class3 |
| class6 |
| teacher1 |
+-----------------------+
4 rows in set (0.00 sec)
mysql> drop database mydatabase1;
Query OK, 4 rows affected (1.67 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example |
| job |
| mydatabase |
| mydatabase2 |
| mydatabases |
| mysql |
| performance_schema |
| test |
+--------------------+
9 rows in set (0.00 sec)
-- 在这里创建该同名数据库是为了还原,执行上面还原命令后,再次查询
mysql> create database mydatabase1;
Query OK, 1 row affected (0.00 sec)
-- 可以看到数据库表的内容恢复了
mysql> use mydatabase1;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_mydatabase1 |
+-----------------------+
| class |
| class3 |
| class6 |
| teacher1 |
+-----------------------+
4 rows in set (0.00 sec)
2、在SQL指令,提供了一种导入SQL指令的方式
source SQL文件位置; //前提是必须先进入到对应的数据库
mysql> drop database mydatabase1;
Query OK, 4 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| example |
| job |
| mydatabase |
| mydatabase2 |
| mydatabases |
| mysql |
| performance_schema |
| test |
+--------------------+
9 rows in set (0.00 sec)
mysql> create database mydatabase1;
Query OK, 1 row affected (0.00 sec)
mysql> use mydatabase1;
Database changed
mysql> source e://data_bakeup/mydb1.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.39 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_mydatabase1 |
+-----------------------+
| class |
| class3 |
| class6 |
| teacher1 |
+-----------------------+
4 rows in set (0.00 sec)
3、人为操作:打开备份文件,复制所有SQL指令,然后到mysql.exe客户端中去黏贴执行。(不推荐)