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';