mysql8.0主从同步_mysql8.0 主从同步

环境：关闭防火墙、selinux

192.168.17.132  master1

192.168.17.134  master2

1、编辑配置文件

master1配置文件

vi /etc/my.cnf

[client]

port=3306socket=/tmp/mysql/mysql.sock

[mysqld]

port=3306user=mysql

socket=/tmp/mysql/mysql.sock

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

log-error=error.log

log_bin=mysql-bin

server_id= 1sync_binlog=1slave-skip-errors=all

auto_increment_increment=2auto_increment_offset=1transaction_isolation= READ-COMMITTED

character-set-server =utf8mb4

collation-server =utf8mb4_general_ci

default_authentication_plugin=mysql_native_password

lower_case_table_names= 1#skip-grant-tables

master2配置文件

vi /etc/my.cnf

[client]

port=3306socket=/tmp/mysql/mysql.sock

[mysqld]

port=3306user=mysql

socket=/tmp/mysql/mysql.sock

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

log-error=error.log

log-bin=mysql-bin　　　　　　　　　　#####

server-id = 2　　　　　　　　　　　　#####slave-skip-errors=all　　　　　　　#####

sync_binlog=1　　　　　　　　　　　　#####auto_increment_increment=2　　　　#####auto_increment_offset=2　　　　　　#####transaction_isolation= READ-COMMITTED

character-set-server =utf8mb4

collation-server =utf8mb4_general_ci

lower_case_table_names= 1default_authentication_plugin=mysql_native_password

2、重启服务

/usr/local/mysql/support-files/mysql.server start/stop/restart

3、创建授权用户

create user mysqlrepl@'192.168.17.%';

update user set authentication_string='' where user='mysqlrepl';

alter user 'mysqlrepl'@'192.168.17.%' identified with mysql_native_password by '123456';

grant all privileges on . to mysqlrepl@'192.168.17.%%' identified by '123456' with grant option ;

查看授权用户

select host, user, authentication_string, plugin from mysql.user;

show grants for mysqlrepl@'192.168.17.%';

4、查看mster信息

show mater status\G;

mater1信息：

master2信息：

5、在slave创建master信息

master1执行：change master to master_host='192.168.17.134',master_user='mysqlrepl', master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=932;

master2执行：change master to master_host='192.168.17.132',master_user='mysqlrepl', master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=156;

6、开启master1和master2的slave：

start slave

查看状态：show slave status\G;

7、验证：在master1上创建库1、在master2上创建库2

同步成功。

注：Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB