mysql 5.7主從配置

1、MySQL主從複制簡介

MySQL主從複制（也稱A/B複制、Replication）,簡單講就是A和B兩台機器做主從後，在A上寫資料，另外一台B也會跟着寫資料，兩者資料實時同步。

MySQL主從是基于binlog的，主上須開啟binlog才能進行主從。

主從過程大緻分為三個步驟：

1）主将更改操作記錄到binlog裡；

2）從将主的binlog事件（SQL語句）同步到從本機上，并記錄在relaylog裡；

3）從根據relaylog裡面的SQL語句按順序執行；

主上有一個log dump線程，用來和從的I/O線程傳遞binlog

從上有兩個線程，其中I/O線程，用來同步主的binlog并生成relaylog，另外一個SQL線程用來把relaylog裡面的SQL語句落地。

主從配置需要注意的點

(1)主從伺服器作業系統版本和位數一緻；

(2) Master和Slave資料庫的版本要一緻；

(3) Master和Slave資料庫中的資料要一緻；

(4) Master開啟二進制日志，Master和Slave的server_id在區域網路内必須唯一；

2、準備工作

2.1 系統及軟體準備

作業系統: CentOS7.8

MySQL版本：5.7.30

Master伺服器：192.168.10.243

Slave伺服器：192.168.10.143

2.2、MySQL伺服器作業系統配置

# 關閉主、從伺服器防火牆
systemctl stop firewalld && systemctl disable firewalld

# 關閉主備伺服器SELinux
# 關閉SELinux
# 檢視SELinux狀态
sestatus –v
getenforce

# 臨時關閉SELinux
setenforce 0
# 永久關閉SELinux（需要重新開機作業系統）
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

# 優化檔案句柄
# 打開檔案的限制
vim /etc/security/limits.conf 
*     soft    nofile  65536
*     hard    nofile  131072
*     -       nproc   unlimited
*     soft    memlock unlimited
*     hard    memlock unlimited

# 優化核心參數
修改/etc/sysctl.conf檔案
# 增加tcp支援的隊列數
# 減少斷開連接配接時 ，資源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.tcp_max_syn_backlog = 1024000

2.3、安裝 MySQL-5.7.30　　

Master、Slave節點安裝MySQL

# MySQL軟體下載下傳
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-client-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-common-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-devel-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-libs-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-server-5.7.30-1.el7.x86_64.rpm

# 安裝MySQL：
yum -y localinstall *.rpm

或者配置yum源，直接使用yum指令安裝MySQL

vim /etc/yum.repo.d/mysql.repo
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0

# 儲存退出，執行
yum clean all
yum makecache
# 安裝 mysql-community-server
yum -y install mysql-community-server

2.4、啟動MySQL服務，并将服務加入開機啟動

systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld

2.5、登入mysql并初始化root密碼

# 登入mysql
說明：mysql在安裝完成，啟動mysqld服務後，MySQL會自動生成一個随機的root使用者密碼，可以到日志裡面檢視。
vim /var/log/mysqld.log

# 可以看到類似如下資訊：
2020-03-03T06:24:40.573789Z 1 [Note] A temporary password is generated for root@localhost: p5+yU>E*q8hh

# 說明：用該臨時密碼登入mysql後，必須要先修改密碼，如下：
[root@aliy-prod-pubser-server001 opt]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.26

Copyright (c) 2000, 2019, 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;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password = PASSWORD('6ps?3n2vPcXjhUfz');

# 如果從伺服器是克隆的主伺服器，則修改 auto.cnf（vi /var/lib/mysql/auto.cnf）檔案中 server-uuid 值

否則主從複制會報 1593 錯誤，修改完記得重新開機MySQL

3、MySQL主從複制配置

3.1、master伺服器配置

1）使用者添加及授權

在master 伺服器授權一個賬戶，擁有slave權限

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.10.143' IDENTIFIED BY 'GrantT^Slave143';
mysql> FLUSH PRIVILEGES;

說明: 192.168.10.143為備庫IP，GrantT^Slave143為賬号repl的密碼，配置時修改成實際環境的資訊。

3.2. my.cnf配置修改

修改mysql配置檔案，my.ini （windows）或 my.cnf（Linux）找到[mysqld] 标記，下方添加

server-id=1
log-bin=master-bin
binlog-format=ROW
# 以下兩個為可選選項，如果要保證資料不丢失最好在清理binlog之前把要清理的日志備份，日志檔案的大小根據磁盤的性能做适當的調整
expire_logs_days = 7 // binlog過期清理時間
max_binlog_size = 1G // binlog每個日志檔案大小

max_connections = 1000
character-set-server = utf8mb4 // 配置字元集
collation-server = utf8mb4_unicode_ci // 配置字元集
// 配置不同步的庫
binlog-ignore-db = mysql
binlog-ignore-db = sys
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

3.3 重新開機master 的mysql服務

systemctl restart mysqld

4、備庫配置

在另一個mysql上配置從伺服器

4.1 備庫my.cnf修改

找到my.ini （windows）或 my.cnf （linux），找到[mysqld]标記，更改如下配置

[mysqld]
max_connections = 1000
log_bin = slave-bin
server_id = 2
relay_log = slave-relay-bin
log_slave_updates = 1
read_only = 1
#以下兩個為可選選項，如果要保證資料不丢失最好在清理binlog之前把要清理的日志備份，日志檔案的大小根據磁盤的性能做适當的調整
expire_logs_days = 7 //binlog過期清理時間
max_binlog_size = 100m //binlog每個日志檔案大小

replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys

重新開機mysql使配置生效

systemctl restart mysqld

5、開啟複制

5.1、檢視主庫binlog資訊

登陸主庫執行

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

執行之後可以擷取目前主庫binlog使用的檔案及pos點　　

5.2、在從庫上設定複制資訊

根據上一步擷取到的資訊（Position, File）配置從庫的複制資訊，在從庫上執行如下指令

CHANGE MASTER TO MASTER_HOST='192.168.10.243',MASTER_USER='repl',MASTER_PASSWORD='GrantT^Slave143',MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=154;

注意：執行從庫配置, 每次重新開機mysql均需要執行（如果沒有開啟自動啟動主從）

參數解釋：

解釋：

// master的ip位址

mysql> change master to master_host='192.168.10.243',

// master授權的使用者

master_user='repl',

// master的授權使用者密碼

master_password='GrantT^Slave143',

// master的binlog日志名稱，這裡使用上述指令搜尋出來的為準

master_log_file='edu-mysql-bin.00000x',

// master的日志位置這裡使用上述“1.3”指令搜尋出來的為準，不能帶引号，必須是整型，否則會報錯

master_log_pos=xx,

// 重試時間、機關秒，預設重試時間為 60s

master_connect_retry=30;

5.3、在從庫上開啟複制

從庫複制資訊配置完成後執行指令開啟複制：

mysql> start slave;

5.4、檢視複制狀态

啟動之後如果不報錯即可執行如下指令檢視複制的狀态：

show slave status \G;

主要檢視下面兩個參數狀态，隻要都是yes，表示主從通信正常。

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

如果均為yes則正常，否則需根據last_error資訊進行調試

6、主從複制測試

# 建立資料庫

create database mydb;

# 建立表

use mydb;

create table mytab1(id int(10),name varchar(50),depid int(11));

# 插入資料

insert into mytab1 (id, name, depid) values (1,'mcb', 2),(2,'zhangsan',3);

檢視主庫資料庫，表，資料

檢視從庫資料庫，表，資料

7、主從同步可能遇到的問題

基于區域網路的Master/Slave機制在通常情況下已經可以滿足“實時”備份的要求了。如果延遲比較大，可以從以下幾個因素進行排查：

(1) 網絡延遲；

(2) Master負載過高；

(3) Slave負載過高；

如果出現同步失敗，可以根據提示處理錯誤，處理完成後，需要重新整理同步配置：

先停止同步

mysql> stop slave;

清理掉之前的配置，防止同步已經同步了的資料

mysql> reset slave all;

然後重新連接配接主庫，進行同步。

人們永遠沒有足夠的時間把它做好，但永遠有足夠的時間重新來過。

可是，因為并不是總有機會重做一遍，你必須做得更好，換句話說，

人們永遠沒有足夠的時間去考慮到底是不是想要它，但永遠有足夠的時間去為之後悔。

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

淺掘千口井，不如深挖一口井！當知識支撐不了野心時，那就靜下心來學習吧！運維技術交流QQ群：618354452

個人微信公衆号，定期釋出技術文章和運維感悟。歡迎大家關注交流。

mysql 5.7主從配置

1、MySQL主從複制簡介

2、準備工作

2.1 系統及軟體準備

2.2、MySQL伺服器作業系統配置

2.3、安裝 MySQL-5.7.30

2.4、啟動MySQL服務，并将服務加入開機啟動

2.5、登入mysql并初始化root密碼

3、MySQL主從複制配置

3.1、master伺服器配置

3.2. my.cnf配置修改

4、備庫配置

4.1 備庫my.cnf修改

5、開啟複制

5.1、檢視主庫binlog資訊

5.2、在從庫上設定複制資訊

5.3、在從庫上開啟複制

5.4、檢視複制狀态

6、主從複制測試

7、主從同步可能遇到的問題

繼續閱讀

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

搭建httpd服務

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案