MySQL Database backup summary

The normal operation of an enterprise, the integrity of the data is the most critical; so we need to be very skilled in the work of data backup and recovery methods; Here is a summary of the three methods of MySQL database backup, I hope it will help you

Preparation of work environment before backup starts:

1, create a directory to save the binary log files

# Mkdir/mybinlog

# chown Mysql.mysql/mybinlog

2, modify the configuration file

# VIM/ETC/MY.CNF

Log-bin=/mybinlog/mysql-bin binary log directory and file prefix

innodb_file_per_table = 1 Enable InnoDB table

DataDir =/mydata/data The directory of the specified database

3. Create a storage point for the backup database

# Mkdir/mybackup

# Chown-r Mysql.mysql/mybackup

4, start the MySQL server

# service Mysqld Start

5, insert the database that needs to be backed up

# MySQL < Jiaowu.sql

First, complete backup using the Mysqldump tool + incremental backup based on mysqldump is usually a full backup + binary log to recover data

1.1, mysqldump used for warm backup, the first need to read the lock for all libraries, and scroll down the binary log, record the current binary file location

# mysqldump--all-databases--lock-all-tables--routines--triggers--master-data=2

--flush-logs >/mybackup/alldatabase.sql

Explain the meaning of each option:

--all-databases Back up all databases

--lock-all-tables Lock for all tables

--routines stored procedures and stored functions

--triggers triggers

--master-data=2 records the location in Change master to, but defaults to being annotated

--flush-logs Perform log scrolling