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!!! - 分别在任意一台数据库服务器操作数据，看另一台是否发生了变化，如果发生相同变化，则数据库服务器集群搭建成功
MySQL官方不推荐mysql-proxy，以下内容仅供参考
-
假设mysql-proxy服务器为192.168.0.13，则在该服务器执行如下操作
cd ~ wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy vim /etc/profile export PATH=$PATH:/usr/local/mysql-proxy/bin ESC :wq source /etc/profile -
配置防火墙(mysql-proxy服务器执行)
systemctl enable firewalld systemctl start firewalld firewall-cmd --permanent --zone=public --add-port=4040/tcp firewall-cmd --permanent --zone=public --add-port=4041/tcp firewall-cmd --reload -
编写lua脚本
vim /usr/local/mysql-proxy/mysql-proxy.lua if not proxy.global.config.rwsplit then proxy.global.config.rwsplit={ min_idle_connections=1, max_idle_connections=1, is_debug=false } end ESC :wq -
编写mysql-proxy脚本
vim /usr/local/mysql-proxy/mysql-proxy.sh #!/bin/bash /usr/local/mysql-proxy/bin/mysql-proxy \ --proxy-address=192.168.0.13:4040 \ --proxy-backend-addresses=192.168.0.11:3306 \ --proxy-backend-addresses=192.168.0.12:3306 \ --log-level=info \ --proxy-lua-script=/usr/local/mysql-proxy/mysql-proxy.lua \ --log-file=/var/lib/mysql/mysql-proxy.log \ --daemon ESC :wq chmod u+x /usr/local/mysql-proxy/mysql-proxy.sh /usr/local/mysql-proxy/mysql-proxy.sh -
查看运行情况
ps aux | grep mysql-proxy -
测试读写分离(每台服务器都执行)
SET GLOBAL general_log_file='/tmp/mysql.log'; SET GLOBAL general_log=ON; tail -f /tmp/mysql.log SET GLOBAL general_log=OFF; - 领支付宝红包支持作者
