MySQL双主模式集群部署

我不是女神ヾ 2022-05-15 03:23 512阅读 0赞

**目录**

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.机器环境 ##

## 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数据库。