NWNU-Sun | 技术沉思录

代码是诗，bug是谜
   ::  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
  77 随笔 :: 49 文章 :: 6 评论 :: 40763 阅读
Mysql 主从、MHA、Mycat 搭建流程

一、搭建Mysql 主从架构

i.部署mysql

下载安装包并解压

cd /usr/local/src
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
tar -zxvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql-8.0.20

修改配置文件 my.cnf

[mysql]
# 默认字符集
default-character-set=utf8mb4
[client]
port       = 3306
socket     = /usr/local/src/mysql-8.0.20/mysql.sock

[mysqld]
port       = 3306
server-id  = 1
gtid_mode=ON                   # 开启GTID模式
enforce_gtid_consistency=on    # 启用全局事务标识（GTID）一致性检查
user       = mysql
socket     = /usr/local/src/mysql-8.0.20/mysql.sock
basedir    = /usr/local/src/mysql-8.0.20 # 安装目录
datadir    = /usr/local/src/mysql-8.0.20/data # 数据存放目录
log-bin    = /usr/local/src/mysql-8.0.20/data/mysql-bin  # 开启binlog
innodb_data_home_dir      =/usr/local/src/mysql-8.0.20/data
innodb_log_group_home_dir =/usr/local/src/mysql-8.0.20/logs
log_slave_updates=1 #服务器在执行收到的来自主服务器的更改时，会将这些更改记录到自己的二进制日志中
binlog_format=row
skip_slave_start=1
#default_authentication_plugin=mysql_native_password #使用旧验证
#日志及进程数据的存放目录
log-error =/usr/local/src/mysql-8.0.20/logs/mysql.log
pid-file  =/usr/local/src/mysql-8.0.20/mysql.pid
# 服务端使用的字符集默认为8比特编码
character-set-server=utf8mb4
lower_case_table_names=1
autocommit =1
 
 ##################以上要修改的########################
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
  
#query_cache_size = 128M
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
   
binlog_format=mixed
    
binlog_expire_logs_seconds =864000
    
# 创建新表时将使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED
      
[mysqldump]
quick
max_allowed_packet = 16M
       
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
        
[mysqlhotcopy]
interactive-timeout

创建mysql用户并授权

groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql /usr/local/src/mysql-8.0.20
chmod -R 750 /usr/local/src/mysql-8.0.20/data

初始化

/usr/local/src/mysql-8.0.20/bin/mysqld \
--defaults-file=/usr/local/src/mysql-8.0.20/my.cnf \
--basedir=/usr/local/src/mysql-8.0.20 \
--datadir=/usr/local/src/mysql-8.0.20/data \
--user=mysql --initialize-insecure

启动

/usr/local/src/mysql-8.0.20/bin/mysqld_safe   \
--defaults-file=/usr/local/src/mysql-8.0.20/my.cnf \
--socket=/usr/local/src/mysql-8.0.20/mysql.sock  \
--user=mysql &

修改密码，设置远程登录

/usr/local/src/mysql-8.0.20/bin/mysql -u root --skip-password --socket=/usr/local/src/mysql-8.0.20/mysql.sock <<EOF
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
use mysql;
update user set user.Host='%'where user.User='root';
flush privileges;
quit
EOF

登陆测试

/usr/local/src/mysql-8.0.20/bin/mysql -u root  -p --socket=/usr/local/src/mysql-8.0.20/mysql.sock

ii.主从设置

主库设置

主服务器登录操作

grant system_user on . to 'root'; # 8版本授权时遇到问题

mysql 8貌似需要先创建用户才能权限操作

---主从配置---
CREATE USER 'asyncuser'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
grant replication slave on *.* to 'asyncuser'@'%'; 
flush privileges; 
GRANT ALL PRIVILEGES ON *.* TO 'asyncuser'@'%';
---解决Mha切换时主机名无法识别---
CREATE USER 'asyncuser'@'master' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'asyncuser'@'master';
CREATE USER 'asyncuser'@'slave1' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'asyncuser'@'slave1';
CREATE USER 'asyncuser'@'slave2' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'asyncuser'@'slave2';
FLUSH PRIVILEGES;

重启，查看master状态

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |     4237 |              |                  | 4b002159-e0e4-11ee-8594-fa163e433cc4:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

记录一下 File、Position值，从库配置需要

从库设置

登陆服务器，停止slave

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

配置主从（从机）

CHANGE MASTER TO 
  MASTER_HOST='192.168.2.250', 
  MASTER_USER='asyncuser', 
  MASTER_PASSWORD='123456', 
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=4237;

CHANGE MASTER TO MASTER_AUTO_POSITION = 0; # 如果执行上述报错

启动master

start slave

检查服务器状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.250
                  Master_User: asyncuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 363
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 491  # 主库
        Relay_Master_Log_File: mysql-bin.000004 # 主库
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

当 Slave_IO_Running 和Slave_SQL_Running 同为yes说明连接成功，如果出现 Slave_IO_Running: Connectiing，需要检查是否关闭主库节点的防火墙

set global read_only=1;		#临时生效，永久需修改my.cnf

从机IO Running报错

https://www.cnblogs.com/abclife/p/16651489.html

iii. 配置Mysql半同步复制

在主库加载插件semisync_master.so，从库加载插件semisync_slave.so

# 主库
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)
# 从库
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)

启用半同步复制

# 主库
set global rpl_semi_sync_master_enabled=1;
# 从库
set global rpl_semi_sync_slave_enabled=1;

将半同步插件配置持久化到配置文件中

my.cnf 追加配置

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled=1
rpl-semi-sync-slave-enabled=1"

重启从库上的IO线程，否则仍然为异步复制模式，查询状态及重启命令如下

# 查看从库状态
show status like 'Rpl_semi_sync_slave_status';
#停止、启动从库IO线程
stop/start slave io_thread;

iV. 扩展从机

背景

业务量无法支撑，需要扩展从机，有哪些方式？如何保证不停止Mysql服务的情况下添加从机

思路

添加从实例，在从实例远程备份并恢复出一个实例，与主库建立复制。

测试方案

MysqlDump

https://mp.weixin.qq.com/s/vMTcNvdIE0gRDC3RKt-MuA

主库备份

./mysqldump   -uroot -p2524354300  --socket=../mysqld.sock  --single-transaction --set-gtid-purged=OFF -A > backup/fullbackup_$(date +%F_%T).sql

复制主库备份数据到从库，执行从库导入

/usr/local/src/mysql-8.0.20/bin/mysql -uroot -p  -uroot p123456 --socket=/usr/local/src/mysql-8.0.20/mysql.sock < fullback.sql

继续执行从库剩余步骤

二、搭建MHA

i.安装perl环境

安装依赖

yum -y install gcc cpan  # 其中cpan是用来编译perl的

下载perl安装包

wget http://www.cpan.org/src/5.0/perl-5.16.1.tar.gz
tar  -zxvf perl-5.16.1.tar.gz
cd perl-5.16.1

编译

./Configure -des -Dprefix=/usr/local/perl
make  && make install

检查版本

perl -v

ii.安装MHA

安装相关依赖

yum install epel-release --nogpgcheck -y \
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN

执行完后perl环境已经安装，执行perl -V检查版本

下载安装包

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz

编译node 、manager

perl Makefile.PL
make && make install

添加配置文件 /etc/master/app1.cnf

[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1.log
master_binlog_dir=/usr/local/src/mysql-8.0.20/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change

ssh_user=root

user=root
password=123456

ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=asyncuser
secondary_check_script=/usr/local/bin/masterha_secondary_check  -s slave1 -s slave2
shutdown_script=""

[server1]
hostname=master
ssh_port=22
candidate_master=1

[server2]
candidate_master=1
check_repl_delay=0
hostname=slave2
port=3306

[server3]
hostname=slave1
port=3306

设置漂移VIP 脚本

#!/usr/bin/env perl

use strict;
use warnings FATAL => 'all';
use Getopt::Long;

my (
    $command, $ssh_user, $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.2.222';   # 设置Virtual IP
my $gateway = '192.168.2.254';   # 网关Gateway IP
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";

GetOptions(
    'command=s' => \$command,
    'ssh_user=s' => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s' => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'new_master_host=s' => \$new_master_host,
    'new_master_ip=s' => \$new_master_ip,
    'new_master_port=i' => \$new_master_port,
);

exit &main();

sub main {
    print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
    
    if ($command eq "stop" || $command eq "stopssh") {
        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
        # If you manage master ip address at global catalog database,
        # invalidate orig_master_ip here.
        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        
        exit $exit_code;
    }
    elsif ($command eq "start") {
        # all arguments are passed.
        # If you manage master ip address at global catalog database,
        # activate new_master_ip here.
        # You can also grant write access (create user, set read_only=0, etc) here.
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
            $exit_code = 0;
        };
        
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        
        exit $exit_code;
    }
    elsif ($command eq "status") {
        print "Checking the Status of the script.. OK \n";
        `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

# A simple system call that enable the VIP on the new master
sub start_vip {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}

# A simple system call that disable the VIP on the old_master
sub stop_vip {
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
    print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

注释：Bareword "FIXME_xxx" not allowed while "strict subs" i line 100

执行互信检查

masterha_check_ssh --conf=/etc/masterha/app1.cnf

执行复制环境检查

masterha_check_repl --conf=/etc/masterha/app1.cnf

mha manager检查状态

masterha_check_status --conf=/etc/masterha/app1.cnf

启动 MHA manager

nohup  masterha_manager --conf=/etc/masterha/app1.cnf  --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

测试工具

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=10000 --threads=10 --time=120 --report-interval=10 --db-driver=mysql prepare

关闭Sql

./mysqladmin -u root -p shutdown --socket=../mysql.sock

三、Mycat读写分离

架构图

image-20240315151010982

前提上述一主双从搭建完整

下载安装包

wget https://github.com/MyCATApache/Mycat-Server/releases/download/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
tar -zxvf Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

配置 server.xml

<user name="mycat" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">TESTDB</property>
		<property name="defaultSchema">TESTDB</property>
</user>

配置schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100"  randomDataNode="dn1" dataNode="dn1">
	</schema>
	<dataNode name="dn1" dataHost="host1" database="testdb" />
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="192.168.2.250:3306" user="root" password="123456">
        <readHost host="hostS1" url="192.168.2.190:3306" user="root" password="123456" />
        <readHost host="hostS2" url="192.168.2.198:3306" user="root" password="123456" />
		</writeHost>
	</dataHost>
</mycat:schema>

启动服务

  • 控制台启动 ./mycat conosole
  • 后台启动 ./mycat start

测试，使用mysql客户端或者navicate 连接，用户名 mycat 密码 123456 端口 8066

参考

第98讲：MHA高可用集群VIP地址配置与漂移实践_mha架构 如何实现vip的飘逸-CSDN博客

记一次部署系列：Mysql高可用之MHA - NorthFeng - 博客园 (cnblogs.com)

https://blog.csdn.net/a_b_e_l_/article/details/127076562

posted on   匿名者nwnu  阅读(186)  评论(0)  编辑  收藏  举报
编辑推荐：
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· AI与.NET技术实操系列（六）：基于图像分类模型对图像进行分类
点击右上角即可分享
微信分享提示
SQL AI 助手