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

This article mainly introduces the general way of mysqldump backup to explain in detail, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

1. Mysqldump backup combined with binlog log recovery

MySQL backup usually takes the way of full database backup and log backup, such as full backup once a day and binary log backup every hour. This allows you to use full and log backups to restore data anywhere or at any time before the last binary log backup after a MySQL failure.

Introduction to 1.binlog log recovery

First of all, the binary log function should be enabled.

Binary logs can be enabled by editing the log-bin option in my.cnf, as follows:

Log-bin [= DIR/ [filename]] (when only log_bin is written in the configuration file and does not write the following file name and path, it is stored in the / usr/local/mysql/data directory by default, and the file name is hostname-bin.000001 … Named)

Here is an example of how binlog logs recover data

First add, delete and modify the table of the test database, otherwise the data in log is a little empty.

Restart a new log file and delete a row of data from the test table to simulate the error operation.

View binary logs on MySQL Server

Looking at the events in the binary log, the events in the first binary log file that can be found are displayed by default, including the log file name, start location of the event, event type, end location, information, and so on.

Syntax format: SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Option resolution:

IN 'log_name' specifies the name of the binlog file to be queried (not specified is the first binlog file) FROM pos specifies which pos starting point to start (unspecified is calculated from the first pospoint of the whole file) offset offset (0 if not specified). For example, if 3 is specified, the total number of row_count queries will start from line 3 (all lines if not specified)

Description of the parameters in the display information:

Format_desc this event format describes the event Query for the query event BEGIN for the transaction start Table_map for the table mapping event Write_rows for our execution of the insert event XidXid time is the action of automatically committing the transaction Rotate for the log rotation event is caused by our execution of flush logs to open a new log file. COMMIT is the commit of the transaction

View events in the specified binary log

In order to see what kind of SQL statement was specifically executed at that time, you can use the mysqlbinlog tool's-v (--verbose) option, which will reconstruct the line event into a pseudo-SQL statement that is commented out. If you want to see more detailed information, you can give this option twice, such as-vv, so you can include some data types and meta-information of the comment content.

By executing the following command, we can see the information shown in the figure above:

Mysqlbinlog-v / usr/local/mysql/data/mysql-bin.000002

In addition, mysqlbinlog and can read binary log files from the remote CVM through the-- read-from-remote-server option, which requires some external connection parameters, such as-h _ read-from-remote-server, p _ Q, p _ Q, and so on. These parameters are valid only if-log is specified.

As can be seen from the figure above, the delete event occurrence position is 291 and the event end position is 420.

Recovery process: directly use the bin-log log to restore the database to delete location 291, then skip the point of failure, and then restore. For all of the following operations, the command is as follows:

Common options are as follows:

-- start-datetime reads log events after the specified timestamp or local computer time from the binary log. -- stop-datetime reads log events before the specified timestamp or local computer time from the binary log. -- start-position reads the specified position event location from the binary log as a start. -- stop-position reads the specified position event location from the binary log as the event expiration.

Delete the test database, use binlog to recover the data, and find that the data has been recovered after completion.

Note:

(1) in the actual production environment, if there is a need to restore the database, do not allow users to access the database to avoid the insertion of new data, and in the master-slave environment, close the master-slave.

(2) in the above methods, only binlog is used to recover the data, and mysqldump full database backup is not used to restore the database, so delete the database before restoring the data.

two。 Using script to realize mysql backup and recovery

(1) introduction to mysqldump

Mysqldump is a tool used by mysql for backup and data transfer. It mainly produces a series of SQL statements that can be encapsulated into a file that contains all the SQL commands needed to rebuild your database, such as CREATE DATABASE,CREATE TABLE,INSERT, and so on. Can be used to achieve lightweight rapid migration or recovery of the database.

Mysqldump exports datasheets into SQL script files, which is relatively appropriate when upgrading between different MySQL versions, and is the most commonly used backup method.

Mysqldump can generally be used for backups when the amount of data is small (a few gigabytes). When the amount of data is relatively large, it is not recommended to use the mysqldump tool for backup.

Export of database

Export object description:

Mysqldump can export for a single table, multiple tables, a single database, multiple databases, all databases

# mysqldump [options] db_name [tbl_name...] / / Export a specified database or a single table # mysqldump [options]-- databases db_name... / / Export multiple databases # mysqldump [options]-- all-databases / / Export all

Import of database

Mysql-uroot-p [options] [db_name] [tbl_name]

< /路径/备份文件 生产环境中Mysql数据库的备份是周期性重复的操作，所以通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本。 mysqldump备份方案： ·周日凌晨1点全库备份 ·周一到周六凌晨每隔4个小时增量备份一次 设置crontab任务，每天执行备份脚本 # crontab -e #每个星期日凌晨1:00执行完全备份脚本0 1 * * 0 /root/mysqlfullbackup.sh >

/ dev/null 2 > & incremental backup every 4 hours from Monday to Saturday 0 * / 4 * * 1-6 / root/mysqldailybackup.sh > / dev/null 2 > & 1

The content of the mysqlfullbackup.sh script:

#! / bin/sh#name:mysqlfullbackup.shmysqldir=/usr/local/mysqluser=rootuserpwd=123456dbname=test_db# definition backup directory databackupdir=/opt/mysqlbackup [!-d $databackupdir] & & mkdir $databackdir# definition mail body file emailfile=$databackupdir/email.txt# definition mail address email=root@localhost.localdomain# definition backup log file logfile=$databackupdir/mysqlbackup.logDATE= `data-I` echo "" > $emailfileecho $(date + "% y-%m-%d%H:%M:%S") > $emailfilecd $databackupdir# definition Dumpfile=mysql_$DATE.sqlgzdumpfile=mysql_$DATE.sql.tar.gz# uses mysqldump to back up the database $mysqldir/bin/mysqldump-u$user-p$userpwd-- flush-logs-x $dbname > $dumpfile# compressed backup file if [$?-eq 0] Thentar zcf $gzdumpfile $dumpfile > > $emailfile 2 > & 1echo "backupfilename:$gzdumpfile" > > $emailfileecho "database backup success!" > > $emailfilerm-f $dumpfileelseecho "database backup fail!" > $emailfilefi# write log file echo "--> $logfilecat $emailfile > > $logfile# send email notification cat $emailfile | mail-s" mysql backup "$email mysqldailybackup.sh script content: #! / Bin/sh# Name:mysqlDailyBackup.sh# defines the database directory and data directory mysqldir=/usr/local/mysqldatadir=$mysqldir/data# defines the user name and password used to back up the database user=rootuserpwd=123456# defines the backup directory Daily backup files are backed up to $dataBackupDir/dailydatabackupdir=/opt/mysqlbackupdailybackupdir=$databackupdir/daily [!-d $dailybackupdir] & & mkdir-p $dailybackupdir/daily# definition mail body file emailfile=$databackupdir/email.txt# definition mail address email=root@localhost.localdomain# definition log file logfile=$databackupdir/mysqlbackup.logecho "> $emailfileecho $(date +"% y-%m-%d% H:%M:%S ") > > $emailfile# refresh log Make the database use the new binary log file $mysqldir/bin/mysqladmin-u$user-p$userpwd flush-logscd $datadir# to get the binary log list filelist= `cat mysql- bin.index`icounter = 0for file in $filelistdoicounter= `expr $icounter+ 1`donenextnum = 0ifile=0for file in $filelistdobinlogname= `basename $file`nextnum = `expr $nextnum+ 1`if [$nextnum-eq $icounter] Thenecho "Skip laster!" > / dev/nullelsedest=$dailybackupdir/$binlognameif [- e $dest]; thenecho "Skip exist $binlogname" > / dev/nullelsecp $binlogname $dailybackupdirif [$?-eq 0]; thenifile= `expr $ifile+ 1`echo "$binlogname backup success!" > > $emailfilefififidoneif [$ifile-eq 0] Thenecho "no binlog backup!" > > $emailfileelseecho "backup $ifile files" > $emailfilefi# send email notification cat $emailfile | mail-s "MySQL Backup" $email# write log file echo "- -" > > $logfilecat $emailfile > > $logfile

Read the above about the general way of mysqldump backup detailed explanation, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.