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

Data backup and recovery

Backup method:

Physical backup: directly copy the files corresponding to the backup library and table

Cp-r / var/lib/mysql/mysql / mysql.bak

Tar-zcvf / mysql.tar.gz / varlib/mysql/mysql/*

Logical backup: when performing a backup, generate corresponding sql commands according to the existing libraries and tables, and store the generated sql commands in the specified backup file.

Backup strategy:

Full backup: back up all data (tables, libraries, servers)

Differential backup: backup newly created after a full backup (backing up newly generated data)

Incremental backup: backup since the last backup, all newly generated (backup of newly generated data)

Issues to consider when backing up data

Backup frequency backup time backup strategy storage space backup file named xx.sql

How backup policies are used

Full + increment

Complete + difference

How to perform data backup: periodically schedule tasks crond to execute backup scripts

Full backup data mysqldump

Mysqldump-uroot-pabc123 library name > directory / name. Sql

The representation of the library name

-- all-database # backup all data from the database server

Library name # back up all data in a library

Library Table # back up all data in a table (libraries and tables are spaced by spaces)

-B library name 1 library name 2 # back up all the data in multiple databases together

[root@mysql4-1 admin] # mkdir / bakdir

[root@mysql4-1 admin] # mysqldump-uroot-pabc123 mysql > / bakdir/mysql.sql

Mysqldump: [Warning] Using a password on the command line interface can be insecure.

Mysql > create database test

[root@mysql4-1 admin] # mysql-uroot-pabc123 test

< /bakdir/mysql.sql mysql: [Warning] Using a password on the command line interface can be insecure. 增量备份与增量恢复 一，启用binlog日志 实现实时增量备份 binlog日志介绍：又被称为二进制日志 是mysql数据库服务日志文件的一种，记录连接服务器后，执行的除查询之外的sql命令 查看的：show desc select 写的：insert update delete 启用binlog日志 vim /etc/my.cnf [mysqld] server_id=1#5.7之前的版本不需要使用 值1-255 log-bin #systemctl restart mysqld 默认存储路径 /var/lib/mysql 默认文件名 主机名-bin.000001 >

At 500m, it will be generated automatically

Hostname-bin.000002

Log index file hostname-bin.index records the existing binlog log file name

[root@mysql4-1 ~] # cd / var/lib/mysql

[root@mysql4-1 mysql] # cat mysql4-1-bin.index

. / mysql4-1-bin.000001

Customize the binlog log

# mkdir / logdir

# chown mysql / logdir

# vim / etc/my.cnf

Server_id=1

Log-bin= directory name / log file name

When the max_binlog_size= number m exceeds the sub-m size, the next log file is automatically generated.

Binlog_format= "mixed"

[root@mysql4-1 ~] # vim / etc/my.cnf

[mysqld]

Server_id=1

Log-bin=/logdir/test

Binlog_format= "mixed"

# systemctl restart mysqld

View log current record format

Mysql > show variables like 'binlog_format'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | ROW |

+-+ +

There are three record formats:

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

Row: do not record the context-sensitive information of sql statements, only save that record that has been modified

Mixed: is a mixture of the above two formats

View binlog log contents

Mysqlbinlog binlog log file name

/ * / in the content is the annotated content

5.7. write directly to sql and write directly to sql

Access to the database

Mysql > insert into studb.user (name,gid,uid) values ('li',123456)

Mysql > insert into studb.user (name,gid,uid) values ('bob',23,56)

Mysql > insert into studb.user (name,gid,uid) values ('ail',5023,5056)

[root@mysql4-1 mysql] # mysqlbinlog / logdir/test.000001 | grep-I insert

SET INSERTIATION IDC 48Universe costs /

Insert into studb.user (name,gid,uid) values ('li',123456)

SET INSERTIVITY IDC 49According to the price /

Insert into studb.user (name,gid,uid) values ('bob',23,56)

SET INSERTHERTIZIDESTRATION 50Universe parts /

Insert into studb.user (name,gid,uid) values ('ail',5023,5056)

How the binlog log file records sql commands:

Time point

Pos point (offset)

# at 4 from offset 4 to 123 time 2017-12-25 20:59:39

# 171225 20:59:39 server id 1 end_log_pos 123

Execute the sql command recorded in binlog to recover data

Mysqlbinlog option binlog log file name | mysql-uroot-pabc123

Option

Point in time-start-datetime= "yyyy/mm/dd hh:mm:ss"

-- stop-datetime= "yyyy/mm/dd hh:mm:ss" # does not declare the end time to read to the end of the log by default

Offset pos point

-- start-positon= number

-- stop-positon= number

Under what circumstances will a new binlog log file be generated (a new one will be created automatically when the default is > 500m)

# systemctl restart mysqld

Mysql > flush logs

# mysql-uroot-pabc123-e "flush logs"

# mysqldump-uroot-pabc123-- flush-logs table name > backup file. Sql

[root@mysql4-1] # mysqldump-uroot-pabc123-- flush-logs mysql > / root/mysql.sql

Mysql > show master status

+-+- -+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+- -+

| | test.000002 | 154 | |

+-+- -+

1 row in set (0.00 sec)

Mysql > create database db1

Mysql > create table db1.t1 (id int)

Mysql > insert into db1.t1 values

Mysql > insert into db1.t1 values

Mysql > insert into db1.t1 values

Mysql > flush logs

Mysql > show master status

+-+- -+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+- -+

| | test.000003 | 154 | |

+-+- -+

1 row in set (0.00 sec)

[root@mysql4-1 ~] # mysql-uroot-pabc123-e "flush logs"

[root@mysql4-1 ~] # cat / logdir/test.index

/ logdir/test.000001

/ logdir/test.000002

/ logdir/test.000003

/ logdir/test.000004

Delete binlog log file method

# rm-rf log file # this deletion will not synchronize the index file is not recommended

Mysql > reset master;# delete all current log files and regenerate one log file

Purge master logs to "Log File name" # Delete the file from the beginning to the current log file name

Mysql > purge master logs to 'test.000003'

[root@mysql4-1 ~] # cat / logdir/test.index

/ logdir/test.000003

/ logdir/test.000004

Mysql > reset master

[root@mysql4-1 ~] # cat / logdir/test.index

/ logdir/test.000001

Practice incremental backup and recovery

1) fully back up a database

[root@mysql4-1 ~] # mysqldump-uroot-pabc123 student > / bakdir/stu.sql

2) insert three records

Mysql > insert into student.users (name,UID,GID) values ('test01',1,1)

Mysql > insert into student.users (name,UID,GID) values ('test02',2,2)

Mysql > insert into student.users (name,UID,GID) values ('test03',3,3)

Mysql > select count (*) from student.users

+-+

| | count (*) |

+-+

| | 47 |

+-+

3) delete all content

Mysql > delete from student.users

Query OK, 47 rows affected (0.06 sec)

4) restore

Restore with full backup first

[root@mysql4-1] # mysql-uroot-pabc123 student

< /bakdir/stu.sql 在使用binlog 做增量恢复 [root@mysql4-1 ~]# mysqlbinlog /logdir/test.000001 | grep -i -n delete 174:delete from student.users 查找其偏移量在1780-2765之间 [root@mysql4-1 ~]# mysqlbinlog --start-position=1780 --stop-position=2765 /logdir/test.000001 | mysql -uroot -pabc123 mysql>

Select count (*) from student.users

+-+

| | count (*) |

+-+

| | 47 |

+-+

1 row in set (0.00 sec)

Mysql > select * from student.users where name like "test%"

+-+ +

| | id | name | password | UID | GID | describes | home | shell |

+-+ +

| | 45 | test01 | NULL | 1 | 1 | NULL | NULL | / bin/bash |

| | 47 | test03 | NULL | 3 | 3 | NULL | NULL | / bin/bash |

+-+ +

3 rows in set (0.00 sec)

Second, use commands provided by third-party software to make incremental backups.

Percona

Install the package

Yum-y install perl-DBD-mysql

Yum-y install perl-Digest-MD5

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

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

Command format

# innobackupex

-- use user name

-- password password

-- database backup database name

-- no-timestamp subdirectories stored in backup files without date naming

-- apply-log recovery log redoes committed transactions and rolls back uncommitted transactions

-- copy-back restores the data directory backed up to the database server

Backup folder must be empty

Required / var/lib/mysql/ is empty

Xtrabackup can only back up tables from innodb and xtradb engines, but not from myisam engines.

Innobackupex is a Perl script that encapsulates xtrabackup and supports backing up both innodb and myisam, but requires a global read lock when backing up myisam. Also, myisam does not support incremental backups.

Support for transactions and transaction rollback

Ls / var/lib/mysql

Transaction log file

Ibdata1

LSN log serial number

Ib_logfile0 record sql command

Ib_logfile1

Backup process

Start xtrabackup_log

Copy .ibd; ibdata1

FLUSH TABLES WITH READ LOCK

Copy. FRM; MYD;MYI;misc files

Get binary log position

UNLOCK TABLES

Stop and copy xtrabackup_log

At the beginning of the backup, a background detection process is started to detect changes in mysql redo in real time. As soon as new log writes are found in redo, logs are immediately recorded in the background log file xtrabackup_log. After that, copy the data file of innodb and the system tablespace file ibdata1, after the copy is finished, perform the flush tableswith read lock operation, copy .frm, MYI,MYD, etc. (the purpose of executing flush tableswith read lock is to prevent the data table from DDL operation, and get the location of binlog at this time) finally issue unlock tables, make the table readable and writeable, and finally stop xtrabackup_log.

Full backup

# innobackupex-- user root-- password abc123-- database= "system Library list Storage Database" backup path name

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1" / allbak

[root@mysql4-1] # ls / allbak/2017-12-260001-35-30 /

[root@mysql4-1 ~] # rm-rf / allbak/

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1" / allbak-no-timestamp

[root@mysql4-1 ~] # ls / allbak/

Backup_type = full-backuped# backup type (full or incremental)

From_lsn = 0

To_lsn = 5379257#LSN log serial number range information

Last_lsn = 5379266

Compact = 0

Recover_binlog_info = 0

Xtrabackup_logfile # background log file

The binary log files currently in use by the xtrabackup_binlog_info # mysql server and the location of the binary log events up to the moment of backup.

Ibdata1# system tablespace file

Xtrabackup_info# 's various details about this backup

Backup-my.cnf # system resources used to perform backups

Restore

Restore the log first. Restore the data.

First of all, make sure / var/lib/mysql is empty

[root@mysql4-1 allbak] # rm-rf / var/lib/mysql/*

[root@mysql4-1 allbak] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-apply-log / allbak

[root@mysql4-1 allbak] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-copy-back / allbak

[root@mysql4-1 allbak] # chown-R mysql:mysql / var/lib/mysql

[root@mysql4-1 allbak] # systemctl restart mysqld

[root@mysql4-1 allbak] # cat xtrabackup_checkpoints

Backup_type = full-prepared# recovered the data

From_lsn = 0

To_lsn = 5379257

Last_lsn = 5379266

Compact = 0

Recover_binlog_info = 0

Incremental backup

-- incremental directory name incremental backup

-- incremental-basedir= directory name incremental backup specifies the directory name of the last data store

# innobackupex-- user root-- password abc123-- databases= "system Library list Storage Database"-- incremental directory name-- incremental-basedir= directory name-- no-timestamp

Have a full backup first

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1" / allbak-no-timestamp

Add some data to the database

Incremental backup / new1

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-incremental / new1-incremental-basedir=/allbak-no-timestamp

[root@mysql4-1 ~] # cat / new1/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 5379681

To_lsn = 5386256

Last_lsn = 5386265

Compact = 0

Recover_binlog_info = 0

Add some data to the database

Incremental backup / new2

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-incremental / new2-incremental-basedir=/new1-no-timestamp

[root@mysql4-1 ~] # cat / new2/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 5386256

To_lsn = 5394126

Last_lsn = 5394135

Compact = 0

Recover_binlog_info = 0

Incremental recovery

-- incremental-dir= directory name

-- redo-only merge logs

1 recover log files

Restore a full backup first

Innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-apply-log-redo-only / allbak

Innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-apply-log-redo-only / allbak-incremental-dir=/new1

Innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-apply-log-redo-only / allbak-incremental-dir=/new2

2 recover data

Innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-copy-back / allbak

3 restart the service

Concrete operation

[root@mysql4-1 ~] # rm-rf / var/lib/mysql/*

[root@mysql4-1 ~] # cat / allbak/xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0

To_lsn = 5379681

Last_lsn = 5379690

Compact = 0

Recover_binlog_info = 0

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-apply-log-redo-only / allbak

[root@mysql4-1 ~] # cat / allbak/xtrabackup_checkpoints

Backup_type = log-applied

From_lsn = 0

To_lsn = 5379681

Last_lsn = 5379690

Compact = 0

Recover_binlog_info = 0

[root@mysql4-1 ~] # cat / new1/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 5379681

To_lsn = 5386256

Last_lsn = 5386265

Compact = 0

Recover_binlog_info = 0

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-apply-log-redo-only / allbak-incremental-dir=/new1

[root@mysql4-1 ~] # cat / allbak/xtrabackup_checkpoints

Backup_type = log-applied

From_lsn = 0

To_lsn = 5386256

Last_lsn = 5386265

Compact = 0

Recover_binlog_info = 0

[root@mysql4-1 ~] # cat / new2/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 5386256

To_lsn = 5394126

Last_lsn = 5394135

Compact = 0

Recover_binlog_info = 0

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-apply-log-redo-only / allbak-incremental-dir=/new2

[root@mysql4-1 ~] # cat / allbak/xtrabackup_checkpoints

Backup_type = log-applied

From_lsn = 0

To_lsn = 5394126

Last_lsn = 5394135

Compact = 0

Recover_binlog_info = 0

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-copy-back / allbak

[root@mysql4-1 ~] # ls / var/lib/mysql

Db1 ib_logfile0 mysql xtrabackup_binlog_pos_innodb

Ib_buffer_pool ib_logfile1 performance_schema xtrabackup_info

Ibdata1 ibtmp1 sys

[root@mysql4-1 ~] # chown-R mysql:mysql / var/lib/mysql

[root@mysql4-1 ~] # systemctl restart mysqld

[root@mysql4-1] # mysql-uroot-pabc123

The information under new1 and new2 has been recorded under allbak.

[root@mysql4-1 ~] # rm-rf / new1

[root@mysql4-1 ~] # rm-rf / new2

Add some data to the database

[root@mysql4-1 ~] # cat / allbak/xtrabackup_checkpoints

Backup_type = log-applied

From_lsn = 0

To_lsn = 5394126

Last_lsn = 5394135

Compact = 0

Recover_binlog_info = 0

Performing an incremental backup new1

[root@mysql4-1] # innobackupex-user root-password abc123-databases= "performance_schema mysql sys db1"-incremental / new1-incremental-basedir=/allbak-no-timestamp

[root@mysql4-1 ~] # cat / new1/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 5394126

To_lsn = 5386173

Last_lsn = 5386182

Compact = 0

Recover_binlog_info = 0

Restore a single table using a full backup file-innobackupex

-- apply-log-- export exports table information .exp .cfg

Discard tablespace deletes tablespace .ibd

Import tablespace imports tablespaces

Full backup

Innobackupex-user root-password abc123-databases= "performance_schema mysql sys gamedb" / gamedbbak-no-timestamp

Simulated data loss

Drop table t1

1. Create a table according to the original table structure

Create table T1 (id int)

two。 Export table information from a configuration file

Innobackupex-user root-password abc123-databases= "performance_schema mysql sys gamedb"-apply-log-export / gamedbbak

3. Delete tablespace

Alter table t1 discard tablespace

4. Copy the exported table information under the corresponding database directory

Ti.ibd t1.exp t1.cfg

Cd / gamedbbak/gamedb

Cp t1. {exp,ibd,cfg} / var/lib/mysql/gamedb/

Ls / var/lib/mysql/gamedb/t1.*

5. Import tablespace

Chown mysql:mysql t1.*

Alter table t1 import tablespace

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.