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

MySQL backup type:

1 according to whether the server is online at the time of backup, it is divided into:

1) Hot backup-the server is online, and read and write are not affected.

2) warm standby-the server is online, but the table needs to be locked and can be read but not written.

3) Cold backup-server is offline and read and write are aborted.

2 according to the backup mode, it can be divided into:

Physical backup-copy data files, characterized by fast backup and recovery. It takes up a lot of space, so it is suitable for big data backup.

Logical backup-exporting data to a file is slow and may lose floating-point accuracy, so it is suitable for scenarios with a small amount of data.

The biggest advantage of logical backup is that it can be backed up in the same way for all kinds of storage engines. While physical backup is different, different storage engines have different backup methods, so it is easier to use logical backup for databases mixed with different storage engines.

Backup tools for MySQL:

1 mysqldump-- logical backup tool, warm backup, hot backup for innoDB.

Options:

-- master-data=0 | 1 | 2

0: do not record binaries and their locations

1: record the location in the way of change master to, which can be used to start the slave server directly after recovery

2: record the location as change master to, but the default is to be annotated

-x,-- lock-all-tables: add this parameter, that is, lock all tables before backup

-Fmam: refresh the binary log.

-- databases: back up specified libraries and tables

-A,-- all-databases: back up all libraries and tables

1) backup and recovery

Export some tables in the specified library: export test01 and test02 tables in the test library:

[root@localhost ~] # mysqldump-uroot-p test test01 test02 > / root/test_$ (date +% F) .sql

Restore: when restoring a table, since there is no statement to build a database in the backup file, the library must be specified. If the library does not exist, the database needs to be established first.

[root@localhost] # mysql-uroot-p test

< /root/test_2017-05-01.sql 导出一个或多个数据库：导出test yewu xiaoshou数据库 [root@localhost ~]# mysqldump -uroot -p --databases test yewu xiaoshou >

/ root/test_yewu_xiaoshou_$ (date +% F) .sql

Restore: since the backup file already contains complete library information, you do not need to specify a library name when restoring

[root@localhost] # mysql-uroot-p

< test_yewu_xiaoshou_2017-05-01.sql 备份MySQL数据库中所有的库： [root@localhost ~]# mysqldump -uroot -p --all-databases >

Alldatabase_$ (date +% F) .sql

Restore:

[root@localhost] # mysql-uroot-p

< alldatabase_2017-05-01.sql 2）生产环境实例 a 上午10：00备份数据库 [root@localhost ~]# mysqldump -uroot -p --lock-all-tables --flush-logs --master-data=2 --databases test >

Test_$ (date +% F) .sql

The table test01 is as follows:

Mysql > select * from test01

+-+ +

| | id | name |

+-+ +

| | 1 | zhan |

| | 2 | liso |

| | 3 | wang |

| | 5 | kang |

+-+ +

B add new data to the table

Mysql > insert into test01 values ('18 years old Dan')

Query OK, 1 row affected (2.56 sec)

Mysql > select * from test01

+-+ +

| | id | name |

+-+ +

| | 1 | zhan |

| | 2 | liso |

| | 3 | wang |

| | 5 | kang |

| | 18 | dan |

+-+ +

5 rows in set (0.00 sec)

C 2:00 in the afternoon, the tables in the database were mistakenly deleted, and the backup began to be restored, first of all

[root@localhost] # mysql-uroot-p test

< test_2017-05-01.sql Enter password: mysql>

Select * from test01

+-+ +

| | id | name |

+-+ +

| | 1 | zhan |

| | 2 | liso |

| | 3 | wang |

| | 5 | kang |

+-+ +

4 rows in set (0.00 sec)

As can be seen from the above results, a row with an id of 18 is missing

D use the mysqlbinlog command to restore binlog since mysqldump backup

View the location of the binlog log when mysqldump backup

[root@localhost] # less test_2017-05-01.sql

-- MySQL dump 10.13 Distrib 5.6.30, for Linux (i686)

--

-- Host: localhost Database: test

-

-- Server version 5.6.30-log

/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /

/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /

/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /

/ * 40101 SET NAMES utf8 * /

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /

/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /

/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /

/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /

--

-- Position to start replication or point-in-time recovery from

--

-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000003', MASTER_LOG_POS=120

--

-- Current Database: `test`

--

CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `test` / *! 40100 DEFAULT CHARACTER SET utf8 * /

USE `test`

--

-- Table structure for table `test01`

--

Backup binary log

[root@localhost ~] # mysqlbinlog localhost-bin.000003 > / root/binlog.sql

Delete the error SQL statement in the binary log

Vim binlog.sql

...

DROP TABLE `test01`

DROP TABLE IF EXISTS `test01`

...

Restore:

Mysql-uroot-p test

< backbinlog.sql 查看恢复后的表： mysql>

Select * from test01

+-+ +

| | id | name |

+-+ +

| | 1 | zhan |

| | 2 | liso |

| | 3 | wang |

| | 5 | kang |

| | 18 | dan |

+-+ +

5 rows in set (0.00 sec)

2 xtrabackup-- is a data backup tool for InnoDB developed by Percona Company. The backup mode is physical backup and supports hot backup.

Xtrabackup contains two tools: xtrabackup and innobackupex

Xtrabackup can only back up the InnoDB and XtraDB data engines, but not the data tables of the MyISAM engine

Innobackupex is a Perl script that encapsulates xtrabackup and supports backing up both InnoDB and MyISAM.

1) to install, you need to install the yum source of epel

[root@localhost ~] # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.8/binary/redhat/6/i386/percona-xtrabackup-2.3.8-1.el6.i686.rpm

[root@localhost] # yum-y localinstall percona-xtrabackup-2.3.8-1.el6.i686.rpm

2) backup and recovery

Backup:

An establish a backup account:

Mysql > grant reload,lock tables,replication client on *. * to 'dbbak'@'localhost' identified by' bk2016'

B full backup, socket may not be specified unless it is inconsistent with the default

[root@localhost] # innobackupex-- socket=/tmp/mysqld.sock-- user=dbbak-- password=bk2016 / root/

...

170501 23:20:35 Backup created in directory'/ root/2017-05-01 / 23-20-25max'

MySQL binlog position: filename 'localhost-bin.000003', position' 15651'

170501 23:20:35 [00] Writing backup-my.cnf

170501 23:20:35 [00]... done

170501 23:20:35 [00] Writing xtrabackup_info

170501 23:20:35 [00]... done

Xtrabackup: Transaction log of lsn (2031876) to (2031876) was copied.

170501 23:20:35 completed OK!

[root@localhost ~] # cd 2017-05-01 / 23-20-25 / # View the directory after backup

[root@localhost 2017-05-01 / 23-20-25] # ls

Backup-my.cnf ibdata1 mysql performance_schema test xiaoshou xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile yewu

Backup directory description:

Xtrabackup_checkpoints-- backup type (such as full and incremental), backup status, LSN (log serial number) scope information

Configuration option information used by the xtrabackup_my.cnf-- backup command

The binary log files currently used by the xtrabackup_binlog_info--MySQL server and the location of the binary log events up to the moment of backup

C preprocess backup files to prepare a full backup, adding memory options,-- use-memory=1G

[root@localhost] # innobackupex-- apply-log / root/2017-05-01 / 23-20-25

D incremental backup through binary after the data is changed

[root@localhost 2017-05-01 / 23-20-25] # cat xtrabackup_binlog_info

Localhost-bin.00000315651

[root@localhost ~] # mysqlbinlog / usr/data/mysql/localhost-bin.000003 > / root/bin$ (date +% F) .sql

Restore:

A stop service

[root@localhost ~] # service mysqld stop

B restore full, MySQL data directory must be empty, otherwise an error will be reported

[root@localhost] # innobackupex-- copy-back-- user=dbbak-- password=bk2016 / root/2017-05-01 / 23-20-25 /

[root@localhost mysql] # cd / usr/local/mysql/ # modify the master group of the data directory

[root@localhost mysql] # chown-R mysql.mysql *

C start the service

[root@localhost mysql] # service mysqld start

D restore incremental backups from binaries

Mysql > set sql_log_bin=0; # temporarily closes binary log

Query OK, 0 rows affected (0.03 sec)

Mysql > source / root/bin2017-05-01.sql

...

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.01 sec)

Mysql > set sql_log_bin=1; # Open binary log

Query OK, 0 rows affected (0.00 sec)

E after the restore is complete, make a full backup again

Import and export of tables

1 Export

Method 1:

[root@localhost ~] # mysql-uroot-p-h 127.0.0.1-e "select * from test.test01" > / root/mysql.txt

Enter password:

[root@localhost ~] # ls

2017-05-01 23-20-25 anaconda-ks.cfg bin2017-05-01.sql cmake-3.7.1 cmake-3.7.1.tar.gz install.log install.log.syslog mysql-5.6.30 mysql-5.6.30.tar.gz mysql.txt percona-xtrabackup-2.3.8-1.el6.i686.rpm

[root@localhost ~] # cat mysql.txt

Idname

1zhan

2liso

3wang

5kang

18dan

Mode 2:

Mysql > select * from test01 into outfile'/ tmp/c.txt'

Query OK, 5 rows affected (0.00 sec)

[root@localhost tmp] # cat / tmp/c.txt

1zhan

2liso

3wang

5kang

18dan

Mode 3:

[root@localhost tmp] # mysqldump-uroot-p-h 127.0.0.1-T / tmp test test02

Enter password:

[root@localhost tmp] # ls

A.txt c.txt mysqld.sock percona-version-check test02.sql test02.txt vmware-root vmware-root-1821705925

[root@localhost tmp] # cat test02.txt

A

B

2 Import

Mysql > select * from test03; # View the content before importing the data

+-+ +

| | id | name |

+-+ +

| | 10 | zwj |

| | 10 | zwj |

+-+ +

2 rows in set (0.00 sec)

Mysql > load data infile'/ tmp/a.txt' into table test.test03; # Import data

Query OK, 5 rows affected (0.13 sec)

Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

Mysql > select * from test.test03

+-+ +

| | id | name |

+-+ +

| | 10 | zwj |

| | 10 | zwj |

| | 1 | zhan |

| | 2 | liso |

| | 3 | wang |

| | 5 | kang |

| | 18 | dan |

+-+ +

Note:

Lock the table:

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.03 sec)

Release the lock table:

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

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.