MySQL8.0复制
MySQL8.0复制
####异步复制#####
1，编辑配置文件
主：
vim /etc/my.cnf
[client]
socket=/data/mysql/3306/data/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
socket=/data/mysql/3306/data/mysql.sock
log-error=/data/mysql/3306/data/mysqld.err
port=3306
user=mysql
log_timestamps=system
default_authentication_plugin = mysql_native_password
#主从复制新增参数
log-bin=mysql-bin
server-id=773306
从：
vim /etc/my.cnf
[client]
socket=/data/mysql/3306/data/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
socket=/data/mysql/3306/data/mysql.sock
log-error=/data/mysql/3306/data/mysqld.err
port=3306
user=mysql
log_timestamps=system
default_authentication_plugin = mysql_native_password
#主从复制新增参数
server-id=663306
2.在主库上创建复制用户
#create user ‘repl’@‘192.168.100.66’ identified by ‘Repl@123’;
#grant replication slave on . to ‘repl’@‘192.168.100.66’;
CREATE USER rep1@192.168.100.66 IDENTIFIED WITH caching_sha2_password BY ‘Repl@123’;
GRANT Replication Slave ON . TO rep1@192.168.100.66;
3.在从库上进行登录测试：
先检查操作系统防火墙状态：systemctl status firewalld.service
测试登录
mysql -h192.168.100.77 -urepl -p’Repl@123’ -P3306;
4.获取主库的备份
mysqldump -uroot -p’P@ssw0rd’ --single-transaction --master-data=2 -E -R --triggers -A > full_bakcup.sql
scp full_bakcup.sql root@192.168.100.66:/root
5.基于主库的备份恢复从库
mysql -uroot -p’P@ssw0rd’ < full_bakcup.sql
6.建立主从复制
grep -m 1 “CHANGE MASTER TO” full_bakcup.sql
– CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001’, MASTER_LOG_POS=1622;
在从库中执行：
change master to master_host=‘192.168.100.77’, master_port=3306,master_user=‘rep1’, master_password=‘Repl@123’,master_log_file=‘mysql-bin.000001’,master_log_pos=156, get_master_public_key=1;
mysql8.0需要增加此参数
get_master_public_key
报错提示：Last_IO_Error: error connecting to master ‘repl @192.168.100.77:3306’ - retry-time: 60 retries: 1 message: Access denied for user 'repl '@‘192.168.100.66’ (using password: YES)
如复制用户登录失败，修改复制用户密码语句，否则不用执行。
ALTER USER ‘repl’@‘192.168.100.66’ IDENTIFIED WITH mysql_native_password BY “Repl@123”;
[root@localhost ~]# perror MY-001045
MySQL error code MY-001045 (ER_ACCESS_DENIED_ERROR): Access denied for user ‘%-.48s’@’%-.64s’ (using password: %s)
解决方法：
从库上执行
show slave status;
STOP SLAVE;
RESET SLAVE;
从库上测试rep1用户是否能正常登录：mysql -h192.168.100.77 -urepl -p’Repl@123’ -P3306;
change master to master_host=‘192.168.100.77’, master_port=3306,master_user=‘rep1’, master_password=‘Repl@123’,master_log_file=‘mysql-bin.000003’,master_log_pos=1242, get_master_public_key=1;
主库上查询位点信息以及复制用户权限
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 1242
Binlog_Do_DB:
