MySQL database recovery of USES the mysqlbinlog command
- 2020-05-10 23:02:32
- OfStack
1: enable binlog logging
Modify the mysql configuration file mysql.ini to add under the [mysqld] node
Do not include Chinese or Spaces in the path. Restart the mysql service. Stop and start the mysql service from the command line
Enter the command line to enter mysql and see if the binary log is started
Sql code
When the log is opened successfully, two files, logbin.index and logbin.000001, are created in the E:/log/ directory. logbin.000001 is the backup file of the database from which you can later restore the database.
2: view the backed up binary files
Sql code
In the future, the record of more operations, the command line method is basically not used. You can view the log contents by exporting the log to a file
2.1 export
Xml code
" > ": import into a file; " > > ": append to file
If there are multiple log files
Sql code
2.2 export at the specified location:
Sql code
2.3 export at the specified time:
Xml code
3: restore the database from a backup
After one update operation, the contents of the log are as follows:
Sql code
3.1 recovery:
Sql code
3.2 restore at the specified position:
Sql code
3.3 restore at the specified time:
Xml code
3.4 recovery via exported script files
Sql code
4. Other common operations
4.1 view all log files
Sql code
4.2 binlog files currently in use
Sql code
4.3 generate a new binlog log file
Sql code
4.4 delete all binary logs and start over again (note: reset master command will delete all binary logs)
Sql code
4.5 quickly backup data to sql files
Sql code
For easy viewing, write the restore command from the script once
Sql code
Modify the mysql configuration file mysql.ini to add under the [mysqld] node
# log-bin
log-bin = E:/log/logbin.log
Do not include Chinese or Spaces in the path. Restart the mysql service. Stop and start the mysql service from the command line
c:\>net stop mysql;
c:\>net start mysql;
Enter the command line to enter mysql and see if the binary log is started
Sql code
mysql>show variables like 'log_%';
When the log is opened successfully, two files, logbin.index and logbin.000001, are created in the E:/log/ directory. logbin.000001 is the backup file of the database from which you can later restore the database.
2: view the backed up binary files
Sql code
c:\mysql\bin\>mysqlbinlog e:/log/logbin.000001
In the future, the record of more operations, the command line method is basically not used. You can view the log contents by exporting the log to a file
2.1 export
Xml code
c:\mysql\bin\>mysqlbinlog e:/log/logbin.000001 > e:/log/log.txt
" > ": import into a file; " > > ": append to file
If there are multiple log files
Sql code
c:\mysql\bin\> mysqlbinlog e:/log/logbin.000001 > e:/log/log.sql
c:\mysql\bin\> mysqlbinlog e:/log/logbin.000002 >> e:/log/log.sq
2.2 export at the specified location:
Sql code
c:\mysql\bin\>mysqlbinlog --start-position=185 --stop-position=338 e:/log/logbin.000001 > e:/log/log3.txt
2.3 export at the specified time:
Xml code
c:\mysql\bin\>mysqlbinlog --start-datetime="2010-01-07 11:25:56" --stop-datetime="2010-01-07 13:23:50" e:/log/logbin.000001 > e:/log/log_by_date22.txt
3: restore the database from a backup
After one update operation, the contents of the log are as follows:
Sql code
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110107 13:23:50 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.53-community-log created 110107 13:23:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ZqMmTQ8BAAAAZgAAAGoAAAABAAQANS4xLjUzLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABmoyZNEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#110107 13:26:58 server id 1 end_log_pos 185 Query thread_id=44 exec_time=1 error_code=0
SET TIMESTAMP=1294378018/*!*/;
SET @@session.pseudo_thread_id=44/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
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 185
#110107 13:26:58 server id 1 end_log_pos 338 Query thread_id=44 exec_time=1 error_code=0
use ncl-interactive/*!*/;
SET TIMESTAMP=1294378018/*!*/;
UPDATE `t_system_id` SET `id_value`='3000' WHERE (`table_name`='t_working_day')
/*!*/;
# at 338
#110107 13:26:58 server id 1 end_log_pos 365 Xid = 8016
COMMIT/*!*/;
DELIMITER ;
DELIMITER /*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110107 13:23:50 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.53-community-log created 110107 13:23:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ZqMmTQ8BAAAAZgAAAGoAAAABAAQANS4xLjUzLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABmoyZNEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#110107 13:26:58 server id 1 end_log_pos 185 Query thread_id=44 exec_time=1 error_code=0
SET TIMESTAMP=1294378018/*!*/;
SET @@session.pseudo_thread_id=44/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
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 185
#110107 13:26:58 server id 1 end_log_pos 338 Query thread_id=44 exec_time=1 error_code=0
use ncl-interactive/*!*/;
SET TIMESTAMP=1294378018/*!*/;
UPDATE `t_system_id` SET `id_value`='3000' WHERE (`table_name`='t_working_day')
/*!*/;
# at 338
#110107 13:26:58 server id 1 end_log_pos 365 Xid = 8016
COMMIT/*!*/;
DELIMITER ;
DELIMITER /*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
3.1 recovery:
Sql code
c:\mysql\bin\>mysqlbinlog e:/log/logbin.000001 | mysql -u root -p
3.2 restore at the specified position:
Sql code
c:\mysql\bin\>mysqlbinlog --start-position=185 --stop-position=338 e:/log/logbin.000001 | mysql -u root -p
3.3 restore at the specified time:
Xml code
c:\mysql\bin\>mysqlbinlog --start-datetime="2010-01-07 11:25:56" --stop-datetime="2010-01-07 13:23:50" e:/log/logbin.000001 | mysql -u root -p
3.4 recovery via exported script files
Sql code
c:\mysql\bin\>mysql -e "source e:/log/log.sql"
4. Other common operations
4.1 view all log files
Sql code
mysql>show master logs;
4.2 binlog files currently in use
Sql code
mysql>show binlog events \g;
4.3 generate a new binlog log file
Sql code
mysql>flush logs;
4.4 delete all binary logs and start over again (note: reset master command will delete all binary logs)
Sql code
mysql > flush logs;
mysql > reset master;
4.5 quickly backup data to sql files
Sql code
c:\mysql\bin>mysqldump -u root -p --opt --quick interactive > e:/log/mysqldump.sql
For easy viewing, write the restore command from the script once
Sql code
c:\>net stop mysql;
c:\>net start mysql;