一、Mysqldump備份恢複案列
1、Mysqldump指令的使用介紹
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<code># mysqldump --help</code>
<code>Usage: mysqldump [OPTIONS] database [tables]</code>
<code>OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]</code>
<code>OR mysqldump [OPTIONS] --all-databases [OPTIONS]</code>
<code>-u </code><code>#指定使用者名</code>
<code>-p </code><code>#指定密碼</code>
<code>-h </code><code>#指定主機位址</code>
<code>-S </code><code>#指定socket檔案</code>
<code>--flush-logs </code><code>#執行日志flush滾動</code>
<code>--lock-all-tables </code><code>#鎖定所有表</code>
<code>--master-data </code><code>#該選項将會記錄binlog的日志位置與檔案名并追加到檔案中，如果為1将 會輸出CHANGE MASTER指令，主從下有用</code>
<code>--triggers </code><code>#備份觸發器的</code>
<code>--events </code><code>#備份資料庫的事件排程器的</code>
<code>--routines </code><code>#備份存儲過程和函數的</code>
<code>--single-transaction </code><code>#如果指定庫中的表類型均為InnoDB，可使用--single-transaction啟動熱備；--single-transaction不要和--lock-all-tables一起使用，因為--single-transaction可以實作熱備,會自動鎖表和重新整理日志。</code>
2、故障模拟測試
(1)首先在資料庫進行一些建立庫和表的操作
16
17
18
19
20
21
22
23
24
<code>mysql> create database Allentuns;</code>
<code>Query OK, 1 row affected (0.00 sec)</code>
<code>mysql> use Allentuns;</code>
<code>Database changed</code>
<code>mysql> create table tab1(</code><code>id</code> <code>int,name char(20),age int);</code>
<code>Query OK, 0 rows affected (0.01 sec)</code>
<code>mysql> insert into tab1 values(1,</code><code>'jerry'</code><code>,24);</code>
<code>Query OK, 1 row affected (0.01 sec)</code>
<code>mysql> insert into tab1 values(2,</code><code>'kimi'</code><code>,12);</code>
<code>mysql> insert into tab1 values(3,</code><code>'jay'</code><code>,35);</code>
<code>mysql> SHOW MASTER STATUS;</code>
<code>+------------------+----------+--------------+------------------+--------------------------------------------+</code>
<code>| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |</code>
<code>| mysql-bin.000023 | 3440 | | | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-140 |</code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
(2)對操作的資料庫進行備份
<code># mkdir /bak</code>
<code># mysqldump -uroot -p -h 127.0.0.1 --databases Allentuns --events --triggers --master-data=2 --flush-logs --lock-all-tables > /bak/Allentuns_full_`date +%F`.sql</code>
(3)向Allentuns資料庫中添加資料來模拟第一次增量備份
<code>mysql> use Allentuns</code>
<code>mysql> create table tab2 like tab1;</code>
<code>mysql> insert into tab2 values(10,</code><code>'java'</code><code>,20);</code>
<code>mysql> insert into tab2 values(11,</code><code>'python'</code><code>,40);</code>
<code>mysql> insert into tab2 values(13,</code><code>'php'</code><code>,10);</code>
<code>| mysql-bin.000024 | 1306 | | | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-144 |</code>
(4)為了使模拟的效果更接近于生産環境，在這裡我們滾動二進制日志，使産生的資料記錄資訊儲存在不同的二進制日志當中
<code>mysql> FLUSH LOGS;</code>
<code>Query OK, 0 rows affected (0.02 sec)</code>
<code>mysql> insert into tab2 values(14,</code><code>'C++'</code><code>,14);</code>
<code>mysql> insert into tab2 values(15,</code><code>'ruby'</code><code>,15);</code>
<code>| mysql-bin.000025 | 833 | | | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-146 |</code>
(5)将二進制日志中産生的新記錄資訊輸出到sql腳本當中
<code># cat /bak/Allentuns_full_2014-11-30.sql |grep "mysql-bin"</code>
<code>-- CHANGE MASTER TO MASTER_LOG_FILE=</code><code>'mysql-bin.000024'</code><code>, MASTER_LOG_POS=191;</code>
<code>{以上是對Allentuns資料庫做全備那一時刻對應的二進制檔案和所處的位置}</code>
<code># mysql -uroot -p -e "show master status;"</code>
<code>Enter password: </code>
<code>{以上是第一次做增量備份後後那一時刻對應的二進制檔案和所處的位置}</code>
<code># cd /mydata/data</code>
<code># mysqlbinlog --skip-gtids --start-position=191 mysql-bin.000024 mysql-bin.000025 > /bak/incremental_`date +%F-%H-%M-%S`.sql</code>
(6)向Allentuns資料庫中添加資料來模拟第二次增量備份
<code>mysql> create table tab3 like tab1;</code>
<code>mysql> insert into tab3 values(21,</code><code>'zhengyansheng'</code><code>,24);</code>
<code>mysql> insert into tab3 values(22,</code><code>'wangtianyu'</code><code>,22);</code>
<code>mysql> insert into tab3 values(23,</code><code>'zhengziyu'</code><code>,20);</code>
<code>| mysql-bin.000025 | 1986 | | | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-150 |</code>
(7)删除Allentuns庫來模拟發生故障
<code>mysql> drop database Allentuns;</code>
<code>Query OK, 3 rows affected (0.02 sec)</code>
<code>mysql> show master status; </code><code>#删除Allentuns資料庫時沒有滾動日志，那麼使用“drop”的指令就記錄在這個二進制日志當中</code>
<code>| mysql-bin.000025 | 2141 | | | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-151 |</code>
(8)檢視删除Allentuns資料庫對應的二進制日志檔案，并找到删除之前的那個點的Position
<code># mysqlbinlog /mydata/data/mysql-bin.000025</code>
<code>----------------</code>
<code># at 2034 #這個就是我們要的值2034</code>
<code>#141130 20:44:35 server id 1 end_log_pos 2141 CRC32 0x23d014a1 Query thread_id=12 exec_time=0 error_code=0</code>
<code>SET TIMESTAMP=1417351475/*!*/;</code>
<code>drop database Allentuns</code>
(9)通過mysqlbinlog導出二進制日志在drop之前的sql腳本
<code># mysqlbinlog --skip-gtids --start-position=833 --stop-position=2034 /mydata/data/mysql-bin.000025 > /bak/incremental_2034.sql</code>
(10)開始做資料庫恢複
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<code>mysql> </code><code>set</code> <code>sql_log_bin=0;</code>
<code>mysql> </code><code>source</code> <code>/bak/Allentuns_full_2014-11-30</code><code>.sql;</code>
<code>mysql> </code><code>source</code> <code>/bak/incremental_2014-11-30-20-42-25</code><code>.sql;</code>
<code>mysql> </code><code>source</code> <code>/bak/incremental_2034</code><code>.sql;</code>
<code>mysql> show tables;</code>
<code>+---------------------+</code>
<code>| Tables_in_Allentuns |</code>
<code>| tab1 |</code>
<code>| tab2 |</code>
<code>| tab3 |</code>
<code>3 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
<code>mysql> </code><code>select</code> <code>* from tab1;</code>
<code>+------+-------+------+</code>
<code>| </code><code>id</code> <code>| name | age |</code>
<code>| 1 | jerry | 24 |</code>
<code>| 2 | kimi | 12 |</code>
<code>| 3 | jay | 35 |</code>
<code>mysql> </code><code>select</code> <code>* from tab2;</code>
<code>+------+--------+------+</code>
<code>| </code><code>id</code> <code>| name | age |</code>
<code>| 10 | java | 20 |</code>
<code>| 11 | python | 40 |</code>
<code>| 13 | php | 10 |</code>
<code>| 14 | C++ | 14 |</code>
<code>| 15 | ruby | 15 |</code>
<code>5 rows </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
<code>mysql> </code><code>select</code> <code>* from tab3;</code>
<code>+------+---------------+------+</code>
<code>| </code><code>id</code> <code>| name | age |</code>
<code>| 21 | zhengyansheng | 24 |</code>
<code>| 22 | wangtianyu | 22 |</code>
<code>| 23 | zhengziyu | 20 |</code>
二、Mysql5.6 新特性1
最近在群上讨論Mysql5.6的話題越來越多了，自從Oracle收購Mysql之後；Oracle對Mysql的存儲引擎做了很大的改進，在Mysql5.5之前Mysql的預設存儲引擎MyISAM，這種存儲引擎不支援事物，現在5.6版本預設的存儲引擎就是InnoDB，此版本并引入了GTID特性，也就是GTID的特性讓我在上面花了一天的時間
在這個難題上，最後去看官方文檔才得到答案。
首先來看一個錯誤截圖，這個錯誤時由于用mysqlbinlog做二進制日志導出後，檢視庫中的表資訊的時候報的一個錯誤，如下圖所示：
<a href="http://s3.51cto.com/wyfs02/M01/54/2F/wKiom1R7G72B4i0yAAUpHRr_ZpM500.jpg" target="_blank"></a>
三、Mysql5.6分庫分表備份腳本的新特性2
1、Mysql基于mysqldump的分庫備份腳本
<code>#!/bin/bash</code>
<code>#Author:Allentuns</code>
<code>#Tel:13260071987</code>
<code>db_user=</code><code>'root'</code>
<code>db_passwd=123456</code>
<code>db_host=192.168.0.104</code>
<code>db_none=</code><code>"information_schema|mysql|performance_schema"</code>
<code>db_command=`mysql -u$db_user -p$db_passwd -h $db_host -e </code><code>"show databases;"</code> <code>|</code><code>sed</code> <code>'1d'</code> <code>|</code><code>egrep</code> <code>-</code><code>v</code> <code>$db_none`</code>
<code>db_dump=</code><code>"mysqldump -u$db_user -p$db_passwd -h $db_host --master-data=2 --flush-logs --lock-all-tables"</code>
<code>db_file=</code><code>'/mydata/mysqlbak'</code>
<code>if</code> <code>[ ! -d $db_file ];</code><code>then</code>
<code> </code><code>mkdir</code> <code>-p $db_file</code>
<code>fi</code>
<code>for</code> <code>i </code><code>in</code> <code>$db_command;</code>
<code>do</code>
<code> </code><code>$db_dump $i |</code><code>gzip</code> <code>> $db_file/${i}_$(</code><code>date</code> <code>+%F-%H-%M-%S).sql.gz</code>
<code>done</code>
2、Mysql基于mysqldump的分表備份腳本
<code>db_user=root</code>
<code>db_host=localhost</code>
<code>db_connect=</code><code>"mysql -u$db_user -p$db_passwd -h $db_host"</code>
<code>for</code> <code>db </code><code>in</code> <code>$db_command</code>
<code> </code><code>if</code> <code>[ ! -d $db_file/$db ];</code><code>then</code>
<code> </code><code>mkdir</code> <code>-p $db_file/$db</code>
<code> </code><code>fi</code>
<code> </code><code>for</code> <code>table </code><code>in</code> <code>`$db_connect -e </code><code>"show tables from $db;"</code><code>|</code><code>sed</code> <code>'1d'</code><code>`</code>
<code> </code><code>do</code>
<code> </code><code>$db_dump $db $table |</code><code>gzip</code> <code>> $db_file/${db}/${table}_$(</code><code>date</code> <code>+%F-%H-%M-%S).sql.gz</code>
<code> </code><code>done</code>
3、在指令行或腳本中如果傳遞密碼就會報一下警告資訊
<a href="http://s3.51cto.com/wyfs02/M02/54/2D/wKioL1R7HZCAxlTOAACRPYn_Hsg729.jpg" target="_blank"></a>
這個也是版本5.6的新特性，如果以明文方式顯示密碼都會報警告資訊的，解決辦法就是将其加入到用戶端的配置檔案中，然後不用輸入賬戶和密碼了，這樣就能避免發出警告資訊。
解決辦法來源網際網路：http://www.68idc.cn/help/jiabenmake/qita/2014010766686.html
<code>cat</code> <code>> ~/.my.cnf << EOF</code>
<code>[mysqldump]</code>
<code>user=root</code>
<code>password=123456</code>
<code>host=127.0.0.1</code>
<code>EOF</code>
四、補充一點
1、lvm邏輯卷
邏輯卷管理器lvm，想必大家一點都不陌生吧！幾乎絕大多數我們都會把資料庫的資料檔案放在lvm卷上，1是為了保證磁盤可以線上動态的擴容，2是為了利用lvm的快照功能可以實作對Mysql資料庫做熱備，我會在後續部落格補充和案列示範。
<code>建立邏輯卷</code>
<code>1.首先将磁盤建立成邏輯卷</code>
<code># fdisk /dev/sdb</code>
<code>/dev/sdb1</code> <code>1 2610 20964793+ 8e Linux LVM</code>
<code>2.将分區轉換成pv實體卷</code>
<code># pvcreate /dev/sdb1 #将分區/dev/sdb1轉換成實體卷</code>
<code># pvs #檢視實體卷資訊</code>
<code>3.将pv實體卷加入到vg卷組</code>
<code># vgcreate myvg /dev/sdb1 #将實體卷加入到卷組中</code>
<code># vgs #檢視卷組資訊</code>
<code>4.從卷組vg中劃分出lv邏輯卷</code>
<code># lvcreate -n mydata -L 10G myvg</code>
<code># lvs</code>
<code>5.格式化lv邏輯卷</code>
<code># mkfs.ext4 /dev/myvg/mydata</code>
<code>6.建立挂載目錄并賦予mysql權限</code>
<code># mkdir -p /mydata/data</code>
<code># chown -R mysql.mysql /mydata/data/</code>
<code>7.開機自動挂載</code>
<code># echo "/dev/myvg/mydata /mydata/data ext4 defaults 0 0" >> /etc/fstab</code>
<code># mount -a</code>
<code>8.檢視挂載資訊</code>
<code># mount</code>
************2014-12-12補充***************
場景：在用mysqldump對測試資料做備份的時候，出來這樣的提示
<a href="http://s3.51cto.com/wyfs02/M00/56/AD/wKioL1SKnw_hgzzjAAHAV0ePoTg142.jpg" target="_blank"></a>
解決辦法：
<code># vim /etc/my.cnf </code>
<code>set</code><code>-gtid-purged=OFF</code>
<code></code>
本文轉自zys467754239 51CTO部落格，原文連結：http://blog.51cto.com/467754239/1584844，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)