MySQL Data backup and recovery

Tag: Time default for serial number transaction data backup state named Copy file

Common MySQL Tube tools
MySQL command line cross-platform MySQL official bundle package comes with

Mysql-workbench Graphics Cross-platform MySQL official offer

Mysql-front Graphics Windows Open source, lightweight client software

PhpMyAdmin Browser cross-platform open source, need lamp platform

Navicat Graphics Windows Professional, powerful, commercial edition

phpMyAdmin Deployment Ideas:
1. Install httpd, MySQL, php-mysql and related packages
2. Start the HTTPD service program
3. Unzip the phpMyAdmin package and deploy to the site Directory
4. Configure config.inc.php, specify the MySQL host address
5. Create an authorized user
6. Browser access, login 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 [email protected] ' 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/*

Recovery operation Format:
CP-RP backup directory/file name/var/lib/mysql/(default storage path)
TAR-ZXVF xxx.tar.gz-c/var/lib/mysql/Database name/

2. Logical backup, mysqldump, MySQL
Rationale: When performing a backup, the generated SQL commands are stored in the specified backup file based on the existing libraries and tables that generate the corresponding SQL commands.

Backup policy:
Full backup: Back up all data (one server, one library, one table)
Incremental backup: Backs up data that has changed since the last backup, which contains a full, differential, and incremental backup.
Differential backup: Backs up data that has changed since the last full backup.

Example: logical Backup and Recovery
Full backup operation format:
Mysqldump-u User name-p password Source Library name > Path/xxx.sql

Recovery operation format for full backup:
Mysql-u User name-p password Target library name < path/xxx.sql

How the library name is represented:
--all-databases All Libraries
A single library specified by the library name
Library name. Table name specifies the table for the library
-B Library Name 1 Library Name 2: Backing up multiple libraries

Precautions:
Verify users and permissions regardless of backup or restore.

Cases:
Back up all libraries as mysql-all.sql files
Mysqldump-u root-p 123456--all-databases > Alldb.sql

To 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

Real-time Incremental backup
Binlog Log
Type: Binary log, Purpose: Records all changes to the data,
Configuration:
Log_bin[=dir/name]
Server_id= Digital
Max_binlog_size= Digital M

Enable Binlog logging
Benefits of using Binlog logs
1. Record all SQL commands except the query.
2. For data recovery.
3. Configure the prerequisites for MySQL master-slave synchronization.

Cases:
Vim/etc/my.cnf
[Mysqld]
....
Log_bin//Enable Binlog log
SERVER_ID=100//Specify ID value, ID number cannot repeat (1-255) range.

Systemctl Restart Mysqld

Binlog Related Documents
Default log file name:
Hostname-bin.index//Record existing Binlog log file name
Hostname-bin.000001//1th binary log (up to 500M to save next record)
Hostname-bin.000002//2nd binary Log
....

To generate a new log file manually
1. Restart the MySQL service
2. Execute SQL operation 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 older than the specified version:
Purge master logs to ' binlog filename ';

Delete all Binlog logs and rebuild the new log:
Reset Master;

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

Mysql>reset master;

Analyze Binlog Logs
To view the log current record format:
Mysql>show variables like ' Binlog_format ';

To modify the logging format:
Vim/etc/my.cnf
[Mysqld]
....
binlog_format= "mixed" to set the record format of the log file

Systemctl Restart Mysqld

Three record formats:
1.statement: Each SQL command that modifies data is recorded in the Binlog log.
2.row: Does not log the SQL statement context-sensitive information, only save which record is modified.
3.mixed: It is a mixed use of the above two formats.

How the Binlog log file records SQL commands:
1. Point in time
2.pos Point (offset)

Using the Mysqlbinlog tool
Format: mysqlbinlog [options] 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 Point:
--start-position= Digital
--stop-position= Digital

例：查看从2017年1月2日15:30开始的更改操作mysqlbinlob  --start-datetime="2017-01-01 15:30"  /var/lib/mysql-bin.000001....at 318

Binlog Recovering data
Basic ideas:
1. Extracting historical SQL operations using Mysqlbinlog
2. Pass the pipe to MySQL command execution

例：恢复第1份binlog日志的部分信息mysqlbinlog  --start-position=296  --start-position=1073  /var/lib/mysql-bin.000001  |mysql -uroot  -p123456

MySQL Backup tool
Physical Backup Disadvantages:
1. Poor cross-platform
2. Long backup time, redundant backup, wasted storage space

Mysqldump Backup Disadvantages:
1. Low efficiency, slow backup and restore speed.
2. During the backup process, data insertion and update operations are suspended.

Xtrabackup Backup tool:
1. The library table is not locked during the backup process and is suitable for production environments.
2. Provided by professional organization Percona (improved MySQL branch).
It consists of two components:
1.XTRABACKUP:C Program, Support INNODB/XTRADB
2.innobackupex: Xtrabackup is packaged in Perl scripts and also supports MyISAM

Supports transaction and transaction rollback requiring the storage engine to be InnoDB
Transaction log files:
Ibdata
LSN Log Sequence Number
IB_LOGFILE0//sql Command
Ib_loggile1
....

Installing Xtrabackup
Yum-y Install per-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//Backup tables for InnoDB, XTRDB, MyISAM engines
/usr/bin/xbcloud
....
/usr/bin/xtrabackup//Backup table for InnoDB, Xtrdb engine
....

innobackupex基本选项--host           //主机名   --user           //用户名--port           //端口号--password       //密码--databases      //数据库名（单个库：databases="库名"，多个库：databases="库1 库2"，单个表：databases="库.表"）--no-timestamp   //不用日期命名备份文件存储的子目录名--redo-only      //日志回滚合并（最后一次增量备份日志回滚不需要此选项）--apply-log      //准备还原（回滚日志）--copy-back      //恢复数据--incremental 目录名  //增量备份--incremental-basedir=目录名   //增量备份时，指定上一次备份数据存储的目录名 --incremental-dir=目录名        //准备恢复数据时，指定增量备份数据存储的目录名--export         //导出表信息import           //导出表空间

Xtrabackup Full Backup
Format: Innobackupex-user user name--password password--databases= "System library list and storage database" Backup directory name--no-timestamp

例：将所有库完全备份到 /backupinnobackupex  --user root  --password 123456 /backup  --no-timestamp  

Xtrabackup Full Recovery
Empty Library directory 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

例:恢复所有数据innobackupex  --user root  --password 123456 --copy-back  

Xtrabackup Incremental Backup
Must have 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

例：完全备份到/allbak、第一次增量备份到/new1、第二次增量备份到/new2cp  -rp  /var/lib/mysql/mysql  /root/mysql.bak  //备份授权库innobackupex  --user root  --password 123456 --databases="gamedb"  /fullbak  --no-timestamp  //完全备份innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new1  --incremental-basedir="/fullbak" --no-timestamp //第一次增量备份innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new2  --incremental-basedir="/new1"  --no-timestamp  //第二次增量备份

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

例：恢复第一次增量备份到/new1、第二次增量备份到/new2的数据rm -rf /var/lib/mysqlmkdir  /var/lib/mysqlinnobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  //恢复完全备份innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  --incremental-dir="/new1"  //恢复增量innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  /fullbak  --incremental-dir="new2"  //恢复增量innobackupex  --user root  --password 123456  --databases="gamedb"  --copy-back  /fullbak  //拷贝文件cp -r /root/mysql.bak  /var/lib/mysql/mysqlchown  -R  mysql:mysql  /var/lib/mysql

Recovering a single table from 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

例：完全备份数据库到/allbak目录innobackupex  --user root  --password 123456  --databases="gamedb"  /allbak --no-timestamp //完全备份mysql>drop  table gamedb.a;innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --export  /allbak  //导出表信息mysql>create  table gamedb.a(id int);  //创建表mysql>alter   table gamedb.a  discard tablespace;  //删除表空间cp  /allbak/gamedb/a.{ibd,cfg,exp}  /var/lib/mysql/gamedb  //拷贝表信息文件chown  mysql:mysql  /var/lib/mysql/gamedb/a.*   //修改所有者mysql>alter  table  gamedb.a  import  tablespace;  //导入表空间

MySQL Data backup and recovery