Full backup and incremental backup strategy of MySQL database backup recovery

MySQL Tutorial database tutorial full backup and incremental backup strategy for backup recovery

Backup strategy One: Copy database files directly (not recommended)
Backup strategy Two: Use Mysqlhotcopy backup Database (full backup, suitable for small database backup)
Backup strategy Three: Use mysqldump Backup Database (full + incremental backup, suitable for medium database backup)
Backup strategy Four: Use master-slave replication mechanism (replication) (real-time database backup)

Backup strategy First, direct copy of database files
Direct copy of data files is the most direct, fast, and convenient, but the disadvantage is that the incremental backup is basically not possible. To ensure data consistency, you need to execute the following SQL statement before backing up the file:
Flush tables with read lock;
That is, the data in memory is flushed to disk, while the data table is locked to ensure that no new data is written in the copy process. This method back up the data recovery is also very simple, directly copied back to the original database directory.

Backup strategy Two, use Mysqlhotcopy to back up the database
Mysqlhotcopy is a Perl program that was originally written by Tim Bunce. It uses lock tables, flush tables, and CP or SCP to quickly back up databases. It is the quickest way to back up a database or a single table, but it can only run on the same machine as the database file (including the datasheet definition file, data file, index file), and mysqlhotcopy can only be used to back up the MyISAM table.

This backup strategy is suitable for small database backup, the amount of data is not large, you can use the Mysqlhotcopy program to do a full backup every day.

Backup Strategy Placement:

(1), install Dbd-mysql Perl module, support Mysqlhotcopy script to connect to MySQL database.

Shell> TAR-XZVF dbd-mysql-4.005.tar.gz

Shell> CD dbd-mysql-4.005

shell> unset Lang

Shell> Perl makefile.pl-mysql_config=/usr/local/mysql/bin/mysql_config-testuser=root-testpassword=userpwd

Shell> make

Shell> make Test

shell> make Install

(2), set the Crontab task, execute the backup script every day

Shell> CRONTAB-E

0 3 * * */root/mysqlbackup/mysqlbackup.sh >/dev/null 2>&1

A backup script is executed 3:00 every day.

MYSQLBACKUP.SH Note:

#!/bin/sh

# name:mysqlbackup.sh

# PS Tutorial: MySQL database backup,use mysqlhotcopy script.

# Write By:i.stone

# Last Modify:2007-11-15

#

# define variables, please modify them as appropriate

# define the directory where the script is located

Scriptsdir= ' pwd '

# Data Directory for database

datadir=/usr/local/mysql/data/

# Data Backup Directory

tmpbackupdir=/tmp/tmpbackup/

backupdir=/tmp/mysqlbackup/

# User name and password used to back up the database

Mysqluser=root

mysqlpwd=111111

# define Email Address

Email=alter@somode.com

# If the temporary backup directory exists, empty it, and create it if it does not exist

if [[E-$tmpbackupdir]]; Then

RM-RF $tmpbackupdir/*

Else

mkdir $tmpbackupdir

Fi

# Create a backup directory if it does not exist

if [[!-e $backupdir]];then

mkdir $backupdir

Fi

# empty Mysqlbackup.log

if [[s mysqlbackup.log]]; Then

Cat/dev/null >mysqlbackup.log

Fi

# Get a list of database backups, where you can filter the databases you don't want to back up

For databases in ' find $datadir-type D |

Sed-e "s//usr/local/mysql/data///" |

Sed-e "s/test//"; Todo

if [[$databases = = ' "]]; Then

Continue

Else

# BACKUP Database

/usr/local/mysql/bin/mysqlhotcopy--user= $mysqluser--password= $mysqlpwd-Q "$databases" $tmpbackupdir

Datetime= ' Date ' +%y.%m.%d%h:%m:%s '

echo "$datetime databasedatabases backup success!" >>mysqlbackup.log

Fi

Done

# Compress backup Files

Date= ' Date-i '

CD $tmpbackupdir

Tar czf $backupdir/mysql-$date. tar.gz./

# Send mail notification

if [[s mysqlbackup.log]]; Then

Cat Mysqlbackup.log | Mail-s "MySQL backup" $email

Fi

# use smbclientmv.sh script to upload database backup to backup server

# $scriptsdir/smbclientmv.sh

SMBCLIENTMV.SH Note:

#!/bin/sh

# name:smbclientmv.sh

# Ps:move The data to backup server.

# Write By:i.stone

# Last Modify:2007-11-15

#

# define Variables

# Backup Server name

Backupserver= "Backupservername"

# shared folder name

Backupshare= "sharename"

# Access user name and password for the backup server

Backupuser= "Smbuser"

backuppw= "Smbpassword"

# define Backup Directory

Backupdir=/tmp/mysqlbackup

Date= ' Date-i '

# move the ' data to Backupserver

Smbclient//$backupserver/$backupshare

$BACKUPPW-d0-w workgroup-u $backupuser

-C "Put $backupdir/mysql-$date. tar.gz

mysql-$date. tar.gz "

# Delete temp Files

Rm-f $backupdir/mysql-$date. tar.gz

(3), restore the state of the database to the time of backup

Mysqlhotcopy Backup is the entire database directory, use can be directly copied to the mysqld specified DataDir (here is/usr/local/mysql/data/) directory, but also attention to the issue of permissions, the following example:
Shell> CP-RF db_name/usr/local/mysql/data/

Shell> chown-r mysql:mysql/usr/local/mysql/data/(convert db_name directory owner to Mysqld run user)

This set of backup policies can only restore the state of the database to the last backup, so that the data lost in the crash should be backed up as infrequently as possible, and the master-slave replication mechanism (replication) is used to restore the data to the state of the crash.

Backup strategy Third, use mysqldump to back up the database

Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files and is relatively appropriate for upgrades between different versions of MySQL, which is also the most common method of backup. Mysqldump are slower than direct copies. For a more detailed explanation of Mysqldump, see the final appendix.

For systems with a medium-level volume of business, the backup strategy can be as follows: The first full backup, an incremental backup every day, a full backup once a week, and so on. For important and busy systems, it may take a full backup every day, incremental backups per hour, or even more frequently. In order not to affect online business, the realization of on-line backup, and can be incremental backup, the best way is to use the master-slave replication mechanism (replication), the slave machine to do backup.

Backup Strategy Placement:

(1), create a backup directory

Shell> Mkdir/tmp/mysqlbackup

Shell> mkdir/tmp/mysqlbackup/daily

(2), enable binary log

The binlog approach is relatively flexible, hassle-saving, and can also support incremental backups.
You must restart Mysqld when Binlog is enabled. First, close mysqld, open/etc/my.cnf, and add the following lines:
[Mysqld]
Log-bin

Then start mysqld on it. The running process will produce hostname-bin.000001 and Hostname-bin.index, the previous file is MYSQLD record all update operations on the data, the following file is all Binlog index, can not be easily deleted. For more detailed information about Binlog, please check the manual.

(3) Configure SSH key login to transfer the MySQL backup to the backup server (if the backup server is Windows, skip this section).

1, in the MySQL server (192.168.0.20) generated SSH key

[Root@lab ~]# ssh-keygen-t RSA
Generating public/private RSA key pair.
Enter file in which to save the key (/ROOT/.SSH/ID_RSA):
Direct return
Enter passphrase (empty for no passphrase):
Direct return without a password
Enter same passphrase again:
Direct return without a password
Your identification has been saved In/root/.ssh/id_rsa.
Your public key has been saved in/root/.ssh/id_rsa.pub.
The key fingerprint is:
C2:96:9F:2D:5A:8E:08:42:43:35:2F:85:5E:72:F8:1C Root@lab

2, create a directory on the backup server (192.168.0.200), modify the permissions, and transmit the public key.
[Root@lab ~]# ssh 192.168.0.200 "mkdir. Ssh;chmod 0700. SSH"
The authenticity of host ' 192.168.0.200 (192.168.0.200) ' can ' t be established.
RSA key fingerprint is 37:57:55:c1:32:f1:dd:bb:1b:8a:13:6f:89:fb:b8:9d.
Are you sure your want to continue connecting (yes/no)? Yes
warning:permanently added ' 192.168.0.200 ' (RSA) to the list of known hosts.
root@192.168.0.200 ' s Password:
Enter the root password for the backup server
[Root@lab ~]# SCP. Ssh/id_rsa.pub 192.168.0.200:.SSH/AUTHORIZED_KEYS2
root@192.168.0.200 ' s Password:
Id_rsa.pub 100% 218 0.2kb/s 00:00
3), test SSH Login
[Root@lab ~]# SSH 192.168.0.200//test SSH Login
Last Login:fri Nov 10:34:02 2007 from 192.168.0.20
[Root@lib ~]#

(4), set the Crontab task, execute the backup script every day

Shell> CRONTAB-E

#每个星期日凌晨3:00 Perform a full backup script

0 3 * * 0/root/mysqlbackup/mysqlfullbackup.sh >/dev/null 2>&1

#周一到周六凌晨3:00 make incremental backups

0 3 * * 1-6/root/mysqlbackup/mysqldailybackup.sh >/dev/null 2>&1

MYSQLFULLBACKUP.SH Note:

#!/bin/sh

# name:mysqlfullbackup.sh

# Ps:mysql Database Full backup.

# Write By:i.stone

# Last MODIFY:2007-11-17

#

# Use mysqldump--help get more detail.

#

# define variables, please modify them as appropriate

# define Script Directory

Scriptsdir= ' pwd '

# define Database Directory

Mysqldir=/usr/local/mysql

# defines the user name and password used to back up the database

User=root

userpwd=111111

# define Backup Directory

Databackupdir=/tmp/mysqlbackup

# define message body files

emailfile= $databackupdir/email.txt

# define email Addresses

Email=alter@somode.com

# define BACKUP log files

logfile= $databackupdir/mysqlbackup.log

Date= ' Date-i '

echo "" > $emailfile

echo $ (date + "%y-%m-%d%h:%m:%s" >> $emailfile

CD $databackupdir

# define backup file name

Dumpfile=mysql_$date.sql

Gzdumpfile=mysql_$date.sql.tar.gz

# Use Mysqldump to back up the database, set the parameters according to the situation

$mysqldir/bin/mysqldump-u$user-p$userpwd

--opt--default-character-set=utf8--extended-insert=false

--triggers-r--hex-blob--all-databases

--flush-logs--delete-master-logs

--delete-master-logs

-X > $dumpfile

# Compress backup Files

if [[$ = = 0]]; Then

Tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "Backupfilenamegzdumpfile" >> $emailfile

echo "Database backup success!" >> $emailfile

Rm-f $dumpfile

# Delete daily backup files.

CD $databackupdir/daily

Rm-f *

# Delete old backup files (mtime>2).

$scriptsdir/rmbackup.sh

# If you do not need to transfer the backup to the backup server or the backup server is Windows, comment out the marked Green Line

# move backup files to backup server.

#适合linux (MySQL server) to Linux (Backup server)

$scriptsdir/rsyncbackup.sh

if ((!$?)); Then

echo "Move backup files to backup server success!" >> $emailfile

Else

echo "Move backup files to backup server fail!" >> $emailfile

Fi

Else

echo "Database backup fail!" >> $emailfile

Fi

# Write log files

echo "--------------------------------------------------------" >> $logfile

Cat $emailfile >> $logfile

# Send mail notification

Cat $emailfile | Mail-s "MySQL backup" $email

(5), restore the state of the database to the time of backup

The file backed up with mysqldump is an SQL script that can be poured directly into the MySQL client.

/usr/local/mysql/bin/mysql-uroot-puserpwd Db_name < Db_name.sql
For any applicable update logs, use them as input to MySQL:
% ls-t-r-1 hostname-bin* | Xargs Mysqlbinlog | Mysql-uuser-puserpwd

The LS command generates a single-column list of update log files sorted according to the order in which they were generated by the server (note: If you modify any of the files, you will change the sort order, which will cause the update log to be used in the wrong order). ）

This set of backup policies can only restore the state of the database to the last backup, so that the data lost in the crash should be backed up as infrequently as possible, and the master-slave replication mechanism (replication) is used to restore the data to the state of the crash. If you use this set of backup scripts, it is a good idea to put the log files and data files on different disks, not only to improve the data write speed, but also to make the data more secure
MySQL supports one-way, asynchronous replication, in which one server acts as the primary server and one or more other servers act as the server. The master server writes the update to the binary log file and maintains an index of the log file to track the log loop. When a server connects to the primary server, it notifies the primary server of the location of the last successful update that was read from the server in the log. Receive any updates from the server that have occurred since then, and then block and wait for the master server to notify the next update.

Why use master-slave replication?

1, the primary server/from the server settings increased robustness. When there is a problem with the primary server, you can switch to backup from the server.

2, through the primary server and from the Server segmentation processing customer inquiries load, can get better customer response time. However, do not update on the master-slave server at the same time, which can cause conflicts.

3. Another benefit of using replication is that you can use a backup from the server without interfering with the primary server. The primary server can continue processing updates during the backup process.

MySQL uses 3 threads to perform replication functions (of which 1 are on the primary server and two on the server). When the start slave is issued, an I/O thread is created from the server to connect to the primary server and have the primary server send the binary log. The primary server creates a thread that sends the contents of the binary log to the server. Reads the contents of the primary server Binlog dump thread from the server I/O thread and copies the data to a local file from the server data directory, that is, the relay log. The 3rd thread is a SQL thread that uses this thread from the server to read the relay log and perform the updates that are contained in the log. The show Processlist statement can query for information about replication that occurs on the primary server and from the server.

The default relay log uses the host_name-relay-bin.nnnnnn file name, where host_name is from the server host name and nnnnnn is the serial number. Use sequential serial numbers to create continuous relay log files, starting at 000001. Track the relay log index files from the server to identify the relay logs that are currently in use. The default relay log index file name is Host_name-relay-bin.index. By default, these files are created in the data directory from the server. The relay log is the same format as the binary log and can be read with Mysqlbinlog. When the SQL thread finishes all the events in the relay log, the relay log is automatically deleted.

Create two additional state files from the server in the data directory--master.info and Relay-log.info. The status file is saved on the hard disk and will not be lost when it is shut down from the server. The next time you start from the server, read the files to determine how many binary logs it has read from the primary server, and how much to handle your own relay logs.

Set up master-slave replication:

1. Make sure that the MySQL version installed on the primary server and from the server is the same, and preferably the latest stable version of MySQL.

2. Set up a connection account for replication on the primary server. The account must be granted replication slave permissions. If the account is for replication only (recommended), no additional permissions are required.

mysql> grant replication Slave on *.*

-> to ' replication ' @ '%.yourdomain.com ' identified by ' slavepass ';

3. Execute flush tables with read lock statement to empty all tables and block write statements:

Mysql> flush tables with read lock;

Keep the MySQL client program and do not exit. Open another terminal to take a snapshot of the primary server data directory.

Shell> cd/usr/local/mysql/

Shell> Tar-cvf/tmp/mysql-snapshot.tar./data

If the user account from the server is different from the primary server, you may not want to copy the MySQL database. In this case, the database should be excluded from the archive. You also do not need to include any log files or Master.info or relay-log.info files in the archive.

Read the current binary log name and offset value on the primary server when flush tables with read lock are in effect (that is, the MySQL client does not exit):

MySQL > Show master status;

+---------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73 | Test | Manual,mysql |

+---------------+----------+--------------+------------------+

The file column displays the log name, and position displays the offset. In this example, the binary log value is mysql-bin.003 and the offset is 73. Record the value. You will need to use these values later when you set up from the server. They represent replication coordinates from which the server should start a new update from the primary server.

If the primary server is not enabled--logs-bin,show master status displays a log name and a location value that is empty. In this case, the value to be used when specifying the log files and locations from the server at a later time is an empty string ("") and 4.

After taking the snapshot and logging the log name and offset, return to the previous midrange to re-enable the write activity:

mysql> unlock tables;

4. Ensure that the [Mysqld] section of the my.cnf file on the master server host includes a log-bin option. The section should also have a server-id=master_id option where master_id must be a positive integer value between 1 and 232–1. For example:

[Mysqld]

Log-bin

Server-id=1

If you do not provide those options, you should add them and restart the server.

5. Stop the MYSQLD service from the server and add the following line to its my.cnf file:

[Mysqld]

server-id=2

The slave_id value, like the master_id value, must be a positive integer value between 1 and 232–1. Also, the ID of the server must be different from the ID of the primary server.

6, the data in the directory. Make sure that the permissions are correct for these files and directories. Users of the server MySQL operation must be able to read and write files as if they were on the primary server.

Shell> Chown-r Mysql:mysql/usr/local/mysql/data

7, start from the server. To execute the following statement from the server, replace the option value with the actual value of your system:

Mysql> Change Master to

-> master_host= ' Master_host_name ',

-> master_user= ' Replication_user_name ',

-> master_password= ' Replication_password ',

-> master_log_file= ' Recorded_log_file_name ',

-> master_log_pos=recorded_log_position;

8. Start from server thread:

mysql> start slave;

After these programs are executed, you should connect to the primary server from the server and supplement any updates that have occurred since the snapshot.

9. If a replication error occurs, an error message will also appear from the server's error log (HOSTNAME.ERR).

10. When replicating from the server, files Master.info and Hostname-relay-log.info are found in their data directories. Use these two files from the server to track how many master servers have been processed by binary logs. Do not remove or edit these files unless you know exactly what you are doing and fully understand its meaning. Even so, it is best to use the change master to statement