mysql双主热备集群搭建

1. 安装前清理

   • 由于centos7中使用mariadb代替了MySQL,所以需要先清理mariadb

     rpm -qa|grep mariadb

     rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps

     

   • 下载并安装mysql

     下载并安装MySQL官方的 Yum Repository

     wget -i -c https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

     yum -y install mysql80-community-release-el7-1.noarch.rpm

     安装MySQL服务器

     yum -y install mysql-community-server

   • 启动MySQL

     systemctl start mysqld.service

     systemctl status mysqld.service

   • Mysql****初始化配置

     cat /var/log/mysqld.log | grep password

     mysql在安装后会创建一个root@locahost账户，并且把初始的密码放到了/var/log/mysqld.log文件中；

• 登录并修改密码

  mysql -u root -p

  mysql -h192.168.88.52 -P3306 -ucp -pMyNewPass4!

  ALTER USER ‘root’@‘localhost’ IDENTIFIED with mysql_native_password BY ‘MyNewPass4!’;

  update user set host =’%’ where user =‘root’;

• 防火墙设置

  关闭防火墙：systemctl stop firwalld

  禁止防火墙开机启动：systemctl disable firealld

  防火墙开放3306端口：firewall-cmd --zone=public --add-port=3306/tcp --permanent

  防火墙重新载入设置：firewall-cmd --reload

• 使用navicat连接

  如果提示 Host XXX is not allowed to connect to this MySQL server

  use mysql;

  select host from user where user =‘root’;

  update user set host =’%’ where user =‘root’;

  alter user ‘root’@‘localhost’ identified with mysql_native_password by ‘MyNewPass4!’;
  

  如果出现 MySQL 连接出现 Authentication plugin caching_sha2_password cannot be loaded

  ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘MyNewPass4!’;

  flush privileges;

• 关闭

  vi /etc/selinux/config

  SELINUX=disabled

2. 双机热备

   A服务器:192.168.88.52服务器,

   B服务器:192.168.88.179服务器

   1. 配置由A—>B的主从同步

      A服务器操作:

      • 创建用户备份的用户

        CREATE USER ‘cp_user’@‘192.168.88.179’ IDENTIFIED WITH mysql_native_password BY ‘MyNewPass4!’;

        GRANT REPLICATION SLAVE ON . TO ‘cp_user’@‘192.168.88.179’;

      • 修改 MySQL配置文件：vim /etc/my.cnf，配置内容如下:

        port=3306
        datadir=/var/lib/mysql
        socket=/var/lib/mysql/mysql.sock
        log-error=/var/log/mysqld.log
        pid-file=/var/run/mysqld/mysqld.pid
        #开始binlog日志
        log-bin=mysql-bin
        #设置binlog日志模式, 
        #STATEMENT:记录的是数据库上执行的原生SQL语句
        #ROW:记录的是数据表的行是怎样被修改的。
        #MIXED:混合模式，
        #如果设置了这种格式，MariaDB / MySQL 会在一些特定的情况下自动从 STATEMENT 格式切换到 ROW 格式。
        #例如，包含 UUID 等不确定性函数的语句，引用了系统变量的语句等等。
        binlog_format=mixed
        #=================开启binlog
        #设置该数据库是否为只读库 0否1是
        read-only=0
        #服务器唯一表示,每台服务器必须配置不一样,最好不要为0
        server-id=5
        #需要备份的数据库名称(可选)
        binlog-do-db=test_db
        #表示自增字段每次递增的量
        auto-increment-increment=2
        #表示自增字段初始值,从1开始,不能超过auto-increment-increment
        auto-increment-offset=1
        

      • 修改完毕保存后，重新启动mysql

        service mysqld restart

      • 登录mysql后,查询master状态

        mysql -uroot -pMyNewPass4!

        show master status

记住mysql-bin.000001和1554 这两个值,后续配置从服务器时会用到,这两个值可能会不一样

mysql-bin.000001:binlog文件的名称

1554:从文件什么位置开始读取

B服务器操作:

• 修改 MySQL配置文件：vim /etc/my.cnf，配置内容如下:

  
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  log-bin=mysql-bin
  binlog_format=mixed
  server-id=12
  
  #开启中继日志
  replicate-do-db=test_db
  relay-log=mysql.relay.bin
  log-slave-updates=ON
  

  • 登录MySQL后,执行同步命令

    - CHANGE MASTER TO  MASTER_HOST='192.168.88.52',MASTER_USER='cp_user',MASTER_PASSWORD='MyNewPass4!', master_log_file='mysql-bin.000001',master_log_pos=857;
    

    重启mysql,查询slave状态

    service mysql restart

    mysql -uroot -pMyNewPass4!

    Show slave status\G
    

    Last Error 为0 即可认为配置正确。

    如果出现connection error 考虑关闭A服务器防火墙或清空iptables （iptables -F)

  • 测试

    在A和B服务器上登陆MySQL运行如下脚本创建数据库test_db;

    CREATE DATABASE IF NOT EXISTS test_db default charset utf8 COLLATE utf8_general_ci;
    

    单独在A服务器上创建表，并插入数据

    USE test_db;
    
    CREATE TABLE user(
    	id int not null auto_increment,
    	user_name VARCHAR(50),
    	password VARCHAR(10) ,
    	name VARCHAR(50),
    	status VARCHAR(10) ,
    	constraint pk__person primary key(id)
    );
    INSERT INTO user (`user_name`,`password`,`name`,`status`) VALUES('admin','admin','admin','1');
    

    到B服务器上test_db中查看是否同步了相同的表和数据

    已同步，则配置A—>B 的主从复制完成

2. 配置由B—>A的主从同步

   B服务器操作:

   • 创建同步A服务器的用户

     CREATE USER ‘cp_user’@‘192.168.88.52’ IDENTIFIED WITH mysql_native_password BY ‘MyNewPass4!’;

     GRANT REPLICATION SLAVE ON . TO ‘cp_user’@‘192.168.88.52’;

   • 开启binlog

     vim /etc/my.cnf 插入如下内容

     #开启binlog
     read-only=0
     binlog-do-db=test_db
     auto-increment-increment=1
     auto-increment-offset=1
     

   • 显示master状态

     show master status

   A服务器操作:

   • 开启中继日志,添加如下配置

     #开启中继日志
     replicate-do-db=test_db
     relay-log=mysqld-relay-bin
     log-slave-updates=ON
     

   • 执行同步命令

     CHANGE MASTER TO  MASTER_HOST='192.168.88.52',MASTER_USER='cp_user',MASTER_PASSWORD='MyNewPass4!', master_log_file='mysql-bin.000001',master_log_pos=857;
     

     master_log_file和master_log_pos填入B服务器对应的master的值

   • 查看slave状态

     show slave status\G;
     

     此时A—>B 和B—>A的同步均已完成

3. 定时备份数据库

   • 创建本分目录

     mkdir /var/backups/mysql

   • 创建备份脚本并赋予可执行权限

     touch mysqlback.sh

     vim mysqlback.sh

     #获取当前时间
     date_now=$(date "+%Y%m%d-%H%M%S")
     backUpFolder=/var/backups/mysql
     username="root"
     password="MyNewPass4!"
     db_name="test_db"
     #定义备份文件名
     fileName="${db_name}_${date_now}.sql"
     #定义备份文件目录
     backUpFileName="${backUpFolder}/${fileName}"
     echo "starting backup mysql ${db_name} at ${date_now}."
     /usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
     #进入到备份文件目录
     cd ${backUpFolder}
     #压缩备份文件
     tar zcvf ${fileName}.tar.gz ${fileName}
     
     # use nodejs to upload backup file other place
     #NODE_ENV=$backUpFolder@$backUpFileName node /home/tasks/upload.js
     date_end=$(date "+%Y%m%d-%H%M%S")
     echo "finish backup mysql database ${db_name} at ${date_end}."
     
     

     chmod 751 mysqlback.sh

   • 进入linux crontab 定时任务编辑界面

     crontab -e

   • 添加命令

     */5 * * * * sh /var/backups/mysql/mysqlback.sh