理论与实践现相结合?
准备:
mysqld --help --verbose | less
...
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
...
主库:
[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同步进程。