MySQL主從同步的步驟：
- 在主服務(wù)器上開啟bin-log二進(jìn)制日志功能，設(shè)置唯一的server_id(除了多實(shí)例外一般設(shè)置為ip地址最后一位)砌梆，設(shè)置完成重啟mysqld服務(wù)
- 在從服務(wù)器上同樣設(shè)置唯一的server_id
- 在主服務(wù)器上授權(quán)REPLICATION SLAVE權(quán)限喉钢，允許從服務(wù)器同步數(shù)據(jù)
- 復(fù)制數(shù)據(jù)前在主服務(wù)器上SHOW MASTER STATUS記錄file和position
1. 準(zhǔn)備兩臺(tái)MySQL服務(wù)器，確保服務(wù)器之間時(shí)間同步
master:192.168.42.129
slave : 192.168.42.130
MySQL數(shù)據(jù)庫的安裝配置見：
http://www.reibang.com/p/31572010fe03
2. 設(shè)置主服務(wù)器bin-log文件
vim /etc/my.cnf文件
在 [mysqld] 主配置文件下，加入
log-bin=replicate-bin # 啟用二進(jìn)制日志温艇，設(shè)置二進(jìn)制文件名（自定義）
log-bin
重啟mysql服務(wù)
[root@master ~]# /etc/init.d/mysqld restart
3. 創(chuàng)建復(fù)制賬號
這個(gè)賬號必須具有REPLICATION SLAVE的權(quán)限，你可以為不同的從服務(wù)器創(chuàng)建不同的賬號堕汞，也可以為所有從服務(wù)器創(chuàng)建統(tǒng)一的賬號
進(jìn)入master mysql服務(wù)：
[root@master ~]# mysql -uroot -p
Enter password:
創(chuàng)建replicate賬號用于復(fù)制
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%' identified by 'replicate';
Query OK, 0 rows affected (0.07 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
獲取主服務(wù)器的二進(jìn)制日志信息:
只讀鎖定后勺爱，所有數(shù)據(jù)庫的寫操作都將被拒絕，但是讀操作可以繼續(xù)讯检。執(zhí)行鎖定可以防止在查看二進(jìn)制日志信息的同時(shí)有人對數(shù)據(jù)進(jìn)行修改操作琐鲁，查看二進(jìn)制日志信息后使用UNLOCK TABLES解鎖即可
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.19 sec)
mysql> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| replicate-bin.000001 | 1234 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
3. 對現(xiàn)有數(shù)據(jù)庫進(jìn)行快照備份
如果在使用二進(jìn)制日志進(jìn)行數(shù)據(jù)復(fù)制之前，主服務(wù)已經(jīng)存在大量的數(shù)據(jù)庫文件人灼，可以使用mysqldump進(jìn)行數(shù)據(jù)的備份還原操作围段。
主服務(wù)器上進(jìn)行備份操作
[root@master ~]# mysqldump -uroot -pqinger --all-databases --lock-all-tables > /tmp/test.sql
Warning: Using a password on the command line interface can be insecure.
[root@master ~]# ls /tmp/test.sql
/tmp/test.sql
將備份的sql文件復(fù)制到從服務(wù)器上進(jìn)行還原
[root@master ~]# scp /tmp/test.sql root@192.168.42.130:/tmp/
root@192.168.42.130's password:
test.sql 100% 633KB 632.6KB/s 00:00
[root@slave ~]# mysql -uroot -pqinger < /tmp/test.sql
Warning: Using a password on the command line interface can be insecure.
4. 配置從服務(wù)器連接主服務(wù)器進(jìn)行數(shù)據(jù)同步
進(jìn)入mysql，連接到master服務(wù)器
指定主機(jī)名挡毅，用戶名蒜撮，密碼，log文件，和log位置
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.42.129',
-> MASTER_USER='replicate',
-> MASTER_PASSWORD='replicate',
-> MASTER_LOG_FILE='replicate-bin.000001',
-> MASTER_LOG_POS=1309;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
開啟同步段磨，查看同步狀態(tài)
顯示
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表示同步正在進(jìn)行
mysql> START SLAVE;
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.42.129
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: replicate-bin.000001
Read_Master_Log_Pos: 1309
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 287
Relay_Master_Log_File: replicate-bin.000001
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: 1309
Relay_Log_Space: 464
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: 129
Master_UUID: c8baa742-b6c8-11e6-bac3-000c295183f1
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.00 sec)
master服務(wù)器創(chuàng)建數(shù)據(jù)庫取逾，slave服務(wù)器上查看是否存在
master
slave
