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

Outline

1. MySQL backup type

Second, what does MySQL backup back up?

3. MySQL backup tool

IV. MySQL backup strategy

V. preparation for backup

VI. Specific demonstration of backup strategy

Note: the system version CentOS6.4 X8634 MySQL version MySQL 5.5.32, download the related software http://yunpan.cn/QnymShsCMzGg9

1. MySQL backup type

1. Hot backup, warm backup, cold backup (based on server status)

Hot backup: read and write are not affected

Warm backup: only read operations can be performed

Cold backup: offline backup; read and write operations are aborted

two。 Physical backup and logical backup (divided by objects)

Physical backup: copying data files

Logical backup: exporting data to a text file

3. Full backup, incremental backup, differential backup (based on data collection)

Full backup: backing up all data

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

Differential backup: only data that has changed since the last full backup is backed up

4. Advantages of logical backup:

In terms of backup speed, the two types of backup depend on different storage engines.

The restore speed of physical backups is very fast. But the minimum strength of physical backup can only be as strong as the table.

The structure saved by logical backups is usually pure ASCII, so we can use text processing tools to deal with

Logical backups are very compatible, while physical backups require very high versions.

Logical backup also ensures the security of data.

5. Disadvantages of logical backup:

Logical backups put additional pressure on RDBMS, while bare backups have no pressure

The result of a logical backup may be larger than the source file. So many people compress the contents of the backup.

Logical backups may lose precision information for floating-point numbers

6. The difference between incremental backup and differential backup

It shows that differential backup takes more space than incremental backup, but it is more convenient to restore! But we usually use incremental backup!

Second, what does MySQL backup back up?

We backup, generally back up the following parts:

1. Data file

two。 Log files (such as transaction logs, binary logs)

3. Stored procedure, stored function, trigger

4. Configuration files (it is very important that each configuration file is backed up)

5. Scripts for database backup, Croutab for database self-cleaning, etc.

3. MySQL backup tool

The figure below is as follows

Comparing all the backup tools above, let's talk about the commonly used backup tools.

Backup tools that come with 1.Mysql

Mysqldump logical backup tool, supports all engines, MyISAM engine is warm backup, InnoDB engine is hot backup, backup speed is medium speed, restore speed is very slow, but in the implementation of restore, there is a lot of room for operation. It has good elasticity.

Mysqlhotcopy physical backup tool, but only supports MyISAM engine, basically belongs to the category of cold backup, physical backup, faster.

two。 File system backup tool

Cp cold backup, support all engines, copy commands, can only achieve cold backup, physical backup. Using archiving tool, cp command, the backup speed is fast, the restore speed is almost the fastest, but the flexibility is very low, can cross-system, but the cross-platform ability is very poor.

Lvm is almost hot backup, supports all engines, snapshot (LVM,ZFS)-based physical backup, very fast, almost hot backup. It only affects the data for a few seconds. However, the process of creating a snapshot itself affects the online use of the database, so the backup speed is faster, the recovery speed is faster, there is no flexible space, and the limitation of LVM: multiple logical volumes cannot be backed up at the same time, so various files such as data files and transaction logs must be placed on the same LVM. On the other hand, ZFS is very good at backing up between multiple logical volumes.

3. Other tools

Ibbackup business tool MyISAM is warm backup, InnoDB is hot backup, backup and restore speed is very fast, this software, its licensed version per server is $5000.

Xtrabackup open source tool MyISAM is a warm backup, InnoDB is a hot backup, is an alternative to ibbackup commercial tools.

IV. MySQL backup strategy

1. Strategy 1: copy database files directly (file system backup tool cp) (suitable for small databases and is the most reliable)

When you use the direct backup method, you must make sure that the table is no longer in use. If the server changes a table while you are copying it, the copy is meaningless. The best way to ensure the integrity of your copy is to shut down the server, copy the files, and then restart the server. If you do not want to shut down the server, lock the server while performing a table check. If the server is running, the same restrictions apply to copying files, and you should use the same locking protocol to "calm down" the server. When you finish the backup, you need to restart the server (if you shut it down) or release the locks added to the table (if you let the server run). To copy a database from one machine to another with a direct copy file, simply copy the file to the appropriate data directory on another server host. Make sure the files are in MyIASM format or that both machines have the same hardware structure, otherwise your database has strange content on another host. You should also make sure that the server on another machine does not access database tables while you are installing them.

two。 Strategy 2: mysqldump backup database (full backup + additional backup, relatively slow, suitable for small and medium-sized databases) (MyISAM is warm backup, InnoDB is hot backup)

Mysqldump uses a SQL-level backup mechanism, which exports data tables into SQL script files, which is relatively appropriate when upgrading between different MySQL versions, which is also the most commonly used backup method. Mysqldump is slower than a direct copy. For medium-volume systems, the backup strategy can be set as follows: the first full backup, an incremental backup every day, and a full backup once a week, and so on. For important and busy systems, full backups may be required once a day, incremental backups per hour, or even more frequently. In order not to affect the online business, achieve online backup, and can backup incrementally, the best way is to use the master-slave replication mechanism (replication) to do backup on the slave machine.

3. Strategy 3: lvs snapshots to achieve almost hot standby full backup from a physical point of view, with binary log backup to achieve incremental backup, fast suitable for more busy databases

Premise:

The data file should be on the logical volume

The volume group to which this logical volume belongs must have enough space to use snapshot volumes

The data file and transaction log should be on the same logical volume

Steps:

(1)。 Open a session, impose a read lock, and lock all tables

one

two

Mysql > FLUSH TABLES WITH READ LOCK

Mysql > FLUSH LOGS

(2)。 Save the binary log file and related location information through another terminal

one

Mysql-uroot-p-e 'SHOW MASTER STATUS\ G' > / path/to/master.info

(3)。 Create a snapshot volum

one

Lvcreate-L #-s-p r-n LV_NAME / path/to/source_lv

(4)。 Release lock

one

Mysql > UNLOCK TABLES

(5)。 Mount snapshot volumes, backup

one

two

Mount

Cp

(6)。 Delete snapshot volum

(7)。 Incremental backup binary log

4. Strategy 4: xtrabackup backup the database to achieve full hot backup and incremental hot backup (MyISAM is warm backup, InnoDB is hot backup). Because some data are not stored on LVM at the beginning of the design, LVM can not be used as backup, so xtrabackup is used to back up the database instead.

Description: Xtrabackup is a data backup tool for InnoDB, supports online hot backup (backup does not affect data read and write), and is a good substitute for commercial backup tool InnoDB Hotbackup or ibbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex

Xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables.

Innobackupex is modified with reference to InnoDB Hotbackup's innoback script. Innobackupex is a perl script wrapper that encapsulates xtrabackup. It is mainly for the convenience of backing up the tables of the InnoDB and MyISAM engines at the same time, but you need to add a read lock when processing myisam. And added some options to use. For example, slave-info can record some information needed by slave after backup and recovery. According to this information, it is convenient to use backup to redo slave.

Features:

The backup process is fast and reliable

The backup process does not interrupt the transaction that is in progress

Ability to save disk space and traffic based on functions such as compression

Automatic backup verification

Fast reduction speed

5. Strategy 5: master-slave replication (replication) to achieve real-time database backup (commonly used in clusters)

6. Summary

Stand-alone backup is a combination of full backup (all database files) and incremental backup (backup binary log)!

Backup in a cluster is a combination of full backup (all database files) + incremental backup (backup binary logs) + master-slave replication (replication)!

V. preparation for backup

1. View server status

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

Mysql >\ s

-

Mysql Ver 14.14 Distrib 5.5.32, for Linux (x86 / 64) using readline 5.1

Connection id: 1

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile:''

Using delimiter:

Server version: 5.5.32-log Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. Characterset: utf8

UNIX socket: / tmp/mysql.sock

Uptime: 2 min 0 sec

Threads: 1 Questions: 4 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.033

two。 View the location of the data directory

one

two

three

four

five

six

seven

Mysql > show variables like'% datadir%'

+-+ +

| | Variable_name | Value |

+-+ +

| | datadir | / mydata/data/ |

+-+ +

1 row in set (0.01 sec)

3. Modify the location of the binary log

(1)。 Set up a directory to store binary logs

one

two

three

four

[root@mysql ~] # mkdir / mybinlog

[root@mysql ~] # chown mysql:mysql / mybinlog

[root@mysql /] # ll | grep mybinlog

Drwxr-xr-x 2 mysql mysql 4096 July 22 14:39 mybinlog

(2)。 Modify my.cnf

one

two

three

[root@mysql ~] # vim / etc/my.cnf

Log-bin=/mybinlog/mysql-bin # binary log directory and filename prefix

Innodb_file_per_table = 1 # enable InnoDB table one file per table. By default, all libraries use one tablespace.

(3)。 Restart mysqld

one

[root@mysql ~] # service mysqld restart

4. View the newly generated binlog log

one

two

[root@mysql ~] # ls / mybinlog/

Mysql-bin.000001 mysql-bin.index

5. Prepare a test library with two tables, T1 table and T2 table!

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

Mysql > show table status from test\ G # View the status of the two tables

* * 1. Row *

Name: t1

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 167772160

Avg_row_length: 7

Data_length: 1174405120

Max_data_length: 1970324836974591

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2013-07-21 19:37:44

Update_time: 2013-07-21 19:52:48

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

* 2. Row * *

Name: t2

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 20971797

Avg_row_length: 31

Data_length: 667942912

Max_data_length: 0

Index_length: 0

Data_free: 4194304

Auto_increment: NULL

Create_time: 2013-07-21 20:00:29

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

2 rows in set (0.01sec)

The first T1 table, using the MyISAM engine, has more than 100 million rows of data, and the second T2 table uses the INNODB engine, which has more than 20 million rows of data! A Boyou will ask, how can you have so much data when you are doing a test? let's talk about the method of quickly inserting 100 million pieces of data! The specific operations are as follows

T1 table

one

two

three

four

five

six

seven

eight

Mysql > use test; # using the test database

Mysql > create table T1 (# create a simple T1 table with only one field id

Id int (10) default null

) engine=myisam default charset=utf8

Mysql > show create table T1; # View the created table

Mysql > insert into T1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); # insert ten data first

Mysql > insert into T1 select * from T1; # 100 million pieces of data can be inserted by repeating multiple times

Mysql > select count (*) from T1; # View the total number of inserted data

T2 table

one

two

three

four

five

six

seven

Mysql > create table T2 (

Id int (10) default null

) engine=innodb default charset=utf8

Mysql > show create table T2

Mysql > insert into T2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

Mysql > insert into T2 select * from T2

Mysql > select count (*) from T2

All right, let's talk about backup and restore in detail!

VI. Specific demonstration of backup strategy

1. Strategy 1: copy database files directly (file system backup tool cp) (suitable for small databases)

(1)。 Standard procedure: lock table-> refresh table to disk-> copy file-> unlock (Note, if possible, you can stop the database first and then prepare it with the cp command, so that the backed up data is the most reliable.)

(2)。 Specific steps:

a. Open the first terminal

one

two

three

[root@mysql data] # mysql

Mysql > FLUSH TABLES WITH READ LOCK; # refresh the table and read the lock on disk

Query OK, 0 rows affected (0.00 sec)

b. Open the second terminal

one

two

[root@mysql data] # mkdir / root/ alldb.`date +% FMI% HMI% MMI% S` / # create a backup directory

[root@mysql data] # cp-rp / mydata/data/* / root/alldb.2013-07-22-13-46-22 / # copy all database files

c. Unlock at the first terminal

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

Mysql > UNLOCK TABLES; # unlock

Query OK, 0 rows affected (0.01 sec)

[root@mysql] # ll alldb.2013-07-22-13-46-22 / # View the backed-up database

The total dosage is 267468

-rw-rw---- 1 mysql mysql 262221824 July 21 20:17 ibdata1

-rw-rw---- 1 mysql mysql 5242880 July 22 13:40 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 July 22 13:40 ib_logfile1

Drwx- 2 mysql mysql 4096 July 20 12:33 mysql

-rw-rw---- 1 mysql mysql 27698 July 20 12:33 mysql-bin.000001

-rw-rw---- 1 mysql mysql 190 July 22 13:40 mysql-bin.index

-rw-rw---- 1 mysql mysql 1925 July 21 13:07 mysql-slow.log

-rw-r- 1 mysql mysql 21906 July 22 13:40 mysql.test.com.err

-rw-rw---- 1 mysql mysql 5 July 22 13:40 mysql.test.com.pid

Drwx- 2 mysql mysql 4096 July 20 12:33 performance_schema

Drwx- 2 mysql mysql 4096 July 21 20:00 test

(3)。 Simulate database corruption

Delete all files in the data directory directly

one

two

three

four

[root@mysql data] # rm-rf *

[root@mysql data] # ll

Total dosage 0

[root@mysql data] #

(4)。 Specific restore steps

A.mysql can't stop at this time.

one

two

[root@mysql mydata] # service mysqld stop

ERROR! MySQL server PID file could not be found!

b. Find all processes in mysql

one

two

three

[root@mysql mydata] # ps aux | grep mysqld

Root 2728 0.00.1 11300 1520 pts/1 S 15:01 0:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/mydata/data-- pid-file=/mydata/data/mysql.test.com.pid

Mysql 3029 0.1 9.1 773908 92312 pts/1 Sl 15:01 0:00 / usr/local/mysql/bin/mysqld-- basedir=/usr/local/mysql-- datadir=/mydata/data-- plugin-dir=/usr/local/mysql/lib/plugin-- user=mysql-- log-error=/mydata/data/mysql.test.com.err-- pid-file=/mydata/data/mysql.test.com.pid-- socket=/tmp/mysql.sock-- port=3306

c. Kill all processes of mysql

one

[root@mysql ~] # killall mysqld

d. Initialize mysql

one

[root@mysql] # / usr/local/mysql/scripts/mysql_install_db-basedir=/usr/local/mysql/-datadir=/mydata/data/-user=mysql

e. Copy the fully backed up data files to the data directory

one

two

[root@mysql test] # alias cp=cp # modify the cp alias, otherwise you will always be reminded whether to overwrite it when copying.

[root@mysql test] # cp-pr / root/alldb.2013-07-22-13-46-22 * / mydata/data/ # copy the fully backed up files to the data directory

f. Start the mysql database

one

two

[root@mysql test] # service mysqld start

Starting MySQL SUCCESS!

g. Test and view the data

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

[root@mysql test] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > use test

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

| | T2 |

+-+

2 rows in set (0.01sec)

Mysql > select count (*) from T1

+-+

| | count (*) |

+-+

| | 167772160 |

+-+

1 row in set (0.01 sec)

Mysql > select count (*) from T2

+-+

| | count (*) |

+-+

| | 20971520 |

+-+

1 row in set (9.95 sec)

You can see that all the data have been restored, hey!

(5)。 Summary

Cp command, the backup speed is fast, the restore speed is almost the fastest, but the flexibility is very low, can cross-system, but the cross-platform ability is very poor, suitable for small database backup!

two。 Strategy 2: mysqldump backup database (full backup + additional backup, relatively slow, suitable for small and medium-sized databases) (MyISAM is warm backup, InnoDB is hot backup)

(1) detailed explanation of the .mysqldump command

one

two

three

four

five

six

seven

eight

Mysqldump-all-databases-lock-all-tables-routines-triggers-events-master-data=2-flush-logs > / root/mybackup/2013-07-22-16-20.full.sql

-- all-tables # backup all libraries

-- lock-all-tables # adds read locks to all tables

Routinge # stored procedures and functions

-- triggers # trigger

-- events # logs events

-- master-data=2 # records the location of the current binary log in the backup file and is annotated. 1 is meaningful only if it is not commented out in the master-slave copy.

-- flush-logs # log scrolls once

(2)。 The specific backup process is as follows

a. View the binlog log before backup

one

two

three

four

five

six

seven

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000022 | 107 |

+-+

1 row in set (0.01 sec)

b. Back up all libraries (full backup)

one

[root@mysql mybackup] # mysqldump-all-databases-- lock-all-tables-- routines-- triggers-- events-- master-data=2-- flush-logs > / root/mybackup/2013-07-22-16-20.full.sql

c. Check whether the backup is successful

one

two

three

[root@mysql mybackup] # ll-h

Total consumption 739m

-rw-r--r-- 1 root root 739m July 22 16:31 2013-07-22-16-20.full.sql

d. View the newly generated binlog log

one

two

three

four

five

six

seven

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000023 | 107 |

+-+

1 row in set (0.01 sec)

e. Insert a few new pieces of data

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

Mysql > use test

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

| | T2 |

+-+

2 rows in set (0.00 sec)

Mysql > select count (*) from T1

+-+

| | count (*) |

+-+

| | 167772160 |

+-+

1 row in set (0.01 sec)

Mysql > insert into T1 values (167772164), (167772165), (167772166)

f. Check the binlog log again

one

two

three

four

five

six

seven

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000023 | 363 |

+-+

1 row in set (0.01 sec)

g. Backup binary log (incremental backup)

one

[root@mysql mybackup] # cp / mybinlog/mysql-bin.000023 / root/mybackup/2013-07-22-16-20.binlog.full.000001

h. View the binary log of backup

one

two

three

four

[root@mysql mybackup] # ll

The total dosage is 756264

-rw-r- 1 root root 363 July 22 16:34 2013-07-22-16-20.binlog.full.000001

-rw-r--r-- 1 root root 774402118 July 22 16:31 2013-07-22-16-20.full.sql

i. Simulate database corruption

one

two

three

four

five

six

seven

eight

nine

[root@mysql mybackup] # cd / mydata/data/

[root@mysql data] # ls

Ibdata1 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql.test.com.err

Ib_logfile0 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql.test.com.pid

Ib_logfile1 mysql-bin.000003 mysql-bin.000007 mysql-bin.index performance_schema

Mysql mysql-bin.000004 mysql-bin.000008 mysql-slow.log test

[root@mysql data] # rm-rf * # Delete all data

[root@mysql data] # ll

Total dosage 0

(3)。 The specific reduction process is as follows

a. Find the mysql process

one

two

three

four

five

[root@mysql data] # ps-aux | grep mysqld

Warning: bad syntax, perhaps a bogus'-'? See / usr/share/doc/procps-3.2.8/FAQ

Root 3599 0.00.1 11304 1340 pts/1 S 15:18 0:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/mydata/data-- pid-file=/mydata/data/mysql.test.com.pid

Mysql 3901 4.7 21.5 1167384 218684 pts/1 Sl 15:18 3:49 / usr/local/mysql/bin/mysqld-basedir=/usr/local/mysql-datadir=/mydata/data-plugin-dir=/usr/local/mysql/lib/plugin-user=mysql-log-error=/mydata/data/mysql.test.com.err-pid-file=/mydata/data/mysql.test.com.pid-socket=/tmp/mysql.sock-port=3306

Root 4469 0.0 103244 876 pts/1 S+ 16:38 0:00 grep mysqld

b. Kill all processes

one

[root@mysql data] # killall mysqld

c. Initialize mysql and start mysql

one

[root@mysql data] # / usr/local/mysql/scripts/mysql_install_db-basedir=/usr/local/mysql/-datadir=/mydata/data/-user=mysql

d. Because we are not newly initialized, there may be binary logs that report errors, all of which we delete here

one

[root@mysql data] # rm-rf / mybinlog/*

e. Start the mysql database, and the new binary log will be regenerated at startup

one

[root@mysql ~] # service mysqld start

f. To restore to the backup state, turn off binary logging of the recovery process before backup, because it is meaningless to record recovery statements

one

two

Mysql > set global sql_log_bin=0

Mysq > source / root/mybackup/2013-07-22-16-20.full.sql

g. Open another terminal to query data

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

Mysql > select count (*) from T1

+-+

| | count (*) |

+-+

| | 167772163 |

+-+

1 row in set (1 min 29.63 sec) # you can see that backing up data with mysqldump takes about 30 seconds to restore the myisam engine (a total of more than 100 million pieces of data, the speed is not very fast)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

| | T2 |

+-+

2 rows in set (0.00 sec)

Mysql > select count (*) from T2

+-+

| | count (*) |

+-+

| | 20971520 |

+-+

1 row in set (46.14 sec) # restore INNODB engine for about 50 seconds (more than 20 million pieces of data)

h. View the last ten pieces of data

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

Mysql > select * from T1 order by id desc limit 10

+-+

| | id |

+-+

| | 167772163 |

| | 167772162 |

| | 167772161 |

| | 10 |

| | 10 |

| | 10 |

| | 10 |

| | 10 |

| | 10 |

| | 10 |

+-+

10 rows in set (0.00 sec)

As you can see, we have been restored to the state of the full backup, but the last three pieces of data we inserted have not been restored, so let's restore the three pieces of data we inserted before the database was damaged!

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

[root@mysql ~] # mysqlbinlog / root/mybackup/2013-07-22-16-20.binlog.full.000001 | mysql test

[root@mysql ~] # mysql test

Mysql > select * from T1 order by id desc limit 10

+-+

| | id |

+-+

| | 167772166 |

| | 167772165 |

| | 167772164 |

| | 167772163 |

| | 167772162 |

| | 167772161 |

| | 10 |

| | 10 |

| | 10 |

| | 10 |

+-+

10 rows in set (47.01 sec)

Mysql >

As you can see, the last three pieces of data we added have been restored!

i. Finally, open the binary record and view the recovery status

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

Mysql > set global sql_log_bin=1

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.01sec)

Mysql > use test

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

| | T2 |

+-+

2 rows in set (0.00 sec)

(4)。 Summary:

Based on mysqldump, usually we are full backup + binary log to restore!

3. Strategy 3: lvs snapshots from a physical point of view to achieve almost hot backup full backup, with binary log backup to achieve incremental backup, fast suitable for more busy databases!

Description: requires that your MySQL data directory must be on the lvm volume!

The specific steps are as follows

(1)。 Add a read lock for all tables in MySQL. Do not close the terminal, or the lock will fail. Scroll the log.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.01 sec)

Mysql > flush logs

Query OK, 0 rows affected (0.02 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 107 |

+-+

1 row in set (0.00 sec)

(2)。 Open another terminal speed to create a snapshot, and my volume group is / dev/myvg/mydata

one

[root@mysql] # lvcreate-L 2G-n mysql-snap-s-p r / dev/myvg/mydata

(3)。 Speed release read lock

one

two

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

(4)。 Mount snapshots, copy them out, unmount snapshots, delete snapshots

one

two

three

four

five

[root@mysql ~] # mount / dev/myvg/mysql-snap / mnt

[root@mysql ~] # mkdir / root/mybackup/lvm

[root@mysql ~] # cp-pR / mnt/* / root/mybackup/lvm/

[root@mysql ~] # umount / mnt

[root@mysql ~] # lvremove / dev/myvg/mysql-snap

(5)。 Just one full backup is completed. Let's test whether it can be used properly.

one

two

three

four

[root@mysql ~] # service mysqld stop

[root@mysql ~] # rm-rf / mydata/*

[root@mysql ~] # cp-Rp / root/mybackup/lvm/* / mydata/

[root@mysql ~] # service mysqld start # if you can start normally, there will be no problem.

(6)。 If MySQL fails after a full backup, like mysqldump, restore the last full backup first, then use the binary log to recover, find the binary location at the time of the full backup, export the logs from then to before the failure with mysqlbinlog, and then import them into MySQL. This is consistent with the experiment in mysqldump and will not be repeated.

(7)。 Summary

Using lvm snapshots to backup is very fast, and almost hot backup, recovery is also very fast, the operation is also simple, and then restore the corresponding binary system after a complete recovery.

4. Strategy 4: xtrabackup backup database to achieve full hot backup and incremental hot backup (MyISAM is warm backup, InnoDB is hot backup)

(1)。 Install the dependency packages required for percona-xtrabackup-2.1.3-608

one

[root@mysql ~] # yum install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.*-y

(2)。 Extract the package and type the command file directory

one

two

three

four

five

six

seven

eight

nine

ten

eleven

[root@mysql ~] # tar xf percona-xtrabackup-2.1.3-608.tar.gz

[root@mysql src] # cd percona-xtrabackup-2.1.3/bin/

[root@mysql bin] # ll

The total dosage is 112284

-rwxr-xr-x 2 root root 110738 May 23 02:50 innobackupex

Lrwxrwxrwx 2 root root 12 July 23 04:48 innobackupex-1.5.1-> innobackupex

-rwxr-xr-x 2 root root 2211237 May 23 02:50 xbcrypt

-rwxr-xr-x 2 root root 2285672 May 23 02:50 xbstream

-rwxr-xr-x 2 root root 13033745 May 23 02:50 xtrabackup

-rwxr-xr-x 2 root root 16333506 May 23 02:28 xtrabackup_55

-rwxr-xr-x 2 root root 80988093 May 23 02:40 xtrabackup_56

(3)。 Copy innobackupex, xtrabackup and other files to the mysql program directory / bin, directory

one

[root@mysql bin] # cp-pl * / usr/local/mysql/bin/

(4)。 Soft link the files in the mysql installation directory to the / usr/bin/ directory. This is more convenient than variables, so the deployment and installation is complete.

one

[root@mysql bin] # ln-sv / usr/local/mysql/bin/* / usr/bin/

(5)。 test

one

two

three

four

[root@mysql bin] # innobackupex

Innobackupex innobackupex-1.5.1

[root@mysql bin] # xtrabackup

Xtrabackup xtrabackup_55 xtrabackup_56

(6)。 View innobackupex options

one

[root@mysql] # innobackupex-- help

(7)。 Set mysql password

one

[root@mysql ~] # mysqladmin-uroot password 123456

(8)。 Full database backup

one

[root@mysql] # innobackupex-- host=localhost-- user=root-- password=123456 / root/mybackup/xtrabackup/

Report an error

one

two

Xtrabackup: Error: Please set parameter 'datadir'

Innobackupex: Error: ibbackup child process has died at / usr/bin/innobackupex line 389.

Solution method

one

two

three

[root@mysql data] # vim / etc/my.cnf

# add one line

Datadir = / mydata/data

Successful execution again

one

two

[root@mysql xtrabackup] # innobackupex-host=localhost-user=root-password=123456-defaults-file=/etc/my.cnf / root/mybackup/xtrabackup/

130723 05:29:13 innobackupex: completed OK!

(9)。 View backup files

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

[root@mysql xtrabackup] # ll

Total dosage 4

Drwxr-xr-x 5 root root 4096 July 23 05:33 2013-07-23 May 05-32-51

[root@mysql xtrabackup] # cd 2013-07-2311 05-32-51 /

[root@mysql 2013-07-230005-32-51] # ll

The total dosage is 190496

-rw-r--r-- 1 root root 260 July 23 05:32 backup-my.cnf

-rw-r- 1 root root 195035136 July 23 05:32 ibdata1

Drwxr-xr-x 2 root root 4096 July 23 05:33 mysql

Drwxr-xr-x 2 root root 4096 July 23 05:33 performance_schema

Drwx- 2 root root 4096 July 23 05:33 test

-rw-r--r-- 1 root root 13 July 23 05:33 xtrabackup_binary

-rw-r--r-- 1 root root 23 July 23 05:33 xtrabackup_binlog_info

-rw-r- 1 root root 95 July 23 05:33 xtrabackup_checkpoints

-rw-r- 1 root root 2560 July 23 05:33 xtrabackup_logfile

The data will be fully backed up to the current date in / root/mybackup/xtrabackup/, and xtrabackup will back up all InnoDB tables. The MyISAM table will only copy table structure files, as well as related files for MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration information. In addition to saving the data, some data files required by xtrabackup are generated, which are explained in detail as follows:

Xtrabackup_checkpoints backup type (such as full or incremental), backup status (such as whether it is already prepared status), and LSN (log serial number) scope information; each InnoDB page (usually 16k size) contains a log serial number, that is, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

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 the backup is made.

The executable file of xtrabackup used in the xtrabackup_binary backup.

Configuration option information used by the backup-my.cnf backup command.

Xtrabackup_logfile records standard output information xtrabackup_logfile

(10)。 Test recovery MySQL, complete recovery with xtrabackup

one

two

three

four

five

six

seven

eight

[root@mysql data] # service mysqld stop

[root@mysql data] # rm-rf / mydata/data/*

[root@mysql data] # innobackupex-- apply-log / root/mybackup/xtrabackup/2013-07-230005-48-03 /

#-the meaning of apply-log is to undo transactions that are not commit when backing up, and to apply to the database those transactions that have been commit but are still in the transaction log

[root@mysql data] # innobackupex-- copy-back / root/mybackup/xtrabackup/2013-07-230005-48-03 /

#-copy-back database recovery, followed by the location of the backup directory

[root@mysql data] # chown-R mysql:mysql / mydata/data

[root@mysql data] # service mysqld start # if it can be started, it will return to normal.

(11)。 Add some data to the table

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

Mysql > insert into T1 values (123,456,789)

# check the data

Mysql > use test

Database changed

Mysql > select * from T1 order by id desc limit 10

+-+

| | id |

+-+

| | 789 |

| | 456 |

| | 333 |

| | 222 |

| | 123 |

| | 111 |

| | 33 |

| | 22 |

| | 11 |

| | 10 |

+-+

10 rows in set (9.47 sec)

(12)。 Incremental backup

one

two

three

four

[root@mysql data] # innobackupex-- user=root-- password=123456-- incremental--incremental-basedir=/root/mybackup/xtrabackup/2013-07-238.05-48-03 / / root/mybackup/xtrabackup/

#-incremental specifies incremental backup

#-incremental-basedir specifies which full backup to base on for incremental backup, and finally the directory where the incremental backup is saved

Note: incremental backup can only do incremental backup for InnoDB engine, and full replication for MyISAM tables.

(13)。 Test incremental backup recovery

one

two

three

four

five

six

[root@mysql data] # service mysqld stop

[root@mysql data] # rm-rf / mydata/data/*

[root@mysql data] # innobackupex-apply-log-redo-only

#-redo-only refers to the data applied in the transaction log that is commit at the time of backup, but not undone if it has not been committed yet

Because this transaction may be committed in an incremental backup, if it is undone, it will not be committed in the incremental backup, because the transaction is already incomplete

# / root/mybackup/xtrabackup/2013-07-230005-48-03 / is a full backup directory

(14)。 Add all incremental backups to the full backup

one

two

three

[root@mysql data] # innobackupex-- apply-log / root/mybackup/xtrabackup/2013-07-2334 05-48-03 /-- incremental-dir=/root/mybackup/xtrabackup/2013-07-23 03 06-05-37 /

# / root/mybackup/xtrabackup/2013-07-230005-48-03 / this is the directory of the full backup

#-incremental-dir is followed by the directory of incremental backup

Note: this will merge the data from the incremental backup into the full backup. If there is an incremental backup, continue to merge and restore the full backup during recovery.

(15) Recover data and start MySQL

one

two

three

[root@mysql data] # innobackupex-- copy-back / root/mybackup/xtrabackup/2013-07-230005-48-03 /

[root@mysql data] # chown-R mysql:mysql / mydata/data/

[root@mysql data] # service mysqld start

(16)。 Check to see if the data is missing

one

two

3

four

five

six

seven

eight

9

ten

eleven

twelve

thirteen

fourteen

15

sixteen

seventeen

eighteen

nineteen

[root@mysql data] # mysql-uroot-p123456 test

Mysql > use test

Database changed

Mysql > select * from T1 order by id desc limit 10

+-+

| | id |

+-+

| | 789 |

| | 456 |

| | 333 |

| | 222 |

| | 123 |

| | 111 |

| | 33 |

| | 22 |

| | 11 |

| | 10 |

+-+

10 rows in set (9.47 sec)

All data is restored!

17. Summary

If the database fails after an incremental backup, we need to restore it through a full backup + all incremental backups so far + the last incremental backup to the current binary log.

18. Notes

Separate backup:

1

Innobackupex-user=root-password=123456-defaults-file=/etc/my.cnf-database=test / root/mybackup

Back up and package compression:

one

Innobackupex-user=root-password=123456-defaults-file=/etc/my.cnf-database=test-stream=tar / root/mybackup/ | gzip > / root/mybackup/testdb.tar.gz

With a timestamp:

one

Innobackupex-- user=root-- password=123456-- defaults-file=/etc/my.cnf-- database=test-- stream=tar / root/mybackup/ | gzip > / root/mybackup/ `date +% F` _ testdb.tar.gz

The backup information output is redirected to a file:

one

Innobackupex-- user=root-- password=123456-- defaults-file=/etc/my.cnf-- database=test-- stream=tar / root/mybackup/ 2 > / root/mybackup/test.log | gzip 1 > / root/mybackup/test.tar.gz

Description:

one

two

three

four

five

six

seven

-- stream # specifies the format of the stream. Currently, only tar is supported

-- database=test # backs up the test database separately. If you don't add this parameter, you will do it for the whole database.

2 > / root/mybackup/test.log # output information is written to the log

1 > / root/mybackup/test.tar.gz # is packaged and compressed and stored in this file

Decompress tar-izxvf to add-I parameter, official interpretation innobackupex: You must use-I (--ignore-zeros) option for extraction of the tar stream.

After the backup is complete, the data cannot be used for the restore operation because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file.

At this time, the data file is still in an inconsistent state. The main role of "preparation" is to make the data file consistent by rolling back uncommitted transactions and synchronizing committed transactions to the data file.

5. Strategy 5: master-slave replication (replication) to achieve real-time database backup (commonly used in clusters)

Note, MySQL master-slave copy will write a separate blog to explain, that's all for today, hey! ^ _ ^.

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.