How to back up and restore a mysql database in linux

This article describes how to back up and restore a mysql database 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.

I. Back up data using commands

MySQLl provides a mysqldump command, which can be used for data backup.

Enter the password as prompted. In this case, all the table structures of the tm database and # mysqldump-u root-p tm> tm_050519. SQL data are backed up to tm_050519. SQL because the backup is always required, if the data volume occupies a large space, you can use gzip to compress the 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:

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

Direct Recovery from compressed files:

#gzip < tm_050519.sql.gz | mysqldump -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/var/backup/mysqlbak

# mkdir -p /var/backup/mysqlbak

2. Create the/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/datafor 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/${i1__1_1_datedir1_.gz done

3. Modify file attributes to make them executable.

# chmod +x /usr/sbin/bakmysql

4. Modify/etc/crontab

# Crontab-e add 01 3 *** root/usr/sbin/bakmysql

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

In this way, you can view the backup SQL file under/var/backup/mysqlbak every day!