云南網(wǎng)站建設(shè)創(chuàng)新企業(yè) 昆明多彩網(wǎng)絡(luò)公司

mysql自動(dòng)備份同步 主從策略即時(shí)備份

來(lái)源：昆明多彩網(wǎng)絡(luò)公司 日期：2011-02-24 閱讀： 發(fā)表評(píng)論

1、A、B兩臺(tái)mysql服務(wù)器，A為主服務(wù)器，B為從服務(wù)器（用來(lái)備份的服務(wù)器）。

2、可以先在主服務(wù)器新建一個(gè)給從服務(wù)器使用的帳戶：
sql命令命令為：GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* to 'backup@%' IDENTIFIED BY 'kmwzjs.com'; 可以將IP改成%，代表是所有的ip都能鏈接，或者10.%，則是10開(kāi)頭的ip，這條命令給于了同步數(shù)據(jù)庫(kù)從數(shù)據(jù)訪問(wèn)的所有權(quán)限。
但是如果需要使用load data from master命令來(lái)一次性復(fù)制主數(shù)據(jù)庫(kù)到從數(shù)據(jù)庫(kù)的話，需要賦予改帳戶所有權(quán)限，sql命令為：grant usage on *.* to 'backup'@%' identified by 'kmwzjs.com'。

4、修改主數(shù)據(jù)庫(kù)的my.ini配置內(nèi)容，在[mysqld]下面增加   
  ###下面這些是必須設(shè)置的###
server-id=1 #主數(shù)據(jù)庫(kù)的進(jìn)程，只要不和其他數(shù)據(jù)庫(kù)進(jìn)程代號(hào)沖突即可，一般是用服務(wù)器的最后ip來(lái)記錄
log-bin=F:/server-php/MySQL Server 5.1/logs/log-bin.log #開(kāi)啟主數(shù)據(jù)庫(kù)的二進(jìn)制日志（改日志只記錄數(shù)據(jù)庫(kù)的修改，不記錄查詢），從數(shù)據(jù)庫(kù)就是通過(guò)訪問(wèn)該日志來(lái)同步主數(shù)據(jù)庫(kù)的，必須開(kāi)啟 binlog_format=mixed  #加上這個(gè)，主數(shù)據(jù)庫(kù)修改數(shù)據(jù)就不會(huì)有警告了，默認(rèn)不是這個(gè)的設(shè)置的。

###這些是可選設(shè)置#####
   binlog-ignore-db  #指定要被同步的數(shù)據(jù)庫(kù)，如果不設(shè)置，則為所有的數(shù)據(jù)庫(kù)，官方建議在slave端使用，多個(gè)數(shù)據(jù)庫(kù)用,分割。
   binlog-do-db=aij  #不想被同步的數(shù)據(jù)庫(kù)，要想過(guò)濾一些不想同步的數(shù)據(jù)庫(kù)，官方建議在slave端使用，多個(gè)數(shù)據(jù)庫(kù)用,分割。
   master-connect-retry=60 #中斷重新連接時(shí)間間隔為60秒

5、修改從數(shù)據(jù)庫(kù)的my.ini配置內(nèi)容，在[mysqld]下面增加
 server-id=2 #從服務(wù)器B的ID值。注意不能和主服務(wù)器的ID值相同
 master-host=192.168.1.101 #主服務(wù)器的IP地址   
 master-user=backup #從服務(wù)器連接主服務(wù)器的帳號(hào)
 master-password=kmwzjs.com #從服務(wù)器連接主服務(wù)器的帳號(hào)密碼
 master-port=3306 #主服務(wù)器端口

######以下內(nèi)容為可選######
   replicate-do-db=mytest #需要同步的數(shù)據(jù)庫(kù)，多個(gè)數(shù)據(jù)庫(kù)用,分割
   binlog-ignore-db=mysql,information_schema #不需要同步的數(shù)據(jù)庫(kù)，多個(gè)數(shù)據(jù)庫(kù)用,分割
   log-bin=D:\mysqllog\log-bin.log #二進(jìn)制變更日志，適用與雙向同步，單向同步可以不設(shè)置
   master-connect-retry=60 #同步時(shí)間間隔為60秒
  
   如果從數(shù)據(jù)庫(kù)data文件夾下存在master.info、relay-log.info就先停止從數(shù)據(jù)庫(kù)的mysql（net stop mysql）、然后刪除這兩個(gè)文件，以后如果要修改主服務(wù)器和從服務(wù)器的my.ini配置，都要停止從服務(wù)器，然后刪除這兩個(gè)文件，因?yàn)閺臄?shù)據(jù)庫(kù)的不會(huì)重復(fù)生成這兩個(gè)文件。

6、這一步比較關(guān)鍵了，要將主服務(wù)器需要同步的數(shù)據(jù)庫(kù)都要拷貝到從服務(wù)器上面來(lái)，這里有兩種方式：
   第一種，將兩個(gè)服務(wù)器的mysql停止，將主服務(wù)器的data文件夾下面的數(shù)據(jù)庫(kù)文件拷貝到從數(shù)據(jù)庫(kù)的數(shù)據(jù)data文件夾下，然后啟動(dòng)兩個(gè)服務(wù)器。目的是先手動(dòng)同步兩個(gè)服務(wù)器的數(shù)據(jù)庫(kù)，注意拷貝的時(shí)候不要拷貝mysql和information_schema這個(gè)兩個(gè)數(shù)據(jù)庫(kù)。
   第二種方法，先重啟從服務(wù)器的mysql服務(wù)，在從服務(wù)器上使用stop slave;load data from master的sql語(yǔ)句，先停止同步，從服務(wù)器默認(rèn)是開(kāi)通同步的，然后直接將主服務(wù)器上的數(shù)據(jù)庫(kù)統(tǒng)統(tǒng)拷貝到從服務(wù)器上來(lái)（僅對(duì)MyISAM引擎的表有用，所以庫(kù)太多就無(wú)法一一將表修改過(guò)來(lái)，該命令會(huì)忽略mysql內(nèi)置庫(kù)），然后再start slave開(kāi)始同步。
   注意，上面兩種方法實(shí)施之前，最好使用以下流程來(lái)做：
   FLUSH TABLES WITH READ LOCK; //鎖定所有數(shù)據(jù)庫(kù)，在內(nèi)存中的數(shù)據(jù)庫(kù)也將存到硬盤上去,注意是大寫命令。
    -- copy data files ...  //這里拷貝數(shù)據(jù)庫(kù)到從數(shù)據(jù)庫(kù)，因?yàn)閿?shù)據(jù)庫(kù)也將被鎖定，不用擔(dān)心在拷貝的時(shí)候被寫入數(shù)據(jù)庫(kù)。
    UNLOCK TABLES; //解除鎖定，注意是大寫命令

7、檢測(cè)是否同步與排錯(cuò)可用以下命令查看：

1、在slave從服務(wù)器B上 輸入：show slave status(也可以在phpmyadmin中輸入sql語(yǔ)句)，主要查看：
Slave_IO_Running: Yes （#注：如果這個(gè)為NO，可重新修改my.ini 中相關(guān)slave的配置信息，重新啟動(dòng)查看 slave的狀態(tài)還是顯示未修改的數(shù)據(jù)，應(yīng)為 第一次是讀取my.ini，之后就會(huì)在mysql/下生成一個(gè)master.info 的文件，因此第二次就不會(huì)讀取my.ini 的內(nèi)容，而是讀取master.info中的內(nèi)容，為此要想使重新修改的my.ini生效的話，刪除master.info文件 ，重起mysql既可解決。如果不能解決則可能是彼此之間的通訊問(wèn)題或其它）。
Slave_SQL_Running: Yes （#注：如果這里為NO，很有可能是因?yàn)槟愕腁庫(kù)和B庫(kù)的數(shù)據(jù)庫(kù)不一致造成的。停止主從服務(wù)器MYSQL服務(wù)后刪除A庫(kù)中所有l(wèi)og-bin.log文件和B庫(kù)所有的relay_log文件 并重啟AB服務(wù)器即可解決）。Seconds_Behind_Master是否為0，0就是已經(jīng)同步。

2、在master主服務(wù)器A上輸入show processlist
mysql> show processlist\G
出現(xiàn) Command: Binlog Dump 表現(xiàn)已經(jīng)同步成功。

備注：MySQL主從復(fù)制經(jīng)常出現(xiàn)的Slave_IO_Running:NO或Slave_SQL_Running:NO問(wèn)題解決辦法
在主數(shù)據(jù)庫(kù)上查看POS位置：
mysql> show master status;
將查詢到的信息給在從數(shù)據(jù)庫(kù)上mysql> slave stop;
mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;
mysql> slave start;
mysql> show slave status
問(wèn)題解決