當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

gitee如何搭建mysql_MySQL高可用架构集群环境搭建手册.md

發布時間：2024/9/27 数据库 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 gitee如何搭建mysql_MySQL高可用架构集群环境搭建手册.md 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

# MySQL高可用架構集群環境搭建手冊

## 環境準備

### 機器規劃

| 節點 | IP | 配置 | 角色 |

| -------- | -------------- | ---- | ---------------- |

| master-1 | 192.168.73.129 | 1c2g | master、HMA node |

| slave-1 | 192.168.73.130 | 1c1g | salve、HMA node |

| slave-2 | 192.168.73.131 | 1c1g | salve、HMA node |

| slave-3 | 192.168.73.132 | 1c1g | HMA manager |

### 安裝包

mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

拷貝到master和salve節點的/opt/software/mysql/目錄下并解壓

```shell

tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

```

### 安裝Mysql

在192.168.73.129、192.168.73.130、192.168.73.131分別按如下步驟命令安裝Mysql。

1)卸載自帶的mariadb

```shell

rpm -qa|grep mariadb

rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps

```

2)安裝Mysql

```shell

rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

```

3)初始化mysql

```shell

mysqld --initialize --user=mysql

```

4)查看臨時密碼

```shell

[root@localhost mysql]# cat /var/log/mysqld.log

2020-07-27T11:35:17.156086Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2020-07-27T11:35:17.291065Z 0 [Warning] InnoDB: New log files created, LSN=45790

2020-07-27T11:35:17.321303Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2020-07-27T11:35:17.389391Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3eb43224-cffd-11ea-b6e3-000c2998582e.

2020-07-27T11:35:17.390659Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2020-07-27T11:35:17.797306Z 0 [Warning] CA certificate ca.pem is self signed.

2020-07-27T11:35:17.916216Z 1 [Note] A temporary password is generated for root@localhost: =L=9m4yg29kA

```

5)啟動mysql，并設置開機啟動

```shell

[root@localhost mysql]# systemctl start mysqld.service

[root@localhost mysql]# systemctl status mysqld.service

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: active (running) since 一 2020-07-27 19:37:41 CST; 5s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 2328 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

Process: 2311 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Main PID: 2331 (mysqld)

CGroup: /system.slice/mysqld.service

└─2331 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

7月 27 19:37:41 localhost.localdomain systemd[1]: Starting MySQL Server...

7月 27 19:37:41 localhost.localdomain systemd[1]: Started MySQL Server.

```

6)登錄mysql，修改密碼

```shell

[root@localhost mysql]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.28

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> set password=password('root');

Query OK, 0 rows affected, 1 warning (0.00 sec)

```

7)關閉防火墻

```shell

[root@localhost mysql]# systemctl status firewalld

● firewalld.service - firewalld - dynamic firewall daemon

Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)

Active: active (running) since 日 2020-07-26 23:36:30 CST; 20h ago

Docs: man:firewalld(1)

Main PID: 775 (firewalld)

CGroup: /system.slice/firewalld.service

└─775 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid

7月 26 23:36:28 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...

7月 26 23:36:30 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.

[root@localhost mysql]# systemctl stop firewalld

[root@localhost mysql]# systemctl disable firewalld.service

Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@localhost mysql]# systemctl status iptables

Unit iptables.service could not be found.

```

沒有iptables就不用禁用了。

## 主從配置

### Master-1配置

1)修改mysql配置文件，默認在/etc/my.cnf

在[mysqld]下添加如下配置

```ini

# 開啟binlog并指定日志文件的文件名，主從復制必須開啟

log_bin=mysql-master-bin

# 為master節點設置id，集群之間各個節點保持唯一

server-id=1

# 每次數據寫入都同步到磁盤

sync-binlog=1

# 設置mysql自帶的數據庫不進行同步

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

# 指定哪些同步哪些數據庫

# binlog-do-db=xxx

# 雙主模式下開啟relay_log，設置中繼日志名稱

relay_log=mysql-master-relay

# 當前master執行更新操作時，寫入日志文件

log_slave_updates=1

```

2)重啟mysql

```shell

systemctl restart mysqld

```

3)登錄mysql進行授權

```shell

# 授權任意節點都可以進行數據復制

mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';

Query OK, 0 rows affected, 1 warning (0.00 sec)

# 授權任意節點可以連接mysql

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';

Query OK, 0 rows affected, 1 warning (0.00 sec)

# 刷新權限

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

```

4)驗證，查看主庫狀態

```shell

mysql> show master status;

+-------------------------+----------+--------------+-------------------------------------------+-------------------+

+-------------------------+----------+--------------+-------------------------------------------+-------------------+

+-------------------------+----------+--------------+-------------------------------------------+-------------------+

1 row in set (0.00 sec)

```

從庫進行同步時，需要指定File、Position。

### Slave-1配置

1)修改配置文件

slave-1作為備選master，在[mysqld]下添加如下配置

```ini

# 設置slave-1的id

server-id=2

# 開啟binlog并指定日志文件的文件名，主從復制必須開啟

log_bin=mysql-master-bin

# 每次數據寫入都同步到磁盤

sync-binlog=1

# 設置mysql自帶的數據庫不進行同步

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

# 設置中繼日志的文件名

relay_log=mysql-slave-1-relay

# 當前master執行更新操作時，寫入日志文件

log_slave_updates=1

# 設置當前節點為只讀，即雙主單寫模式

read_only=1

```

2)重啟mysql

```shell

systemctl restart mysqld

```

3)登錄mysql，查看從庫狀態

```shell

mysql> show ma status;

Empty set (0.00 sec)

```

初始狀態下`show slave status`會顯示`Empty set`，如果能顯示信息說明已經進行過主從配置。

4)對備選主進行初始化

```shell

mysql> change master to master_host='192.168.73.129',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-master-bin.000001',master_log_pos=869;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

```

5)因為salve-1是備選主，需要授權master-1進行數據同步

```shell

grant replication slave on *.* to 'root'@'%' identified by 'root';

grant all privileges on *.* to 'root'@'%' identified by 'root';

flush privileges;

```

6)查看備選主狀態

```shell

mysql> show master status;

+--------------------------+----------+--------------+-------------------------------------------+-------------------+

+--------------------------+----------+--------------+-------------------------------------------+-------------------+

+--------------------------+----------+--------------+-------------------------------------------+-------------------+

```

然后將master1的主節點配置成備選主信息，**登錄master1然后執行如下命令**：

```shell

change master to master_host='192.168.73.130',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-slave-1-bin.000001',master_log_pos=589;

```

### Slave-2配置

1)修改配置文件

```shell

# 設置slave-2的id

server-id=3

# 設置中繼日志的文件名

relay_log=mysql-slave-2-relay

```

2)剩下步驟參照slave-1。

## 集群半同步配置

### master配置

1)查看是否支持插件動態加載

```shell

mysql> select @@have_dynamic_loading;

+------------------------+

| @@have_dynamic_loading |

+------------------------+

| YES |

+------------------------+

1 row in set (0.00 sec)

```

> show plugins;可以顯示mysql已加載的插件

2)安裝semi插件

```shell

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';

Query OK, 0 rows affected (0.03 sec)

```

rpl_semi_sync_master：插件名

soname：指定插件別名，參照mariadb命名

3)semi配置

```shell

mysql> show variables like '%semi%';

+-------------------------------------------+------------+

| Variable_name | Value |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_for_slave_count | 1 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_master_wait_point | AFTER_SYNC |

+-------------------------------------------+------------+

6 rows in set (0.02 sec)

```

通過set命令修改以下配置：

```shell

# 開啟semi

set global rpl_semi_sync_master_enabled=1;

# 設置超時時間為1s

set global rpl_semi_sync_master_timeout=1000;

```

### slave配置

1)安裝semi

```shell

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

```

2)semi配置

```shell

mysql> show variables like '%semi%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

2 rows in set (0.02 sec)

```

通過set命令修改以下配置：

```shell

set global rpl_semi_sync_slave_enabled=1;

```

3)重啟slave

```shell

stop slave;

start slave;

```

## MHA配置

1)準備

mha-node安裝包：mha4mysql-node-0.58-0.el7.centos.noarch.rpm

mha-manager安裝包：mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

2)在192.168.73.129、192.168.73.130、192.168.73.131三臺機器上安裝mha-node

```shell

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

```

3)在192.168.73.132上安裝mha-manager和mha-node

```shell

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

```

4)配置manager節點和node節點之間的ssh通信

```shell

# 在所有節點都執行以下命令

ssh-keygen -t rsa

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.73.129

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.73.130

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.73.131

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.73.132

```

5)配置manager

分別創建以下目錄：

```shell

mkdir /opt/software/mysql/hma

mkdir /opt/software/mysql/hma/app

mkdir /etc/mysql/mha

```

編寫manager主配置文件

vi /etc/mysql/mha/app.cnf

```ini

[server default]

#mysql用戶

user=root

#mysql密碼

password=root

# manager工作目錄

manager_workdir=/opt/software/mysql/hma/app

# manager日志文件存放位置

manager_log=/opt/software/mysql/hma/app/manager.log

# ssh通信用戶名

ssh_user=root

# ？

repl_user=root

repl_password=root

# 心跳檢測

ping_interval=1

[server1]

hostname=192.168.73.129

port=3306

candidate_master=1

[server2]

hostname=192.168.73.130

port=3306

candidate_master=1

[server3]

hostname=192.168.73.131

port=3306

no_master=1

```

6)運行manager

- 檢查SSH配置

```shell

[root@localhost hma]# masterha_check_ssh --conf=/etc/mysql/hma/app.cnf

```

- 檢查MHA當前配置

```shell

masterha_check_repl --conf=/etc/mysql/hma/app.cnf

```

- 啟動mha

```shell

nohup masterha_manager --conf=/etc/mysql/hma/app.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /opt/software/mysql/hma/app/manager.log 2>&1 &

```

- 查看狀態

```shell

masterha_check_status --conf=/etc/mysql/hma/app.cnf

```

- 查看日志

```shell

tail -f /opt/software/mysql/hma/app/manager.log

```

## 問題記錄

1.安裝mysql-server時失敗

```shell

[root@localhost mysql]# rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

警告：mysql-community-server-5.7.28-1.el7.x86_64.rpm: 頭V3 DSA/SHA1 Signature, 密鑰 ID 5072e1f5: NOKEY

錯誤：依賴檢測失敗：

/usr/bin/perl 被 mysql-community-server-5.7.28-1.el7.x86_64 需要

net-tools 被 mysql-community-server-5.7.28-1.el7.x86_64 需要

perl(Getopt::Long) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要

perl(strict) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要

```

解決：

```shell

yum -y install perl

yum -y install net-tools

```

2.安裝mha-node失敗

```shell

[root@localhost mysql]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

錯誤：依賴檢測失敗：

perl(DBD::mysql) 被 mha4mysql-node-0.58-0.el7.centos.noarch 需要

perl(DBI) 被 mha4mysql-node-0.58-0.el7.centos.noarch 需要

```

解決：

```shell

yum install perl-DBD-MySQL

```

3.安裝mha-manager失敗

```shell

[root@localhost mysql]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

錯誤：依賴檢測失敗：

mha4mysql-node >= 0.54 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Carp) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Config::Tiny) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(DBD::mysql) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(DBI) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Data::Dumper) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(English) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Errno) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(File::Basename) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(File::Copy) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Getopt::Long) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(IO::File) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Log::Dispatch) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Log::Dispatch::File) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Log::Dispatch::Screen) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(MHA::BinlogManager) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(MHA::NodeConst) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(MHA::NodeUtil) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(MHA::SlaveUtil) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(POSIX) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Parallel::ForkManager) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Pod::Usage) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Sys::Hostname) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(Time::HiRes) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(constant) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(strict) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

perl(warnings) 被 mha4mysql-manager-0.58-0.el7.centos.noarch 需要

```

解決：

```shell

yum -y install wget

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

rpm -ivh epel-release-latest-7.noarch.rpm

yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

```

4.檢測配置失敗

```shell

[root@localhost hma]# masterha_check_ssh --conf=/etc/mysql/hma/app.cnf

# .....

Mon Jul 27 21:52:38 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.73.130(192.168.73.130:3306) :1130:Host '192.168.73.132' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.

Mon Jul 27 21:52:38 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.

Mon Jul 27 21:52:38 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.73.131(192.168.73.131:3306) :1130:Host '192.168.73.132' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.

Mon Jul 27 21:52:38 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.

Mon Jul 27 21:52:38 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations

Mon Jul 27 21:52:38 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.

Mon Jul 27 21:52:38 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Mon Jul 27 21:52:38 2020 - [info] Got exit code 1 (Not master dead).

```

修改slave-1和slave-2的權限

```shell

grant all privileges on *.* to 'root'@'%' identified by 'root';

flush privileges;

```

再次檢測仍然報錯：

```shell

Mon Jul 27 21:54:12 2020 - [info] Checking slave configurations..

Mon Jul 27 21:54:12 2020 - [info] read_only=1 is not set on slave 192.168.73.130(192.168.73.130:3306).

Mon Jul 27 21:54:12 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.73.130(192.168.73.130:3306).

Mon Jul 27 21:54:12 2020 - [warning] log-bin is not set on slave 192.168.73.130(192.168.73.130:3306). This host cannot be a master.

Mon Jul 27 21:54:12 2020 - [info] read_only=1 is not set on slave 192.168.73.131(192.168.73.131:3306).

Mon Jul 27 21:54:12 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.73.131(192.168.73.131:3306).

Mon Jul 27 21:54:12 2020 - [warning] log-bin is not set on slave 192.168.73.131(192.168.73.131:3306). This host cannot be a master.

Mon Jul 27 21:54:12 2020 - [info] Checking replication filtering settings..

Mon Jul 27 21:54:12 2020 - [info] binlog_do_db= , binlog_ignore_db= information_schema,performance_schema,sys

Mon Jul 27 21:54:12 2020 - [info] Replication filtering check ok.

Mon Jul 27 21:54:12 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln364] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf

Mon Jul 27 21:54:12 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.

Mon Jul 27 21:54:12 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Mon Jul 27 21:54:12 2020 - [info] Got exit code 1 (Not master dead).

```

報錯信息顯示，當前環境時一臺master和兩臺slave，沒有備選的master，需要講其中一個slave改造成備選master。參照課程Mysql集群雙主實戰進行修改。

一鍵復制

編輯

Web IDE

原始數據

按行查看

歷史

總結

以上是生活随笔為你收集整理的gitee如何搭建mysql_MySQL高可用架构集群环境搭建手册.md的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： fullcalendar 显示的时间间隔
下一篇： oracle默认导出路径linux,传统