一、目標：搭建兩臺MySQL服務器欲侮，一臺作為主服務器崭闲，一臺作為從服務器，實現(xiàn)主從復制

二威蕉、環(huán)境：

主數(shù)據(jù)庫： 172.18.0.202
從數(shù)據(jù)庫： 172.18.0.169
yum安裝mysql安裝可參考：https://www.cnblogs.com/brianzhu/p/8575243.html

三刁俭、原理：

數(shù)據(jù)庫之所以能進行主從復制，主要是因為二進制文件binlog的存在韧涨。多臺數(shù)據(jù)庫之間可以通過線程進行通信牍戚，從庫不斷的從主庫讀取binlog日志并且把內(nèi)容同步到從庫上。

image.png

四虑粥、配置步驟：

1. 保證兩個數(shù)據(jù)庫中的庫和數(shù)據(jù)是一致的如孝；

（以下為主數(shù)據(jù)庫）

2. 在主數(shù)據(jù)中創(chuàng)建一個同步賬號（可不創(chuàng)建使用現(xiàn)有的），如果僅僅為了主從復制創(chuàng)建賬號娩贷，只需要授予REPLICATION SLAVE權限第晰。`（此賬號是主從復制binlog記錄使用的，不要與<從數(shù)據(jù)庫>測試賬號混淆）`

mysql> GRANT REPLICATION SLAVE ON *.* to 'fei'@'172.18.0.169' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

說明：

備用：
set global validate_password_mixed_case_count=0; 
set global validate_password_length=3; 
set global validate_password_special_char_count=0; 
set global validate_password_policy=0; 
set global validate_password_number_count=3;
SHOW VARIABLES LIKE 'validate_password%'; 
GRANT REPLICATION SLAVE ON *.* to 'fei'@'172.18.0.169' identified by '123';

如果報1819錯誤表示密碼太簡單了彬祖，先運行set global validate_password_policy=0;設置完這句以后密碼就只判斷長度了茁瘦，運行set global validate_password_number_count=3;
查看添加的用戶：
select user,host from mysql.user;

3. 配置主數(shù)據(jù)庫

1）要主數(shù)據(jù)庫，你必須要啟用二進制日志（binary logging）储笑，并且創(chuàng)建一個唯一的Server ID甜熔，這步驟可能要重啟MySQL。
2）主服務器發(fā)送變更記錄到從服務器依賴的是二進制日志突倍，如果沒啟用二進制日志腔稀，復制操作不能實現(xiàn)（主庫復制到從庫）。
3）復制組中的每臺服務器都要配置唯一的Server ID赘方，取值范圍是1到(232)?1烧颖，你自己決定取值。
4）配置二進制日志和Server ID窄陡，你需要關閉MySQL和編輯my.cnf或者my.ini文件炕淮，在 [mysqld] 節(jié)點下添加配置。
5）下面是啟用二進制日志跳夭，日志文件名以“master-bin”作為前綴涂圆，Server ID配置為1们镜，如下：

[mysqld]
log-bin=mysql-bin # 默認在是/var/lib/mysql下
server-id=1 #主庫id，必須唯一
binlog-do-db=test # 要同步的庫
binlog-ignore-db=mysql #不給從機同步的庫(多個寫多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys 
expire_logs_days=7 #自動清理 7 天前的log文件,可根據(jù)需要修改

4. 重啟mysql

systemctl restart mysqld

5. 查看主服務器狀態(tài)：

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     8518 |     test     | mysql            |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

`注意：記錄好File和Position润歉，后面要用`

（以下為從數(shù)據(jù)庫）

6. 配置從數(shù)據(jù)庫：

1）從服務器模狭，同理，要分配一個唯一的Server ID踩衩，需要關閉MySQL嚼鹉，修改好my.cnf后再重啟，如下：

[mysqld]
server-id = 2 # 集群內(nèi)唯一
log-bin=salve-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
# read-only=true # 從庫只讀驱富，但是root依然可以修改锚赤，所以需要設置非root賬號進行使用

2）在從服務器里配置連接主服務器的信息：
進入mysql:

mysql> stop slave;

mysql> change master to master_host='172.18.0.202', master_port=3306, master_user='fei', master_password='123', master_log_file='master-bin.000003', master_log_pos=8515;

mysql> start slave;

說明：

172.18.0.202是主服務器的id。
master_log_file='master-bin.000003'是主服務器的File（你主服務器查出來的是什么就寫什么）褐鸥。
master_log_pos=8515是主服務器的Position（你主服務器查出來的是什么就寫什么）线脚。
每次重新啟動主服務器，master_log_file和master_log_pos都會變叫榕。

3）查看狀態(tài)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.202
                  Master_User: fei
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 8518
               Relay_Log_File: slave-relay-bin.000020
                Relay_Log_Pos: 4020
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

說明：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都是yes就說明成功了浑侥。

4）若 Slave_SQL_Running: no 請重復執(zhí)行以下內(nèi)容，直至yes：


mysql>stop slave; 
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
mysql>start slave;

`5）需要為從庫創(chuàng)建一個登錄主庫的賬號`

create user 'test'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'test'@'localhost' identified by '123';
flush privileges;

五晰绎、測試

主庫添加信息"fei"

mysql> select * from box;
+----+---------+------+
| id | name    | flag |
+----+---------+------+
| 16 | test222 | A    |
| 17 | test111 | A    |
| 18 | fei     | A    |
+----+---------+------+
3 rows in set (0.00 sec)

從庫查看：

mysql> select * from box;
+----+------+------+
| id | name | flag |
+----+------+------+
| 18 | fei  | A    |
+----+------+------+

這邊我們也發(fā)現(xiàn)寓落，因為主從是通過binlog進行同步的，所以在同步之前的數(shù)據(jù)沒有寫入到當前l(fā)og里面荞下，因此也就沒有辦法自動進行同步了零如。

六、一主多從

有了主從復制的操作案例锄弱。我們進行一主多從的配置時是非常簡單的。只需要按照相同的配置祸憋，再添加一臺從庫服務器即可会宪。
從服務器配置如下：

server-id = 3 # 唯一
read-only=true

mysql> change master to master_host='172.18.0.171', master_port=3306, master_user='tom', master_password='123', master_log_file='master-bin.000003', master_log_pos=8515;

mysql> start slave;

為從庫創(chuàng)建一個登錄主庫的賬號

create user 'tom'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'tom'@'localhost' identified by '123';
flush privileges;

搞定，按照主從復制的方法進行測試蚯窥。當主庫進行數(shù)據(jù)添加的時候掸鹅，多個從庫進行了同步的更新

七、讀寫分離

對于mysql單實例數(shù)據(jù)庫和master庫拦赠，如果需要設置為只讀狀態(tài)巍沙，需要進行如下操作和設置：

mysql -uroot -p
mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";

將MySQL從只讀設置為讀寫狀態(tài)的命令：

mysql> unlock tables;
mysql> set global read_only=0;

對于需要保證master-slave主從同步的salve庫，如果要設置為只讀狀態(tài)荷鼠，需要執(zhí)行的命令為：

mysql> set global read_only=1;

將salve庫從只讀狀態(tài)變?yōu)樽x寫狀態(tài)句携，需要執(zhí)行的命令是：

mysql> set global read_only=0;

也可以通過配置文件：

read-only=true

對于數(shù)據(jù)庫讀寫狀態(tài)，主要靠 read_only=1全局參數(shù)來設定允乐；默認情況下矮嫉，數(shù)據(jù)庫是用于讀寫操作的削咆，所以read_only參數(shù)也是0或faluse狀態(tài)，這時候不論是本地用戶還是遠程訪問數(shù)據(jù)庫的用戶蠢笋，都可以進行讀寫操作拨齐；如需設置為只讀狀態(tài)，將該read_only參數(shù)設置為1或TRUE狀態(tài)昨寞，但設置read_only=1狀態(tài)有兩個需要注意的地方：

read_only=1只讀模式瞻惋，不會影響slave同步復制的功能，所以在MySQL slave庫中設定了read_only=1后援岩，通過show slave status\G命令查看salve狀態(tài)歼狼，可以看到salve仍然會讀取master上的日志，并且在slave庫中應用日志窄俏，保證主從數(shù)據(jù)庫同步一致蹂匹；
read_only=1只讀模式，可以限定普通用戶進行數(shù)據(jù)修改的操作凹蜈，但不會限定具有super權限的用戶（root）的數(shù)據(jù)修改操作限寞；在MySQL中設置read_only=1后，普通的應用用戶進行insert仰坦、update履植、delete等會產(chǎn)生數(shù)據(jù)變化的DML操作時，都會報出數(shù)據(jù)庫處于只讀模式不能發(fā)生數(shù)據(jù)變化的錯誤悄晃，但具有super權限的用戶玫霎，例如在本地或遠程通過root用戶登錄到數(shù)據(jù)庫，還是可以進行數(shù)據(jù)變化的DML操作妈橄；

為了確保所有用戶庶近，包括具有super權限的用戶也不能進行讀寫操作，就需要執(zhí)行給所有的表加讀鎖的命令 flush tables with read lock;這樣使用具有super權限的用戶登錄數(shù)據(jù)庫眷蚓，想要發(fā)生數(shù)據(jù)變化的操作時鼻种，也會提示表被鎖定不能修改的報錯。
這樣通過設置set global read_only=1;和flush tables with read lock;兩條命令沙热，就可以確保數(shù)據(jù)庫處于只讀模式叉钥，不會發(fā)生任何數(shù)據(jù)改變，在MySQL進行數(shù)據(jù)庫遷移時篙贸，限定master主庫不能有任何數(shù)據(jù)變化投队，就可以通過這種方式來設定。
但同時由于加表鎖的命令對數(shù)據(jù)庫表限定非常嚴格爵川，如果再slave從庫上執(zhí)行這個命令后敷鸦，slave庫可以從master讀取binlog日志，但不能夠應用日志，slave庫不能發(fā)生數(shù)據(jù)改變轧膘，當然也不能夠?qū)崿F(xiàn)主從同步了钞螟，這時如果使用 unlock tables;解除全局的表讀鎖，slave就會應用從master讀取到的binlog日志谎碍，繼續(xù)保證主從庫數(shù)據(jù)庫一致同步鳞滨。
為了保證主從同步可以一直進行，在slave庫上要保證具有super權限的root等用戶只能在本地登錄蟆淀，不會發(fā)生數(shù)據(jù)變化拯啦，其他遠程連接的應用用戶只按需分配為select,insert,update,delete等權限，保證沒有super權限熔任，則只需要將salve設定read_only=1模式褒链，即可保證主從同步，又可以實現(xiàn)從庫只讀疑苔。
當然設定了read_only=1后甫匹，所有的select查詢操作都是可以正常進行的。

八惦费、互為主從

互為主從時兵迅，需要注意id步長的問題，每一臺只生成一個固定步長的id薪贫。這邊因為是兩臺機器恍箭，所以步長為2。
A數(shù)據(jù)庫只生產(chǎn)ID為 1 3 5 7 9 瞧省。扯夭。。的數(shù)據(jù)鞍匾。
B數(shù)據(jù)庫只生產(chǎn)ID為 2 4 6 8 10 交洗。。橡淑。藕筋。

auto-increment-increment = 2 
auto-increment-offset = 100（另外一臺配置單數(shù)101）

刪掉所有日志，reset slave;
再執(zhí)行flush logs;
可以保證你不會因為binlog的原因?qū)е鲁鲥e梳码。
此時開啟start slave;我們發(fā)現(xiàn)

image.png

兩臺mysql服務器按照自己預先設置的ID步長進行數(shù)據(jù)添加，ID不沖突伍掀，并且互為主從掰茶，互相復制。

九蜜笤、多主多從結構

多主多從結構濒蒋，其實就是在雙主結構上把從庫也加入進來。唯一需要注意的：從庫只會讀取一臺主庫上的binlog，而每臺主庫的binlog都是“殘缺”的沪伙，因此需要使用

log-slave-updates=on

來促使多臺主庫之間更新互相之間的binlog瓮顽，重啟服務即可。

十围橡、測試與思考

如果從庫數(shù)據(jù)不同步暖混，會出現(xiàn)什么情況？
答：如果從庫人為加入一條數(shù)據(jù)翁授，那么同步就失效了拣播，因此主從架構里從庫一定不要寫入數(shù)據(jù)。
如果同步之前收擦，主庫就有數(shù)據(jù)會怎么樣贮配？
答：不會自動同步，只會同步binlog里面的數(shù)據(jù)塞赂。所以還是停掉主庫（防止數(shù)據(jù)寫入）泪勒，先進行備份，再進行主從架構配置宴猾。
從庫怎么設置智能只讀圆存？
答：read-only=true可以讓從庫只讀，但是這邊依然無法限制root權限的人進行修改和寫操作鳍置，一般做法是創(chuàng)建一個普通權限的用戶辽剧，登錄的時候用普通權限，這樣就只能只讀了税产。但是如果用root依然是可以修改的怕轿。
如果主庫重啟會怎么樣？
答：binlog的名稱發(fā)生了改變辟拷。主庫重啟撞羽，從庫會自動跟上binlog的位置。
如果從庫重啟會怎么樣衫冻？
答：從庫會自動跟上binlog的位置诀紊。
長時間關機會倒是BInlog更新對不上。（重點）
答：需要到日志目錄下（正常是/var/lib/mysql）將所有binlog刪除隅俘。刪除完回到Mysql命令行執(zhí)行flush logs;刷新日志邻奠。必要時reset slave;重置一些從庫信息。然后再繼續(xù)配置主從架構为居。

reset slave;
flush logs;

—————————————————————————————

問題一：`如果配置都沒問題碌宴，但就是看不到庫和表，這里必須自己手動建立一樣的庫和表蒙畴，之后就可以看到數(shù)據(jù)同步了贰镣。`

問題二：當使用 grant 權限列表 on 數(shù)據(jù)庫 to ‘用戶名’@’訪問主機’ identified by ‘密碼’; 時會出現(xiàn)”……near ‘identified by ‘密碼” at line 1”這個錯誤

因為新版的的mysql版本已經(jīng)將創(chuàng)建賬戶和賦予權限的方式分開了
解決辦法:創(chuàng)建賬戶:create user ‘用戶名’@’訪問主機’ identified by ‘密碼’;賦予權限:grant 權限列表 on 數(shù)據(jù)庫 to ‘用戶名’@’訪問主機’ with grant option;

create user 'win'@'localhost' identified by '123';
grant select,insert,update,delete on test.* to 'win'@'localhost' with grant option;

問題三：Mysql主從同步Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情況故障排除

參考：https://blog.csdn.net/csdn317797805/article/details/100932662
參考：https://blog.csdn.net/zleiw/article/details/78243316

mysql主從復制、讀寫分離服務(centos)

mysql主從復制挂脑、讀寫分離服務(centos)

一、目標：搭建兩臺MySQL服務器欲侮，一臺作為主服務器崭闲，一臺作為從服務器，實現(xiàn)主從復制

二威蕉、環(huán)境：

三刁俭、原理：

四虑粥、配置步驟：

1. 保證兩個數(shù)據(jù)庫中的庫和數(shù)據(jù)是一致的如孝；

（以下為主數(shù)據(jù)庫）

3. 配置主數(shù)據(jù)庫

4. 重啟mysql

5. 查看主服務器狀態(tài)：

`注意：記錄好File和Position润歉，后面要用`

（以下為從數(shù)據(jù)庫）

6. 配置從數(shù)據(jù)庫：

`5）需要為從庫創(chuàng)建一個登錄主庫的賬號`

五晰绎、測試

六、一主多從

七、讀寫分離

八惦费、互為主從

九蜜笤、多主多從結構

十围橡、測試與思考

問題一：`如果配置都沒問題碌宴，但就是看不到庫和表，這里必須自己手動建立一樣的庫和表蒙畴，之后就可以看到數(shù)據(jù)同步了贰镣。`

問題二：當使用 grant 權限列表 on 數(shù)據(jù)庫 to ‘用戶名’@’訪問主機’ identified by ‘密碼’; 時會出現(xiàn)”……near ‘identified by ‘密碼” at line 1”這個錯誤

問題三：Mysql主從同步Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情況故障排除

mysql主從復制挂脑、讀寫分離服務(centos)

一、目標：搭建兩臺MySQL服務器欲侮，一臺作為主服務器崭闲，一臺作為從服務器，實現(xiàn)主從復制

二威蕉、環(huán)境：

三刁俭、原理：

四虑粥、配置步驟：

1. 保證兩個數(shù)據(jù)庫中的庫和數(shù)據(jù)是一致的如孝；

（以下為主數(shù)據(jù)庫）

3. 配置主數(shù)據(jù)庫

4. 重啟mysql

5. 查看主服務器狀態(tài)：

注意：記錄好File和Position润歉，后面要用

（以下為從數(shù)據(jù)庫）

6. 配置從數(shù)據(jù)庫：

5）需要為從庫創(chuàng)建一個登錄主庫的賬號

五晰绎、測試

六、一主多從

七、讀寫分離

八惦费、互為主從

九蜜笤、多主多從結構

十围橡、測試與思考

問題一：如果配置都沒問題碌宴，但就是看不到庫和表，這里必須自己手動建立一樣的庫和表蒙畴，之后就可以看到數(shù)據(jù)同步了贰镣。

問題二：當使用 grant 權限列表 on 數(shù)據(jù)庫 to ‘用戶名’@’訪問主機’ identified by ‘密碼’; 時會出現(xiàn)”……near ‘identified by ‘密碼” at line 1”這個錯誤

問題三：Mysql主從同步Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情況故障排除

`注意：記錄好File和Position润歉，后面要用`

`5）需要為從庫創(chuàng)建一個登錄主庫的賬號`

問題一：`如果配置都沒問題碌宴，但就是看不到庫和表，這里必須自己手動建立一樣的庫和表蒙畴，之后就可以看到數(shù)據(jù)同步了贰镣。`