mysql配置主从同步备份及切换

1.环境准备

环境
宿主机 win10
mysql:8.0.19
mysql1(master): 10.18.40.123:3306
mysql2(slave): 10.18.40.23:3306

2.mysql 配置文件配置

mysql1(master): 10.18.40.123 my.ini 配置文件设置

[mysqld]

server_id = 1
#唯一标识，主库从库不能重复
log_bin = mysql-bin
#开启日志
binlog_format=MIXED
#日志记录的格式
max_binlog_size = 512M
#单个日志文件最大
expire_logs_days = 7
#日志有效期（天）
binlog_do_db =     #需要同步日志记录那些数据库
binlog_ignore_db = mysql,performance_schema,information_schema #日志记录忽略那些数据库的

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

# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\ProgramData\\mysql-8.0.19-winx64   # 切记此处一定要用双斜杠\\，单斜杠我这里会出错，不过看别人的教程，有的是单斜杠。自己尝试吧
# 设置mysql数据库的数据的存放目录
datadir=D:\\ProgramData\\mysql-8.0.19-winx64\\Data   # 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

mysql2(slave): 10.18.40.23 my.ini 配置

[mysqld] 
server-id = 2 
log-bin=mysql-bin 
relay-log = mysql-relay-bin 
replicate-wild-ignore-table=mysql.% 
replicate-wild-ignore-table=test.% 
replicate-wild-ignore-table=information_schema.% 
default-time-zone='+08:00' 
# 设置3306端口 
port=3306 
# 设置mysql的安装目录 
basedir=D:\\mysql-8.0.19-winx64   # 切记此处一定要用双斜杠\\，单斜杠我这里会出错，不过看别人的教程，有的是单斜杠。自己尝试吧 
# 设置mysql数据库的数据的存放目录
 datadir=D:\\mysql-8.0.19-winx64\\Data   # 此处同上 
 # 允许最大连接数 
 max_connections=200 
 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
 max_connect_errors=10 
 # 服务端使用的字符集默认为UTF8 
 character-set-server=utf8 
 # 创建新表时将使用的默认存储引擎 
 default-storage-engine=INNODB 
 # 默认使用“mysql_native_password”插件认证
 default_authentication_plugin=mysql_native_password  
 [mysql] 
 # 设置mysql客户端默认字符集 
 default-character-set=utf8  
 [client] 
 # 设置mysql客户端连接服务端时默认使用的端口 
 port=3306 
 default-character-set=utf8

3.master数据库，创建复制用户并授权

1.进入master的数据库，为master创建复制用户

CREATE USER repl_user IDENTIFIED BY 'repl_passwd';

2.赋予该用户复制的权利

grant replication slave on *.* to 'repl_user';

FLUSH PRIVILEGES;

3.查看master的状态

show master status;

4.配置从库

set GLOBAL slave_net_timeout=20000;重连间隔需要设置成心跳周期的2倍，否则会造成日志文件不断产生。


CHANGE MASTER TO 
MASTER_HOST = '10.18.40.123',  
MASTER_USER = 'repl_user', 
MASTER_PASSWORD = 'repl_passwd',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000011',
MASTER_LOG_POS=400,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000; 

MASTER_LOG_FILE='mysql-bin.000005',#与主库File 保持一致
MASTER_LOG_POS=120 , #与主库Position 保持一致

5.启动从库slave进程

Start slave;

6. 查看是否配置成功

show slave status;

7.主从切换

1> 正常切换

1）从服务器检查SHOW PROCESSLIST语句的输出，直到你看到Has read all relaylogwaiting for the slave I/O thread to update it
2）确保从服务器已经处理了日志中的所有语句。 mysql> STOP SLAVE IO_THREAD
当从服务器都执行完这些，它们可以被重新配置为一个新的设置。
3）在被提升为主服务器的从服务器上，发出 STOP SLAVE和RESET MASTER和RESET SLAVE操作。
4）然后重启mysql服务。
5) 在原主服务器上RESET MASTER。然后再从③步开始操作。
6）修改应用的连接地址到新的主库

2> 主机直接宕机

1> 在备机上执行STOP SLAVE 和RESET MASTER
2> 查看show slave status;
3> 然后修改应用的连接地址。

8.备份数据文件并压缩存储

将gzip.exe放入C:\Windows\System32文件夹，修改bat脚本信息之后即可运行，按月份建文件夹，按日期压缩文件，路径和数据库及用户需自己指定

rem *******************************Code Start*****************************
@echo off

D:
CD D:\ProgramData\mysql-8.0.19-winx64\bin

set "Y=%date:~,4%"
set "m=%date:~5,2%"
set "d=%date:~8,2%"

if not exist "D:\MySQL Backup\BK%Y%%m%\" (mkdir "D:\MySQL Backup\BK%Y%%m%\")
mysqldump --opt -u root --password=root streamsets | gzip -v  >"D:\MySQL Backup\BK%Y%%m%\streamsets_%Y%%m%%d%.sql.gz"

@echo on
rem *******************************Code End*****************************