Examples of mysqldump and big-log backup and recovery

Examples of mysqldump and big-log backup and recovery

Mysql version

[root@zxl-nginx~]#mysql-VmysqlVer14.14Distrib5.6.28,forlinux-glibc2.5(x86_64)usingEditLinewrapper

1. mysqldump

Mysqldump is only applicable to scenarios with small datasets.

Usage: mysqldump [options] [db_name [tbl_name...] main options: -- all-databases,-A: Back up all databases -- databases,-B: the database to be backed up, you can back up multiple at the same time, separated by Spaces -- flush-logs, -F: Scroll logs before backup and after the request is locked. to record the binary logs during replication -- flush-privileges: notifies the database to re-read the authorization table -- host = host_name,-hhost_name: the Host Name of the database to be backed up. You can use the network backup -- lock-all-tables,-x: To request to lock all tables and then back up the database. For MyISAM, InnoDB, aria performs warm backup -- single-transaction: the username that can implement hot backup-uusename backup for the InnoDB Storage engine-ppassword login Database Password -- events: Backup event scheduler code -- routines: backup storage process and storage function -- triggers: Backup trigger -- master-date = {0 | 1 | 2}, 0 indicates no record, 1 indicates changemaster statement, 2 indicates the changemaster statement recorded as the Annotation

2. Create databases and tables and insert data

mysql>createdatabasezxl;QueryOK,1rowaffected(0.00sec)mysql>usezxlDatabasechangedmysql>CREATETABLE`users`(->`id`bigint(20)NOTNULLAUTO_INCREMENT,->`name`varchar(255)DEFAULTNULL,->PRIMARYKEY(`id`)->)ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=utf8;QueryOK,0rowsaffected(0.02sec)

# This is an example of creating a table and inserting data from the Internet...

# Users # -- Tablestructureforusers # users # DROPTABLEIFEXISTS 'users'; # CREATETABLE 'users' (# 'id' bigint (20) NOTNULLAUTO_INCREMENT, # 'name' varchar (255) DEFAULTNULL, # PRIMARYKEY ('id') #) ENGINE = InnoDBAUTO_INCREMENT = 7 DEFAULTCHARSET = utf8; # Keys # -- Recordsofusers # Keys # INSERTINTO 'users' VALUES ('1 ', 'xiaoming '); # INSERTINTO 'users' VALUES ('2', 'xiaohu'); # INSERTINTO 'users' VALUES ('3', 'xiaohua '); # INSERTINTO 'users' VALUES ('4', 'xiaohua '); # INSERTINTO 'users' VALUES ('5', 'xiaohua '); # INSERTINTO 'users' VALUES ('6', 'tiger ');

Insert data

Mysql> INSERTINTO 'users' VALUES ('1', 'xiaoming '); QueryOK, 1 rowaffected (0.00sec) mysql> INSERTINTO 'users' VALUES ('2 ', 'tiger '); QueryOK, 1 rowaffected (0.00sec) mysql> INSERTINTO 'users' VALUES ('3', 'xiaohua'); QueryOK, 1 rowaffected (0.00sec) mysql> INSERTINTO 'users' VALUES ('4', 'xiaohua '); QueryOK, 1 rowaffected (0.00sec) mysql> INSERTINTO 'users' VALUES ('5 ', 'flout'); QueryOK, 1 rowaffected (0.01sec) mysql> INSERTINTO 'users' VALUES ('6', 'tiger '); QueryOK, 1 rowaffected (0.00sec)

View inserted data

Mysql> select * fromusers; + ---- + -------- + | id | name | + ---- + -------- + | 1 | Xiao Ming | 2 | Xiao Hu | 3 | Xiao Hua | 4 | Xiao Hua | 5 | Xiao Hua | | 6 | Xiaohu | + ---- + -------- + 6 rowsinset (0.00sec)

Iii. Back up the database and demonstrate how to restore it

[root@zxl-nginx~]#mysqldump-uroot-p123456--databaseszxl--single-transaction--flush-logs--master-data=2>/tmp/zxl_users.sql

Warning: Using a password on the command line interface can be insecure.

Note: The prompt "warning" is displayed. Because the security mechanism is added in version 5.6, the password cannot be displayed in the command line. For details, see my. adding a user and a password to the cnf file under [mysqldump] does not prompt warning.

After the database is backed up, insert new data again.

mysql>INSERTINTO`users`VALUES('7','bob');QueryOK,1rowaffected(0.01sec)mysql>INSERTINTO`users`VALUES('8','tom');QueryOK,1rowaffected(0.00sec)mysql>INSERTINTO`users`VALUES('9','lili');QueryOK,1rowaffected(0.00sec)

View newly inserted data

Mysql> select * fromusers; + ---- + -------- + | id | name | + ---- + -------- + | 1 | Xiao Ming | 2 | Xiao Hu | 3 | Xiao Hua | 4 | Xiao Hua | 5 | Xiao Hua | | 6 | Xiaohu | 7 | bob | 8 | tom | 9 | lili | + ---- + -------- + 9 rowsinset (0.00sec)

Delete database zxl

mysql>dropdatabasezxl;QueryOK,1rowaffected(0.01sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||test|+--------------------+4rowsinset(0.00sec)

View the position of the pos node in the SQL file backed up by mysqldump and the corresponding binary file name

The binary files and pos nodes are as follows:

-- Change master to MASTER_LOG_FILE = 'master-bin.000060', MASTER_LOG_POS = 120;

Use mysqlbiglog to view the binary file, and delete the database zxl at 778.

[root@zxl-nginxdata]#mysqlbinlogmaster-bin.000060#at778#16012014:25:42serverid1end_log_pos867CRC320x014503a4Querythread_id=44exec_time=0error_code=0SETTIMESTAMP=1453271142/*!*/;dropdatabasezxl

Backup binary log location

[root@zxl-nginx~]#mysqlbinlog--start-position=120--stop-position=778/usr/local/mysql/data/master-bin.000060>/tmp/big_log.sql

Restore database

[root@zxl-nginx~]#mysql-uroot-p

View recovered Database

Disable binary logs

mysql>setsessionsql_log_bin=0;QueryOK,0rowsaffected(0.00sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||test||zxl|+--------------------+5rowsinset(0.00sec)

View the restored mysql table> usezxl; Tables-ADatabasechangedmysql> showtables; + ------------- + | tables | + tables + | users | + --------------- + 1 rowinset (0.00sec) mysql> select * fromusers; + ---- + -------- + | id | name | + ---- + -------- + | 1 | Xiao Ming | 2 | Xiao Hu | 3 | Xiao Hua | 4 | Xiao Hua | 5 | Xiao Hua | | 6 | Xiaohu | + ---- + -------- + 6 rowsinset (0.00sec) note: The added data is not recovered.

Restore the big-log file of the binary backup

[root@zxl-nginx~]#mysql-uroot-p

View the users table again

Mysql> select * fromusers; + ---- + -------- + | id | name | + ---- + -------- + | 1 | Xiao Ming | 2 | Xiao Hu | 3 | Xiao Hua | 4 | Xiao Hua | 5 | Xiao Hua | | 6 | Xiaohu | 7 | bob | 8 | tom | 9 | lili | + ---- + -------- + 9 rowsinset (0.00sec)

Enable big-log

mysql>setsessionsql_log_bin=1;QueryOK,0rowsaffected(0.00sec)

Note: If you disable binary, you can only disable binary without any operations. You can see the reason.

Note: It is best to edit the my. cnf configuration file during actual recovery and add the following items:

Skip-networking // skip the network function to restore Data