數(shù)據(jù)實(shí)時(shí)雙向備份

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

配置兩個(gè)容器的配置文件

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
#服務(wù)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
# 最多連續(xù)3次錯(cuò)誤登錄
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

分別查看兩個(gè)機(jī)器的文件和下標(biāo)
show master status;

01的機(jī)器 設(shè)置02的文件和下標(biāo)
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的機(jī)器 設(shè)置01的文件和下標(biāo)
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;

如果配置錯(cuò)誤，重置
reset slave;

啟動(dòng)
start slave;

查看狀態(tài)
show slave status;

測(cè)試：在其中一個(gè)庫(kù)進(jìn)行創(chuàng)建表和數(shù)據(jù)

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');

錯(cuò)誤：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;

錯(cuò)誤：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

http://www.itdecent.cn/p/431ff991099b

nginx負(fù)載

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