Full backup and Incremental Backup policies for mysql Database Backup Recovery

Mysql tutorial database tutorial Backup Recovery full backup and Incremental backup policy

Backup policy 1: directly copy database files (not recommended)
Backup policy 2: Use mysqlhotcopy to back up the database (full backup, suitable for backup of small databases)
Backup Policy 3: Use mysqldump to back up the database (full + Incremental backup, suitable for medium-sized database backup)
Backup policy 4: Use the master-slave replication mechanism (for real-time database backup)

Backup policy 1. Directly copy database files
Directly copying data files is the most direct, fast, and convenient, but the disadvantage is that Incremental backup is basically not supported. To ensure data consistency, run the following SQL statement before backing up the file:
Flush tables with read lock;
That is, the data in the memory is refreshed to the disk, and the data table is locked to ensure that no new data is written during the copy process. In this way, the backup data recovery is also very simple, just copy it back to the original database directory.

Backup policy 2. Use mysqlhotcopy to back up a database
Mysqlhotcopy is a perl program originally written by tim bunce. It uses lock tables, flush tables, and cp or scp to quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database file (including the data table definition file, data file, and index file) is located, in addition, mysqlhotcopy can only be used to back up myisam tables.

This backup policy is suitable for backup of small databases with a small amount of data. You can use the mysqlhotcopy program to perform a full backup every day.

 

Backup policy layout:

 

(1) install the dbd-mysql perl module and support connecting the mysqlhotcopy script to the 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 and execute the backup script every day.

 

Shell> crontab-e

 

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

 

Run the backup script at every day.

 

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. Modify the variables as needed

 

# Define the directory where the script is located

 

Scriptsdir = 'pwd'

 

# Database Data Directory

 

Datadir =/usr/local/mysql/data/

 

# Data Backup Directory

 

Tmpbackupdir =/tmp/tmpbackup/

 

Backupdir =/tmp/mysqlbackup/

 

# Username and password used to back up the database

 

Mysqluser = root

 

Mysqlpwd = 111111

 

# Define email addresses

 

Email = alter@somode.com

 

 

# If the temporary Backup directory exists, clear it. If it does not exist, create it.

 

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

 

 

# Clearing mysqlbackup. log

 

If [[-s mysqlbackup. log]; then

 

Cat/dev/null> mysqlbackup. log

 

Fi

 

 

# Obtain the database backup list, where you can filter databases that do not want to be backed up

 

For databases in 'Find $ datadir-type d |

 

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

 

Sed-e "s/test //" '; do

 

 

If [[$ databases = ""]; then

 

Continue

 

Else

 

# Backing up a 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

 

 

# Compressing backup files

 

Date = 'date-I'

 

Cd $ tmpbackupdir

 

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

 

 

# Send email notification

 

If [[-s mysqlbackup. log]; then

 

Cat mysqlbackup. log | mail-s "mysql backup" $ email

 

Fi

 

 

# Use the smbclientmv. sh script to upload the database backup to the 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

 

#

 

# Defining variables

 

# Backup Server Name

 

Backupserver = "backupservername"

 

# Shared folder name

 

Backupshare = "sharename"

 

# Backup server access username and password

 

Backupuser = "smbuser"

 

Backuppw = "smbpassword"

 

# Define the 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-rjdate.tar.gz"

 

 

# Delete temp files

 

Rm-f $ backupdir/mysql-1_date.tar.gz

 

 

 

 

(3) restore the database to the backup status

Mysqlhotcopy backs up the entire database Directory, which can be copied directly to the datadir (/usr/local/mysql/data/) directory specified by mysqld during use, pay attention to permission issues as follows:
Shell> cp-rf db_name/usr/local/mysql/data/

Shell> chown-r mysql: mysql/usr/local/mysql/data/(change the owner of the db_name directory to the mysqld running user)

 

This backup policy can only restore the database to the state of the last backup. If you want to lose as little data as possible during the crash, you should back up the database more frequently, to restore data to the state of the crash, use the master-slave replication mechanism (replication ).

Backup Policy 3. Use mysqldump to back up the database

 

 

 

Mysqldump adopts an SQL-level backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different mysql versions. This is also the most common backup method. Mysqldump is slower than direct copy. For more detailed explanations of mysqldump, see the appendix at the end.

For systems with medium business volume, the backup policy can be set as follows: the first full backup, one Incremental Backup every day, and one full backup every week. For important and BUSY systems, full backup may be required once a day, Incremental backup once an hour, or even more frequently. To achieve online backup and Incremental Backup without affecting online services, the best way is to use the master-slave replication mechanism (replication) to back up data on the slave machine.

Backup policy layout:

 

(1) create a backup directory

 

Shell> mkdir/tmp/mysqlbackup

 

Shell> mkdir/tmp/mysqlbackup/daily

 

(2) Enable binary logs

The binlog method is more flexible and labor-saving, and supports Incremental backup.
Mysqld must be restarted when binlog is enabled. First, close mysqld, open/etc/my. cnf, and add the following lines:
[Mysqld]
Log-bin

Start mysqld. The hostname-bin.000001 and hostname-bin.index will be generated during running, the previous file is mysqld record all the update operations on the data, the subsequent file is the index of all binlog, cannot be easily deleted. For more information about binlog, see the manual.

 

(3) Configure ssh key logon to send mysql backup to the backup server (if the backup server is windows, skip this step ).

 

1) generate an ssh key on the mysql server (192.168.0.20)

[Root @ lab ~] # Ssh-keygen-t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/. ssh/id_rsa ):
// Press Enter.
Enter passphrase (empty for no passphrase ):
// Press enter directly without a password
Enter same passphrase again:
// Press enter directly 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 send 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 you 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 of 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
3) test ssh Logon
[Root @ lab ~] # Ssh 192.168.0.200 // test ssh Logon
Last login: fri nov 16 10:34:02 2007 from 192.168.0.20
[Root @ lib ~] #

 

(4) set the crontab task and execute the backup script every day.

 

Shell> crontab-e

 

# Execute the full backup script at every Sunday.

 

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

 

# Perform Incremental backup at from Monday to Saturday

 

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

 

 

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. Modify the variables as needed

 

# Define the script directory

 

Scriptsdir = 'pwd'

 

# Define the database directory

 

Mysqldir =/usr/local/mysql

 

# Define the username and password used to back up the database

 

User = root

 

User Pwd = 111111

 

# Define the Backup Directory

 

Databackupdir =/tmp/mysqlbackup

 

# Define the mail body File

 

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 the backup file name

 

Dumpfile = mysql _ $ date. SQL

 

Gzdumpfile1_mysql_1_date. SQL .tar.gz

 

 

# Use mysqldump to back up the database. set parameters as needed

 

$ 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

 

 

# Compressing 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 transmit the backup to the backup server or the backup server is windows, comment out the lines marked green

 

# Move backup files to backup server.

 

# Suitable for 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 a log file

Echo "--------------------------------------------------------"> $ logfile

 

Cat $ emailfile> $ logfile

 

# Send email notification

Cat $ emailfile | mail-s "mysql backup" $ email

(5) restore the database to the backup status

The file backed up with mysqldump is an SQL script that can be directly imported into the database. You can import the file directly using the mysql client.

/Usr/local/mysql/bin/mysql-uroot-puserpwd db_name <db_name. SQL
For any applicable update logs, use them as mysql input:
% Ls-t-r-1 hostname-bin * | xargs mysqlbinlog | mysql-uuser-puserpwd

The ls command generates a single column list of update log files and sorts them by the server's order (Note: If you modify any file, you will change the sorting order, this will cause the Update log to be used in the wrong order .)

This backup policy can only restore the database to the state of the last backup. If you want to lose as little data as possible during the crash, you should back up the database more frequently, to restore data to the state of the crash, use the master-slave replication mechanism (replication ). If you use this backup script to place log files and data files on different disks, this not only improves data writing speed, but also improves data security.
Mysql supports unidirectional and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the log file to track log loops. When an slave server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of the next update.

Why do I use master-slave replication?

1. The master server/slave server settings increase robustness. When the master server encounters a problem, you can switch to the slave server as a backup.

2. By splitting the customer query load between the master server and slave server, a better customer response time can be obtained. But do not perform updates on the Master/Slave servers at the same time, which may cause conflicts.

3. Another advantage of using replication is that one slave server can be used for backup without interfering with the master server. During the backup process, the master server can continue to process updates.
 

Mysql uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server. When start slave is issued, an I/o thread is created from the server to connect to the master server and send binary logs to the master server. The master server creates a thread to send the binary log content to the slave server. Read the content sent by the binlog dump thread of the master server from the server I/o thread and copy the data to a local file in the data directory of the slave server, that is, relay logs. The first thread is an SQL thread. The server uses this thread to read relay logs and execute updates contained in the logs. The show processlist statement can be used to query information about replication on the master server and slave server.

The default relay log uses a file name in the form of a host_name-relay-bin.nnnnnn, where host_name is the slave server host name and nnnnnnnn is the serial number. Create a continuous relay log file with a continuous serial number, starting from 000001. Track the relay log index file from the server to identify the currently used relay logs. The default relay log index file name is the host_name-relay-bin.index. By default, these files are created in the data directory of the slave server. Relay logs are in the same format as binary logs and can be read using mysqlbinlog. After the SQL thread executes all the events in the relay log, the relay log is automatically deleted.

The slave server creates two more state files --master.info and relay-log.info in the data directory. Status files are stored on the hard disk and will not be lost when the slave server is closed. When starting the next time from the server, read these files to determine how many binary logs it has read from the master server and how much it will process its own relay logs.
 

Set master-slave replication:

1. Ensure that the mysql version installed on the master server and slave server is the same, and preferably the latest stable version of mysql.

2. Set a connection account for replication on the master server. This account must be granted the replication slave permission. If your account is only used for replication (recommended), you do not need to grant any other permissions.

Mysql> grant replication slave on *.*

-> To 'replicase' @ '% .yourdomain.com' identified by 'slavepass ';

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

Mysql> flush tables with read lock;

Do not exit the mysql client program. Enable another terminal to take snapshots of the Data Directory of the master server.

Shell> cd/usr/local/mysql/

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

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

When the flush tables with read lock is effective (that is, the mysql client does not exit), read the current binary log name and offset value on the master server:

Mysql> show master status;

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

| File | position | binlog_do_db | binlog_ignore_db |

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

| Mysql-bin.003 | 73 | test | manual, mysql |

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

The file column shows the log name, while the position column shows the offset. In this example, the binary log value is mysql-bin.003 and the offset is 73. Record this value. These values will be used for setting slave servers later. They represent the replication coordinates. The slave server should start from this point and perform new updates from the master server.

If -- logs-bin is not enabled when the master server is running, the log name and location values displayed in show master status are empty. In this case, when you specify the log files and locations of the slave server in the future, the values required are null strings ('') and 4.

After obtaining the snapshot and recording the log name and offset, return to the previous middle end to re-enable the Write activity:

Mysql> unlock tables;

4. Make sure that the [mysqld] section of the my. cnf file on the master server host contains a log-bin option. This part should also have a server-id = master_id option, where master_id must be a positive integer between 1 and 232-1. For example:

[Mysqld]

Log-bin

Server-id = 1

If no options are provided, add them and restart the server.

5. Stop the mysqld service on the slave server and add the following lines to its my. cnf file:

[Mysqld]

Server-id = 2

The slave_id value is the same as the master_id value. It must be a positive integer between 1 and 232-1. In addition, the slave server id must be different from the master server id.

6. store the data in the backup data directory. Ensure that the permissions on these files and directories are correct. The user running mysql on the server must be able to read and write files, just as on the master server.

Shell> chown-r mysql: mysql/usr/local/mysql/data

7. Start the slave server. Execute the following statement on the slave server and replace the option value with the actual value of your system:

 

Mysql> change master

-> 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 the slave server thread:

Mysql> start slave;

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

9. If a replication error occurs, an error message will also appear in the server's error log (hostname. err.

10. When copying from the server, the file master.info and hostname-relay-log.info are found in its data directory. The slave server uses these two files to track the number of master server binary logs that have been processed. Do not remove or edit these files unless you know exactly what you are doing and fully understand what it means. Even so, it is best to use the change master to statement