MySQL数据备份与恢复

    一、简介

成都创新互联公司从2013年成立,是专业互联网技术服务公司,拥有项目做网站、网站设计网站策划,项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命,1280元渑池做网站,已为上家服务,为渑池各地企业和个人服务,联系电话:13518219792

    数据在当今的互联网行业中非常重要,掌握了数据能够从中发现价值,所以数据对于公司来书再重要不过了,当我们面临数据的丢失时,比如数据意外删除、自然灾害等,然而数据的备份和恢复就显得尤为重要。   

   

    二、MySQL/MariaDB数据备份与恢复

    常见的备份类型:

    根据备份时数据库是否在线:

    冷备:cold backup,服务器离线,线上业务终止,无法执行读写操作,但是此方法备份数据最为靠谱,丢数据少      热备:hot backup,服务器在线,线上业务正常进行,读写操作都可进行,此方法备份要求复杂 

    温备:warn backup,服务器在线,但是对数据库施加全局锁,只读操作正常进行,不可写 

    根据备份时的接口:

    物理备份:physical backup,直接从数据库的数据目录进行cp复制归档的方式

    逻辑备份:logical backup,把数据从库中提取出来保存为文本文件,主要用到的工具是mysqldump 

    注意:在数据量很大时使用逻辑备份很实用,当数据量小于10G时可以考虑使用mysqldump实现逻辑备份

    根据备份的数据集:

    完全备份:full backup,指的是备份整个库 

    部分备份:partial backup,指的是备份数据库中的部分数据  

    根据备份时是否备份整个 数据还是仅仅备份变化的数据

    完全备份: full backup,备份整个数据库

    增量备份:incremental backup,仅仅备份上一次完全备份之后所改变的数据做备份的为增量备份

    差异备份:differential backup,比如说周一做一次备份,到周二了就把周一和周二这两天的做一次备份,到周三就把周一周二周三的做一次备份,这就叫差异备份。比较容易恢复。

    MYISAM与InnoDB区别:

    MYISAM存储引擎不支持热备,而InnoDB支持 InnoDB记录数据时都会给数据一个序列号,所以在备份时基于MVCC(多版本并发控制)的机制自动加快照,每启动一个事务都会创建当前集的一个快照,而后基于MVCC的机制把每一个序列号都给它记录一份下来,备份时只备份序列号或序列号之前的数据,往后发生的将不做备份,如果事务的隔离级别不是特别高的话,它并不会影响事务的读写操作,而这样备份出来的数据一定是时间点一致的数据,所以要完成热备份,通常是基于事务的存储引擎才能够完成的。           

     Innobase:提供了商业备份工具为Innobackup,可以实现InnoDB的热备支持增量备份;但是对于MyISAM不支持增量备份,只能实现完全备份,属于物理备份,速度比较快。
   Xtrabackup:由Percona组织提供的开源备份工具,物理备份,速度快;

    mysqldump:常用的备份工具,也是个逻辑备份工具,用于小数据备份,一般都是在10G以下的小数据进行备份;可以使用文本进行二次处理;相当于MySQL的客户端工具   

    

    三、数据备份与恢复实战

    常见备份工具:

    mysqldump:逻辑备份工具,备份和恢复过程都比较慢 

    mysqldumper:多线程,的mysqldump,很难实现差异或增量备份 

    lvm-snapshot: 

    接近于热备的工具,因为要事先请求全局锁,而后创建快照,并在创建快照完成后释放全局锁。使用cp,tar等工具进行物理备份;备份和恢复速度快,很难实现增量备份,并且请求全局需要一段时间,  

    逻辑备份工具,快于mysqldump  

    Innobase:商业备份工具,innobackup 

    xtrabackup:由percona提供的开源备份工具 

    innodb热备,增量备份 

    myisam温备,不支持增量  

    物理备份,速度快 

    mysqlhotcopy:几乎冷备 

    mysqldump:仅适用于数据集较小的数据库,数据量达到10G以上时建议不使用,备份太慢 

mysqldump:
mysqldump [options] [db_name [tbl_name ...]]    
mysqldump -uroot -hlocalhost -p testdb>/tmp/testdb.sql #备份单个表
mysql -uroot -hlocalhost -p testdb/tmp/dbs.sql #使用此备份方法恢复时无需连入mysql创建数据库
mysqldump -uroot -hlocalhost -p --all-databases >/tmp/all.sql #备份所有数据库,适用于冷备
备份单库或多库:--databases    备份所有数据库:--all-databases
注意:线上备份时,在实施温备时需施加锁对单表或多表施加锁,让用户只能读而不能写
mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs>/tmp/testdb.sql
mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs >/tmp/testdb1.sql #仅对Innodb存储引擎实现热备,不支持myisam引擎
mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs --master-data=2 >/tmp/testdb2.sql
mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs --master-data=2 >/tmp/testdb3.sql
--lock-all-tables #请求锁定所有表之后备份    --single-transaction #能够对innodb存储引擎实现热备
--flush-logs #备份时滚动日志    --master-data=[0|1|2]设置为1:保存为一个change master语句 设置为2:注释掉的change master语句,设置为0:不记录
数据恢复:建议关闭二进制日志,关闭其他用户连接:set session sql_log_bin=0

使用mysqldump实现备份及其使用二进制日志做恢复:                 

使用mysqldump实现备份,用二进制日志恢复数据,这里我们以当前系统上的数据库hellodb为例:

   第一步:先把hellodb这个数据库做一次完全备份,当数据库的数据很大,比如说大于10G的话不建议使用mysqldump这个工具做备份,而使用物理备份更为合理:

mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --flush-logs --master-data=2 >/tmp/hellodb.sql

   第二步:在数据库hellodb中修改或创建一些表或数据,使得之前备份的数据跟现有的数据库中的数据存在差别,以完后后面的通过二进制日志进行数据恢复:

MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| t1                |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> drop table t1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> create table tb1(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
MariaDB [hellodb]> insert into tb1 values(1,'alren'),(2,'tbname');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| tb1               |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye

   第三步:使用另外一台服务器做恢复测试,将hellodb.sql,及发生改变的二进制日志导入出来拷贝至另一台主机

[root@centos6 binlog]# mysqlbinlog --start-position=245 master-bin.000005
/*!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
#161117  6:43:00 server id 1  end_log_pos 245 Start: binlog v 4, server v5.5.32-MariaDB-log created 161117  6:43:00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
9OAsWA8BAAAA8QAAAPUAAAABAAQANS41LjMyLU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAALfrHBw==
'/*!*/;
# at 245
#161117  7:01:18 server id 1  end_log_pos 352 Querythread_id=23exec_time=0error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1479337278/*!*/;
SET @@session.pseudo_thread_id=23/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `t1` /* generated by server */
/*!*/;
# at 352
#161117  7:01:51 server id 1  end_log_pos 459 Querythread_id=23exec_time=0error_code=0
SET TIMESTAMP=1479337311/*!*/;
create table tb1(id int,name varchar(20))
/*!*/;
# at 459
#161117  7:02:43 server id 1  end_log_pos 530 Querythread_id=23exec_time=0error_code=0
SET TIMESTAMP=1479337363/*!*/;
BEGIN
/*!*/;
# at 530
#161117  7:02:43 server id 1  end_log_pos 642 Querythread_id=23exec_time=0error_code=0
SET TIMESTAMP=1479337363/*!*/;
insert into tb1 values(1,'alren'),(2,'tbname')
/*!*/;
# at 642
#161117  7:02:43 server id 1  end_log_pos 669 Xid = 1515
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos6 binlog]# mysqlbinlog --start-position=245 master-bin.000005 >inre.sql

第四步:恢复数据,进入到mysql的命令行模式下或在命令行,把二进制日志关掉,恢复数据时不需要把恢复信息记录到二进制日志中去;

MariaDB [(none)]>set sql_log_bin=0
MariaDB [hellodb]>source /root/hellodb1.sql
MariaDB [hellodb]> source /root/inre.sql
MariaDB [hellodb]>set sql_log_bin=1
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| tb1               |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.00 sec)

    

如上诉结果说明数据已经恢复到原来最初始的状态,基于mysqldump+二进制日志的备份和恢复完成。




名称栏目:MySQL数据备份与恢复
链接URL:http://pwwzsj.com/article/ishedj.html