Mysql bin-log and data recovery

1. Mysqlbin-logLogs

1.EnableMysqlbin-logLogs

# Vi/etc/my. cnf

[Client]

# Password = your_password

Port = 3306

Socket =/tmp/mysql. sock

Default-character-set-utf8

.......

[Mysqld]

Port = 3306

Socket =/var/lib/mysql. sock

Log-slow-queries = mysql-slowlog

Log-error = mysql. err

Log = mysql. log

Log-bin = mysql-bin //Delete # symbol

2.View binary filesMysql-bin .******)

# Cd/usr/local/mysql/bin/& ls

3.ViewMysqbinlogLog enabled?

Mysql> showvariables like "% log-bin %;

4.AndMysqlbin-logRelated log operations

Mysql> flushlogs; // The latest bin-log will be added.

Mysql> showmaster status; // view the last bin log

Mysql> resetmaster; // clear all bin-log logs

Mysql> mysqlbinlog-no-defaults mysql-bin. ****** | more // view the bin-log

Ii. Mysqlbin-logLog Recovery

1.Back up existing data(Library)

# Mysqldump-uroot-p123456 test-l-F>/tmp/test. SQL //-F is flushlogs and-l is the read lock. You cannot perform write operations during Backup.

2.First recovery operation

# Mysql-uroot-p123456 test-v (optional)-f </tmp/test. SQL

3.Second slaveBin-logLog Recovery

#/Usr/local/mysql/bin/mysqlbinlog -- no-defaults -- stop-position = "position_num" mysql-bin. * ***** |/usr/local/mysql/bin/mysql-uroot-p123456 // position is used to record addition, deletion, and modification operations.

3. A completeMysqlbin-logLog Recovery Process instance

1 ).Create a table:

Mysql> usetest;

Mysql> createtable t1 (id int );

Mysql> showtables;

2 ).Add two rows of data:

Mysql> insertinto t1 values (1 );

Mysql> insertinto t1 values (2 );

Mysql> select * from t1;

3 ).Back up existing data):

# Mysqldump-The uroot-p123456 test-l-F>/tmp/test. SQL

4 ).Add three rows of data records after the backup):

Mysql> insertinto t1 values (3 );

Mysql> insertinto t1 values (4 );

Mysql> insertinto t1 values (5 );

5 ).In this case, the database is suddenly damaged or the database is deleted:

Mysql> droptable t1;

6 ).Use the backed upTest. SQLTo restore the database:

# Mysql-uroot-p123456 test <test. SQL

7 ).SlaveBin-logLog Recovery:

#/Usr/local/mysql/bin/mysqlbinlog-no-defaults mysql-bin.000006 | more // view bin-log logs on my virtual machine bin-log logs starting from the mysql-bin.000005

#/Usr/local/mysql/bin/mysqlbinlog -- no-defaults -- stop-position = "272" mysql-bin.000006 |/usr/local/mysql/bin/mysql-uroot-p123456/ /restore the bin-log

This article is from the "mhf_zzjc" blog, please be sure to keep this http://1meir3a.blog.51cto.com/6176787/1298413