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
全库级别备份还原
- 在MySQL中，如果想使用mysqldump进行全库级备份，必须开启二进制日志
# vim my.cnf
[mysqld]
...
server-id=10
log-bin=/export/server/mysql/data/binlog
# service mysqld restart
# 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