Mysqldump usage, and full + incremental backup with mysqldump

using mysqldump to MySQL of full and incremental backups

purpose of Backup: When data is an important asset, we need to back it up frequently to prevent data corruption , it can be restored to its damaged state in time.

Backup content: Data, configuration files, binary logs, transaction logs

Backup Category:

Backup Type:

hot Backup, warm backup and cold backup

Hot backup: Read, write not affected;

Warm backup: Only read operation can be performed;

Cold backup: Offline backup, read and write operations are aborted;

physical and logical backups

physical Backup: Copy data files;

Logical Backup: Export data to a text file;

Full backup, incremental backup, and differential backup;

full backup: Back up all data;

Incremental backup: Backs up only data that has changed since the last full or incremental backup;

differential backup: Backs up only data that has changed since the last full backup

Backup tool:xtrabackup, mysqldump

MyISAM engine can only support warm backup,InnoDB can support hot standby and Win Bei.

backup strategy: Full + delta; full + diff

Mysqldump Backup Tool Introduction

mysqldump Syntax

mysqldump db_name [TB1] [TB2] backs up only one database, or a table in the library, note: It does not contain the name of the database, that is, you must manually create the database when you want to restore it in the future

--MASTER-DATA={0|1|2}

0:: Do not log the binary log file and location:

1: record location in change MASTER to , can be used for recovery after direct start from the server:

2: The location is recorded as change MASTER to , but is commented by default:

--lock-all-tables: locks All tables before starting a backup of the MyISAM engine's tables.

--flush-logs: lock table before backup, perform log scrolling

-single-transaction start hot standby

if the tables in the specified library are InnoDB, you can start hot standby with-single-transaction:

--events backup events, backing up database-defined event schedulers

--routines Backup stored procedures and storage functions

--triggers backup triggers

Backing up multiple libraries

--all-databases: backing up all libraries

--databases db_name,db_name,... back up the specified library

These two commands do not have to be created manually until the library is backed up by more than one library.

Test One:mysqldump Full backup + binary log to do timely point restore simulated full backup + Incremental backup

test Environment: MySQL version:mysql-5.1.73-8

Linux Distributions:CentOS6.8

Preparation before the experiment:

1. Create DATABASE Jiaowu and tutors tables, as follows

CREATE DATABASE Educational affairs:mysql> create databases Jiaowu;

To create a tutors table:

CREATE TABLE ' Tutors ' (

' TID ' smallint (5) unsigned not NULL auto_increment,

' tname ' varchar (not NULL),

' Gender ' enum (' F ', ' m ') DEFAULT ' m ',

' Age ' tinyint (3) unsigned DEFAULT NULL,

UNIQUE KEY ' tid ' (' tid ')

) Engine=innodb auto_increment=10 DEFAULT charset=latin1; CREATE TABLE Structure

Insert INTO ' tutors ' VALUES// Insert Table Data

(1, ' HongQiGong ', ' m ', ', '), (2, ' Huangyaoshi ', ' m ', ' 5 '), (3, ' Miejueshitai ', ' F ',), (4, ' Ouyangfeng ', ' m ', ","), (Yideng) , ' m ', ' N ', (6, ' Yucanghai ', ' m ', ' a '), (7, ' Jinlunfawang ', ' m ', '), (8, ' Huyidao ', ' m ', "), (9, ' Ningzhongze ', ' F ', 49);

2. Enable the binary log

add log-bin=mysql-bin to [ mysqld] in profile /etc/my.cnf (mysql-bin indicates binary log name) ; and then restart the service: Servicesmysqld Restart

See if Bin_log is on:mysql> SELECT @ @sql_log_bin; 1: Open

Experimental steps:

1. lock table, refresh

Mysql> FLUSH TABLES with READ LOCK;

2. View current binary log

mysql> SHOW MASTER LOGS;

3, do log scrolling

Mysql> FLUSH TABLES with READ LOCK;

4. View the start record location of the binary record after scrolling: The next transaction starts at the 106 of mysql-bin.000004:

mysql> SHOW BINARY LOGS;

5. Create a backup directory

[Email protected] ~]# Mkdir/root/backup

6, do a full backup

[[email protected] ~]# mysqldump-uroot-p Jiaowu >/root/backup/jiaowu-' Date +%f_%h-%m-%s '

7. View backup Files

[Email protected] backup]# ls-l/root/backup/

8. change the owner or group after the backup

[Email protected] backup]# chown-r mysql.mysql/root/backup/

9. View the result after the change:

[Email protected] backup]# ls-l/root/backup/

after the backup is finished, unlock

Mysql> UNLOCK TABLE;

Insert new Data in the tutors standard of the JIAOWU database

Mysql> INSERT into Tutors (tname) VALUES (' stu1 '), (' stu2 ');

look at the updated content of the table

Mysql> SELECT * from tutors;

13. Enter the binary directory

[Email protected] backup]# Cd/var/lib/mysql

back up the updated binary log

[email protected] mysql]# CP Mysql-bin.000004/root/backup

Delete all data files

[Email protected] mysql]# RM-RF./*

16. Restart MySQL service

[Email protected] ~]# service mysqld restart

17. Create the original database

mysql> CREATE DATABASE Jiaowu;

18. Restore the database

MySQL Jiaowu < jiaowu.2018-04-21-07-13-59

19. View the results after the restore:

Mysql> select * from tutors;

20, using binary log to do a timely point of restoration

[Email protected] backup]# Mysqlbinlog mysql-bin.000004 | Mysql-uroot–p

21. View the restore results:

Mysql> select * from tutors;

Mysqldump usage, and full + incremental backup with mysqldump