MySQL備份數(shù)據(jù)庫的方法分享

2024-07-24 12:36:11

字體：大中小

供稿：網(wǎng)友

　　這篇文章主要講解了MySQL備份數(shù)據(jù)庫的方法，內(nèi)容清晰明了，對此有興趣的小伙伴可以學習一下，相信大家閱讀完之后會有幫助。

　　一提到數(shù)據(jù)，大家神經(jīng)都會很緊張，數(shù)據(jù)的類型有很多種，但是總歸一點，數(shù)據(jù)很重要，非常重要，因此，日常的數(shù)據(jù)備份工作就成了運維工作的重點中的重點的重點.................

　　首先來看看數(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語句導出

　　-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ù)的作用一目了然，就是當我們的數(shù)據(jù)庫丟失時，可以直接用此備份文件進行恢復，無需再重新建庫、建表，然后再進行數(shù)據(jù)恢復的操作

　　2、壓縮備份
　　有時候，數(shù)據(jù)庫的數(shù)據(jù)比較大，可能會用到壓縮后進行備份，節(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ù)庫有問題了，就不好進行單庫恢復了，故此備份方法不常用，也不符合實際需求，因此多庫備份時就需要進行多次單庫備份的操作

　　[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、單表備份
　　分庫備份是為了恢復數(shù)據(jù)庫時方便操作，但是同樣面臨問題，如果是某個庫中的某一個表有損壞，但又不有全庫進行恢復，所以實際生產(chǎn)中常用的是分庫、分表進行備份，這樣數(shù)據(jù)也備份了，恢復時也好操作

　　[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
　　因此分表備份同分庫備份一樣，只需要進行多次單表備份的操作，但是有的小伙伴肯定會提出問題了，如果一個庫里幾千張表，幾萬張表，這種備份要備到猴年馬月吧？？？？，數(shù)據(jù)量比較大的備份可以使用專業(yè)的備份工具，數(shù)據(jù)量不大或者表不是很多的情況，可以將備份操作寫成腳本納入定時任務，定時執(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}'`
　　#定義服務器IP變量
　　BAKDIR=/backup
　　#定義備份路徑

　　[ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}
　　 #判斷如果不存在這個路徑就創(chuàng)建一個，為了服務器多的時候方便看

　　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 #服務器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
　　看完上述內(nèi)容，是不是對MySQL備份數(shù)據(jù)庫的方法有進一步的了解，如果還想學習更多內(nèi)容。

（編輯：武林網(wǎng)）

上一篇：MySQL基本查詢例子2

下一篇：MySQL數(shù)據(jù)庫主從同步的實現(xiàn)方法推薦