基于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;

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

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 linuxeaG

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 滚动日志，复制日志

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