mysql （percona）5.7 安装及主从配置

一、基础环境

备：192.168.1.1
主：192.168.1.2
pmoopr/密码
root/密码
mysql：密码

二、安装步骤

开始安装：

[root@yycgggdb Data]# rpm -ivh Percona-Server-server-57-5.7.26-29.1.el7.x86_64.rpm
warning: Percona-Server-server-57-5.7.26-29.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
Percona-Server-client-57 is needed by Percona-Server-server-57-5.7.26-29.1.el7.x86_64
Percona-Server-shared-57 is needed by Percona-Server-server-57-5.7.26-29.1.el7.x86_64
net-tools is needed by Percona-Server-server-57-5.7.26-29.1.el7.x86_64

注意提示需要安装net-tools包：

[root@yycgggdb Data]# yum -y install net-tools

按顺序安装：

[root@yycgggdb Data]# rpm -ivh Percona-Server-shared-compat-57-5.7.26-29.1.el7.x86_64.rpm
warning: Percona-Server-shared-compat-57-5.7.26-29.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5
: NOKEYPreparing...                          ################################# [100%]
Updating / installing...
1:Percona-Server-shared-compat-57-5################################# [100%]
[root@yycgggdb Data]# 
[root@yycgggdb Data]# rpm -ivh Percona-Server-shared-57-5.7.26-29.1.el7.x86_64.rpm
warning: Percona-Server-shared-57-5.7.26-29.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
1:Percona-Server-shared-57-5.7.26-2################################# [100%]
[root@yycgggdb Data]# rpm -ivh Percona-Server-client-57-5.7.26-29.1.el7.x86_64.rpm
warning: Percona-Server-client-57-5.7.26-29.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
1:Percona-Server-client-57-5.7.26-2################################# [100%]
[root@yycgggdb Data]# rpm -ivh Percona-Server-server-57-5.7.26-29.1.el7.x86_64.rpm
warning: Percona-Server-server-57-5.7.26-29.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
1:Percona-Server-server-57-5.7.26-2################################# [100%]
Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://www.percona.com/doc/percona-server/5.7/management/udf_percona_toolkit.html for more details

2.1 设置环境变量

vi /etc/profile.d/mysql.sh
exportPATH=/Data/perhome/mysql-5.7.26/bin/:$PATH
source /etc/profile.d/mysql.sh

2.2 初始化mysql

mysqld --initialize --user=mysql --datadir=/Data/perdata/ --basedir=/Data/perhome/mysql-5.7.26/

2.3 修改密码

查询日志里面的初始化密码：

[root@yycgggdb log]# cat /var/log/mysqld.log | grep password

修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';

2.4 修改配置文件

vim my.cnf
[mysqld]
port = 41667 
user=mysql
basedir =/Data/perhome/mysql-5.7.26
datadir =/Data/perdata
socket =/Data/perhome/var/run/mysql.sock
[mysqld_safe]
open-files-limit =10240
log-error =/Data/perhome/var/log/mysql_err.log
pid-file =/Data/perhome/var/run/mysqld.pid

mkdir -p /Data/perhome/var/log
mkdir -p   /Data/perhome/var/run
mkdir -p  /Data/perhome/
mkdir -p  /Data/perdata
#

第二章 配置mysql主备

一、修改字符集

1,设置utf-8字符格式，两个主机配置相同

character_set_server=utf8
init_connect='SET NAMES utf8'

二、主机创建复制用户

create user 'repl'@'%' identified by '密码';

授权

GRANT REPLICATION SLAVE ON *.* TO  'repl'@'%' identified by '密码';

主机（master）添加配置文件信息：

要给从机同步的库（如果不写，默认全部同步）

binlog-do-db=db01

不给从机同步的库(多个写多行)

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

自动清理 7 天前的log文件,可根据需要修改

expire_logs_days=7

三、启用二进制日志

log-bin=master-bin

四、设置主从参数

主机添加配置信息：

server-id=241
log_bin_index = master-bin.index

从机添加的配置信息：

server-id = 242

加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错

read_only = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay-log = slave-relay-bin

主服务器I/O日志读取、记录及存放

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

master服务器查看MySQL状态：

show master status;

运行slave服务器MySQL（从服务器）：执行接入master服务器MySQL服务语句：

change master to master_host='主服务器ip地址',
master_port=3306,master_user='链接主服务器账户',
master_password='链接主服务器角色账户密码',
master_log_file='主服务器file参数',
master_log_pos=主服务器positon参数

运行：

change master to
master_host='192.168.1.241',
master_user='repl',
master_password='密码',
master_port=3306,
master_log_file='master-bin.000001',
master_log_pos=1095,
master_connect_retry=10;

五、导入用户数据

5.1 导入客户给的用户

create database cloudplatform charset utf8mb4;
source /Data/cloudplatform.sql
stop slave ;
start slave ;
show slave status\G
drop user repl@localhost ;

5.2 从主库同步备库的情况

主库上操作

s1: FLUSH TABLES WITH READ LOCK;　　#锁定数据库,不让写入数据
s2: show master status;　　         #获取binlog文件和位置.后面slave需要使用
s2: mysqldump -uroot -p'xxx' -B --master-data=1 --events datafbdns yhxtxxxcn >/root/datafbdns2.sql.gz  #备份现有数据库.
s1:  UNLOCK TABLES;　　             #解除锁定

六、授予堡垒机访问权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.61' IDENTIFIED BY 'XXXXXX' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.62' IDENTIFIED BY 'XXXXXX' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.101' IDENTIFIED BY 'XXXXXX' WITH GRANT OPTION;

FLUSH PRIVILEGES;

本文由博客一文多发平台 OpenWrite 发布！

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容