前提环境:
没必要和我的版本一样,操作系统适用于Linux 7.x,MySQL数据库版本适用于MySQL5.7.x
两台操作系统之间可以互信。
主从配置:
1、创建master和slave复制用户
a、master创建复制用户repuser
mysql -uroot -p
create user 'repuser'@'%' identified by 'repuser';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
b、slave创建复制用户repuser
mysql -uroot -p
create user 'repuser'@'%' identified by 'repuser';
grant replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
2、修改主从配置文件(my.cnf)
a、 修改master配置文件,把下面参数加进去,因为每个人安装MySQL的配置文件中参数都不同,没有就加,有的话,就无需加
####我安装MySQL5.7.36文章及配置参数目录可见:Oracle Linux 7.9安装MySQL5.7.36_零Ⅰ的博客-CSDN博客
vi 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
bind-address=0.0.0.0
server_id=513306
expire_logs_days = 7
innodb_support_xa =1
binlog_cache_size = 1M
max_binlog_size = 2048M
log_bin_trust_function_creators = 1
innodb_flush_log_at_trx_commit =1
sync_binlog = 1
transaction-isolation = read-committed
b、修改slave配置文件,把下面参数加进去,因为每个人安装MySQL的配置文件中参数都不同,没有就加,有的话,就无需加,和mater有区别的是 server_id=523306,并添加以下参数
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
3、分别重启两台MySQL
systemctl restart mysqld
3.1查看master库binlog信息
show master status;
3.2在从库上使slave与master建立连接,从而同步
先把slave停止再执行
stop slave;
change master to
master_host='192.168.16.51',
master_user='repuser',
master_password='repuser',
master_log_file='00db-binlog.000001',
master_log_pos=154;
启动slave库
start slave;
show slave status \G
测试:
1、主从数据测试:
主库执行:create database 00db1;
use 00db1;
create table wqbak11 (id int primary key,name varchar(40));
insert into wqbak11 values
(1,'wq111'),(2,'wq112'),(3,'wq113'),(4,'wq114'),(5,'wq1
15');
commit;
select * from wqbak11;
从库执行:
select * from 00db1.wqbak11;
######题外话:主库Position值和从库Exec_Master_Log_Pos值、Read_Master_Log_Pos值一样,表示数据一致。
##有那些地方不明白,大家可以相互学习交流。