Backing up and recovering MySQL databases using LVM

The command used for the query in the case column:

Show engines; What storage engine is---now available

Show variables like '%storage_engine% '; ----- The current default storage engine

Set InnoDB is the default engine: add a default-storage-engine=innodb sentence under [mysqld] in profile my.cnf , or you can add innodb_file_per_table=1 # let each table file of InnoDB be stored separately

Show master status; -- View node status

Show Binlog events in ' mysql-bin.000001 '; Execute MySQL binary event query --- query

Set sql_log_bin=0; -- turn off mysql binary sync 0 to turn off 1 to open

SELECT @ @sql_log_bin; --- query binary query open state

mysql> \. /root/zzxtbl.sql importing Zzxtbl.sql scripts ---The current MySQL session

SELECT * into outfile '/opt/zzxtbl.txt ' form zzxtbl; ---- Query the ZZXTBL table and Export to /opt/zzxtbl.txt

CREATE table zzxtbl like Zztbls;

Desc ZZXTBL, Zzxtbl; -------desc Display table structure

drop table zzxtbl; ------ Delete a table

TRUNCATE TABLE zzxtbl; ------ Clear Table Data

mysqlbinlog/data/mysql-bin.000001---- Viewing The contents of a MySQL binary file

Mysqlbinlog--start-positsion=100/mysql-bin.000001 >/root/zzxtbl.sql--- view the number of nodes is , and export the SQL Script

Mysql-e ' Show Master Status\g ' >/opt/master-' data+%f '. Info---- View the binary files that saved the backup

Show variables like '%dir% '; ---- check mysql directory for storage

first, MySQL uses select INTO outfile the table file in the database into text (. txt) and then restores the database content

Grammar : select * into outfile '/tmp/zzx.txt ' form zzx;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/37/wKioL1Xeyf-SN2ljAABPsnrzZhw178.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1xeyf-sn2ljaabpsnrzzhw178.jpg "/>

The structure of the exported data displayed

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/3B/wKiom1Xex-aAf7AzAABF3qeydks242.jpg "style=" float: none; "title=" 2.png "alt=" Wkiom1xex-aaf7azaabf3qeydks242.jpg "/>

The procedure is as follows:

(1) after the table structure of table A is copied into B , the data structure dsca\b in the two tables are viewed ;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/37/wKioL1Xeyf_iXXUCAAFZNRRtcN8454.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1xeyf_ixxucaafznrrtcn8454.jpg "/>

(2) After removing a, use loaddata infile '/tmp/zzx.txt ' into table B to recover the data in a table.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/3B/wKiom1Xex-fhI4ZuAABZVsQWo-U020.jpg "style=" float: none; "title=" 4.png "alt=" Wkiom1xex-fhi4zuaabzvsqwo-u020.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/37/wKioL1XeygDwuHKnAADlAQFFzCw034.jpg "style=" float: none; "title=" 5.png "alt=" Wkiol1xeygdwuhknaadlaqffzcw034.jpg "/>

second,MySQL uses binary files to recover the database

Here you can see the show Master Status andmaster node information based on the Binlog log Check method on your MySQL server . Then in view of that node show Binlog events in ' mysql-bin.000001 ';

Binlog logs run as SQL scripts based on the number of nodes started

mysqlbinlog--start-position= "319"/var/lib/mysql/mysql-bin.000005>/root/zzx.sql

1. Clear the table data (TRUNCATE table zzxs;)

2. Turn off binary file synchronization (setsql_log_bin=0; ), turn on the MySQL binary switch (set_log_bin)after successful recovery.

3.mysql Server on-line

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/3B/wKiom1Xex-eAXreFAAEH84pC-Nw268.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1xex-eaxrefaaeh84pc-nw268.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/37/wKioL1XeygDAYth4AAD9PBhJOtc775.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1xeygdayth4aad9pbhjotc775.jpg "/>

the recovery was successful.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/37/wKioL1XeygDRdpHtAADfNrH9aSA723.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1xeygdrdphtaadfnrh9asa723.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/3B/wKiom1Xex_3Dmpr4AABej7gq7Lk712.jpg "style=" float: none; "title=" 9.png "alt=" Wkiom1xex_3dmpr4aabej7gq7lk712.jpg "/>

Iii. using LVM to back up mysql database online

Note: put the MySQL data Catalog in advance on the logical volume I created. There is enough space on this volume group to be used for backup, both in this MySQL database and in the Binlog binaries on the volume group.

Steps:

1. in the current session of MySQL, to lock all the current tables;

Mysql> flush tables with read lock;

mysql> flush logs;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/3B/wKiom1Xex_3ykIGTAADu77DdST4988.jpg "style=" float: none; "title=" 10.png "alt=" Wkiom1xex_3ykigtaadu77ddst4988.jpg "/>

When the transaction commits

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/37/wKioL1XeyhaS95iFAABN0byzDqI394.jpg "style=" float: none; "title=" 11.png "alt=" Wkiol1xeyhas95ifaabn0byzdqi394.jpg "/>

2. Save the corresponding location information for the binary log file through another terminal.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/3B/wKiom1Xex_6B8uBSAADGpy1eef8503.jpg "style=" float: none; "title=" 12.png "alt=" Wkiom1xex_6b8ubsaadgpy1eef8503.jpg "/>

3. Create a Snapshot volume

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/37/wKioL1XeyheABKxWAACBTWZFDEg447.jpg "style=" float: none; "title=" 13.png "alt=" Wkiol1xeyheabkxwaacbtwzfdeg447.jpg "/>

4. Release the lock

Mysq>unlock tables;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/37/wKioL1XeyhfRu6fSAABfNGRLBNo623.jpg "style=" float: none; "title=" 14.png "alt=" Wkiol1xeyhfru6fsaabfngrlbno623.jpg "/>

Releases the current table lock. The object is automatically submitted.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/3B/wKiom1Xex_6DgttLAACrETZSRxU772.jpg "style=" float: none; "title=" 15.png "alt=" Wkiom1xex_6dgttlaacretzsrxu772.jpg "/>

5. mount the snapshot volume, backup

Lvcreate-l 50m-s-P r-n mydata-snap/dev/zzxtbl/mydata

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/37/wKioL1XeyjLzZ9xlAAGHJC-rPhM154.jpg "style=" float: none; "title=" 16.png "alt=" Wkiol1xeyjlzz9xlaaghjc-rphm154.jpg "/>

Mount/dev/zzxtbl/mydata-snap/mnt-o ro

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/3B/wKiom1XeyBnxTVy_AAGpSEC6EdM543.jpg "style=" float: none; "title=" 17.png "alt=" Wkiom1xeybnxtvy_aagpsec6edm543.jpg "/>

copy The data from the current LVM volume backup to /opt

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/37/wKioL1XeyjLyXStoAAF5EKy7f0E202.jpg "style=" float: none; "title=" 18.png "alt=" Wkiol1xeyjlyxstoaaf5eky7f0e202.jpg "/>

6. Deleting a snapshot volume

Unload the current logical volume

Umount/mnt

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/37/wKioL1XeyjLzHXxuAABQLocXkeQ398.jpg "style=" float: none; "title=" 19.png "alt=" Wkiol1xeyjlzhxxuaabqlocxkeq398.jpg "/>

Delete a volume group lvremove--force/dev/zzxtbl/mydata-snap

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/72/3B/wKiom1XeyBqxxWcUAACDrLcOTlw326.jpg "style=" float: none; "title=" 20.png "alt=" Wkiom1xeybqxxwcuaacdrlcotlw326.jpg "/>

iv. simulating mysql error deletion and incremental recovery

1. delete the MySQL database on the current volume group

Stop the MYSQLD service

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/37/wKioL1XeyjPCGRZuAABinVrJsts963.jpg "style=" float: none; "title=" 21.png "alt=" Wkiol1xeyjpcgrzuaabinvrjsts963.jpg "/>

2. Copy the previously backed up MySQL database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/3B/wKiom1XeyBqTdwHYAADapF7ilmw045.jpg "style=" float: none; "title=" 22.png "alt=" Wkiom1xeybqtdwhyaadapf7ilmw045.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/37/wKioL1XeyjPCHJHFAABgKAg3XAo718.jpg "style=" float: none; "title=" 23.png "alt=" Wkiol1xeyjpchjhfaabgkag3xao718.jpg "/>

3. Restore Incremental Data

Mysqlbinlog--start-datetime= ' 2015-08-26 23:45:57 ' mysql-bin.000008 >/opt/zengliang-' Date +%F-%H-%M-%S '. sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/72/3B/wKiom1XeyBqjEriOAABwWwqJPw4499.jpg "style=" float: none; "title=" 24.png "alt=" Wkiom1xeybqjerioaabwwwqjpw4499.jpg "/>

Import the current MySQL database. The data is restored . Turn on the binary sync switch set sql_log_bin=1;

Backing up and recovering MySQL databases using LVM