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

本人最近開始接觸網站負載均衡方面，先准備處理一下mysql的主從配置，這篇教程是從網站整理過來，是linux下的，不過本人是在windows下操作，所以說也同樣適用於windows，經本人親自測試可用。

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上面的數據庫，並且只具有數據庫備份的權限
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