Shulou(Shulou.com)06/01 Report--

When learning mysql backup, I deeply feel that the backup and restore function of mysql is not as powerful as oracle; for example, a very common recovery scenario: based on point-in-time recovery, oracle can quickly restore the database through the rman tool, but a large part of mysql relies on the tool mysqlbinlog to run binlog statements to achieve incomplete recovery. This document introduces the recovery of various scenarios through mysqlbinlog. 1. Test environment description: the premise of using mysqlbinlog tool requires an integrity backup of the database, so you need to make a complete backup of the database beforehand. This document is complete to the database through mysqlbackup (use of mysqlbackup: http://blog.itpub.net/12679300/viewspace-1329578/)

2. Test step description: database insertion preparation work 2.1 make a full backup of the database at time point A; 2.2 create a database BKT at time point B, create a table JOHN under BKT, and insert 5 pieces of data; 2.3 at time point C continue to insert data into table JOHN into 10 pieces of data

Database recovery 2.4 restore the database to point-in-time A, then check the status of the database tables; 2.5 restore the database to point-in-time B, check the corresponding system state; 2.6 restore the database to point-in-time C and check the status of the restore

Third, scenario simulation test steps (backup and recovery is a very important thing) 3.1 perform a full backup of the database

Click (here) to collapse or open

[root@mysql01 backup] # mysqlbackup-- user=root-- password-- backup-dir=/backup backup-and-apply-log / / run a full backup of the database 3.2 create databases, tables, and insert data

Click (here) to collapse or open

Mysql > SELECT CURRENT_TIMESTAMP

+-+

| | CURRENT_TIMESTAMP |

+-+

| | 2014-11-26 17:51:27 |

+-+

1 row in set (0.01 sec)

Mysql > show databases; / / Database BKT has not been created yet

+-+

| | Database |

+-+

| | information_schema |

| | john |

| | mysql |

| | performance_schema |

+-+

4 rows in set (0.03 sec)

Mysql > Ctrl-C--

Aborted

[root@mysql02 data] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\\ g.

Your MySQL connection id is 2

Server version: 5.5.36-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type\ 'help;\' or\'\ h\ 'for help. Type\'\ c\'to clear the current input statement.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 107 | / / pos status of the current database log |

+-+

1 row in set (0.00 sec)

Mysql > SELECT CURRENT_TIMESTAMP; / / current timestamp current time point A

+-+

| | CURRENT_TIMESTAMP |

+-+

| | 2014-11-26 17:54:12 |

+-+

1 row in set (0.00 sec)

Mysql > create database BKT; / / create database BKT

Query OK, 1 row affected (0.01sec)

Mysql > create table john (id varchar (32))

ERROR 1046 (3D000): No database selected

Mysql > use bkt

ERROR 1049 (42000): Unknown database\ 'bkt\'

Mysql > use BKT

Database changed

Mysql > create table john (id varchar (32))

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into john values (\'1\')

Query OK, 1 row affected (0.01sec)

Mysql > insert into john values (\'2\')

Query OK, 1 row affected (0.01sec)

Mysql > insert into john values (\'3\')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into john values (\'4\')

Query OK, 1 row affected (0.01sec)

Mysql > insert into john values (\'5\')

Query OK, 1 row affected (0.01sec)

Mysql > SELECT CURRENT_TIMESTAMP; / / time point B of the database after inserting 5 pieces of data. Recording this point is convenient for database recovery.

+-+

| | CURRENT_TIMESTAMP |

+-+

| | 2014-11-26 17:55:53 |

+-+

1 row in set (0.00 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 1204 | / / the pos location of the current binlog |

+-+

1 row in set (0.00 sec) 3.3 Test of setting point-in-time C

Click (here) to collapse or open

Mysql > insert into john values (\'6\')

Query OK, 1 row affected (0.02 sec)

Mysql > insert into john values (\'7\')

Query OK, 1 row affected (0.01sec)

Mysql > insert into john values (\'8\')

Query OK, 1 row affected (0.01sec)

Mysql > insert into john values (\'9\')

Query OK, 1 row affected (0.01sec)

Mysql > insert into john values (\'10\')

Query OK, 1 row affected (0.03 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 2125 | |

+-+

1 row in set (0.00 sec)

Mysql > SELECT CURRENT_TIMESTAMP

+-+

| | CURRENT_TIMESTAMP |

+-+

| | 2014-11-26 17:58:08 |

+-+

After the operation of 1 row in set (0.00 sec) and above is completed, the recovery test of the database can be performed.

Click (here) to collapse or open

[root@mysql02 data] # mysqlbackup-defaults-file=/backup/server-my.cnf-datadir=/data/mysql-backup-dir=/backup/ copy-back

MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014-08-26]

Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

Mysqlbackup: INFO: Starting with following command line...

Mysqlbackup-defaults-file=/backup/server-my.cnf-datadir=/data/mysql

-- backup-dir=/backup/ copy-back

Mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful\ 'copy-back\' run mysqlbackup

Prints\ "mysqlbackup completed OK!\".

141126 17:59:58 mysqlbackup: INFO: MEB logfile created at / backup/meta/MEB_2014-11-26.17-59-58_copy_back.log

Server Repository Options:

Datadir = / data/mysql

Innodb_data_home_dir = / data/mysql

Innodb_data_file_path = ibdata1:10M:autoextend

Innodb_log_group_home_dir = / data/mysql/

Innodb_log_files_in_group = 2

Innodb_log_file_size = 5242880

Innodb_page_size = Null

Innodb_checksum_algorithm = none

Backup Config Options:

Datadir = / backup/datadir

Innodb_data_home_dir = / backup/datadir

Innodb_data_file_path = ibdata1:10M:autoextend

Innodb_log_group_home_dir = / backup/datadir

Innodb_log_files_in_group = 2

Innodb_log_file_size = 5242880

Innodb_page_size = 16384

Innodb_checksum_algorithm = none

Mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads

1 read-threads 1 write-threads

Mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with-- log-bin.

Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.

141126 17:59:58 mysqlbackup: INFO: Copying / backup/datadir/ibdata1.

Mysqlbackup: Progress in MB: 200 400 600

141126 18:00:22 mysqlbackup: INFO: Copying the database directory\ 'john\'

141126 18:00:23 mysqlbackup: INFO: Copying the database directory\ 'mysql\'

141126 18:00:23 mysqlbackup: INFO: Copying the database directory\ 'performance_schema\'

141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files.

141126 18:00:23 mysqlbackup: INFO: Copying the logfile\ 'ib_logfile0\'

141126 18:00:23 mysqlbackup: INFO: Copying the logfile\ 'ib_logfile1\'

141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in / data/mysql

141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully.

141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to\'/ data/mysql\'

Mysqlbackup completed / / Database recovery completes authorization and opens database

Click (here) to collapse or open

[root@mysql02 data] # chmod-R 777 mysql / / requires authorization to open it

[root@mysql02 data] # cd mysql

[root@mysql02 mysql] # ll

The total dosage is 733220

-rwxrwxrwx. 1 root root 305 November 26 18:00 backup_variables.txt

-rwxrwxrwx. 1 root root 740294656 November 26 18:00 ibdata1

-rwxrwxrwx. 1 root root 5242880 November 26 18:00 ib_logfile0

-rwxrwxrwx. 1 root root 5242880 November 26 18:00 ib_logfile1

Drwxrwxrwx. 2 root root 4096 November 26 18:00 john

Drwxrwxrwx. 2 root root 4096 November 26 18:00 mysql

Drwxrwxrwx. 2 root root 4096 November 26 18:00 performance_schema

-rwxrwxrwx. 1 root root 8488 November 26 18:00 server-all.cnf

-rwxrwxrwx. 1 root root 1815 November 26 18:00 server-my.cnf / / No BKT database

[root@mysql02 mysql] # service mysqld start / / start database 3.5 to recover the database to point in time B

Click (here) to collapse or open

[root@mysql02 mysql2] # pwd / / when backing up, you need to back up the binlog log. The previous binlog directory is / data/mysql2

/ data/mysql2

[root@mysql02 mysql2] # mysqlbinlog-- start-position=107-- stop-position=1203 mysql-bin.000001 | mysql- uroot-p / / restore based on the location of post. The current pos location is 107, and restore to pos location to 1203

Enter password:

[root@mysql02 mysql2] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\\ g.

Your MySQL connection id is 3

Server version: 5.5.36-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type\ 'help;\' or\'\ h\ 'for help. Type\'\ c\'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | BKT |

| | john |

| | mysql |

| | performance_schema |

+-+

5 rows in set (0.02 sec)

Mysql > use BKT

Database changed

Mysql > show tables

+-+

| | Tables_in_BKT |

+-+

| | john |

+-+

1 row in set (0.00 sec)

Mysql > select * from john

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

+-+

5 rows in set (0.01 sec) / / View database recovery succeeded 3.6 restore database to point-in-time C

Click (here) to collapse or open

[root@mysql02 mysql2] # mysqlbinlog-- start-date=\ "2014-11-27 09:21:56\"-- stop-date=\ "2014-11-27 09:22:33\" mysql-bin.000001 | mysql- uroot-p123456 / / this time restore to time C through a point-in-time recovery

Warning: Using unique option prefix start-date instead of start-datetime is deprecated and will be removed in a future release. Please use the full name instead.

Warning: Using unique option prefix stop-date instead of stop-datetime is deprecated and will be removed in a future release. Please use the full name instead.

[root@mysql02 mysql2] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\\ g.

Your MySQL connection id is 6

Server version: 5.5.36-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type\ 'help;\' or\'\ h\ 'for help. Type\'\ c\'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | BKT |

| | john |

| | mysql |

| | performance_schema |

+-+

5 rows in set (0.00 sec)

Mysql > use BKT

Database changed

Mysql > select * from john

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

| | 6 |

| | 7 |

| | 8 |

| | 9 |

| | 10 |

+-+

10 rows in set (0.00 sec) / / successful recovery to point-in-time C 4 after checking. Other summary of mysqlbinlog: the above is a test of POS location recovery based on point-in-time and binlog using binlog files. There are many functions in the use of mysqlbinlog, and you can run mysqlbinlog-help to view the corresponding parameters. 4.1 to view the contents of binlog: [root@mysql02 mysql2] # mysqlbinlog mysql-bin.000001

4.2 other common parameters of mysqlbinlog:

-h according to IP of the database

-P according to the port occupied by the database

-server-id is restored according to the database serverid (useful in clusters)

-d based on the database name

For example: [root@mysql02 mysql2] # mysqlbinlog-d BKT mysql-bin.000001// restore BKT database information

The combined use of parameters:

Click (here) to collapse or open

[root@mysql02 mysql2] # mysqlbinlog-- start-date=\ "2014-11-27 09:21:56\"-- stop-date=\ "2014-11-27 09:22:33\"-d BKT-h 127.0.0.1 / var/lib/mysql/mysql-bin.000001 | mysql- u root-p

# if you have multiple binlog files, separate them with commas. 4.4 recovery is a very important thing. If you do not know the specific time point to restore, please convert the binlog file into a text file and review the corresponding contents in detail before restoring. [root@mysql02 mysql2] # mysqlbinlog mysql-bin.000001 > / tmp/00001.sql

Conclusion: sometimes you can never use a backup, but you never know when you will use it. As the saying goes, if you have a backup that is available as a qualified DBA, you can be sure of it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.