mysql自動備份同步 主從策略即時備份

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

1、A、B兩臺mysql服務器，A為主服務器，B為從服務器（用來備份的服務器）。

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

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

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

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

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

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

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

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

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

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