在不同的環境中完成mysql備份

一、實驗環境準備

1、所用的mysql為源碼安裝的mysql-5.5.33，這裡就不示範了，參照http://qidian510.blog.51cto.com/7319882/1282064

2、建立一個新目錄存放二進制日志

#mkdir /binlogdircd

#chown mysql:mysql /binlogdir

修改二進制檔案存放路徑為建立目錄下，并設定innodb每表一檔案

#vim /etc/my.cnf

log-bin=/binlogdir/mysql-bin #二進制日志檔案存放位置

innodb_file_per_table = 1 #啟用InnoDB表每表一檔案，預設所有庫使用一個表空間

啟動服務

#service mysqld start

3、配置所用的測試庫和測試表

mysql> create database testdb;

mysql> use testdb;

mysql> CREATE TABLE tb1 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, Name CHAR(30) NOT NULL UNIQUE KEY, Age TINYINT UNSIGNED);

mysql> insert into tb1 values (1,'Huchong',24);

mysql> insert into tb1 values (2,'Huafeng',24);

mysql> insert into tb1 values (3,'Jim',21);

mysql> insert into tb1 values (4,'Tom',25);

檢視一下生成的表

建立用于存放備份的目錄

#mkdir /backup

#chown -R mysql:mysql /backup

一、用mysqldump實作熱備（邏輯備份）

1、進行完全備份

#mysqldump -uroot -pmypass --single-transaction --events--master-data=2 --all-databases > /backup/all_db_`date +%F`.sql

各選項的意義：

--single-transaction: 基于此選項能實作熱備InnoDB表；mysql> show table status\G；可以檢視表的類型，如果表的類型不是全部為InnoDB，則需要使用--lock-all-tables；

--events備份資料的同時，備份事件排程器代碼；

--master-data=2 在備份檔案中記錄目前二進制日志的位置

--all-databases 備份所有庫

完全備份完成後，對資料庫進行修改，模拟增量備份

mysql> delete from tb1 where id = 4;

此時tb1表中剩下3個使用者，這時候我們對其進行增量備份，而增量備份要指定開始位置和結束位置，因而我們先檢視其開始位置：

#less /backup/all_db_2013-09-01.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=2352;

找到相關的說明可以指定開始位置在2352

接着檢視結束位置，也是目前所在的位置

2、增量備份：

#cd /binlogdir

# mysqlbinlog --start-position=2352 --stop-position=2542 mysql-bin.000007 > /backup/cactidb_`date +%F_%H`.sql

在伺服器上進行其他操作，然後将二進制日志導出

mysql> create table tb2 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, Name CHAR(30) NOT NULL UNIQUE KEY);

模拟資料庫發生意外

mysql> drop databases testdb；

檢視二進制日志最終的位置

# mysqlbinlog --start-position=2542 mysql-bin.000007 會出現類似下面的資訊：

可以看到在執行drop database testdb的前面的位置#at 2716，表示我們回複到這個位置就是資料庫發生意外之前的狀态，是以我們取二進制日志最終位置為2716

導出二進制日志

# mysqlbinlog --start-position=2542 --stop-position=2716 mysql-bin.000007 > /tmp/cadb.sql

也可以不用導出二進制檔案，在滾動後直接複制即可。

恢複

恢複過程的二進制日志記錄對我們沒有意義，是以關閉此功能

mysql> setsql_log_bin=0;

滾動一下日志

mysql> flush logs；

然後導入檔案：

# mysql -uroot -pmypass < /backup/all_db_2013-09-01.sql

# mysql -uroot -pmypass < /backup/cactidb_2013-09-01_20.sql

# mysql -uroot -pmypass < /tmp/cadb.sql

這時候修複完成，我們可以進去檢視一下以确認

二、基于lvm的備份，屬于幾乎熱備（屬于實體備份）

前提：事務日志必須跟資料檔案在同一個邏輯卷上；

1、我們安裝mysql時就是将其安裝在邏輯卷上，這裡我們就不示範了，可以參照http://qidian510.blog.51cto.com/7319882/1282064

如果我們不在邏輯卷上：需要進行操作将其移到邏輯卷上

#mysqldump -uroot -pmypass --lock-all-tables --all-databases --events > /backup/all_db_`date +%F`.sql

#service mysqld stop

#rm -rf /mydata/*

然後建立邏輯卷，格式化完成後挂載，可以參照http://qidian510.blog.51cto.com/7319882/1282064

在挂載的目錄下建立新目錄，屬主屬組為mysql

#mkdir /mydata/data

#chown -R /mydata/data

進行初始化

#cd /usr/local/mysql

#scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

#service mysqld start

連接配接到mysql上

mysql>set session sql_log_bin=0; 關閉二進制日志

mysql>source /backup/all_db_`date +%F`.sql #找到all_db_`date +%F`.sql 檔案，這裡将其導入

mysql> flush privileges; #讓其生效

mysql> show binary logs； #檢視二進制日志檔案

連接配接到mysql上，施加全局鎖

mysql> flush tables with read lock;

mysql> flush logs;

注意：施加鎖以後不能退出，我們重新打開一個會話框進行操作

是以我們另起終端進行操作，先将上面的表儲存到新建立的目錄裡

# mkdir /backup/snapback-`date +%F`

# mysql -uroot -pmypass -e 'show master status;' > /backup/snapback-2013-09-02/bin.txt

2、建立快照卷，這裡要強調一下，對于我們建立的快照卷的大小一定要大于備份的時間中資料的變化大小，否則就會使快照卷崩潰，進而失去了建立快照卷的意義

#lvcreate -L 100M -n mydata-snap -p r -s /dev/myvg/mydata

回到施加鎖的終端上釋放鎖

mysql> unlock tables;

将建立的快照卷挂載

#mount /dev/myvg/mydata-snap /mnt

3、備份

在/backup/目錄下建立一個新目錄，将檔案備份至建立目錄中

# mkdir /backup/snapback-2013-09-02_incremental

# cd /binlogdir

# cp -rp /mnt/* /backup/

然後我們執行新的操作

mysql> insert into tb2 values (1,'tom');

mysql> insert into tb2 values (2,'jerry');

備份完成後，删除快照卷

# mysqlbinlog --start-position=107 mysql-bin.000007 > /mybackup/snapback-2013-09-02/incremental.sql

#umount /mnt

#lvremove /dev/myvg/mydata-snap

模拟資料庫意外損壞

# service mysqld stop

# rm -rf/mydata/data/*

4、恢複資料庫

# cp -a /backup/snapback-2013-09-02/* /mydata/data/

将複制的目錄及檔案屬主屬組都改為mysql

# chown -R mysql:mysql /mydata/data/*

#service mysqld start

連接配接到mysql上，通過二進制日志進行恢複

mysql>set session sql_log_bin=0; #關閉二進制日志

mysql>source /mybackup/snapback-2013-09-02_incremental/incremental.sql

檢視資料庫裡我們添加的資訊

mysql> use testdb

mysql> select * from tb2;

确認恢複成功，打開二進制日志功能

mysql>set session sql_log_bin=0;

三、基于extrabackup完成備份（實體備份）

innodb_file_per_table = 1 # 寫入配置檔案/etc/my.cnf中實作每表一個表空間

下載下傳相關軟體并安裝，根據自己的情況選擇下載下傳

percona-toolkit-2.2.4-1.noarch.rpm

percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

# innobackupex --help #可以檢視幫助

為備份建立一個隻有備份權限的使用者

mysql> create user 'percona'@'localhost' identified by 'mypass';

mysql> revoke all privileges,grant option from 'percona'@'localhost';

mysql> grant reload,lock tables,replication client on *.* to 'percona'@'localhost';

mysql> flush privileges;

2、完全備份一次MySQL

#mkdir /mybackup

#innobackupex --host=localhost --user=percona --password=mypass /mybackup/

如果執行正确，其最後輸出的幾行資訊通常如下：

xtrabackup: Transaction log of lsn (1607784) to (1607784) was copied.

130902 10:13:15 innobackupex: All tables unlocked

innobackupex: Backup created in directory '/mybackup/2013-09-02_10-13-10'

innobackupex: MySQL binlog position: filename 'mysql-bin.000005', position 583

130902 10:13:15 innobackupex: Connection to database server closed

130902 10:13:15 innobackupex: completed OK!

一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。

innobakupex指令的--apply-log選項可用于實作上述功能

模拟異常損壞并恢複

#service mysqld stop

#rm -rf /mydata/data/*

#innobackupex --apply-log /mybackup/2013-09-02_10-13-10/

--apply-log #已經送出的事務同步至資料檔案；沒commit的事務撤銷

#innobackupex --copy-back /mybackup/2013-09-02_10-13-10/

--copy-back #資料庫恢複，後面跟上備份目錄的位置

#chown -R mysql:mysql /mydata/data/*

#service mysqld start

3、使用innobackupex進行增量備份

需要注意的是，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份。

注意：每一次恢複操作後都要重新進行備份的

為了示範效果，我們對資料庫進行一些操作

mysql> drop table tb2;

進行增量備份

#innobackupex --user=percona --password=mypass --incremental/mybackup/ --incremental-basedir=/mybackup/2013-09-02_11-17-35/

--incremental-basedir= #表示基于什麼進行增量備份

4、對mysql操作來進行第二次增量備份

mysql> create database backupdb;

# innobackupex --user=percona --password=mypass --incremental /mybackup/--incremental-basedir=/mybackup/2013-09-02_11-30-51/

如果我們二次增量備份完成後，又執行了新的操作

mysql> insert into tb1 (id,name,age) value (4,'julia',22);

mysql> insert into tb1 (id,name,age) value (5,'kate',26);

5、模拟mysql意外損壞

#rm -rf /mydata/data/*

6、進行恢複

# innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/

# innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/--incremental-dir=/mybackup/2013-09-02_11-23-00/

# innobackupex --apply-log --redo-only /mybackup/2013-09-02_11-30-51/--incremental-dir=/mybackup/2013-09-02_11-32-18/

--redo-only 表示将已經送出的事務應用到資料庫，還沒有送出的則不撤銷，防止所恢複的資料不完整

#innobackupex --copy-back /mybackup/2013-09-02_11-30-51/

#cd /mydata/data

#chown -R mysql:mysql *

最後啟動服務

本文轉自宋鵬超 51CTO部落格，原文連結：http://blog.51cto.com/qidian510/1291804，如需轉載請自行聯系原作者

在不同的環境中完成mysql備份

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述