Linux how to restore the mysql database backup

This article describes how to back up and restore mysql database under linux.

Database backup is very important. If you do a regular backup, so that you can recover the data in the event of a system crash to the last normal state, the loss is reduced to a minimum.

First, the order to achieve backup

MySQLl provides a mysqldump command, we can use it for data backup.

Follow the prompts to enter the password, which put all the tm database table structure and # mysqldump-u root-p tm> tm_050519.sql data backup to tm_050519.sql, because the total backup job, if the data volume assembly takes up a lot of space , Then you can use gzip compressed data, the command is as follows:

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

System crash, reconstruction system, you can restore the data:

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

Directly from the compressed file recovery:

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

Of course, there are many MySQL tools to provide more intuitive backup and recovery capabilities, such as using phpMyAdmin is very convenient. But I think, mysqldump is the most basic and most common.

Second, the use of crontab, the system regularly backup mysql database

Use the system crontab to regularly perform backup files, save the backup results by date, to achieve the purpose of backup.

1, create a backup file backup path / var / backup / mysqlbak

# mkdir -p / var / backup / mysqlbak

2, create / usr / sbin / bakmysql file

#vi /usr/sbin/bakmysql.sh

#! / bin / bash # mysql backup script cd / var / backup / mysqlbak / dateDIR = `date + '% y-% m-% d" `mkdir -p $ dateDIR / data for i in` / usr / local / www / mysql / bin / mysql -uroot -plin31415926 -e "show databases" | grep -v "Database" | grep -v "information_schema" `do / usr / local / www / mysql / bin / mysqldump -uroot -plin31415926 $ i | gzip> /var/backup/mysqlbak/$dateDIR/${i}_${dateDIR}.gz done

3, modify the file attributes, make it executable

# chmod + x / usr / sbin / bakmysql

4, modify / etc / crontab

# crontab -e Add 01 3 * * root / usr / sbin / bakmysql below

# Indicates that the backup is performed at 3 o'clock every day

So every day you can see the backup sql file under / var / backup / mysqlbak!