MySQL bin-log

What is 1.bin-log?

Bin-log is a very important log for MySQL. It records all DDL and DML (except data query statements) statements in the form of events, as well as the time spent on the execution of the statements, and the MySQL binary log is transaction-safe.

Its main use

one。 Data recovery

two。 Master-slave replication

two。 Turn on bin-log

Vi / etc/mysql/my.cnf

Log_bin = / var/log/mysql/mysql-bin.log

Expire_logs_days = 10

Max_binlog_size = 100m

3. Verify whether it is turned on

Mysql > show variables like "log%"

Check whether log_bin is ON

Catalog view

/ var/log/mysql > ls

4. Commands relating to bin-log

Command interpretation flush logs restart a bin-log log show master status view the latest bin-log log reset master clear all bin-log logs

5. View bin-log Log

/ var/log/mysql > mysqlbinlog mysql-bin.000251

6. Experiment 1 on data recovery of bin-log @ create table br/ > one @ create table

Mysql > create table T2 (A1 int (10) not null default 0) engine=innodb default charset=utf8; 2 @ insert data br/ > 2 @ insert data br/ > 3 @ backup data

Zc@ubuntu:~$ ls-la / tmp/t2_ bak.sql add three pieces of data after backup br/ > four @ backup add three pieces of data

Mysql > insert into T2 values (4)

Mysql > insert into T2 values (5); five @ table is deleted br/ > five @ table is deleted

Six @ restore data with backed-up t2_bak.sql (previous paragraph)

Zc@ubuntu:~$ mysql-uroot-paired alarm * test

< /tmp/t2_bak.sql mysql>

The three pieces of data after show tables; 7 @ backup are restored with bin-log, and the three pieces of data after br/ > 7 @ backup are restored with bin-log to find out the location (the latter part).

Eight @ begin to recover

Zc@ubuntu:/var/log/mysql$ mysqlbinlog-- stop-position= "653" mysql-bin.000257 | mysql- uroot-paired verification data * Test9 @ validation data br/ > Nine @ validation data

7. Appendix A

-- stop-position= "100"-- start-position= "50"-- stop-date= "YYYY-mm-dd HH:ii:ss"-- start-date= "YYYY-mm-dd HH:ii:ss"

8. Appendix B

Mysql exports table structure and mysqldump usage of table data

The specific usage under the command line is as follows: mysqldump-u uses the name-p password-d number library name table name script name

1. Export the table structure of dbname (where the name is root, the password is dbpasswd, and the generated script is db.sql) mysqldump-uroot-pdbpasswd-d dbname > db.sql;2, mysqldump-uroot-pdbpasswd-d dbname test > db.sql;3 of a table (test) structure of dbname, and all table structures and table numbers of dbname (without-d) mysqldump-uroot-pdbpasswd dbname > db.sql. 4. The export database is dbname test structure and table data (without-d) mysqldump-uroot-pdbpasswd dbname test > db.sql