Linux recovers databases via MySQL binary log binlog

Linux recovers databases via MySQL binary log binlog

Go to MySQL

See if MySQL logs are started

Go to MySQL

Mysql>show variables like ' log_% ';

The following binary log setup methods for Linux are as follows

In the/etc/my.cnf file, under "Mysqld", add

Server-id = 1

Log-bin = Binlog

Log-bin-index = Binlog.index

Binlog log file defaults to/var/lib/mysql

View Binlog log file commands

Mysqlbinlog binlog.000001

To view the Binlog file, filter for error actions, such as the Delete data table operation on line No. 2586, use the SQL statement before 2586

Methods are as follows

Mysqlbinlog--stop-position= "2586"/var/lib/mysql/binlog.000001 >~/nn/backup_001.sql

Restores

Mysql–u Root–p Nsq_name < ~/nn/backup_001.sql

Note Be sure to note the spaces and capitalization

Other:

Linux Backup Database

1. Backup

1 [[Email protected] ~]# mysqldump-u root-p mysql > ~/mysql.sql #把数据库mysql备份到家目录下命名为mysql. sql

2 Enter Password:

3 [[email protected] ~]# ls ~/mysql.sql

/root/mysql.sql

2. Restore

[Email protected] ~]# mysql-u root-p Web < ~/web.sql #把web. SQL Import Database Web

Common Binlog Log Operations commands

1. View all Binlog log lists      mysql> show master logs;
2. View the master status, which is the last (most recent) one Binlog log's number name, and its last action event Pos end point (Position) value      mysql> show Master status;
3. Refresh log log, starting from now to produce a new numbered Binlog log file      mysql> flush logs;
Note: Each time the MYSQLD service restarts, this command is automatically executed to refresh the Binlog log;
Adding the-F option to mysqldump backup data also refreshes the Binlog log;    
4. Reset (empty) all Binlog logs      mysql> reset master;

Linux recovers databases via MySQL binary log binlog