MySQL backup and recovery policy notes (2)

MySQL backup and recovery policy (ii) 12:00:08 big label: knowledge/exploration MySQL backup and recovery MySQL backup policy MySQL recovery policy

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 without a password

Enter same passphrase again: // press enter 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 backup server's root password

[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 "backupfilename: $ gzdumpfile" >>$ 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

 

Previous Article: MySQL backup