Mysql8 集群部署 - 双主多从-部署指南-Liunx(abc.htmltoo.com)

Mysql8 集群部署 - 双主多从 mysql

ihunter 2020-3-3 68

[百度收录]

[搜狗收录]

[360收录]

https://hub.docker.com/_/mysql/

https://hub.docker.com/r/mysql/mysql-cluster/

https://hub.docker.com/r/databack/mysql-backup

使用mysqlbinlog恢复MySQL数据库

应用: mycat -> 添加服务: mysql1, mysql2, mysql3, mysql4, .....

镜像: mysql:latest

命令: --default-authentication-plugin=mysql_native_password

环境变量:

MYSQL_ROOT_PASSWORD=m

MYSQL_ALLOW_EMPTY_PASSWORD=no

添加卷:

/etc/localtime:/etc/localtime:ro

/data/file:/data/file/

/data/docker/mysql/mysql8/1.cnf:/etc/mysql/my.cnf:ro # 2.cnf ....

/data/file/logs/mysql81:/var/log/mysql # mysql82 ....

==========

chmod -R 777 /data/file/logs # 重启宿主机后, 日志权限异常修复

==========

# mycat-mysql1-1, mycat-mysql1-2: 为主,

# mycat-mysql1-3, mycat-mysql1-4: 为备

==========================

mysql -A -uroot -pwdqdmm@r;

grant all on *.* to 'ihunter'@'%' identified by 'wdqdmm@m';

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'wdqdmm@m';

create user ihunter@'%' identified by 'wdqdmm@m';

create user slave@'%' identified by 'wdqdmm@m';

grant all privileges on *.* to ihunter@'%' with grant option;

GRANT REPLICATION SLAVE on *.* to slave@'%' with grant option;

#revoke all privileges on *.* from slave@'%'; # drop user slave@'%';

flush privileges;

# 查看是否启用了bin

show variables like '%log_bin%';

# 添加用于同步的数据库账号

use mysql;

create user 'slave'@'%' identified by 'Test@1234';

grant replication slave on *.* to 'slave'@'%';

flush privileges;

mysql1:

# 清空两台主机的master和slave日志

reset master;

reset slave;

# mysql1数据库加读锁，防止配置同步时有数据写入

SHOW MASTER STATUS; # mysql-bin.000001 | 155

# 配置过程中为了防止突然的主机数据写入导致配置主从失败可以将机器锁定只允许读

注意千万不要将从机配成锁定否则无法同步数据了

锁住mysql2的表，防止写操作,

flush tables with read lock;

接下来，导出mysql2 db数据库中的数据，恢复到server1, server3, server4数据库中

# 此时请保证执行这两条命令的mysql控制台不要退出，

然后进mysql控制台，分别记下File和Position参数

在mysql1上：

show master status;

在mysql2服务器上：

show master status;

# 添加从机到主机:

# mycat-mysql1-1, mycat-mysql1-2: 为主,

# mycat-mysql1-3, mycat-mysql1-4: 为备

mycat-mysql1-1:

stop slave;

change master to master_host='mycat-mysql2-1',master_user='slave',master_password='wdqdmm@m',master_log_file='mysql-bin.000001',master_log_pos=155;

start slave;

mycat-mysql3-1:

stop slave;

change master to master_host='mycat-mysql2-1',master_user='slave',master_password='wdqdmm@m',master_log_file='mysql-bin.000001',master_log_pos=155;

start slave;

mycat-mysql2-1:

stop slave;

change master to master_host='mycat-mysql1-1',master_user='slave',master_password='wdqdmm@m',master_log_file='mysql-bin.000001',master_log_pos=155;

start slave;

mycat-mysql4-1:

stop slave;

change master to master_host='mycat-mysql1-1',master_user='slave',master_password='wdqdmm@m',master_log_file='mysql-bin.000001',master_log_pos=155;

start slave;

# 这样子，mysql1和mysql2就互为主从，即为主主模式！

unlock tables; # mysql1上解锁表

# 假定mysql1宕机，如何重新恢复并接上

a.在mysql2上停止slave, 并保证没有新的数据进来，可以采取锁库锁表的方式

stop slave;

flush tables with read lock;

b.mysql1进行修复恢复，恢复完毕后执行命令

stop slave;

c.将mysql2上的数据备份恢复到mysql1, 并保证数据一致性。

d.然后参考上面的步骤，用change命令互相设为主从即可

e.对master11解锁

# 出现Slave_SQL_Running：no和slave_io_running：no问题的解决方法

# 如果从机同步是和主机出现一个同步错误已经解决的情况下可以执行跳过一个错误

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

start slave;

show slave status\G

#需要重新搭建主从此时可以重置master 重新设置slave

reset master # 清空重置master

my.cnf # 双主多从配置: 1.cnf

[mysqld]

#skip-log-bin

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

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

datadir = /var/lib/mysql

secure-file-priv= NULL

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

server-id=1 # 主机id，不能重复

# slaves

relay_log = /var/log/mysql/relay-bin #作为从服务器时的中继日志

relay_log_index = /var/log/mysql/relay-bin.index #开启中继日志索引

relay_log_info_file = /var/log/mysql/relay-bin.info

sync_binlog = 1 #事务特性,最好设为1

log_slave_updates = on

auto-increment-increment = 2

auto-increment-offset = 1 # id，不能重复

# 不同步mysql，information_schema， performance_schema这几个库

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

general_log = 1

general_log_file = /var/log/mysql/query.log

slow_query_log=1

slow_query_log_file = /var/log/mysql/slow.log

log-error = /var/log/mysql/error.log

log-bin=mysql-bin

log_bin = /var/log/mysql/mysql-bin

log_bin_index = /var/log/mysql/mysql-bin.index

binlog_format=mixed

expire_logs_days = 7

max_binlog_size = 200M

bind-address = 0.0.0.0

default_storage_engine = InnoDB

innodb_flush_log_at_trx_commit=1

#skip-grant-tables

max_connections = 10000

max_allowed_packet = 500M

wait_timeout= 600

interactive_timeout = 600

character-set-client-handshake=FALSE

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

init_connect='SET NAMES utf8mb4'

# Custom config should go here

!includedir /etc/mysql/conf.d/

my.cnf # 单机无日志

[mysqld]
#skip-log-bin
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#数据强一致性
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_slave_enabled=1
#rpl_semi_sync_master_timeout=6000
#主机id，不能重复
server-id=1                      
#启用gtid类型，否则就是普通的复制架构
#gtid_mode = on
#强制GTID的一致性
#enforce_gtid_consistency = true
#启用二进制日志, row复制模式,具有最高性能.
binlog_format=row
log_bin                = /var/log/mysql/mysql-bin
log_bin_index          = /var/log/mysql/mysql-bin.index
log-error = /var/log/mysql/error.log
binlog_expire_logs_seconds        = 7
max_binlog_size         = 200M
# slaves
#默认读写状态为0,只读状态为1, 请去掉注释.
#read_only=1           
#作为从服务器时的中继日志
relay_log              = /var/log/mysql/relay-bin 
#开启中继日志索引           
relay_log_index        = /var/log/mysql/relay-bin.index       
#中继日志信息记录库
#relay-log-info-repository=TABLE 
#事务特性,最好设为1
sync_binlog            = 1                                    
#slave 更新是否记录到日志
log_slave_updates  =  on
auto-increment-increment = 2
auto-increment-offset = 1          #  id，不能重复
# 不同步mysql, information_schema, performance_schema这几个库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
general_log = 1
general_log_file = /var/log/mysql/query.log
#开启慢查询日志
slow_query_log=1
##超出时间设定值的SQL即被记录到慢查询日志
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log
bind-address = 0.0.0.0
default_storage_engine  = InnoDB
#实时写，实时刷
innodb_flush_log_at_trx_commit=1
#skip-grant-tables
max_connections = 10000
max_allowed_packet = 500M
wait_timeout= 600
interactive_timeout = 600
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
# Custom config should go here
!includedir /etc/mysql/conf.d/

# Seconds_Behind_Master: 和主库比同步延迟的秒数

show slave status\G;

从库状态 Seconds_Behind_Master 变小到很小就可以

# Slave_SQL_Running: No mysql同步故障

发现一台MySQL Slave未和主机同步，查看Slave状态：

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: No

slave stop;

-跳过错误步数，后面步数可变

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

slave start;

# MySQL的错误：No query specified

show slave status\G;的时候，多加了一个“;”分号。

# max_binlog_cache_size设置不当

show global status like '%binlog_cache%';

set global max_binlog_cache_size=40*1024*1024*1024;

https://blog.csdn.net/weixin_38073885/article/details/95354417

https://blog.csdn.net/wx5040257/article/details/79782808

https://www.cnblogs.com/l-hh/p/9922548.html

追梦赤子心：domsn.com

http://www.htmltoo.com/

签名：这个人很懒，什么也没有留下！

猜你喜欢：

最新回复 (0)

只看楼主

频道：

论坛：

我的：

Mysql8 集群部署 - 双主多从 mysql

猜你喜欢：

ihunter

作者最近主题：