Centos7.5生产环境搭建MySQL5.7主从
环境:
服务器两台:
192.168.19.77 4核心,16G内存 内网隔离,不通外网
192.168.19.78 4核心,16G内存 内网隔离,不通外网
10年积累的成都网站建设、网站制作经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先做网站设计后付款的网站建设流程,更有大新免费网站建设让你可以放心的选择与我们合作。
一. 先有外网的机器,下载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.rpm
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.23-1.el7.x86_64.rpm
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.23-1.el7.x86_64.rpm
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.23-1.el7.x86_64.rpm
https://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/data
socket=/u1/mysql/data/mysql.sock
log-error=/u1/mysql/log/mysqld.log
pid-file=/u1/mysql/pid/mysqld.pid
user=mysql
server-id=1
port=3306
##要给从机同步的库
#binlog-do-db=
##不给从机同步的库(多个写多行)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-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/data
socket=/u1/mysql/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/u1/mysql/log/mysqld.log
pid-file=/u1/mysql/pid/mysqld.pid
user=mysql
server-id=2
port=3306
##从库上的参数
read_only = 1
master_info_repository=TABLE
relay_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 -p
Enter password: --健入上面egrep过滤出来的随机密码
mysql > ALTER USER root@localhost identified by 'TestMySQL5.7';
四. 配置主从
在主服务器上授权从服务器复制帐号
# mysql -uroot -p
Enter 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,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
来到从服务器上配置连接主服务器
# mysql -uroot -p
Enter 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: 0
Master_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)
至此完成。
本文题目:Centos7.5生产环境搭建MySQL5.7主从
网页路径:http://scyanting.com/article/jddsid.html