mysql主从配置实现一主一从读写分离

主从介绍
Mysql主从又叫Replication、AB复制。简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，实现数据实时同步

mysql主从是基于binlog，主上需开启binlog才能进行主从

主从过程大概有3个步骤

主将更改操作记录到binlog里

从将主的binlog事件（sql语句） 同步本机上并记录在relaylog里

从根据relaylog里面的sql语句按顺序执行

主从作用

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免影响业务

主从形式

* 一主一从
* 主主复制
* 一主多从---扩展系统读取的性能，因为读是在从库读取的
* 多主一从---5.7版本开始支持
* 联级复制

主从复制原理

主从复制步骤

• 主库将所有的写操作记录在binlog日志中，并生成log dump线程，将binlog日志传给从库的I/O线程

• 从库生成两个线程，一个是I/O线程，另一个是SQL线程

• I/O线程去请求主库的binlog日志，并将binlog日志中的文件写入relay log（中继日志）中
• SQL线程会读取relay loy中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终数据一致的目的

主从复制配置步骤：

1. 确保从数据库与主数据库里的数据一致
2. 在主数据库里创建一个同步账户授权给从数据库使用
3. 配合主数据库（修改配置文件）
4. 配置从数据库（修改配置文件）

需求
搭建两台MYSQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明

在两台服务器上都按装mysql

环境准备
关闭防火墙以SELINUX
[ ~]# systemctl stop firewalld
[ ~]# systemctl disable firewalld
[ ~]#  sed -ri ‘s/(SELINUX=).*/\1disabled/g‘ /etc/selinux/config
[ ~]# setenforce 0

安装mysql
安装依赖包
[ ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel

创建用户和组
[ ~]# groupadd -r -g 306 mysql
[ ~]# useradd -M -s /sbin/nologin -g 306 -u 306 mysql

下载二进制格式的mysql软件包
[ ~]# cd /usr/src/
[ src]#wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

解压软件至/usr/local/
[ src]# ls
debug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[ src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[ src]#  ls  /usr/local/
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.22-linux-glibc2.12-x86_64  sbin  share  src
[ src]#  cd  /usr/local/
[ local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.22-linux-glibc2.12-x86_64/"
[ local]# ll

总用量 0
drwxr-xr-x. 2 root root   6 11月  5 2016 bin
drwxr-xr-x. 2 root root   6 11月  5 2016 etc
drwxr-xr-x. 2 root root   6 11月  5 2016 games
drwxr-xr-x. 2 root root   6 11月  5 2016 include
drwxr-xr-x. 2 root root   6 11月  5 2016 lib
drwxr-xr-x. 2 root root   6 11月  5 2016 lib64
drwxr-xr-x. 2 root root   6 11月  5 2016 libexec
lrwxrwxrwx. 1 root root  36 9月   7 22:20 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 9月   7 22:19 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 11月  5 2016 sbin
drwxr-xr-x. 5 root root  49 9月   3 23:02 share
drwxr-xr-x. 2 root root   6 11月  5 2016 src

修改目录/usr/locaal/mysql的属主属组
[ local]# chown -R mysql.mysql /usr/local/mysql
[ local]#  ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 9月   7 22:20 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

添加环境变量
[ local]# ls /usr/local/mysql
bin  COPYING  docs  include  lib  man  README  share  support-files
[ local]# cd
[ ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
[ ~]# . /etc/profile.d/mysql.sh
[ ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

建立数据存放目录
[ ~]# cd /usr/local/mysql
[ mysql]# mkdir /opt/data
[ mysql]#  chown -R mysql.mysql /opt/data/
[ mysql]#  ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 9月   7 22:25 data

初始化数据库
[ mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
//这个命令的最后会生成一个临时密码，此处密码是1EbNA-k*BtKo

配置mysql
[ ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
"/usr/local/include/mysql" -> "/usr/local/mysql/include/"
[ ~]# echo ‘/usr/local/mysql/lib‘ > /etc/ld.so.conf.d/mysql.conf
[ ~]#  ldconfig -v

生成配置文件
[ ~]# cat > /etc/my.cnf <<EOF
> [mysqld]
> basedir = /usr/local/mysql
> datadir = /opt/data
> socket = /tmp/mysql.sock
> port = 3306
> pid-file = /opt/data/mysql.pid
> user = mysql
> skip-name-resolve
> EOF

配置服务启动脚本
[ ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[ ~]#  sed -ri ‘s#^(basedir=).*#\1/usr/local/mysql#g‘ /etc/init.d/mysqld
[ ~]# sed -ri ‘s#^(datadir=).*#\1/opt/data#g‘ /etc/init.d/mysqld

启动mysql
[ ~]#  service mysqld start
Starting MySQL.Logging to ‘/opt/data/yanyinglai.err‘.
.. SUCCESS!
[ ~]#  ps -ef|grep mysql
root       4897      1  0 22:38 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql      5075   4897  6 22:38 pts/2    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=yanyinglai.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root       5109   4668  0 22:38 pts/2    00:00:00 grep --color=auto mysql
[ ~]# ss -antl
State       Recv-Q Send-Q                     Local Address:Port                                    Peer Address:Port              
LISTEN      0      128                                    *:22                                                 *:*                  
LISTEN      0      100                            127.0.0.1:25                                                 *:*                  
LISTEN      0      128                                   :::22                                                :::*                  
LISTEN      0      100                                  ::1:25                                                :::*                  
LISTEN      0      80                                    :::3306                                              :::*                  

修改密码
使用临时密码修改
[ ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> set password = password(‘123456‘);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

mysql主从配置
确保从数据库与主数据库的数据一样先在主数据库创建所需要同步的库和表
[ ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. Al

Oracle is a registered trademark of Oracle Corporation and
affiliates. Other names may be trademarks of their respect
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the curr

mysql> create database yan;
Query OK, 1 row affected (0.00 sec)

mysql> create database lisi;
Query OK, 1 row affected (0.00 sec)

mysql> create database wangwu;
Query OK, 1 row affected (0.00 sec)

mysql> use yan;
Database changed
mysql> create table tom (id int not null,name varchar(100)not null ,age tinyint);
Query OK, 0 rows affected (11.83 sec)

mysql> insert tom (id,name,age) values(1,‘zhangshan‘,20),(2,‘wangwu‘,7),(3,