RPM方式部署MySQL8主从复制

释放双眼,带上耳机,听听看~!

环境准备

IP地址 系统版本 MySQL版本 硬件配置 主机名称
192.168.31.215 CentOS Linux release 7.6.1810 MySQL.8.0.18 2c/4G/50G mysql_master
192.168.31.216 CentOS Linux release 7.6.1810 MySQL.8.0.18 2c/4G/50G mysql_slave

卸载老的数据库

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

部署准备

使用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

安装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

设置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_unicode_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_unicode_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'

建立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

[root@mysql_slave /]# cat /usr/local/mysql/log/mysql_error.log | grep password
2019-11-12T15:44:15.502050+08:00 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: _t&OkQ4q:Z/V

数据库加固及使用SSL连接

#使用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                    #刷新授权

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)

查看同步状态

测试主从复制

在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)

人已赞赏
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧