mysql 主从配置

# master修改密码，创建用户，授权用户 （主从都设置）

ALTER USER USER() IDENTIFIED BY 'mima';
create user 'root'@'%' identified WITH mysql_native_password by 'mima';
grant all on *.* to 'root'@'%' WITH GRANT OPTION;

create user 'replication'@'%' identified WITH mysql_native_password by 'mima';
grant replication slave,replication client on *.* to 'replication'@'%';
flush privileges;

 

 

#master配置文件
server_id=220
log_bin=master-binlog
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1

#slave配置文件
server_id=221
log_bin=slave-binlog
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
skip-slave-start

 

#备份主库

mysqldump --all-databases -p >>all.sql

#还原从库

mysql -p < all.sql

 

#slave

#基于Gtid 的
CHANGE MASTER TO
MASTER_HOST='192.168.10.221',
master_user='replication',
master_password='mima',
master_port=3306,
MASTER_AUTO_POSITION = 1;

 

#基于binlog日志的

mysql> show master status\G;
*************************** 1. row ***************************
File: 30b61a8f81a5-bin.000003
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

##########################

CHANGE MASTER TO
MASTER_HOST='172.17.0.2',
MASTER_USER='replication',
MASTER_PASSWORD='mima',
MASTER_PORT=3306,
MASTER_LOG_FILE='c30b61a8f81a5-bin.000003',
MASTER_LOG_POS=154;

mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.10.220
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000002
Read_Master_Log_Pos: 8753
Relay_Log_File: mysql-slave-relay-bin.000002
Relay_Log_Pos: 3996
Relay_Master_Log_File: master-binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes