Red Hat(红帽)系统搭建MySQL主主集群详细操作

猴子不知道啊

已于 2024-07-27 13:53:22 修改

阅读量218

点赞数 8

文章标签： mysql linux 数据库架构

于 2024-07-27 12:17:54 首次发布

本文链接：https://blog.csdn.net/qq_50983123/article/details/140733436

版权

环境搭建

本次采用VirtualBox + vagrant构建两台Red hat (红帽)虚拟机。

安装虚拟机

首先安装vagrant:2.2.18 和 virtualBox:6.1.26，安装教程自行搜索

#vagrant 安装红帽虚拟机

#新建两个文件夹分别存放两个虚拟机

#分别在两个目录下打开cmd初始化VagrantFile
vagrant init generic/rhel7

查看本机内网IP 给虚拟机分配IP要和本机内网IP保持再同一个网段这样虚拟机之间才能通信

#修改固定ip 打开Vagrantfile修改下面的配置  
#注意：要和本机保持在一个ip地址段内 两台虚拟机的ip要不一样 我的分别是10和11
config.vm.network "private_network", ip: "192.168.56.10"

#打开cmd安装上面的file
vagrant up

#进入系统  vagrant用户
vagrant ssh 

#切换root用户
sudo -i

#修改root密码
passwd root

#root密码
wwwwssss

#修改 /etc/ssh/sshd_config 文件 允许ssh链接
PermitRootLogin yes
PasswordAuthentication yes
#加载配置
systemctl reload sshd

#开启ssh链接之后就可以用ssh链接工具操作了，还方便点

下载mysql安装包

下载链接：https://downloads.mysql.com/archives/community/

安装mysql

#新建目录
mkdir /data/mysql
mkdir /data/mysqlload

#将下载好的安装包上传mysqlload并解压
tar xvf mysql-8.0.17-1.el7.x86_64.rpm-bundle.tar

#安装MySQL
yum install mysql-community*.rpm

#如果上面的命令不行就用这个命令安装，不检测依赖强制安装
#yum install mysql-community*.rpm --skip-broken

#如果有四个包被跳过  client devel test server  单独安装以下两个包，test和devel不安装不影响使用
#yum install mysql-community-client-8.0.17-1.el7.x86_64.rpm 
#yum install mysql-community-server-8.0.17-1.el7.x86_64.rpm 

#创建目录
mkdir -pv /data/mysql/{mysql_bin,mysql_data,mysql_logs}
chown -R mysql.mysql /data/mysql

#编辑my.cnf文件
vim /etc/my.cnf

==========文件内容开始========
[client]
socket = /data/mysql/mysql_data/mysql.sock

[mysqld]
skip-name-resolve
default-time-zone = '+8:00'
log_timestamps = system
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4'

#自己定义serverid 两台不要一样
server-id = 96
auto_increment_increment = 2
auto_increment_offset = 1

datadir = /data/mysql/mysql_data
socket = /data/mysql/mysql_data/mysql.sock
log-error = /data/mysql/mysql_logs/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

#下面的一些配置可以根据自己的内存大小修改
binlog_format = MIXED
log-bin = /data/mysql/mysql_bin/mysql-bin
binlog_expire_logs_seconds = 2592000
max_binlog_size = 512M
binlog_cache_size = 64M
max_binlog_cache_size = 1G
back_log = 500
sync_binlog = 0

max_connections = 2000
max_connect_errors = 20
max_allowed_packet = 256M
max_heap_table_size = 1G
tmp_table_size = 1G
wait_timeout = 3600
interactive_timeout = 7200

read_buffer_size = 4M
read_rnd_buffer_size = 8M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 64

default-storage-engine = INNODB
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_data_file_path = ibdata1:1G;ibdata2:512M:autoextend

innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 1
innodb_max_dirty_pages_pct = 25
innodb_lock_wait_timeout = 120
innodb_sort_buffer_size = 4M

innodb_log_file_size = 4G
innodb_log_buffer_size = 256M
innodb_log_files_in_group = 2
innodb_change_buffer_max_size = 50
innodb_log_write_ahead_size = 4096
innodb_fsync_threshold = 15M
innodb_io_capacity = 5000
max_prepared_stmt_count = 100000

[mysqldump]
quick
max_allowed_packet = 256M

==========文件内容结束========

#另外一台机器需要修改的地方
server-id = 95
auto_increment_increment = 2
auto_increment_offset = 2


#启动mysql
systemctl enable mysqld
systemctl start mysqld

#如果启动的时候报错
#查看mysql的状态systemctl status mysqld 报以下错误则需要关闭Linux的selinux防火墙
#并重新设置一下文件夹的权限chown -R mysql.mysql /data/mysql
#[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.35) starti...cess 3304
#[Warning] [MY-010091] [Server] Can't create test file /data/mysql/mys...ower-test
#[Warning] [MY-010159] [Server] Setting lower_case_table_names=2 becau...sensitive
#[ERROR] [MY-010187] [Server] Could not open file '/data/mysql/mysql_l...on denied
#[ERROR] [MY-010119] [Server] Aborting
#[System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (my...er - GPL.

#临时关闭防火墙 SELinux惹的祸
#setenforce 0 


#查看端口是否开放
iptables -L -n | grep 3306

#开放端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

#查看默认密码
cat /data/mysql/mysql_logs/mysqld.log | grep password

#进入
mysql -p 
==========mysql内操作开始==============
#先修改密码
alter user 'root'@'localhost' identified by 'Langyu@123' password expire never;

#修改host
use mysql;
update user set Host='%' where User='root';
flush privileges;
alter user 'root'@'%' identified by 'root@123' password expire never;
alter user 'root'@'%' identified with mysql_native_password by 'root@123';
flush privileges;

#两台服务器上创建复制用户
Create user 'repluser'@'192.168.56.%' identified with mysql_native_password by 'root@1228';
grant replication slave on *.* to 'repluser'@'192.168.56.%';
flush privileges;

#两台服务器分别查看binlog文件以及位置
show master status;


---10上执行---
change master to
master_host='192.168.56.11',  --指向11的地址
master_user='repluser',   
master_password='root@1228', 
master_log_file='mysql-bin.000002', --11的log文件
master_log_pos=2567;  --log文件的位置


--11上执行--

change master to
master_host='192.168.56.10',
master_user='repluser',
master_password='root@1228',
master_log_file='mysql-bin.000005',
master_log_pos=62076221;


#两台配置完成之后启动slave同步进程
start slave;

#两台分别检查slave状态
show slave status\G
==========mysql内操作结束==============

#当以下两个状态都成功表示ok
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes

#可以在一台执行数据库文件查看两台数据是否同步
mysql -p </home/root/sms_ly.sql 
#查看两个数据库的数据是否一致

#至此数据库主主配置完成