Shulou(Shulou.com)06/01 Report--

The following is about how MySQL implements data backup and recovery. The secret of the text lies in being close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on how to achieve data backup and recovery in MySQL.

Common MySQL pipe tools

Mysql command line cross-platform MySQL official bundle package comes with

MySQL-Workbench graphics cross-platform MySQL official

MySQL-Front graphics Windows open source, lightweight client software

PhpMyAdmin browser is cross-platform open source and requires LAMP platform

Navicat Graphics Windows Professional, powerful, Commercial Edition

PhpMyAdmin deployment ideas:

1. Install httpd, mysql, php-mysql and related packages

two。 Start the httpd service program

3. Extract the phpMyAdmin package and deploy to the website directory

4. Configure config.inc.php to specify the MySQL host address

5. Create an authorized user

6. Browser access, login to use

Yum-y install httpd php php-mysql

Tar-zxf phpMyAdmin-2.11.11-all-languages.tar.gz-C / var/www/html/

Cd / var/www/html/

Mv phpMyAdmin-2.11.11-all-languages/ phpmyadmin

Chown-R apache:apache phpmyadmin/

Cp phpmyadmin/config.sample.inc.php phpmysdmin/config.inc.php

Vim / var/www/html/phpmyadmin/config.inc.php

$cfg ['Servers'] [$I] [' host'] = 'localhost'

$cfg ['blowfish_secret'] =' 123456'

Mysql > create database bbsdb

Mysql > grant all on bbsdb.* to admin@'localhost' identified by '654321'

Systemclt start httpd

Firefox http://localhost/phpmyadmin

MySQL data backup and recovery

Data backup method:

1. Physical backup, cold backup: cp, tar,....

Example: physical backup and recovery

Backup operation format:

Cp-rp / var/lib/mysql/ database backup directory / file name

Tar-zcvf xxx.tar.gz / var/lib/mysql/ database / *

Restore operation format:

Cp-rp backup directory / filename / var/lib/mysql/ (default storage path)

Tar-zxvf xxx.tar.gz-C / var/lib/mysql/ database name /

two。 Logical backup, mysqldump, mysql

Principle: when performing a backup, the corresponding SQL commands are generated according to the existing libraries and tables, and the generated SQL commands are stored in the specified backup file.

Backup strategy:

Full backup: back up all data (one CVM, one database, one table)

Incremental backup: back up data that has changed since the last backup (including full backup, differential backup, incremental backup).

Differential backup: backing up data that has changed since the last full backup.

Example: logical backup and recovery

Full backup operation format:

Mysqldump-u username-p password source library name > path / xxx.sql

Restore operation format for full backup:

Mysql-u username-p password target library name

< 路径/xxx.sql 库名的表示方式： --all-databases 所有库 库名 指定的单个库 库名.表名 指定库的指定表 -B 库名1 库名2 .. 备份多个库 注意事项： 无论备份还是恢复，都要验证用户及权限。 例： 将所有的库备份为mysql-all.sql文件 mysqldump -u root -p 123456 --all-databases >

Alldb.sql

Back up the userdb library as a userdb.sql file

Mysqldump-u root-p 123456 userdb > userdb.sql

Restore the backup file userdb.sql to the userdb3 library

Mysql > create databases userdb3

Mysql-u roo-p 123456 userdb3

< userdb.sql 实时增量备份 binlog日志 类型：二进制日志，用途：记录所有更改数据的操作， 配置： log_bin[=dir/name] server_id=数字 max_binlog_size=数字m 启用binlog日志 采用binlog日志的好处 1.记录除查询之外的所有SQL命令。 2.用于数据恢复。 3.配置mysql主从同步的必要条件。 例： vim /etc/my.cnf [mysqld] .... log_bin //启用binlog日志 server_id=100 //指定id值,id号不能重复（1-255）范围。 systemctl restart mysqld binlog相关文件 默认日志文件名： 主机名-bin.index //记录已有的binlog日志文件名 主机名-bin.000001 //第1个二进制日志（达到500M存去下一条纪录） 主机名-bin.000002 //第2份二进制日志 .... 手动生成新的日志文件 1.重启mysql服务 2.执行SQL操作 mysql >

Flush logs; (flush logs, switch to the next binlog log file)

3.mysqldump-flush-logs

4.mysql-uroot-p password-e'flush logs'

Clean up the binlog log

Delete binlog logs earlier than the specified version:

Purge master logs to 'binlog file name'

Delete all binlog logs and rebuild the new log:

Reset master

Example:

Mysql > purge master logs to 'mysql-bin.000003'

Mysql > reset master

Analyze binlog Lo

View the log's current record format:

Mysql > show variables like 'binlog_format'

Modify the logging format:

Vim / etc/my.cnf

[mysqld]

....

Binlog_format= "mixed" sets the record format of log files

Systemctl restart mysqld

Three record formats:

1.statement: every sql command that modifies data is recorded in the binlog log.

2.row: no sql statement context-sensitive information is recorded, only which record is modified.

3.mixed: is a mixture of the above two formats.

How the binlog log file records sql commands:

1. Time point

2.pos point (offset)

Check what bilog logs are available:

Mysql > show master logs

Use the mysqlbinlog tool

Format: mysqlbinlog [option] binlog log file name

Common options:

1. Point in time:

-start-datetime= "yyyy-mm-dd hh:mm:ss"

-stop-datetime= "yyyy-mm-dd hh:mm:ss"

2.pos points:

-- start-position= number

-- stop-position= number

Ex.: view the change action mysqlbinlob-- start-datetime= "2017-01-01 15:30" / var/lib/mysql-bin.000001....at 318 that began 15:30 on January 2, 2017

Explanation:

Server id 1: service number of the database host

End_log_pos 796: pos node at the end of sql

Thread_id=11: thread number

Binlog recovers data

Method 1:

Basic ideas:

1. Use mysqlbinlog to extract historical SQL operations

two。 Send the mysql command to execute through the pipeline.

Example: restore part of the information of the first binlog log mysqlbinlog-- base64-output=decode-rows-v / var/lib/mysql/mysql-bin.000001mysqlbinlog-- start-position=296-- stop-position=1073 / var/lib/mysql-bin.000001 | mysql- uroot-p123456

Method 2:

Command format:

Mysql > show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Parameter explanation:

IN 'log_name': specify the name of the binlog file to be queried (the first binlog file if not specified)

FROM pos: specify the starting point of pos (if not specified, it will start from the first pos point of the whole file)

LIMIT [offset,]: offset (0 if not specified)

Row_count: total number of queries (all rows are not specified)

Example:

Mysql > show binlog events in 'mysql-bin.000002'\ G

MySQL backup tool

Physical backup disadvantages:

1. Poor cross-platform

two。 Long backup time, redundant backup, waste of storage space

Mysqldump backup disadvantages:

1. Low efficiency, slow backup and restore.

two。 During the backup, data insertion and update operations are suspended.

XtraBackup backup tool:

1. The library table is not locked during backup, which is suitable for production environment.

two。 Provided by the professional organization Percona (improved MySQL branch).

There are two main components:

1.xtrabackup:C program that supports InnoDB/XtraDB

2.innobackupex: encapsulates xtrabackup in Perl scripts and supports MyISAM

Supports transaction and transaction rollback, and requires the storage engine to be innodb

Transaction log file:

Ibdata

LSN log serial number

Ib_logfile0 / / SQL command

Ib_loggile1

....

Install XtraBackup

Yum-y install perl-Digest-MD5.x86_64 rsync perl-DBD-MySQL

Rpm-ivh libev-4.15-1.el6.rf.x86_64.rpm

Rpm-ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

Rpm-ql percona-xtrabackup-24

/ usr/bin/innobackupex / / back up the tables of innodb, xtrdb, myisam engines

/ usr/bin/xbcloud

....

/ usr/bin/xtrabackup / / back up the tables of innodb and xtrdb engines

....

Innobackupex basic options-host / / hostname-user / / username-port / / port number-password / / password-databases / / database name (single library: databases= "library name", multiple libraries: databases= "library 1, library 2", single table: databases= "library. Table ")-- no-timestamp / / subdirectory name of backup file storage without date naming-- redo-only / / log rollback merge (this option is not required for the last incremental backup log rollback)-- apply-log / / ready to restore (rollback log)-- copy-back / / restore data-- incremental directory name / / incremental backup-- incremental-basedir= directory name / / when incremental backup Specify the directory name of the last backup data store-incremental-dir= directory name / / when you are ready to restore data, specify the directory name of the incremental backup data store-export / / export table information import / / export tablespace

XtraBackup full backup

Format: innobackupex-user user name-password password-databases= "system Library list and Storage Database" backup directory name-no-timestamp

Example: completely back up all libraries to / backupinnobackupex-user root-password 123456 / backup-no-timestamp

XtraBackup full recovery

An empty library directory is required for full recovery

Rm-rf / var/lib/mysql

Mkdir / var/lib/mysql

Chown-R mysql:mysql / var/lib/mysql

Format: innobackupex-user user name-password password-databases= "system Library list and Storage Database"-copy-back backup directory name

Example: restore all data innobackupex-- apply-log / backup innobackupex-- copy-back / backup

XtraBackup incremental backup

There must be a full backup first.

Format: innobackupex-- user user name-- password password databases= "system library list and storage database"-- incremental directory name-- incremental-basedir= "full backup directory name"-no-timestamp

Example: full backup to / allbak, first incremental backup to / new1, Second incremental backup to / new2cp-rp / var/lib/mysql/mysql / root/mysql.bak / / backup authorization library innobackupex-- user root-- password 123456-- databases= "gamedb" / fullbak-- no-timestamp / / full backup innobackupex-- user root-- password 123456-- databases= "gamedb"-- incremental / new1-- incremental-basedir= "/ fullbak"-- no-timestamp / / first incremental backup innobackupex -- user root-- password 123456-- databases= "gamedb"-- incremental / new2-- incremental-basedir= "/ new1"-- no-timestamp / / second incremental backup

XtraBackup incremental recovery

Rm-rf / var/lib/mysql

Mkdir / var/lib/mysql

Chown-R mysql:mysql / var/lib/mysql

Format:

1.innobackupex-- user user name-- password password-- databases= "system Library list and Storage Database"-- apply-log-- redo-only full backup directory name

2.innobackupex-- user user name-- password password-- databases= "system Library list and Storage Database"-- apply-log-- redo-only full backup directory name-- incremental-dir= incremental backup directory name

3.innobackupex-- user user name-- psssword password-- databases= "system Library list and Storage Database"-- copy-back full backup directory name

Example: restore the first incremental backup to / new1, Rm-rf / var/lib/mysqlmkdir / var/lib/mysqlinnobackupex of the second incremental backup to / new2-- user root-- password 123456-- databases= "gamedb"-- apply-log-- redo-only / fullbak / / restore full backup innobackupex-- user root-- password 123456-- databases= "gamedb"-- apply-log-- redo-only / fullbak-- incremental-dir= "/ new1" / / restore increment Innobackupex-- user root-- password 123456-- databases= "gamedb"-- apply-log / fullbak-- incremental-dir= "new2" / / restore incremental innobackupex-- user root-- password 123456-- databases= "gamedb"-- copy-back / fullbak / / copy file cp-r / root/mysql.bak / var/lib/mysql/mysqlchown-R mysql:mysql / var/lib/mysql

Restore a single table in a full backup file

Format: innobackupex innobackupex-- user user name-- password password-- databases= "system Library list and Storage Database"-- apply-log-- export full backup directory name

Example: fully back up the database to the / allbak directory innobackupex-- user root-- password 123456-- databases= "gamedb" / allbak-- no-timestamp / / full backup mysql > drop table gamedb.a;innobackupex-- user root-- password 123456-- databases= "gamedb"-- apply-log-- export / allbak / / Export table information mysql > create table gamedb.a (id int); / / create table mysql > alter table gamedb.a discard tablespace / / Delete the tablespace cp / allbak/gamedb/a. {ibd,cfg,exp} / var/lib/mysql/gamedb/ / copy the table information file chown mysql:mysql / var/lib/mysql/gamedb/a.* / / modify the owner

Mysql > alter table gamedb.an import tablespace; / / Import tablespaces

Is there anything you don't understand about the above MySQL how to achieve data backup and recovery? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.