Ubuntu study notes mysql database backup and recovery

First, go to the mysql installation directory.

Backup:./mysqldump-u root-proot (do not have spaces) dbname>/home/aa. SQL

Restore: mysql-u root-proot dbname

Sorted out some instances

1. Backup

The code is as follows:	Copy code
[Root @ localhost ~] # Cd/var/lib/mysql (go to the MySQL database directory and adjust the directory based on your MySQL installation) [Root @ localhost mysql] # mysqldump-u root-p voice> voice. SQL. Enter the password.

2. Restore

Method 1:

The code is as follows:	Copy code
[Root @ localhost ~] # Mysql-u root-p Press enter and enter the password to go to the MySQL console "mysql>", which is restored in the same way as 1.2.

Method 2:

The code is as follows:	Copy code
[Root @ localhost ~] # Cd/var/lib/mysql (go to the MySQL database directory and adjust the directory based on your MySQL installation) [Root @ localhost mysql] # mysql-u root-p voice <voice. SQL, enter the password. Mysqldump: Got error: 1016 when using LOCK TABLES

Hitidea is a database with many data tables (Wordpress MU). After searching, we found that when mysqldump is added

The above method is certainly not used by experts, because server data can be backed up frequently, so I google again to find a piece of code

Example: Description:

Here I want to back up the pw85 database under the/var/lib/MySql database directory to the compressed file format of/home/snapshot (2012_04_11 refers to the date on which the backup was executed ), at last, only the backups of the last 7 days are retained.

Steps:

1. Create a directory to save the backup file:/home/mysql_data
Cd/home # enter the Directory
Mkdir mysql_data # Create a directory
2. Create a backup script file:/home/mysql_data/mysql_databak.sh
Cd/home/mysql_data # enter the Directory
Touch mysql_databak.sh # Create a file
Nano mysql_databak.sh # edit the file and enter the following content

The code is as follows:

Copy code

#! /Bin/sh DUMP =/usr/bin/mysqldump # mysqldump backup program execution path OUT_DIR =/home/mysql_data # backup file storage path LINUX_USER = root # system username DB_NAME = pw85 # 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

(Note: {}; there is a space in the middle)
######################################## ######################################## #######################
Ctrl + o # save configuration
Ctrl + x # exit

3. Modify file attributes to make them executable.

Chmod + x/home/mysql_data/mysql_databak.sh

4. Modify/etc/crontab

Nano/etc/crontab # edit the file and add
45 22 * root/home/mysql_data/mysql_databak.sh # indicates that backup is performed at every day.
Ctrl + o # save configuration
Ctrl + x # exit

5. Restart crond to make the settings take effect.

Service cron stop # stop
Service cron start # start
/Etc/init. d/cron restart # restart
Chkconfig cron on # set to boot

Every day, you can see compressed files such as mysqldata_bak_2012_04_11.tar.gz under the/home/mysql_datadirectory.
To restore a file, extract the file tar-zxvf mysqldata_bak_2012_04_11.tar.gz.
Import the data to the database.

So far, the MySql database backup script under Ubuntu Server is complete.