两台创建单实例数据库安装mysql8.0版本的数据库

192.168.0.3

192.168.0.4

官网下载mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar


安装并启动

每次更改/etc/my.cnf配置文件后,重启mysqld

rpm -ivh *.rpm --nodeps --force
systemctl restart mysqld.service
systemctl enable mysqld.service
systemctl stop mysqld.service

主库配置

#[root@mysql001 ~]# cat /etc/my.cnf
[mysqld]

datadir=/data/mysql8/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


max_connections=500
port=3306
server-id=1
log-bin=mysql-bin
symbolic-links=0
character-set-server=utf8
expire_logs_days=180
wait_timeout=120
interactive_timeout=120

log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

max_binlog_size = 100m
expire_logs_days = 7
binlog_cache_size = 4m
max_binlog_cache_size= 512m
# 设置自增ID初始值为1,每次自增量为2。即都是奇数1,3,5,7,...
auto_increment_offset = 1
auto_increment_increment = 2


# 开启gtid
gtid_mode = ON
enforce_gtid_consistency = 1

# 实现数据库宕机恢复后,自动同步缺少的数据
relay_log = mysql-relay-bin


从库配置

#[root@mysql002 ~]# cat /etc/my.cnf
[mysqld]

datadir=/data/mysql8/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

max_connections=500
port=3306
server-id=2
log-bin=mysql-bin
symbolic-links=0
character-set-server=utf8
expire_logs_days=180
wait_timeout=120
interactive_timeout=120

replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay-log-recovery
binlog-format=ROW
read_only=1

# 奇数2468是
auto_increment_offset = 2
auto_increment_increment = 2

# 开启gtid
gtid_mode = ON
enforce_gtid_consistency = 1

# 实现数据库宕机恢复后,自动同步缺少的数据
relay_log = mysql-relay-bin

主库创建同步用户


CREATE USER master_user IDENTIFIED BY 'HF$$$$%%%a1';
grant replication slave on *.* to 'master_user'@'192.168.0.4';
FLUSH PRIVILEGES;


主库查看主库bin log文件名称和存放位置

mysql主从搭建,mysql主主模式搭建并远程连接_主从





备库执行语句加入主库

备库日志

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.0.3',
MASTER_PORT=3306,
MASTER_USER='master_user',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1463;

mysql主从搭建,mysql主主模式搭建并远程连接_mysql_02


从库开启主从同步

start slave;

备库报错日志

mysql主从搭建,mysql主主模式搭建并远程连接_mysql_03

权限问题

update user set host='%'  where user='master_user';

关掉同步重新开始

stop slave;

start slave;

查看状态

show slave status;

mysql主从搭建,mysql主主模式搭建并远程连接_主从_04

mysql主从搭建,mysql主主模式搭建并远程连接_sql_05


初始的遗留数据的更改会受到影响,新增的数据和删除不会受到影响

mysql主从搭建,mysql主主模式搭建并远程连接_主主模式_06

mysql主从搭建,mysql主主模式搭建并远程连接_主主模式_07


到此主从数据库是搭建完毕,和测试完毕

接下来做将主从数据库更改为主主模式的数据库备份方式

相互之间互为主从

从库创建同步账户

从库查看show master status;

CREATE USER master_user2 IDENTIFIED BY 'HF$$$$%%%a1';
grant replication slave on *.* to 'master_user2'@'%';
FLUSH PRIVILEGES;

主库执行语句加入主库


mysql> CHANGE MASTER TO
MASTER_HOST='192.168.0.4',
MASTER_PORT=3306,
MASTER_USER='master_user2',
MASTER_PASSWORD='HF$$$$%%%a1',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=2068,
master_connect_retry=60,
GET_MASTER_PUBLIC_KEY=1;

start slave;

主库加入从库的日志

mysql主从搭建,mysql主主模式搭建并远程连接_sql_08

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for source to send event

                  Master_Host: 192.168.0.4

                  Master_User: master_user2

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 2068

               Relay_Log_File: mysql-relay-bin.000002

                Relay_Log_Pos: 326

        Relay_Master_Log_File: mysql-bin.000004

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

mysql主主搭建完成了,测试创建库,删除库,导入库,删除表,删除数据,导入数据等等,只要数据库服务器资源充足,可以同时进行,经过测试,对服务器的CPU要求不高,对内存要求很对,最低最低是要8G的资源,设置读写分离,读的库,要设置更高的资源

配置监控主从同步的脚本

#[root@mysql002 ~]# cat check_mysql_sync.sh
#!/bin/bash
. /usr/local/z/.profile
MYUSER=root
MYSOCKET=/var/lib/mysql/mysql.sock
MYPASSWD=$pass
MYLOGIN="mysql -S $MYSOCKET -p$MYPASSWD -u$MYUSER "
ERROR=(1158 1159 1008 1007 1062 1050)
check_status(){
STATUS=($($MYLOGIN -e "show slave status\G"|egrep "Slave_SQL_Running|Slave_IO_Running|Seconds_Behind_Master|Last_SQL_Errno"|awk '{print $NF}'))
if [ "${STATUS[0]}" = "Yes" -a "${STATUS[1]}" = "Yes" -a "${STATUS[2]} = 0" ];then
echo "Mysql Slave is Ok!"
CHECK_NUM=0
return $CHECK_NUM
else
CHECK_NUM=1
return $CHECK_NUM
fi
}

check_error(){
check_status
if [ $? -eq 1 ];then
for ((i=0;i<${#ERROR[*]};i++));do
if [ "${ERROR[i]}" == "${STATUS[3]}" ];then
$MYLOGIN -e "stop slave;"
$MYLOGIN -e "set global sql_slave_skip_counter=1"
$MYLOGIN -e "start slave;"
fi
done
fi
}

check_again(){
STATUS=($($MYLOGIN -e "show slave status\G"|egrep "Slave_SQL_Running|Slave_IO_Running|Seconds_Behind_Master|Last_SQL_Errno"|awk '{print $NF}'))
check_status >/dev/null 2&>1
if [ $? -eq 1 ];then
echo "Mysql slave is fail $(date +%F)"
echo "Mysql slave is fail $(date +%F)" >> /root/Slave_IO_Running.error
fi
}
main(){
while true;do
check_error
check_again
sleep 10
done
}
main

cat /usr/local/z/.profile

cd 
. $HOME/.bash_profile >/dev/null
cd -
pass="123456"



mysql主从报错状态码

报错1:

ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.

No connection. Trying to reconnect...

处理

mysql所在主机内存过低,清理内存,或者扩容内存尝试


报错2:

2022-05-25T01:58:50.294117Z 26 [ERROR] [MY-013117] [Repl] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: MY-013117:

mysql主从搭建,mysql主主模式搭建并远程连接_主主模式_09

处理

mysql主从搭建,mysql主主模式搭建并远程连接_主从_10

stop slave;
stop slave;
systemctl stop mysqld
[root@mysql002 mysql]# cat /data/mysql8/mysql/auto.cnf
[auto]
server-uuid=942c69f1-bfae-11ec-a28d-fa163edc5045
[root@mysql002 ~]# rm -f /data/mysql8/mysql/auto.cnf
[root@mysql002 ~]# systemctl start mysqld
[root@mysql002 ~]# cat /data/mysql8/mysql/auto.cnf
[auto]
server-uuid=109c844e-dbcf-11ec-b1db-00163e010279
mysql -uroot -p
start slave;


报错3:

mysql主从搭建,mysql主主模式搭建并远程连接_mysql_11

2022-05-25T02:18:48.672608Z 22 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction '942c69f1-bfae-11ec-a28d-fa163edc5045:7' at master log mysql-bin.000005, end_log_pos 1558; Error 'Can't drop database 'bbb'; database doesn't exist' on query. Default database: 'bbb'. Query: 'DROP DATABASE `bbb`', Error_code: MY-001008

2022-05-25T02:18:48.672758Z 21 [Warning] [MY-010584] [Repl] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756



查看从库同步状态

mysql主从搭建,mysql主主模式搭建并远程连接_mysql_12

mysql主从搭建,mysql主主模式搭建并远程连接_主主模式_13

查看主库数据,没有查看到

mysql主从搭建,mysql主主模式搭建并远程连接_主主模式_14

查看主库的bin log日志

mysqlbinlog --no-defaults -v /data/mysql8/mysql/mysql-bin.000005 |grep -A 20 "fa163edc5045" --color

查看时间点是删除了一个库,初始时没有同步,从库中没有这个库,所以删除数据时从库没有删除的项,不影响数据一致性,是初始搭建时遗留历史数据导致

mysql主从搭建,mysql主主模式搭建并远程连接_mysql_15

主从数据恢复一致后需要在slave上跳过报错的事务

在从库中执行

再次查看从库状态,恢复正常即解决

stop slave;
set @@SESSION.GTID_NEXT='942c69f1-bfae-11ec-a28d-fa163edc5045:7';
begin;
commit;
set @@SESSION.GTID_NEXT = AUTOMATIC;
start slave;

show slave status\G;

mysql主从搭建,mysql主主模式搭建并远程连接_数据库_16

mysql主从搭建,mysql主主模式搭建并远程连接_主从_17


或者将同步指针调整为下一个操作也可以解决该问题

stop slave;
set global sql_slave_skip_counter = 1;
start slave;

数据库连接出现mysql 1129 ,errors; unblock with 'mysqladmin flush-hosts'

不管是Navicat还是本地连接出现后可能不是连接数超了,重新刷新一下hosts文件,再次尝试进行连接

mysqladmin flush-hosts -h 127.0.0.1 -uroot -p