公司服务器不稳定,挂了之后,用bin_log恢复有点慢,做了一个简单的主从库。
一. 主库-----备份数据库
- 配置文件
只增加了最后2行,配置主库
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
max_connections=2000
secure_file_priv=/var/lib/mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
log_bin_trust_function_creators=1
max_allowed_packet=1024M
server-id = 1
log-bin=mysql-bin
- 锁表,防止备份时,数据有差异
# 二选一
flush tables with read lock;
# lock tables;
- cmd 备份数据
mysqldump.exe -h 192.168.1.11 -u root -p -P 30006 --all-databases > all.sql
- 创建从库同步用户
注意插件类型必须是 mysql_native_password
create user 'slave'@'%' identified WITH 'mysql_native_password' by '@@xx@@xx@@xx123Aa';
grant replication slave on *.* to 'slave'@'%' with grant option;
flush privileges;
- 查看主库的信息
show master status;
注意图片中的File 和 Position ,这2个值后面配置是需要用到。
二. 从库----同步配置
- 从库配置文件
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
## 开启中继日志并指定 server-id 和 只读
# log-bin=mysql-bin
# binlog_format = mixed
relay-log = mysql-relay-bin
server-id = 2
read-only = on
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
- docker 从库命令
docker run --restart=always -itd --name mysql-dev-slave -p 3306:3306 -v /data/mysql/data/:/var/lib/mysql -v /data/mysql/logs/:/var/log/mysql -v /data/mysql/conf/my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=Kgb@@7dnmt@@8 mysql:8.0.30 --lower_case_table_names=1
- 还原主库数据
mysql.exe -h 123.123.123.123 -u root -p -P 3306 --default-character-set=utf8 < all.sql
- 配置从库
# 停止从库(第一次可以不执行,执行了也不会有问题)
stop slave;
# 重置从库配置(第一次可以不执行,执行了也不会有问题)
reset slave;
# 配置主库信息
# master_log_file 和 master_log_pos 2个属性需要与第一部分主库配置中第5小点一致
change master to master_host='123.123.123.123',
master_port=123,
master_user='slave',
master_password='@@xx@@xx@@xx123Aa',
master_log_file='mysql-bin.000001',
master_log_pos=3582,
master_retry_count = 60,
master_heartbeat_period = 10000;
# 开启从库
start slave;
# 查看从库状态
show slave status ;
图片中2个 Yes,代表成功
- 其他命令
# 查看bin_log文件的内容
show binlog events IN 'mysql-bin.000002';
# 查看错误
select * from performance_schema.replication_applier_status_by_worker;
二. 主库----恢复
- 解锁表
unlock tables