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 |