mysql集群搭建

angus_study
325 阅读4分钟

mysql集群搭建

虚拟机环境

版本

VirtualBox：版本6.1.22

CentOS：CentOS-7-x86_64

关闭防火墙

查看防火墙状态

firewall-cmd --state

停止firewall

systemctl stop firewalld.service

禁止firewall开机启动

systemctl disable firewalld.service

安装mysql (v5.7.17)

获取程序包

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

解压并重命名

tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
cd /usr/local
mv ~/workspace/mysql-5.7.17-linux-glibc2.5-x86_64 mysql

创建mysql系统用户和组

groupadd -g 27 mysql
useradd -u 27 -g mysql mysql
id mysql

mysql集群部署

创建mysql集群目录

mkdir -p /tmp/mysql

mkdir -p /data/mysql/mysql_3306/data
mkdir -p /data/mysql/mysql_3306/log
mkdir -p /data/mysql/mysql_3306/tmp

mkdir -p /data/mysql/mysql_3307/data
mkdir -p /data/mysql/mysql_3307/log
mkdir -p /data/mysql/mysql_3307/tmp

mkdir -p /data/mysql/mysql_3308/data
mkdir -p /data/mysql/mysql_3308/log
mkdir -p /data/mysql/mysql_3308/tmp

更改目录权限

chown -R mysql:mysql /tmp/mysql/

chown -R mysql:mysql /data/mysql/ 
chown -R mysql:mysql /usr/local/mysql/

添加环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin' >>  /etc/profile 
source /etc/profile

复制my.cnf文件到etc目录

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

修改my.cnf

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysqld_multi.log

[mysqld]
basedir = /usr/local/mysql
federated
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]
user=mysql
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3306/data
tmpdir=/tmp/mysql/
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3306/log/slow.log
log-error = /data/mysql/mysql_3306/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin

[mysqld3307]
user=mysql
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3307/data
tmpdir=/tmp/mysql/
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3307/log/slow.log
log-error = /data/mysql/mysql_3307/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin

[mysqld3308]
user=mysql
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/data/mysql/mysql_3308/data
tmpdir=/tmp/mysql/
port=3308
server_id=3308
socket=/tmp/mysql_3308.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3308/log/slow.log
log-error = /data/mysql/mysql_3308/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3308/log/mysql3308_bin

初始化数据库

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data/ --user=mysql --initialize-insecure 

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data/ --user=mysql --initialize-insecure

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3308/data/ --user=mysql --initialize-insecure

多实例启动

mysqld_multi --defaults-extra-file=/etc/my.cnf start

查看多实例状态

mysqld_multi --defaults-extra-file=/etc/my.cnf report

修改root用户密码，给用户cx授权

[root@localhost ~] mysql -S /tmp/mysql_3306.sock
set password for root@'localhost'=password('cx123456');
grant ALL PRIVILEGES on *.* to cx@'%' identified by 'cx123456'; 
flush privileges;

[root@localhost ~] mysql -S /tmp/mysql_3307.sock
set password for root@'localhost'=password('cx123456');
grant ALL PRIVILEGES on *.* to cx@'%' identified by 'cx123456'; 
flush privileges;

[root@localhost ~] mysql -S /tmp/mysql_3308.sock
set password for root@'localhost'=password('cx123456');
grant ALL PRIVILEGES on *.* to cx@'%' identified by 'cx123456'; 
flush privileges;

主机客户端连接测试

image.png

集群主从机制验证

建立siger用户并设置权限

[root@localhost ~]mysql -S /tmp/mysql_3306.sock -pcx123456
CREATE USER 'siger'@'%'IDENTIFIED BY 'cx123456';
GRANT ALL PRIVILEGES ON *.* TO 'siger'@'%';
grant ALL PRIVILEGES on *.* to siger@'localhost' identified by 'cx123456';
FLUSH PRIVILEGES;
select host,user from mysql.user;

配置3306为主，3307, 3308为从

查看3306 信息

[root@localhost ~]mysql -S /tmp/mysql_3306.sock -pcx123456

MySQL [(none)]>  SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3306_bin.000001 |     1437 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+

配置3307,3308主信息

[root@localhost ~] mysql -S /tmp/mysql_3307.sock -pcx123456

change master to master_host='127.0.0.1', master_port=3306, master_user='siger',master_password='cx123456',master_log_file='mysql3306_bin.000001',master_log_pos=1437;

start slave;
show slave status\G


[root@localhost ~] mysql -S /tmp/mysql_3308.sock -pcx123456

change master to master_host='127.0.0.1', master_port=3306, master_user='siger',master_password='cx123456',master_log_file='mysql3306_bin.000001',master_log_pos=1437;

start slave;
show slave status\G

image.png

数据变动验证

[root@localhost ~]mysql -S /tmp/mysql_3306.sock -pcx123456
create database siger;
use siger;
create table slave_test(id int(6),name varchar(10));
insert into slave_test values(000001,'angus');
FLUSH PRIVILEGES;


[root@localhost ~]# mysql -S /tmp/mysql_3308.sock -pcx123456

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| siger              |
| sys                |
+--------------------+

MySQL [siger]> select * from slave_test;
+------+---------+
| id   | name    |
+------+---------+
|    1 | angus   |
+------+---------+
1 row in set (0.00 sec)

双主配置

登录3306

mysql -S /tmp/mysql_3306.sock -pcx123456
mysql -S /tmp/mysql_3307.sock -pcx123456

显示作为主节点的信息

## 3306 主节点信息
MySQL [(none)]> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3306_bin.000005 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+

## 3307 主节点信息
 MySQL [(none)]> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3307_bin.000005 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+

停止之前的从节点服务

STOP SLAVE IO_THREAD FOR CHANNEL '';
STOP SLAVE SQL_THREAD FOR CHANNEL '';

从节点属性重置

reset slave;

根据3306和3307的数据相互设置为从节点 mysql3307_bin.000005，mysql3306_bin.000005

## 3306
change master to master_host='127.0.0.1', master_port=3307, master_user='siger',master_password='cx123456',master_log_file='mysql3307_bin.000005',master_log_pos=154;

## 3307
change master to master_host='127.0.0.1', master_port=3306, master_user='siger',master_password='cx123456',master_log_file='mysql3306_bin.000005',master_log_pos=154;

两个mysql分别启动从服务

start slave;  #启动
show slave status\G    # 查看从节点状态

image.png

停止从服务复制功能
stop slave;

重新配置主从
stop slave;
reset master;

avatar
文章
阅读
粉丝