Backup to restore MySQL database in Linux server

1. Back up MySQL:

Export a library to a SQL file

The code is as follows	Copy Code
Mysqldump db_name >/home/hx/backup/name.sql-uroot-p

Back up multiple libraries (using the-b parameter)

The code is as follows	Copy Code
Mysqldump-b db1 DB2 >/path/name.sql-u root-p

Back Up all libraries

The code is as follows	Copy Code
Mysqldump--all-databases >/path/name.sql-u root-p

Back up a table in a library

The code is as follows	Copy Code
Mysqldump db1 tb1 >/path/name.sql

Back up multiple tables in a library

The code is as follows	Copy Code
mysqldump db1 tb1 tb2 >/path/name.sql

Create a structure for a table in a library

The code is as follows	Copy Code
mysqldump-d DB1 users > Users.structure

Produce the contents of a table in a library

The code is as follows	Copy Code
Mysqldump-t DB1 users > Users.data

2. Data recovery

There are two ways of doing this:
First Type:

The code is as follows	Copy Code
MySQL db_name </path/backup-file.sql-u root-p The second type: Mysql-u root-p Use Db_name Source/path/backup-file.sql 3.Linux compression, the generated SQL is prepared

file for packing.
Zip Xxx.zip xxx.sql
TAR-CZF Xxx.rar Xxx.sql
The decompression of tar is x, and the package is C.

4. Upload and download the backup, I use the SECURECRT.
Download to local, SZ directory/filename
Upload to server, RZ
Rz-r continued to pass
Rz-y Overwrite Existing Files

The above all need manual, below I will introduce an automatic backup MySQL database

MySQL regular backup is an important work, but manual operation is too cumbersome, also difficult to avoid omissions, use the following methods to allow the system to back up data on a regular basis.

1. Create a backup folder

The code is as follows	Copy Code
#cd/www #makedir Backup

2. Write Run script

The code is as follows	Copy Code
#vi Autobackup

Write the following:

The code is as follows	Copy Code
Filename= ' Date +%y%m%d ' Mysql_bin_dir/mysqldump–opt Dataname-u User-ppassword | gzip >/www/mysqlbackup/name$filename.gz

Save exit

Description

(1) The bin path of the Mysql_bin_dir:mysql;

(2) Dataname: database name;

(3) User: Database username;

(4) Password: User password;

(5) Name: Custom backup file prefix identification.

As in the previous example, the MySQL database is automatically backed up and stored in gzip compression, in the form of a file named name20080101.gz.

3. Add execution permissions to the script

#chmod +x Autobackup

4, let crontab to complete the task of regular execution

In this step, the Redhat method will be different, specifically given in the following.

Edit crontab:

The code is as follows	Copy Code
#vi/etc/crontab

In the last line, add:

The code is as follows	Copy Code
5 * * * root/www/autobackup

Run the script at 5 o ' Day, or you can modify 5 for another specified time.

Redhat Method:

Redhat crontab use 4 directories per hour (/etc/cron.hourly: hourly;/etc/cron.daily: every day;/etc/cron.weekly: Weekly;/etc/ Cron.monthly: Monthly) The way the script runs out.

In Redhat, just copy the script you just edited to the appropriate directory.

5, restart Crontab

The code is as follows	Copy Code
#/etc/rc.d/init.d/crond restart