简单的主从配置

公司服务器不稳定，挂了之后，用bin_log恢复有点慢，做了一个简单的主从库。

一. 主库-----备份数据库

1. 配置文件
   只增加了最后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

2. 锁表，防止备份时，数据有差异

# 二选一
flush tables with read lock;
# lock tables;

3. cmd 备份数据

mysqldump.exe -h 192.168.1.11 -u root -p  -P 30006 --all-databases > all.sql

4. 创建从库同步用户
   注意插件类型必须是 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;

5. 查看主库的信息

show master status;

注意图片中的File 和 Position ，这2个值后面配置是需要用到。

二. 从库----同步配置

1. 从库配置文件

[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/

2. 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

3. 还原主库数据

mysql.exe -h 123.123.123.123 -u root -p  -P 3306  --default-character-set=utf8 < all.sql

4. 配置从库

# 停止从库（第一次可以不执行，执行了也不会有问题）
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，代表成功

5. 其他命令

# 查看bin_log文件的内容
show binlog events IN 'mysql-bin.000002';

# 查看错误
select * from performance_schema.replication_applier_status_by_worker;

二. 主库----恢复

1. 解锁表

unlock tables