Mysql backup and import data (including binary logs)

Mysql backup and data import (including binary logs) Preface, some frequently used commands are described below? Backup Data: C: Usersxxxxmysqldump-uxxx-pxxxbackupbackup.sqlC: Usersx

Mysql backup and data import (including binary logs) Preface, some frequently used commands are described below? Backup Data: C: \ Users \ xxxxmysqldump-uxxx-pxxx backup. sqlC: \ Users \ x

Mysql backup and import data (including binary logs)

Preface: When I wrote a blog this morning, I thought over and over again. Without a better clue, I remembered some of the commands that I used to operate mysql. Below I will briefly introduce them.

?

Backup Data:

C:\Users\xxxx>mysqldump -uxxx -pxxx backup > backup.sqlC:\Users\xxxx>

? Mysqldump: mysql is used to store database applications and generates an SQL file, such as backup. SQL, which contains the create and insert into statements of the database.

?

Run the dir command to view the file list in the C: \ Users \ xxxx> directory.

2014/08/23  10:15             1,271 backup.sql

? Recover data:

mysql> use backup;Database changedmysql> source C:\Users\xxxx\backup.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.03 sec)

? Use the mysql command to enter the mysql command line, use backup to switch to the database, and then use the source command to restore data.

?

The above source command can recover a large amount of data from mysql at a considerable speed.

However, binary logs are often used in real projects. Next we will back up binary logs.

?

First, check whether mysql binary log is enabled.

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | OFF   |+---------------+-------+1 row in set (0.14 sec)

?

Step 2: Enable binary log (Add the following content to the my. ini file in the mysql installation directory of Windows to ensure that your f disk has the mysqlbin directory)

# add log-binlog-bin=f:/mysqlbin/binlog.log# set the database for testbinlog-do-db=backup

?

Step 3: restart mysql

C: \ Users \ xxxx> net stop mysqlMySQL service is stopping. MySQL service has been stopped successfully. C: \ Users \ xxxx> net start mysqlMySQL service is starting. MySQL service has been started successfully.

Step 4: Check whether log_bin is enabled again

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)

?

Step 5: perform operations on the backup database. If the database already exists

mysql> show columns from binlog;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+1 row in set (0.08 sec)mysql>

? Insert a data record.

mysql> insert into binlog values (2);Query OK, 1 row affected (0.01 sec)mysql> select * from binlog;+------+| id   |+------+|    1 ||    2 |+------+2 rows in set (0.00 sec)mysql>

?

Step 6: Check the binary log directory.

F: \ mysqlbin> the volume in the dir drive F is F: the serial number of the volume is 000C-A0EB F: \ mysqlbin directory
 
  
.
  
   
.. 126 binlog.0000012014/08/23 02 107 binlog.0000022014/08/23 02 52 binlog. index 3 files 285 bytes 2 directories 117,389,742,080 available bytes
  
 

Step 7: Use mysqlbinlog to check the content. The content is large and the content is up and down... is empty. You can see that "insert into binlog values (2)" is the content we just operated on.

F:\mysqlbin>mysqlbinlog binlog.000002...use backup/*!*/;SET TIMESTAMP=1408763281/*!*/;insert into binlog values (2)/*!*/;# at 271#140823 11:08:01 server id 1  end_log_pos 298   Xid = 9...

?

So let's go back to the title of binary log recovery and backup again. Backup is as mentioned above, so let's talk about binary restoration.

?

First: Using the source command, binary logs cannot be restored directly using the source command, so we need to follow the steps below

?

Step 1: first convert binary logs into common SQL files

F: \ mysqlbin> mysqlbinlog-uxxx-pxxx binlog.000001> binlog1.sqlF: \ mysqlbin> mysqlbinlog-uxxx-pxxx binlog.000002> binlog2.sqlF: \ mysqlbin> the volume in drive F is F: the serial number of the volume is 000C-A0EB F: \ mysqlbin directory
 
  
.
  
   
.. 2014/08/23 02 126 binlog.0000012014/08/23 08 298 binlog.0000022014/08/23 02 52 binlog. index2014/08/23 22 644 binlog1.sql2014/08/23 22 1,569 binlog2. SQL 5 files 2,689 bytes 2 directories 117,389,737,984 bytes available
  
 

?

Step 2: Create an SQL file and enter the following content

source F:\mysqlbin\binlog1.sqlsource F:\mysqlbin\binlog2.sql

?

Step 3: import the preceding SQL File

mysql> source C:\Users\xxx\backup.sqlQuery OK, 0 rows affected (0.00 sec)

?

Method 2: Use mysqlbinlog

F:\mysqlbin>mysqlbinlog binlog.000001 | mysql -uroot -prootF:\mysqlbin>mysqlbinlog binlog.000002 | mysql -uroot -proot

?

Last: These simple, commonly used commands and usage methods are worth summarizing.

?