mysql主备删除备数据,mysql 互为主备宕机数据丢失_Devops部署-mysql主备多从搭建...

斗鱼游戏直播

于 2021-03-17 22:23:02 发布

阅读量152

点赞数

文章标签： mysql主备删除备数据

双主多从架构原理介绍

IP端口账号密码服务名

10.1.1.23306root/syncabc123!数据库A

10.1.1.33306root/syncabc123!数据库B

10.1.1.43306root/syncabc123!数据库C

1.两台mysql都可读写，互为主备，默认只使用一台(masterA)负责数据的写入，另一台(masterB)备用；

2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；

3.两台主库之间做高可用,可以采用MyCAT中间件暴露一个统一的服务给client,同时当2主任意一台宕机时，还能提供服务。宕掉的机器重启恢复之后，数据可以同步得到；

4.所有提供服务的从服务器与masterB进行主从同步(双主多从);

#创建目录 mkdir -p /home/docker-compose/mysql cd /home/docker-compose/mysql/ && mkdir data conf vi docker-compose.yml

version: '3.1'

services:

mysql:

restart: always

image: mysql:5.7

container_name: mysql

ports:

- 3306:3306

environment:

TZ: Asia/Shanghai

MYSQL_ROOT_PASSWORD: abc123! ##密码

command:

--character-set-server=utf8mb4

--collation-server=utf8mb4_general_ci

--explicit_defaults_for_timestamp=true

--lower_case_table_names=1

--max_allowed_packet=128M

--sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"

volumes:

- ./data:/var/lib/mysql

- ./conf:/etc/mysql/mysql.conf.d

进入目录

cd /home/docker-compose/mysql/conf

编写MySQL配置文件

vi mysqld.cnf

主节点1配置

[mysqld]

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

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

default-time-zone='+8:00'

symbolic-links=0

server-id = 40

log-bin = mysql-bin

enforce_gtid_consistency = ON

gtid_mode = ON

auto_increment_increment=1

auto_increment_offset=1

log-slave-updates

sync_binlog=1

max_connections=1000

innodb_file_per_table=1

innodb_file_format=Barracuda

innodb_page_size=32K

innodb_log_file_size=148M

主节点2配置

[mysqld]

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

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

default-time-zone='+8:00'

symbolic-links=0

server-id = 66

log-bin = mysql-bin

enforce_gtid_consistency = ON

gtid_mode = ON

auto_increment_increment=2

auto_increment_offset=2

log-slave-updates

sync_binlog=1

max_connections=1000

innodb_file_per_table=1

innodb_file_format=Barracuda

innodb_page_size=32K

innodb_log_file_size=148

启动mysql

docker-compose up -d

docker-compose down //直接删除容器

docker-compose stop //不删除容器

docker-compose start //直接启动

进入容器：

docker exec -it mysql /bin/bash

mysql -uroot -pabc123!

设置2个主节点的主从关系

查看10.1.1.2的日志信息:

File: mysql-bin.000005

Position: 1495

mysql> show master status G;

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

File: mysql-bin.000005

Position: 1495

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: c83eb7e9-2556-11eb-b4ad-0242ac120002:20-21,

d52d0074-2556-11eb-93b5-0242ac120002:1-20

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

查看10.1.1.3的日志信息:

File: mysql-bin.000004

Position: 1796

mysql> show master status G;

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

File: mysql-bin.000004

Position: 1796

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: c83eb7e9-2556-11eb-b4ad-0242ac120002:1-21,

d52d0074-2556-11eb-93b5-0242ac120002:20

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

在两个节点执行

create user 'sync'@'%' identified by 'password';

grant replication slave on *.* to sync;

##Mysql复制错误error_code: 1045 Access denied for user

grant replication slave on *.* to 'sync'@'%' identified by 'password';

flush privileges; //刷新MySQL的系统权限相关表，否则会无法用账号链接

修改master_host与master_log_file与master_log_pos三个值

#节点设置需要同步的节点(主库节点)

#在10.1.1.2设置

FLUSH TABLES WITH READ LOCK;

change master to master_host='10.1.1.3',master_port=3306,master_user='sync',master_password='password',master_log_file='mysql-bin.000004',master_log_pos=1796;

UNLOCK TABLES;

#在10.1.1.3设置

FLUSH TABLES WITH READ LOCK;

change master to master_host='10.1.1.2',master_port=3306,master_user='sync',master_password='password',master_log_file='mysql-bin.000005',master_log_pos=1495;

UNLOCK TABLES;

开启主从复制 :

start slave;

检查

mysql> show slave status G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.1.1.3

Master_User: sync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004