数据库日常学习案例20240118- MYSQL 8.0 双主搭建

-- mysql8 主主搭建

mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz

主1 ： 192.168.2.160 
主2 ： 192.168.2.161

--解压mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz为mysql8

-- 初始化mysql8 （略）

-- 参数192.168.2.160

[root@19cdg01 mysql]# ps -ef| grep mysql
root      1552  1433  0 01:00 pts/0    00:00:00 su - mysql
mysql     1554  1552  0 01:00 pts/0    00:00:00 -bash
mysql     1602  1554  0 01:00 pts/0    00:00:00 /bin/sh ./mysqld_safe --defaults-file=/mysql/mysql8/my.cnf --user=mysql
mysql     1877  1602  0 01:00 pts/0    00:00:19 /mysql/mysql8/bin/mysqld --defaults-file=/mysql/mysql8/my.cnf --basedir=/mysql/mysql8 --datadir=/mysql/mysql8/data --plugin-dir=/mysql/mysql8/lib/plugin --log-error=/mysql/mysql8/data/error.log --pid-file=19cdg01.pid --socket=/mysql/mysql8/mysql.sock --port=3306
mysql     2123  1554  0 01:04 pts/0    00:00:00 ./mysql -uroot -h127.0.0.1 -p
root      4317  4118  0 01:47 pts/2    00:00:00 grep --color=auto mysql

[root@19cdg01 mysql]# cat /mysql/mysql8/my.cnf
[mysqld]
server_id=3306
admin_port=33062
mysqlx_port=33060
socket=/mysql/mysql8/mysql.sock
mysqlx_socket=/mysql/mysql8/mysqlx.sock
log-error=/mysql/mysql8/data/error.log
port=3306
basedir=/mysql/mysql8
datadir=/mysql/mysql8/data
log_bin=binlog
log_bin_index=/mysql/mysql8/data/binlog.index
gtid_mode=on
enforce_gtid_consistency=true
auto-increment-increment = 2
auto-increment-offset = 1
 
binlog-ignore-db = mysql,information_schema,performance_schema,sys
 
 
[client]
port=3306
socket=/mysql/mysql/mysql88888888.sock
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
[root@19cdg01 mysql]#

-- 参数192.168.2.161

[root@19cdg02 ~]# ps -ef| grep mysql
root      1583  1438  0 01:00 pts/0    00:00:00 /bin/sh ./mysqld_safe --defaults-file=/mysql/mysql8/my.cnf --user=mysql
mysql     1855  1583  0 01:00 pts/0    00:00:20 /mysql/mysql8/bin/mysqld --defaults-file=/mysql/mysql8/my.cnf --basedir=/mysql/mysql8 --datadir=/mysql/mysql8/data --plugin-dir=/mysql/mysql8/lib/plugin --user=mysql --log-error=/mysql/mysql8/data/error.log --pid-file=19cdg02.pid --socket=/mysql/mysql8/mysql.sock --port=3306
root      2105  1438  0 01:04 pts/0    00:00:00 ./mysql -uroot -h127.0.0.1 -p
root      4303  4277  0 01:48 pts/2    00:00:00 grep --color=auto mysql
[root@19cdg02 ~]# cat /mysql/mysql8/my.cnf
[mysqld]
server_id=33061
admin_port=33062
mysqlx_port=33060
socket=/mysql/mysql8/mysql.sock
mysqlx_socket=/mysql/mysql8/mysqlx.sock
log-error=/mysql/mysql8/data/error.log
port=3306
basedir=/mysql/mysql8
datadir=/mysql/mysql8/data
log_bin=binlog
log_bin_index=/mysql/mysql8/data/binlog.index
gtid_mode=on
enforce_gtid_consistency=true
auto-increment-increment = 2
auto-increment-offset = 2
 
binlog-ignore-db = mysql,information_schema,performance_schema,sys
 
 
[client]
port=3306
socket=/mysql/mysql8/mysql.sock
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
[root@19cdg02 ~]#
-- 开启半同步 (8.0和5.7的半同步插件，不一样。安装5.7的插件也可以，log中有提示，即将被淘汰)

--INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
--SET GLOBAL rpl_semi_sync_master_enabled=ON;
install plugin rpl_semi_sync_source soname 'semisync_source.so';
set global rpl_semi_sync_source_enabled=1;

-- 启动数据库

./mysqld_safe --defaults-file=/mysql/mysql8/my.cnf --user=mysql
./mysqld_safe --defaults-file=/mysql/mysql8/my.cnf --user=mysql
-- 连接mysql

./mysql -uroot -h127.0.0.1 -p
./mysql -uroot -h127.0.0.1 -p

-- 创建复制账号rep1

CREATE USER 'rep1'@'192.168.2.%' IDENTIFIED WITH 'mysql_native_password' BY 'mysql';
grant replication slave on *.* to 'rep1'@'192.168.2.%';
flush privileges;
-- 设置复制.没有指定具体的log和position，使用参数MASTER_AUTO_POSITION = 1

CHANGE MASTER TO master_host='192.168.2.160', master_port=3306,master_user='rep1',master_password='mysql',MASTER_AUTO_POSITION = 1;
CHANGE MASTER TO master_host='192.168.2.161', master_port=3306,master_user='rep1',master_password='mysql',MASTER_AUTO_POSITION = 1;
start slave ;
show slave status ;

## 出现的问题及解决方法

1 因为刚开始没有设置binlog-ignore-db，分别在两个库上都建立和删除过账号，导致复制冲突。
1 节点1 上，log  drop user 'rep1'@'192.168.2.%'失败，执行事务'94c47d50-a0a1-11ee-86c9-000c299429f9:5'失败

2023-12-23T06:00:49.431587Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /mysql/mysql8/mysqlx.sock
2023-12-23T06:00:49.456284Z 10 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction '94c47d50-a0a1-11ee-86c9-000c299429f9:5' at source log binlog.000004, end_log_pos 392; Error 'Operation DROP USER failed for 'rep1'@'192.168.2.%'' on query. Default database: ''. Query: 'drop user 'rep1'@'192.168.2.%'', Error_code: MY-001396
2023-12-23T06:00:49.458896Z 6 [ERROR] [MY-010586] [Repl] Error running query, replica SQL thread aborted. Fix the problem, and restart the replica SQL thread with "START REPLICA". We stopped at log 'binlog.000004' position 197
2023-12-23T06:01:00.804824Z 14 [Warning] [MY-010055] [Server] IP address '192.168.2.161' could not be resolved: Name or service not known
2023-12-23T06:01:00.806701Z 14 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-12-23T06:01:49.442498Z 8 [System] [MY-014002] [Repl] Replica receiver thread for channel '': connected to source 'rep1@192.168.2.161:3306' with server_uuid=94c47d50-a0a1-11ee-86c9-000c299429f9, server_id=33061. Starting GTID-based replication.
2 节点2上，log  create user CREATE USER 'rep1'@'%' IDENTIFIED WITH 'mysql_native_password'  失败 ，执行事务'9fa8b4aa-a0a0-11ee-86d4-000c29a929d8:10'失败

023-12-23T06:01:00.641166Z 8 [System] [MY-014002] [Repl] Replica receiver thread for channel '': connected to source 'rep1@192.168.2.160:3306' with server_uuid=9fa8b4aa-a0a0-11ee-86d4-000c29a929d8, server_id=3306. Starting GTID-based replication.
2023-12-23T06:01:00.662057Z 9 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction '9fa8b4aa-a0a0-11ee-86d4-000c29a929d8:10' at source log binlog.000003, end_log_pos 1580; Error 'Operation CREATE USER failed for 'rep1'@'%'' on query. Default database: ''. Query: 'CREATE USER 'rep1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA'', Error_code: MY-001396
2023-12-23T06:01:00.664259Z 6 [ERROR] [MY-010586] [Repl] Error running query, replica SQL thread aborted. Fix the problem, and restart the replica SQL thread with "START REPLICA". We stopped at log 'binlog.000003' position 197
2023-12-23T06:01:49.333516Z 14 [Warning] [MY-010055] [Server] IP address '192.168.2.160' could not be resolved: Name or service not known
2023-12-23T06:01:49.334585Z 14 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

1 节点1上跳过事务

stop slave;
select @@server_uuid;                                            <<< 查看当前服务器的uuid，可以不执行
set session gtid_next='94c47d50-a0a1-11ee-86c9-000c299429f9:5';  <<< 这个事务失败，设置下一个执行的事务为这个事务
begin;commit;                                                    <<< 执行一个空事务，欺骗库，执行过了上面的事务
set session gtid_next='AUTOMATIC';                               <<< 自动执行下一个事务 

2 节点2上跳过事务

stop slave;
select @@server_uuid;
set session gtid_next='9fa8b4aa-a0a0-11ee-86d4-000c29a929d8:10';
begin;commit;
set session gtid_next='AUTOMATIC';
另一种解决方法 （未测试）
在会话层面关闭sql log ，set session sql_log_bin = 0;
然后执行缺失的sql 。（sql 可以通过查看binlog的日志获取）

END
————————————————
版权声明：本文为CSDN博主「文档搬运工」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xxzhaobb/article/details/135169184