目录
2.主库修改my.cnf ?生效配置default-authentication-plugin=mysql_native_password
?Last_IO_Error: Error connecting to source 'repl_user@192.168.213.15:3306'. This was attempt 3/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
[root@ms-server1 ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[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 the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_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
bind-address=0.0.0.0
default-storage-engine=Innodb
innodb_buffer_pool_size = 2048M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=1
log-bin=mysql-bin
[root@ms-server1 ~]# systemctl restart mysqld
删除之前创建的复制用户
mysql> drop user ?'repl_user'@'%' ;
Query OK, 0 rows affected (0.01 sec)
重新创建用户
use mysql
create user 'repl_user'@'%' identified by 'Antute_123';
grant replication slave on *.* to 'repl_user'@'%';
flush privileges;
主库查看新状态
show master status\G;
mysql> show master status\G;
*************************** 1. row ***************************
? ? ? ? ? ? ?File: mysql-bin.000003
? ? ? ? ?Position: 1023
? ? ?Binlog_Do_DB:
?Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
从库修改my.cnf配置 default-authentication-plugin=mysql_native_password
[root@ms-server2 ~]# vi /etc/my.cnf
[root@ms-server2 ~]# cat /etc/my.cnf
[mysqld]
default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=2
log-bin=mysql-bin
重启数据库服务
[root@ms-server2 ~]# systemctl restart mysqld
按照主库当前的状态进行复制
CHANGE MASTER TO MASTER_HOST='192.168.213.15', MASTER_USER='repl_user' ,MASTER_PASSWORD='Antute_123',MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1023;
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.213.15', MASTER_USER='repl_user' ,MASTER_PASSWORD='Antute_123',MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1023;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
重新查看状态同步正常
mysql> show slave status\G;
*************************** 1. row ***************************
? ? ? ? ? ? ? ?Slave_IO_State: Waiting for source to send event
? ? ? ? ? ? ? ? ? Master_Host: 192.168.213.15
? ? ? ? ? ? ? ? ? Master_User: repl_user
? ? ? ? ? ? ? ? ? Master_Port: 3306
? ? ? ? ? ? ? ? Connect_Retry: 60
? ? ? ? ? ? ? Master_Log_File: mysql-bin.000003
? ? ? ? ? Read_Master_Log_Pos: 1023
? ? ? ? ? ? ? ?Relay_Log_File: ms-server2-relay-bin.000002
? ? ? ? ? ? ? ? Relay_Log_Pos: 326
? ? ? ? Relay_Master_Log_File: mysql-bin.000003
? ? ? ? ? ? ?Slave_IO_Running: Yes --显示yes为正常
? ? ? ? ? ? Slave_SQL_Running: Yes --显示yes为正常
主库
mysql> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| antute_db ? ? ? ? ?|
| information_schema |
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sys ? ? ? ? ? ? ? ?|
+--------------------+
5 rows in set (0.00 sec)
mysql> use antute_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show create table user\G;
*************************** 1. row ***************************
? ? ? ?Table: user
Create Table: CREATE TABLE `user` (
? `id` int NOT NULL AUTO_INCREMENT,
? `username` varchar(255) DEFAULT NULL COMMENT '用户名',
? `role_id` int DEFAULT NULL COMMENT '角色id',
? `password` varchar(255) DEFAULT NULL COMMENT '密码',
? `salt` varchar(255) DEFAULT NULL COMMENT '盐',
? PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37794282 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
主库插入一条数据
mysql> insert into user values(1001,'dadada',10010,'abcd123','ssss');
Query OK, 1 row affected (0.00 sec)
从库查询该表
mysql> use antute_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
查询无误同步正常
mysql> select * from user;
+------+----------+---------+----------+------+
| id ? | username | role_id | password | salt |
+------+----------+---------+----------+------+
| 1001 | dadada ? | ? 10010 | abcd123 ?| ssss |
+------+----------+---------+----------+------+
1 row in set (0.00 sec)
mysql>
?