MySQL主從備份配置實(shí)例

場(chǎng)景:

1,、主服務(wù)器192.168.0.225,、從服務(wù)器192.168.0.226,。其中，主服務(wù)器上已有數(shù)據(jù),。

2,、主從服務(wù)器上的mysql版本及安裝配置相同。

一,、主從備份的原理:

主服務(wù)器數(shù)據(jù)庫(kù)的每次操作都會(huì)記錄在二進(jìn)制日志文件mysql-bin.xxx中,。從服務(wù)器的I/O線(xiàn)程使用專(zhuān)用賬號(hào)登陸到主服務(wù)器中讀取該二進(jìn)制文件，并將文件內(nèi)容寫(xiě)入到自己本地的中繼日志relay-log文件中,。然后從服務(wù)器的SQL線(xiàn)程會(huì)根據(jù)中繼日志中的內(nèi)容執(zhí)行SQL語(yǔ)句,。

這要求兩臺(tái)服務(wù)器有同樣的初態(tài)。

二,、同步初態(tài):

1,、將主服務(wù)器要同步的數(shù)據(jù)庫(kù)加鎖，避免同步時(shí)發(fā)生改變:

>use database_name;
>flush tables with read lock;

2,、使用mysqldump工具導(dǎo)出數(shù)據(jù):

mysqldump -uroot -pxxx database_name >database_name.sql

3,、備份完成后，解鎖數(shù)據(jù)庫(kù):

>unlock tables;

4,、將初始數(shù)據(jù)導(dǎo)入從數(shù)據(jù)庫(kù):

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

 完成以上操作后,，主從服務(wù)器就有一樣的初態(tài)了。

三,、主從同步設(shè)置:

1,、配置從數(shù)據(jù)庫(kù):

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

log-bin=mysql-bin                                 #開(kāi)啟二進(jìn)制日志
    

server-id       = 2                               #主數(shù)據(jù)庫(kù)id為1,不能相同。
replicate_wild_do_table=test.%                    #只同步test庫(kù)下的表
relay_log=mysqld-relay-bin                        #記錄中繼日志
log-slave-updates=YES                             #從服務(wù)器同步后記錄日志

修改完成后重啟mysql服務(wù),。

2,、查看主服務(wù)器日記記錄位置:

>show master status\G

顯示內(nèi)容如下:

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

3、主服務(wù)器創(chuàng)建允許從服務(wù)器同步數(shù)據(jù)的賬戶(hù):

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

4,、從服務(wù)器開(kāi)啟同步:

>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;

配置完以上后,，重啟從服務(wù)器mysql服務(wù)。

5,、查看從服務(wù)器是否已經(jīng)成功開(kāi)啟同步:

>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: 0
Master_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: 1
1 row in set (0.00 sec)

其中:Slave_IO_Running和Slave_SQL_Running的狀態(tài)都是YES,說(shuō)明同步開(kāi)啟成功,。

現(xiàn)在就可以去主服務(wù)器上的test庫(kù)下創(chuàng)建表開(kāi)測(cè)試同步了。

 MySQL主主備份配置實(shí)例

在主從備份配置完畢的基礎(chǔ)上實(shí)現(xiàn)雙主備份:

一,、在從服務(wù)器上創(chuàng)建用于同步的賬號(hào):

>grant ...

二,、修改主服務(wù)器mysql.cnf，加入

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

三,、記錄從服務(wù)器二進(jìn)制日志文件名與位置:

>show master status\G

四,、主服務(wù)器開(kāi)啟同步設(shè)置:

>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服務(wù)。

以上就是mysql主從,、主主設(shè)置的過(guò)程,。

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

四,、疑惑記錄:

1、關(guān)于my.cnf配置文件中binlog-format的值:

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

　　1.1、row:基于行的復(fù)制(row-based replicate,RBP)

　　優(yōu)點(diǎn):

　　binlog中可以不記錄執(zhí)行的sql語(yǔ)句的上下文相關(guān)的信息,，僅需要記錄那一條記錄被修改成什么了,。所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程,，或function,，以及trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題

　　缺點(diǎn):

　　所有的執(zhí)行的語(yǔ)句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來(lái)記錄,，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容,比如一條update語(yǔ)句,，修改多條記錄，則binlog中每一條修改都會(huì)有記錄,，這樣造成binlog日志量會(huì)很大,，特別是當(dāng)執(zhí)行alter table之類(lèi)的語(yǔ)句的時(shí)候，由于表結(jié)構(gòu)修改,，每條記錄都發(fā)生改變,，那么該表每一條記錄都會(huì)記錄到日志中。

　　1.2,、statement:基于SQL語(yǔ)句的復(fù)制(statement-based replicate, SBR)

　　每一條會(huì)修改數(shù)據(jù)庫(kù)的SQL語(yǔ)句都會(huì)被記錄在binlog中。

　　優(yōu)點(diǎn):

　　不需要記錄每一行的變化,，減少了binlog日志量,，節(jié)約了IO，提高性能,。(相比row能節(jié)約多少性能與日志量,，這個(gè)取決于應(yīng)用的SQL情況，正常同一條記錄修改或者插入row格式所產(chǎn)生的日志量還小于Statement產(chǎn)生的日志量,，但是考慮到如果帶條件的update操作,，以及整表刪除，alter表等操作,，ROW格式會(huì)產(chǎn)生大量日志,，因此在考慮是否使用ROW格式日志時(shí)應(yīng)該跟據(jù)應(yīng)用的實(shí)際情況，其所產(chǎn)生的日志量會(huì)增加多少,，以及帶來(lái)的IO性能問(wèn)題,。)

　　缺點(diǎn):

　　由于記錄的只是執(zhí)行語(yǔ)句，為了這些語(yǔ)句能在slave上正確運(yùn)行,，因此還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的一些相關(guān)信息,，以保證所有語(yǔ)句能在slave得到和在master端執(zhí)行時(shí)候相同 的結(jié)果,。另外mysql 的復(fù)制,像一些特定函數(shù)功能，slave可與master上要保持一致會(huì)有很多相關(guān)問(wèn)題(如sleep()函數(shù),， last_insert_id(),，以及user-defined functions(udf)會(huì)出現(xiàn)問(wèn)題).

　　使用以下函數(shù)的語(yǔ)句也無(wú)法被復(fù)制：

　　* LOAD_FILE()

　　* UUID()

　　* USER()

　　* FOUND_ROWS()

　　* SYSDATE() (除非啟動(dòng)時(shí)啟用了 --sysdate-is-now 選項(xiàng))

　　同時(shí)在INSERT ...SELECT 會(huì)產(chǎn)生比 RBR 更多的行級(jí)鎖。

　　1.3,、mixed:混合模式復(fù)制(mixed-based replicate,MBR)

　　mysql的默認(rèn)模式,。

　　mixed模式是以上兩種模式的混合使用，一般的語(yǔ)句修改使用statment格式保存binlog,，如一些函數(shù),，statement無(wú)法完成主從復(fù)制的操作，則采用row格式保存binlog,MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式,，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊(duì)row level模式也被做了優(yōu)化,，并不是所有的修改都會(huì)以row level來(lái)記錄，像遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以statement模式來(lái)記錄,。至于update或者delete等修改數(shù)據(jù)的語(yǔ)句,，還是會(huì)記錄所有行的變更。

2,、關(guān)于復(fù)制參數(shù):replicate-do-db,、replicate-do-table、replicate-wild-do-table

MySQL官網(wǎng)的介紹:https://dev./doc/refman/5.5/en/replication-options-slave.html

　　2.1,、replicate-do-db:在從服務(wù)器上設(shè)置需要同步的數(shù)據(jù)庫(kù),，要同步多個(gè)庫(kù)時(shí)可以寫(xiě)多行，每行一個(gè),。

　　缺點(diǎn):不適用于跨庫(kù)數(shù)據(jù)更新的復(fù)制,。

　　2.2、replicate-do-table:在從服務(wù)器上設(shè)置需要同步的表,，格式為db_name.tb_name,。每行一個(gè)，同步多個(gè)表時(shí)需要寫(xiě)多行,。

　　缺點(diǎn):不適用于跨庫(kù)數(shù)據(jù)更新的復(fù)制,。

　　2.3、replicate-wild-do-table:在從服務(wù)器上設(shè)置需要同步的數(shù)據(jù)庫(kù),，格式為db_name.tb_name,。每行一個(gè)，同步多個(gè)表時(shí)需要寫(xiě)多行,?？墒褂猛ㄅ浞?%"、"_",，來(lái)匹配多個(gè)表,，如同步所有以share字符串開(kāi)頭的數(shù)據(jù)庫(kù)的所有use字符串開(kāi)頭的表 :replicate-wild-do-db=share%.user%,。

　　支持跨庫(kù)數(shù)據(jù)更新的復(fù)制。