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

This article mainly gives you a brief description of the detailed methods of operating mysqldump full and incremental backups. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help. I hope this article can bring you some practical help.

In the daily operation and maintenance work, the backup of the mysql database is very important, in case the database table is lost or damaged, the data can be recovered in time.

Online database backup scenario:

Perform a full backup every Sunday, and then perform an MySQLdump incremental backup at 1 pm every day.

The following is a detailed description of this backup scheme:

1.MySQLdump incremental backup configuration

The prerequisite for performing an incremental backup is that MySQL opens the binlog log function and adds it to my.cnf.

Log-bin=/opt/Data/MySQL-bin

The string after "log-bin=" is the logging directory, which is generally recommended to be placed on a disk different from the MySQL data directory.

2.MySQLdump incremental backup

Suppose a full backup is performed at 1 p. M. on Sunday, which applies to the MyISAM storage engine.

[root@test-huanqiu] # MySQLdump-- lock-all-tables-- flush-logs-- master-data=2-u root-p test > backup_sunday_1_PM.sql

Replace-- lock-all-tables with-- single-transaction for InnoDB

-- flush-logs generates a new log file to end the current log

The master-data=2 option will record the name of the new log file after the full backup in the output SQL

It can be used as a reference for later recovery, for example, the output backup SQL file contains:

CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002', MASTER_LOG_POS=106

Additional instructions for 3.MySQLdump incremental backup:

If MySQLdump is added with-delete-master-logs, the previous log is cleared to free up space. However, if the CVM is configured as a mirrored replication master CVM, it is dangerous to delete the MySQL binary log with MySQLdump-delete-master-logs, because the slave CVM may not have fully processed the contents of the binary log. In this case, it is safer to use PURGE MASTER LOGS.

Use MySQLadmin flush-logs regularly every day to create new logs and end the previous log writing process. And back up the previous log, for example, start saving the log file MySQL-bin.000002 under the data directory in the above example.

1. Restore a full backup

Mysql-u root-p

< backup_sunday_1_PM.sql 2.恢复增量备份 mysqlbinlog MySQL-bin.000002 … | MySQL -u root -p注意此次恢复过程亦会写入日志文件，如果数据量很大，建议先关闭日志功能 --compatible=name 它告诉 MySQLdump，导出的数据将和哪种数据库或哪个旧版本的 MySQL云服务器相兼容。值可以为 ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。 --complete-insert，-c 导出的数据采用包含字段名的完整 INSERT 方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。 --default-character-set=charset 指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。 --disable-keys 告诉 MySQLdump 在 INSERT 语句的开头和结尾增加 /!40000 ALTER TABLE table DISABLE KEYS /; 和 /!40000 ALTER TABLE table ENABLE KEYS /; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。 --extended-insert = true|false 默认情况下，MySQLdump 开启 --complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。 --hex-blob 使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。 --lock-all-tables，-x 在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。 --lock-tables 它和 --lock-all-tables 类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 选项。 --no-create-info，-t 只导出数据，而不添加 CREATE TABLE 语句。 --no-data，-d 不导出任何数据，只导出数据库表结构。 mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 >

Test.dump

Only the table structure will be available. -- databases indicates the database to back up on the host.

-- opt

This is just a shortcut option, which is equivalent to adding the-- add-drop-tables-- add-locking-- create-option-- disable-keys-- extended-insert-- lock-tables-- quick-- set-charset option at the same time. This option allows MySQLdump to export data quickly, and the exported data can be imported back quickly. This option is on by default, but can be disabled with-- skip-opt. Note that if running MySQLdump does not specify the-- quick or-- opt option, the entire result set is placed in memory. Problems may occur if you export a large database.

-- quick,-q

This option is useful when exporting large tables, forcing MySQLdump to take records directly from the cloud query instead of caching them in memory after getting all the records.

-- routines,-R

Export stored procedures and custom functions.

-- single-transaction

This option submits a BEGIN SQL statement before exporting the data. BEGIN does not block any applications and ensures the consistent state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB. This option and the-- lock-tables option are mutually exclusive because LOCK TABLES will implicitly commit any pending transaction. If you want to export a large table, use the-- quick option together.

-- triggers

Export triggers at the same time. This option is enabled by default and disabled with-- skip-triggers.

Cross-host backup

Use the following command to copy the sourceDb on host1 to the targetDb of host2, provided that the targetDb database has been created on the host2 host:

-C instructs data transmission between hosts to use data compression

Mysqldump-- host=host1-- opt sourceDb | mysql-- host=host2-C targetDb

Realization of scheduled backup combined with cron command of Linux

For example, you need to back up all the databases on a host at 1:30 every morning and compress the dump file into gz format.

30 1 * mysqldump-u root-pPASSWORD-- all-databases | gzip > / mnt/disk2/database_date'+% m-%d-%Y'.sql.gz

An example of a complete Shell script backing up the MySQL database. Such as backing up the database opspc

[root@test-huanqiu ~] # vim / root/backup.sh

#! bin/bash

Echo "Begin backup mysql database"

Mysqldump-u root-ppassword opspc > / home/backup/mysqlbackup-date +% Y-%m-%d.sql

Echo "Your database backup successfully completed"

[root@test-huanqiu] # crontab-e

30 1 * / bin/bash-x / root/backup.sh > / dev/null 2 > & 1

Mysqldump full backup + mysqlbinlog binary log incremental backup

1) restoring data from mysqldump backup files loses updated data from the backup point, so incremental backups are also required in combination with mysqlbinlog binary logs.

First make sure that the binlog logging feature is turned on. Include the following configuration in my.cnf to enable binary logging:

[mysqld]

Log-bin=mysql-bin

2) the mysqldump command must take the-- flush-logs option to generate a new binary log file:

Mysqldump-single-transaction-flush-logs-master-data=2 > backup.sql

Parameter-- master-data= [0 | 1 | 2]

0: no record

1: record as CHANGE MASTER statement

2: CHANGE MASTER statements recorded as comments

For more information on the mysqldump full + incremental backup solution, please see the following two documents:

Http://www.cnblogs.com/kevingrace/p/5904800.html

Http://www.cnblogs.com/kevingrace/p/5907254.html

Let's share the full and incremental mysqldump backup scripts that you have used.

Application scenarios:

1) incremental backups copy mysql-bin.00000 to the specified directory from Monday to Saturday at 3: 00 a.m.

2) full backup uses mysqldump to export all databases, which is executed every Sunday at 3: 00 a.m., and deletes the mysq-bin.00000 left last week, and then the backup operation of mysql is kept in the bak.log file.

Script implementation:

1) full backup script (assuming the mysql login password is 123456; pay attention to the command path in the script):

[root@test-huanqiu ~] # vim / root/Mysql-FullyBak.sh

#! / bin/bash

# Program

# use mysqldump to Fully backup mysql data per week!

# History

# Path

BakDir=/home/mysql/backup

LogFile=/home/mysql/backup/bak.log

Date=date +% Y%m%d

Begin=date + "Y% m / d% H:%M:%S"

Cd $BakDir

DumpFile=$Date.sql

GZDumpFile=$Date.sql.tgz

/ usr/local/mysql/bin/mysqldump-uroot-p123456-- quick-- events-- all-databases-- flush-logs-- delete-master-logs-- single-transaction > $DumpFile

/ bin/tar-zvcf $GZDumpFile $DumpFile

/ bin/rm $DumpFile

Last=date + "Y% m / d% H:%M:%S"

Echo start: $Begin end: $Last $GZDumpFile succ > > $LogFile

Cd $BakDir/daily

/ bin/rm-f *

2) incremental backup script (the data storage path of mysql in the script is / home/mysql/data, which is adjusted according to your actual situation)

[root@test-huanqiu ~] # vim / root/Mysql-DailyBak.sh

#! / bin/bash

# Program

# use cp to backup mysql data everyday!

# History

# Path

Copy the target directory of mysql-bin.00000 during BakDir=/home/mysql/backup/daily / / incremental backup, and create this directory manually in advance

BinDir=/home/mysql/data

LogFile=/home/mysql/backup/bak.log

BinFile=/home/mysql/data/mysql-bin.index

/ usr/local/mysql/bin/mysqladmin-uroot-p123456 flush-logs

# this is used to generate new mysql-bin.00000 files

Counter=wc-l $BinFile | awk'{print $1}'

NextNum=0

# this for loop is used to compare the two values of $Counter,$NextNum to determine whether the file exists or is up to date

For file in cat $BinFile

Do

Base=basename $file

# basename is used to intercept the mysql-bin.00000* file name and remove the. / before the. / mysql-bin.000005

NextNum=expr $NextNum + 1

If [$NextNum-eq $Counter]

Then

Echo $base skip! > > $LogFile

Else

Dest=$BakDir/$base

If (test-e $dest)

# test-e is used to detect whether the target file exists. Write exist if it exists! Go to $LogFile

Then

Echo $base exist! > > $LogFile

Else

Cp $BinDir/$base $BakDir

Echo $base copying > > $LogFile

Fi

Fi

Done

Echo date + "Y year m month d H:%M:%S" $Next Bakup succ! > $LogFile

3) set the crontab task and execute the backup script. The incremental backup script is executed first, and then the full backup script is executed:

[root@test-huanqiu] # crontab-e

# execute a full backup script at 3:00 every Sunday morning

0 30 / bin/bash-x / root/Mysql-FullyBak.sh > / dev/null 2 > & 1

# do incremental backups from Monday to Saturday at 3:00

0 31-6 / bin/bash-x / root/Mysql-DailyBak.sh > / dev/null 2 > & 1

4) execute the above two scripts manually to test the backup effect

[root@test-huanqiu backup] # pwd

/ home/mysql/backup

[root@test-huanqiu backup] # mkdir daily

[root@test-huanqiu backup] # ll

Total 4

Drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily

[root@test-huanqiu backup] # ll daily/

Total 0

Execute the incremental backup script first

[root@test-huanqiu backup] # sh / root/Mysql-DailyBak.sh

[root@test-huanqiu backup] # ll

Total 8

-rw-r--r--. 1 root root 121 Nov 29 11:29 bak.log

Drwxr-xr-x. 2 root root 4096 Nov 29 11:29 daily

[root@test-huanqiu backup] # ll daily/

Total 8

-rw-r-. 1 root root 152 Nov 29 11:29 mysql-binlog.000030

-rw-r-. 1 root root 152 Nov 29 11:29 mysql-binlog.000031

[root@test-huanqiu backup] # cat bak.log

Mysql-binlog.000030 copying

Mysql-binlog.000031 copying

Mysql-binlog.000032 skip!

November 29th, 2016 11:29:32 Bakup succ!

Then execute the full backup script

[root@test-huanqiu backup] # sh / root/Mysql-FullyBak.sh

20161129.sql

[root@test-huanqiu backup] # ll

Total 152

-rw-r--r--. 1 root root 145742 Nov 29 11:30 20161129.sql.tgz

-rw-r--r--. 1 root root 211 Nov 29 11:30 bak.log

Drwxr-xr-x. 2 root root 4096 Nov 29 11:30 daily

[root@test-huanqiu backup] # ll daily/

Total 0

[root@test-huanqiu backup] # cat bak.log

Mysql-binlog.000030 copying

Mysql-binlog.000031 copying

Mysql-binlog.000032 skip!

The detailed method of operating mysqldump full-volume and incremental backup will first stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.