mysql 双主热备配置
安装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)
[aaa@qq.com ~]# 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)
[aaa@qq.com ~]# 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