centos7部署配置MySQL8 单机、主备

这篇文章是本人上一个文章的延续，前期服务器、虚拟机配置在上一篇文章，本文主要讲解mysql8的部署。 上一篇链接：超超超保姆级教学--常用的linux部署与运维--------从部署到维护 （认识字就能学会！！！！）-CSDN博客文章浏览阅读9次。基础linux部署与运维，从创建虚拟机，系统命令演示，系统调优，常用软件、中间件、数据库及环境部署与配置、搭建简单的开发环境。https://blog.csdn.net/m813411458/article/details/134010630

一、MySQL8单机部署：

本次使用mysql8.0.30 rpm版进行部署
 

1.上传mysql包到服务器的 /data/ 目录下

2.删除CentOS自带的MariaDB：       

3.按顺序安装一下mysql包

rpm命令安装：

rpm -ivh mysql-community-common-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.30-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.30-1.el7.x86_64.rpm

4.创建mysql的目录

5.修改mysql的配置文件

vim /etc/my.cnf


[client]
port = 3306
socket = /data/mysql/mysqld.sock

[mysql]
no-auto-rehash

[mysqld]
port = 3306            #可根据现场情况更改端口
datadir = /data/mysql/data
socket = /data/mysql/mysqld.sock
log-error = /data/mysql/error.log
pid-file = /data/mysql/mysqld.pid
tmpdir = /data/mysql/mysqltemp
log_bin_trust_function_creators = 1
default_authentication_plugin = 'mysql_native_password'

max_connections = 1000
max_connect_errors = 1000
max_allowed_packet = 1G
wait_timeout = 1800
lock_wait_timeout = 3600
tmp_table_size = 64M
max_heap_table_size = 64M
master_info_repository = TABLE
group_concat_max_len = 10240000
interactive_timeout = 1800
innodb_flush_log_at_trx_commit = 1
open_files_limit = 65535
default_time_zone = "+8:00"
character-set-server = utf8
collation-server = utf8_general_ci
init_connect='SET NAMES utf8'
lower_case_table_names = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sort_buffer_size=2048M

6.修改mysql文件夹所属组

chown -R mysql.mysql /data/mysql

7.查看MySql运行状态：

systemctl status mysqld

8.启动MySql：

systemctl start mysqld

9.查看root随机密码：

MySQL为root用户生成的随机密码通过mysqld.log文件可以查找到：

grep 'temporary password' /data/mysql/error.log

通过mysql -uroot -p 回车后输入刚刚查找到的随机密码

进入MySQL数据库后进行以下操作

mysql>set password for root@localhost = 'XXXXXXXXXX';        #第一次登录后需重新设置密码
mysql> use mysql;
mysql> update user set user.Host='%' where user.User='root';
mysql> FLUSH PRIVILEGES;
###mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'XXXXXXXXXX' PASSWORD EXPIRE NEVER; #修改加密规则 
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'XXXXXXXXXX'; #更新一下用户的密码 
mysql> FLUSH PRIVILEGES; #刷新权限

重启mysql：

systemctl restart mysqld

单机mysql到此安装完毕。

二、mysql主备搭建

1.主备搭建前期准备

mysql主	mysql备
192.168.45.132	192.168.45.132

关闭防火墙   启动ntp时钟服务   启动ntp时钟服务   配置免密登录   配置两台的hosts文件

2.开始主备搭建

修改两台mysql的配置文件

主：

server_id = 11              #服务器id 两台不一样
log_bin = master-binlog     #从1为 master-binlog,从2为 slave-binlog
log-slave-updates = 1    
binlog_format = row            #强烈建议，其他格式可能造成数据不一致
binlog_row_image = FULL
binlog_expire_logs_seconds = 1209600
sync_binlog = 1
skip_slave_start=1
skip_name_resolve = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

备：

server_id = 12                 #服务器id 两台不一样
log_bin = master-binlog        #从1为 master-binlog,从2为 slave-binlog
relay_log = relay-log-bin
relay_log_index = slave-relay-bin.index
relay_log_info_repository = TABLE
relay_log_recovery = 1
binlog_format = row            #强烈建议，其他格式可能造成数据不一致
binlog_row_image = FULL
binlog_expire_logs_seconds = 1209600
sync_binlog = 1
skip_slave_start=1
skip_name_resolve = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

重启主、备两台的MySql服务：

systemctl restart mysqld

主、备两台mysql执行：

create user repl@'%' identified by 'XXXXXXXXXX';
grant replication slave,replication client on *.* to 'repl'@'%';
flush privileges;

主库查看状态：

从库执行：

根据真实情况修改配置

change master to
MASTER_HOST='10.191.26.21',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='XXXXXXXXXX',
MASTER_LOG_FILE=master-binlog.000003',
MASTER_LOG_POS=839;

从库开启slave

start slave;
show slave status\G

出现以下内容表示主备部署完毕