RPM方式部署Mysql8主从模式

1、环境

系统版本	MySQL版本
centos7.9	mysql8.0.18

2、卸载老版本数据库

rpm -qa | grep mysql
rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64

rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps

3、部署准备

使用mysql 官方给定yum源进行部署

wget  https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

rpm -ivh mysql80-community-release-el7-1.noarch.rpm 

yum clean all
yum makecache

4、安装MySQL

1.查看mysql源内的mysql

yum repolist all | grep mysql

yum list | grep mysql-community

2、安装mysql8

yum install mysql-community-server -y

3、查看安装包

rpm -qa | grep mysql
mysql-community-libs-8.0.18-1.el7.x86_64
mysql80-community-release-el7-1.noarch                  #此包为yum源
mysql-community-common-8.0.18-1.el7.x86_64
mysql-community-client-8.0.18-1.el7.x86_64
mysql-community-server-8.0.18-1.el7.x86_64

5、设置my.cnf配置文件

一定要在启动mysql前进行配置my.cnf

mysql_master

[root@mysql_master /]# vim /etc/my.cnf
[client]
port = 8809
socket = /usr/local/mysql/run/mysql.sock
[mysqld]
local_infile = 1
secure_file_priv = /usr/local/mysql/temp
log_timestamps=SYSTEM
default-time_zone = '+8:00'
port = 8809
wait_timeout = 28800
socket= /usr/local/mysql/run/mysql.sock
pid_file = /usr/local/mysql/run/mysql.pid
datadir = /usr/local/mysql/data
default_storage_engine = InnoDB
default_authentication_plugin=mysql_native_password
max_allowed_packet = 512M
max_connections = 65536
open_files_limit = 65536
log_bin_trust_function_creators=1
skip-name-resolve
lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
innodb_read_io_threads = 16
innodb_io_capacity = 2000
innodb_buffer_pool_size = 2G           #MySQL最大缓冲区可用内存，看机器内存情况给定
innodb_log_file_size = 128M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 512M
log-error = /usr/local/mysql/log/mysql_error.log
log-bin = /usr/local/mysql/binlogs/mysql-bin
server-id = 1
max_binlog_size = 100M
binlog_expire_logs_seconds = 604800
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/mysql_slow_query.log
long_query_time = 5
# optimized on 20190314
binlog_cache_size=1048576
binlog_format='ROW'
binlog_stmt_cache_size=1048576
log_queries_not_using_indexes=ON
sync_binlog=100
innodb_flush_log_at_trx_commit=2
tmp_table_size = 512M
max_heap_table_size = 512M
table_open_cache=30000

master_slave

[root@mysql_slave src]# vim /etc/my.cnf
[client]
port = 8809
socket = /usr/local/mysql/run/mysql.sock
[mysqld]
local_infile = 1
secure_file_priv = /usr/local/mysql/temp
log_timestamps=SYSTEM
default-time_zone = '+8:00'
port = 8809
wait_timeout = 28800
socket= /usr/local/mysql/run/mysql.sock
pid_file = /usr/local/mysql/run/mysql.pid
datadir = /usr/local/mysql/data
default_storage_engine = InnoDB
default_authentication_plugin=mysql_native_password
max_allowed_packet = 512M
max_connections = 65536
open_files_limit = 65536
log_bin_trust_function_creators=1
skip-name-resolve
lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
innodb_read_io_threads = 16
innodb_io_capacity = 2000
innodb_buffer_pool_size = 2G
innodb_log_file_size = 128M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 512M
log-error = /usr/local/mysql/log/mysql_error.log
log-bin = /usr/local/mysql/binlogs/mysql-bin
server-id = 2
max_binlog_size = 100M
binlog_expire_logs_seconds = 604800
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/log/mysql_slow_query.log
long_query_time = 5
# optimized on 20190314
binlog_cache_size=1048576
binlog_format='ROW'
binlog_stmt_cache_size=1048576
log_queries_not_using_indexes=ON
sync_binlog=100
innodb_flush_log_at_trx_commit=2
tmp_table_size = 512M
max_heap_table_size = 512M
table_open_cache=30000
# skip talbe
replicate-wild-ignore-table='crmdb.da_buffer'

6、建立mysql目录并赋予权限

使用rpm安装后会自动创建mysql用户，所以并不需要我们去手动创建

mkdir /usr/local/mysql/{binlogs,data,log,run,temp} -p

chown -Rf mysql.mysql /usr/local/mysql/*

启动MySQL并查看密码

systemctl start mysqld

systemctl enable mysqld

在日志中抓取密码，是被存储在错误日志里面需要筛选出来

[root@mysql_master /]# cat /usr/local/mysql/log/mysql_error.log | grep password
2019-11-12T15:45:14.455872+08:00 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: *3Oq?r/7&Bmp

#使用SSL加密连接
mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

#数据库加固
mysql_secure_installation

Enter password for user root:           #输入刚才日志里面的密码
New password:                           #修改新的mysql root密码
Re-enter new password:                  #再次输入确认新密码
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No                  #是否需要更改root密码
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Yes                         #移除匿名账户
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Yes                   #禁止root远程登录
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Yes          #移除测试数据库
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Yes                    #刷新授权

7、MySQL主从复制配置

主库上创建主从复制用户

master

[root@mysql_master /]# mysql -u root -p
Enter password:         #输入刚才使用加固时修改后的密码 
#创建专属在备库上能够登录的用户,账户密码复杂度需要满足策略
mysql> create user 'db_repl'@'192.168.31.216' identified with mysql_native_password by '1qaz!QAZ';
Query OK, 0 rows affected (0.00 sec)
#授权改用户为slave
mysql> grant replication slave on *.* to 'db_repl'@'192.168.31.216';
Query OK, 0 rows affected (0.00 sec)
#刷新授权
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#获取主节点当前binary log文件名称及位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1496 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

备库设置主节点参数

slave

[root@mysql_slave /]# mysql -u root -p
Enter password: 
mysql> change master to
    -> master_host='192.168.31.215',
    -> master_port=8809,
    -> master_user='db_repl',
    -> master_password='1qaz!QAZ',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=1496;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
#参数解释：
master_host：指定主节点[IP/主机名/域名]
maser_port：指定主节点mysql端口
master_user：指定复制用户
master_password：指定用户密码
master_log_file：指定主库上的 binlog 日志名称
master_log_pos：指定主库上 postion 值

备用开启同步

slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

查看同步状态

8、测试主从复制

在Master主库上进行创建，插入操作

[root@mysql_master /]# mysql -uroot -p
Enter password: 
mysql> create database crmdb;
Query OK, 1 row affected (0.00 sec)

备库登录查看

[root@mysql_slave /]# mysql -u root -p
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| crmdb              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)