mysql資料庫資料同步/主從複制的配置方法

如果有多台mysql資料庫伺服器需要需要做資料同步，或者讀寫分離的時候。就需要做一個資料庫的主從複制，操作起來也比較簡單。

如下：

主伺服器x.x.x.233

從伺服器x.x.x.234

首先需要在主資料庫配置一下my.cnf

開啟binlog增加serverid

server-id                       = 1

log-bin                        = bin-log

重新開機服務後生效

登陸資料庫

<code>1</code>

<code>#mysql -uroot -p</code>

建立一個使用者台tmpcopy,密碼為mysq，并授權這個使用者可以從x.x.x.234這個IP以replication的權限登陸

<code>mysq&gt; update mysql.user </code><code>set</code> <code>user=</code><code>'tmpcopy'</code> <code>where password=password(</code><code>"mysql"</code><code>);</code>

<code>2</code>

<code>Query OK, 1 row affected (0.48 sec)</code>

<code>3</code>

<code>Rows matched: 1  Changed: 1  Warnings: 0</code>

<code>4</code>

<code>5</code>

<code>mysq&gt; flush privileges;</code>

<code>6</code>

<code>Query OK, 0 rows affected (0.03 sec)</code>

<code>7</code>

<code>8</code>

<code>mysq&gt; grant replication slave on *.* to [email protected] identified by </code><code>'mysql'</code><code>;</code>

<code>9</code>

<code>Query OK, 0 rows affected (0.06 sec)</code>

鎖住主伺服器的資料寫入（防止下面記錄bonlog的位置後，産生新的資料）

<code>mysql&gt;flush tables with </code><code>read</code> <code>lock;</code>

然後檢視一下主伺服器的狀态

<code>mysq&gt; show master status;</code>

<code>+----------------+----------+--------------+------------------+</code>

<code>| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |</code>

<code>| bin-log.000001 |    41582 |              |                  |</code>

目前的binlog檔案是bin-log.000001,位置是41582

這時候需要手工做一次同步，讓主伺服器和從伺服器資料保持一緻。

複制datadir或者從主伺服器mysqldump導出sql檔案再導入從伺服器，這裡不再累贅。

同步後目前兩台伺服器資料是一緻的

然後配置一下從伺服器的my.cnf

增加server-id=2

然後重新開機服務，登陸

切換從伺服器的master,并配置目前資料所處的binlog檔案以及位置

<code>mysq&gt; change master to master_host=</code><code>'x.x.x.1'</code><code>,master_user=</code><code>'tmpcopy'</code><code>,master_password=</code><code>'mysql'</code><code>,master_log_file=</code><code>'bin-log.000001'</code><code>,master_log_pos=41582;</code>

<code>Query OK, 0 rows affected (0.01 sec)</code>

啟動主從

<code>mysq&gt; start slave;</code>

<code>Query OK, 0 rows affected (0.00 sec)</code>

檢視一下從伺服器的狀态

<code>01</code>

<code>mysq&gt; show slave status\G</code>

<code>02</code>

<code>*************************** 1. row ***************************</code>

<code>03</code>

<code>Slave_IO_State: Waiting </code><code>for</code> <code>master to send event</code>

<code>04</code>

<code>Master_Host: x.x.x.233</code>

<code>05</code>

<code>Master_User: tmpcopy</code>

<code>06</code>

<code>Master_Port: 3306</code>

<code>07</code>

<code>Connect_Retry: 60</code>

<code>08</code>

<code>Master_Log_File: bin-log.000001</code>

<code>09</code>

<code>Read_Master_Log_Pos: 98925</code>

<code>10</code>

<code>Relay_Log_File: mysqld-relay-bin.000002</code>

<code>11</code>

<code>Relay_Log_Pos: 57594</code>

<code>12</code>

<code>Relay_Master_Log_File: bin-log.000001</code>

<code>13</code>

<code>Slave_IO_Running: Yes</code>

<code>14</code>

<code>Slave_SQL_Running: Yes</code>

<code>15</code>

<code>Replicate_Do_DB:</code>

<code>16</code>

<code>Replicate_Ignore_DB:</code>

<code>17</code>

<code>Replicate_Do_Table:</code>

<code>18</code>

<code>Replicate_Ignore_Table:</code>

<code>19</code>

<code>Replicate_Wild_Do_Table:</code>

<code>20</code>

<code>Replicate_Wild_Ignore_Table:</code>

<code>21</code>

<code>Last_Errno: 0</code>

<code>22</code>

<code>Last_Error:</code>

<code>23</code>

<code>Skip_Counter: 0</code>

<code>24</code>

<code>Exec_Master_Log_Pos: 98925</code>

<code>25</code>

<code>Relay_Log_Space: 57751</code>

<code>26</code>

<code>Until_Condition: None</code>

<code>27</code>

<code>Until_Log_File:</code>

<code>28</code>

<code>Until_Log_Pos: 0</code>

<code>29</code>

<code>Master_SSL_Allowed: No</code>

<code>30</code>

<code>Master_SSL_CA_File:</code>

<code>31</code>

<code>Master_SSL_CA_Path:</code>

<code>32</code>

<code>Master_SSL_Cert:</code>

<code>33</code>

<code>Master_SSL_Cipher:</code>

<code>34</code>

<code>Master_SSL_Key:</code>

<code>35</code>

<code>Seconds_Behind_Master: 0</code>

<code>36</code>

<code>Master_SSL_Verify_Server_Cert: No</code>

<code>37</code>

<code>Last_IO_Errno: 0</code>

<code>38</code>

<code>Last_IO_Error:</code>

<code>39</code>

<code>Last_SQL_Errno: 0</code>

<code>40</code>

<code>Last_SQL_Error:</code>

<code>41</code>

<code>Replicate_Ignore_Server_Ids:</code>

<code>42</code>

<code>Master_Server_Id: 1</code>

<code>43</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.01 sec)</code>

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

這兩個狀态運作了就已經配置完成了

測試一下：

建立資料庫testdb

<code>mysq&gt; create database testdb;</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

選擇資料庫testdb并建立表test_tables以及兩個字段id和test

<code>mysq&gt; use testdb;</code>

<code>Database changed</code>

<code>mysq&gt; create table test_table (</code><code>id</code> <code>int(3),</code><code>test</code> <code>char (10));</code>

插入資料

<code>mysq&gt; insert test_table values(</code><code>'1'</code><code>,</code><code>'test-data'</code><code>);</code>

<code>Query OK, 1 row affected (0.01 sec)</code>

我們再到從伺服器看一下：

<code>mysq&gt; show databases;</code>

<code>+--------------------+</code>

<code>| Database           |</code>

<code>| </code><code>test</code>               <code>|</code>

<code>| testdb             |</code>

<code>| txt                |</code>

<code>15 rows </code><code>in</code> <code>set</code> <code>(0.08 sec)mysq&gt; use testdb;</code>

<code>mysq&gt; </code><code>select</code> <code>* from test_table;</code>

<code>+------+-----------+</code>

<code>| </code><code>id</code>   <code>| </code><code>test</code>      <code>|</code>

<code>|    1 | </code><code>test</code><code>-data |</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>

<code>mysq&gt;</code>

新建立的資料庫，表，表内的資料都已經同步了。

主從複制完成

如果需要一主多從，從伺服器的配置再到别的伺服器上配置一個不通的server-id重複操作一次即可。