mysql8.0 主从复制陈帅亲测

理论与实践现相结合?

准备:

...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
...

参考:https://www.jianshu.com/p/acac5124991d

主库:
[mysqld]
# Only allow connections from localhost
#bind-address = 127.0.0.1 #这个需要屏蔽掉，不然从库链接不上主库
log-bin=mysql-bin #二进制文件存放路径
server-id=57 #服务器 id 唯一一般跟ip最后断相同 127.0.0.57
binlog-do-db=chenshuai #等待同步的数据库

从库:
log-bin=mysql-bin
server-id=136
binlog-do-db=chenshuai

mysql8和原来的版本有点不一样，8的安全级别更高，所以在创建远程连接用户的时候，
不能用原来的命令（同时创建用户和赋权）:
mysql>grant all PRIVILEGES on *.* to test@'localhost' identified  by '123456';

必须先创建用户（密码规则：mysql8.0以上密码策略限制必须要大小写加数字特殊符号）：
mysql>create user chenadmin@'%' identified  by 'Chenadmin0.';
再进行赋值：
mysql>grant all privileges on *.* to chenadmin@'%' with grant option;
最后刷新一下：
mysql>flush privileges;
当你进行远程连接是，会出现这样的错误：
Unable to load authentication plugin 'caching_sha2_password'.
是因为mysql8使用的是caching_sha2_password加密规则，最简单的方法是修改远程连接用户的加密规则：
mysql>ALTER USER 'chenadmin'@'%' IDENTIFIED WITH mysql_native_password BY 'Chenadmin0.';

查看主库信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 1329 | chenshuai | | |
+------------------+----------+--------------+------------------+-------------------+

指定主服务器信息
demo: CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='user', MASTER_PASSWORD='password',MASTER_LOG_FILE='filename',MASTER_LOG_POS=position;
操作: CHANGE MASTER TO MASTER_HOST='172.16.1.57', MASTER_USER='chenadmin', MASTER_PASSWORD='Chenadmin0.',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=1361;

6. 启动slave同步进程。