mysql5.7 同步_MySQL 5.7 主从同步操作

1.主：10.1.8.73  从：10.1.8.72，主my.cnf配置文件，从my.cnf配置文件

# 主my.conf

[mysqld]

user = mysql

port = 3307

socket = /data/mysql/data3307/mysql.sock

# 关键参数，server-id 从的不能相同，一般情况大一点

server-id = 149

log_bin = on

log-bin = /data/mysql/data3307/mysql-bin

# 从my.conf

[mysqld]

user = mysql

port = 3307

socket = /data/mysql/data3307/mysql.sock

# 关键参数，server-id 从的不能相同，一般情况大一点,read_only=on只读，从库log_bin，log_slave_update一般是不开启的，除非从库下面还有从库

server-id = 150

#log_bin = on

#log-bin = /data/mysql/data3307/mysql-bin

#log_slave_update

read_only=on

2.主服务器执行

grant replication slave on *.* to 'mirrors'@'10.1.8.72' identified by 'JdsfaasforsDB';

flush privileges;

show master status;

3.从库服务器执行

# 数据库开启GTID时用这句

change master to master_host='10.1.8.73',master_port=3307,master_user='mirrors',master_password='JZJmirrorsDB',master_auto_position=1;

# 数据库没开启GTID用这个，log_file,log_pos 在备份数据时必须在备份文件时加上--master-data=2，vim查看sql，查找

change master to master_host='10.1.8.73',master_port=3307,master_user='mirrors',master_password='JZJmirrorsDB',master_log_file='mysql-bin.000004', master_log_pos=107;

#change master to master_host='10.1.8.73',master_user='mirrors',master_password='JZJmirrorsDB',master_auto_position=1;

start slave;

show slave status\G

如果看到如同主主复制一样的两个YES证明成功！

4.查看状态

mysql> show slave status\G

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 13

Current database: *** NONE ***

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.1.8.73

Master_User: mirrors

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 1259

Relay_Log_File: jzj-mysql02-relay-bin.000004

Relay_Log_Pos: 1472

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes # yes 成功

Slave_SQL_Running: Yes # yes 成功

5.导入数据库

# shell命令执行

gzip -d WEJIU_2020-04-13_00_10.sql.gz

mysql -uroot -p

# 进入数据库里面执行

mysql -h 127.0.0.1 -u root -p -P3307

source /usr/local/src/WEJIU_2020-04-13_00_10.sql；

# 授权访问

grant select,delete,insert,update,execute on *.* to 'jzjconnect'@'10.1.8.%' identified by 'JZ161684684864641';

flush privileges;

6.修改密码

# 修改root密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PdsafasffdsafafwD';