MySql数据备份与恢复小结

方法1

备份

1 SELECT * INTO OUTFILE "D:\1.sql" FROM 表名;

恢复

1 LOAD DATA INFILE "D:\1.sql" INTO TABLE 表名;

补充几个设置

1. FIELDS TERMINATED BY ',' #字段间隔符
2. OPTIONALLY ENCLOSED BY '"' #字符串类型字段包围符
3. LINES TERMINATED BY '
' #换行符

于是备份命令改为

1 SELECT
2     * INTO OUTFILE "D:\1.sql" FIELDS TERMINATED BY ','
3     *  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
'
4 FROM
5     表名;

恢复数据改为

1 LOAD DATA INFILE "D:\1.sql" 
2 　　INTO TABLE 表名 
3 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
';

注意

要有文件目录的读写权限特别是在linux下
不能备份表结构善用SHOW CREATE TABLE 表名来获取结构并备份;

方法2

备份

1 mysqldump -uroot -p密码 数据库名 -l -F > '/usr/data.sql'
2 
3 #在mysql5.6中失效原因是不能在命令行中出现密码   需要在etc下的my.cnf中写入
4 [mysqldump]
5 user=备份用户名
6 password=备份用户密码
7 #然后执行
8 mysqldump 数据库名 -l -F > '/usr/data.sql'

恢复的时候可能会因为数据过大造成失败先运行

set global wait_timeout=60000;
set global max_allowed_packet = 64*1024*1024

恢复

1 mysql -uroot -p123456 数据库名 -v -f</usr/data.sql
2 #-v 查看详细信息
3 #-f 当中间遇到错误 可以跳过 忽略错误

恢复binlog数据

1 #恢复全部数据
2 mysqlbinlog --no-defaults mysql.000001 |mysql -uroot -p123456 数据库名
3 #恢复部分数据
4 mysqlbinlog --no-defaults mysqlbinlog.00001 --start-position="193" --stop-position="280"  |mysql -uroot -p123456 数据库名
5 #根据pos位置193到280之间 恢复数据

方法3

停掉mysql的服务，然后tar打包data目录下的数据文件即可，恢复数据时最好采用相同版本的数据库软件