How to backup and restore MySQL database under Linux

This article describes how to backup and restore the MySQL database under Linux.

Database backup is very important. If you do a regular backup, you can restore the data to the last normal state in the event of a system crash, minimizing the loss.

One, use the command to implement the backup

MYSQLL provides a mysqldump command that we can use to back up the data.

Press the prompt for the password, which backs up all the table structures and # Mysqldump-u ROOT-P TM > Tm_050519.sql data for the TM database to Tm_050519.sql, because the total backup effort, if the data volume assembly takes up a lot of space, You can use gzip to compress data at this point, with the following commands:

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

System crashes, you can restore data when you rebuild the system:

# Mysqldump-u Root-p TM < Tm_050519.sql

To recover directly from a compressed file:

#gzip < tm_050519.sql.gz  Mysqldump-u root-p TM

Of course, there are a lot of MySQL tools to provide more intuitive backup recovery features, such as the use of phpMyAdmin is very convenient. But I think, mysqldump is the most basic, most general.

Second, the use of crontab, the system regularly backup MySQL database daily

Use the system crontab to execute the backup file regularly, save the backup result by date, and achieve the purpose of backup.

1, create the path to save the backup file/var/backup/mysqlbak

# mkdir-p/var/backup/mysqlbak

2. Create/usr/sbin/bakmysql files

#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 properties to make it executable

# chmod +x/usr/sbin/bakmysql

4. Modify/etc/crontab

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

#表示每天3点钟执行备份

This way you can see the backup SQL file in/var/backup/mysqlbak every day!