MySQL两主配置

双主模式：两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。

1. 安装wget依赖

   yum install wget -y

2. 下载mysql5.7.26到data目录

   wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

3. 解压mysql

   tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

4. 修改mysql文件夹

   mv mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mysql-5.7.26

5. 创建mysql数据存储文件

   cd /mysql-5.7.26

   mkdir data

   chmod -R 777 /data/mysql-5.7.26/data/

6. 创建组和用户，并将用户加入组

   groupadd mysql

   useradd -g mysql mysql

7. 修改Master1 mysql配置文件

   vi /etc/my.cnf

   [mysqld]
   bind-address=0.0.0.0
   port=3306
   log-bin=mysql-bin
   sync-binlog=1
   #双主配置
   relay_log=mysql-relay-bin
   log_slave_updates=1
   auto_increment_offset=1
   auto_increment_increment=2
   #skip-grant-tables
   user=mysql
   basedir=/data/mysql-5.7.26
   datadir=/data/mysql-5.7.26/data
   socket=/tmp/mysql.sock
   log-error=/data/mysql-5.7.26/data/mysql.err
   pid-file=/data/mysql-5.7.26/data/mysql.pid
   #character config
   character_set_server=utf8mb4
   symbolic-links=0
   explicit_defaults_for_timestamp=true

8. 修改Master2 my.cnf文件

   [mysqld]
   server-id=2
   log-bin=mysql-bin
   sync-binlog=1
   relay_log=mysql-relay-bin
   log_slave_updates=1
   auto_increment_offset=2
   auto_increment_increment=2
   bind-address=0.0.0.0
   port=3306
   #skip-grant-tables
   user=mysql
   basedir=/data/mysql-5.7.26
   datadir=/data/mysql-5.7.26/data
   socket=/tmp/mysql.sock
   log-error=/data/mysql-5.7.26/data/mysql.err
   pid-file=/data/mysql-5.7.26/data/mysql.pid
   log-slave-updates =  1
   #character config
   character_set_server=utf8mb4
   symbolic-links=0
   explicit_defaults_for_timestamp=true

9. 进入bin目录，初始化mysql

   cd /data/mysql-5.7.26/bin

   ./mysqld --initialize --user=mysql --datadir=/data/mysql-5.7.26/data/ --basedir=/data/mysql-5.7.26/

10. 查看mysql初始化密码

    cat /data/mysql-5.7.26/data/mysql.err

11. 添加软链接，可以在任意目录执行mysql命令

    ln -s /data/mysql-5.7.26/support-files/mysql.server /etc/init.d/mysql

    ln -s /data/mysql-5.7.26/bin/mysql /usr/bin/mysql

    service mysql restart

12. 登录mysql修改密码

    vi /etc/my.cnf

    去掉#skip-grant-tables的注释

    mysql -u root -p

    use mysql

    修改密码

    update user set authentication_string=password('Pw!123456') where user='root';

    |alter user USER() identified by 'Pw!123456';

    开启远程访问权限

    update user set host ='%' where user ='root';

    刷新权限

    flush privileges;

    重启mysql

    service mysql restart

13. 将mysql端口加入防火墙的白名单

    firewall-cmd --zone=public --add-port=3306/tcp --permanent

    systemctl restart firewalld.service

14. 重启MySQL验证

    service mysql restart

    mysql -u root -p

    查看二进制日志是否开启

    show global variables like '%log_bin%';

    log_bin value 等于ON 说明开启

    查看主节点二进制日志列表 （以后会有多个）

    show master logs;

    查看主节点的serverid

    show global variables like '%server%';

15. 在mysql中创建从服务器的用户和权限

    GRANT REPLICATION SLAVE ON . TO 'root'@'%' IDENTIFIED BY 'abc123';

    flush privileges;

16. 查看状态

    show master status;

17. 配置双主关系

    Maser01
    mysql> stop slave;
    mysql> change master to master_host='192.168.0.103',master_port=3306,master_user='root',master_password='Pw!123456',master_log_file='mysql-bin.000003',master_log_pos=403;
    ​
    ​
    ​
    master02
    mysql> stop slave;
    mysql> change master to master_host='192.168.0.102',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=403;
    ​
    #查看状态
    mysql> start slave;
    mysql> show slave status \G;
    ​
    注意没报错并且Slave_IO_Running和Slave_SQL_Running都为YES，则配置成功。

18. 设置开机自启动

    查看自启动的服务

    chkconfig --list

19. 设置自启动

    cd /etc/rc.d/init.d

    cp /data/mysql-5.7.26/support-files/mysql.server /etc/rc.d/init.d/

    赋予可执行权限：chmod +x /etc/init.d/mysql.server

    添加为服务: chkconfig --add mysql.server

20.