mysql主從同步配置與不同步問題整理

本文章詳細的介紹了mysql主從同步配置，包括了linux和windows中的主從配置及可能出現問題的解決辦法。

linux中主從同步
主服務器IP: 250.110.120.119
從服務器IP: 110.120.119.250

1.在主服務器新建一個用戶，給從服務器同步使用。

 

 代碼如下 復制代碼 GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'110.120.119.250.' IDENTIFIED BY 'tongbu';

意思是新建一個用戶名為tongbu 密碼為tongbu 具有所有操作權限的帳號。

2.修改主服務器my.cnf文件
其實這一步可以省略，因為保持默認的配置文件就可以了。
/*
添加
server-id=1
並開啟log-bin二進制日志文件
log-bin=mysql-bin
注:需要把默認的server-id=1去掉
*/

3.重啟主服務器mysql數據庫

4.進入mysql界面執行下面命令 獲取日志文件名和偏移量

 

 代碼如下 復制代碼 show master status;
顯示如:
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010 | 106 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

得到的日志文件名為 mysql-bin.000010 偏移量為106

5.修改從服務器的my.cnf
添加一個
server-id=2
注:需要把默認的server-id=1去掉

6.重啟從服務器mysql數據庫

7.對從數據庫進行設置，@@@這是最最重要的地方。

 

 代碼如下 復制代碼 change master to master_host='250.110.120.119', master_user='tongbu', master_password='tongbu', master_log_file='mysql-bin.000010', master_log_pos=106;

8.啟動從服務器

start slave;
9.執行下面命令 查看進程

show processlistG
顯示如下

 代碼如下 復制代碼 mysql> show processlistG
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 1773
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 1630
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)

10.查看從服務器狀態

 

 代碼如下 復制代碼 show slave statusG
顯示如下
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 250.110.120.119
Master_User: tongbu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 281
Relay_Log_File: up2-relay-bin.000002
Relay_Log_Pos: 426
Relay_Master_Log_File: mysql-bin.000010
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: 281
Relay_Log_Space: 579
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:
1 row in set (0.00 sec)

測試:
1、在主服務器test數據庫中創建user表

 代碼如下 復制代碼 mysql>use test;
mysql>create table user(id int);

2、在從服務器中查看user表

 代碼如下 復制代碼 mysql>use test;
mysql> show tables like ‘user’;
+———————-+
| Tables_in_test(user) |
+———————-+
| user |
+———————-+
1 row in set (0.00 sec)

說明主從數據同步成功。

windows主從同步操作方案

負責在主、從服務器傳輸各種修改動作的媒介是主服務器的二進制變更日志，這個日志記載著需要傳輸給從服務器的各種修改動作。因此，主服務器必須激活二進制 日志功能。從服務器必須具備足以讓它連接主服務器並請求主服務器把二進制變更日志傳輸給它的權限。
       
環境：
A、B的MySQL數據庫版本同為5.0.18
A：
操作系統：Windows 2003
IP地址：10.100.0.100
B：
操作系統：Windows 2000 server
的IP地址：10.100.0.200

配置過程：
1、在A的數據庫中建立一個備份帳戶，命令如下：

 代碼如下 復制代碼 GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO backup@’10.100.0.200’
IDENTIFIED BY ‘1234’;

建立一個帳 戶backup，並且只能允許從10.100.0.200這個地址上來登陸，密碼是1234。

2、因為 mysql版本新密碼算法不同，所以進入mysql下，輸入：

 代碼如下 復制代碼 set password for'backup'@'10.100.0.200'=old_password('1234');

3、關停A服務器，將A中的數據拷貝到B服務器中，使得A和B中的數據同步，並且確保在全部設置操作結束前，禁止在A和B服務器中進行寫操作，使得兩數據 庫中的數據一定要相同！

4、對A服 務器的配置進行修改，打開mysql/my.ini文件，在[mysqld]下面添加如下內容：

 代碼如下 復制代碼 server-id=1
log-bin=c:log-bin.log

server- id：為主服務器A的ID值
log-bin：二進制變更日值

4、重啟A 服務器，從現在起，它將把客戶堆有關數據庫的修改記載到二進制變更日志裡去。

5、關停B 服務器，對B服務器錦熙配置，以便讓它知道自己的鏡像ID、到哪裡去找主服務器以及如何去連接服務器。最簡單的情況是主、從服務器分別運行在不同的主機上 並都使用著默認的TCP/IP端口，只要在從服務器啟動時去讀取的mysql/my.ini文件裡添加以下幾行指令就行了。

 代碼如下 復制代碼 [mysqld]
server-id=2
master-host=10.100.0.100
master-user=backup
master-password=1234
//以下內容為可選
replicate-do-db=backup

server- id：從服務器B的ID值。注意不能和主服務器的ID值相同。
master-host：主服務器的IP地址。
master-user：從服務器連接主服務器的帳號。
master-password：從服務器連接主服務器的帳號密碼。
replicate-do-db：告訴主服務器只對指定的數據庫進行同步鏡像。

6、重啟從 服務器B。至此所有設置全部完成。更新A中的數據，B中也會立刻進行同步更新。如果從服務器沒有進行同步更新，你可以通過查看從服務器中的 mysql_error.log日志文件進行排錯。

8、查看日志一些命令
1, show master statusG;
           在這裡主要是看log-bin的文件是否相同。
    show slave statusG;
    在這裡主要是看:
                   Slave_IO_Running=Yes
                   Slave_SQL_Running=Yes
   如果都是Yes,則說明配置成功.

2,在 master上輸入show processlistG;
 

 代碼如下 復制代碼     mysql> SHOW PROCESSLISTG
     *************************** 1. row ***************************
       Id: 2
       User: root
       Host: localhost:32931
       db: NULL
       Command: Binlog Dump
       Time: 94
       State: Has sent all binlog to slave; waiting for binlog to
         be updated
       Info: NULL

   如果出現Command: Binlog Dump,則說明配置成功.

MySQL主從不同步問題解決

1.首先停掉Slave服務：

mysql> slave stop2.到主服務器上查看主機狀態：
記錄File和Position對應的值。

 代碼如下 復制代碼

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)3.到slave服務器上執行手動同步：

mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3307,
> master_log_file='mysql-bin.000020',
> master_log_pos=135617781;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)再次查看slave狀態發現：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

...
Seconds_Behind_Master: 0我們知道，因為DATA REPLICATION機制完全是基於在主上執行的增量SQL要被傳播到輔服務器上，並且被成功運行。這就勢必要求：在運行此機制前，主輔數據庫中數據是一致的；以及在運行此機制中，輔數據庫禁止來自其他的SQL（非主上傳播過來SQL）的寫操作。但是在運行中仍然可能遇到不一致的產生，這會導致通信無法正常繼續下去。因此一旦主從出現問題，首先應該解決同步位置的問題，修復丟失的數據

MySQL主從不同步問題解決

問題 描述：主從復制在某個時刻，從服務器同步失敗，並給出報錯消息。

消息內容：基本意思是缺少某個庫的某個表

原因：主從服務長時間不同步，主庫已經生成很多新的表或者庫，而從庫卻沒有。

操作過程：
（1）登陸主服務器，查看主服務器的狀態

 代碼如下 復制代碼 mysql>show master status；

（2）登陸從服務器，執行同步操作。[code]

 代碼如下 復制代碼 mysql>stop slave;mysql > change master to ...(此處省略)；mysql > start slave;

（3）從服務器上查看狀態[code]

 代碼如下 復制代碼 mysql > show slave statusG

看報錯信息少什麼表或庫，少什麼就直接從主服務器通過scp復制，然後重復過程（1）~（3）直到不報錯為止