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:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> show grants for rep1@192.168.100.66;
±----------------------------------------------------------+
| Grants for rep1@192.168.100.66 |
±----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON . TO rep1@192.168.100.66 |
±----------------------------------------------------------+
1 row in set (0.00 sec)
创建用户并赋权
1.先进入mysql数据库
use mysql;
2.在user表中创建账号
create user ‘root’@’%’ identified by ‘P@ssw0rd’;
3.给创建好的账号赋予远程权限
grant all privileges on . to ‘root’@‘10.150.32.100’ with grant option;
4.允许所有 ip 远程访问(危险！)
grant all privileges on . to ‘root’@’%’ with grant option;
5.刷新数据库
FLUSH PRIVILEGES;
6.查看数据库中的用户权限表
select User,authentication_string,Host from user;
######GTID复制搭建#######
GTID的搭建
参数配置：
gtid-mode=on
enforce-gtid-consistency=1
在MYSQL5.6中，除了上述两个参数，还需要设置以下参数：
log-bin=mysql-bin
log-slave-updates=1
CHANGE MASTER TO 命令
change master to master_host=‘192.168.100.77’, master_port=3306,master_user=‘rep1’, master_password=‘Repl@123’,master_auto_position=1;
mysql> show binlog events in ‘mysql-bin.000006’;
######半同步复制##########
半同步复制的安装
(1)、加载插件
主库：
install plugin rpl_semi_sync_master soname ‘semisync_master.so’;
从库：
install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
判断插件是否加载成功？
1.show plugins命令
rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL
2.查询information_schema.plugins表
select plugin_name,plugin_status from information_schema.plugins where plugin_name like ‘%semi%’;
| plugin_name | plugin_status |
±---------------------±--------------+
| rpl_semi_sync_master | ACTIVE
(2)、启动半同步复制
主库：
set global rpl_semi_sync_master_enabled=1;
从库：
set global rpl_semi_sync_slave_enabled=1;
(3)、重启从库上的I/O线程。默认为异步复制
stop slave io_thread;
start slave io_thread;
(4)、查看半同步复制是否运行。
分别在主库和从库上执行以下命令
主库：show status like ‘Rpl_semi_sync_master_status’;
Variable_name | Value
-----------------------------±-----
Rpl_semi_sync_master_status | ON
-----------------------------±-----
从库：show status like ‘Rpl_semi_sync_slave_status’;
Variable_name | Value
----------------------------±-----
Rpl_semi_sync_slave_status | ON
----------------------------±-----
只有这两个变量都为ON，才意味着半同步复制正在运行。
############并行复制###############
#在从库上开启并行复制
vim /etc/hosts
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
slave_preserve_commit_order=ON
show variables like ‘%parallel%’
Variable_name | Value |
------------------------------±--------------+
innodb_parallel_read_threads | 4 |
slave_parallel_type | LOGICAL_CLOCK |
slave_parallel_workers | 16 |
#给数据库加全局读锁：测试表的insert
flush tables with read lock;
mysql> insert into t1 values (6);
ERROR 1223 (HY000): Can’t execute the query because you have a conflicting read lock
##########延迟复制#################
#延迟复制模拟实验
开启延迟复制方法一：
在搭建主从库时设置
stop slave;
change master to master_host=‘192.168.100.77’, master_port=3306,master_user=‘rep1’, master_password=‘Repl@123’,master_auto_position=1, master_delay=28800;
start slave;
show slave status \G;
SQL_Delay: 28800
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
实验：
主库：
mysql> create database slowtech;
Query OK, 1 row affected (0.00 sec)
mysql> use slowtech;
Database changed
mysql> create table t1 (id int primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
id
1
2
从库：
id
1
2
从库开启延迟复制：
stop slave;
change master to master_delay=28800;
start slave;
主库：
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1;
ERROR 1146 (42S02): Table ‘slowtech.t1’ doesn’t exist
mysql> show master status;
1 row in set (0.00 sec)
mysql> pager grep -iB 5 drop;
PAGER set to ‘grep -iB 5 drop’
mysql> show binlog events in ‘mysql-bin.000007’;
备库：
stop slave;
change master to master_delay=0;
start slave until master_log_file=‘mysql-bin.000007’,master_log_pos=2823;
start slave sql_thread;
show slave status\G;
select * from slowtech.t1;
####################解决主从复制报错问题###########################
ERROR 2061 (HY000): Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
主库：
- Remove the repl
drop user ‘rep1’@‘192.168.100.66’;
flush privileges;
- Confirm the repl has been dropped
select user from mysql.user;
- Manually create the user with the 192.168.100.66 and IP address
#create user ‘rep1’@‘IP’ identified with mysql_native_password by ‘Repl@123’;
create user ‘rep1’@‘192.168.100.66’ identified with mysql_native_password by ‘Repl@123’;
#GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT, LOCK TABLES, RELOAD ON . TO ‘repl’@‘IP’;
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT, LOCK TABLES, RELOAD ON . TO ‘rep1’@‘192.168.100.66’;
flush privileges;
- Confirm you can log in to MySQL witrh the repl
从库：
mysql -u rep1 -pRepl@123 -h 192.168.100.77
[root@localhost ~]# perror 1396
MySQL error code MY-001396 (ER_CANNOT_USER): Operation %s failed for %.256s
##################重新搭建主从##############################################
reset master、reset slave与reset slave all区别
https://cloud.tencent.com/developer/article/1533731
主库：
reset master
从库：
reset slave all;
在主库上创建复制用户
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
