Mysql（双主）架构配置

主主环境（这里只介绍2台主的配置方案）:

1.CentOS 7.3 64位

2.官方Mysql5.6版本

搭建过程：

1.安装MySQL服务
下载免编译的二进制包

wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

解压：

tar zxvf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

移动并重命名； 进入新的目录；

mv mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql
cd /usr/local/mysql

增加一个mysql的用户； 创建一个data的目录；

useradd mysql
mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql

初始化配置；

./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

等待完成后一个非常重要的操作是检查有没有成功~

拷贝配置模板到指定目录下并重命名！

cp support-files/my-default.cnf /etc/my.cnf

制作启动脚本

[root@zhdy-02 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@zhdy-02 mysql]# vi /etc/init.d/mysqld 

#vim编辑下面两行basedir和datadir配置
basedir=/usr/local/mysql
datadir=/data/mysql

[[email protected] mysql]# chmod 755 /etc/init.d/mysqld

启动服务并设为开机启动

[root@zhdy-02 mysql]# chkconfig --add mysqld

[root@zhdy-02 mysql]# chkconfig --list

[root@zhdy-02 mysql]# /etc/init.d/mysqld start
#或者 :
[root@zhdy-02 mysql]# service mysqld start

查看启动状态：

[root@zhdy-02 mysql]# ps aux |grep mysql
[root@zhdy-02 mysql]# netstat -lntp |grep mysql

配置主主同步

2.配置 /etc/my.cnf

masterA（192.168.59.132） 配置文件

[[email protected]04 mysql]# vim /etc/my.cnf

server-id=132
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates

重启mysql：

[root@zhdy-04 mysql]# /etc/init.d/mysqld restart

masterB（192.168.59.133） 配置文件

server-id=133
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates

重启

[root@zhdy-04 mysql]# /etc/init.d/mysqld restart

auto-increment 两行的配置，使 masterA字段产生的数值是 奇数1，3，5，7 下面的masterB 产生的是 2，4，6，8 等，这样会避开双主 id 重复的问题。

2.2 添加主从同步账户

masterA上：

[root@zhdy-04 mysql]# export PATH=$PATH:/usr/local/mysql/bin/

[root@zhdy-04 mysql]# vim /etc/profile

[root@zhdy-04 mysql]# source /etc/profile

[root@zhdy-04 mysql]# mysql -uroot

mysql> grant replication slave on *.* to 'repl'@'192.168.59.133' identified by 'zhangduanya';

mysql> flush privileges;

masterB上：

[root@zhdy-05 mysql]# export PATH=$PATH:/usr/local/mysql/bin/

[root@zhdy-05 mysql]# vim /etc/profile

[root@zhdy-05 mysql]# source /etc/profile

[root@zhdy-05 mysql]# mysql -uroot

mysql> grant replication slave on *.* to 'repl'@'192.168.59.132' identified by 'zhangduanya';

mysql> flush privileges;

查看主库的状态
masterA上：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      208 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

masterB上：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置同步信息：

masterA上：

mysql> change master to master_host='192.168.59.133',master_port=3306,master_user='repl',master_password='zhangduanya',master_log_file='mysql-bin.000001',master_log_pos=120;

mysql> start slave;

mysql> show slave status\G


*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.59.133
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: zhdy-04-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
            

masterB上：

mysql> change master to master_host='192.168.59.132',master_port=3306,master_user='repl',master_password='zhangduanya',master_log_file='mysql-bin.000001',master_log_pos=208;

mysql> start slave;

mysql> show slave status\G


*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.59.132
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 208
               Relay_Log_File: zhdy-05-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试主主同步

masterA上创建一个数据库测试同步效果

mysql> create database asd;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asd                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

到masterB查看是否已经同步创建数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asd                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

已经成功！！！