事例基于Linux系統(tǒng)
1. 準備兩個數(shù)據(jù)庫
主庫：192.168.239.234
從庫：192.168.239.235
兩個數(shù)據(jù)庫的版本最好一致
2. 主庫Master配置
- 首先在Mysql的安裝目錄下面找到 my.cnf 配置文件
[root@localhost ~]# ls /usr/local/mysql/
bin data include man mysql-test scripts sql-bench
COPYING docs lib my.cnf README share support-files
- 然后修改my.cnf文件，在 [mysqld] 下添加配置
[root@localhost ~]# vi /usr/local/mysql/my.cnf
[mysqld]
//添加如下代碼
log-bin=mysql-bin //開啟二進制日志
server-id=1 //設置server-id
之后保存退出
- 重啟Mysql，創(chuàng)建用于同步的賬號
//重啟Mysql
[root@localhost ~]# service mysqld restart
//打開Mysql會話
[root@localhost ~]# mysql -u root -p
//輸入密碼之后進入會話
//創(chuàng)建用戶并授權(quán)：用戶：synchro 密碼：123456
mysql> CREATE USER 'synchro'@'192.168.239.235' IDENTIFIED BY '123456'; //創(chuàng)建用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO 'synchro'@'192.168.239.235'; //分配權(quán)限
mysql> flush privileges; //刷新權(quán)限
- 查看Master狀態(tài)，記錄二進制文件名(mysql-bin.000006)和位置(120)：
mysql> SHOW MASTER STATUS; //查看狀態(tài)
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3. 從庫slave配置
- 同樣先修改 my.cnf 文件
[root@localhost ~]# vi /usr/local/mysql/my.cnf
[mysqld]
//添加如下代碼
server-id=2 //設置server-id，必須唯一
- 重啟Mysql服務，打開Mysql會話，執(zhí)行同步SQL語句(需要主服務器主機名，登陸憑據(jù)，二進制文件的名稱和位置)：
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.239.234',
-> MASTER_USER='synchro',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000006',
-> MASTER_LOG_POS=120; //注意最后的數(shù)字不帶單引號
- 啟動slave同步進程：
mysql> start slave;
- 查看slave狀態(tài)：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.239.234
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000009
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000006
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: 120
Relay_Log_Space: 623
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
Master_UUID: 7d55ead0-f134-11e7-bd0c-000c294d0dd7
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.02 sec)
ERROR:
No query specified
當Slave_IO_Running和Slave_SQL_Running都為YES的時候就表示主從同步設置成功了， 然后就可以通過插入數(shù)據(jù)等操作來測試同步是否可用。還可以關(guān)閉slave（mysql> stop slave;）,然后再修改master，看slave是否也相應修改（停止slave后，master的修改不會同步到slave），就可以完成主從復制功能的驗證了。
master開啟二進制日志后默認記錄所有庫所有表的操作，可以通過配置來指定只記錄指定的數(shù)據(jù)庫甚至指定的表的操作，具體在mysql配置文件的 [mysqld] 可添加修改如下選項：
// 不同步哪些數(shù)據(jù)庫
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
//只同步哪些數(shù)據(jù)庫，除此之外，其他不同步
binlog-do-db = game
