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

This article is mainly about how to achieve backup and recovery methods in MySQL database. If you are interested, let's take a look at this article. I believe it is of some reference value to you after reading how to achieve backup and recovery methods in MySQL database.

Backup and recovery of MySQL Database working principle of mysqdump

The process of backing up data with mysqldump command is actually the process of directly outputting or generating backup files from the form of a logical sql statement in mysql library.

Backing up individual data

Mysql database comes with a very useful backup command, that is, the basic use of mysqldump is as follows

Syntax: mysqldump-u user name-p database name > backup file name

1. Back up the data and restore:

Mysqldump-uroot-p radius > / opt/mysql_bak.sql

Restore database

Mysql-uroot-p radius / opt/mysql_bak.sql

Restore database

Mysql-uroot-p radius / opt/mysql_bak_B.sql

Restore database

Mysql-uroot-p / opt/mysq_bak.sql.gz

5. View the backup content

Egrep-v "# |\ * |-- | ^ $" / opt/mysql_bak.sql

Root@solin:~# mysql-uroot-p-e "select id,username,is_superuser,last_login from radius.auth_user;"

Enter password:

+-- +

| | id | username | is_superuser | last_login | |

+-- +

| | 1 | root | 1 | 2016-11-25 03:26:43 |

| | 2 | WeiFei | 0 | NULL |

+-- +

Back up multiple libraries

1. Back up mysql and radius libraries at the same time

Mysqldump-uroot-p-B radius mysql | gzip > / opt/mul.bak.sql.gz

Hint:-B

Means to connect multiple libraries and add command information for use db and create database db

2. Back up all libraries (- A backs up all libraries)

Mysqldump-uroot-p-A-B | gzip > / opt/a.sql.gz

3. Use source command to restore database.

Log in to the database console and log in to mysql-uroot-p

Msyql > use database

Then use the source command, followed by a data file

Example: root@solin:/opt# mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 251

Server version: 5.6.28-0solin0.15.04.1 (Solin)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

+-+

1 row in set (0.00 sec)

Mysql > system ls / opt

A.sql mysql.sql.gz radius_t.sql.gz shop.sql

Bak radius.sql shop_shop.sql

Mysql > source a.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

...

...

...

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > show database

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | radius |

+-+

3 rows in set (0.00 sec)

Mysql >\ Q

Bye

Sub-library backup

The database of an enterprise will have many libraries, and the problem may be a certain library. If all the libraries are backed up into a data file when backing up, it will be troublesome to restore a certain library.

1. Command line branch library backup

Root@solin:~# mysql-uroot-p-e "show databases;" | grep-Evi "database" | infor | perfor "| sed-r's # ^ ([Amurz]. * $) # mysqldump-uroot-p-B\ 1 | gzip > / opt/bak/\ 1.sql.gzroomg' | bash

Enter password:

Root@solin:~# ls / opt/bak/

Mysql.sql.gz radius.sql.gz

2. For cycle to achieve sub-library backup

[root@solin scripts] # cat fenku.sh

#! / bin/sh

For dbname in `mysql-uroot-paired centos'-e "show databases;" | grep-Evi "database | info | perfor" `

Do

Mysqldump-uroot-paired centos'-events-B $dbname | gzip > / opt/bak/$ {dbname} _ bak.sql.gz

Done

[root@solin scripts] # sh fenku.sh

[root@solin scripts] # ls / opt/bak/

Mysql_bak.sql.gz solin_gbk_bak.sql.gz solin_utf8_bak.sql.gz test_bak.sql.gz

Backup and restore of sub-library

[root@solin] # gzip-d / opt/bak/*

[root@solin ~] # cd / opt/bak/

[root@solin bak] # ls * .sql | sed's benchmark bak.sqlroomroomg'

Mysql

Radius

[root@solin bak] # for dbname in `ls * .sql | sed's roombak.sqlroomroomroomg`; do mysql-uroot-pbdyun

< ${dbname}_bak.sql;done Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. [root@solin bak]# 备份表 1、备份单个表 语法：mysqldump -u用户名 -p 数据库名 表名>

File name of the backup

Example: mysqldump-u root-p radius shop_shop > / opt/shop_shop.sql

View the result

Root@solin:~# ls / opt/

Bak mysql.sql.gz radius.sql.gz shop_shop.sql

Root@solin:~# egrep-v "# |\ * |-- | ^ $" / opt/shop_shop.sql

DROP TABLE IF EXISTS `shop_ shop`

CREATE TABLE `shop_ shop` (

`id`int (11) NOT NULL AUTO_INCREMENT

`shopname` varchar (200) COLLATE utf8_unicode_ci NOT NULL

`contract_person_qq_ number` varchar (50) COLLATE utf8_unicode_ci NOT NULL

`agent` varchar (200) COLLATE utf8_unicode_ci DEFAULT NULL

`url`varchar (200) COLLATE utf8_unicode_ci DEFAULT NULL

……

2. Back up multiple tables

Syntax: mysqldump-u user name-p database name table name 1 table name 2 > backup file name

Example: root@solin:~# mysqldump-uroot-p radius shop_shop shop_shop_admins > / opt/shop.sql

Enter password:

Root@solin:~# ls / opt/

Bak mysql.sql.gz radius.sql.gz shop_shop.sql shop.sql

Root@solin:~# egrep-v "# |\ * |-- | ^ $" / opt/shop.sql DROP TABLE IF EXISTS `shop_ shop`

CREATE TABLE `shop_ shop` (

`id`int (11) NOT NULL AUTO_INCREMENT

`shopname` varchar (200) COLLATE utf8_unicode_ci NOT NULL

`contract_person_qq_ number` varchar (50) COLLATE utf8_unicode_ci NOT NULL

`agent` varchar (200) COLLATE utf8_unicode_ci DEFAULT NULL

`url`varchar (200) COLLATE utf8_unicode_ci DEFAULT NULL

`address` varchar (200) COLLATE utf8_unicode_ci DEFAULT NULL

……

3. Backup of subdatabase and table.

Mysqdump-uroot-p radius test > radius_test.sql

Mysqdump-uroot-p radius test1 > radius_test2.sql

……

Note: put the above command into a script to back up the script table. Of course, you can also search for other better backup scripts and restore multiple SQl files in batches during recovery.

Backup database table structure and data

1. Mysqldump-d parameter only the structure of the backup table

Root@solin:~# mysqldump-uroot-p-B-d radius > / opt/radius_d.sql.gz

Enter password:

Root@solin:~# egrep-v "# |\ * |-- | ^ $" / opt/radius_d.sql.gz

USE `radius`

DROP TABLE IF EXISTS `accounting_ authconnectiondefiny`

CREATE TABLE `accounting_ authconnectiondefiny` (

`id`int (11) NOT NULL AUTO_INCREMENT

`wlanuserip` varchar (255) COLLATE utf8_unicode_ci DEFAULT NULL

`wlanacname` varchar (255) COLLATE utf8_unicode_ci DEFAULT NULL

`wlanacip` varchar (255) COLLATE utf8_unicode_ci DEFAULT NULL

`ssid` varchar (255) COLLATE utf8_unicode_ci DEFAULT NULL

……

2. The mysqldump-t parameter only backs up the data of the table.

Example: root@solin:~# mysqldump-uroot-p-B-t radius > / opt/radius_t.sql.gz

Enter password:

Root@solin:~# egrep-v "# |\ * |-- | ^ $" / opt/radius_t.sql.gz

USE `radius`

LOCK TABLES `accounting_ authconnectiondefiny` WRITE

UNLOCK TABLES

LOCK TABLES `accounting_ authconnectionuseronlinetimelist` WRITE

UNLOCK TABLES

LOCK TABLES `accounting_ marriage toryalloweduserlist` WRITE

UNLOCK TABLES

LOCK TABLES `accounting_ userrecord` WRITE

UNLOCK TABLES

LOCK TABLES `adminbd_ adpullroomy` WRITE

UNLOCK TABLES

……

Refresh, cut binlog

1. Refresh and cut binlog with mysqldump-F parameters

2. The parameter mysqldump-- master-data=1 finds the file and location of the binlog

Description of key parameters of mysqldump

1.-A back up all libraries

2.-B specify multiple libraries and add table building statements and use statements

3.-F refresh binlog log

4.-d backup list structure only

5.-t only reserve table data

6.-x submit request to lock all tables in all databases to ensure data consistency. This is a global read lock and automatically turns off the-- single-transaction and-- lock-tables options.

7.-l-- lock-tables lock all tables are read-only

8.-- master-data adds binlog log file name and corresponding location point

9.-- compact removes comments, which is suitable for debugging output (not used in production environment)

10.-- singe-transaction is suitable for backup of innodb transaction database

Production scene backup

1. Regular backup

Myisam backup:

Mysqldump-uroot-p-A-B-master-data=2-x | gzip > / opt/all.sql.gz

Innodb backup:

Mysqldump-uroot-p-A-B-master-data=2-- singe-transaction | gzip > / opt/all.sql.gz

2. Professional DBA mysqldump backup exercise

The above details about how to achieve backup and recovery of MySQL database are helpful to you? If you want to know more about it, you can continue to follow our industry information section.

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.