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

The backup of Mysql database is the most important. In the process of production, the database will lose data due to hardware failure, software failure, *, misoperation and so on, but after precise backup, the data can be recovered completely.

First, backup tools

Here are several commonly used backup methods

Cp: physical backup tool, suitable for all storage engines, cold backup, full backup, partial backup

Mysqldump: logical backup tool for all storage engines, supporting warm backup, full backup, partial backup, hot backup for InnoDB storage engine

Xtrabackup: a very powerful InnoDB/XtraDB hot backup tool that supports full backup and incremental backup

Second, cp backup and recovery

Let's look at the data first and experiment with the student table in the test library.

Mysql > select * from test.student;+-+-+ | id | name | +-+-+ | 1 | a | +-+-+ 1 row in set (0.00 sec)

Copy mysql data to the specified directory

Mkdir / backupcp-a / var/lib/mysql/* / backup

Simulate lost data and delete mysql data

Rm-rf / var/lib/mysql/*

Restart the database and find that the data is missing (here, the yum installation can restart the database directly. If it is compiled and installed, it needs to be initialized)

Service mysqld restartmysql > select * from test.student;ERROR 1146 (42S02): Table 'test.student' doesn't existmysql >

Restore the backed-up data to the data directory

Cp-a / backup/* / var/lib/mysql/

Log in to the database and find that the data has been restored

Mysql > select * from test.student;+-+-+ | id | name | +-+-+ | 1 | a | +-+-+ 1 row in set (0.00 sec) mysql > III, mysqldump backup and recovery

Let's look at the data first and experiment with the student table in the test library.

Mysql > select * from test.student;+-+-+ | id | name | +-+-+ | 1 | a | 2 | b | +-+-+ 2 rows in set (0.00 sec)

Modify my.cnf, add log_bin, restart database

Vim / etc/ my.cnf[mysqld] log_bin=mysql-binservice mysqld restart

Use Mysqldump to back up data and parameter description

Mysqldump-uroot-A-- events-- master-data=2-- single-transaction > / opt/all.sql-An is complete, and there is no need to create a library during recovery-- events backup time scheduler-- master-data recording time point-- single-transaction lock table backup.

Insert new data

Mysql > use test;mysql > insert into student values; Query OK, 1 row affected (0.00 sec) mysql > select * from student;+-+-+ | id | name | +-+-+ | 1 | a | 2 | b | 3 | c | +-+-+ 3 rows in set (0.00 sec)

Loss of analog data, misoperation

Mysql > drop database test;Query OK, 1 row affected (0.01 sec) mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | +-+ 2 rows in set (0.00 sec)

Start recovery with full mysqldump plus incremental backup

1, turn off logging

Set global sql_log_bin=0

2. Restore everything and check the data.

Mysql-uroot

Select * from test.student;+-+-+ | id | name | +-+-+ | 1 | a | | 2 | b | +-+-+ 2 rows in set (0.00 sec)

3. Take advantage of point-in-time recovery and incremental recovery. Check the start time of / opt/all.sql. There is a string similar to the following in the file.

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=705

When looking at the time of misoperation

Mysqlbinlog / var/lib/mysql/mysql-bin.000018 # at 869mm 161102 18:36:09 server id 1 end_log_pos 896 Xid = 4088COMMIT / Canada drop database test server id # at 896mm 161102 18:36:25 server id 1 end_log_pos 977 Query thread_id=41 exec_time=0 error_code=0SET timestamp 1478082985

Restore based on the start and end time points

Mysqlbinlog / var/lib/mysql/mysql-bin.000018-- start-position=705-- stop-position=896 | mysql- uroot

4. Restore is complete, view data

Mysql > select * from test.student;+-+-+ | id | name | +-+-+ | 1 | a | 2 | b | 3 | c | +-+-+ 3 rows in set (0.00 sec) IV, the total amount of xtrabackup backup and recovery

1, download the tool and install it

Wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpmyum install percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

2. Create backup directory and backup

Mkdir / exbackupinnobackupex-- user=root / exbackup/innobackupex-- apply-log / exbackup/2016-11-02 20-14-08 /

3. View backup files

[root@data-1-1 3306] # ll / exbackup/2016-11-02 20-14-08 / total dosage 30760drwxmuri. 2 root root 4096 November 2 20:49 2016-11-02 20-49-56 Murray rwmurr. 1 root root 386 November 2 20:14 backup-my.cnf-rw-r-. 1 root root 18874368 November 2 20:21 ibdata1-rw-r--r--. 1 root root 5242880 November 2 20:21 ib_logfile0-rw-r--r--. 1 root root 5242880 November 2 20:21 ib_logfile1drwx-. 2 root root 4096 November 2 20:14 mysqldrwx-. 2 root root 4096 November 2 20:14 oldboydrwx-. 2 root root 4096 November 2 20:14 performance_schemadrwx-. 2 root root 4096 November 2 20:14 test-rw-r-. 1 root root 21 November 2 20:14 xtrabackup_binlog_info-rw-r--r--. 1 root root 23 November 2 20:21 xtrabackup_binlog_pos_innodb-rw-r-. 1 root root 113 November 2 20:21 xtrabackup_checkpoints-rw-r-. 1 root root 511 November 2 20:14 xtrabackup_info-rw-r-. 1 root root 2097152 November 2 20:21 xtrabackup_logfile

4. Simulated data loss and recovery

Rm-rf / data/*mysql > show databases;+-+ | Database | +-+ | information_schema | +-+ 1 row in set (0.00 sec)

5. Use backup files, restore data, restart database, and view data

Innobackupex-- copy-back / exbackup/2016-11-02 / 20-14-08 / chown-R mysql:mysql / data/*service mysqld restartmysql > show databases +-- + | Database | +-- + | information_schema | | # mysql50#2016-11-02 / 20-49-56 | | mysql | | oldboy | | performance_schema | | test | +-- + 6 rows in set (0.00 sec) 5 | Increment of xtrabackup backup and recovery

1. We have done everything above, and we have added new data to do incremental backup experiments.

Mysql > show databases +-- + | Database | +-- + | information_schema | | # mysql50#2016-11-02 / 20-49-56 | | mysql | | oldboy | | performance_schema | | T1 | | T2 | | test | +-+ |

2. Perform incremental backup. Note: next increment-the most recent incremental backup path of incremental-basedir=

Innobackupex-- incremental / exbackup/-- incremental-basedir=/exbackup/2016-11-02 20-14-08 / innobackupex-- apply-log-- redo-only / exbackup/2016-11-02 20-14-08 / innobackupex-- apply-log-- redo-only / exbackup/2016-11-02 20-14-08 /-- incremental-basedir=/exbackup/2016-11-02 21-31-50 /

3. View incremental backup

[root@data-1-1 exbackup] # ll / exbackup/ total dosage 8drwxmuri. 7 root root 4096 November 2 20:49 2016-11-02 October 20-14-08drwxmuri. 9 root root 4096 November 2 21:32 2016-11-02 October 21-31-50

4. Simulated data loss and recovery

Rm-rf / data/*mysql > show databases;+-+ | Database | +-+ | information_schema | +-+ 1 row in set (0.00 sec)

5. Use backup files, restore data, restart database, and view data

Innobackupex-- copy-back / exbackup/2016-11-02 recovery 20-14-08 / # directly through full incremental recovery chown-R mysql:mysql / data/*service mysqld restartmysql > show databases +-- + | Database | +-- + | information_schema | | # mysql50#2016-11-02 / 20-49-56 | | mysql | | oldboy | | performance_schema | | T1 | | T2 | | test | +-+ 8 rows in set (0.01 sec) 6 Xtrabackup backup and restore multiple instances

1. The steps are basically the same, except for adding more parameters.

Innobackupex-- defaults-file=/data/3306/my.cnf-- user=root / exbackupinnobackupex-- apply-log / exbackup/2016-11-02 20-14-08 / innobackupex-defaults-file=/data/3306/my.cnf-- copy-back / exbackup/2016-11-02 20-14-08 /

2, increment

Innobackupex-- defaults-file=/data/3306/my.cnf-- user=root-- incremental / exbackup/-- incremental-basedir=/exbackup/2016-11-02 20-14-08/innobackupex-- apply-log-- redo-only / exbackup/2016-11-02 20-14-08/innobackupex-- apply-log-- redo-only / exbackup/2016-11-02 20-14-08 /-- incremental-dir=/exbackup/2016-11-02 21-31- 50/innobackupex-defaults-file=/data/3306/my.cnf-copy-back / exbackup/2016-11-02-20-14-08 /

Summary: just more parameters of the configuration file, other steps are completely the same, here do not spend space on these operations, you can test, this article does not have too much principle explanation, all practical information operation, want to understand the principle of these backup methods, the parameters of the netizens will forgive me

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.