MySQL5.7主从复制

　　本文所用MySQL版本是5.7.30，主机IP是192.168.43.151，从机IP是192.168.43.152。

　　1、主机配置

　　1）备份配置文件

cp /etc/my.cnf /etc/my.cnf.bak

　　2）修改配置文件

#数据库安装后默认部分
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
validate-password=OFF
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#开启binlog日志
server-id = 151
log_bin=mysql-bin

#主从复制，主机部分
#需要进行复制的数据库
#binlog-do-db=mydb1
#不需要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1m
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

　　3）重启数据库

systemctl restart mysqld

　　2、从机配置

　　1）备份配置文件，如主机部分

　　2）修改配置文件

#数据库安装后默认部分
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
validate-password=OFF
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#配置server-id
server-id = 152
#log_bin=mysql-bin

#主从复制,从机部分
#为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1m
binlog_format=mixed
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
relay_log=dev-relay-bin
#read_only=1
super_read_only=1

　　3）重启数据库，如主机部分

　　3、准备数据

　　1）主机建库、建表、准备数据

CREATE DATABASE IF NOT EXISTS mydb1 DEFAULT CHARACTER SET utf8;

USE mydb1;

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id int(11) DEFAULT NULL,
  name varchar(20) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES ('1', 'a1', '18', '男');
INSERT INTO student VALUES ('2', 'a2', '16', '女');
INSERT INTO student VALUES ('3', 'a3', '17', '男');
INSERT INTO student VALUES ('4', 'a4', '17', '女');
INSERT INTO student VALUES ('5', 'a5', '18', '男');
INSERT INTO student VALUES ('6', 'a6', '19', '女');

　　2）主机导出数据

mysqldump -uroot -p123456 mydb1 > /var/mysql/backup/mydb1_$(date +%Y%m%d_%H%M%S).sql

　　3）从机导入数据

mysql> source /tmp/mydb1_20170615_122859.sql;

　　备注：导入之前先要创建数据库

　　4、演示主从复制

　　1）从机连接主机

　　首先在主机上确定主机当前的日志文件，和文件起始位置（最好刷新日志，否则可能出现问题，比如没创建表却插入等）。

mysql> flush logs;
mysql> show master status;

　　在从机上执行连接语句：

change master to master_host='192.168.43.151',master_user='root',master_password='123456',master_port=3306,
master_log_file='mysqlbin.000006',master_log_pos=154,master_connect_retry=30;

　　2）查看从机状态

mysql> show slave status G;

　　3）启动从机并再次查看从机状态，看到两个Yes说明成功

mysql> start slave;
mysql> show slave status G;

　　4）主机写入数据，从机会同步数据

INSERT INTO student VALUES ('7', 'a7', '20', '男');
INSERT INTO student VALUES ('8', 'a8', '20', '女');

　　5）从机不能写数据

　　备注：

　　1）正常情况下应该在主机上新建用户，然后授权，而不是直接使用root用户（我之前给所有机器授了所有权限）。

grant replication slave, replication client on *.* to 'repl'@'192.168.43.152' identified by '123456';
flush privileges;

　　2）配置文件my.cnf中设置read_only=1，只能控制普通用户只读，控制root用户要设置super_read_only=1。