MySQL數(shù)據(jù)庫入門之備份數(shù)據(jù)庫操作詳解
更新時間：2020年05月11日 11:08:20 作者：民工哥
這篇文章主要介紹了MySQL數(shù)據(jù)庫入門之備份數(shù)據(jù)庫操作,結(jié)合實例形式詳細(xì)分析了MySQL備份數(shù)據(jù)庫基本操作命令與相關(guān)注意事項,需要的朋友可以參考下
本文實例講述了MySQL數(shù)據(jù)庫入門之備份數(shù)據(jù)庫操作。分享給大家供大家參考，具體如下：
一提到數(shù)據(jù)，大家神經(jīng)都會很緊張，數(shù)據(jù)的類型有很多種，但是總歸一點，數(shù)據(jù)很重要，非常重要，因此，日常的數(shù)據(jù)備份工作就成了運(yùn)維工作的重點中的重點的重點.................
首先來看看數(shù)據(jù)庫中的數(shù)據(jù)
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、單庫備份
[root@centos6 ~]# mysqldump -uroot -p test >/download/testbak_$(date +%F).sql Enter password: [root@centos6 ~]# ll /download/ total 2 -rw-r--r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql
下面我們看看這個備份文件到底是什么內(nèi)容
[root@centos6 ~]# 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;
由上的文件內(nèi)容，可以看出，這個備份實際的過程就是將創(chuàng)建數(shù)據(jù)庫、建表、插入數(shù)據(jù)的sql語句備份出來，也可以說是將sql語句導(dǎo)出
-B參數(shù)
[root@centos6 ~]# mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sql Enter 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ù)庫丟失時，可以直接用此備份文件進(jìn)行恢復(fù)，無需再重新建庫、建表，然后再進(jìn)行數(shù)據(jù)恢復(fù)的操作
2、壓縮備份
有時候，數(shù)據(jù)庫的數(shù)據(jù)比較大，可能會用到壓縮后進(jìn)行備份，節(jié)省備份時間與磁盤空間的使用
[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz Enter 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
同時也可以看的壓縮后的效果
3、多庫備份
[root@centos6 ~]# 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. [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
此處有個警告信息，可以忽略也可以備份時加上參數(shù)，備份語句如下
[root@centos6 ~]# mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz Enter 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)生一個問題，如果只是其中一個數(shù)據(jù)庫有問題了，就不好進(jìn)行單庫恢復(fù)了，故此備份方法不常用，也不符合實際需求，因此多庫備份時就需要進(jìn)行多次單庫備份的操作
[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ù)庫時方便操作，但是同樣面臨問題，如果是某個庫中的某一個表有損壞，但又不有全庫進(jìn)行恢復(fù)，所以實際生產(chǎn)中常用的是分庫、分表進(jìn)行備份，這樣數(shù)據(jù)也備份了，恢復(fù)時也好操作
[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)行多次單表備份的操作，但是有的小伙伴肯定會提出問題了，如果一個庫里幾千張表，幾萬張表，這種備份要備到猴年馬月吧？？？？，數(shù)據(jù)量比較大的備份可以使用專業(yè)的備份工具，數(shù)據(jù)量不大或者表不是很多的情況，可以將備份操作寫成腳本 納入定時任務(wù)，定時執(zhí)行，只需要檢查備份是否成功即可
分享一下民工哥，實際生產(chǎn)環(huán)境中一個簡單的備份腳本，僅供參考
[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}
#判斷如果不存在這個路徑就創(chuàng)建一個，為了服務(wù)器多的時候方便看
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}
#判斷這個路徑，為了區(qū)別哪個庫的備份文件
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
#分表備份
done
done
執(zhí)行的結(jié)果如下
[root@ranzhioa ~]# tree /backup/ /backup/ 10.1xx.1xx.1xx #服務(wù)器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
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。
相關(guān)文章
這篇文章主要介紹了mysql的3種分表方案,先從為什么要分表說起,然后分析了3種方法的優(yōu)劣勢,最后做了一個總結(jié),需要的朋友可以參考下2014-05-05
這篇文章主要給大家介紹了關(guān)于MySQL中執(zhí)行計劃explain命令的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用explain命令具有一定的參考學(xué)習(xí)價值，需要的朋友們下面說來一起學(xué)習(xí)學(xué)習(xí)吧2018-05-05
這篇文章主要介紹了MySQL?配置主從復(fù)制實踐分享，通過檢測通信展開詳情，具有一定的參考價值，需要的小伙伴可以參考一下2022-07-07
這篇文章主要給大家介紹了關(guān)于MySQL如何生成唯一的server-id的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
本文中介紹了一個MySQL的存儲過程，其中涉及Cursor的使用2008-12-12
MySQL 5.5.49 大內(nèi)存優(yōu)化配置文件優(yōu)化詳解
最近mysql服務(wù)器升級到了MySQL 5.5.49版本，性能比mysql 5.0.**肯定效率高了不少，但mysql的默認(rèn)配置文件不合理，這里是針對大內(nèi)存訪問量大的機(jī)器的配置方案，需要的朋友可以參考下2016-05-05
MySQL存儲過程中游標(biāo)循環(huán)的跳出和繼續(xù)操作示例
這篇文章主要介紹了MySQL存儲過程中游標(biāo)循環(huán)的跳出和繼續(xù)操作示例,解決了在MySQL存儲過程中循環(huán)時執(zhí)行游標(biāo)的一個conitnue的操作解決方法,需要的朋友可以參考下2014-07-07
MySQL如何導(dǎo)入SQL數(shù)據(jù)庫的實戰(zhàn)舉例
在使用mysql數(shù)據(jù)庫是,經(jīng)常需要備份或者恢復(fù)數(shù)據(jù)庫數(shù)據(jù),最便捷的方式就是通過導(dǎo)出sql文件備份和直接執(zhí)行sql文件恢復(fù),下面這篇文章主要給大家介紹了關(guān)于MySQL如何導(dǎo)入SQL數(shù)據(jù)庫的相關(guān)資料,需要的朋友可以參考下2023-05-05
這篇文章主要為大家詳細(xì)介紹了windows下mysql修改root密碼的4種方法，大家可以根據(jù)的自己的實際情況進(jìn)行選擇，感興趣的小伙伴們可以參考一下2016-05-05
這篇文章主要介紹了一些MySQL 常用函數(shù)的總結(jié)，文中講解非常細(xì)致，幫助大家更好的學(xué)習(xí)mysql，感興趣的朋友可以了解下2020-08-08