centos 7做mysql 集群_CentOS7.X搭建MySQL集群(互为主从)

参考链接

参考链接

准备2台服务器假设IP为

# 192.168.0.11

# 192.168.0.12

安装前的准备

yum install \

lua \

vim \

git \

gcc \

gcc-c++ \

wget \

make \

cmake \

automake \

autoconf \

libaio \

libtool \

net-tools \

bison-devel \

libaio-devel \

ncurses-devel \

perl-Data-Dumper \

-y

获取源码资源包

wget http://repo.mysql.com/mysql57-community-release-el7.rpm

安装源码资源包

rpm -ivh mysql57-community-release-el7.rpm

通过资源包安装mysql

yum install mysql-server -y

修改my.conf

> /etc/my.cnf

vim /etc/my.cnf

[client]

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

datadir=/data/mysql

character-set-server=utf8mb4

init_connect='SET NAMES utf8mb4'

collation-server=utf8mb4_unicode_ci

character-set-client-handshake=FALSE

symbolic-links=0

log-error=/var/log/mysqld.log

socket=/var/lib/mysql/mysql.sock

pid-file=/var/run/mysqld/mysqld.pid

secure_file_priv=/var/lib/mysql-files

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ESC

:wq

创建数据文件目录、所属组、用户改为mysql

mkdir /data

mkdir /data/mysql

chown -R mysql:mysql /etc/my.cnf

chown -R mysql:mysql /data/mysql/

chown -R mysql:mysql /var/*/mysql*

修改SELinux

vim /etc/sysconfig/selinux

SELINUX=permissive

ESC

:wq

setenforce 0

启动数据库(2台服务器均执行)

systemctl enable mysqld

systemctl start mysqld

cat /var/log/mysqld.log|grep password

# 结果显示

A temporary password is generated for root@localhost: **************

修改数据库初始密码(2台服务器均执行)

mysql -u root -p

# 输入原始密码

**************

# 设置允许使用弱密码

SET GLOBAL validate_password_policy=0;

ALTER USER 'root'@'localhost' IDENTIFIED BY '**************';

GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'192.168.%' IDENTIFIED BY '**************' WITH GRANT OPTION;

FLUSH PRIVILEGES;

QUIT;

初始化数据库(2台服务器均执行)

# 如果是数据库【安装】则只执行下面这步(除了密码一律选择Y)

mysql_secure_installation

# 如果是数据库【升级】则只执行下面这步(**************表示数据库之前的密码)

mysql_upgrade -u root -p

**************

修改集群相关配置

vim /etc/my.cnf

# 192.168.0.11

server-id=1

binlog-format=Row

log-bin=/data/mysql/mysql-bin

relay-log=localhost1-relay-bin

# 192.168.0.12

server-id=2

binlog-format=Row

log-bin=/data/mysql/mysql-bin

relay-log=localhost2-relay-bin

ESC

:wq

# 重启各服务器MySQL!重启各服务器MySQL!!重启各服务器MySQL!!!

配置防火墙(2台服务器均执行)

yum install firewalld -y

systemctl enable firewalld

systemctl start firewalld

firewall-cmd --permanent --zone=public --add-port=3306/tcp

firewall-cmd --reload

创建服务器数据同步账号密码

mysql -u root -p

**************

SET GLOBAL validate_password_policy=0;

GRANT REPLICATION SLAVE ON *.* TO 'mysql'@'192.168.%' IDENTIFIED BY '**************';

FLUSH PRIVILEGES;

从服务器中指定二进制文件路径

# 这里如果是n台服务器，则1为2的MASTER，2为3的MASTER，(...)，n为1的MASTER。

# 因为这里n=2，所以1为2的MASTER，2为1的MASTER

# 192.168.0.11执行

mysql -u root -p

**************

CHANGE MASTER TO

MASTER_HOST='192.168.0.12',

MASTER_USER='mysql',

MASTER_PASSWORD='**************',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=600;

# 192.168.0.12

mysql -u root -p

**************

CHANGE MASTER TO

MASTER_HOST='192.168.0.11',

MASTER_USER='mysql',

MASTER_PASSWORD='**************',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=600;

# 重启各服务器MySQL!重启各服务器MySQL!!重启各服务器MySQL!!!

SHOW SLAVE STATUS\G

# Slave_IO_Running: Yes

# Slave_SQL_Running: Yes

# 表示配置成功！！！

# 如果没成功，检查防火墙3306端口是否打开

解决Slave_IO_Running: No

# 显示相应服务器MASTER状态

SHOW MASTER STATUS;

# 修改MASTER状态配置

CHANGE MASTER TO Master_Log_File = 'mysql-bin.000002', Master_Log_Pos = 154;

# 重启各服务器MySQL!重启各服务器MySQL!!重启各服务器MySQL!!!

分别在任意一台数据库服务器操作数据，看另一台是否发生了变化，如果发生相同变化，则数据库服务器集群搭建成功