MySQL集群(双工、多工)

  1. 参考链接
  2. 参考链接

  3. 准备2台服务器假设IP为

     # 192.168.0.11
     # 192.168.0.12
    
  4. 安装前的准备

     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
    
  5. 获取源码资源包

     wget http://repo.mysql.com/mysql57-community-release-el7.rpm
    
  6. 安装源码资源包

     rpm -ivh mysql57-community-release-el7.rpm
    
  7. 通过资源包安装mysql

     yum install mysql-server -y
    
  8. 修改文件打开数上限

     # 系统文件打开数上限
     vim /etc/security/limits.conf
    
     * hard nofile 65535
     * soft nofile 65535
    
     ESC
     :wq
    
     # MySQL文件打开数上限
     vim /usr/lib/systemd/system/mysqld.service
    
     LimitNOFILE=65535
    
     ESC
     :wq
    
     systemctl daemon-reload
    
  9. 修改my.conf

     > /etc/my.cnf
    
     vim /etc/my.cnf
    
     [client]
     default-character-set = utf8mb4
    
     [mysql]
     default-character-set = utf8mb4
    
     [mysqld]
     tmp_table_size = 256M
     key_buffer_size = 512M
     sort_buffer_size = 64M
     read_buffer_size = 64M
     thread_cache_size = 64
     join_buffer_size = 128M
     sort_buffer_size = 128M
     query_cache_size = 512M
     table_open_cache = 2048M
     read_rnd_buffer_size = 128M
     innodb_buffer_pool_size = 128M
     datadir=/data/mysql
     log_timestamps = SYSTEM
     default_storage_engine = InnoDB
     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
    
  10. 创建数据文件目录、所属组、用户改为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*
    
  11. 修改SELinux

     vim /etc/sysconfig/selinux
    
     SELINUX=permissive
    
     ESC
     :wq
    
     setenforce 0
    
  12. 启动数据库(2台服务器均执行)

     systemctl enable mysqld
     systemctl start mysqld
     cat /var/log/mysqld.log|grep password
     # 结果显示
     A temporary password is generated for root@localhost: **************
    
  13. 修改数据库初始密码(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;
    
  14. 初始化数据库(2台服务器均执行)

     # 如果是数据库【安装】则只执行下面这步(除了密码一律选择Y)
     mysql_secure_installation
    
     # 如果是数据库【升级】则只执行下面这步(**************表示数据库之前的密码)
     mysql_upgrade -u root -p
     **************
    
  15. 修改集群相关配置

     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!!!
    
  16. 配置防火墙(2台服务器均执行)

     yum install firewalld -y
     systemctl enable firewalld
     systemctl start  firewalld
     firewall-cmd --permanent --zone=public --add-port=3306/tcp
     firewall-cmd --reload
    
  17. 创建服务器数据同步账号密码

     mysql -u root -p
     **************
     SET GLOBAL validate_password_policy=0;
     GRANT REPLICATION SLAVE ON *.* TO 'mysql'@'192.168.%' IDENTIFIED BY '**************';
     FLUSH PRIVILEGES;
    
  18. 从服务器中指定二进制文件路径

     # 这里如果是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端口是否打开
    
  19. 解决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!!!
    
  20. 分别在任意一台数据库服务器操作数据,看另一台是否发生了变化,如果发生相同变化,则数据库服务器集群搭建成功

MySQL官方不推荐mysql-proxy,以下内容仅供参考

  1. 假设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
    
  2. 配置防火墙(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
    
  3. 编写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
    
  4. 编写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
    
  5. 查看运行情况

     ps aux | grep mysql-proxy
    
  6. 测试读写分离(每台服务器都执行)

     SHOW GLOBAL VARIABLES LIKE '%general%';
     SET GLOBAL general_log_file='/tmp/mysql.log';
     SET GLOBAL general_log=ON;
     tail -f /tmp/mysql.log
     SET GLOBAL general_log=OFF;
    
@耿志环 2012-∞ 冀ICP备17033181号, powered by Gitbook修订: 2019-05-23 13:51:52

results matching ""

    No results matching ""