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

This article mainly explains the method of MySQL backup database, the content is clear, interested friends can learn, I believe it will be helpful after reading.

When it comes to data, everyone will be very nervous, there are many types of data, but in general, data is very important, very important, therefore, the daily data backup work has become the focus of the operation and maintenance work.

First, let's take a look at the data in the database.

Mysql > select * from test +-+-+ | id | name | +-+-+ | 1 | 1 | 11 | text | 21 | abc | 9 | bcd | | 111 | 1 | 441 | text | 41 | abc | | 999 | bcd | +-+-+ 8 rows in set (0.00 sec) 1, Single library backup [root@centos6 ~] # mysqldump-uroot-p test > / download/testbak_$ (date +% F). SqlEnter password: [root@centos6 ~] # ll / download/total 2 Murray. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql

Let's take a look at the contents of this backup file.

[root@centos6 ~] # egrep-v "^-|\ * | ^ $" / download/testbak_2016-12-12.sqlDROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id`int (4) NOT NULL, `name`char (20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;LOCK TABLES `test` WRITE INSERT INTO `test` VALUES (1dc`), (11d`), (9pcd'), (111d'), (441) (441), (41pc'), (999)); UNLOCK TABLES

From the contents of the files on, we can see that the actual process of this backup is to back up the sql statements that create the database, create tables, and insert data, or export the sql statements.

-B parameter

[root@centos6] # mysqldump-uroot-p-B test > / download/testbak_$ (date +% F) _ b.sqlEnter password: [root@centos6 ~] # egrep-v "^-- | ^ $" / download/testbak_2016-12-12_b.sql / *! 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 * /; CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `test` / *! 40100 DEFAULT CHARACTER SET latin1 * /; USE `test`; DROP TABLE IF EXISTS `test`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; / *! 40101 SET character_set_client = utf8 * / CREATE TABLE `test` (`id` int (4) NOT NULL, `name` char (20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET latin1), (441) SET character_set_client = @ saved_cs_client * /; LOCK TABLES `test`WRITE: 40000 ALTER TABLE `test` DISABLE KEYS * /; INSERT INTO `test` VALUES (1'), (11'), (21)), (9)), (111)), (441)), (41)), (999) / *! 40000 ALTER TABLE `test`Test` ENABLE KEYS * /; UNLOCK TABLESTABLESTABLES TABLESTABLESTABLESTABLESTABLES TABLESTABLESTABLESTABLESTABLESTABLES TABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABSTABSTABSTABSTABLSTABLES: / *! 40101 SET SQL_MODE=@OLD_SQL_MODE * / /; / *! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS * /; / *! 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS * /; / *! 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /; / *! 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS * / / *! 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /; / *! 40111 SET SQL_NOTES=@OLD_SQL_NOTES * /

The function of parameter-B is clear at a glance, that is, when our database is lost, we can directly use this backup file to restore, without the need to rebuild the database, build tables, and then perform data recovery operations.

2. Compress backup

Sometimes, the data in the database is relatively large, so it may be used for backup after compression to save backup time and disk space.

[root@centos6] # mysqldump-uroot-p-B test | gzip > / download/testbak_$ (date +% F). Sql.gzEnter password: [root@centos6 ~] # ll / download/testbak_2016-12-12.sql.gzMurray RW Murray. 1 root root 753 Dec 12 20:49 / download/testbak_2016-12-12.sql.gz [root@centos6] # ll / download/total 14 RWKui. 1 root root 2027 Dec 12 20:41 testbak_2016-12-12 copyright b.sqlmurr RW Murray RW Muhami. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql RW Murray. 1 root root 753 Dec 12 20:49 testbak_2016-12-12.sql.gz

At the same time, you can also see the compressed effect.

3. Multi-library backup [root@centos6 ~] # mysqldump-uroot-p-B test mysql | gzip > / download/testbak_$ (date +% F). Sql01.gzEnter password:-- Warning: Skipping the data of table mysql.event. Specify the-- events option explicitly. [root@centos6] # ll / download/testbak_2016-12-12.sql01.gz-rw-r--r--. 1 root root 152696 Dec 12 20:52 / download/testbak_2016-12-12.sql01.gz

There is a warning message here, which can be ignored or backed up with parameters. The backup statement is as follows

[root@centos6 ~] # mysqldump-uroot-p-B-- events test mysql | gzip > / download/testbak_$ (date +% F). Sql02.gzEnter password: [root@centos6 ~] # ll / download/testbak_2016-12-12.sql02.gz-rw-r--r--. 1 root root 152749 Dec 12 20:54 / download/testbak_2016-12-12.sql02.gz

So there won't be a warning message.

However, this kind of multi-database backup together will give rise to a problem. If there is a problem with only one of the databases, it is difficult to restore a single database, so the backup method is not commonly used and does not meet the actual requirements. Therefore, multiple single-database backups are required when backing up multiple databases.

[root@centos6 ~] # mysqldump-uroot-p-B test | gzip > / download/testbackup_$ (date +% F). Sql.gz Enter password: [root@centos6 ~] # mysqldump-uroot-p-B-- events mysql | gzip > / download/mysqlbak_$ (date +% F) .sql.gz Enter password: [root@centos6 ~] # ll / download/total 80Murray 1 root root 152608 Dec 12 20:58 mysqlbak_2016-12-12.sql.gzmurr RW Murray 1 root root 754 Dec 12 20:58 testbackup_2016-12-12.sql.gzmurr RW Murray 1 root root 2027 Dec 12 20:41 testbak_2016-12-12 copyright b.sqlmurr RW Murray RW Muhami. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql Murray RW Murray RW Murray. 1 root root 152696 Dec 12 20:52 testbak_2016-12-12.sql01.gz, RW, RW, RMI. 1 root root 152749 Dec 12 20:54 testbak_2016-12-12.sql02.gz Murray RW Murray RMI Murray. 1 root root 753 Dec 12 20:49 testbak_2016-12-12.sql.gz4, single table backup

The purpose of sub-database backup is to facilitate operation when restoring the database, but it is also faced with problems. If a certain table in a certain database is damaged, but there is no full database for recovery, it is commonly used to back up by sub-database and sub-table in actual production. This way, the data is also backed up, and the recovery is easy to operate.

[root@centos6 ~] # mysqldump-uroot-p-B test test > / download/test_testbak_$ (date +% F). Sql Enter password: [root@centos6 ~] # egrep-v "# | ^ $|\ *" / download/test_testbak_2016-12-12.sql Distrib-MySQL dump 10.13 Distrib 5.5.52 For linux2.6 (x86y64)-Host: localhost Database: test-- Server version 5.5.53 Muhammad-Current Database: `test`-- USE `test` -Table structure for table `test`-DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id`test` (4) NOT NULL, `name` char (20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;---- Dumping data for table `test`-LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES (1dc`), (11pcd`), (21pc`), (9recordbcd'), (111meme1text'), (441)), (41jobc'), (999reqbcd'); UNLOCK TABLES -Current Database: `test`-- USE `test`;-Table structure for table `test`-- DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id`int (4) NOT NULL, `name` char (20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;---- Dumping data for table `test`-- LOCK TABLES `test` WRITE INSERT INTO `test` VALUES (1dc`), (11d`), (111d'), (441), (41pc`), (999pcd'); UNLOCK TABLES;-- Dump completed on 2016-12-12 21:13:16

Therefore, sub-table backup is the same as sub-database backup, which only requires multiple single-table backups, but some partners will certainly ask questions. If there are thousands of tables and tens of thousands of tables in a library, this kind of backup will not be ready until the year of the monkey, right? A backup with a large amount of data can use professional backup tools, and if the amount of data is small or the table is not many, you can script the backup operation into the scheduled task and execute it regularly. You only need to check whether the backup is successful.

Share a simple backup script for migrant workers in the actual production environment. For reference only, [root@centos6 scripts] # vi bak.sh #! / bin/sh###this scripts create by root of mingongge#create at 2016-11-11 IPADDR' / etc/sysconfig/network-scripts/ifcfg-eth0 | awk-F "='{print $2}'`# defines the server IP variable BAKDIR=/backup # defines the backup path [!-d $BAKDIR/$ {ip}] & & mkdir-p $BAKDIR/$ {ip} # determines that if this path does not exist, create one To facilitate viewing DB_PWD= "mingongge" DB_USER= "root" MYSQL= "/ application/mysql/bin/mysql" MYSQL_DUMP= "/ application/mysql/bin/mysqldump" DATA= `date +% F` # bak data of test's databses####DB_NAME= `$ MYSQL-u$DB_USER-p$DB_PWD-e "show databases when there are many servers | | sed '1author5d`` # defines database variable for name in $DB_NAME#for circular statement takes the library name do $MYSQL_DUMP-u$DB_USER-p$DB_PWD-B ${name} | gzip > $BAKDIR/$ {ip} / ${name} _ $DATA.sql.gz # full library backup [!-d $BAKDIR/$ {ip} / ${name}] & & mkdir-p $BAKDIR/$ {ip} / ${name} # judge this path To distinguish which library's backup file for tablename in `$ MYSQL-u$DB_USER-p$DB_PWD-e "show tables from ${name} | | sed '1d' `# for loop statement takes the table name do $MYSQL_DUMP-u$DB_USER-p$DB_PWD ${name} ${tablename} | gzip > $BAKDIR/$ {ip} / ${name} / ${tablename} _ $DATA.sql.gz# sub-table backup donedone

The result of the execution is as follows

[root@ranzhioa] # tree / backup//backup/10.1xx.1xx.1xx # server IP xxxxxxx # is actually the library name cash_balance_2016-12-15.sql.gzcash_depositor_2016-12-15.sql.gzcash_trade_2016-12-15.sql.gzcrm_customer_2016-12-15.sql.gzcrm_delivery_2016-12-15.sql.gzcrm_order_2016-12-15.sql.gzcrm_orderAction_2016-12-15.sql. Gzcrm_orderField_2016-12-15.sql.gzcrm_plan_2016-12-15.sql.gz read the above content Do you have a further understanding of the method of MySQL backup database? if you want to learn more, you are welcome to follow the industry information channel.

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.