mysql8读写分离

环境：CentOS7

master：192.168.0.221

slave：192.168.0.220

master创建相关帐户，在主数据库服器上执行如下操作

mysql> create user 'rw'@'%' identified with mysql_native_password by 'tqw961110';
Query OK, 0 rows affected (0.13 sec)

mysql>  grant all on *.* to 'rw'@'%';
Query OK, 0 rows affected (0.10 sec)

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

[root@server02 mysql]# vi /usr/local/mysql/etc/my.cnf 

[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
server-id=2
log-bin=mysql-bin
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log
#这个就是用之前的身份认证插件
default-authentication-plugin = mysql_native_password
#保证日志的时间正确
log_timestamps = SYSTEM

slave从数据库上操作

[root@server01 mysql]# cat /usr/local/mysql/etc/my.cnf 
[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
server-id=1
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log
#这个就是用之前的身份认证插件
default-authentication-plugin = mysql_native_password
#保证日志的时间正确
log_timestamps = SYSTEM

mysql> change master to
    -> master_host='192.168.0.221',
    -> master_user='rw',
    -> master_password='tqw961110',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=3104;
Query OK, 0 rows affected, 2 warnings (0.38 sec)

mysql> start slave;
Query OK, 0 rows affected (0.10 sec)

 

测试：master上操作。

mysql> create  database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test;
Database changed
mysql> create table test(id int(10),name varchar(20));
Query OK, 0 rows affected (0.15 sec)

mysql> insert into test values(1,'this_is_master');
Query OK, 1 row affected (0.17 sec)

mysql> select * from test;
+------+----------------+
| id   | name           |
+------+----------------+
|    1 | this_is_master |
+------+----------------+
1 row in set (0.00 sec)

slave上查看

mysql> use test;
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 test;
+------+----------------+
| id   | name           |
+------+----------------+
|    1 | this_is_master |
+------+----------------+
1 row in set (0.00 sec)