mysql数据库双主热备

数据实时双向备份

docker run --restart=always  --privileged=true --name mysql2 \
    -p 13301:3306 \
    -v /data/mysql2/conf:/etc/mysql/conf.d \
    -v /data/mysql2/logs:/var/log/mysql \
    -v /data/mysql2/data:/var/lib/mysql \
    -v /data/mysql2/mysql-files:/var/lib/mysql-files \
    -e MYSQL_ROOT_PASSWORD=123456 \
    -d mysql:8.0

docker run --restart=always  --privileged=true --name mysql3 \
    -p 13302:3306 \
    -v /data/mysql3/conf:/etc/mysql/conf.d \
    -v /data/mysql3/logs:/var/log/mysql \
    -v /data/mysql3/data:/var/lib/mysql \
    -v /data/mysql3/mysql-files:/var/lib/mysql-files \
    -e MYSQL_ROOT_PASSWORD=123456 \
    -d mysql:8.0

配置两个容器的配置文件

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
#服务id
server-id = 1 
log-bin=mysql-bin
binlog-do-db = db_test
binlog-ignore-db = mysql,information_schema
#主－主形式需要多添加的部分
log-slave-updates
sync_binlog = 1
replicate-do-db = db_test
replicate-ignore-db = mysql,information_schema

# 放暴力攻击插件
plugin-load-add=connection_control.so
# 最多连续3次错误登录
connection_control_failed_connections_threshold=3
# 休眠200秒后再次尝试建立连接
connection_control_min_connection_delay=200000
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
skip-name-resolve
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
max_connections=10000

分别查看两个机器的文件和下标
show master status;

01的机器 设置02的文件和下标
CHANGE MASTER TO MASTER_HOST='192.168.3.2',
MASTER_PORT=13302,
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=157;

02的机器 设置01的文件和下标
CHANGE MASTER TO MASTER_HOST='192.168.3.1',
MASTER_PORT=13301,
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=832;

如果配置错误，重置
reset slave;

启动
start slave;

查看状态
show slave status;

测试：在其中一个库进行创建表和数据

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `users` (`name`, `email`) VALUES ('Tom', 'tom@example.com');
INSERT INTO `users` (`name`, `email`) VALUES ('Jack', 'jack@example.com');
INSERT INTO `users` (`name`, `email`) VALUES ('Lucy', 'lucy@example.com');

错误：Got fatal error 1236 from source when reading data from binary log: 'Could not find first log file name in binary log index file'
set global max_allowed_packet =110241024*1024; stop slave; start slave;

错误：The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always
docker容器同一镜像id一致了
docker exec -it mysql3 mv /var/lib/mysql/auto.cnf  /var/lib/mysql/auto.cnf.bk

https://www.jianshu.com/p/431ff991099b

nginx负载

stream{
    upstream mysql{
        server 192.168.1.101:3306 max_fails=1 fail_timeout=30s;
        server 192.168.1.102:3306 max_fails=1 fail_timeout=30s;
    }
    server{
        listen 3306;
        server_name 192.168.1.100;
        proxy_pass mysql;
    }
}

https://blog.51cto.com/u_16213670/7298723