环境:关闭防火墙、selinux
192.168.17.132 master1
192.168.17.134 master2
1、编辑配置文件
master1配置文件
vi /etc/my.cnf
[client] port=3306 socket=/tmp/mysql/mysql.sock [mysqld] port=3306 user=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 = 1 sync_binlog=1 slave-skip-errors=all auto_increment_increment=2 auto_increment_offset=1 transaction_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=3306 socket=/tmp/mysql/mysql.sock [mysqld] port=3306 user=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 = 1 default_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