MySQL一主一從架構的實現
目錄
文章目錄
- 目錄
- 1. MySQL主從原理概述
- 2. 主從環境
- 3. 主庫實現步驟
- 4. 從庫實現步驟
- 4.1 先將ip_master之前備份好的資料恢復到ip_slave從伺服器
- 4.2 配置slave從庫伺服器
- 4.3 從庫進行changemaster設定-告知主庫的logfile名稱和position位置點等資訊
- 5. mysql主從同步測試
- 5.1 在master主庫上，你可以看到slave的I/O執行緒建立的連線
- 5.2 在主資料庫ip_master上建立新表
- 5.3 檢視主庫ip_master在day15資料庫中student的建表時間
- 5.4 檢視從庫ip_slave在day15資料庫中student的執行建表sql的時間
- 5.5 檢視mysql主從複製之間的延遲時間
- 5.6 在主資料庫ip_master上給day15.student表插入資料
- 5.7 檢視從資料庫ip_slave的day15.student表
- 6. 測試從庫只讀屬性
1. MySQL主從原理概述
對於每一個連上來的從庫，主庫都有一個client執行緒與之對應。現在先看一下主從的基本資料流：
1、客戶端sql更新命令
2、主庫執行sql語句
3、主庫寫binlog
4、主庫client執行緒讀binlog傳送給從庫的io執行緒
5、從庫io執行緒寫盤（relay-log）
6、從庫sql執行緒讀（relay-log）
7、從庫執行更新（relay-info）。
這裡有涉及到兩個寫盤，主庫binlog和從庫的relaylog（3、5）。
不過不用擔心不停掃描檔案造成的延遲，因為讀檔案的執行緒是在同一個程式內，每次寫完都會廣播，所以雖然看上去是非同步，實際上延遲並不大。
2. 主從環境
2.1 虛擬機器準備
- CentOS系統伺服器2臺：
master主庫ip地址：ip_master hostname: mysqlmaster 埠號：port_master
slave從庫ip地址：ip_slave hostname: mysqlslave 埠號：port_slave
- 配置好yum源、防火牆和selinux關閉、各節點時鐘服務同步、各節點之間可以通過主機名互相通訊。
- 主從資料庫都安裝版本一致的MYSQL軟體
2.2 分別檢查主從mysql服務是否正常
分別啟動兩臺伺服器mysql服務，確保服務正常。主庫和從庫的mysql服務檢查方法是一致的：
# /etc/init.d/mysqld start
# /etc/init.d/mysqld status
# lsof -i:port_master
# lsof -i:port_slave
# ps -ef |grep mysqld
# netstat -lntup
3. 主庫實現步驟
3.1 配置master主庫伺服器
對master的/etc/my.cnf進行配置。
- 必須的配置【必須】
[mysqld]
server-id=101 #伺服器唯一ID，預設是1，一般取IP最後一段
log-bin=/application/mysql/log/mysql-bin #主庫必須啟用二進位制日誌，此路徑必須存在（手動建立）
#log-slave-updates=1 #將更新的記錄些不斷地寫入到二進位制檔案裡
log_slave_updates=1 #將更新的記錄些不斷地寫入到二進位制檔案裡
- 其他擴充套件配置項【非必須】
skip-name-resolve #關閉名稱解析
binlog-do-db=day15 #需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可
binlog-ignore-db=mysql #不需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可
slave-skip-errors=1 #是跳過錯誤，繼續執行復制操作(可選)
- 檢查修改的配置是否正確
[root@mysqlmaster mysql]# egrep 'server-id|log-bin' /etc/my.cnf
server-id=101
log-bin=/application/mysql/log/mysql-bin
#log-slave-updates=1
log_slave_updates=1
- 重啟master的mysql服務
[root@mysqlmaster log]# /etc/init.d/mysqld restart
- 通過兩個渠道檢視配置是否生效
[root@mysqlmaster log]# pwd
/application/mysql/log
[root@mysqlmaster log]# ll
total 8
-rw-rw----. 1 mysql mysql 107 May 24 18:18 mysql-bin.000001
-rw-rw----. 1 mysql mysql 40 May 24 18:18 mysql-bin.index
[root@mysqlmaster log]# mysql -uroot -proot123456 -e "show variables like 'log_bin'; "
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
3.2 在master庫上建立備份賬戶並授權
備份賬戶：是指可以連線到主庫的進行備份的賬號，一般只授權給從庫訪問許可權，且賬戶的操作許可權只有備份；
[root@mysqlmaster log]# mysql -uroot -proot123456
mysql> grant replication slave on day15.student to 'repl_user'@'%' identified by 'repl_user123456';
error 1144 (42000): illegal grant/revoke command; please consult the manual to see which privileges can be used
mysql> grant replication slave on day15.* to 'repl_user'@'%' identified by 'repl_user123456';
error 1221 (hy000): incorrect usage of db grant and global privileges
參考連結: http://www.cnblogs.com/tianshupei88/p/5075367.html
grant replication slave on *.* to 'repl_user'@'%' identified by 'repl_user123456';
3.3 在master上備份之前已經存在的資料。（主要指我們自己之前建的庫表）
3.3.1 鎖表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
3.3.2 確定備份時的位置點
得到binlog日誌檔名和偏移量（此處記住File名稱和Position值，後面slave伺服器配置時需要用到）
mysql> show master status;
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 107
Binlog_Do_DB: day15
Binlog_Ignore_DB: mysql,information_schema,performance_schema
1 row in set (0.00 sec)
ERROR:
No query specified
3.3.3 開始備份day15資料庫（上面的視窗最好不要關掉，另外開一個視窗進行備份）
[root@mysqlmaster ~]# mysqldump -uroot -proot123456 -B day15> /home/day15_bak.sql
[root@mysqlmaster ~]# cd /home/
[root@mysqlmaster home]# ll
total 4
-rw-r--r-- 1 root root 1390 May 25 20:40 day15_bak20180525.sql
另外一種備份方法：
mysqldump -uroot -proot123456 -B day15 --master-data=1> /home/day15_bak.sql
3.3.4 解鎖表
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
4. 從庫實現步驟
4.1 先將ip_master之前備份好的資料恢復到ip_slave從伺服器
4.1.1 從ip_master把備份檔案拉取過來
[root@mysqlslave ~]# scp -P22 –r -p root@ip_master:/home/day15_bak.sql /opt
[root@mysqlslave ~]# cd /opt/
[root@mysqlslave opt]# ll
total 8
-rw-r--r-- 1 root root 1390 May 25 19:45 day15_bak.sql
4.1.2 恢復資料到從伺服器ip_slave
[root@mysqlslave opt]# mysql -uroot -proot123456 </opt/day15_bak.sql
[root@mysqlslave opt]# mysql -uroot -proot123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| day15 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.04 sec)
4.2 配置slave從庫伺服器
對slave的/etc/my.cnf進行配置。
- 必須的配置【必須】
server-id=102 #配置server-id，讓從伺服器有唯一ID號
relay_log = /application/mysql/log/mysql-relay-bin #開啟Mysql中繼日誌，此路徑必須存在，手動建立
- 其他擴充套件配置項【非必須】
read_only = 1 #設定只讀許可權
log_bin = mysql-bin #開啟從伺服器二進位制日誌(一主一從時從庫可以不開啟)
log_slave_updates = 1 #使得更新的資料寫進二進位制日誌中(一主一從時從庫可以不開啟)
- 檢查修改的配置是否正確
[root@mysqlslave mysql]# chown -R mysql.mysql /application/mysql
[root@mysqlslave log]# egrep 'server-id|log|read_only' /etc/my.cnf
server-id = 102
relay_log =/application/mysql/log/mysql-relay-bin
log-bin=mysql-bin
read_only = 1
log_slave_updates = 1 #使得更新的資料寫進二進位制日誌中(一主一從時從庫可以不開啟)
- 重啟slave的mysql服務
[root@mysqlslave log]# /etc/init.d/mysqld restart
4.3 從庫進行changemaster設定-告知主庫的logfile名稱和position位置點等資訊
mysql> stop slave;
mysql> change master to master_host='ip_master',master_port=port_master,master_user='repl_user',master_password='repl_user123456',master_log_file='mysql-bin.000003',master_log_pos=107;
- 檢查slave伺服器上changemaster是否成功
[root@mysqlslave data]# pwd
/application/mysql/data
[root@mysqlslave data]# ll
total 28728
drwx------ 2 mysql mysql 4096 May 25 20:34 day15
-rw-rw----. 1 mysql mysql 18874368 May 25 20:59 ibdata1
-rw-rw----. 1 mysql mysql 5242880 May 25 20:59 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 May 9 11:30 ib_logfile1
-rw-rw---- 1 mysql mysql 95 May 25 22:16 master.info ######
drwx------. 2 mysql mysql 4096 May 9 11:27 mysql
-rw-r-----. 1 mysql mysql 1880 May 9 11:30 mysqlhost.err
-rw-rw----. 1 mysql mysql 6 May 9 11:30 mysqlhost.pid
-rw-r-----. 1 mysql mysql 19408 May 25 20:10 mysqlslave.err
-rw-rw---- 1 mysql mysql 5 May 25 19:55 mysqlslave.pid
drwx------. 2 mysql mysql 4096 May 24 15:14 performance_schema
-rw-rw---- 1 mysql mysql 71 May 25 20:46 relay-log.info
drwx------. 2 mysql root 4096 May 24 15:14 test
[root@mysqlslave data]#
[root@mysqlslave data]# cat master.info
18
mysql-bin.000003
752
ip_master
repl_user
repl_user123456
3306
60
0
0
1800.000
0
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: ip_master
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60 #重新連線的時間
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes #兩個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: 107
Relay_Log_Space: 409
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: 101
1 row in set (0.00 sec)
上述項配置完以後可檢視master和slave上執行緒的狀態。
5. mysql主從同步測試
5.1 在master主庫上，你可以看到slave的I/O執行緒建立的連線
- 在master上輸入show processlist\G
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 3
User: repl_user
Host: ip_slave:port_slave
db: NULL
Command: Binlog Dump
Time: 381
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
2 rows in set (0.00 sec)
5.2 在主資料庫ip_master上建立新表
mysql> use day15;
Database changed
mysql> create table student(
-> id int(4) not null,
-> name char(20) not null,
-> age int(2) NOT NULL default 0,
-> dept varchar(16) default NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | int(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
5.3 檢視主庫ip_master在day15資料庫中student的建表時間
mysql> use day15;
Database changed
mysql> select table_schema,table_name,create_time from information_schema.tables where table_name='student';
+--------------+------------+---------------------+
| table_schema | table_name | create_time |
+--------------+------------+---------------------+
| day15 | student | 2018-05-25 20:34:08 |
+--------------+------------+---------------------+
1 row in set (0.00 sec)
5.4 檢視從庫ip_slave在day15資料庫中student的執行建表sql的時間
mysql> use day15;
Database changed
mysql> select table_schema,table_name,create_time from information_schema.tables where table_name='student';
+--------------+------------+---------------------+
| table_schema | table_name | create_time |
+--------------+------------+---------------------+
| day15 | student | 2018-05-25 21:43:21 |
+--------------+------------+---------------------+
1 row in set (0.00 sec)
5.5 檢視mysql主從複製之間的延遲時間
mysql> select timediff('2018-05-25 21:43:21', '2018-05-25 20:34:08');
+--------------------------------------------------------+
|timediff('2018-05-25 21:43:21', '2018-05-25 20:34:08') |
+--------------------------------------------------------+
| 01:09:13 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
#結論：這還算是最理想的狀態，此時資料還不多，更新還比較少
5.6 在主資料庫ip_master上給day15.student表插入資料
mysql> insert into student (id,name,age,dept)values(1,'yuki',26,'外語');
Query OK, 1 row affected (0.03 sec)
mysql> insert into student (id,name,age,dept)values(2,'admin',23,'金融');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+-----+--------+
| id | name | age | dept |
+----+-------+-----+--------+
| 1 | yuki | 26 | 外語 |
| 2 | admin | 23 | 金融 |
+----+-------+-----+--------+
2 rows in set (0.03 sec
5.7 檢視從資料庫ip_slave的day15.student表
mysql> select * from day15.student;
+----+-------+-----+--------+
| id | name | age | dept |
+----+-------+-----+--------+
| 1 | yuki | 26 | 外語 |
| 2 | admin | 23 | 金融 |
+----+-------+-----+--------+
2 rows in set (0.03 sec)
6. 測試從庫只讀屬性
6.1 我們之前在從伺服器上設定了只讀，現在來測試能不能直接在從庫上插入資料
mysql> use day15;
Database changed
mysql> select * from day15.student;
+----+-------+-----+--------+
| id | name | age | dept |
+----+-------+-----+--------+
| 1 | yuki | 26 | 外語 |
| 2 | admin | 23 | 金融 |
+----+-------+-----+--------+
2 rows in set (0.03 sec)
mysql> insert into student (id,name,age,dept)values(3,'root',23,'計算機');
Query OK, 1 row affected (0.02 sec)
mysql> select * from day15.student;
+----+-------+-----+-----------+
| id | name | age | dept |
+----+-------+-----+-----------+
| 1 | yuki | 26 | 外語 |
| 2 | admin | 23 | 金融 |
| 3 | root | 23 | 計算機 |
+----+-------+-----+-----------+
3 rows in set (0.00 sec)
解決方式也很簡單，我們只需要把表鎖起來就可以了：
mysql>flush tables with read lock; ##圖片中with和read應該分開寫
Query OK, 0 rows affected (0.00 sec)
進行鎖表操作以後，我們再來插入一條資料：
mysql> insert into student (id,name,age,dept)values(4,'lisi',22,'工商');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
相關文章
- Mysql實現主從複製（一主雙從）MySql
- Linux實現MySql資料庫的主從複製（一主一從）LinuxMySql資料庫
- MySQL主從同步（一主一從、一主多從、主從從）等結構的概述與配置MySql主從同步
- 【mysql】mysql的資料庫主從(一主一從)MySql資料庫
- Broker的主從架構是怎麼實現的？架構
- MySQL叢集之 主從複製 主主複製 一主多從 多主一叢 實現方式MySql
- MySQL 主從配置-之-一主一從MySql
- MySql架構原理(MySql從淺入深 一）MySql架構
- MySQL主從複製架構轉換MGR架構MySql架構
- mysql主從複製(一):一主多從MySql
- MySQL（14）---Docker搭建MySQL主從複製(一主一從)MySqlDocker
- Mysql 一主一從配置MySql
- Mysql主從同步實戰(一)【知其然】MySql主從同步
- MySQL高可用架構之Keepalived+主從架構部署MySql架構
- MYSQL一個裝置上的主從複製實現-windowsMySqlWindows
- Mycat2+Mysql一主一從實現讀寫分離配置MySql
- 搭建PowerDNS+LAP+NFS+MySQL主從半節點同步實現LAMP架構DNSNFSMySqlLAMP架構
- MySQL主從原理, 高可用架構與高效能架構MySql架構
- mysql8.0 主從架構模式【0到1架構系列】MySql架構模式
- 【MySQL（二十二）】一主一從換主MySql
- Mysql主從架構搭建的時候遇到的問題MySql架構
- mysql實現主從複製MySql
- MySQL主從分離實現MySql
- 揭秘MySQL的主從同步實現方案MySql主從同步
- MySQL資料庫各場景主從高可用架構實戰MySql資料庫架構
- Docker-Compose實現Mysql主從DockerMySql
- docker實現mysql主從複製DockerMySql
- 基於bin-log&position搭建主從架構MySQL架構MySql
- MySQL 主從同步架構中你不知道的“坑”（上）MySql主從同步架構
- MySQL高可用架構：mysql+keepalived實現MySql架構
- MHA+MySQL主從配置實現MySQL高可用MySql
- 那一天，我被Redis主從架構支配的恐懼Redis架構
- 架構師必備：MySQL主從同步原理和應用架構MySql主從同步
- MySQL 配置多主一從 ( 8.0.18 版本 )MySql
- 記一次 MySQL 主從搭建MySql
- MySql雙主一從服務搭建MySql
- 簡單實踐實現 MySQL 主從複製MySql
- MySQL 實現高可用架構之 MHAMySql架構