环境准备
操作系统:两台 Oracle Linux 7.9
数据库版本:5.7.36
主库IP:192.168.16.51 3306
从库IP:192.168.16.52 3306
前言
目前单机数据库数据库量为10g。需要搭建主从复制架构,基于主库搭建一个从库,从库用于数据容灾和主库备机,采用mysqldump+传统的无损同步复制
一、配置主从用户及my.cnf文件
1、主库和从库创建复制用户并授权
mysql -uroot -proot
create user 'repuser'@'%' identified by 'repuser';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
2、安装无损同步插件 --采用写到配置中的方法
主库从库相同操作
查看插件路径:show variables like 'plugin_dir';
######semi sync replication settings########
plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count = 1
3、修改配置文件 my.cnf
修改主my.cnf
vi /mysql/data/3306/my.cnf
log_bin=/mysql/log/3306/binlog/00db-binlog
log_bin_index=/mysql/log/3306/binlog/00db-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
修改从my.cnf
vi /mysql/data/3306/my.cnf
log_bin=/mysql/log/3306/binlog/00db-binlog
log_bin_index=/mysql/log/3306/binlog/00db-binlog.index
binlog_format=row
binlog_rows_query_log_events=on
relay_log = /mysql/log/3306/relaylog/00db-relay.log
log-slave-updates = 1
read_only=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=table
relay_log_info_repository=table
relay_log_recovery=1
slave_skip_errors = ddl_exist_errors
slave_preserve_commit_order=1
4、分别重启主从MySQL服务
systemctl restart mysqld
二、主从操作步骤
1.主库备份并恢复到从库
cd /mysql/backup
mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --flush-privileges --events --routines --all-databases >db_fullbackup.sql
2、从库恢复
把主库备份文件传到从库服务器
scp 192.168.16.51:/mysql/backup/db_fullbackup.sql /mysql/backup/
恢复备份:
mysql -uroot -p < /mysql/backup/db_fullbackup.sql
从库查看,确认数据已恢复。
3、在从库上使slave与master 建立连接,从而同步:
more db_fullbackup.sql
查找MASTER_LOG_FILE值和 MASTER_LOG_POS值
stop slave;
change master to
master_host='192.168.16.51',
master_user='repuser',
master_password='repuser',
master_log_file='00db-binlog.000002',
master_log_pos=154;
start slave;
show slave status \G
测试:
主库创建数据库和表的数据
create database 00db1;
use 00db1;
create table 00bak11 (id int primary key,name varchar(40));
insert into 00bak11 values
(1,'00111'),(2,'00112'),(3,'00113'),(4,'00114'),(5,'001
15');
commit;
select * from 00bak11;
备库检查:
select * from 00db1.00bak11;