mysql 双主热备配置

最新推荐文章于 2022-09-28 06:49:16 发布

zhc794429974

最新推荐文章于 2022-09-28 06:49:16 发布

阅读量413

点赞数

本文链接：https://blog.csdn.net/zhc794429974/article/details/103738103

版权

安装mysql

参照 https://www.cnblogs.com/daemon-/p/9009360.html

系统、mysql版本

mysql-5.7.28-linux 端口3306

slave3: 192.168.64.10

slave4: 192.168.64.11

mysql配置信息

1、关闭防火墙

2、slave3（192.168.64.10）

[root@localhost ~]# cat /etc/my.cnf
[client]
default-character-set=utf8


[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
# 取消密码验证
#skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# skip-grant-tables



# master1
log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment=2
auto-increment-offset=1
server-id=1    #服务id 俩个服务器保证不一致
log_slave_updates=1
read_only=0

#mysql双主配置
binlog-ignore_db=mysql  #忽略记录二进制日志的数据库
#replicate-do-db=test   #指定复制的数据库
replicate_ignore_db=mysql   #不复制的数据库
binlog-ignore-db=information_schema #不复制的数据库
binlog-ignore-db=performance_schema #不复制的数据库
log-slave-updates=1 #该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要
auto_increment_offset=2 #该服务器自增列的初始值
auto_increment_increment=2  #该服务器自增列增量
relay_log=mysql-relay-bin   #从库的中继日志，主库日志写到中继日志，中继日志再重做到从



[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

3、slave4（192.168.64.11）

[root@localhost ~]# cat /etc/my.cnf
[client]
default-character-set=utf8


[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
#取消密码验证
#skip-grant-tables
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-grant-tables


# master2
log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment=2
auto-increment-offset=2
server-id=2
log_slave_updates=1
read_only=1


#mysql双主配置
#binlog-do-db=test  #需要记录到二进制日志的数据库
binlog-ignore_db=mysql  #忽略记录二进制日志的数据库
#replicate-do-db=test   #指定复制的数据库
replicate_ignore_db=mysql   #不复制的数据库
binlog-ignore-db=information_schema #不复制的数据库
binlog-ignore-db=performance_schema #不复制的数据库
log-slave-updates=1 #该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要
auto_increment_offset=1 #该服务器自增列的初始值
auto_increment_increment=2  #该服务器自增列增量
relay_log=mysql-relay-bin   #从库的中继日志，主库日志写到中继日志，中继日志再重做到从库



[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

4、重启mysql，slave3、slave4

mysql> service mysql restart

搭建双主

1、连接数据库，查看日志记录的位置

mysql> show master status;

File :当前正在记录的二进制日志文件
Position ：记录偏移量，日志 mysql-bin.000010 所记录到的位置。
Binlog_Do_DB ：要记录日志的数据库
Binlog_Ignore_DB ：不记录日志的数据库
Executed_Gtid_Set ：已执行的事务ID

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

确保log_bin是开启状态

2、连接slave3（192.168.64.10），创建slave4（192.168.64.11）的同步账号

mysql> grant replication slave on *.* to 'replUser'@'192.168.64.11' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush  privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user, host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| root          | %             |
| replUser      | 192.168.64.10 |
| repl_user     | 192.168.64.10 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
+---------------+---------------+
5 rows in set (0.50 sec)

3、获取slave3（192.168.64.10）数据日志记录位置相关信息

mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000010 |      616 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

4、连接slave4（192.168.64.11），使用刚在slave3（192.168.64.10）数据库创建的同步账号连接到master主库（slave3）

mysql> CHANGE MASTER TO 
    ->    MASTER_HOST='192.168.64.10', 
    ->    MASTER_USER='replUser', 
    ->    MASTER_PASSWORD='123456', 
    ->    MASTER_LOG_FILE='mysql-bin.000010', 
    ->    MASTER_LOG_POS=616;    #对应pos的位置值
Query OK, 0 rows affected, 2 warnings (0.25 sec)

mysql> start slave;    #启动同步
Query OK, 0 rows affected (0.00 sec)

5、连接slave4（192.168.64.11），创建slave3（192.168.64.10）的同步账号

mysql> grant replication slave on *.* to 'replUser'@'192.168.64.10' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.53 sec)

mysql> flush  privileges;
Query OK, 0 rows affected (0.45 sec)

6、获取slave4（192.168.64.11）数据日志记录位置相关信息

mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000005 |      608 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.36 sec)

7、连接slave3（192.168.64.10），使用刚在slave4（192.168.64.11）数据库创建的同步账号连接到master主库（slave4）

mysql> CHANGE MASTER TO 
    ->    MASTER_HOST='192.168.64.11', 
    ->    MASTER_USER='replUser', 
    ->    MASTER_PASSWORD='123456', 
    ->    MASTER_LOG_FILE='mysql-bin.000005', 
    ->    MASTER_LOG_POS=608;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

8、主主同步配置完毕，查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.64.11
                  Master_User: replUser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 608
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 608
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: da0db970-27ea-11ea-9b0d-000c29ab4a3c
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

配置完成双主，相互在两个数据库操作数据，测试数据同步

slave3上创建数据库及表，自动同步到slave4

mysql> create database mytest;
Query OK, 1 row affected (0.05 sec)

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

mysql> use mytest;
Database changed

mysql> insert into demo(value) values(1),(1),(1),(1),(1);                                                                             Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> select * from demo;
+----+-------+
| id | value |
+----+-------+
|  2 |     1 |
|  4 |     1 |
|  6 |     1 |
|  8 |     1 |
| 10 |     1 |
| 11 |     3 |
+----+-------+
6 rows in set (0.00 sec)

切换到slave4，可看到这些数据。

start slave；

stop slave；

reset slave；

https://www.jianshu.com/p/68ce29345441

zhc794429974

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
mysql 双主热备配置

安装mysql 参照https://www.cnblogs.com/daemon-/p/9009360.html系统、mysql版本 mysql-5.7.28-linux 端口3306slave3:192.168.64.10slave4:192.168.64.11mysql配置信息1、关闭防火墙2、slave3（192.168.64.10）[r...
复制链接

扫一扫