mysql双主配置
测试环境:mysql8
注:MySQL双主需要配置默认跳过几条报错:1062,1053,1032,1146,1051,1050,ddl_exist_errors
1. 下载mysql,并初始化密码
已测试环境:centos7,centos8
注:yum下载mysql一般无初始密码,安装包下载一般密码在:/var/log/mysqld.log或/var/log/mysql/mysqld.log
2. 改配置
注1:使用mysql_native_password是为了方便配置密码
注2:以下配置是为了应对网络抖动引起的读写切换,防止短时间反复读写易位后产生主键冲突(比如写操作从节点一切换到节点二片刻后再次切回,节点二正在读取主键为100的数据,主键101排队,此时节点二成为主,写入主键101,102;此时节点一再次写入主键102。。。);但是如此配置会导致主键不再连续
节点1:
auto-increment-increment=2
auto-increment-offset=1
节点2:
auto-increment-increment=2
auto-increment-offset=2
各节点详细配置如下:
节点一:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid default-authentication-plugin=mysql_native_password server-id=1 log-bin=no binlog_format=mixed relay-log=relay-bin relay-log-index=slave-relay-bin.index auto-increment-increment=2 auto-increment-offset=1 log-slave-updates slave_skip_errors=1062,1053,1032,1146,1051,1050,ddl_exist_errors sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
节点二:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid default-authentication-plugin=mysql_native_password server-id=2 log-bin=on binlog_format=mixed relay-log=relay-bin relay-log-index=slave-relay-bin.index auto-increment-increment=2 auto-increment-offset=2 log-slave-updates slave_skip_errors=1062,1053,1032,1146,1051,1050,ddl_exist_errors sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
改完配置后,重启数据库
systemctl restart mysqld
3. 创建共享用户,并配置同步
两个节点同时执行以下命令
CREATE USER share@'%' IDENTIFIED BY 'Admin123!';
GRANT REPLICATION SLAVE ON *.* to share@'%';
reset master;
change master to master_host='192.168.202.10',master_port=3306,master_user='share',master_password='Admin123!';
4. 验证
show master status;
show slave status\G
示例
注意:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#横向看主(数据少)
mysql> show master status; +-----------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-----------+----------+--------------+------------------+-------------------+ | no.000001 | 4054 | | | | +-----------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
#纵向看从(数据太多) mysql> show master status\G *************************** 1. row *************************** File: no.000001 Position: 4054 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.202.10 Master_User: share Master_Port: 3306 Connect_Retry: 60 Master_Log_File: on.000001 Read_Master_Log_Pos: 4103 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1213 Relay_Master_Log_File: on.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 4103 Relay_Log_Space: 1416 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: 58a1741a-a12c-11ed-8177-0050569c7c4a Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.01 sec) mysql>
5. 测试示例
1)从任意节点建库建表,两个节点上的库表都能同步
create database if not exists testdb1; create table testdb1.table01( id INT UNSIGNED AUTO_INCREMENT primary key, name VARCHAR(100) NOT NULL, sex VARCHAR(40) NOT NULL, age smallint,
salary int default 0
);
insert into testdb1.table01(name,sex,age,salary) values('tom','man',18,9000);
delete from testdb1.table01 where name='tom';
drop table testdb1.table01;
drop database testdb1;
验证
show databases; show tables from testdb1; desc testdb1.table01; select * from testdb1.table01;