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

1 data backup and recovery

2 use third-party software for backup

1 data backup and recovery

1.1 data backup mode

Physical backup: directly copy the files corresponding to the library or table. Cp,tar,...

It has limitations, as long as the storage engine of the table is myisam, the cross-platform is poor, and the data backup and recovery is a waste of time.

Logical backup: when performing a backup, the corresponding sql command is generated according to the existing data, and the sql is saved to the specified file. Execute the sql command in the backup file during recovery.

1.2 data backup strategy

Full backup: back up all data.

Incremental backup: backing up data that has changed since the last backup

Differential backup: backing up data that has changed since a full backup

Full backup + incremental backup

Full backup + differential backup

1.3 full backup

Backup command:

[root@ser51 ~] # mysqldump-hlocalhost-uroot-p password source library name > file name

Representation of the source library name:

-- all-databases all libraries

Library name specifies a single library

The library name table name specifies the table of the library (note that there is a space between the library name and the table name)

-B database 1 database 2 back up multiple libraries

Restore command:

There are two ways:

(1) [root@ser51 ~] # mysql connection library operation target library name source backup file path

1.4 incremental backup

(1) enable binlog log to realize real-time incremental backup

Binlog log introduction: binary log, is one of the mysql database service log files, records the execution of sql commands other than queries. It is not enabled by default.

Vim / etc/my.cnf

[mysqld]

Server_id= values # values range from 1-255,

Log_bin # enable default storage path / var/lib/mysql/

Binlog_format= "mixed"

The file generated after restarting the service is:

/ var/lib/mysql/ hostname-bin.000001

# generate new ones over 500m (000001-999999)

/ var/lib/mysql/localhost-bin.index # index file

# systemctl restart mysqld # restart the service

(2) View the current record format of the log

Mysql > show variables like "binlog_format"

Three record formats:

Statement: every sql command that modifies data is recorded in the binlog log

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

Mixed: is a mixture of the above two formats.

(3) View the contents of the binlog log file

# mysqlbinlog / var/lib/mysql/localhost-bin.000001

# mysqlbinlog localhost-bin.000001 | grep-I insert

(4) customize the directory and file name of the binlog log file

# mkdir / logdir

# chown mysql / logdir; setenforce 0

# vim / etc/my.cnf

Server_id= values # values range from 1-255,

Log_bin=/logdir/mysql

# Custom storage path (mysql is equivalent to hostname and must be written without mkdir)

Binlog_format= "mixed"

# systemctl restart mysqld

# ls / logdir/

(5) analyze binlog logs

How does the binlog log file record sql commands?

Time point

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

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

Pos point (offset)

-- start-position= number

-- stop-position= number

[root@ser51 logdir] # mysqlbinlog

-start-position=336-stop-position=1494 mysql.000001

(6) execute the sql command in binlog log to recover data

# mysqlbinlog [option] log file name | mysql-uroot-p123qqq

# mysqlbinlog-start-position=300-stop-position=1006 / var/lib/mysql/localhost-bin.000001 | mysql-uroot-p123456

(7) generate new binlog logs manually

Type 1: mysql > flush logs; # refresh and generate once

Type 2: mysql-uroot-p123qqq-e "flush logs"

#-e is to execute the sql command with mysql login.

Type 3: # systemctl restart mysqld

Type 4: # mysqldump-uroot-p123qqq-- flush-logs teadb T7 > / databak/t7.sql # regenerate when backing up

(8) Delete existing binlog log files

Type 1: mysql > reset master; # reset all

Type 2: mysql > purge master logs to "binlog file name"

# Delete logs before the specified binlog

Type 3: # rm-rf binlog log file

2 use third-party software for backup

2.1 install Percona

A powerful online hot backup tool, the backup process does not lock the library table, suitable for production environment.

There are two main components:

Xtrabackup:

Innobackupex:

[root@ser51 ~] # yum-y install perl-Digest-MD5 perl-DBD-MySQL

# different environments install different dependent packages

[root@ser51] # rpm-ivh libev-4.15-1.el6.rf.x86_64.rpm

[root@ser51] # rpm-ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

2.2 innobackupex full backup

(1) basic options:

-host-- user-- port-- password-- databases=

-- no-timestamp # the name of the subdirectory stored in the backup file without date naming

-- redo-only # log rollback merge. This item is not required for the last incremental backup log rollback.

-- apply-log # ready to restore (rollback log)

-- copy-back # restore data

-- incremental directory name # path where incremental backups are stored

-- incremental--basedir= directory name

# for incremental backups, specify the directory name of the last backup data store

-- incremental--dir= directory name # specify the directory name of the incremental backup data store when restoring data

-- export # Exporting table information

Import # Import tablespaces

Data recovery requires that the database directory must be empty (/ var/lib/mysql)

(2) use innobackupex for full backup and full recovery

Case: full backup to / allback

[root@ser51] # innobackupex-user root-password 123456-databases= "mysql performance_schema sys db66" / allback

# databases: the database name / allback to be backed up is automatically generated when it is created, but does not need to be created

[root@ser51] # ls / allback/2018-01-22 / 21-19-33 /

# View the generated backup file

[root@ser51] # innobackupex-- user root-- password 123456-- databases= "mysql performance_schema sys db66"-- apply-log / allback/2018-01-2211 21-19-33

# synchronize log

[root@ser51 ~] # rm-rf / var/lib/mysql/ # requires emptying during recovery

[root@ser51 ~] # mkdir / var/lib/mysql # create a new

[root@ser51] # innobackupex-- user root-- password 123456-- databases= "mysql performance_schema sys db66"-- copy-back / allback/2018-01-2211 21-19-33

# restore data

[root@ser51 ~] # ls-ld / var/lib/mysql # View the properties of the directory

[root@ser51] # chown-R mysql:mysql / var/lib/mysql

# change to owner, belong to group mysql

[root@ser51 ~] # systemctl restart mysqld # restart the service

[root@ser51 ~] # mysql-uroot-p123456 # enter to check whether the recovery is successful

What if the service doesn't succeed?

Mv / etc/my.cnf / etc/my.cnf.back

Mysql_install_db-user=mysql-datadir=/var/lib/mysql

2.3 innobackupex incremental backup

(1) conditions

There must be a full backup. This example takes backup to / onedir as an example.

First incremental backup to / new1dir

The second incremental backup to / new2dir

(2) incremental backup

Step 1: full backup

[root@ser51] # innobackupex-user root-password 123456-databases= "mysql performance_schema sys db66" / onedir-no-timestamp

Step 2: change the data in the db66 database table

Make the first incremental backup to / new1dir

[root@ser51] # innobackupex-user root-password 123456-databases= "mysql performance_schema sys db66"-incremental / new1dir-incremental-basedir=/onedir-no-timestamp

# for incremental backups, specify the directory name of the last backup data store

Step 3: change the data in the db66 database table again

Make a second incremental backup to / new2dir

[root@ser51] # innobackupex-user root-password 123456-databases= "mysql performance_schema sys db66"-incremental / new2dir-incremental-basedir=/new1dir-no-timestamp

Step 4: data recovery

[root@ser51] # innobackupex-user root-password 123456-databases= "mysql performance_schema sys db66"

-- apply-log-- redo-only / onedir # restore the full backup log

[root@ser51] # innobackupex-user root-password 123456-databases= "mysql performance_schema sys db66"

-apply-log-redo-only / onedir/-incremental-dir=/new1dir

# restore incremental logs

[root@ser51] # innobackupex-user root-password 123456-databases= "mysql performance_schema sys db66"

-apply-log-redo-only / onedir/-incremental-dir=/new2dir

# restore incremental logs

[root@ser51] # innobackupex-- user root-- password 123456-- databases= "mysql performance_schema sys db66"-- copy-back / onedir # restore files

[root@ser51] # chown-R mysql:mysql / var/lib/mysql/

[root@ser51 ~] # systemctl restart mysqld

Log in to the database to see if the data is restored

2.4 principle of incremental backup:

Transaction logs ib _ logfile0 and ib_logfile1 ibdata1 exist under / var/lib/mysql/

After the backup, the synchronization log has the following two files in the files of the new backup:

Xtrabackup_checkpoints # exists lsn: log sequence number

Xtrabackup_logfile # Log File

Each time the log is recovered incrementally, the log is merged with the full backup log, and the log sequence number changes, which records the location of the merged sequence number and merges the incremental backup data into the full backup.

[root@ser51 ~] # cat / onedir/xtrabackup_checkpoints

[root@ser51 ~] # cat / new1dir/xtrabackup_checkpoints

[root@ser51 ~] # cat / new2dir/xtrabackup_checkpoints

2.5 restore a table in the full backup data

(1) Delete tablespace command

Mysql > alter table library. Table discard tablespace

(2) the database that is backed up completely:

# innobackupex-user root-password 123456-databases= "db66" / db66bak-no-timestamp

# ls / db66bak

(3) mysql > use db66

Mysql > drop table a; # the process of deleting table an and restoring table a

Mysql > create table a (id int); # create an empty table, the table field information must be the same as the original

# ls / var/lib/mysql/db66/

A.frm a.ibd

(4) Export table information

# innobackupex-user root-password 123456

Databases= "db66"-- apply-log-- export / db66bak

# ls / db66bak/db66/ # see what has changed

(5) Delete tablespace

Mysql > alter table db66.a discard tablespace

# ls / var/lib/mysql/db66/

(6) copy the table information files exported from the backup directory to the database directory

# cp / db66bak/db66/a. {cfg,exp,ibd} / var/lib/mysql/db66/

(7) modify the file owner

# chown mysql/ var/lib/mysql/db66/a.*

(8) Import tablespace

Mysql > alter table db66.an import tablespace

Mysql > select * from a

Mysql > select * from b

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.