mysql 数据备份

测试数据

create database test;

use test;

create table user
(
    id int unsigned auto_increment primary key,
    name char(10)
) engine = innodb default charset utf8;

insert into user (id, name) values (1,"name_1"), (2, "name_2");

create table user_2
(
    id int unsigned auto_increment primary key,
    name char(10)
) engine = innodb default charset utf8;

insert into user_2 (id, name) values (1,"name_2_1"), (2, "name_2_2");

mysqldump常用参数

命令	含义
–single-transaction	以事务来保证导出完整性，只在innodb下有效
–user	导出所使用的账户
–password	导出所使用的账户的密码
–R	导出包含存储过程
–triggers	导出包含触发器
–E	导出包含事件
–master-data=value	value=1不写入注释 value=2 写入注释，注释内容：标记备份数据在binlog中的位置,如:( CHANGE MASTER TO MASTER_LOG_FILE=‘binlog.000006’, MASTER_LOG_POS=2252)
–tab=path	将表文件导出生成两个文件，表结构和表数据分离，存放在path
–all-databases	导出所有,包括mysql自带的系统表结构，数据
–where	导出符合where条件的数据

备份数据

# 备份全部数据
mysqldump --user=root --password=123456 --single-transaction -R --master-data=2 --triggers -E --all-databases > /data/back/all.sql;

# 备份指定库 test
mysqldump --user=root --password=123456 --single-transaction -R --master-data=2 --triggers -E test > /data/back/test.sql;

# 备份指定数据库 test，指定表 user user_2
mysqldump --user=root --password=123456 --single-transaction -R --master-data=2 --triggers -E test user user_2 > /data/back/test_user_1_2.sql;

# 使用 --tab=/data/tab 后
# /data/back/user_tab.sql 中不再存储表结构和表数据
# 以user表为例，将user表结构存储到 data/tab/user.sql ，将user表数据存储到 data/tab/user.txt
mysqldump --user=root --password=123456 --single-transaction -R --master-data=2 --triggers -E --tab=/data/tab test user > /data/back/user_tab.sql;

# 使用 --where 来备份id = 1 的数据
mysqldump --user=root --password=123456 --single-transaction -R --master-data=2 --triggers -E --tab=/data/tab --where "id = 1" test user > /data/back/user_tab.sql;

数据恢复

# 恢复全部
mysql -u root -p < /data/back/all.sql;

# 恢复指定数据库,mysql中需要有一个库去接收
# 将test.sql 恢复至new_test数据库
mysql -u root -p new_test < /data/back/test.sql;

# 恢复表结构就和恢复数据库操作无区别
# 以恢复user表结构为例
mysql -u root -p test < /data/tab/user.sql;

# 恢复表数据需要进入数据库，然后使用load将数据加载到表
# 加载 /data/tab/user.txt 数据 到 user表
mysql> load data infile "/data/tab/user.txt" into table user;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

# 在外部使用mysql命令恢复数据
# 在mysql内部可以使用source完成一样的效果
# 以恢复user表结构为例
source /data/tab/user.sql;