基于mysqldump二进制备份恢复

备份需要哪些文件?
除了数据库数据,还有mysql配置文件,mysql代码(存储过程,存储函数,触发器) ,OS相关的 配置文件,如:crontab配置计划任务及脚本,
备份工具本身也需要备份,在主从环境中和复制相关的配置,二进制日志文件,也需要备份!

备份工具:
mysqldump单机备份工具,对Innodb热备,MyISAM温备,Aria温备。但是mysqldump备份和恢复的过程较慢,数据过10G后,mysqldump则不建议使用
mysqldumper多线程mysqldump,很难实现差异活增量备份,只能人工手动增量
lvm-snapshot:快照备份,接近于热备工具:先请求全局锁,而后创建快照,快照创建完后释放全局锁
使用cp.tar等工具进行物理备份
备份和恢复速度较快,很难实现增量备份,并且请求全局需要等待一段时间,在繁忙的服务器尤其如此

物理备份工具,不会备份关系等译,仅备份表中数据、
逻辑备份工具,快于mysqldump

innobase商业备份工具,innobackup,innobackup实现innodb热备,增量备份,但是对于myisa只能温备和完全备份。
xtrabackup开源的备份工具,由percona提供
mysqlhotcopy:几乎冷备,接近于冷备工具

备份单个库

备份单个库 [root@mysql-salve ~]# mysqldump -uroot -hlocalhost linuxea > /tmp/linuxea.sql

恢复单个库
恢复之前,如果库本身不存在则需要事先创建:MariaDB [(none)]> create databases linuxea;

[root@mysql-salve ~]# mysql < /tmp/linuxea.sql

备份所有数据库--all-databases
备份所有数据库: mysqldump --all-databases > /tmp/all.sql
备份挑选的库:--databases db1,db2,备份指定的多个库

[root@mysql-salve ~]# mysqldump --databases linuxea123 linuxea > /tmp/linuxeaall.sql

恢复

[root@mysql-salve ~]# mysql < /tmp/linuxeaall.sql 

mysqldump备份前需要加锁:

`--lock-all-tables`:请求锁定所有表之后在备份,对MyISA温备,Innodb做温备,Aria做温备
[root@mysql-salve ~]# mysqldump --databases linuxea123 linuxea --lock-all-tables < /tmp/linuxeaall.sql

单事务
单事务--single-transaction:能够对innodb存储引擎实现热备
备份代码
--events:事件调度器代码
--routines:备份存储过程和存储函数
--triggers:备份触发器
备份时滚动日志:
--flush-logs:备份前,请求到锁之后滚动日志
复制时的同步位置标记: --master-data=[0|1|2]
0:不急了
1:记录change master 语句
2:记录为注释的CHANGE MASTER语句
使用mysqldump备份
请求锁:--local-all-tables或者使用--singe-transaction进行innodb热备
滚动日志:--flush-logs
选定要被备份的库:--databases
记录二进制日志文件及位置:SHOW MASTER STATUS;
手动备份

刷新所有表,将缓存数据同步至磁盘

MariaDB [(none)]> FLUSH TABLES;

一,手动锁表备份1
1,手动请求全局锁
刷新所有表,将缓存数据同步至磁盘并且加全锁!

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;

查看日志的其实位置

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |     1125 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2,手动备份

[root@mysql-salve ~]# mysqldump --databases mysql > /tmp/mysql.sql

3,在释放锁

MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

二,手动锁表备份2

[root@mysql-salve ~]# mysqldump --databases mysql --lock-all-tables --flush-logs > /tmp/mysql-3.sql

三,手动热备1

[root@mysql-salve ~]# mysqldump --databases mysql --single-transaction  --flush-logs > /tmp/mysql-3.sql

及时点还原,需要一次全备来作为下次恢复的起点时间

1,查看表的存储引擎

MariaDB [(none)]> SHOW TABLE STATUS FROM linuxea\G

2,全表温备份

[root@mysql-salve ~]# mysqldump --databases mydb --lock-all-tables --flush-logs --master-data=2 > /opt/11.sql

1,插入数据

MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| students       |
| t1             |
| t2             |
| t3             |
| t4             |
| t5             |
| t6             |
+----------------+
7 rows in set (0.00 sec)

MariaDB [mydb]> create table nettb1(NAME CHAR(30));
Query OK, 0 rows affected (0.01 sec)

MariaDB [mydb]> insert into nettb1 values ('Tom'),('Jerry');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [mydb]> 

删除表!

MariaDB [(none)]> DROP DATABASE mydb;
Query OK, 8 rows affected (0.05 sec)

在删除后进行还原到创建数据时需要断开远程链接
1,找到二进制日志文件,根据上次完全备份的日志段开始进行查看

[root@mysql-salve tmp]# vim /opt/11.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=245;


2,找到执行DROP DATABASE mydb;上一个at导出
[root@mysql-salve tmp]# mysqlbinlog --start-position=245 /data/mysql/mysql-bin.000017

# at 516
#160121  1:00:13 server id 1  end_log_pos 543   Xid = 5538
COMMIT/*!*/;
# at 543
#160121  1:00:45 server id 1  end_log_pos 624   Query   thread_id=58    exec_time=0 error_code=0
SET TIMESTAMP=1453366845/*!*/;
DROP DATABASE mydb
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


3,保存
[root@mysql-salve tmp]# mysqlbinlog --start-position=245 --stop-position=516 /data/mysql/mysql-bin.000017 > /opt/22.sql

还原
3,关掉二进制session
(1)

 MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.01 sec)

关闭session MariaDB [(none)]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

(2)

恢复全备 MariaDB [(none)]> source /opt/11.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

(3)

恢复增量MariaDB [mydb]> source /opt/22.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec

(4查看)

MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| nettb1         |
| students       |
| t1             |
| t2             |
| t3             |
| t4             |
| t5             |
| t6             |
+----------------+
8 rows in set (0.00 sec)

MariaDB [mydb]> 

1 row in set (0.00 sec)

5,上线二进制日志

上线二进制日志 MariaDB [mydb]> set session sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

备份策略:基于

mysqldump

备份:
mysqldump+二进制日志文件
如:每周做完全备份,备份时滚动日志
周一到周日做二进制日志备份
mysqladmin flush-logs 滚动日志,复制日志

恢复:
完全备份+各二进制日志文件中至此刻的事件

1 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2016-01-21分类: Mariadb

标签: mariadb, xtrabackup

发表评论