mysql8主从配置

• 环境准备

	Master	slave
OS	ubuntu-19.10-desktop-amd64.iso
mysql	8.0.18
ip	192.168.11.132	192.168.11.134

 

 

• 配置master库的配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id               = 132                                   
log_bin                 = /var/log/mysql/mysql-bin.log               
expire_logs_days        = 10                                
max_binlog_size         = 200M                                    
binlog_do_db            = python                             
binlog_ignore_db        = mysql     
port=3306

• 配置slave库的配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id               = 134                       
expire_logs_days        = 10                  
max_binlog_size         = 200M              
replicate_do_db         = python             
replicate_ignore_db     = mysql 
port=3306

• 分别配置master、slave库的库以及相关表

mysql> create database python;
mysql> use python;
mysql> create table user(id int(11) primary key auto_increment not null,name varchar(32) not null);

• 在master配置

#授权给任何ip通过root登陆
mysql>create USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
#授权给ip为134地址为slave，可以通过root的所有库的所有表
mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.11.134';

• 定位master的日志及其偏移（在master中）

mysql> show master status;

 

•  在slave中授权

mysql>CHANGE MASTER TO
MASTER_HOST='192.168.11.132',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=1022;

• 在slave中启动同步

start slave;

• 查看状态

mysql>show slave status\G;