MYSQL数据备份与恢复（mysqldump）

MySQL备份之mysqldump

表级别备份还原

格式：mysqldump [OPTIONS] database [tables]

实例：把db_user数据库中的tb_student数据表进行备份

备份：#mysqldump db_user tb_student > /tmp/sqlbak/tb_student.sql -p

还原：#mysql 数据库名称 < .sql文件位置 -p

库级别备份还原

格式：mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

备份：#mysqldump --databases db_itheima > /tmp/sqlbak/db_itheima.sql -p

还原：#mysql < .sql文件位置 -p

全库级别备份还原

1. 在MySQL中，如果想使用mysqldump进行全库级备份，必须开启二进制日志

2. # vim my.cnf

   [mysqld]

   ...

   server-id=10

   log-bin=/export/server/mysql/data/binlog

3. # service mysqld restart

4. # ll /export/server/mysql/data/

格式：#mysqldump [OPTIONS] --all-databases [OPTIONS]

备份：#mysqldump --all-databases --source-data --single-transaction > /tmp/sqlbak/all.sql -p

还原：# mysql < /tmp/sqlbak/all.sql -p

mysqldump高级选项说明：

常用选项	描述说明
--flush-logs, -F	开始备份前刷新日志（二进制日志）binlog.000001 => binlog.000002
--flush-privileges	备份包含mysql数据库时刷新授权表 => 刷新用户和授权信息
--lock-all-tables, -x	MyISAM一致性，服务可用性（针对所有库所有表）
--lock-tables, -l	备份前锁表（针对要备份的库）
--single-transaction	适用InnoDB引擎，保证一致性，服务可用性
--source-data=1	表示将二进制日志位置和文件名写入到备份文件,并在dump文件中不注释掉这一行
--source-data=2	表示将二进制日志位置和文件名写入到备份文件,在dump文件中注释这一行

mysqldump + binlog增量备份

实施步骤：

• 要有全量备份 ② 继续增删改数据 ③ 再次需要备份时，不需要进行全量备份，只需要备份binlog日志文件即可（因为binlog日志记录了增删改操作的所有SQL语句）

第一步：先准备数据（前提）

第二步：开启二进制，然后做全量备份（全库备份）

第三步：继续对数据库进行增删改操作（还未备份）

第四步：突然发生了硬件故障，数据库丢失了

第五步：恢复全量备份导出的数据（不完整，可能只有90%）

第六步：备份二进制日志，根据其信息导入剩余的10%的数据

第一步：准备数据

mysql> create database db_user default charset=utf8;

mysql> db_user;

mysql> create table tb_student(

    id int not null auto_increment,

    name varchar(20),

    age tinyint unsigned default 0,

    gender enum('male','female'),

    subject enum('ui','java','bigdata','test'),

    primary key(id)

) engine=innodb default charset=utf8;

mysql> insert into tb_student values (null,'刘备',33,'male','java');

mysql> insert into tb_student values (null,'关羽',32,'male','bigdata');

mysql> insert into tb_student values (null,'张飞',30,'male','bigdata');

mysql> insert into tb_student values (null,'貂蝉',18,'female','ui');

mysql> insert into tb_student values (null,'大乔',18,'female','ui');

第二步：开启二进制，然后做全量备份

# vim /etc/my.cnf

[mysqld]

...

server-id=10

log-bin=/export/server/mysql/data/binlog

binlog_format=statement

default_authentication_plugin=mysql_native_password

# service mysqld restart

# rm -rf /tmp/sqlbak/*

# mysqldump --single-transaction --flush-logs --source-data=2 --all-databases > /tmp/sqlbak/all.sql -p

注意：--flush-logs会让系统重新生成一个新的二进制文件，以后增量数据都会写入到新二进制文件

第三步：继续对数据库进行增删改操作

mysql> insert into tb_student values (null,'小乔',16,'female','ui');

mysql> delete from tb_student where id = 3;

第四步：突然发生了硬件故障，数据库丢失了

# mysql -e "drop database db_user;" -p

Enter password:123

第五步：开始进行数据恢复，马上把最新的二进制文件进行备份

# cp /export/server/mysql/data/binlog.000003 空格 /tmp/sqlbak/

第六步：先进行全库恢复

# mysql < /tmp/sqlbak/all.sql -p

Enter password:123

第七步：通过binlog增量备份还原数据到100% => at 362 ~ at 841=> 增量数据

# mysqlbinlog /tmp/sqlbak/binlog.000003 |less

# mysqlbinlog /tmp/sqlbak/binlog.000003  =>  重点找事故的临界点，如drop database

确认at位置

# mysqlbinlog --start-position=362 --stop-position=841 /tmp/sqlbak/binlog.000003 |mysql -p