MYSQL backup and recovery in linux

Database Backup is very important. If regular backup is performed, the data can be restored to the last normal state in the case of a system crash, minimizing the loss. 1. run the mysqldump command to back up MySQLl. we can use it to back up data. the following assumes that we want to back up the tm database: # mysqldump-uroot-ptm & gt; tm_050519. SQL enter the password as prompted, which makes it very important to back up all the databases in the tm database. If regular backup is performed, the data can be restored to the last normal state in the case of a system crash, minimizing the loss.

　　I. back up data using commands

MySQLl provides a mysqldump command, which can be used for data backup. the following assumes that you want to back up the tm database:

# Mysqldump-u root-p tm> tm_050519. SQL

Enter the password as prompted, and back up all the table structures and data of the tm database to tm_050519. SQL. because the backup work is always required, if the data volume occupies a large space, in this case, you can use gzip to compress data. the command is as follows:

# Mysqldump-u root-p tm | gzip> tm_050519. SQL .gz

When the system crashes and the system is rebuilt, data can be restored as follows:

# Mysql-u root-p tm <tm_050519. SQL

Direct Recovery from compressed files:

# Gzip <tm_050519. SQL .gz | mysql-u root-p tm

Of course, many MySQL tools provide more intuitive backup and recovery functions, such as phpMyAdmin. However, I think mysqldump is the most basic and common.

　　II. using crontab, the system regularly backs up mysql databases every day

The system crontab is used to regularly execute backup files and save the backup results by date to achieve the purpose of backup.

1. create the path to save the backup file/mysqldata

# Mkdir/mysqldata

2. create the/usr/sbin/bakmysql file.

# Vi/usr/sbin/bakmysql

Input

Rq = 'date + % Y % m % d'

Tar zcvf/mysqldata/mysql1_rq.tar.gz/var/lib/mysql

Or write it

Rq = 'date + % Y % m % d'

Mysqldump -- all-databases-u root-p password>/mysqldata/mysql $ rq. SQL

/Var/lib/mysql is the directory of your database files. some users are/usr/local/mysql/data.

/Mysqldata/indicates the directory where the backup file is saved. this can be done according to your requirements.

3. Modify file attributes to make them executable.

# Chmod + x/usr/sbin/bakmysql

4. modify/etc/crontab

# Vi/etc/crontab

Add

01 3 *** root/usr/sbin/bakmysql

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

5. restart crond.

#/Etc/rc. d/init. d/crond restart

Complete.

In this way, you can see such files in/mysqldata every day.

Mysql20040619.tar.gz

You can download it directly.

Cd/usr/local/mysql/bin/

Mysqldump-u username-p password -- databases Database name>/backdata/ddd07-11-15. SQL

Restore syntax

Mysqladmin create target_db_name

Mysql target_db_name <backup-file. SQL

That is, the mysql database name <file name

Complete. Note that there is no space behind-u. you just need to replace the above Chinese with your related information!