MySQL主從備份配置執行個體

標籤：同步   開啟   日記   second   connect   def   alter   ons   選擇   

情境:

1、主伺服器192.168.0.225、從伺服器192.168.0.226。其中，主伺服器上已有資料。

2、主從伺服器上的mysql版本及安裝配置相同。

 

一、主從備份的原理:

主伺服器資料庫的每次操作都會記錄在二進位記錄檔mysql-bin.xxx中。從伺服器的I/O線程使用專用帳號登陸到主伺服器中讀取該二進位檔案，並將檔案內容寫入到自己本地的中繼日誌relay-log檔案中。然後從伺服器的SQL線程會根據中繼日誌中的內容執行SQL語句。

這要求兩台伺服器有同樣的初態。

 

二、同步初態:

1、將主伺服器要同步的資料庫加鎖，避免同步時發生改變:

>use database_name;>flush tables with read lock;

2、使用mysqldump工具匯出資料:

mysqldump -uroot -pxxx database_name >database_name.sql

3、備份完成後，解鎖資料庫:

>unlock tables;

4、將初始資料匯入從資料庫:

>create database database_name;>use database_name;>source database_name.sql;

 完成以上操作後，主從伺服器就有一樣的初態了。

三、主從同步設定:

1、配置從資料庫:

/etc/my.cnf主要配置如下:

log-bin=mysql-bin                                 #開啟二進位日誌    server-id       = 2                               #主要資料庫id為1,不能相同。replicate_wild_do_table=test.%                    #只同步test庫下的表relay_log=mysqld-relay-bin                        #記錄中繼日誌log-slave-updates=YES                             #從伺服器同步後記錄日誌

修改完成後重啟mysql服務。

2、查看主伺服器日記記錄位置:

>show master status\G

顯示內容如下:

***************** 1. row ****************            File: mysql-bin.000001       #目前記錄的日誌        Position: 80647293               ＃日誌中記錄的位置    Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

3、主伺服器建立允許從伺服器同步資料的賬戶:

>grant replication slave on *.* to ‘user_name‘@‘192.168.0.226‘ identified by ‘ahaii‘;

4、從伺服器開啟同步:

>change master to                   master_host=‘192.168.0.225‘,                             master_user=‘user_name‘,                  master_password=‘ahaii‘,                 master_log_file=‘mysql-bin.000001‘,                  master_log_pos=80647293;

配置完以上後，重啟從伺服器mysql服務。

5、查看從伺服器是否已經成功開啟同步:

>show slave status\G

顯示如下:

*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.0.225                  Master_User: user_name                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 1114               Relay_Log_File: mysqld-relay-bin.000004                Relay_Log_Pos: 1260        Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table: test.%  Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 1114              Relay_Log_Space: 1563              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)

其中:Slave_IO_Running和Slave_SQL_Running的狀態都是YES,說明同步開啟成功。

現在就可以去主伺服器上的test庫下建立表開測試同步了。

 

 MySQL主主備份配置執行個體

在主從備份配置完畢的基礎上實現雙主備份:

一、在從伺服器上建立用於同步的帳號:

>grant ...

二、修改主伺服器mysql.cnf，加入

replicate_wild_do_table=test.%relay_log=mysqld-relay-binlog-slave-updates=YES

三、記錄從伺服器二進位記錄檔名與位置:

>show master status\G

四、主伺服器開啟同步設定:

>change master to             master_host=‘192.168.0.226‘,             master_user=‘user_name‘,            master_password=‘ahaii‘,            master_log_file=‘mysql-bin.000009‘,            master_log_pos=107;                

 

然後重啟mysqld服務。

 

以上就是mysql主從、主主設定的過程。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

 

四、疑惑記錄:

1、關於my.cnf設定檔中binlog-format的值:

mysql的binlog-format有三種格式，分別是:row、statement和mixed

　　1.1、row:基於行的複製(row-based replicate,RBP)

　　優點:

　　binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。所以rowlevel的日誌內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的預存程序，或function，以及trigger的調用和觸發無法被正確複製的問題

　　缺點:

　　所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日誌量會很大，特別是當執行alter table之類的語句的時候，由於表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日誌中。

　　1.2、statement:基於SQL語句的複製(statement-based replicate, SBR)

　　每一條會修改資料庫的SQL語句都會被記錄在binlog中。

　　優點:

　　不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高效能。(相比row能節約多少效能與日誌量，這個取決於應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日誌量還小於Statement產生的日誌量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日誌，因此在考慮是否使用ROW格式日誌時應該跟據應用的實際情況，其所產生的日誌量會增加多少，以及帶來的IO效能問題。)

　　缺點:

　　由於記錄的只是執行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的複製,像一些特定函數功能，slave可與master上要保持一致會有很多相關問題(如sleep()函數， last_insert_id()，以及user-defined functions(udf)會出現問題).

　　使用以下函數的語句也無法被複製：

　　* LOAD_FILE()

　　* UUID()

　　* USER()

　　* FOUND_ROWS()

　　* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)

　　同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖。

　　1.3、mixed:混合模式複製(mixed-based replicate,MBR)

　　mysql的預設模式。

　　mixed模式是以上兩種模式的混合使用，一般的語句修改使用statment格式儲存binlog，如一些函數，statement無法完成主從複製的操作，則採用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了最佳化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改資料的語句，還是會記錄所有行的變更。

2、關於複製參數:replicate-do-db、replicate-do-table、replicate-wild-do-table

MySQL官網的介紹:https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html

　　2.1、replicate-do-db:在從伺服器上設定需要同步的資料庫，要同步多個庫時可以寫多行，每行一個。

　　缺點:不適用於跨庫資料更新的複製。

　　2.2、replicate-do-table:在從伺服器上設定需要同步的表，格式為db_name.tb_name。每行一個，同步多個表時需要寫多行。

　　缺點:不適用於跨庫資料更新的複製。

　　2.3、replicate-wild-do-table:在從伺服器上設定需要同步的資料庫，格式為db_name.tb_name。每行一個，同步多個表時需要寫多行。可使用萬用字元如"%"、"_"，來匹配多個表，如同步所有以share字串開頭的資料庫的所有use字串開頭的表 :replicate-wild-do-db=share%.user%。

　　支援跨庫資料更新的複製。

參考連結：https://www.cnblogs.com/ahaii/p/6307648.html

 

MySQL主從備份配置執行個體