CentOS7.X搭建MySQL集群(互为主从)

  1. 云栖社区>
  2. 博客>
  3. 正文

CentOS7.X搭建MySQL集群(互为主从)

qq2233466866 2018-06-11 16:20:06 浏览1199
展开阅读全文

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. 修改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
  9. 创建数据文件目录、所属组、用户改为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*
  10. 修改SELinux

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

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

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

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

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

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

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

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. 测试读写分离(每台服务器都执行)

    SET GLOBAL general_log_file='/tmp/mysql.log';
    SET GLOBAL general_log=ON;
    tail -f /tmp/mysql.log
    SET GLOBAL general_log=OFF;
  7. 领支付宝红包支持作者

    扫码领支付宝红包

网友评论

登录后评论
0/500
评论
qq2233466866
+ 关注