Mysql之备份与恢复(简单版)

1. select into outfile导出表数据

SELECT [列名] FROM table [WHERE 语句]
        INTO OUTFILE '目标文件' [OPTION];

其中的option为可选参数,在缺省的情况下,默认的表现为:

1. 在字段值间加上制表符。

2. 不用任何引号字符封装字段(默认为\n)。

3. 使用 “\” 转义出现在字段值中的定位符、换行符或`' 字符实例。

4. 在行的结尾处加上换行符。

那么option的可选参数为fields子句和lines子句,在fields子句下有三个亚子句,分别为:

terminated by(用来指定字段值之间的符号),

enclosed by(用来指定包裹文件中字符值的符号),

escaped by(用来指定转义字符).

如果指定了fields子句,那么这三个起码要指定一个.

在lines子句下有terminated by(指定一行结束的标志),starting by(设置每行数据开头的字符)

例子:

fields terminated by ',' //fields子句
enclosed by '"'
lines terminated by '\r\n' //lines子句

下面来演示一遍缺省option选项的效果:

如今有这样的一个表,user_info

mysql> select * from user_info;
+----+----------+
| id | nickname |
+----+----------+
|  1 | 昵称1    |
|  2 | 昵称2    |
|  3 | 昵称3    |
+----+----------+
3 rows in set (0.00 sec)

执行select into outfile语句:

mysql> select * from user_info into outfile '/home/xiaoma/file.txt';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

没想到的是mysql报错了,原来是mysql默认只能导出文件到一个指定的文件夹内,现在执行select @@secure_file_priv看看:

 mysql> select @@secure_file_priv;
+-----------------------+
| @@secure_file_priv    |
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+
1 row in set (0.00 sec)

现在再改一下:

mysql> select * from user_info into outfile '/var/lib/mysql-files/file.txt';
Query OK, 3 rows affected (0.00 sec)

为什么会这样呢,原来啊如果使用 select ... into outfile ...方式导出数据，需要分为两种情况,windows系统下,可以随意指定对应的目录,如果是linux系统的话,只能导出到指定的目录,包括下面的load data infile语句的执行也同样如此处理

如果说导出数据到文件之后出现乱码的情况,那么可以指定字符集:

mysql> select * from user_info into outfile '/var/lib/mysql-files/file1.txt' character set gbk;

Query OK, 3 rows affected (0.00 sec)

---

2. load data infile将文件数据导入到表中(select into outfile的反操作)

导入文件时要注意编码问题,也要加上同样的分隔限制语句

先看看此语句的结构:

load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name, )]

由上面的结构图,先分析一下这个语句结构图吧:

2.1 low_priority关键词:

若指定此关键词,MySQL将会等到没有其他人读这个表的时候，才把插入数据

2.2 local关键词:

如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上

2.3 replace和ignore关键词:

当插入数据的时候,对于重复插入现有的唯一键的行,在默认情况下,mysql会报错,然后中断插入数据,若指定replace关键词,新行将会替换原现有的行,若指定ignore关键词,就会直接跳过对于重复插入行的操作.

2.4 fields子句:

fields子句包含3个亚子句,若指定了此子句那么必须要至少指定一个亚子句,亚子句分别为:
2.4.1 terminated by 描述字段的分隔符，默认情况下是tab字符（\t）
2.4.2 enclosed by 描述的是字段的括起字符.
2.4.3 escaped by 描述的转义字符.默认的是反斜杠

例如:

mysql> load data infile '/var/lib/mysql-files/loadInFile.txt' into table user_info fields terminated by ',';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

2.5 lines子句:

lines子句只有一个亚子句,terminated by 指定一行结束的标志.

2.6 指定字符集导入数据:

load data infile '/var/lib/mysql-files/loadInFile.txt' into table user_info character set gbk fields terminated by ',';

---

3. mysqldump 命令备份数据库(会备份表结构和数据)

先来看看用此命令时的结构

mysqldump -uroot -p [database name] > [dump file] //其中root为mysql账号名

3.1 备份指定的数据库:

mysqldump -uroot -p test > test.sql

如果加上--opt参数则生成的dump文件中稍有不同：

 . 建表语句包含drop table if exists tableName

 . insert之前包含一个锁表语句lock tables tableName write，insert之后包含unlock tables

mysqldump -uroot -p --opt test > test.sql

3.2 备份多个数据库:

mysqldump -uroot -p --databases test blog_test > /test.sql

3.3 只备份数据库中的表结构:(不保存数据哦)

mysqldump -uroot -p --no-data test > test.sqlmysqldump -uroot -p --no-data test > test.sql

3.4 备份所有的数据库:

mysqldump -uroot -p --all-databases > /all.sql

3.5 用了mysqldump后,数据的还原

语法结构如下:

mysql -u root -p [dbname] < backup.sql

假如备份了所有的数据库,然后只恢复部分的数据库:

mysql -uroot -p test < all.sql

除了用这个语法结构，还可以用source命令恢复数据库，例如有一个test数据库,有一个test.sql的备份文件,进入mysql后,use test切换到test数据库底下,然后执行:

mysql>source /mysql.sql;

4. 在linux系统中定时备份mysql

结合Linux的cron命令实现定时备份

比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式，那么可在/etc/crontab配置文件中加入下面代码行：

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz
前面5个参数分别表示分钟、小时、日、月、年，星号表示任意。date '+%m-%d-%Y'得到当前日期的MM-DD-YYYY格式。