本文實(shí)例講述了MySQL數(shù)據(jù)庫入門之備份數(shù)據(jù)庫操作。分享給大家供大家參考，具體如下：
接上一次：MySQL數(shù)據(jù)庫入門多實(shí)例配置
一提到數(shù)據(jù)，大家神經(jīng)都會很緊張，數(shù)據(jù)的類型有很多種，但是總歸一點(diǎn)，數(shù)據(jù)很重要，非常重要，因此，日常的數(shù)據(jù)備份工作就成了運(yùn)維工作的重點(diǎn)中的重點(diǎn)的重點(diǎn).................
首先來看看數(shù)據(jù)庫中的數(shù)據(jù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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、單庫備份
|
1
2
3
4
5
6
|
[root@centos6 ~]# mysqldump -uroot -p test >/download/testbak_$(date +%F).sqlEnter password: [root@centos6 ~]# ll /download/total 2-rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql |
下面我們看看這個(gè)備份文件到底是什么內(nèi)容
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[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 (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');UNLOCK TABLES; |
由上的文件內(nèi)容，可以看出，這個(gè)備份實(shí)際的過程就是將創(chuàng)建數(shù)據(jù)庫、建表、插入數(shù)據(jù)的sql語句備份出來，也可以說是將sql語句導(dǎo)出
-B參數(shù)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
[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;/*!40101 SET character_set_client = @saved_cs_client */;LOCK TABLES `test` WRITE;/*!40000 ALTER TABLE `test` DISABLE KEYS */;INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');/*!40000 ALTER TABLE `test` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!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 */; |
-B參數(shù)的作用一目了然，就是當(dāng)我們的數(shù)據(jù)庫丟失時(shí)，可以直接用此備份文件進(jìn)行恢復(fù)，無需再重新建庫、建表，然后再進(jìn)行數(shù)據(jù)恢復(fù)的操作
2、壓縮備份
有時(shí)候，數(shù)據(jù)庫的數(shù)據(jù)比較大，可能會用到壓縮后進(jìn)行備份，節(jié)省備份時(shí)間與磁盤空間的使用
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gzEnter password: [root@centos6 ~]# ll /download/testbak_2016-12-12.sql.gz-rw-r--r--. 1 root root 753 Dec 12 20:49 /download/testbak_2016-12-12.sql.gz[root@centos6 ~]# ll /download/total 14-rw-r--r--. 1 root root 2027 Dec 12 20:41 testbak_2016-12-12_b.sql-rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql-rw-r--r--. 1 root root 753 Dec 12 20:49 testbak_2016-12-12.sql.gz |
同時(shí)也可以看的壓縮后的效果
3、多庫備份
|
1
2
3
4
5
6
|
[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 |
此處有個(gè)警告信息，可以忽略也可以備份時(shí)加上參數(shù)，備份語句如下
|
1
2
3
4
|
[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 |
這樣就不會有這為警告信息了
但是這種多庫一起備份，就會產(chǎn)生一個(gè)問題，如果只是其中一個(gè)數(shù)據(jù)庫有問題了，就不好進(jìn)行單庫恢復(fù)了，故此備份方法不常用，也不符合實(shí)際需求，因此多庫備份時(shí)就需要進(jìn)行多次單庫備份的操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
[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 80-rw-r--r--. 1 root root 152608 Dec 12 20:58 mysqlbak_2016-12-12.sql.gz-rw-r--r--. 1 root root 754 Dec 12 20:58 testbackup_2016-12-12.sql.gz-rw-r--r--. 1 root root 2027 Dec 12 20:41 testbak_2016-12-12_b.sql-rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql-rw-r--r--. 1 root root 152696 Dec 12 20:52 testbak_2016-12-12.sql01.gz-rw-r--r--. 1 root root 152749 Dec 12 20:54 testbak_2016-12-12.sql02.gz-rw-r--r--. 1 root root 753 Dec 12 20:49 testbak_2016-12-12.sql.gz |
4、單表備份
分庫備份是為了恢復(fù)數(shù)據(jù)庫時(shí)方便操作，但是同樣面臨問題，如果是某個(gè)庫中的某一個(gè)表有損壞，但又不有全庫進(jìn)行恢復(fù)，所以實(shí)際生產(chǎn)中常用的是分庫、分表進(jìn)行備份，這樣數(shù)據(jù)也備份了，恢復(fù)時(shí)也好操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
[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-- MySQL dump 10.13 Distrib 5.5.52, for linux2.6 (x86_64)---- Host: localhost Database: test-- -------------------------------------------------------- Server version 5.5.53-log---- 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 (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');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 (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');UNLOCK TABLES;-- Dump completed on 2016-12-12 21:13:16 |
因此分表備份同分庫備份一樣，只需要進(jìn)行多次單表備份的操作，但是有的小伙伴肯定會提出問題了，如果一個(gè)庫里幾千張表，幾萬張表，這種備份要備到猴年馬月吧？？？？，數(shù)據(jù)量比較大的備份可以使用專業(yè)的備份工具，數(shù)據(jù)量不大或者表不是很多的情況，可以將備份操作寫成腳本 納入定時(shí)任務(wù)，定時(shí)執(zhí)行，只需要檢查備份是否成功即可
分享一下民工哥，實(shí)際生產(chǎn)環(huán)境中一個(gè)簡單的備份腳本，僅供參考
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
[root@centos6 scripts]# vi bak.sh #!/bin/sh###########################################this scripts create by root of mingongge#create at 2016-11-11#######################################ip=`grep 'IPADDR' /etc/sysconfig/network-scripts/ifcfg-eth0|awk -F "=" '{print $2}'`#定義服務(wù)器IP變量BAKDIR=/backup #定義備份路徑[ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip} #判斷如果不存在這個(gè)路徑就創(chuàng)建一個(gè)，為了服務(wù)器多的時(shí)候方便看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;"|sed '1,5d'` #定義數(shù)據(jù)庫變量for name in $DB_NAME#for循環(huán)語句取庫名do $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz #全庫備份 [ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p $BAKDIR/${ip}/${name}#判斷這個(gè)路徑，為了區(qū)別哪個(gè)庫的備份文件 for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'`#for循環(huán)語句取表名 do $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz#分表備份 donedone |
執(zhí)行的結(jié)果如下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@ranzhioa ~]# tree /backup//backup/10.1xx.1xx.1xx #服務(wù)器IP xxxxxxx #其實(shí)是庫名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 |
希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。
原文鏈接：https://segmentfault.com/a/1190000020675546
