MySQL database backup Summary

Data integrity is the most critical for an enterprise to operate normally. Therefore, we need to master data backup and recovery methods in our work; the following is a summary of the three methods for Mysql database backup.

Data integrity is the most critical for an enterprise to operate normally. Therefore, we need to master data backup and recovery methods in our work; the following is a summary of the three methods for Mysql database backup.

Data integrity is the most critical for an enterprise to operate normally. Therefore, we need to master data backup and recovery methods in our work; the following is a summary of the three methods for Mysql database backup. I hope this will be helpful to you.

Prepare the work environment before the backup starts:

1. Create a directory for saving binary log files

# Mkdir/mybinlog

# Chown mysql. mysql/mybinlog

2. modify the configuration file

3. Create a storage point for the backup database

# Mkdir/mybackup

# Chown-R mysql. mysql/mybackup

4. Start the mysql server

# Service mysqld start

5. Insert the database to be backed up

1. Use mysqldump to complete full backup + Incremental backup. Based on mysqldump, full backup + binary logs are usually used to restore data.

1.1 mysqldump is used to warm up the backup. First, you need to apply a read lock to all the databases and scroll the binary log to record the current location of the binary file.

1.2. Back up binary logs

# Cp/mybinlog/mysql-bin.000001/mybackup/alldatabase.000001

1.3 simulate accidental database damage and use full backup to recover the database

# Rm-rf/mydata/data /*

# Rm-rf/mybinlog /*

1.4 initialize mysql and start the server

1.5 Delete binary logs and start the service

1.6 restore to the backup status and import the backup database files:

2. Simulate adding data to the students table and the Hong Kong virtual host. After adding the host, the table is accidentally deleted. We want to restore it to the status before deletion, and the newly added data will also exist.

2.1 add data to the students table

Mysql> insert into students (Name, Age, Gender) values ('hadoop ', 22, 'M ');

2.2 simulate and accidentally delete the table

2.3 check the location of the binary log file

2.4 restore complete data first (the restoration process should not be recorded in logs)

# Mysql </mybackup/alldatabase. SQL

2.5 view the record location when deleting a table

2.5. Export the records before the binary file is completely backed up to the deleted table.

2.6 import changed database logs to the mysql database

2.7. The database was restored successfully and the inserted data was restored.

2. The select command can also complete logical backup, which saves more space and speed than mysqldump, but is more difficult to use than mysqldump, and the data backed up is plain text information, no additional overhead space. It is suitable for backing up a table to simulate backing up a table.

1. Back up the file and save it in a directory. However, you must note that the file under this directory has permissions. When you log on to mysql, you must have the execution permission.

2. To restore the database, you must create an empty table that imitates the original table creation.

Deleted the original table.

Mysql> load data infile '/tmp/tutor.txt' into table tutor;

Verification:

Of course, using select can also back up statements that meet the conditions in the table. The Hong Kong virtual host will not be demonstrated here. The Hong Kong virtual host is very simple.

This method is suitable for the backup of a long table but not recorded in binary logs.

Iii. Use LVM snapshots to achieve almost full hot backup from a physical perspective, and use binary log backup to achieve Incremental backup and database backup. It is very fast to use lvm snapshots for backup, and it is almost hot-standby, recovery is fast, and the operation is simple. After the complete recovery, the corresponding binary data can be restored. Prerequisites:

1. Data Files must be stored on logical volumes.

2. The volume group of the logical volume must have enough space to use the snapshot volume.

3. data files and transaction logs must be on the same logical volume

Steps:

1. Start the transaction

2. open the session, apply a read lock, and lock all tables. At this time, other users cannot execute commands (data cannot be inserted into the database)

Note: Do not exit when the table lock is executed.

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

4. Create a snapshot volume

# Lvcreate-L 50 M-s-p r-n mydata-snap/dev/myvg/mydata

5. Release the lock

6. Mount and back up snapshot volumes

7. unmount/mnt and delete the snapshot volume

# Umount/mnt/

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

8. Incremental backup of binary logs

First, delete the binary log file, which is of little use to us.

# Rm-rf/backup/full-backup-2013-05-06/mysql-bin. *-f

Now the simulated database is formatted

Back up binary logs before formatting.

Restored to the status before restoration

This is a full backup of the logical volume.

This article is from the "Li's blog". Please keep this source