欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL逻辑备份mysqldump

程序员文章站 2022-05-18 14:26:20
MySQL 备份之 mysqldump mysqldump mysqldump工具备份: 本质:导出的是SQL语句文件 优点:不论是什么存储引擎,都可以用mysqldump备成SQL语句 缺点:速度较慢,导入时可能会出现格式不兼容的突发情况,无法做增量备份和累计增量备份 提供三种级别的备份,表级,库 ......

mysql 备份之 mysqldump

mysqldump

mysqldump工具备份:

本质:导出的是sql语句文件

优点:不论是什么存储引擎,都可以用mysqldump备成sql语句

缺点:速度较慢,导入时可能会出现格式不兼容的突发情况,无法做增量备份和累计增量备份

提供三种级别的备份,表级,库级和全库级

usage: mysqldump [options] database [tables]
or     mysqldump [options] --databases [options] db1 [db2 db3...]
or     mysqldump [options] --all-databases [options]

说明:

如果备份对象下的数据库绝大多数都是myisam类型表,为了保证数据的一致性,备份时需要锁定表

如果是针对innodb的表进行备份由于innodb是事务型的引擎,会话与会话之间是隔离的,所以备份的时候不影响数据库的正常使用,无需锁表

--lock-tables          如果备份的数据库里的表与其他库没有关系的话,那么只需要锁定该库下的表就可以了
--lock-all-tables      如果备份的数据库里的表与其他库有关系的话,那么需要锁定整个mysql数据库的所有库下的所有表
--flush-logs, -f       开始备份前刷新日志
--flush-privileges     备份包含mysql数据库时刷新授权表
--lock-all-tables, -x myisam 一致性 服务可用性
--lock-tables, -l      备份前锁表
--single-transaction   适用innodb引擎,保证一致性 服务可用性
--master-data=1|2      该选项将会记录binlog的日志位置与文件名并追加到文件中

表级备份 / 恢复

表级备份:

[root@admin ~]# mysqldump -p123 db01 table01 > /tmp/mysqlback/table01.sql              # 备份单个表

[root@admin ~]# mysqldump -p123 db01 table01 table02 > /tmp/mysqlback/table01_02.sql   # 备份多个表

表级恢复:

[root@admin ~]# mysql -p123 db01 < /tmp/mysqlback/table01.sql

或者在mysql数据库内使用source命令来执行外部的sql文件
mysql> source /tmp/mysqlback/table01.sql

库级备份 / 恢复

库级备份:

[root@admin ~]# mysqldump --databases db01 -p123 > /tmp/mysqlback/db01.sql             # 备份单个库

[root@admin ~]# mysqldump --databases db01 db02 -p123 > /tmp/mysqlback/db01_02.sql     # 备份多个库

表级恢复:

[root@admin ~]# mysql -p123 < /tmp/mysqlback/db01.sql

mysql> source /tmp/mysqlback/db01_02.sql
说明: 不要从操作层面删除任何一个库和任何一个表,如果真的这么做了,那么恢复的时候会恢复失败,因为mysql库里面不仅记录了用户信息等还记录了数据库表结构等。从操作层面删除,而mysql库里面并不知道。 所以坚决不能从操作层面删除任何一个库和任何一个表。

全库备份 / 恢复

全库级备份:

考虑到数据库有innodb,也有其他类型的表,那么就只能锁表备份

[root@admin ~]# mysqldump -p123 --lock-tables --all-databases > /tmp/mysqlback/alldb.sql

全库级恢复:

[root@admin ~]# mysql -p123 < /tmp/mysqlback/alldb.sql

mysql> source /tmp/mysqlback/alldb.sql

注意:如果是在终端直接在数据目录里面将数据全部删除, 再恢复数据的话就需要初始化才能恢复。

mysqldump+binlog

完全备份(mysqldump)+增量备份(binlog)

适用于中小型数据库;通过结合二进制日志文件,把数据库恢复到最新的状态

二进制日志默认会记录下所有对数据库变化的操作

二进制日志文件中会记录某个操作的详细sql语句,还有执行的时候环境,时间,以及该记录在二进制日志文件的起始和结束点pos值

error log  # 错误日志,记录mysql服务端在运行时产生的错误信息,以及mysql启动和关闭的日志信息(排错)
slow log   # 慢查询日志,慢查询时间阀值,以秒为单位,如果超过这个阀值就是慢查询(调优)
bin log    # 二进制日志 ,记录对数据库增、删、改的sql操作,可以使用这个日志做增量备份(备份)
relay log  # 中继日志(主从复制日志)从机器上从主机器复制过来日志,根据日志来同步数据(复制)

配置二进制日志

查看二进制日志是否开启:

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| variable_name                   | value                    |
+---------------------------------+--------------------------+
| log_bin                         | off                      |
| log_bin_basename                | /data/db/mysql-bin       |
| log_bin_index                   | /data/db/mysql-bin.index |
| log_bin_trust_function_creators | on                       |
| log_bin_use_v1_row_events       | off                      |
| sql_log_bin                     | on                       |
+---------------------------------+--------------------------+
6 rows in set (0.00 sec)

log_bin  |off 关闭   on 开启

修改配置文件

[root@admin ~]# vim /etc/my.cnf
log-bin=/var/lib/mysql/mysql56-bin.log
log-bin  (可直接这样写就ok)

[root@admin ~]# service mysqld restart 
shutting down mysql....                                    [确定]
starting mysql.....                                        [确定]

再次查看开启:

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| variable_name                   | value                    |
+---------------------------------+--------------------------+
| log_bin                         | on                       |
| log_bin_basename                | /data/db/mysql-bin       |
| log_bin_index                   | /data/db/mysql-bin.index |
| log_bin_trust_function_creators | on                       |
| log_bin_use_v1_row_events       | off                      |
| sql_log_bin                     | on                       |
+---------------------------------+--------------------------+
6 rows in set (0.00 sec)

mysqlbinlog

--start-datetime=name 开始的时间
--stop-datetime=name  结束的时间
--start-position=#    开始的位置(pos)
--stop-position=#     结束的位置

示例1

备份

先做全量备份,然后更新数据并误操作,数据恢复

[root@admin ~]# mysqldump -p123 --flush-logs --master-data=2 --all-databases > /tmp/mysqlback/all_back.sql
  • --flush-logs      备份时先将内存中日志写回磁盘,然后截断日志,并产生新的日志文件
  • --master-data=2  该选项将二进制日志的位置和文件名写入到备份文件,等于2表示change
  • master语句被写成sql注释;1表示没有注释,默认是1.

查看完整备份文件中的字段

[root@admin ~]# vim /tmp/mysqlback/all_back.sql

-- change master to master_log_file='mysql-bin.000008', master_log_pos=120;

数据更改

添加几条数据,然后随便删除一个库

更改完后查看mysql-bin.000008日志文件找到误删除的pos值

MySQL逻辑备份mysqldump
[root@admin db]# mysqlbinlog --no-defaults mysql-bin.000008 
/*!50530 set @@session.pseudo_slave_mode=1*/;
/*!40019 set @@session.max_insert_delayed_threads=0*/;
/*!50003 set @old_completion_type=@@completion_type,completion_type=0*/;
delimiter /*!*/;
# at 4
#180520  3:55:12 server id 1  end_log_pos 120 crc32 0x077f82c8     start: binlog v 4, server v 5.6.31-log created 180520  3:55:12
# warning: this binlog is either in use or was not closed properly.
binlog '
iieaww8baaaadaaaahgaaaabaaqans42ljmxlwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaaxaaegggaaaaicagcaaaacgokgrkaacic
fwc=
'/*!*/;
# at 120
#180520  3:56:46 server id 1  end_log_pos 201 crc32 0xa954edb5     query    thread_id=1    exec_time=0    error_code=0
set timestamp=1526759806/*!*/;
set @@session.pseudo_thread_id=1/*!*/;
set @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
set @@session.sql_mode=1075838976/*!*/;
set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\c utf8 *//*!*/;
set @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
set @@session.lc_time_names=0/*!*/;
set @@session.collation_database=default/*!*/;
begin
/*!*/;
# at 201
#180520  3:56:46 server id 1  end_log_pos 316 crc32 0xc34378c8     query    thread_id=1    exec_time=0    error_code=0
use `login`/*!*/;
set timestamp=1526759806/*!*/;
insert into t1(id,name) values(6,'eee')
/*!*/;
# at 316
#180520  3:56:46 server id 1  end_log_pos 347 crc32 0xea43bde1     xid = 1227
commit/*!*/;
# at 347
#180520  3:56:53 server id 1  end_log_pos 428 crc32 0x5fd30851     query    thread_id=1    exec_time=0    error_code=0
set timestamp=1526759813/*!*/;
begin
/*!*/;
# at 428
#180520  3:56:53 server id 1  end_log_pos 543 crc32 0x97402f36     query    thread_id=1    exec_time=0    error_code=0
set timestamp=1526759813/*!*/;
insert into t1(id,name) values(8,'aaa')
/*!*/;
# at 543
#180520  3:56:53 server id 1  end_log_pos 574 crc32 0xfa2cc4ba     xid = 1228
commit/*!*/;
# at 574
#180520  3:57:00 server id 1  end_log_pos 655 crc32 0x7ba6913f     query    thread_id=1    exec_time=0    error_code=0
set timestamp=1526759820/*!*/;
begin
/*!*/;
# at 655
#180520  3:57:00 server id 1  end_log_pos 771 crc32 0x7856052f     query    thread_id=1    exec_time=0    error_code=0
set timestamp=1526759820/*!*/;
insert into t1(id,name) values(10,'bbb')
/*!*/;
# at 771
#180520  3:57:00 server id 1  end_log_pos 802 crc32 0x0b597d2b     xid = 1229
commit/*!*/;
# at 802
#180520  3:57:19 server id 1  end_log_pos 894 crc32 0x47136864     query    thread_id=1    exec_time=0    error_code=0
set timestamp=1526759839/*!*/;
drop database db01
/*!*/;
delimiter ;
# end of log file
rollback /* added by mysqlbinlog */;
/*!50003 set completion_type=@old_completion_type*/;
/*!50530 set @@session.pseudo_slave_mode=0*/;
mysql-bin.000008日志文件

通过查看二进制日志,由于我刚刚是删除的一个db01库,删除db01库的那条操作上面的pos值是at 802, 所以我应该恢复到802

恢复

先进行全库恢复

[root@admin ~]# mysql -p123 < /tmp/mysqlback/all_back.sql
[root@admin ~]# mysqlbinlog --start-position=120 --stop-position=802 /data/db/mysql-bin.000008 |mysql -p123

恢复完成后进入数据库查看是否存在刚刚添加的数据

总结

mysqldump+binlog做增量备份——>通过binlog日志恢复到最新状态

  • 当前数据库必须开启二进制日志(修改配置文件)
  • 使用mysqldump工具做全库备份
  • 更新数据
  • 直接恢复
  • 使用全库备份恢复
  • 使用binlog日志恢复到最新状态