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

Rookie Learning Linux 079 Notes mysql backup lvm

Content overview

Supplement of knowledge points in the previous section

Export and import of single table

LVM creates Snapshot to realize Database backup

There is a little less knowledge in the last section.

Temporarily turn off sql_log_bin when using mysqldump backup and restore (temporarily turn off binary logging)

Because the binary log generated during restore is useless for the next backup, turn off binary during restore

Keep in mind the log record.

SET sql_log_bin=0; shuts down

Import and use

Mysql >\. / path/backup.sql

SET sql_log_bin=1; is enabled

Mysqldump

-- databases DB1,DB2

-- all-databases

-- flush-logs

-- routines

-- triggers

-- master-data= {0 | 1 | 2}

MyISAM warm backup

-- lock-all-tables

-- lock-table

InnoDB hot backup

-- single-transaction

Logical backup

1. Floating-point data loss accuracy

two。 The backup data uses more storage space, and the compressed data can greatly save space.

3. It is not suitable for full backup of large databases.

INNODB

SHOW ENGINES INNODB STATUS

Mvcc, REPEATABLE-READ

-- single-transaction

Export the table directly without adding any information

Mysql > SELECT * INTO OUTFILE'/ path/file_name.txt' FROM tb_name [WHERE]

Mysql > SELECT * INTO OUTFILE'/ tmp/tutor.txt' FROM tutors

(note that the given system path must be accessible to mysql users.)

Restore exported tables

Before import, you need to re-establish the table, and the corresponding column names and their properties

Mysql > CREATE TABLE tutor LIKE tutors

Mysql > LOAD DATA INFILE'/ tmp/tutor.txt' INTO TABLE tutor

This method only makes a single table backup, which is fast (and will not be written into binary log files).

Almost hot standby LVM

Snapshot

Premise

1. The data file should be on the logical volume

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

3. Using snapshot hot standby data files and transaction logs must be in a logical volume

1. Lock table

Mysql > FLUSH TABLES WITH READ LOCK

Mysql > FLUSH LOGS

Mysql > SHOW MASTER STATUS

Remember not to exit the session at this time, but to establish a new session and execute the following command

# mkdir / backup

# mysql-e "SHOW MASTER STATUS\ G;" > / backup/master- `date +% F`.info

two。 Create a snapshot and back up the data

# lvcreate-L 50m-s-pr-n mydata-snap / dev/myvg/mydata

Release the table lock

Mysql > UNLOCK TABLES

# mount / dev/myvg/mydata-snap / mnt/-o ro

# mkdir / backup/full-backup- `date +% F`

# cp-a / mnt/data/* / backup/full-backup-2017-01-13 /

# rm-rf / backup/full-backup-2017-01-13 Universe MySQL. *

(I am simulated here, but in fact, the binary log should not be in the data file, so delete it.)

# cd

# umount / mnt

# lvremove-- force / dev/myvg/mydata-snap

3. Back up binary log files

# cat / backup/master-2017-01-13.info

I'll find a location in here. Mine is 107.

The binary log file is mysql-bin.000005

# mysqlbinlog-- start-position=107 / mydata/data/mysql-bin.000003

This sentence is to find out the start time, and the next sentence is backup.

# mysqlbinlog-- start-datetime='2017-01-13 'mysql-bin.000003

Mysql-bin.000004 mysql-bin.000005... > / backup/binary/binary- `date% F`.sql

4. Simulate database corruption and restore

Delete all information in the database

# service mysqld stop

# rm-rf / mydata/data/*

# killall mysqld

Achieve restore

# cp-a / backup/full-backup-2017-01-13 Universe * / mydata/data/

# ls / mydata/data

# service mysqld start

5. Instant point restore (import binary log files)

Mysql > SET sql_log_bin=0

Mysql > SOURCE / backup/binary-2017-01-13.sql

Mysql > SET sql_log_bin=1

Summarize the steps

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

Mysql > FLUSH TABLES WITH READ LOCK

Mysql > FLUSH LOGS

two。 Save the binary log file and related location information through another terminal

# mysql-uroot-p-e "SHOW MASTER STATUS\ g" > / path/master- `date +% F`.info

3. Create a snapshot

# lvcreate-L #-s-p r-n LV_name / dev/path/source-lv

4. Release lock

Mysql > UNLOCK TABLES

5. Mount snapshot volumes, backup

Mount

Cp

6. Delete snapshot volum

7. Incremental backup binary log

8. Be sure to close the binary log temporarily when restoring.

Be sure to remember to use InnoDB database

Innodb_file_per_table | ON

Otherwise, it will be troublesome to back up a single database using snapshots.

Creating a snapshot to back up the database is more suitable for all library backups

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.