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

MySQL backup and restore through LVM logical volumes (almost hot backup):

Premise:

1. The data file should be on the logical volume

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

3. Data files and transaction logs should be on the same logical volume

Steps:

1. Open a session, apply a read lock, and lock all tables

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

Mysql > FLUSH LOGS; # scrolling log

2. Save the binary log file and related location information through another terminal

[root@lamp ~] # mysql-uroot-p-e 'SHOW MASTER STATUS\ G' > / path/to/master.info

# check the location status directly without logging in to the mysql client, and save the location information to the appropriate directory

3. Create a snapshot volume

[root@lamp] # lvcreate-L SIZE-s-pr-n LV_NAME / path/to/source_lv

# create a snapshot volume,-s: specify a snapshot volume s=snapshot snapshot;-p: specify permission p=permission license

R: read permission;-n: specify snapshot name; LV_NAME: snapshot name; / path/to/source_lv: for which logical volume directory

Path to make a snapshot;-L: specifies the snapshot volume size.

4. Release lock

Mysql > UNLOCK TABLES; # release the lock

5. Mount the snapshot volume and back it up

Mount / dev/myvg/mydata-snap / mnt-o ro # Mount snapshot volume to / mnt directory, read-only mount

Cp-a. / * / backup/full-backup-2017-06-06 / # Mount and copy the data to the backup directory.-a: indicates that

Copy all attributes and contents of the file, and retain all attributes and permissions of the source file

6. Delete snapshot volume

Umount / mnt # unmount the directory where the snapshot volume is mounted after backup

Lvremove-force / dev/myvg/mydata-snap # forcibly remove snapshot volumes

Rm-rf mysql-bin.* # deletes the log files in the backup directory to save space

7. Incremental backup binary log

Mysqlbinlog-- start-datetime='2017-06-06 10 10 mysql-bin.000005 mysql-bin.000006 11 mysql-bin.000005 mysql-bin.000006 > / backup/incremental- `log +% Fmure% Hmure% Mmurf% S`.sql # back up binary logs if the binary logs are backed up incrementally

Contains 2 or more log files and needs to be backed up by specifying a start time.

Example: through practical operation, lvm logical volume snapshot backup and recovery mysql operation is realized.

First open the mysql client:

[root@lamp ~] # mysql # Open the mysql client

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

Your MySQL connection id is 11

Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2012, 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 > FLUSH TABLES WITH READ LOCK; # first refresh the table and impose a read lock

Query OK, 0 rows affected (0.00 sec)

Mysql > FLUSH LOGS; # scrolling log

Query OK, 0 rows affected (0.06 sec)

Mysql > SHOW MASTER STATUS; # View binary log location status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000005 | 107 |

+-+

1 row in set (0.00 sec)

Do not exit or close the mysql client, reopen a server terminal and save the location information

[root@lamp ~] # mkdir / backup # create a backup directory

[root@lamp ~] # mysql-e 'SHOW MASTER STATUS\ date' > / backup/master- `log +% F`.info # do not log in to the mysql client, edit the database directly through mysql-e, and save the location status of viewing binary logs to the backup directory

[root@lamp ~] # ls / backup/

Master-2017-06-06.info

Perform a snapshot of the mysql data directory (so backing up the database through a logical volume requires that the data files are stored on the logical volume)

[root@lamp ~] # lvcreate-L 50m-s-p r-n mydata-snap / dev/myvg/mydata # take a snapshot of the / dev/myvg/mydata logical volume with a size of 50m, which means snapshot snapshot, and-p: specify permission permission

R: read permission read,-n: specifies the name of the snapshot volume.

Rounding up size to full physical extent 52.00 MiB

Logical volume "mydata-snap" created

[root@lamp ~] # lvs # View the logical volume, the newly created snapshot logical volume

LV VG Attr LSize Origin Snap% Move Log Copy% Convert

Mydata myvg owi-ao 10.00g

Mydata-snap myvg sri-a- 52.00m mydata 0.02

[root@lamp ~] # mount # View mount related information

/ dev/sda7 on / type ext4 (rw)

Proc on / proc type proc (rw)

Sysfs on / sys type sysfs (rw)

Devpts on / dev/pts type devpts (rw,gid=5,mode=620)

Tmpfs on / dev/shm type tmpfs (rw,rootcontext= "system_u:object_r:tmpfs_t:s0")

/ dev/sda1 on / boot type ext4 (rw)

/ dev/sda3 on / home type ext4 (rw)

/ dev/sda5 on / tmp type ext4 (rw)

/ dev/sda2 on / usr/local type ext4 (rw)

/ dev/mapper/myvg-mydata on / mydata type ext4 (rw)

None on / proc/sys/fs/binfmt_misc type binfmt_misc (rw)

Sunrpc on / var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)

After performing the snapshot, you can unlock the table

Mysql > UNLOCK TABLES; # unlock

Query OK, 0 rows affected (0.00 sec)

Mount and back up the snapshot

[root@lamp ~] # mount / dev/myvg/mydata-snap / mnt-o ro # Mount the newly made snapshot volume read-only

[root@lamp ~] # cd / mnt # enter the mount directory

[root@lamp mnt] # ls

Data lost+found

[root@lamp mnt] # cd data/

[root@lamp data] # ls

Hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu

Ib_logfile0 lamp.err mysql mysql-bin.000003 mysql-bin.index test

[root@lamp data] # mkdir / backup/full-backup- `date +% F` # create a backup directory and name it in a time format

[root@lamp data] # cp-a. / * / backup/full-backup-2017-06-06 / #-a: copy all the contents of the current directory and its permission attributes to the backup directory

[root@lamp data] # cd

[root@lamp ~] # umount / mnt # Unmount the mounted directory / mnt

[root@lamp ~] # lvremove-- force / dev/myvg/mydata-snap # remove the snapshot volume you just created-force is mandatory

Logical volume "mydata-snap" successfully removed

[root@lamp] # cd / backup/full-backup-2017-06-06 /

[root@lamp full-backup-2017-06-06] # ls

Hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu

Ib_logfile0 lamp.err mysql mysql-bin.000003 mysql-bin.index test

[root@lamp full-backup-2017-06-06] # rm-rf mysql-bin.* # delete related binary log files to save space

[root@lamp full-backup-2017-06-06] # ls

Hellodb ib_logfile0 jiaowu lamp.pid mysql stu testdb

Ibdata1 ib_logfile1 lamp.err mydb performance_schema test

Root@lamp ~] # cd / mydata/data

[root@lamp data] # ls

Hellodb ib_logfile1 lamp.pid mysql-bin.000001 mysql-bin.000004 performance_schema testdb ibdata1 jiaowu mydb mysql-bin.000002 mysql-bin.000005 stu

Ib_logfile0 lamp.err mysql mysql-bin.000003 mysql-bin.index test

[root@lamp data] # cat / backup/master-2017-06-06.info

* * 1. Row *

File: binary log file recorded by mysql-bin.000005 #

Position: 107 # location of the binary log

Binlog_Do_DB:

Binlog_Ignore_DB:

Mysql > FLUSH LOGS; # scrolling log

Query OK, 0 rows affected (0.01 sec)

Mysql > USE jiaowu

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > INSERT INTO tutors (Tname) VALUES ('stu0003'); # insert data into table tutors, field Tname value stu0003

Query OK, 1 row affected (0.00 sec)

Mysql > INSERT INTO tutors (Tname) VALUES ('stu0004'); # insert data into table tutors, field Tname value stu0004

Query OK, 1 row affected (0.00 sec)

Mysql > SHOW MASTER STATUS; # View the location status information of the binary log at this time

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000006 | 575 |

+-+

1 row in set (0.00 sec)

Mysql >\ Q

Bye

[root@lamp data] # mysqlbinlog-- start-datetime='2017-06-06 10 start-datetime='2017 11 start-datetime='2017 02' mysql-bin.000005 mysql-bin.000006 > / backup/incremental- `date +% Fmure% Hmure% Mashi% S`.sql # Export of binary log files bin.000005 and bin.000006 after 10:11:02 in 2017-06-06

[root@lamp data] # ls / backup/incremental-2017-06-06-17-01-41.sql

/ backup/incremental-2017-06-06-17-01-41.sql

[root@lamp data] # service mysqld stop # stop the mysqld process

Shutting down MySQL.. [OK]

[root@lamp data] # rm-rf. / * # simulate the loss of data directory content in mysql database (manually deleted)

All the contents in the [root@lamp data] # ls # directory are deleted, and the view is empty.

[root@lamp data] # cp-a / backup/full-backup-2017-06-06 *. / # copy the contents backed up by the snapshot volume to the current data directory,-a: copy the contents and permission attributes of the file.

[root@lamp data] # ll # ensure that all files are owned by mysql users

Total 28712

Drwx-. 2 mysql mysql 4096 Jun 2 15:30 hellodb

-rw-rw----. 1 mysql mysql 18874368 Jun 5 14:00 ibdata1

-rw-rw----. 1 mysql mysql 5242880 Jun 5 14:00 ib_logfile0

-rw-rw----. 1 mysql mysql 5242880 Jun 2 15:28 ib_logfile1

Drwx-. 2 mysql mysql 4096 Jun 5 14:00 jiaowu

-rw-rw----. 1 mysql root 1853 Jun 2 15:28 lamp.err

-rw-rw----. 1 mysql mysql 6 Jun 2 15:28 lamp.pid

Drwx-. 2 mysql mysql 4096 Jun 2 15:30 mydb

Drwx-. 2 mysql root 4096 Jun 2 15:30 mysql

Drwx-. 2 mysql mysql 4096 Jun 2 15:28 performance_schema

Drwx-. 2 mysql mysql 4096 Jun 2 15:30 stu

Drwx-. 2 mysql root 4096 Jun 2 15:28 test

Drwx-. 2 mysql mysql 4096 Jun 2 15:30 testdb

[root@lamp data] # service mysqld start # start the mysqld process

Starting MySQL [OK]

[root@lamp data] # mysql-uroot-p # Log in to the mysql client

Enter password:

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

Your MySQL connection id is 3

Server version: 5.5.28-log Source distribution

Copyright (c) 2000, 2012, 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 jiaowu

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > SELECT * FROM tutors; # query the contents of the tutors table (there are no 2 rows of data inserted after this time)

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

+-+

9 rows in set (0.00 sec)

Mysql > SET sql_log_bin=0; # disable logging before importing binary logs: 0: off, 1: on

Query OK, 0 rows affected (0.00 sec)

Mysql > SOURCE / backup/incremental-2017-06-06-17-01-41.sql # Import backup binary log data

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT * FROM tutors; # queries the contents of the tutors table again, and the 2 rows of data inserted after that have been generated

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

| | 10 | stu0003 | M | NULL |

| | 11 | stu0004 | M | NULL |

+-+

11 rows in set (0.00 sec)

Mysql > SET sql_log_bin=1; # binary log import and then turn on binary logging function

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW MASTER STATUS; # View the binary log location status information at this time

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 107 |

+-+

1 row in set (0.00 sec)

So far, the backup and restore of mysql database are realized through the functions of lvm logical volume and binary cp.

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.