mysql集群高可用搭建
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
rpm包下载地址
https://galeracluster.com/downloads/
http://releases.galeracluster.com/mysql-wsrep-5.7/redhat/7/x86_64/
http://releases.galeracluster.com/galera-3/redhat/7/x86_64/
https://downloads.mysql.com/archives/community/
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
skip-grant-tables
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=48
5.重启mysql
systemctl restart mysqld
6.查看启动日志
离线节点mysql服务安装
cp rsync /usr/bin/ cp lsof /usr/bin/ cp socat /usr/bin/ cp stunnel /usr/bin/ cp libssl.so.1.0.2o /usr/lib64/ ln -s /usr/lib64/libssl.so.1.0.2o /usr/lib64/libssl.so.10 cp libcrypto.so.1.0.2o /usr/lib64/ ln -s /usr/lib64/libcrypto.so.1.0.2o /usr/lib64/libcrypto.so.10 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 --force --nodeps rpm -ivh mysql-wsrep-client-5.7-5.7.40-25.32.el7.x86_64.rpm --force --nodeps rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.40-25.32.el7.x86_64.rpm --force --nodeps rpm -ivh mysql-wsrep-server-5.7-5.7.40-25.32.el7.x86_64.rpm --force --nodeps rpm -ivh mysql-wsrep-5.7-5.7.40-25.32.el7.x86_64.rpm --force --nodeps 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 --force --nodeps systemctl start mysqld systemctl status mysqld GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'e3434' WITH GRANT OPTION; mysql -u root -h 172.80.4.22 -p565656a
扩展节点安装
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=48
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=512M
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 2
数据库集群的访问地址配置为 haproxyIP:Port 192.168.30.90:3307
启动节点和重启节点注意事项
重启扩展节点的mysqld服务的时候可能需要先重启首节点的mysqld服务才行 整个集群的mysqld服务启动顺序有依赖关系
否则扩展节点重启的时候由于连接主节点超时导致扩展节点上mysqld的服务启动异常
本文来自博客园，作者：不懂123，转载请注明原文链接：https://www.cnblogs.com/yxh168/p/17605031.html
