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

Note: mysqldump backup database experiment (in this paper, there are two methods to achieve backup, method 1 used in this lab)

Steps:

1. Full backup of database with mysqldump

Law 1:

# mysqldump-all-databases-lock-all-tables-master-data=2-flush-logs > / backup/all- `date +% F`.sql

After locking the table, scroll the binlog data file, and then back up the database. The next incremental backup only needs to start with the newly created binlog file.

Or:

Law 2:

# mysqldump-all-databases-lock-all-tables-master-data=2 > / backup/all- `date +% F`.sql

And open the backed up file and view this line:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=8230

If you find that MASTER_LOG_POS=8230; represents the end transaction point of backup is 8230, then the incremental backup needs to record the backup from 8230.

two。 Try to modify the data in the database, create a new text2 table, and insert a row of data (simulate running new data online)

MariaDB [hellodb] > create table test2 (id int)

Query OK, 0 rows affected (0.09 sec)

MariaDB [hellodb] > insert into test2 values (1)

Query OK, 1 row affected (0.04 sec)

3. Use the mysqlbinlog command to make an incremental backup

Law 1:

# mysqlbinlog mysql-bin.000011 > / backup/zlbf- `date +% F`.sql

Or:

Law 2:

# mysqlbinlog-start-position=8230 mysql-bin.000010 > / backup/zlbf- `date +% F`.sql

4. Try to modify the data in the database again: delete the text1 table (as data that has not been backed up in time)

MariaDB [hellodb] > show tables

+-+

| | Tables_in_hellodb |

+-+

| | classes |

| | coc |

| | courses |

| | scores |

| | students |

| | teachers |

| | test2 |

| | text1 |

| | toc |

+-+

9 rows in set (0.00 sec)

MariaDB [hellodb] > drop table text1

Query OK, 0 rows affected (0.17 sec)

5. Delete hellodb database and test data recovery

MariaDB [hellodb] > drop database hellodb

Query OK, 8 rows affected (0.07 sec)

6. Export binlog files that have not been backed up in time

# mysqlbinlog mysql-bin.000011 > / backup/ binlog.`date +% F`.sql

And modify the exported binlog file to delete the sql statement that caused the database to be deleted at the end

# vim binlog.2015-04-09.sql

# at 355

# 150409 0:15:01 server id 1 end_log_pos 442 Query thread_id=6104 exec_time=0 error_code=0

SET timestamp 1428509701

Drop database hellodb (delete the misoperation sql statement for this line)

/ *! * /

DELIMITER

# End of log file

7. Start restore

First restore the full backup:

# mysql

< all-2015-04-08.sql 查看还原的数据：（可以看见完全备份的数据已然恢复回来） MariaDB [(none)]>

Use hellodb

Database changed

MariaDB [hellodb] > show tables

+-+

| | Tables_in_hellodb |

+-+

| | classes |

| | coc |

| | courses |

| | scores |

| | students |

| | teachers |

| | text1 |

| | toc |

+-+

8 rows in set (0.02 sec)

Then restore the incremental backup:

# mysql

< zlbf-2015-04-09.sql 查看还原的数据：（可以看到增量备份以前的数据也已经恢复） MariaDB [(none)]>

Use hellodb

Database changed

MariaDB [hellodb] > show tables

+-+

| | Tables_in_hellodb |

+-+

| | classes |

| | coc |

| | courses |

| | scores |

| | students |

| | teachers |

| | test2 |

| | text1 |

| | toc |

+-+

9 rows in set (0.03 sec)

Then recover the unbacked up data from the binlog file:

# mysql

< binlog.2015-04-09.sql 查看还原的数据：（未备份时候删除的text1表也已经成功删除，可以看到未备份的数据也成功恢复） MariaDB [(none)]>

Use hellodb

Database changed

MariaDB [hellodb] > show tables

+-+

| | Tables_in_hellodb |

+-+

| | classes |

| | coc |

| | courses |

| | scores |

| | students |

| | teachers |

| | test2 |

| | toc |

+-+

8 rows in set (0.00 sec)

At this point, the backup recovery experiment is complete.

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.