Beginner Tutorial: MySQL database backup command

MySQL database backup command

1. Export the entire database

The exported files are stored in the mysql \ bin directory by default.
Mysqldump-u username-p database name> exported file name
Mysqldump-u user_name-p123456 database_name> outfile_name. SQL

2. Export a table

Mysqldump-u user name-p database name table name> exported file name
Mysqldump-u user_name-p database_name table_name> outfile_name. SQL

3. Export a database structure

Mysqldump-u user_name-p-d-add-drop-table database_name> outfile_name. SQL
-D no data-add-drop-table add a drop table before each create statement

4. Export with language parameters

Mysqldump-uroot-p-default-character-set = latin1-set-charset = gbk-skip-opt database_name> outfile_name. SQL

For example, back up the aaa database to the file back_aaa:

[Root @ test1 root] # cd/home/data/mysql
[Root @ test1 mysql] # mysqldump-u root-p-opt aaa> back_aaa

5. 1. Copy the date folder backup
======================================
Hypothetical environment:
MySQL installation path: C: \ MySQL
Forum database name: bbs
Database backup destination: C: \ db_bak \
======================================
Create db_bak.bat and write the following code

* ***************************** Code Start ******* **********************
Net stop mysql
Xcopy c: \ mysql \ data \ bbs \ *. * c: \ db_bak \ bbs \ % date :~ 0, 10% \/S/I
Net start mysql
* ***************************** Code End ******* **********************

Then run the batch processing script periodically using Windows's scheduled task. (For example, execute back_db.bat at every day)
Explanation: The backup and recovery operations are simple, the integrity is relatively high, and the backup cycle is controlled flexibly. For example, % date :~ 0, 10%. This method is suitable for users who have independent hosts but have no management experience on mysql. The disadvantage is that it takes a lot of space and mysql will be disconnected for a short time during the backup period (for example, it takes about 5 seconds for a database of about 30 MB), for % date :~ Usage Reference

In Linux, Mysql databases are automatically backed up and uploaded to a remote FTP server.

Steps:

1. Create the path to save the backup file:/home/mysql_data
Cd/home
Mkdir mysql_data
2. Create a backup script file:/home/mysql_data/mysql_databak.sh
Cd/home
Cd mysql_data
Touch mysql_databak.sh
Vim mysql_databak.sh
Enter the following content:
######################################## ######################################## #######################
#! /Bin/sh
DUMP =/usr/bin/mysqldump # mysqldump backup file execution path
OUT_DIR =/home/mysql_data # backup storage path
LINUX_USER = root # system username
DB_NAME = data # Name of the database to be backed up
DB_USER = root # Database account Note: non-root users must use the backup parameter -- skip-lock-tables; otherwise, an error may occur.
DB_PASS = 123456 # database password
DAYS = 7 # DAYS = 7 indicates that the backup created seven DAYS ago is deleted, that is, only the backup of the last seven DAYS is retained.
Cd $ OUT_DIR # enter the backup storage directory
DATE = 'date + % Y _ % m _ % d' # obtain the current system time
OUT_ SQL = "$ DATE. SQL" # Name of the backup database file
TAR_ SQL = "mysqldata_bak_1_date.tar.gz" # Name of the final database backup file
$ DUMP-u $ DB_USER-p $ DB_PASS $ DB_NAME -- default-character-set = utf8 -- opt-Q-R -- skip-lock-tables> $ OUT_ SQL # backup
Tar-czf $ TAR_ SQL./$ OUT_ SQL compressed into .tar.gz format
Rm $ OUT_ SQL # delete backup files in. SQL format
Chown $ LINUX_USER: $ LINUX_USER $ OUT_DIR/$ TAR_ SQL # Change the owner of the backup database file
Find $ OUT_DIR-name "mysqldata_bak _ *"-type f-mtime + $ DAYS-exec rm {}\; # delete backup files 7 DAYS ago

Deldate = 'date-d-7day + % Y _ % m _ % d' # get the time seven days ago
Ftp-n <!
Open 192.168.1.1 21 # open the ftp server. Port 21 is the ftp port
User admin 123456 # user name and password
Binary # set binary transmission
Cd mysqlbak # enter the ftp directory (this directory must be a real directory in the ftp space)
LCD/home/mysql_data # List local directories
Prompt
Put mysqldata_bak_$DATE.tar.gz # upload files in the directory
Delete mysqldata_bak_includeldate.tar.gz # delete backup of ftp space seven days ago
Close
Bye!

######################################## ######################################## #######################

3. Modify file attributes to make them executable.

Chmod + x/home/mysql_data/mysql_databak.sh

4. Modify/etc/crontab
Vi/etc/crontab
Add

30 1 * root/home/mysql_data/mysql_databak.sh
Indicates that the backup is performed at every day.

5. Restart crond to make the settings take effect.
/Etc/rc. d/init. d/crond restart
Chkconfig crond on # set to boot
Service crond start # start

Every day, you can see compressed files such as mysqldata_bak_2012_12_19.tar.gz under the/home/mysql_datadirectory.
To restore a file, you only need to decompress the file.
Decompress tar-zxvf mysqldata_bak_2012_12_19.tar.gz