MySQL backup solution -- & gt; (using mysqldump and binlog binary logs)

As data increases and the innodb Storage engine is compatible with the future, The mysqldump full backup + incremental log backup policy is considered. Using mysqldump provides good support for most mysql storage engines such as myisam and innodb.

Solution 1: mysqldump full backup + Log Incremental Backup

1. mysqldump backup solution:

Full backup at on Monday

Incremental backup from Tuesday to Sunday at a.m.

 

2. Backup steps

(1) create a backup directory and store the backup script directory.

Shell> mkdir/usr/mysqlbackup;

Shell> chmod 755/usr/mysqlbackup;

Shell> mkdir/usr/mysqlbackup/daily;

Shell> chmod 755/usr/mysqlbackup/daily;

Shell> mkdir/usr/script;

Shell> chmod 777/usr/script/*. sh

 

(2) Enable binary log

If the log is not enabled, binlog must be enabled. to restart mysqld, first disable mysqld, open/etc/my. cnf, and add the following lines:

[Mysqld]

Log-bin

Then restart mysqld, which will generate a hostname-bin.000001 as well as a hostname-bin.index, the previous log file is to record all the update operations on the data, the subsequent file is the index that stores all binary files and cannot be easily deleted.

 

(3) Full backup and Incremental backup.

For details, see the mysqlFullBackup. sh and mysqlDailyBackup. sh scripts (note the Backup Directory, mysql software installation directory, compressed file name, and username and password in the script. If there is any inconsistency, modify it ).

Below are some test commands manually executed by a single shell.

Shell>/usr/local/mysql/bin/mysqldump-uroot-pnYuIman25040slave201012301124 -- no-create-info = FALSE -- order-by-primary = FALSE -- force = FALSE -- no-data = FALSE -- tz-utc = TRUE -- flush-privileg

Es = FALSE -- compress = FALSE -- replace = FALSE -- insert-ignore = FALSE -- extended-insert = TRUE -- quote-names = TRUE -- hex-blob = TRUE -- complete-insert = FALSE -- add-locks = TRUE -- port = 3306 -- d

Isable-keys = TRUE -- delayed-insert = FALSE -- create-options = TRUE -- delete-master-logs = FALSE -- comments = TRUE -- default-character-set = utf8 -- max_allowed_packet = 1G -- flush-logs = FALSE-

-Dump-date = TRUE -- lock-tables = TRUE -- allow-keywords = FALSE -- events = FALSE -- single-transaction = TRUE -- routines -- all-databases>/backup/mysql/ full/mysql_20110104_195546. SQL

 

(4) set the crontab task and execute the backup script every day.

Shell> crontab-e

# Execute the full backup script at every Sunday.

0 3 ** 0/usr/scrpit/mysqlFullBackup. sh>/dev/null 2> & 1

# Perform Incremental backup at from Monday to Saturday

0 3 ** 1-6/root/MySQLBackup/mysqlDailyBackup. sh>/dev/null 2> & 1

(5) Clear old backup files.

Check the backup disk space every day and delete the old backup compressed files.

3. database recovery

 

[Note] if an ERROR similar to "ERROR 1051 (42S02) at line 32: Unknown table 'fin _ cell_attr_his '" is reported in the incremental recovery operation, create an empty table, then perform incremental recovery.

Mysql> createtable tb1 ......;

 

(1) full recovery:

The file backed up with mysqldump is an SQL script that can be directly poured into. You can use the mysql command to restore the last full backup:

Copy the file from the backup file server, decompress it, and restore it. For example:

Shell>/root/s3cmd/s3cmd get s3: // csf-backup/mysqldb/mysql_20101216_231900. SQL .tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd get s3: // csf-backup/mysqldb/mysql_binlog_20101217_030100.tar.gz/usr/mysqlrestore/

Shell> cd/usr/mysqlrestore/

Shell> tar-xvf mysql_20101216_231900. SQL .tar.gz

Shell> tar-xvf mysql_binlog_20101217_030100.tar.gz

 

Refresh log:

Mysql> flush logs;

Restore the last full BACKUP command:

Shell>/usr/local/mysql/bin/mysql-uroot-p123456 </tmp/mysqlbackup/backup_20101215_adb. SQL

Restore all Incremental Backup commands after the last full backup:

Shell>/usr/local/mysql/bin/mysqlbinlog mysql-bin.00007 mysql-bin.00008mysql-bin.00009 mysql-bin.00010 mysql-bin.00011 | mysql

Check: view the database structure and data.

 

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases

 

For more details of this article, please continue to read the highlights of page 2nd:

1 2 Next Page