歡迎您光臨本站 註冊首頁

MySQL數據庫入門之備份數據庫操作詳解

←手機掃碼閱讀     bom485332 @ 2020-05-12 , reply:0

本文實例講述了MySQL數據庫入門之備份數據庫操作。分享給大家供大家參考,具體如下:
接上一次:MySQL數據庫入門多實例配置
一提到數據,大家神經都會很緊張,數據的類型有很多種,但是總歸一點,數據很重要,非常重要,因此,日常的數據備份工作就成了運維工作的重點中的重點的重點.................
首先來看看數據庫中的數據
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、單庫備份
[[email protected] ~]# mysqldump -uroot -p test >/download/testbak_$(date +%F).sql Enter password: [[email protected] ~]# ll /download/ total 2 -rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql
下面我們看看這個備份文件到底是什麼內容
[[email protected] ~]# egrep -v "^--|*|^$" /download/testbak_2016-12-12.sql DROP 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;
由上的文件內容,可以看出,這個備份實際的過程就是將創建數據庫、建表、插入數據的sql語句備份出來,也可以說是將sql語句導出
-B參數
[[email protected] ~]# mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sql Enter password: [[email protected] ~]# egrep -v "^--|^$" /download/testbak_2016-12-12_b.sql /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */; /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */; /*!40101 SET @[email protected]@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @[email protected]@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @[email protected]@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 [email protected]_TIME_ZONE */; /*!40101 SET [email protected]_SQL_MODE */; /*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */; /*!40014 SET [email protected]_UNIQUE_CHECKS */; /*!40101 SET [email protected]_CHARACTER_SET_CLIENT */; /*!40101 SET [email protected]_CHARACTER_SET_RESULTS */; /*!40101 SET [email protected]_COLLATION_CONNECTION */; /*!40111 SET [email protected]_SQL_NOTES */;
-B參數的作用一目瞭然,就是當我們的數據庫丟失時,可以直接用此備份文件進行恢復,無需再重新建庫、建表,然後再進行數據恢復的操作
2、壓縮備份
有時候,數據庫的數據比較大,可能會用到壓縮後進行備份,節省備份時間與磁盤空間的使用
[[email protected] ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz Enter password: [[email protected] ~]# 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 [[email protected] ~]# 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
同時也可以看的壓縮後的效果
3、多庫備份
[[email protected] ~]# mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz Enter password: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. [[email protected] ~]# 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
此處有個警告信息,可以忽略也可以備份時加上參數,備份語句如下
[[email protected] ~]# mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz Enter password: [[email protected] ~]# 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
這樣就不會有這為警告信息了
但是這種多庫一起備份,就會產生一個問題,如果只是其中一個數據庫有問題了,就不好進行單庫恢復了,故此備份方法不常用,也不符合實際需求,因此多庫備份時就需要進行多次單庫備份的操作
[[email protected] ~]# mysqldump -uroot -p -B test|gzip >/download/testbackup_$(date +%F).sql.gz Enter password: [[email protected] ~]# mysqldump -uroot -p -B --events mysql|gzip >/download/mysqlbak_$(date +%F).sql.gz Enter password: [[email protected] ~]# 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、單表備份
分庫備份是為了恢復數據庫時方便操作,但是同樣面臨問題,如果是某個庫中的某一個表有損壞,但又不有全庫進行恢復,所以實際生產中常用的是分庫、分表進行備份,這樣數據也備份了,恢復時也好操作
[[email protected] ~]# mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql Enter password: [[email protected] ~]# 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
因此分表備份同分庫備份一樣,只需要進行多次單表備份的操作,但是有的小夥伴肯定會提出問題了,如果一個庫裡幾千張表,幾萬張表,這種備份要備到猴年馬月吧????,數據量比較大的備份可以使用專業的備份工具,數據量不大或者表不是很多的情況,可以將備份操作寫成腳本 納入定時任務,定時執行,只需要檢查備份是否成功即可
分享一下民工哥,實際生產環境中一個簡單的備份腳本,僅供參考
[[email protected] 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}'` #定義服務器IP變量 BAKDIR=/backup #定義備份路徑 [ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip} #判斷如果不存在這個路徑就創建一個,為了服務器多的時候方便看 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'` #定義數據庫變量 for name in $DB_NAME #for循環語句取庫名 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} #判斷這個路徑,為了區別哪個庫的備份文件 for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'` #for循環語句取表名 do $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz #分表備份 done done
執行的結果如下
[[email protected] ~]# tree /backup/ /backup/ 10.1xx.1xx.1xx #服務器IP xxxxxxx #其實是庫名 cash_balance_2016-12-15.sql.gz cash_depositor_2016-12-15.sql.gz cash_trade_2016-12-15.sql.gz crm_customer_2016-12-15.sql.gz crm_delivery_2016-12-15.sql.gz crm_order_2016-12-15.sql.gz crm_orderAction_2016-12-15.sql.gz crm_orderField_2016-12-15.sql.gz crm_plan_2016-12-15.sql.gz


[bom485332 ] MySQL數據庫入門之備份數據庫操作詳解已經有245次圍觀

http://coctec.com/docs/mysql/show-post-234053.html