mysql galera 集群模式
mysql节点多主模式
对任意一个节点的mysql操作都会实时同步到集群中的所有节点mysql上 使mysql集群像是无状态对外提供服务
首节点安装
1.安装mysql依赖rpm
2.安装mysql rpm
rpm -e postfix-2:2.10.1-9.el7.x86_64
rpm -e mariadb-libs-5.5.68-1.el7.x86_64
rpm -ivh mysql-wsrep-common-5.7-5.7.40-25.32.el7.x86_64.rpm
rpm -ivh mysql-wsrep-libs-5.7-5.7.40-25.32.el7.x86_64.rpm
rpm -ivh mysql-wsrep-client-5.7-5.7.40-25.32.el7.x86_64.rpm
rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.40-25.32.el7.x86_64.rpm
rpm -ivh mysql-wsrep-server-5.7-5.7.40-25.32.el7.x86_64.rpm
rpm -ivh mysql-wsrep-5.7-5.7.40-25.32.el7.x86_64.rpm
rpm -ivh mysql-wsrep-devel-5.7-5.7.40-25.32.el7.x86_64.rpm --force --nodeps
rpm -ivh mysql-wsrep-test-5.7-5.7.40-25.32.el7.x86_64.rpm --force --nodeps
rpm -ivh galera-3-25.3.37-1.el7.x86_64.rpm
3.安装rsync
rpm -ivh rsync-2.8.el7.x86_64.rpm
创建用户和日志目录
useradd mysql
mkdir -p /data/logs/mysql/
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /data/logs/mysql/
chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /var/run/mysqld
2.初始化mysql
部署完mysql后必须先初始化mysql系统数据库,否则mysqld服务无法正常启动
mysql_install_db --no-defaults --datadir=/data/mysql --user=mysql
3.设置远程登录用户名和密码
vi /etc/my.cnf
systemctl start mysqld
mysql –uroot –p
>flush privileges;
>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '11111' WITH GRANT OPTION;
>flush privileges;
4.修改my.cnf
首节点的my.cnf和其它扩展节点的my.cnf有区别 不能直接拷贝覆盖
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/data/logs/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=5
binlog_format=row
default_storage_engine=InnoDB
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_name='galera'
wsrep_cluster_address='gcomm://'
wsrep_node_name='node5'
wsrep_node_address='10.32.3.5'
wsrep_sst_auth=root:aabbcc
wsrep_sst_method=rsync
#validate_password=off
#skip-grant-tables
# start
#max_connections 应设置为 1000，默认151
max_connections = 1000
#local_infile应设置为：OFF
local-infile=0
#log_slave_updates 应设置为 ON
log_slave_updates=1
#log_bin 应设置为 ON
log_bin = mysql-bin
#slow_query_log 应设置为 ON
slow_query_log = 1
#应设置 log_error 错误日志
#log_error = /home/mysql.err
#general_log 应设置为 ON
general_log = 1
#应以非管理员帐号权限运行 MySQL
#user=mysql
lower_case_table_names = 1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
default-time_zone = '+8:00'
query_cache_type=1
query_cache_size=128M
max_allowed_packet=32M
tmp_table_size=96M
max_heap_table_size=96M
innodb_buffer_pool_size=64G
innodb_log_buffer_size=512M
innodb_thread_concurrency=48my.cnf
5.重启mysql
systemctl restart mysqld
6.查看启动日志
扩展节点安装
1.添加第二个节点
和首节点的安装部署步骤一致：
1.安装所有的mysql rpm包
2.安装rsync服务
3.初始化mysql 系统数据库
4.配置mysql的远程连接用户名和密码
5.配置my.cnf 这个和首节点的my.cnf不同
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/data/logs/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=71
binlog_format=row
default_storage_engine=InnoDB
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_name='galera'
wsrep_cluster_address='gcomm://10.32.3.5' //这里配置首节点的ip地址
wsrep_node_name='node71'
wsrep_node_address='10.30.92.71'
wsrep_sst_auth=root:aabbb
wsrep_sst_method=rsync
#validate_password=off
# 安全加固
#binlog-format=ROW
#log-bin=mysqlbinlog
#plugin-load=validate_password.so
#log_slave_updates=ON
#log_error=/data/logs/mysql/mysqld.log
# start
#max_connections 应设置为 1000，默认151
max_connections = 1000
#local_infile应设置为：OFF
local-infile=0
#log_slave_updates 应设置为 ON
log_slave_updates=1
#log_bin 应设置为 ON
log_bin = mysql-bin
#slow_query_log 应设置为 ON
slow_query_log = 1
#应设置 log_error 错误日志
#log_error = /home/mysql.err
#general_log 应设置为 ON
general_log = 1
#应以非管理员帐号权限运行 MySQL
#user=mysql
lower_case_table_names = 1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
default-time_zone = '+8:00'
query_cache_type=1
query_cache_size=128M
max_allowed_packet=32M
tmp_table_size=96M
max_heap_table_size=96M
innodb_buffer_pool_size=64G
innodb_log_buffer_size=512M
innodb_thread_concurrency=48View Code
6.启动mysqld
systemctl start mysqld
启动扩展节点的mysqld服务后自动会连接首节点mysqld服务 自动组成mysql集群
2.添加第三个节点
和第二个节点的安装部署步骤一致：
1.安装所有的mysql rpm包
2.安装rsync服务
3.初始化mysql 系统数据库
4.配置mysql的远程连接用户名和密码
5.配置my.cnf 这个和首节点的my.cnf不同
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/data/logs/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=70
binlog_format=row
default_storage_engine=InnoDB
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_name='galera'
wsrep_cluster_address='gcomm://10.32.3.5,10.30.92.71'
wsrep_node_name='node70'
wsrep_node_address='10.30.92.70'
wsrep_sst_auth=root:aabb
wsrep_sst_method=rsync
#validate_password=off
# 安全加固
#binlog-format=ROW
#log-bin=mysqlbinlog
#plugin-load=validate_password.so
#log_slave_updates=ON
#log_error=/data/logs/mysql/mysqld.log
# start
#max_connections 应设置为 1000，默认151
max_connections = 1000
#local_infile应设置为：OFF
local-infile=0
#log_slave_updates 应设置为 ON
log_slave_updates=1
#log_bin 应设置为 ON
log_bin = mysql-bin
#slow_query_log 应设置为 ON
slow_query_log = 1
#应设置 log_error 错误日志
#log_error = /home/mysql.err
#general_log 应设置为 ON
general_log = 1
#应以非管理员帐号权限运行 MySQL
#user=mysql
lower_case_table_names = 1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
default-time_zone = '+8:00'
query_cache_type=1
query_cache_size=128M
max_allowed_packet=32M
tmp_table_size=96M
max_heap_table_size=96M
innodb_buffer_pool_size=32G
innodb_log_buffer_size=512Mmy.cnf
6.启动mysqld服务
systemctl start mysqld
3.测试集群数据库同步
1.登录三台的mysql服务
mysql -uroot -h10.32.3.3 -P3306 -p
mysql -uroot -h10.32.3.4 -P3306 -p
mysql -uroot -h10.32.3.5 -P3306 -p
2.测试数据
在任何一台服务上创建一个新的数据库 然后到其它节点查看是否有新建的数据库
> create database test;
> show databases;
高可用反向代理
如果不用反向代理 整个集群对外暴露出的服务地址为三个IP和端口 程序调用的时候需要填写三个地址 只配置一个的话就会造成集群主机负载不均衡,其它集群节点只起到一个数据备份的作用
这种情况就适合通过haproxy服务对整个集群进行代理，整个集群通过haproxy的ip和端口对外进行暴露 程序调用的时候只需要配置haproxy的ip的端口 由haproxy对客户端连接进行转发
#---------------------------------------------------------------------
# 全局设定部分
#---------------------------------------------------------------------
global
defaults
log global
mode tcp
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000
#---------------------------------------------------------------------
# HAPROXY状态页面
#---------------------------------------------------------------------
listen admin_stats
stats enable
# 监听端口
bind *:12345
mode http
option httplog
log global
maxconn 10
# 刷新间隔
stats refresh 30s
# 页面路径
stats uri /
stats realm haproxy
# 访问认证
stats auth admin:password
stats hide-version
#---------------------------------------------------------------------
# TCP转发及负载均衡及简单TCP可用性校验
# weight权重；check inter检测频率；rise 1一次可用恢复；fall 2两次失败降级
#---------------------------------------------------------------------
frontend mysql-in
bind *:3307
maxconn 80000
default_backend mysqldb
backend mysqldb
#使用HTTP对URI路径可用性进行检测
#option httpchk
#http-check send meth GET uri /check.html
#http-check expect status 200
server mysql-1 10.30.92.70:3306 maxconn 40480 weight 10 check inter 10s rise 1 fall 2
server mysql-2 10.30.92.71:3306 maxconn 40480 weight 10 check inter 10s rise 1 fall 2
server mysql-3 10.32.3.5:3306 maxconn 44440 weight 10 check inter 10s rise 1 fall 2haproxy.cfg
数据库集群的访问地址配置为 haproxyIP:Port 192.168.30.90:3307
启动节点和重启节点注意事项
重启扩展节点的mysqld服务的时候可能需要先重启首节点的mysqld服务才行 整个集群的mysqld服务启动顺序有依赖关系
否则扩展节点重启的时候由于连接主节点超时导致扩展节点上mysqld的服务启动异常
