Mysql 5.7.9 Linux环境主从配置

Mysql 主从环境配置相对与Oracle DataGuard比较简单，安全性和稳定性，可管理性应该差很多，在前面源码安装Mysql的基础上配置Mysql主从环境

准备Master主机的Mysql配置文件

编译Mysql时指定Mysql配置文件位置在/opt/apps/etc目录下
新建my.cnf文件

[client]
port = 8088
socket = /opt/apps/mysql/mysql.sock
default-character-set = utf8

[mysqld]
collation-server = utf8_unicode_ci
port = 3306
bind-address = 0.0.0.0
character-set-server = utf8
init-connect ='SET NAMES utf8'
max_allowed_packet = 64M
port = 3306
datadir=/opt/data/mysql
socket = /opt/apps/mysql/mysql.sock
innodb_file_per_table=1
lower_case_table_names=1
max_allowed_packet = 100M
sort_buffer_size=10M
net_buffer_length = 8K
read_buffer_size=10M
read_rnd_buffer_size=10M
query_cache_size=64M
join_buffer_size=10M
max_connections=3600
#log-slow-queries=/opt/apps/mysql/mysqlslowquery.log

innodb_buffer_pool_size = 4G
default-storage-engine=InnoDB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id=1  #服务器id (主从必须不相同)
log-bin=mysql-bin  #打开日志(主机必须开启日志)
binlog-do-db=auth #同步的数据库名称
log-bin-index=master-bin.index

binlog-ignore-db=mysql  #不参加同步的数据库
binlog-ignore-db=information_schema  #不参加同步的数据库
binlog-ignore-db=performance_schema #不参加同步的数据库
binlog-ignore-db=sys #不参加同步的数据库
expire_logs_days=7 #log文件保留最近7天

[mysqld_safe]
timezone = '+8:00'

设计Master Mysql启动文件

startmsyql.sh

#!/bin/bash

mysqld  --init-file=/opt/apps/etc/my.cnf  &

启动Master Mysql数据库，准备用户

/opt/apps/msyql/startmysql.sh

Mysql>create user  'slaver'@'%' IDENTIFIED BY 'slaver';

Mysql>grant replication slave on *.*  to 'slaver'@'%' identified by 'slaver';

主库配置完成

从库Slave Mysql配置文件

[client]
port = 8088
socket = /opt/apps/mysql/mysql.sock
default-character-set = utf8

[mysqld]
collation-server = utf8_unicode_ci
port = 3306
bind-address = 0.0.0.0
character-set-server = utf8
init-connect ='SET NAMES utf8'
max_allowed_packet = 64M
port = 3306
datadir=/opt/data/mysql
socket = /opt/apps/mysql/mysql.sock
innodb_file_per_table=1
lower_case_table_names=1
max_allowed_packet = 100M
sort_buffer_size=10M
net_buffer_length = 8K
read_buffer_size=10M
read_rnd_buffer_size=10M
query_cache_size=64M
join_buffer_size=10M
max_connections=3600
#log-slow-queries=/opt/apps/mysql/mysqlslowquery.log

innodb_buffer_pool_size = 4G
default-storage-engine=InnoDB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server_id=2 #服务器id (主从必须不相同)
read_only=1 #只读配置
master_info_repository=TABLE
relay_log_info_repository=TABLE

relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin



[mysqld_safe]
timezone = '+8:00'

准备从库启动脚本

#!/bin/bash

mysqld  --init-file=/opt/apps/etc/my.cnf  &

启动从库

Mysql> use 数据库；

Mysql>stop slave;

Mysql>change master to
master_host=‘192.168.100.2’,
master_user=‘slaver’,
master_password=‘slaver’,
master_log_file=‘mysql-bin.000001’,#在Master主库上执行show master status 查看
master_log_pos=1880;#在Master主库上执行show master status 查看

mysql> start slave;

测试

在主库上执行
create table t_test (id int);

insert into t_test(id) values(1);
insert into t_test(id) values(2);
insert into t_test(id) values(3);
insert into t_test(id) values(4);

在从库上查看数据

如果从库同步数据失败，需要重新创建主从环境

1、从主库上备份数据库db
2、到从库上恢复数据库
3、到主库上执行show master status找到同步日志点
4、到从库上启动数据库，执行stop slave
5、到从库上执行

change master to
master_host='192.168.100.2',
master_user='slaver',
master_password='slaver',
master_log_file='mysql-bin.000012',
master_log_pos=154;

6、到从库上执行start slave，并执行show slave status查看从库状态

说明从库启动成功，

异常处理
如果在从库上执行show slave status返回如下错误

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

修改此错误在主库上
执行flush logs；后再执行show master status;

重新配置从库即可