mysql差异备份

1. mysql差异备份

差异备份用到的工具：

• mysqladmin ----用来备份数据库
• mysqlbinlog ----用来恢复数据库

//开启mysql服务的二进制日志功能

	[root@xaii-59 ~]# vim /etc/my.cnf
	[root@xaii-59 ~]# tail  /etc/my.cnf
	basedir = /usr/local/mysql
	datadir = /opt/data
	socket = /tmp/mysql.sock
	port = 3306
	pid-file = /opt/data/mysql.pid
	user = mysql
	skip-name-resolve
	
	server-id = 1			//服务器的标志符
	log-bin = mysql_bin		//开启二进制日志功能

	[root@xaii-59 ~]# service mysqld restart 
	Shutting down MySQL.... SUCCESS! 
	Starting MySQL.. SUCCESS! 

//对数据库进行完全备份

	mysql> selete * from lizhao.student；
	+----+-----------+------+
	| id | name      | age  |
	+----+-----------+------+
	|  1 | tom       |   20 |
	|  2 | jerry     |   23 |
	+----+-----------+------+
	
	[root@xaii-59 ~]# mysqldump -uroot -plizhao123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902221900.sql
	mysqldump: [Warning] Using a password on the command line interface can be insecure.

	//添加新内容
	mysql> insert into student values(3,‘messi',22),(4,'leo',23); 
	+----+-----------+------+
	| id | name      | age  |
	+----+-----------+------+
	|  1 | tom       |   20 |
	|  2 | jerry     |   23 |
	|  3 | messi     |   22 |
	|  4 | leo       |   23 |
	+----+-----------+------+

//修改messi年龄

	mysql> update student set age = 30 where id = 4;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0

	mysql> selete * from lizhao.student；
	+----+-----------+------+
	| id | name      | age  |
	+----+-----------+------+
	|  1 | tom       |   20 |
	|  2 | jerry     |   23 |
	|  3 | messi     |   22 |
	|  4 | leo       |   30 |
	+----+-----------+------+

//模拟删除lizhao数据库

	mysql> drop database lizhao;
	Query OK, 2 rows affected (0.19 sec)

	Query OK, 3 rows affected (0.31 sec)

	mysql> show databases;
	+--------------------+
	| Database           |
	+--------------------+
	| information_schema |
	| mysql              |
	| performance_schema |
	| sys                |
	| zabbix             |
	+--------------------+
	5 rows in set (0.00 sec)

	[root@xaii-59 ~]# mysqladmin -uroot -plizhao123! flush-logs
	mysqladmin: [Warning] Using a password on the command line interface can be insecure.
	[root@xaii-59 ~]# ll /opt/data
	总用量 188580
	-rw-r-----. 1 mysql mysql       56 2月  20 15:32 auto.cnf
	-rw-r-----  1 mysql mysql     4326 2月  23 02:57 ib_buffer_pool
	-rw-r-----. 1 mysql mysql 79691776 2月  23 03:12 ibdata1
	-rw-r-----. 1 mysql mysql 50331648 2月  23 03:12 ib_logfile0
	-rw-r-----. 1 mysql mysql 50331648 2月  20 15:31 ib_logfile1
	-rw-r-----  1 mysql mysql 12582912 2月  23 03:02 ibtmp1
	drwxr-x---. 2 mysql mysql     4096 2月  21 21:48 mysql
	-rw-r-----  1 mysql mysql      364 2月  23 03:14 mysql_bin.000002
	-rw-r-----  1 mysql mysql      154 2月  23 03:14 mysql_bin.000003 （新生成的日志文件）
	-rw-r-----  1 mysql mysql       38 2月  23 03:14 mysql_bin.index
	-rw-r-----  1 mysql mysql        6 2月  23 02:57 mysql.pid
	drwxr-x---. 2 mysql mysql     8192 2月  20 15:32 performance_schema
	drwxr-x---. 2 mysql mysql     8192 2月  20 15:32 sys
	-rw-r-----  1 mysql mysql    49454 2月  23 02:57 xaii-59.err
	-rw-r-----. 1 mysql mysql    38337 2月  22 00:38 xaii.err
	drwxr-x---  2 mysql mysql    12288 2月  23 00:40 zabbix

//恢复完全备份

	[root@xaii-59 ~]# mysql < all-201902221900.sql
	
	mysql> show databases;
	+--------------------+
	| Database           |
	+--------------------+
	| information_schema |
	| lizhao             |
	| mysql              |
	| performance_schema |
	| sys                |
	| zabbix             |
	+--------------------+
	6 rows in set (0.12 sec)

	mysql> selete * from lizhao.student；
	+----+-----------+------+
	| id | name      | age  |
	+----+-----------+------+
	|  1 | tom       |   20 |
	|  2 | jerry     |   23 |
	|  3 | messi     |   22 |
	|  4 | leo       |   23 |
	+----+-----------+------+

//只恢复到了没有修改数据前的数据表，查询正在使用的二进制文件

	mysql> show master status;
	+------------------+----------+--------------+------------------+-------------------+
	| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
	+------------------+----------+--------------+------------------+-------------------+
	| mysql_bin.000003 |  5008264 |              |                  |                   |
	+------------------+----------+--------------+------------------+-------------------+
	1 row in set (0.00 sec)

//查看日志，找到误删记录在哪

	
	mysql> show binlog events in 'mysql_bin.000002';
	+------------------+-----+----------------+-----------+-------------+---------------------------------------+
	| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
	+------------------+-----+----------------+-----------+-------------+---------------------------------------+
	| mysql_bin.000002 |   4 | Format_desc    |        99 |         123 | Server ver: 5.7.23-log, Binlog ver: 4 |
	| mysql_bin.000002 | 123 | Previous_gtids |        99 |         154 |                                       |
	| mysql_bin.000002 | 154 | Anonymous_Gtid |        99 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
	| mysql_bin.000002 | 219 | Query          |        99 |         293 | BEGIN                                 |
	| mysql_bin.000002 | 293 | Table_map      |        99 |         349 | table_id: 111 (lizhao.student)        |
	| mysql_bin.000002 | 349 | Update_rows    |        99 |         409 | table_id: 111 flags: STMT_END_F       |
	| mysql_bin.000002 | 409 | Xid            |        99 |         440 | COMMIT /* xid=2484 */                 |
	| mysql_bin.000002 | 440 | Anonymous_Gtid |        99 |         505 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
	| mysql_bin.000002 | 505 | Query          |        99 |         603 | drop database lizhao                  |
	| mysql_bin.000002 | 603 | Rotate         |        99 |         650 | mysql_bin.000003;pos=4                |
	+------------------+-----+----------------+-----------+-------------+---------------------------------------+
	10 rows in set (0.00 sec)

//恢复到没有误删之前的数据库

	[root@xaii-59 ~]# mysqlbinlog --stop-position=505 /opt/data/mysql_bin.000002 |mysql -uroot -plizhao123!
	mysql: [Warning] Using a password on the command line interface can be insecure.

	
	[root@xaii-59 ~]# mysql
	Welcome to the MySQL monitor.  Commands end with ; or \g.
	Your MySQL connection id is 26
	Server version: 5.7.23-log MySQL Community Server (GPL)
	
	Copyright (c) 2000, 2018, 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> 

	mysql> use lizhao;
	mysql> selete * from lizhao.student；
	+----+-----------+------+
	| id | name      | age  |
	+----+-----------+------+
	|  1 | tom       |   20 |
	|  2 | jerry     |   23 |
	|  3 | messi     |   22 |
	|  4 | leo       |   30 |
	+----+-----------+------+