Mysql之备份与恢复(简单版)
1. select into outfile导出表数据
SELECT [列名] FROM table [WHERE 语句]
INTO OUTFILE '目标文件' [OPTION];
其中的option为可选参数,在缺省的情况下,默认的表现为:
-
在字段值间加上制表符。
-
不用任何引号字符封装字段(默认为\n)。
-
使用 “\” 转义出现在字段值中的定位符、换行符或`' 字符实例。
-
在行的结尾处加上换行符。
那么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格式。
