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

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

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

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

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

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

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

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

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

7、檢測是否同步與排錯可用以下命令查看：

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)還是顯示未修改的數據，應為 第一次是讀取my.ini，之后就會(huì )在mysql/下生成一個(gè)master.info 的文件，因此第二次就不會(huì )讀取my.ini 的內容，而是讀取master.info中的內容，為此要想使重新修改的my.ini生效的話(huà)，刪除master.info文件 ，重起mysql既可解決。如果不能解決則可能是彼此之間的通訊問(wèn)題或其它）。
Slave_SQL_Running: Yes （#注：如果這里為NO，很有可能是因為你的A庫和B庫的數據庫不一致造成的。停止主從服務(wù)器MYSQL服務(wù)后刪除A庫中所有log-bin.log文件和B庫所有的relay_log文件 并重啟AB服務(wù)器即可解決）。Seconds_Behind_Master是否為0，0就是已經(jīng)同步。

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

備注：MySQL主從復制經(jīng)常出現的Slave_IO_Running:NO或Slave_SQL_Running:NO問(wèn)題解決辦法
在主數據庫上查看POS位置：
mysql> show master status;
將查詢(xún)到的信息給在從數據庫上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)題解決