適用於windows及linux的mysql5.5主從伺服器配置

Linux下具體安裝配置步驟如下：

一、首先安裝MySQL

在兩台MySQL伺服器192.168.1.169(主)和192.168.1.168(從)上分別安裝MySQL 5.5.22，因為安裝過程比較簡單，本站也有很多教程，所以就不細述。

二、配置MySQL主伺服器（192.168.1.169）

mysql  -uroot  -p    #進入MySQL控制台
create database osyunweidb;   #建立資料庫osyunweidb
insert into mysql.user(Host,User,Password) values('localhost','osyunweiuser',password('123456'));   #建立使用者osyunweiuser

#建立MySQL主從資料庫同步處理的使用者osyunweidbbak密碼123456

flush privileges;   #重新整理系統授權表

#授權使用者osyunweidbbak只能從192.168.1.168這個IP訪問主伺服器192.168.1.169上面的資料庫，並且只具有Database Backup的許可權
grant replication slave  on *.* to 'osyunweidbbak'@'192.168.1.168' identified by '123456' with grant option;


三、把MySQL主伺服器192.168.1.169中的資料庫osyunweidb匯入到MySQL從伺服器192.168.1.168中

1、匯出資料庫osyunweidb

mysqldump -u root -p osyunweidb > /home/osyunweidbbak.sql    #在MySQL主伺服器進行操作，匯出資料庫osyunweidb到/home/osyunweidbbak.sql

備忘：在匯出之前可以先進入MySQL控制台執行下面命令

flush tables with read lock;    #資料庫唯讀鎖定命令，防止匯出資料庫的時候有資料寫入

unlock tables;   #解除鎖定

2、匯入資料庫到MySQL從伺服器

mysql  -u root -p  #進入從伺服器MySQL控制台

create database osyunweidb;   #建立資料庫

use osyunweidb    #進入資料庫

source  /home/osyunweidbbak.sql  #匯入備份檔案到資料庫

mysql -u osyunweidbbak -h 192.168.1.169 -p  #測試在從伺服器上登入到主伺服器

四、配置MySQL主伺服器的my.cnf檔案

vi /etc/my.cnf   #編輯設定檔，在[mysqld]部分添加下面內容
server-id=1   #設定伺服器id，為1表示主伺服器，注意：如果原來的設定檔中已經有這一行，就不用再添加了。
log_bin=mysql-bin  #啟動MySQ二進位日誌系統，注意：如果原來的設定檔中已經有這一行，就不用再添加了。
binlog-do-db=osyunweidb  #需要同步的資料庫名，如果有多個資料庫，可重複此參數，每個資料庫一行
binlog-ignore-db=mysql   #不同步mysql系統資料庫
service mysqld  restart  #重啟MySQL
mysql -u root -p   #進入mysql控制台
show master status;  查看主伺服器，出現以下類似資訊
+------------------+----------+--------------+------------------+
| File                        | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 |    7131    | osyunweidb    | mysql                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注意：這裡記住File的值：mysql-bin.000019和Position的值：7131，後面會用到。

五、配置MySQL從伺服器的my.cnf檔案

vi /etc/my.cnf   #編輯設定檔，在[mysqld]部分添加下面內容
server-id=2   #設定檔中已經有一行server-id=1，修改其值為2，表示為從資料庫
log-bin=mysql-bin  #啟動MySQ二進位日誌系統，注意：如果原來的設定檔中已經有這一行，就不用再添加了。
replicate-do-db=osyunweidb   #需要同步的資料庫名，如果有多個資料庫，可重複此參數，每個資料庫一行
replicate-ignore-db=mysql   #不同步mysql系統資料庫
:wq!    #儲存退出
service mysqld restart   #重啟MySQL
注意：MySQL 5.1.7版本之後，已經不支援把master配置屬性寫入my.cnf設定檔中了，只需要把同步的資料庫和要忽略的資料庫寫入即可。
mysql  -u root -p  #進入MySQL控制台
slave stop;   #停止slave同步進程
change master to master_host='192.168.1.169',master_user='osyunweidbbak',master_password='123456',master_log_file='mysql-bin.000019' ,master_log_pos=7131;    #執行同步語句
slave start;    #開啟slave同步進程
SHOW SLAVE STATUS\G   #查看slave同步資訊，出現以下內容
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.169
                  Master_User: osyunweidbbak
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 7131
               Relay_Log_File: MySQLSlave-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: osyunweidb
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
1 row in set (0.00 sec)
注意查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上這兩個參數的值為Yes，即說明配置成功！
 

六、測試MySQL主從伺服器雙機熱備是否成功

1、進入MySQL主伺服器

mysql -u root -p  #進入主伺服器MySQL控制台

use osyunweidb   #進入資料庫

CREATE TABLE test ( id int not null primary key,name char(20) );   #建立test表

2、進入MySQL從伺服器

mysql -u root -p  #進入MySQL控制台

use osyunweidb   #進入資料庫

show  tables;  #查看osyunweidb表結構，會看到有一個建立的表test，表示資料庫同步成功

至此，MySQL資料庫配置主從伺服器實現雙機熱備執行個體教程完成

MYSQL 5.5.20 For Windows 主從備份配置說明

例：
      主伺服器IP:192.168.0.1 資料庫版本：mysql-installer-5.5.20.0.msi
      從伺服器IP:192.168.0.2 資料庫版本：mysql-installer-5.5.20.0.msi

主伺服器配置

1，配置my.ini 檔案：
　　開啟 my.ini 設定檔，在檔案底部加入如下兩行代碼：
　　server-id=1
　　log-bin=mysql-bin #開啟二進位日誌功能

2，建立一同步帳號授權給從伺服器使用。(如：root , 111111)

3，重啟資料庫服務。
　　net stop mysql55
　　net start mysql55

從伺服器配置：

1，配置my.ini 檔案：
　　開啟 my.ini 設定檔，在檔案底部加入如下兩行代碼：
      server-id = 2
      log-bin = mysql-bin
      binlog-do-db = test  # 要複製同步的資料庫
      binlog-ignore-db = mysql  # 不複製同步的資料庫
      binlog-ignore-db = paybank  # 不複製同步的資料庫
      binlog-ignore-db = performance_schema  # 不複製同步的資料庫

2，重啟資料庫服務。
　　net stop mysql55
　　net start mysql55

3，進入mysql命令列
      mysql>stop slave;
                 Query OK,0 rows affected(0.03 sec)

      mysql> change master to
              -> master_host="192.168.0.1",
              -> master_user="root",
              -> master_password="111111";
                  Query OK,0 rows affected(0.00 sec)

      mysql> start slave;
                  Query OK,0 rows affected(0.03 sec)

      mysql> show slave status \G
      記得這行尾部不要加分號，否則沒有辦法看到錯誤輸出。
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 174088685
               Relay_Log_File: hatang-ee1113bc-relay-bin.000019
                Relay_Log_Pos: 4249
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 174088685
              Relay_Log_Space: 7234
              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)


如果我們在1.row中看到如下兩行均為Yes 說明已成功同步。
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

同步碰到 1062 錯誤：

解決方案1
 mysql>stop slave;
 mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
 mysql> start slave;
 跳過這行錯誤，當然資料可能就與主要資料庫不一致了，所以需要從主要資料庫恢複一份最新備份的到從資料庫，期間主要資料庫就不讓使用者更新資料。

解決方案2
    本人在使用解決方案1的時候常常由於要跳過的條數太多，或效果不佳而無法解決問題。所以經過多次嘗試改良就有瞭解決方法2
    1，配置my.ini 檔案中加入下面一行代碼，（作用是跳過所有1062錯誤）
         slave-skip-errors = 1062
    2，重啟資料庫服務。
　　  net stop mysql55
　  　net start mysql55

    3，進入mysql命令列
          mysql>stop slave;
          Query OK,0 rows affected(0.03 sec)

          mysql> change master to
                  -> master_host="192.168.0.1",
                  -> master_user="root",
                  -> master_password="111111";
                      Query OK,0 rows affected(0.00 sec)

          mysql> start slave;
                      Query OK,0 rows affected(0.03 sec)

          mysql> show slave status \G

         做到這一步從資料庫與主要資料庫資料的想差很多跟本不一致，所以需要從主要資料庫備份一份最新的資料還原到從資料庫中

    4，停止同步
          mysql>stop slave;
    5，備份主要資料庫。
    6，還原資料庫到從資料庫。
    7，注掉配置my.ini 檔案中 slave-skip-errors = 1062
    8，重啟資料庫服務。
　  　net stop mysql55
　  　net start mysql55

    9，開啟同步
         mysql>stop slave;
         mysql>start slave;
         mysql> show slave status \G


解決方案3 (未嘗試)
    mysql>stop slave;
    mysql> show master status;
    mysql> change master to
            -> master_host="192.168.0.1",
            -> master_user="root",
            -> master_password="111111",
            -> master_log_file="mysql-bin.000001",
            -> mysql> master_log_pos=0;
    mysql>start slave;
    mysql> show slave status \G