MySQL data backup and recovery _ MySQL-mysql tutorial

MySQL data backup and recovery I. data backup methods

Description of MySQL parameters: http://www.linuxidc.com/Linux/2014-04/99673.htm

Data backup and recovery http://www.linuxidc.com/Linux/2014-04/99674.htm for MySQL management

1. physical backup

1.1 cold standby cp tar and so on

1.2 hot standby mysqlhotcopy can only back up tables whose storage engine is myisam and rely on perl-DBD-MySQL

2. logical backup: use the backup commands provided by mysql or the backup commands provided by third-party software to back up the SQL statements used to create databases, tables, and table records.

II. data backup policy:

1. complete backup of all tables in all databases on the database server.

2. differential backup all new data generated after the complete backup.

3. incremental backup all new data generated after the last backup

Generally, full backup + incremental backup or full backup + differential backup are used in the production environment.

III. logical backup and recovery

1. mysqldump command for full data backup

Command format:

Mysqldump-h database server ip address-u user name-p password database name> directory name/backup file name

Database name representation:

-- All-databases backup all tables in all databases on the database server

The database name backs up a database.

The database name table backs up the specified table in the specified database.

-Database B name 1 database name 2 database N complete backup of several databases

# Directory name: if no directory name is specified during backup, the backup file is stored in the directory where the backup command is executed.

# Backup File name: you can define it by yourself. it must be distinguished. it is customary that mysql backup files end with. SQL.

If the file name is duplicate, the previous backup will be overwritten by the current backup.

# Users who use backup to connect to the database server must have the permissions of the target database to be backed up.

2. restore full backup data

Command format:

Mysql-h database server ip-u user name-p password database name <directory name/xxx. SQL

Mysql-h database server ip-u username-p password <directory name/xxx. SQL

# Database name: Optional. when the backup file contains the create database or use SQL statement, the database name is not specified during data recovery.

# Restoring data with a full backup file only restores data to the backup state. The information data generated after the full backup cannot be recovered.

4. use binlog for incremental backup and data recovery

1. mysql log type

Mysql has four types of logs:

Binlog logs (binary logs) record SQL statements in addition to queries.

The error log records the errors generated during mysql service startup and running.

Query all SQL operations performed by log records.

Slow query logs only record SQL statements that display query results when the specified time is exceeded. The default timeout value is 10 seconds.

# By default, mysql only enables error logs.

# Logs are stored in the Database Directory by default. Each log file is named by default.

2. enable binlog

Vim/etc/my. cnf

[Mysqld]

Log_bin =/binlogdir/filename // Set the binlog directory and file name to be stored. the custom directory must allow mysql users to write data, if the directory is not set, it is in the database directory/var/lib/mysql/by default. if the file name is not set, the default value is host name-bin.20.01.

Max-binlog-size = 100 M // Set the binlog log file size. if not set, the default value is M and the second binlog log file is automatically generated.

Service mysql restart

# The changed SQL statement sent for all database operations will be written into the binlog file with the largest number currently.

# The localhost-bin.index records the current binlog log file

3. analyze the binlog file content.

How to record SQL statements in binlog:

① Time mode

② Character offset

Mysqlbinlog [option] binlog log file name

Option:

Offset

-- Start-position = 100 start position

-- Stop-position = 1200 end position

Time point:

-- Start-datetime = "YYYY-mm-dd HH: MM: SS" start time

-- Stop-datetime = "YYYY-mm-dd HH: MM: SS" end time

# View all log content by default without adding any options

4. manually generate a new binlog file:

① Mysql> flush logs;

② # Mysql-h database server IP address-u user name-p password-e "flush logs"

③ # Mysqldump-h database server IP-u username-p password -- flush-logs database name>/directory name/xxx. SQL

④ Service mysql restart // not commonly used

5. incremental backup data recovery

Principle: use mysqlbinlog to extract historical SQL operation pipelines and redo them with mysql commands.

Command format:

Mysqlbinlog [option] binlog log file name | mysql-h database server ip-u user name-p password database name

Batch restore incremental backup:

Mysqlbinlog 'cat localhost-bin.index '| mysql-h database server ip-u username-p password [Database name]

6. clear binlog logs

Delete a binlog file that is earlier than the specified number:

Mysql> purge master logs to 'binlog log file name ';

Delete all binlog log files and recreate the logs:

Mysql> reset master;

For more details, refer to the highlights on the next page.: Http://www.linuxidc.com/Linux/2014-05/102266p2.htm