MySQL双主模式集群部署

目录

1.机器环境

2.mysql双主模式搭建

2.1 解压缩包，创建软连接

2.2 修改配置文件

2.3 配置环境变量

2.4 初始化数据库（24，25机器都要初始化

2.5 启动数据库，并修改root密码（24，25机器都要初始化）

2.6 登陆24机器，查看master的状态

2.7 登陆25机器，查看master的状态

2.8.验证测试

---

1.机器环境

机器信息	角色
172.18.1.24	主
172.18.1.25	主

 

2.mysql双主模式搭建

2.1 解压缩包，创建软连接

[root@bfd-yiz-1p24 /opt]# tar -xf software/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ 
[root@bfd-yiz-1p24 /opt]# ln -s mysql-5.7.20-linux-glibc2.12-x86_64 mysql 
[root@bfd-yiz-1p24 /opt]# useradd mysql            #25机器也要创建mysql用户
[root@bfd-yiz-1p24 /opt]# chown -R mysql.mysql mysql* 

 

2.2 修改配置文件

配置/etc/my.cnf，下面是24机器上的配置文件内容，25机器和24机器上mysql的配置文件内容一致，只是要自增长初始值为2，和修改server-id参数即可。

[root@bfd-yiz-1p24 /opt]# vim /etc/my.cnf
#配置内容如下：
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

server-id=24                             #25机器上这里写25的数值
gtid_mode=on                             #开启gtid模式
enforce_gtid_consistency=on
log-bin=mysql-bin
  
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
  
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
 
# character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE
  
# Default user
user=mysql


auto_increment_increment=2        #自动增长的步长
auto_increment_offset=1           #自动增长的起始数值
  
# slow query
slow_query_log=on
slow_query_log_file=/usr/local/mysql/slow-query.log



# Adjust as your needed
max_connections=512
back_log=256
connect_timeout=10
key_buffer_size=16777216
innodb_buffer_pool_size=536870912
tmp_table_size=536870912
thread_cache_size=100
long_query_time=2
max_allowed_packet=64M


[mysqld_safe]
log-error=/usr/local/mysql/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid

2.3 配置环境变量

配置环境变量，然后同步到25机器上

#配置环境变量，24机器
[root@bfd-yiz-1p24 /usr/local]# vim /etc/profile
#添加下面的内容
export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH
#环境变量生效
[root@bfd-yiz-1p24 /usr/local]# source /etc/profile
# 同步到25机器
[root@bfd-yiz-1p24 /usr/local]# rsync -av mysql-5.7.20-linux-glibc2.12-x86_64  mysql 172.18.1.25:/usr/local
#配置环境变量，25机器也要操作
[root@bfd-yiz-1p25 /usr/local]# vim /etc/profile
#添加下面的内容
export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH
#环境变量生效
[root@bfd-yiz-1p25 /usr/local]# source /etc/profile

2.4 初始化数据库（24，25机器都要初始化

#初始化24机器上mysql
[root@bfd-yiz-1p24 /usr/local/mysql]# ./bin/mysqld --initialize -umysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#会生成密码
2018-12-26T08:43:55.060644Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-12-26T08:43:55.060751Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2018-12-26T08:43:55.060760Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2018-12-26T08:43:55.347583Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-12-26T08:43:55.394659Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-12-26T08:43:55.463950Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6105a21b-08ea-11e9-940d-b083feda0369.
2018-12-26T08:43:55.464843Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-12-26T08:43:55.466665Z 1 [Note] A temporary password is generated for root@localhost: ILm1Qu:0UVCu         #记住该密码    

2.5 启动数据库，并修改root密码（24，25机器都要初始化）

[root@bfd-yiz-1p24 /usr/local/mysql]#  cp support-files/mysql.server  /etc/init.d/mysql.server 
#启动
[root@bfd-yiz-1p24 /usr/local/mysql]# service mysql.server start
#修改24机器上mysql的密码
[root@bfd-yiz-1p24 /usr/local/mysql]# mysql -uroot -p
Enter password:   #输入上面生成的随机密码
mysql> set password = password ("baifendian");
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit

创建repl帐户并授权slave，24,25机器都要执行。

#创建帐户并授权slave,24,25机器都要执行。
[root@bfd-yiz-1p24 /usr/local/mysql]# mysql -uroot -pbaifendian
mysql> GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'baifendian';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.6 登陆24机器，查看master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      550 |              |                  | 6105a21b-08ea-11e9-940d-b083feda0369:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec )

在25机器上配置

mysql>change master to master_host='172.18.1.24', master_user='repl', master_password='baifendian', master_port=3306, master_auto_position=1;
#启动slave
mysql>start slave;
mysql>show slave status\G
# 如果看到两个yes ,如下所示,则表示成功
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

2.7 登陆25机器，查看master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      570 |              |                  | 6105a21b-08ea-11e9-940d-b083feda0369:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec )

在24机器上配置

mysql> change master to master_host='172.18.1.25', master_user='repl', master_password='baifendian', master_port=3306, master_auto_position=1;
#启动slave
mysql>start slave;
mysql>show slave status\G
# 如果看到两个yes ,如下所示,则表示成功
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

 

2.8.验证测试

1.在24上创建testdb1数据库，在25上查看是否存在testdb1数据库。

2.在25上创建testdb2数据库，在24上查看是否存在testdb2数据库。