[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]#