1、為什么要使用主從同步
1.如果主服務器出現(xiàn)問題，可以快速切換到從服務器提供的服務
2.可以在從服務器上執(zhí)行查詢操作，降低主服務器的訪問壓力
3.可以在從服務器上執(zhí)行備份，以避免備份期間影響主服務器的服務
注意：一般只有更新不頻繁的數(shù)據(jù)或者對實時性要求不高的數(shù)據(jù)可以通過從服務器查詢堂湖，實時性要求高的數(shù)據(jù)仍然需要從主服務器獲得。
Window 數(shù)據(jù)庫主從（Master/Slave）同步安裝與配置詳解
2状土、 CentOS安裝Mysql 5.7.19
3无蜂、測試環(huán)境
我這里使用parallels desktop 虛擬機安裝的Centos操作系統(tǒng)
數(shù)據(jù)庫版本： 5.7.19
主機A：10.211.55.3（Master）
主機B：10.211.55.4（Slave）
service mysqld stop #停止數(shù)據(jù)庫
service mysqld start #啟動數(shù)據(jù)庫
service mysqld restart #重啟數(shù)據(jù)庫
系統(tǒng)信息
4、配置主服務器Master
4.1 給從服務器設置授權用戶(創(chuàng)建復制帳號)
建立一個帳戶javen蒙谓，并且只能允許從 10.211.55.4 這個地址上來登陸斥季，密碼是123456。
mysql> grant replication slave on *.* to 'javen'@'10.211.55.4' identified by '123456';
mysql> flush privileges;
4.2 主服務器Master配置
在 etc/my.cnf的[mysqld]下添加如下內(nèi)容
port=3306
#[必須]啟用二進制日志
binlog-ignore-db=mysql
#[必須]服務器唯一ID，默認是1
server-id= 1
#只保留7天的二進制日志酣倾，以防磁盤被日志占滿
expire-logs-days = 7
#不備份的數(shù)據(jù)庫
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-ignore-db=gogs
4.3 重啟MySQL服務并設置讀取鎖定
service mysqld restart
在主服務器上設置讀取鎖定有效舵揭，確保沒有數(shù)據(jù)庫操作，以便獲得一個一致性的快照
mysql -u root -proot -P3306
mysql> flush tables with read lock;
4.4 查看主服務器上當前的二進制日志名和偏移量值
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------------+-------------------+
| mysql-bin.000001 | 154 | | mysql,information_schema,performation_schema,sys,gogs | |
+------------------+----------+--------------+-------------------------------------------------------+-------------------+
1 row in set (0.00 sec)
這里的 File 躁锡、Position 是在配置Salve的時候要使用到的午绳，Binlog_Do_DB表示要同步的數(shù)據(jù)庫，Binlog_Ignore_DB 表示Ignore的數(shù)據(jù)庫映之，這些都是在配置的時候進行指定的拦焚。
另外：如果執(zhí)行這個步驟始終為Empty set(0.00 sec)，那說明前面的my.cnf 沒配置對杠输。
5赎败、配置從服務器Slave
5.1 修改從數(shù)據(jù)庫的配置
修改之后完整的配置如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
##以下是添加的內(nèi)容
log-bin=mysql-bin
server-id=3
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performation_schema
binlog-ignore-db = sys
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
5.2 重啟從數(shù)據(jù)庫并設置Slave數(shù)據(jù)庫
service mysqld restart
登錄從數(shù)據(jù)庫并做如下設置
mysql> stop slave; #關閉Slave
mysql> change master to master_host='10.211.55.3',master_user='javen',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos= 154;
mysql> start slave; #開啟Slave
注意：在這里指定Master的信息，master_log_file是在配置Master的時候的File選項蠢甲， master_log_pos是在配置Master的Position 選項僵刮，這里要進行對應。
5.3 查看Slave配置的信息
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.211.55.3
Master_User: javen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: centos-linux-2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 154
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
6峡钓、關閉掉主數(shù)據(jù)庫的讀取鎖定
mysql> unlock tables;
7妓笙、在主數(shù)據(jù)庫中創(chuàng)建一個表以及添加數(shù)據(jù)測試
主庫
從庫
