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

cnhejia發表於2016-04-07
MySql資料庫

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

首先需要在主資料庫配置一下my.cnf
開啟binlog增加serverid
server-id                       = 1
log-bin                        = bin-log

重啟服務後生效

登陸資料庫

1 #mysql -uroot -p

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

1 mysq> update mysql.user set user=`tmpcopy` where password=password("mysql");
2 Query OK, 1 row affected (0.48 sec)
3 Rows matched: 1  Changed: 1  Warnings: 0
4  
5 mysq> flush privileges;
6 Query OK, 0 rows affected (0.03 sec)
7  
8 mysq> grant replication slave on *.* to tmpcopy@x.x.x.234 identified by `mysql`;
9 Query OK, 0 rows affected (0.06 sec)

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

1 mysql>flush tables with read lock;

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

1 mysq> show master status;
2 +----------------+----------+--------------+------------------+
3 | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
4 +----------------+----------+--------------+------------------+
5 | bin-log.000001 |    41582 |              |                  |
6 +----------------+----------+--------------+------------------+

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

這時候需要手工做一次同步，讓主伺服器和從伺服器資料保持一致。
複製datadir或者從主伺服器mysqldump匯出sql檔案再匯入從伺服器，這裡不再累贅。

同步後目前兩臺伺服器資料是一致的
然後配置一下從伺服器的my.cnf
增加server-id=2
然後重啟服務，登陸
切換從伺服器的master,並配置當前資料所處的binlog檔案以及位置

1 mysq> change master to master_host=`x.x.x.1`,master_user=`tmpcopy`,master_password=`mysql`,master_log_file=`bin-log.000001`,master_log_pos=41582;
2 Query OK, 0 rows affected (0.01 sec)

啟動主從

1 mysq> start slave;
2 Query OK, 0 rows affected (0.00 sec)

檢視一下從伺服器的狀態

01 mysq> show slave statusG
02 *************************** 1. row ***************************
03 Slave_IO_State: Waiting for master to send event
04 Master_Host: x.x.x.233
05 Master_User: tmpcopy
06 Master_Port: 3306
07 Connect_Retry: 60
08 Master_Log_File: bin-log.000001
09 Read_Master_Log_Pos: 98925
10 Relay_Log_File: mysqld-relay-bin.000002
11 Relay_Log_Pos: 57594
12 Relay_Master_Log_File: bin-log.000001
13 Slave_IO_Running: Yes
14 Slave_SQL_Running: Yes
15 Replicate_Do_DB:
16 Replicate_Ignore_DB:
17 Replicate_Do_Table:
18 Replicate_Ignore_Table:
19 Replicate_Wild_Do_Table:
20 Replicate_Wild_Ignore_Table:
21 Last_Errno: 0
22 Last_Error:
23 Skip_Counter: 0
24 Exec_Master_Log_Pos: 98925
25 Relay_Log_Space: 57751
26 Until_Condition: None
27 Until_Log_File:
28 Until_Log_Pos: 0
29 Master_SSL_Allowed: No
30 Master_SSL_CA_File:
31 Master_SSL_CA_Path:
32 Master_SSL_Cert:
33 Master_SSL_Cipher:
34 Master_SSL_Key:
35 Seconds_Behind_Master: 0
36 Master_SSL_Verify_Server_Cert: No
37 Last_IO_Errno: 0
38 Last_IO_Error:
39 Last_SQL_Errno: 0
40 Last_SQL_Error:
41 Replicate_Ignore_Server_Ids:
42 Master_Server_Id: 1
43 1 row in set (0.01 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
這兩個狀態執行了就已經配置完成了

測試一下：
建立資料庫testdb

1 mysq> create database testdb;
2 Query OK, 1 row affected (0.00 sec)

選擇資料庫testdb並建立表test_tables以及兩個欄位id和test

1 mysq> use testdb;
2 Database changed
3 mysq> create table test_table (id int(3),test char (10));
4 Query OK, 0 rows affected (0.01 sec)

插入資料

1 mysq> insert test_table values(`1`,`test-data`);
2 Query OK, 1 row affected (0.01 sec)

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

01 mysq> show databases;
02 +--------------------+
03 | Database           |
04 +--------------------+
05 | test               |
06 | testdb             |
07 | txt                |
08 +--------------------+
09 15 rows in set (0.08 sec)mysq> use testdb;
10 Database changed
11 mysq> select * from test_table;
12 +------+-----------+
13 | id   | test      |
14 +------+-----------+
15 |    1 | test-data |
16 +------+-----------+
17 1 row in set (0.00 sec)
18  
19 mysq>

新建立的資料庫，表，表內的資料都已經同步了。
主從複製完成

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


相關文章