Mysqldump and Big-log Backup and recovery examples

MySQL version

[Email protected] ~]# mysql-vmysql Ver 14.14 distrib 5.6.28, for linux-glibc2.5 (x86_64) using Editline Wrapper

First, mysqldump

Mysqldump only for smaller data set scenarios

Usage: mysqldump [options] [db_name [tbl_name&nbsp [...]]        main Options explained:       --all-databases, - A: Back Up all databases       --databases, -b: The database to be backed up, you can back up multiple at the same time, use space delimited        --flush-logs, -f: Before the backup, the request to the lock after the rolling log, to record the copy of the binary log       -- Flush-privileges: Notify database reread Authorization form       --host=host_name, -h host_ Name: The host name of the database to be backed up, which can be based on network backup       --LOCK-ALL-TABLES, -X: Requests lock all tables before backing up, to MyISAM, Innodb,aria Win Bei       --single-transaction: Capable of hot provisioning of InnoDB storage engine        -u usename  user name for backup       -p password  Login Database Password       --events: Backup Event Scheduler Code       -- Routines: Backup stored procedure and storage function       --triggers: Backup trigger        --master-date={0|1|2},0 means no logging, 1 means the distance is a change master  statement, and 2 represents a Change master statement that is recorded as a comment 

Ii. creating databases and tables and inserting data

mysql> CREATE DATABASE ZXL; Query OK, 1 row Affected (0.00 sec) mysql> use zxldatabase changedmysql> CREATE TABLE ' users ' (with ' id ' bigin T (+) NOT null auto_increment, ' name ' varchar (255) DEFAULT NULL, PRIMARY KEY (' id ') engin E=innodb auto_increment=7 DEFAULT Charset=utf8; Query OK, 0 rows affected (0.02 sec)

#这是创建表以及插入数据的示例, from the internet ...

#------------------------------#--table structure for users#------------------------------#DROP table IF EXISTS ' Users '; #CREATE TABLE ' users ' (# ' ID ' bigint () not NULL auto_increment,# ' name ' varchar (255) DEFAULT null,# PRIMARY KE Y (' id ') #) Engine=innodb auto_increment=7 DEFAULT charset=utf8;# #------------------------------#--Records of users#-- ----------------------------#INSERT into ' users ' values (' 1 ', ' xiaoming '); #INSERT into ' users ' values (' 2 ', ' Little Tigers '); #INSERT into ' Users ' VALUES (' 3 ', ' Floret '); #INSERT into ' users ' values (' 4 ', ' Floret '); #INSERT into ' users ' values (' 5 ', ' Floret '); #INSERT into ' Us ERs ' VALUES (' 6 ', ' Little Tigers ');

Inserting data

Mysql> INSERT into ' users ' VALUES (' 1 ', ' xiaoming '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into ' users ' VALUES (' 2 ', ' Little Tigers '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into ' users ' VALUES (' 3 ', ' Floret '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into ' users ' VALUES (' 4 ', ' Floret '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into ' users ' VALUES (' 5 ', ' Floret '); Query OK, 1 row affected (0.01 sec) mysql> INSERT into ' users ' VALUES (' 6 ', ' Little Tigers '); Query OK, 1 row Affected (0.00 sec)

To view the inserted data

Mysql> SELECT * from users;+----+--------+| ID |  Name |+----+--------+| 1 |  Xiao Ming | | 2 |  Xiao Hu | | 3 |  Floret | | 4 |  Floret | | 5 |  Floret | | 6 | Little Tiger |+----+--------+6 rows in Set (0.00 sec)

Iii. backing up the database and demonstrating how to recover

[Email protected] ~]# mysqldump-uroot-p123456--databases zxl--single-transaction--flush-logs--master-data=2 >/t Mp/zxl_users.sql

Warning:using a password on the command line interface can is insecure.

Note: Prompt warning, because 5.6 version increases security mechanism, does not allow to appear in the command line password, specifically did not study, can add in the my.cnf file [mysqldump] under the user and the password will not prompt warning.

After backing up the database, insert the new data again.

Mysql> INSERT into ' users ' VALUES (' 7 ', ' Bob '); Query OK, 1 row affected (0.01 sec) mysql> INSERT into ' users ' VALUES (' 8 ', ' Tom '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into ' users ' VALUES (' 9 ', ' Lili '); Query OK, 1 row Affected (0.00 sec)

To view the newly inserted data

Mysql> SELECT * from users;+----+--------+| ID |  Name |+----+--------+| 1 |  Xiao Ming | | 2 |  Xiao Hu | | 3 |  Floret | | 4 |  Floret | | 5 |  Floret | | 6 |  Xiao Hu | | 7 |  Bob | | 8 |  Tom | | 9 | Lili |+----+--------+9 rows in Set (0.00 sec)

Delete Database Zxl

mysql> drop Database ZXL; Query OK, 1 row affected (0.01 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+4 rows in Set (0.00 sec)

View POS node locations in SQL files backed up by mysqldump and corresponding binary filenames

The binaries and POS nodes are as follows:

--Change MASTER to master_log_file= ' master-bin.000060 ', master_log_pos=120;

Using Mysqlbiglog to view binaries, delete database Zxl at location is 778

[[email protected] data]# mysqlbinlog master-bin.000060# at 778#160120 14:25:42 Server ID 1 end_log_pos 867 CRC32 0x01450 3A4 querythread_id=44exec_time=0error_code=0set timestamp=1453271142/*!*/;d ROP Database ZXL

Back up the binary log location

[Email protected] ~]# Mysqlbinlog--start-position=120--stop-position=778/usr/local/mysql/data/master-bin.000060 >/tmp/big_log.sql

Recovering a Database

[Email protected] ~]# mysql-uroot-p </tmp/zxl_users.sql Enter Password:

View the recovered database

Turn off binary logging

mysql> set session sql_log_bin=0; query ok, 0 rows affected  (0.00 sec) mysql> show databases;+-------- ------------+| database           |+-------------- ------+| information_schema | |  mysql              | |  performance_schema | |  test               | |  zxl                |+--- -----------------+5 rows in set  (0.00 sec) 

View the recovered table mysql> use zxl; reading table information for completion of table and column  Namesyou can turn off this feature to get a quicker startup  with -adatabase changedmysql> show tables;+---------------+| TABLES_IN_ZXL  |+---------------+| users         |+---------------+1  row in set  (0.00 sec) mysql> select * from users;+----+------- -+| id | name   |+----+--------+|  1 |  Xiao Ming    | |   2 |  Little Tiger    | |   3 |  Floret    | |   4 |  Floret    | |   5 |  Floret    | |   6 |  Tiger    |+----+--------+6 rows in set  (0.00 sec Note: After the added data is notRestore it. 

Restore a binary backup of the Big-log file

[Email protected] ~]# mysql-uroot-p </tmp/big_log.sql Enter Password:

View the Users table again

Mysql> SELECT * from users;+----+--------+| ID |  Name |+----+--------+| 1 |  Xiao Ming | | 2 |  Xiao Hu | | 3 |  Floret | | 4 |  Floret | | 5 |  Floret | | 6 |  Xiao Hu | | 7 |  Bob | | 8 |  Tom | | 9 | Lili |+----+--------+9 rows in Set (0.00 sec)

Open Big-log

Mysql> set session sql_log_bin=1; Query OK, 0 rows Affected (0.00 sec)

Note: Close does not turn off the binary only if you do not do any action can not shut down, the reason you understand.

Note: It is best to edit the MY.CNF profile when actually recovering, adding the following:

skip-networking//Skip network function to recover data

This article from "Village Boy" blog, declined reprint!

Mysqldump and Big-log Backup and recovery examples