Centos7.5 生产环境搭建MySQL 5.7主从

环境：
服务器两台：
192.168.19.77 4核心，16G内存 内网隔离，不通外网
192.168.19.78 4核心，16G内存 内网隔离，不通外网

一. 先有外网的机器，下载MySQL 5.7官网软件包

官网下载链接：
为了方便，我这里将安装MySQL5.7需要的官方软件包下载链接地址就直接贴上来
使用官方RPM包安装，需要安装5个包，分别是下面的5个链接，都是官方链接

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.23-1.el7.x86_64.rpmhttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.23-1.el7.x86_64.rpmhttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.23-1.el7.x86_64.rpmhttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.23-1.el7.x86_64.rpmhttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-devel-5.7.23-1.el7.x86_64.rpm

二. 安装MySQL

使用FTP软件，将软件包上传至内网2台数据库服务器77/78的/tmp目录下
安装rpm前，先要将MariaDB数据库和libs卸载掉

    # yum remove mariadb mariadb-server mariadb-devel mariadb-libs

安装MySQL

   #  rpm -ivh mysql-community-libs-5.7.23-1.el7.x86_64.rpm    #  rpm -ivh mysql-community-common-5.7.23-1.el7.x86_64.rpm    #  rpm -ivh mysql-community-libs-5.7.23-1.el7.x86_64.rpm    #  rpm -ivh mysql-community-client-5.7.23-1.el7.x86_64.rpm    #  rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm    #  rpm -ivh mysql-community-devel-5.7.23-1.el7.x86_64.rpm

三. 定制配置数据库
将数据库的数据、日志指向到data存放目录下，再加入主从的参数

192.168.19.77主服务器的my.cnf

[root@mysql-m ~]# cat /etc/my.cnf[mysqld]datadir=/u1/mysql/datasocket=/u1/mysql/data/mysql.socklog-error=/u1/mysql/log/mysqld.logpid-file=/u1/mysql/pid/mysqld.piduser=mysqlserver-id=1port=3306##要给从机同步的库#binlog-do-db=##不给从机同步的库(多个写多行)binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=sys##开启二进制日志log-bin=/u1/mysql/binary/mysql1-bin##自动清理 7 天前的log文件,可根据需要修改expire_logs_days=7[client]socket=/u1/mysql/data/mysql.sock

192.168.19.77从服务器的my.cnf

[root@mysql-m ~]# cat /etc/my.cnf[mysqld]datadir=/u1/mysql/datasocket=/u1/mysql/data/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/u1/mysql/log/mysqld.logpid-file=/u1/mysql/pid/mysqld.piduser=mysqlserver-id=2port=3306##从库上的参数read_only = 1master_info_repository=TABLErelay_log_info_repository=TABLE#relay_log_recovery=1   #从机禁止写#super_read_only=1      #从机禁止写[client]socket=/u1/mysql/data/mysql.sock

建立配置文件指定的目录

# mkdir -p /u1/mysql/{data,logs,pid}# chown -R mysql.mysql  /u1

启动数据库并找出root密码

# systemctl start mysqld--找出root随机密码# egrep "root@localhost"  /u1/mysql/log/mysqld.log |awk -F":" '{print $4}' zn9>

登陆数据库并修改root密码

# mysql -uroot -pEnter password:         --健入上面egrep过滤出来的随机密码mysql > ALTER USER root@localhost identified by 'TestMySQL5.7';

四. 配置主从

在主服务器上授权从服务器复制帐号

# mysql -uroot -pEnter password:         mysql > grant replication slave on *.* to mysql_ab@'192.168.19.%' identified by 'mysql_AB5.7';mysql > show master status\G*************************** 1. row ***************************             File: mysql1-bin.000001         Position: 1082     Binlog_Do_DB:  Binlog_Ignore_DB: mysql,information_schema,performance_schema,sysExecuted_Gtid_Set: 1 row in set (0.00 sec)

来到从服务器上配置连接主服务器

# mysql -uroot -pEnter password:     mysql > stop slave;mysql > chagne master to   -> master_host='192.168.19.77',     -> master_port=3306,     -> master_user='mysql_ab',     -> master_password='mysql_AB5.7',     -> master_log_file='mysql1-bin.000001',     -> master_log_pos=1082;mysql > start slave;mysql > show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.19.77                  Master_User: mysql_ab                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql1-bin.000001          Read_Master_Log_Pos: 1082               Relay_Log_File: dosercn10235b-relay-bin.000004                Relay_Log_Pos: 951        Relay_Master_Log_File: mysql1-bin.000001            Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 1082              Relay_Log_Space: 1166              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1                  Master_UUID: 45d68d37-b6f9-11e8-a947-0050569afd93             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0         Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)

配置完成，从服务器已经连接上了主服务器

五. 验证

主服务器上创建数据库、表、并插入数据

mysql > CREATE DATABASE test_ab default charset utf8;mysql > CREATE TABLE test_ab.a1(id int(2),name varchar(20));mysql > INSERT INTO test_ab.a1(id,name) VALUES(1,"测试1");mysql> select * from test_ab.a1;+------+---------+| id   | name    |+------+---------+|    1 | 测试1   |+------+---------+1 row in set (0.00 sec)

从服务器上查询该数据，验证是否复制过来

mysql> select * from test;+------+---------+| id   | name    |+------+---------+|    1 | 测试1   |+------+---------+1 row in set (0.00 sec)

至此完成。