MySQL数据实战（初步增量恢复）

[root@localhost ~]# ll /application/mysql
lrwxrwxrwx. 1 root root 26 9月  18 06:19 /application/mysql -> /application/mysql-5.5.32/
[root@localhost ~]# ll /application/mysql/data/
总用量 28720
-rw-rw----. 1 mysql mysql 18874368 9月  20 22:05 ibdata1
-rw-rw----. 1 mysql mysql  5242880 9月  20 22:05 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 9月  19 01:03 ib_logfile1
-rw-r-----. 1 mysql root     16948 9月  20 15:36 localhost.localdomain.err
-rw-rw----. 1 mysql mysql        5 9月  20 15:36 localhost.localdomain.pid
drwx------. 2 mysql root      4096 9月  19 01:02 mysql
drwx------. 2 mysql mysql     4096 9月  20 22:05 oldboy
drwx------. 2 mysql mysql     4096 9月  19 11:51 oldboy_gbk
drwx------. 2 mysql mysql     4096 9月  19 11:54 oldboy_utf8
drwx------. 2 mysql mysql     4096 9月  19 01:02 performance_schema
[root@localhost ~]# vi /etc/my.cnf 　　　　　　　　　　打开log-bin
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# mysql -uroot -pdubin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution

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> use oldboy
Database changed
mysql> update test set name='gongli';
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | gongli |
|  2 | gongli |
|  3 | gongli |
|  4 | gongli |
|  5 | gongli |
+----+--------+
5 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# ll /application/mysql/data/
总用量 28728
-rw-rw----. 1 mysql mysql 18874368 9月  20 22:13 ibdata1
-rw-rw----. 1 mysql mysql  5242880 9月  20 22:13 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 9月  19 01:03 ib_logfile1
-rw-r-----. 1 mysql root     18527 9月  20 22:12 localhost.localdomain.err
-rw-rw----. 1 mysql mysql        5 9月  20 22:12 localhost.localdomain.pid
drwx------. 2 mysql root      4096 9月  19 01:02 mysql
-rw-rw----. 1 mysql mysql      298 9月  20 22:13 mysqlbin_oldboy.000001
-rw-rw----. 1 mysql mysql       25 9月  20 22:12 mysqlbin_oldboy.index
drwx------. 2 mysql mysql     4096 9月  20 22:05 oldboy
drwx------. 2 mysql mysql     4096 9月  19 11:51 oldboy_gbk
drwx------. 2 mysql mysql     4096 9月  19 11:54 oldboy_utf8
drwx------. 2 mysql mysql     4096 9月  19 01:02 performance_schema
[root@localhost ~]# ll /application/mysql/data/mysqlbin_oldboy.000001 
-rw-rw----. 1 mysql mysql 298 9月  20 22:13 /application/mysql/data/mysqlbin_oldboy.000001
[root@localhost ~]# cat /application/mysql/data/mysqlbin_oldboy.000001                              nބ]Fstd!!!oldboyBEGINnބ]^std!!!oldboyupdate test set name='gongli'nބ][root@localhost ~]# file /application/my/application/mysql/data/mysqlbin_oldboy.000001: MySQL replication log
[root@localhost ~]# file /application/mysql/data/mysqlbin_oldboy.000001 
/application/mysql/data/mysqlbin_oldboy.000001: MySQL replication log
[root@localhost ~]# mysqlbinlog /application/mysql/data/mysqlbin_oldboy.000001 
/*!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
#190920 22:12:42 server id 1  end_log_pos 107     Start: binlog v 4, server v 5.5.32-log created 190920 22:12:42 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
Wt6EXQ8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABa3oRdEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190920 22:13:02 server id 1  end_log_pos 177     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1568988782/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 177
#190920 22:13:02 server id 1  end_log_pos 271     Query    thread_id=1    exec_time=0    error_code=0
use `oldboy`/*!*/;
SET TIMESTAMP=1568988782/*!*/;
update test set name='gongli'
/*!*/;
# at 271
#190920 22:13:02 server id 1  end_log_pos 298     Xid = 4
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@localhost ~]# mysql -uroot -pdubin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32-log Source distribution

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> use oldboy 
Database changed
mysql> insert into test values(6,'xiaozhang');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | gongli    |
|  2 | gongli    |
|  3 | gongli    |
|  4 | gongli    |
|  5 | gongli    |
|  6 | xiaozhang |
+----+-----------+
6 rows in set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# cd /application/mysql/data/
[root@localhost data]# ll
总用量 28728
-rw-rw----. 1 mysql mysql 18874368 9月  20 22:16 ibdata1
-rw-rw----. 1 mysql mysql  5242880 9月  20 22:16 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 9月  19 01:03 ib_logfile1
-rw-r-----. 1 mysql root     18527 9月  20 22:12 localhost.localdomain.err
-rw-rw----. 1 mysql mysql        5 9月  20 22:12 localhost.localdomain.pid
drwx------. 2 mysql root      4096 9月  19 01:02 mysql
-rw-rw----. 1 mysql mysql      498 9月  20 22:16 mysqlbin_oldboy.000001
-rw-rw----. 1 mysql mysql       25 9月  20 22:12 mysqlbin_oldboy.index
drwx------. 2 mysql mysql     4096 9月  20 22:05 oldboy
drwx------. 2 mysql mysql     4096 9月  19 11:51 oldboy_gbk
drwx------. 2 mysql mysql     4096 9月  19 11:54 oldboy_utf8
drwx------. 2 mysql mysql     4096 9月  19 01:02 performance_schema
[root@localhost data]# cp mysqlbin_oldboy.000001 /opt/
[root@localhost data]# mysqladmin -uroot -pdubin flush-log
[root@localhost data]# ll
总用量 28732
-rw-rw----. 1 mysql mysql 18874368 9月  20 22:16 ibdata1
-rw-rw----. 1 mysql mysql  5242880 9月  20 22:16 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 9月  19 01:03 ib_logfile1
-rw-r-----. 1 mysql root     18527 9月  20 22:12 localhost.localdomain.err
-rw-rw----. 1 mysql mysql        5 9月  20 22:12 localhost.localdomain.pid
drwx------. 2 mysql root      4096 9月  19 01:02 mysql
-rw-rw----. 1 mysql mysql      547 9月  20 22:18 mysqlbin_oldboy.000001
-rw-rw----. 1 mysql mysql      107 9月  20 22:18 mysqlbin_oldboy.000002
-rw-rw----. 1 mysql mysql       50 9月  20 22:18 mysqlbin_oldboy.index
drwx------. 2 mysql mysql     4096 9月  20 22:05 oldboy
drwx------. 2 mysql mysql     4096 9月  19 11:51 oldboy_gbk
drwx------. 2 mysql mysql     4096 9月  19 11:54 oldboy_utf8
drwx------. 2 mysql mysql     4096 9月  19 01:02 performance_schema
[root@localhost data]# mysqladmin -uroot -pdubin oldboy </opt/oldboy_bak.sql 
mysqladmin: Unknown command: 'oldboy'
[root@localhost data]# mysql -uroot -pdubin oldboy </opt/oldboy_bak.sql 
[root@localhost data]# mysql -uroot -pdubin -e "select * from oldboy.tset;"
ERROR 1146 (42S02) at line 1: Table 'oldboy.tset' doesn't exist
[root@localhost data]# mysql -uroot -pdubin -e "select * from oldboy.test;"
+----+---------+
| id | name    |
+----+---------+
|  1 | oldboy  |
|  2 | oldgirl |
|  3 | inca    |
|  4 | zuma    |
|  5 | kaka    |
+----+---------+
[root@localhost data]# mysqlbinlog -d oldboy mysqlbin_oldboy.000001 >bin.sql
[root@localhost data]# vim bin.sql 　　　　　　删除错误的命令行
[root@localhost data]# mysql -uroot -pdubin oldboy <bin.sql 
[root@localhost data]# mysql -uroot -pdubin -e "select * from oldboy.test;"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | xiaozhang |
+----+-----------+
[root@localhost data]#