linuxmysql全局變數的簡單介紹

本文目錄一覽：

• 1、如何做好MySQL安全策略
• 2、mysql 變數設置問題
• 3、查看mysql是否為雙機
• 4、如何修改MYSQL的系統變數
• 5、如何將linux中mysql備份恢復

如何做好MySQL安全策略

摘至網頁鏈接

常見Mysql配置文件：linux系統下是my.conf，windows環境下是my.ini；

資料庫整體安全需求：機密性、完整性、可用性；

下面以mysql 5.7版本為例，介紹mysql常見的安全策略、配置、加固方式等等，有些策略可能只針對Linux操作系統，更多策略可以參考CIS Mysql Benchmark相關文檔：

1、操作系統級別安全配置

1.1不要將資料庫放在系統分區

Windows系統：直接檢查是否將資料庫放置在C盤。

Linux系統：

在終端連接上mysql資料庫，執行如下命令：

show variables where variable_name = ‘datadir’;

然後返回shell命令行：

df -h datadir

其中datadir是上一條命令的返回值。

上述命令的返回值不應是/、/var、/usr

1.2使用專用的最小許可權賬號運行mysql資料庫進程

Windows系統：直接打開任務管理器，查看運行mysql進程的操作系統賬號，不能為administrator賬號。

Linux系統：

Shell命令行運行如下命令：

ps -ef | grep mysql

查看mysql服務的運行賬號是否為root或其他高許可權賬號，如果是的，則需要創建一個非管理員專用賬號來運行mysql服務。

1.3禁止使用mysql命令行歷史記錄

Linux系統：

執行如下命令：

find / -name “.mysql_history”

查看是否存在mysql的歷史命令記錄文件，如果存在，則需要進行如下加固：

（1）刪除.mysql_history文件；

（2）設置環境變數MYSQL_HISTFILE為/dev/null，並添加到shell的初始化腳本中，創建mysql_history到/dev/null的鏈接：

ln -s /dev/null $HOME/.mysql_history

1.4 確保MYSQL_PWD環境變數未設置敏感信息

Windows系統下進入cmd命令行，使用如下命令：

Set

查看是否設置了環境變數MYSQL_PWD。

Linux系統下使用如下命令：

grep MYSQL_PWD /proc/*/environ

查看MYSQL_PWD環境變數是否設置了敏感信息。

確認那個配置文件或腳本設置了MYSQL_PWD環境變數。

2、安裝

2.1使用資料庫專用伺服器

使用專用的伺服器安裝mysql服務可以減少mysql服務的攻擊面，盡量卸載或刪除操作系統上的不必要的應用或服務，減少其他應用的安裝可能給mysql的運行帶來的安全風險。

2.2 不要復用資料庫賬號

運行mysql服務的操作系統賬號不要用來運行其他應用或服務，這樣可以避免其他應用或伺服器被攻擊給mysql服務帶來影響。

2.3 歷史命令行密碼設置為不可見

使用如下命令：

mysql -u admin -p password

連接mysql資料庫服務，退出後查看歷史命令，確認password是否為明文。

建議使用如下命令方式登錄：

（1）先輸入mysql -u admin -p

（2）根據命令行提示輸入密碼；

而不要在一整條命令中輸入密碼。

另外要控制mysql配置文件訪問許可權。

3、文件許可權控制

3.1 控制數據目錄的訪問許可權

數據目錄是mysql資料庫存放的位置，在mysql命令行界面下執行如下命令：

show variables where variable_name = ‘datadir’;

在終端命令行下執行如下命令：

ls -l datadir/.. | egrep “^d[r|w|x]{3}——\s*.\s*mysql\s*mysql\s*\d*.*mysql”

其中datadir是第一條命令的執行結果

如果存在問題，linux環境下在終端執行如下命令進行加固：

chmod 700 datadir

chown mysql:mysql datadir

3.2 控制二進位日誌文件的許可權

mysql的運行會產生很多日誌，例如二進位日誌、錯誤日誌、慢查詢日誌等等，Mysql命令行下執行如下命令：

show variables like ‘log_bin_basename’;

在終端命令行執行如下命令：

ls log_bin_basename.*

對於發現的每一個文件，執行如下命令：

ls -l log_bin_basename.nnnnn | egrep “^-[r|w]{2}-[r|w]{2}—-\s*.*$”

根據輸出確認日誌文件的許可權設置是否存在問題。

對於每個日誌文件，修改其許可權和屬組如下：

chmod 660 log file

chown mysql:mysql log file

3.3 控制錯誤日誌文件的許可權

Mysql命令行下執行如下命令：

show variables like ‘log_error’;

在終端命令行執行如下命令：

ls log_error.*

對於發現的每一個文件，執行如下命令：

ls -l log_error | egrep “^-[r|w]{2}-[r|w]{2}—-\s*.*$”

根據輸出確認日誌文件的許可權設置是否存在問題。

對於每個日誌文件，修改其許可權和屬組如下：

chmod 660 log file

chown mysql:mysql log file

3.4控制慢查詢日誌文件的許可權

Mysql命令行下執行如下命令：

show variables like ‘slow_query_log_file’;

在終端命令行執行如下命令：

ls slow_query_log_file.*

對於發現的每一個文件，執行如下命令：

ls -l slow_query_log_file | egrep “^-[r|w]{2}-[r|w]{2}—-\s*.*$”

根據輸出確認日誌文件的許可權設置是否存在問題。

對於每個日誌文件，修改其許可權和屬組如下：

chmod 660 log file

chown mysql:mysql log file

3.5控制通用日誌文件的許可權

Mysql命令行下執行如下命令：

show variables like ‘general_log_file’;

在終端命令行執行如下命令：

ls general_log_file.*

對於發現的每一個文件，執行如下命令：

ls -l general_log_file | egrep “^-[r|w]{2}-[r|w]{2}—-\s*.*$”

根據輸出確認日誌文件的許可權設置是否存在問題。

對於每個日誌文件，修改其許可權和屬組如下：

chmod 660 log file

chown mysql:mysql log file

3.6控制審計日誌文件的許可權

Mysql命令行下執行如下命令：

show global variables where variable_name =  ‘audit_log_file’;

在終端執行如下命令：

ls -l audit_log_file | egrep “^-rw[-x]rw[-x][-r][-w][-x][ \t]*[0-9][ \t]*mysql[

\t]*mysql.*$”

根據輸出確認日誌文件的許可權設置是否存在問題。

對於每個日誌文件，修改其許可權和屬組如下：

chmod 660 audit_log_file

chown mysql:mysql audit_log_file

4、通用安全

4.1安裝最新的補丁

在mysql命令行下查詢MySQL的版本：

SHOW VARIABLES WHERE Variable_name LIKE “version”;

確認是否由需要安裝的補丁包，如果有請安裝。

4.2 刪除test資料庫

Mysql資料庫默認安裝好後，存在一個名為test的資料庫，如果存在，請執行如下命令刪除：

Drop database 「test」

4.3 確保讀取本地文件的參數設置為失效

Mysql命令行下，使用如下命令：

SHOW VARIABLES WHERE Variable_name = ‘local_infile’;

查看結果是否為OFF。

如果該命令為ON，則資料庫用戶可以通過LOAD DATA INFILE 或者 SELECT local_file 讀取到資料庫所在操作系統本地的文件，在這種情況下，需要在mysql配置文件中新增一行：

Local-infile=0；

然後重啟資料庫服務。

5、許可權配置

5.1控制可以訪問所有資料庫的賬號

Mysql資料庫下的user表和db表中存放著可以授予資料庫用戶的許可權，確保只有管理員賬號才能訪問所有資料庫。可以訪問mysql資料庫的用戶或許可以查看密碼哈希值、修改用戶許可權等等。

使用如下sql語句：

SELECT user, host FROM mysql.user

WHERE (Select_priv = ‘Y’) OR (Insert_priv = ‘Y’) OR (Update_priv = ‘Y’)

OR (Delete_priv = ‘Y’)  OR (Create_priv = ‘Y’)  OR (Drop_priv = ‘Y’);

SELECT user, host FROM mysql.db WHERE db = ‘mysql’

AND ((Select_priv = ‘Y’) OR (Insert_priv = ‘Y’) OR (Update_priv = ‘Y’)

OR (Delete_priv = ‘Y’) OR (Create_priv = ‘Y’) OR (Drop_priv = ‘Y’));

確保返回結果只能是資料庫管理員賬號。

5.2限制非管理員用戶的許可權

Mysql.user表中的許可權列有：

file_priv：表示是否允許用戶讀取資料庫所在主機的本地文件；

Process：表示是否允許用戶查詢所有用戶的命令執行信息；

Super_priv：表示用戶是否有設置全局變數、管理員調試等高級別許可權；

Shutdown_priv：表示用戶是否可以關閉資料庫；

Create_user_priv：表示用戶是否可以創建或刪除其他用戶；

Grant_priv：表示用戶是否可以修改其他用戶的許可權；

應確保只有資料庫管理員才有上述許可權，使用如下sql語句查看擁有各個許可權的資料庫賬號：

select user, host from mysql.user where File_priv = ‘Y’;

select user, host from mysql.user where Process_priv = ‘Y’;

select user, host from mysql.user where Process_priv = ‘Y’;

SELECT user, host FROM mysql.user WHERE Shutdown_priv = ‘Y’;

SELECT user, host FROM mysql.user WHERE Create_user_priv = ‘Y’;

SELECT user, host FROM mysql.user WHERE Grant_priv = ‘Y’;

SELECT user, host FROM mysql.db WHERE Grant_priv = ‘Y’;

確保查詢結果中不存在非管理員用戶。

如果存在非管理員用戶，使用如下命令進行許可權回收：

REVOKE FILE ON *.* FROM ‘user’;

REVOKE PROCESS ON *.* FROM ‘user’;

REVOKE SUPER ON *.* FROM ‘user’;

REVOKE SHUTDOWN ON *.* FROM ‘user’;

REVOKE CREATE USER ON *.* FROM ‘user’;

REVOKE GRANT OPTION ON *.* FROM user;

其中user為上述查詢到的非管理員用戶。

5.3合理控制DML/DDL操作授權

DML/DDL語句包括創建或修改資料庫結構的許可權，例如insert、update、delete、create、drop和alter語句，在任何資料庫中都要控制用戶的此類許可權，確保只授權給有業務需求的非管理員用戶。Mysql命令行下執行如下命令：

SELECT User,Host,Db FROM mysql.db WHERE Select_priv=’Y’

OR Insert_priv=’Y’ OR Update_priv=’Y’ OR Delete_priv=’Y’ OR Create_priv=’Y’

OR Drop_priv=’Y’ OR Alter_priv=’Y’;

上述查詢到的用戶只能對特地的資料庫才有相關的許可權，使用如下命令進行相關許可權的回收：

REVOKE SELECT ON host.database FROM user;

REVOKE INSERT ON host.database FROM user;

REVOKE UPDATE ON host.database FROM user;

REVOKE DELETE ON host.database FROM user;

REVOKE CREATE ON host.database FROM user;

REVOKE DROP ON host.database FROM user;

REVOKE ALTER ON host.database FROM user;

其中user為查詢到的未授權的用戶，host為相關主機，database為相關資料庫。

6、審計和日誌

6.1開啟錯誤日誌審計功能

錯誤日誌包括資料庫運行和停止過程中的一系列活動信息，有助於分析資料庫運行過程中的一些異常活動，一般情況下需要開啟錯誤日誌記錄功能，使用如下命令查詢：

SHOW variables LIKE ‘log_error’;

確保返回結果為非空，如果為空，需要在mysql資料庫配置文件中增加相關配置。

6.2確保日誌存放在非系統區域

日誌文件隨著資料庫的運行會不斷增加，如果存放在系統區域，則會影響系統的正常運行，使用如下命令進行查詢：

SELECT @@global.log_bin_basename;

確保返回結果不是如下路徑：/、/var、/usr

6.3關閉原始日誌功能

原始日誌選項會決定一些敏感信息是否會被明文寫進日誌中，例如查詢日誌、慢查詢日誌、二進位日誌，確保資料庫配置文件中存在如下配置項：

Log-raw = OFF

7、認證

7.1 Old_passwords環境變數設置

Old_passwords決定了使用PASSWORD()函數和IDENTIFIED BY 、CREATE USER 、GRANT 等語句是時的hash演算法：

0 – authenticate with the mysql_native_password plugin

1 – authenticate with the mysql_old_password plugin

2 – authenticate with the sha256_password plugin

設置為mysql_old_password代表弱hash演算法，可以快速通過密碼字典進行暴力破解。使用如下命令查詢相關值：

SHOW VARIABLES WHERE Variable_name = ‘old_passwords’;

確保返回值不為1。

7.2 secure_auth 選項設置

如果客戶端採用Old_passwords發起連接請求，如果伺服器端設置了secure_auth，則客戶端會拒絕連接請求，可以根據安全需求在配置文件中做相應配置。

7.3 密碼保存

確保密碼沒有明文保存在全局配置文件中。

7.4 確保所有用戶都要求使用非空密碼登錄

執行如下語句查詢是否有用戶不需要密碼即可登錄：

SELECT User,host

FROM mysql.user

WHERE (plugin IN(‘mysql_native_password’, ‘mysql_old_password’)

AND (LENGTH(Password) = 0

OR Password IS NULL))

OR (plugin=’sha256_password’ AND LENGTH(authentication_string) = 0);

7.5不存在空賬號

使用如下命令查詢是否存在空賬號：

SELECT user,host FROM mysql.user WHERE user = ”;

8、網路設置

如果mysql資料庫伺服器與應用是跨信任域部署的，則需要考慮在資料庫伺服器與應用伺服器之間建立ssl通道進行數據傳輸，不過這種場景一般很少見，在此不詳細描述。

9、資料庫備份

mysql 變數設置問題

mysql變數的術語分類：

1.用戶變數：以”@”開始，形式為”@變數名”

用戶變數跟mysql客戶端是綁定的，設置的變數，只對當前用戶使用的客戶端生效

2.全局變數：定義時，以如下兩種形式出現，set

GLOBAL

變數名

或者

set

@@global.變數名，對所有客戶端生效。只有具有super許可權才可以設置全局變數

3.會話變數：只對連接的客戶端有效。

4.局部變數：作用範圍在begin到end語句塊之間。在該語句塊里設置的變數

declare語句專門用於定義局部變數。set語句是設置不同類型的變數，包括會話變數和全局變數

通俗理解術語之間的區別：

用戶定義的變數就叫用戶變數。這樣理解的話，會話變數和全局變數都可以是用戶定義的變數。只是他們是對當前客戶端生效還是對所有客戶端生效的區別了。所以，用戶變數包括了會話變數和全局變數

局部變數與用戶變數的區分在於兩點:

1.

用戶變數是以”@”開頭的。局部變數沒有這個符號。

2.

定義變數不同。用戶變數使用set語句，局部變數使用declare語句定義

3.

作用範圍。局部變數只在begin-end語句塊之間有效。在begin-end語句塊運行完之後，局部變數就消失了。

所以，最後它們之間的層次關係是：變數包括局部變數和用戶變數。用戶變數包括會話變數和全局變數。

使用備忘，set

@var

若沒有指定GLOBAL

或SESSION

，那麼默認將會定義用戶變數

兩種方式定義用戶變數：

1.”=”,如

set

@a

=3,@a:=5

2.”:=”。select常常這樣使用

總結：使用select

和set設置變數的區別，set可以使用以上兩種形式設置變數。而select只能使用”:=”的形式設置變數

實踐積累：用戶變數在mysql客戶端退出後，會自動消失。之後我打開客戶端，使用”select

@a;”

顯示變了的值為null。說明，未定義的變數初始化是null

實際中的問題

設置常量對group_concat()的配置影響：

SET

@@GROUP_CONCAT_MAX_LEN=4

手冊中提到設置的語法是這樣的：

SET

[SESSION

|

GLOBAL]

group_concat_max_len

=

val;

以下兩種形式都能達到達到同樣的效果，但是有什麼區別？

SET

@@global.GROUP_CONCAT_MAX_LEN=4;

global可以省略，那麼就變成了：SET

@@GROUP_CONCAT_MAX_LEN=4;

2011.2.25

之前的理解不怎麼準確。現在對加深理解後的地方進行總結。

mysql中變數的層次關係是:大體包括用戶變數和系統變數。系統變數包括系統會話變數和系統全局變數。

相互之間的區別：

因為用戶變數就是用戶定義的變數，系統變數就是mysql定義和維護的變數。所以，用戶變數與系統變數的區別在於，是誰在管理這些變數。mysql一啟動的時候就會讀取系統變數(這樣做目的是可以確定mysql的以何種機制或模式運行)。

系統會話變數與用戶變數都是在當前客戶端退出後消失。他們之間的區別可以這樣理解，雖然常常看到”set

@@varible”的形式去改變系統變數的值，但是並不涉及到定義系統變數。用戶變數是可以自己定義(初始化)。系統變數按照只是在改變值。

局部變數只在begin-end語句塊中定義並有效。執行到該語句塊之後就消失了。定義的方式有明顯的特點，使用declare語句。

使用系統變數理論上是可以使用兩種形式：

1.

前面帶有符號”@@”

2.

符號省略。比如我會看的如下形式：CURRENT_USER。但是，約定系統變數要使用”@@變數名”的形式，就是在前面加上符號”@@”

查看mysql是否為雙機

mysql雙機熱備實現原理分析，在本文經過深思熟慮和多次用不同的方式實測試後。最後在這篇文章中，用一個小例子來完成mysql雙機熱備的實現。

Mysql資料庫沒有增量備份的機制，當數據量太大的時候備份是一個很大的問題。還好mysql資料庫提供了一種主從備份的機制，其實就是把主資料庫的所有的數據同時寫到備份的資料庫中。實現mysql資料庫的熱備份。

要想實現雙機的熱備，首先要了解主從資料庫伺服器的版本的需求。要實現熱備mysql的版本都高於3.2。還有一個基本的原則就是作為從資料庫的數據版本可以高於主伺服器資料庫的版本，但是不可以低於主伺服器的資料庫版本。

當然要實現mysql雙機熱備，除了mysql本身自帶的REPLICATION功能可以實現外，也可以用Heartbeat這個開源軟體來實現。不過本文主要還是講如何用mysql自帶的REPLICATION來實現mysql雙機熱備的功能。

1. 準備伺服器

由於Mysql不同版本之間的（二進位日誌）binlog格式可能會不太一樣，因此最好的搭配組合是主（Master）伺服器的Mysql版本和從（Slave）伺服器版本相同或者更低，主伺服器的版本肯定不能高於從伺服器版本。

本次我用於測試的兩台伺服器版本都是Mysql-5.5.17。

2. Mysql 建立主－從伺服器雙機熱備配置步驟

2.1環境描述

A伺服器（主伺服器Master）：59.151.15.36

B伺服器（從伺服器Slave）：218.206.70.146

主從伺服器的Mysql版本皆為5.5.17

Linux環境下

將主伺服器需要同步的資料庫內容進行備份一份，上傳到從伺服器上，保證始初時兩伺服器中資料庫內容一致。

不過這裡說明下，由於我是利用Mysql在安裝後就有的資料庫test進行測試的，所以兩台伺服器裡面是沒有建立表的，只不分別在test裡面建立了同樣的一張空表tb_mobile;

Sql語句如下：

mysql create table tb_mobile( mobile VARCHAR(20) comment’手機號碼’, time timestamp DEFAULT now() comment’時間’ );

2.2 主伺服器Master配置

2.2.1 創建同步用戶

進入mysql操作界面，在主伺服器上為從伺服器建立一個連接帳戶，該帳戶必須授予REPLICATION SLAVE許可權。因為從mysql版本3.2以後就可以通過REPLICATION對其進行雙機熱備的功能操作。

操作指令如下：

mysql grant replication slave on *.* to ‘replicate’@’218.206.70.146’ identified by ‘123456’;

mysql flush privileges;

創建好同步連接帳戶後，我們可以通過在從伺服器（Slave）上用replicat帳戶對主伺服器（Master）資料庫進行訪問下，看下是否能連接成功。

在從伺服器（Slave）上輸入如下指令：

[root@YD146 ~]# mysql -h59.151.15.36 -ureplicate -p123456

如果出現下面的結果，則表示能登錄成功，說明可以對這兩台伺服器進行雙機熱備進行操作。

2.2.2 修改mysql配置文件

如果上面的準備工作做好，那邊我們就可以進行對mysql配置文件進行修改了，首先找到mysql配置所有在目錄，一般在安裝好mysql服務後，都會將配置文件複製一一份出來放到/ect目錄下面，並且配置文件命名為：my.cnf。即配置文件準確目錄為/etc/my.cnf

(Linux下用rpm包安裝的MySQL是不會安裝/etc/my.cnf文件的，

至於為什麼沒有這個文件而MySQL卻也能正常啟動和作用，在點有兩個說法，

第一種說法，my.cnf只是MySQL啟動時的一個參數文件，可以沒有它，這時MySQL會用內置的默認參數啟動，

第二種說法，MySQL在啟動時自動使用/usr/share/mysql目錄下的my-medium.cnf文件，這種說法僅限於rpm包安裝的MySQL，

解決方法，只需要複製一個/usr/share/mysql目錄下的my-medium.cnf文件到/etc目錄，並改名為my.cnf即可。)

找到配置文件my.cnf打開後，在[mysqld]下修改即可：

[mysqld]

server-id = 1

log-bin=mysql-bin //其中這兩行是本來就有的，可以不用動，添加下面兩行即可

binlog-do-db = test

binlog-ignore-db = mysql

2.2.3 重啟mysql服務

修改完配置文件後，保存後，重啟一下mysql服務，如果成功則沒問題。

2.2.4 查看主伺服器狀態

進入mysql服務後，可通過指令查看Master狀態，輸入如下指令：

注意看裡面的參數，特別前面兩個File和Position，在從伺服器（Slave）配置主從關係會有用到的。

註：這裡使用了鎖表，目的是為了產生環境中不讓進新的數據，好讓從伺服器定位同步位置，初次同步完成後，記得解鎖。

2.3 從伺服器Slave配置

2.3.1修改配置文件

因為這裡面是以主－從方式實現mysql雙機熱備的，所以在從伺服器就不用在建立同步帳戶了，直接打開配置文件my.cnf進行修改即可，道理還是同修改主伺服器上的一樣，只不過需要修改的參數不一樣而已。如下：

[mysqld]

server-id = 2

log-bin=mysql-bin

replicate-do-db = test

replicate-ignore-db = mysql,information_schema,performance_schema

2.3.2重啟mysql服務

修改完配置文件後，保存後，重啟一下mysql服務，如果成功則沒問題。

2.3.3用change mster 語句指定同步位置

這步是最關鍵的一步了，在進入mysql操作界面後，輸入如下指令：

mysqlstop slave; //先停步slave服務線程，這個是很重要的，如果不這樣做會造成以下操作不成功。

mysqlchange master to

master_host=’59.151.15.36′,master_user=’replicate’,master_password=’123456′,

master_log_file=’ mysql-bin.000016 ‘,master_log_pos=107;

註：master_log_file, master_log_pos由主伺服器（Master）查出的狀態值中確定。也就是剛剛叫注意的。master_log_file對應File, master_log_pos對應Position。Mysql 5.x以上版本已經不支持在配置文件中指定主伺服器相關選項。

遇到的問題，如果按上面步驟之後還出現如下情況：

則要重新設置slave。指令如下

mysqlstop slave;

mysqlreset slave;

之後停止slave線程重新開始。成功後，則可以開啟slave線程了。

mysqlstart slave;

2.3.4查看從伺服器（Slave）狀態

用如下指令進行查看

mysql show slave status\G;

查看下面兩項值均為Yes，即表示設置從伺服器成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

2.4 測試同步

之前開始已經說過了在資料庫test只有一個表tb_mobile沒有數據，我們可以先查看下兩伺服器的資料庫是否有數據：

Master:59.151.15.36

Slave:218.206.70.146

好了，現在可以在Master伺服器中插入數據看下是否能同步。

Master:59.151.15.36

Slave:218.206.70.146

可以從上面兩個截圖上看出，在Master伺服器上進行插入的數據在Slave伺服器可以查到，這就表示雙機熱備配置成功了。

3. Mysql 建立主－主伺服器雙機熱備配置步驟

伺服器還是用回現在這兩台伺服器

3.1創建同步用戶

同時在主從伺服器建立一個連接帳戶，該帳戶必須授予REPLIATION SLAVE許可權。這裡因為伺服器A和伺服器B互為主從，所以都要分別建立一個同步用戶。

伺服器A：

mysql grant replication slave on *.* to ‘replicate’@’218.206.70.146’ identified by ‘123456’;

mysql flush privileges;

伺服器B：

mysql grant replication slave on *.* to ‘replicate’@’59.151.15.36’ identified by ‘123456’;

mysql flush privileges;

3.2修改配置文件my.cnf

伺服器A

[mysqld]

server-id = 1

log-bin=mysql-bin

binlog-do-db = test

binlog-ignore-db = mysql

#主－主形式需要多添加的部分

log-slave-updates

sync_binlog = 1

auto_increment_offset = 1

auto_increment_increment = 2

replicate-do-db = test

replicate-ignore-db = mysql,information_schema

伺服器B：

[mysqld]

server-id = 2

log-bin=mysql-bin

master-slave need

replicate-do-db = test

replicate-ignore-db = mysql,information_schema,performance_schema

#主－主形式需要多添加的部分

binlog-do-db = test

binlog-ignore-db = mysql

log-slave-updates

sync_binlog = 1

auto_increment_offset = 2

auto_increment_increment = 2

3.3分別重啟A伺服器和B伺服器上的mysql服務

重啟伺服器方式和上面的一樣，這裡就不做講解了。

3.4分別查A伺服器和B伺服器作為主伺服器的狀態

伺服器A：

伺服器B：

3.5分別在A伺服器和B伺服器上用change master to 指定同步位置

伺服器A：

mysqlchange master to

master_host=’218.206.70.146′,master_user=’replicate’,master_password=’123456′,

master_log_file=’ mysql-bin.000011 ‘,master_log_pos=497;

伺服器B：

mysqlchange master to

master_host=’59.151.15.36′,master_user=’replicate’,master_password=’123456′,

master_log_file=’ mysql-bin.000016 ‘,master_log_pos=107;

3.6 分別在A和B伺服器上重啟從服務線程

mysqlstart slave;

3.7 分別在A和B伺服器上查看從伺服器狀態

mysqlshow slave status\G;

查看下面兩項值均為Yes，即表示設置從伺服器成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.8 測試主－主同步例子

測試伺服器A：

在伺服器A上插入一條語句如下圖所示：

之後在伺服器B上查看是否同步如下圖所示：

測試伺服器B：

在伺服器B上插入一條語句如下圖所示：

然後在從伺服器A上查看是否有同步數據如下圖所示：

最後從結果可以看出主－主形式的雙機熱備是能成功實現的。

4. 配置參數說明

Server-id

ID值唯一的標識了複製群集中的主從伺服器，因此它們必須各不相同。Master_id必須為1到232－1之間的一個正整數值，slave_id值必須為2到232－1之間的一個正整數值。

Log-bin

表示打開binlog，打開該選項才可以通過I/O寫到Slave的relay-log，也是可以進行replication的前提。

Binlog-do-db

表示需要記錄二進位日誌的資料庫。如果有多個數據可以用逗號分隔，或者使用多個binlog-do-dg選項。

Binglog-ingore-db

表示不需要記錄二進位日誌的資料庫，如果有多個資料庫可用逗號分隔，或者使用多binglog-ignore-db選項。

Replicate-do-db

表示需要同步的資料庫，如果有多個數據可用逗號分隔，或者使用多個replicate-do-db選項。

Replicate-ignore-db

表示不需要同步的資料庫，如果有多個資料庫可用逗號分隔，或者使用多個replicate-ignore-db選項。

Master-connect-retry

master-connect-retry=n表示從伺服器與主伺服器的連接沒有成功，則等待n秒（s）後再進行管理方式（默認設置是60s）。如果從伺服器存在mater.info文件，它將忽略些選項。

Log-slave-updates

配置從庫上的更新操作是否寫入二進位文件，如果這台從庫，還要做其他從庫的主庫，那麼就需要打這個參數，以便從庫的從庫能夠進行日誌同步。

Slave-skip-errors

在複製過程，由於各種原因導致binglo中的sql出錯，默認情況下，從庫會停止複製，要用戶介入。可以設置slave-skip-errors來定義錯誤號，如果複製過程中遇到的錯誤是定義的錯誤號，便可以路過。如果從庫是用來做備份，設置這個參數會存在數據不一致，不要使用。如果是分擔主庫的查詢壓力，可以考慮。

Sync_binlog=1 Or N

Sync_binlog的默認值是0，這種模式下，MySQL不會同步到磁碟中去。這樣的話，Mysql依賴操作系統來刷新二進位日誌binary log，就像操作系統刷新其他文件的機制一樣。因此如果操作系統或機器（不僅僅是Mysql伺服器）崩潰，有可能binlog中最後的語句丟失了。要想防止這種情況，可以使用sync_binlog全局變數，使binlog在每N次binlog寫入後與硬碟同步。當sync_binlog變數設置為1是最安全的，因為在crash崩潰的情況下，你的二進位日誌binary log只有可能丟失最多一個語句或者一個事務。但是，這也是最慢的一種方式（除非磁碟有使用帶蓄電池後備電源的緩存cache,使得同步到磁碟的操作非常快）。

即使sync_binlog設置為1，出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，Mysql伺服器處理COMMIT語句，它將整個事務寫入binlog並將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB回滾，但仍然存在binlog中。可以用-innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。（注釋：在Mysql 5.1版本中不需要-innodb-safe-binlog；由於引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的binlog(sync_binlog=1)和（默認情況為真）InnoDB日誌與硬碟同步，該選項的效果是崩潰後重啟時，在滾回事務後，Mysql伺服器從binlog剪切回滾的InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數據等，並使從伺服器保持與主伺服器保持同步（不接收回滾的語句）。

Auto_increment_offset和Auto_increment_increment

Auto_increment_increment和auto_increment_offset用於主－主伺服器（master-to-master）複製，並可以用來控制AUTO_INCREMENT列的操作。兩個變數均可以設置為全局或局部變數，並且假定每個值都可以為1到65,535之間的整數值。將其中一個變數設置為0會使該變數為1。

這兩個變數影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值，auto_increment_offset確定AUTO_INCREMENT列值的起點。

如果auto_increment_offset的值大於auto_increment_increment的值，則auto_increment_offset的值被忽略。例如：表內已有一些數據，就會用現在已有的最大自增值做為初始值。

如何修改MYSQL的系統變數

MySQL系統變數(system variables)實際上是一些系統參數，用於初始化或設定資料庫對系統資源的佔用，文件存放位置等等。mysql show variables like “log”;+—————+——-+Variable_name Value +—————+——-+log OFF +—————+——-+1 row in set (0f[mysqld]wait_timeout=10# service mysqld restart不過這個方法太生硬了, 線上服務重啟無論如何都應該儘可能避免f[mysqld]wait_timeout=103) 設置會話變數方法: 在命令行里通過SET來設置如果要修改會話變數值, 可以指定”SESSION”或者”@@session.”或者”@@”或者”LOCAL”或者”@@local.”, 或者什麼都不使用. mysql set wait_timeout=10;ormysql set session wait_timeout=10;ormysql set local wait_timeout=10;ormysql set @@wait_timeout=10;ormysql set @@session.wait_timeout=10;ormysql set @@local.wait_timeout=10;然後查看設置是否成功:mysql select @@wait_timeout;ormysql select @@session.wait_timeout;ormysql select @@local.wait_timeout;ormysql show variables like ‘wait_timeout’;ormysql show local variables like ‘wait_timeout’;ormysql show session variables like ‘wait_timeout’;+—————+——-+Variable_name Value +—————+——-+wait_timeout 10 +—————+——-+4) 會話變數和全局變數轉換方法: 在命令行里通過SET來設置將會話變數值設置為對應的全局變數值呢:mysql set @@session.wait_timeout=@@global.wait_timeout;將會話變數值設置為MySQL編譯時的默認值(wait_timeout=28800):mysql set wait_timeout=DEFAULT;這裡要注意的是, 並不是所有的系統變數都能被設置為DEFAULT, 如果設置這些變數為DEFAULT則會返回錯誤. 參考資料：

如何將linux中mysql備份恢復

一般是即時備份。做主從。或者是每天增量備份。

本文是在linux下,mysql 4.1.14版本下測試的,經過適當修改可能適合mysql 4.0,5.0及其其他版本.

本文適合於沒有啟動複製功能的mysql,如果啟動了複製,可能不需要採取這種備份策略或者需要修改相關參數.

每個人的備份策略都可能不同,所以請根據實際情況修改,做到舉一反三,不要照搬照抄,可能會造成不必要的損失.

希望你明白這個腳本要幹什麼工作!

腳本描述

每7天備份一次所有數據,每天備份binlog,也就是增量備份.

(如果數據少,每天備份一次完整數據即可,可能沒必要做增量備份)

作者對shell腳本不太熟悉,所以很多地方寫的很笨 :)

開啟 bin log

在mysql 4.1版本中,默認只有錯誤日誌,沒有其他日誌.可以通過修改配置打開bin log.方法很多,其中一個是在/etc/my.cnf中的mysqld部分加入:

[mysqld]

log-bin

這個日誌的主要作用是增量備份或者複製(可能還有其他用途).

如果想增量備份,必須打開這個日誌.

對於資料庫操作頻繁的mysql,這個日誌會變得很大,而且可能會有多個.

在資料庫中flush-logs,或者使用mysqladmin,mysqldump調用flush-logs後並且使用參數delete-master-logs,這些日誌文件會消失,併產生新的日誌文件(開始是空的).

所以如果從來不備份,開啟日誌可能沒有必要.

完整備份的同時可以調用flush-logs,增量備份之前flush-logs,以便備份最新的數據.

完整備份腳本

如果資料庫數據比較多,我們一般是幾天或者一周備份一次數據,以免影響應用運行,如果數據量比較小,那麼一天備份一次也無所謂了.

#!/bin/sh

BakDir=/backup/mysql

LogFile=/backup/mysql/mysqlbak.log

DATE=`date +%Y%m%d`

echo ” ” $LogFile

echo ” ” $LogFile

echo “——————————————-” $LogFile

echo $(date +”%y-%m-%d %H:%M:%S”) $LogFile

echo “————————–” $LogFile

cd $BakDir

DumpFile=$DATE.sql

GZDumpFile=$DATE.sql.tgz

mysqldump –quick –all-databases –flush-logs

–delete-master-logs –lock-all-tables

$DumpFile

echo “Dump Done” $LogFile

tar czvf $GZDumpFile $DumpFile $LogFile 21

echo “[$GZDumpFile]Backup Success!” $LogFile

rm -f $DumpFile

#delete previous daily backup files:採用增量備份的文件,如果完整備份後,則刪除增量備份的文件.

cd $BakDir/daily

rm -f *

cd $BakDir

echo “Backup Done!”

echo “please Check $BakDir Directory!”

echo “copy it to your local disk or ftp to somewhere !!!”

ls -al $BakDir

上面的腳本把mysql備份到本地的/backup/mysql目錄,增量備份的文件放在/backup/mysql/daily目錄下.

注意:上面的腳本並沒有把備份後的文件傳送到其他遠程計算機,也沒有刪除幾天前的備份文件:需要用戶增加相關腳本,或者手動操作.

增量備份

增量備份的數據量比較小,但是要在完整備份的基礎上操作,用戶可以在時間和成本上權衡,選擇最有利於自己的方式.

增量備份使用bin log,腳本如下:

#!/bin/sh

#

# mysql binlog backup script

#

/usr/bin/mysqladmin flush-logs

DATADIR=/var/lib/mysql

BAKDIR=/backup/mysql/daily

###如果你做了特殊設置,請修改此處或者修改應用此變數的行:預設取機器名,mysql預設也是取機器名

HOSTNAME=`uname -n`

cd $DATADIR

FILELIST=`cat $HOSTNAME-bin.index`

##計算行數,也就是文件數

COUNTER=0

for file in $FILELIST

do

COUNTER=`expr $COUNTER + 1 `

done

NextNum=0

for file in $FILELIST

do

base=`basename $file`

NextNum=`expr $NextNum + 1`

if [ $NextNum -eq $COUNTER ]

then

echo “skip lastest”

else

dest=$BAKDIR/$base

if(test -e $dest)

then

echo “skip exist $base”

else

echo “copying $base”

cp $base $BAKDIR

fi

fi

done

echo “backup mysql binlog ok”

增量備份腳本是備份前flush-logs,mysql會自動把內存中的日誌放到文件里,然後生成一個新的日誌文件,所以我們只需要備份前面的幾個即可,也就是不備份最後一個.

因為從上次備份到本次備份也可能會有多個日誌文件生成,所以要檢測文件,如果已經備份過,就不用備份了.

注:同樣,用戶也需要自己遠程傳送,不過不需要刪除了,完整備份後程序會自動生成.

訪問設置

腳本寫完了,為了能讓腳本運行,還需要設置對應的用戶名和密碼,mysqladmin和mysqldump都是需要用戶名和密碼的,當然可以寫在腳本中,但是修改起來不太方便,假設我們用系統的root用戶來運行此腳本,那麼我們需要在/root(也就是root用戶的home目錄)創建一個.my.cnf文件,內容如下

[mysqladmin]

password =password

user= root

[mysqldump]

user=root

password=password

注:設置本文件只有root可讀.(chmod 600 .my.cnf )

此文件說明程序使用mysql的root用戶備份數據,密碼是對應的設置.這樣就不需要在腳本里寫用戶名和密碼了.

自動運行

為了讓備份程序自動運行,我們需要把它加入crontab.

有2種方法,一種是把腳本根據自己的選擇放入到/etc/cron.daily,/etc/cron.weekly這麼目錄里.

一種是使用crontab -e放入到root用戶的計劃任務里,例如完整備份每周日凌晨3點運行,日常備份每周一-周六凌晨3點運行.