9. The establishment of standalone running environment -- mysqldump Backup and Restore database under CentOS-6.4

Create a database for testing

Mysql-u root-P
# Set the permission to facilitate backup

set password for 'root'@'localhost' =password('dsideal');

flush privileges;

# Delete the old test database
Drop database test;

# Create database test; use test; Create Table Test (id int); insert into test values (1); insert into test values (2 ); insert into test values (3); insert into test values (4); insert into test values (5); select * from test;

Back up the entire database of test.

# Use the following VI/usr/local/backup_test.sh

The content is as follows:

#!/bin/sh

date_str=`date +%Y%m%d`cd /data2/backupmysqldump -h localhost -u root --password=dsideal -R -E -e \  --max_allowed_packet=1048576 --net_buffer_length=16384 test\   | gzip > /tmp/data/dsideal_test_$date_str.sql.gz

echo "DataBase Backup Success!"

Parameter description:

The max_allowed_packet and net_buffer_length parameters play a decisive role. The speed difference is several hundred or thousands of times. My configuration here is: -- max_allowed_packet = 25165824 -- net_buffer_length = 16384 its principle is to merge multiple data entries into one SQL insert statement.

Note:
-EUse the multiline insert syntax that includes several values lists;
-- Max_allowed_packet =The maximum size of the cache area for communications between clients and servers of XXX;
-- Net_buffer_length =The size of the XXX TCP/IP and socket communication buffer, and the line with length up to net_buffer_length is created.
Note: max_allowed_packet and net_buffer_length cannot be greater than the set value of the target database. Otherwise, an error may occur.
First, determine the parameter value of the target database.
Mysql>Show variables like 'max _ allowed_packet ';
Mysql>Show variables like 'net _ buffer_length ';
Write the mysqldump Command Based on the parameter value, for example:
Mysql> mysqldump-uroot-psupidea jb51.net goodclassification-e -- max_allowed_packet = 1048576 -- net_buffer_length = 16384> www.jb51.net. SQL

 

# Run

SH/usr/local/backup_test.sh

Restore:

Direct Recovery from compressed files:

gzip < dsideal_test_20130405.sql.gz | mysqldump -u root -p test

 

Note: The test database must exist here. Otherwise, it cannot be restored. That is to say, to ensure that the database exists, you must create a new machine manually.

# Modifying file attributes to make them executable

Chmod + x/usr/local/backup_test.sh

# Modify/etc/crontab

Crontab-e
Add

01 3 * * * root /usr/local/backup_test.sh

# Indicates that a backup is performed at three o'clock every day.

# If you need a backup of a different host

http://www.cnblogs.com/dwzjs/archive/2011/04/25/2027687.html

Crontab scheduled task management

Crontab-e01 03 ***/var/proftpd_data/backup/bin/mysqlfullbackup. sh10 04 ***/var/proftpd_data/backup/bin/ftpdownload. the integer value range and significance of the first five fields of SH are: 0 ~ 59 indicates minute 1 ~ 23 indicates the hour 1 ~ 31 indicates the day 1 ~ 12 indicates the month from 0 ~ 6 indicates the week (0 indicates Sunday)