mysql二进制日志重置_MySQL备份与恢复常用方法总结（mysqldump/xtrabackup/lvm快照备份/逻辑备份与恢复/二进制日志及时点恢复）...

MySQL备份与恢复常用方法总结

(mysqldump/xtrabackup/lvm快照备份/逻辑备份与恢复/二进制日志及时点恢复)

自言：学习在于总结，把所了解的类似东西放到一起更能加深记忆

一、了解备份相关知识

1)按服务器备份时状态可分为：

热备份：读、写不受影响；

温备份：仅可以执行读操作；

冷备份：离线备份；读、写操作均中止；

2)按服务器备份数据集可分为：

物理备份：复制数据文件；

逻辑备份：将数据导出至文本文件中；

3)按服务器备份数据量可分为：

完全备份：备份全部数据；

增量备份：仅备份上次完全备份或增量备份以后变化的数据；

差异备份：仅备份上次完全备份以来变化的数据；

4)我们需要备份些什么数据文件？

数据、配置文件、二进制文件、事务日志

二、学习相关备份工具

1)mysqldump：逻辑备份工具

2)lvm-snapshot：温备(首先需要先全局锁表，而后创建LVM快照，然后释放锁。)

3)xtrabackup：完全备份、增量备份以及高级部分备份

4)逻辑备份

三、mysqldump备份与恢复实验

1)准备条件：查看当前数据库状态mysql> show databases; //查看己有的数据库；

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

mysql> show tables; //查看当前数据库mydb中有哪些表；

+----------------+

| Tables_in_mydb |

+----------------+

| tb1            |

+----------------+

1 row in set (0.00 sec)

mysql> select * from tb1;   //查看tb1数据；

+----+-----------+--------+------+

| id | name      | gender | age  |

+----+-----------+--------+------+

|  1 | jun.wang  | m      |   25 |

|  2 | jerry.liu | m      |   27 |

|  3 | windy.ma  | m      |   30 |

|  4 | lilian.wu | f      |   27 |

+----+-----------+--------+------+

4 rows in set (0.00 sec)

mysql> show binary logs; //如果此时你的系统中有多个二进制日志文件，可以用下面一条命令将其手动删除；

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| master-bin.000005 |       120 |

+-------------------+-----------+

1 row in set (0.00 sec)

mysql> purge binary logs to 'master-bin.000005'; //删除之前的所有二进制日志；

mysql> show master status;  //查看当前正在使用的二进制日志；

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000006 |      120 |              |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2)全量数据备份与增量备份# mysqldump --lock-all-tables --master-data=2 --all-databases --flush-logs >mydb_all_backup_`date +%F`.sql

mysqldump常用选项：

--master-data={0|1|2}

0：不记录二进制日志文件及路径位置

1：以change master to 的方式记录位置，可用于恢复后直接启动从服务器

2：以change master to 的方式记录位置，但默认为被注释

--lock-all-tables: 锁定所有表

--flush-logs: 执行日志flush

如果指定库中的表类型为Innodb则可以使用--single-transaction启动热备；

--all-databases :备份所有库

--databases DB_NAME1,DB_NAME2,....: 备份多个库

--events :事务

--routines :存储过程，存储函数

--triggers :触发器

mysql> insert into tb1 (name,gender,age) values('tom.chen','m',47);

Query OK, 1 row affected (0.08 sec)

mysql> insert into tb1 (name,gender,age) values('tina.li','f',35);

Query OK, 1 row affected (0.03 sec)

# cp /mydata/data/master-bin.000006 /root/mydb_backup/

1.模拟数据全部删除：

# service mysqld stop

Shutting down MySQL..                                      [  OK  ]

# rm -fr /mydata/data/*

2.重新初始化

# cd /usr/local/mysql

# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

3.my.cnf配置文件

datadir = /mydata/data

port = 3306

server_id = 1

socket = /tmp/mysql.sock

innodb_file_per_table = 1

query_cache_type = 1

log-bin = mysql-bin

4.启动mysql服务

# service mysqld start

Starting MySQL..                                           [  OK  ]

5.恢复操作

1)全量恢复操作

# mysql 

# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

mysql> use mydb;

mysql> show tables;

+----------------+

| Tables_in_mydb |

+----------------+

| tb1            |

+----------------+

1 row in set (0.00 sec)

mysql> select * from tb1;

+----+-----------+--------+------+

| id | name      | gender | age  |

+----+-----------+--------+------+

|  1 | jun.wang  | m      |   25 |

|  2 | jerry.liu | m      |   27 |

|  3 | windy.ma  | m      |   30 |

|  4 | lilian.wu | f      |   27 |

+----+-----------+--------+------+

4 rows in set (0.00 sec)

2)增量恢复操作

# mysqlbinlog master-bin.000006 |mysql

mysql> select * from mydb.tb1;

+----+-----------+--------+------+

| id | name      | gender | age  |

+----+-----------+--------+------+

|  1 | jun.wang  | m      |   25 |

|  2 | jerry.liu | m      |   27 |

|  3 | windy.ma  | m      |   30 |

|  4 | lilian.wu | f      |   27 |

|  5 | tom.chen  | m      |   47 |

|  6 | t