MYSQL backup and recovery in linux (mainly in red) _ MySQL

MYSQL backup and recovery in linux (mainly in red) bitsCN.com

Database Backup is very important. if you back up data regularly, you can restore the data to the last normal state in case of a system crash, reducing the number of data backups to a minimum.

I. using Minhang for backup

MySQL provides a mysqldump command, which can be used for Database Backup. let's assume that you have backed up the tm database:

#mysqldump -u root -p tm > tm_050519.sql

Enter the password as Prompted. in this case, all the table structures and data of the tm database are backed up to the tm_050519. SQL file, because the backup work is always required. if the data volume occupies a large space, you can Use gizip compression commandThe command is as follows:

#mysqldump -u root -p tm | gzip > tm_050519.sql.gz

When the system crashes and the system is rebuilt, you can reply to the data as follows:

#mysql -u root -p tm < tm_050519.sql

Direct Recovery from compressed files:

#gunzip < 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.

2. use crontab to regularly execute backup files and save the backup results by date to achieve the backup purpose.

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/mysql$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 file. some users are/usr/local/mysql/data.

/Mysqldata/indicates the directory for storing the backup file. This user can also do it as needed.

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.

Mysql20140324.tar.gz

You can download it directly.

Cd/usr/local/mysql/bin

Mysqldump-u username-p password -- databases Database name>/backdata/ddd14-03-23. SQL

Restore syntax

mysqladmin create target_db_namemysql target_db_name < backup-file.sql

That is, the mysql database name <file name

Complete. note:-u is followed by no space. you only need to replace the above Chinese with your relevant information.

BitsCN.com