Mysql双主配置 GTID...

1. 安装mysql

1.1.解压安装mysql

#tar zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
#mv mysql-5.7.32-linux-glibc2.12-x86_64 /forp/mysql-5.7.32
#chown -R forp1:root /forp/mysql-5.7.32

1.2.创建相关目录

下面所有操作切换至普通用户forp1下执行
su – forp1
$ mkdir /forp/mysql-5.7.32/data
$ mkdir /forp/mysql-5.7.32/conf
$ mkdir /forp/mysql-5.7.32/logs
$ mkdir /forp/mysql-5.7.32/temp

1.3.创建配置文件

cat /forp/mysql-5.7.32/conf/mysql.conf

[mysqld]
port=3306
socket=/forp/mysql-5.7.32/temp/mysql.sock
user=forp1
datadir=/forp/mysql-5.7.32/data
default-time_zone='+8:00'
log-output=FILE
log-error=/forp/mysql-5.7.32/logs/error.log
long_query_time=8
slow_query_log=1
slow_query_log_file =/forp/mysql-5.7.32/logs/slow.log
general_log=0 
general_log_file=/forp/mysql-5.7.32/logs/general.log
back_log=900
sync_binlog=1
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
interactive_timeout=30000000 
wait_timeout=30000000
lower_case_table_names=1
max_connections=1000
max_connect_errors=5000
#slave-skip-errors = all

1.4.生成密码

$./mysqld --initialize --datadir=/forp/mysql-5.7.32/data
  A temporary password is generated for root@localhost: speQ2;ILfUB7

1.5.启动服务

$./mysqld_safe --defaults-file=/forp/mysql-5.7.32/conf/mysql.conf &

1.6.登录

$ mysql -S /forp/mysql-5.7.32/temp/mysql.sock -uroot -p speQ2;ILfUB7
mysql>

#如果报错libncurses.so.5 【yum install libncurses*】

1.7.修改root密码

mysql> alter user root@'localhost' identified by 'root';

2. 双主配置

2.1.架构方案：

两台mysql数据库服务器masterA和masterB,两台都可读写，互为主从。

主机名	主机名	IP	角色
masterA	MySQL-5.7.32	192.168.2.100	与B互为主从
masterB	MySQL-5.7.32	192.168.2.200	与A互为主从

2.2.masterA：

2.2.1.修改配置文件

在masterA主机的/forp/mysql-5.7.32/conf/mysql.conf中添加如下几行
vi /forp/mysql-5.7.32/conf/mysql.conf

[mysqld]
log_bin=master100
 server_id=100
 auto_increment_offset = 1
 auto_increment_increment = 2
 log-slave-updates = true    

#systemctl start mysqld 重启服务
登录数据库查看主库的状态

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

添加主从同步账号
mysql> grant replication slave on . to 'forp’@”%” identified by '123456’;

2.2.2.配置B为A的从

在masterB主机上登录mysql数据库，执行如下命令，配置masterB为masterA的从。

mysql> change master to
master_host="192.168.2.100",
master_port=3306,
master_user="forp",
master_password="123456",
master_log_file="masterA.000001",
master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;

显示有如下状态则正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

2.3.masterB

2.3.1.修改配置文件

在masterB主机的/forp/mysql-5.7.32/conf/mysql.conf中添加如下几行
vi /forp/mysql-5.7.32/conf/mysql.conf

[mysqld]
log_bin=master200
 server_id=200
 auto_increment_offset = 2
 auto_increment_increment = 2
 log-slave-updates = true    

#systemctl start mysqld 重启服务
登录后添加主从同步账号
mysql> grant replication slave on . to ''forp’’@’’%’’ identified by ''123456’’;
mysql> flush privileges;

查看主库的状态

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

2.3.2.配置A为B的从

在A主机执行：

mysql> change master to
master_host="192.168.2.200",
master_port=3306
master_user="forp",
master_password="FoRp_g147369",
master_log_file="masterB.000001",
master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;

显示有如下状态则正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3. 配置GTID

在所有主机的/etc/my.cnf中添加如下几行

[mysqld]
log-slave-updates = true    
gtid_mode=ON
enforce_gtid_consistency=ON

#]systemctl start mysqld 重启服务