前言前两天看见网上有人抱怨mysql备份有点麻烦，所以我这两天写了个自动化备份mysql的脚本脚本#!/usr/bin/bash StepShow () { #tipEcho 第几步 主题 echo -e "\n------------------------------------------" echo -e "\033[34;3mstep $1 : $2\033[0m" echo -e "------------------------------------------\n" } WarnShow () { echo -e "\n\033[33;3m Warn: $1 \033[0m" } ErrorShow () { echo -e "\n\033[31;43;4m ERROR: $1 \033[0m" } TipShow () { echo -e "\n\033[32;3m TIP: $1 \033[0m" } TestMysqlRunnung () { systemctl start mysqld &> /dev/null # 测试mysql是否运行 if [ $( ps -aux|grep 'mysqld '|wc -l) -gt 1 ]; then # 进一步检查 mysql -u backup -p"${password}" -e "SELECT 1" &>/dev/null if [ $? -eq 0 ];then echo 'mysql正在运行' else echo -e '\033[31m mysql没有运行 \033[0m' fi else echo -e '\033[31m mysql没有运行 \033[0m' fi } TestXtrabackupExist () { # 测试是否安装Xtrabackup xtrabackup --version &>/dev/null if [ $? -eq 0 ]; then echo 'xtrabackup已安装' else echo 'xtrabackup未安装' exit 2 fi } CheckBackupDir () { # 创建备份目录 echo '注意会清除目录内同名文件' if [ ! -d "${backupDir}" ];then rm ${backupDir} -f mkdir ${backupDir} fi } ChooseStrategy(){ # 创建策略文件用于永久存储策略 if [ $(ls ${backupDir}|grep strategy|wc -l) -ne 1 ];then touch ${backupDir}/strategy fi # 在strategy为空时才进行策略选择，否则会直接读取 while [ $(cat ${backupDir}/strategy|wc -l) -eq 0 ];do echo -e "1.增量备份\n2.差异备份" read -p '选择:' choose if [ ${choose} -eq 1 ];then echo -e 'increment\n###' > ${backupDir}/strategy elif [ ${choose} -eq 2 ];then echo -e 'difference\n###' > ${backupDir}/strategy else echo '错误输入，重新输入' fi done echo '创建成功' } CheckLatest(){ latestBackup=$(cat ${backupDir}/strategy|tail -1) if [ ${latestBackup} == '###' ];then status=1 # 需要全量备份，status为1 echo '没有备份，即将完整备份' else # 已经全量备份就要进行差异或增量备份 strategy=$(cat ${backupDir}/strategy|head -1) [ ${strategy} == 'difference' ] && status=2 # 差异备份 [ ${strategy} == 'increment' ] && status=3 # 增量备份 echo "最新的备份为${latestBackup}" fi } Backup() { oldtarget=${latestBackup} # oldtarget--inc3 newtarget--inc4 # 初始化newtarget if [ ${oldtarget} == '###' ];then newtarget='base' elif [ ${oldtarget} == 'base' ];then newtarget="$(cat ${backupDir}/strategy|head -1|cut -c1-3)1" else newtarget="$(echo ${oldtarget}|cut -c1-3)$(($(echo ${oldtarget}|cut -c4)+1))" fi echo "${oldtarget}-----${newtarget}" if [ ${status} -eq 1 ];then # 没有完整备份 xtrabackup --backup --user=${user} --password="${password}" --target-dir=${backupDir}/${newtarget} &>> ${backupDir}/log elif [ ${status} -eq 2 ]; then # 需要差异备份 oldtarget='base' # 差异备份的 oldtarget固定 xtrabackup --backup --user=${user} --password="${password}" --target-dir=${backupDir}/${newtarget} --incremental-basedir=${backupDir}/${oldtarget} &>> ${backupDir}/log elif [ ${status} -eq 3 ]; then # 需要增量备份 xtrabackup --backup --user=${user} --password="${password}" --target-dir=${backupDir}/${newtarget} --incremental-basedir=${backupDir}/${oldtarget} &>> ${backupDir}/log else echo -e "错误的状态:${status}" exit 1 fi [ $? -eq 0 ] && TipShow "备份成功" || ErrorShow "备份失败" echo ${newtarget} >> ${backupDir}/strategy } main() { declare -A strategytofile status=0 # 状态，0待定，1为需要完整备份，2为需要差异备份，3为需要增量备份 strategytofile['increment']='inc' strategytofile['difference']='dif' read -p '请输入用户：' password user=${1:-'backup'} read -p '请输入密码：' password password=${password:-'StrongPass!123'} read -p '请输入目录：' backupDir backupDir=${1:-'/backup/mysql'} backupDir=${backupDir%/} # 此方法有bug，不是按顺序的，使用固定的文件内容替代不可控的排序 # latestBackup=$(find ${backupDir} -maxdepth 1 -type d |tail -1|xargs basename) StepShow 1 '测试mysql' TestMysqlRunnung StepShow 2 '测试Xtrabackup' TestXtrabackupExist StepShow 3 '检查备份目录是否存在' CheckBackupDir StepShow 4 '检查备份策略' ChooseStrategy StepShow 5 '检查最新的备份' CheckLatest StepShow 6 '备份' Backup } main ########################### # README # 该脚本用于自动化的完成mysql的增量/差异备份 # - 具有一定的报错检查机制 # - 完善的日志内容 # - 一次配置，永久记住 ########################### # 2025.9.2 70%，在没有base情况下没有正常进行完整备份 # 2025.9.3 完成，正常运行，自动备份 ########################### # 升级方向 # 1.自解压安装xtrabackup # 2.提供更高程度的自定义配置 # 3.压缩/加密备份 # 4.与自动恢复的联动
前言复习MHA,通过两种方式实现MAH的VIP漂移|NODE|IP|ROLE||-|-|-||T1|192.168.179.191|master,MHA-node||T2|192.168.179.192|slave,MHA-manager,binlog-server||T3|192.168.179.193|slave,MHA-node|配置了binlog-server,减轻主库压力,保持日志的长时间保持(用于主从切换后复制)MHA自带脚本方式这个自带脚本需要下载tgz包查看,位于mha4mysql-manager-0.58/samples/scripts,rpm包(yum安装)没有脚本模板请先完成mysql一主二从的搭建,完成mha-manage与node的安装1.添加故障切换脚本vim /app/tools/masterha/app1.cnf #故障切换VIP脚本 master_ip_failover_script= /app/tools/masterha/master_ip_failover #在线切换VIP脚本 master_ip_online_change_script= /app/tools/masterha/master_ip_online_changeMHA提供了故障切换脚本模板master_ip_failover2.修改脚本修改master_ip_failovercp /app/tools/masterha/app1/master_ip_failover.pl \ /app/tools/masterha/app1/master_ip_failovervim /app/tools/masterha/master_ip_failover #!/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 ); #添加或修改如下内容 #指定VIP my $vip = '192.168.179.200/24'; my $key = '0'; #这里可能需要修改网卡 my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig ens33:$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" ) { 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" ) { 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"; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } sub stop_vip() { return 0 unless ($ssh_user); `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"; }修改master_ip_online_change#太长了,其他略,基本与master_ip_failover修改的内容相同 my $vip = '192.168.179.200/24'; my $key = '0'; my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip;/usr/sbin/arping -q -A -c 1 -I ens33 $vip"; my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";3.添加权限#修改权限 chmod a+x /app/tools/masterha/master_ip_failover4.master手动加VIP#master节点执行 #这里的0是key,192.168.179.200/24是VIP ifconfig ens33:0 192.168.179.200/24 ip a |grep ens335.测试#测试ssh,失败请参考MHA实践(1) masterha_check_ssh --conf=/app/tools/masterha/app1.cnf #测试mysql账户,失败请参考MHA实践(1) masterha_check_repl --conf=/app/tools/masterha/app1.cnf 6.重启mhanohup masterha_manager --conf=/app/tools/masterha/app1.cnf \ &>/app/tools/masterha/app1/manager.log &7.停止旧mastersystemctl stop mysqld8.检查新master的VIPip a |grep ens33其他在线切换#相较于没有配置VIP切换脚本时,执行该命令会同时切换VIP masterha_master_switch --master_state=alive \ --conf=/app/tools/masterha/app1.cnf --new_master_host=192.168.179.193 \ --orig_master_is_new_slave --running_updates_limit=1000 --interactive=0错误脑裂发现出现脑裂现象,MHA并没有把原来的旧master的VIP去除导致切换主从后有两个VIP原因经过大量测试与日志检测,发现是脚本问题,删除mysql时的grep过滤范围过大,将mhamysql也删除了,虽然后面我发现了这个问题,又安装了一遍,但是少安装了T3的(新master)上的mha-node节点虽然没有对脚本仔细研究,不知道为什么少安装新master节点上的node会导致已经安装node的旧master没有删除VIP,甚至mha检测时也没有报错(猜测这个工作可能是node来做的),但是重置后为每个节点安装上node就不会导致脑裂解决为每个节点安装mha-nodeKeepalived方式需要说明的是,使用Keepalived也需要MHA的脚本,上一个方式是通过ifconfig添加VIP的,现在通过keepalived添加VIP请先完成mysql一主二从的搭建,完成mha-manage与node的安装1.配置keepalivedyum install -y popt-devel gcc mkdir -p /app/tools/keepalived wget https://www.keepalived.org/software/keepalived-2.3.1.tar.gz tar zxvf keepalived-2.3.1.tar.gz cd keepalived-2.3.1 ./configure --prefix=/app/tools/keepalived/ make make install #安装路径为/app/tools/keepalived/经测试,centos7.9不支持通过该方式安装2.3.2版本(可能要安装其他依赖)2.修改配置文件keepalived(每个节点都要)vim /app/tools/keepalived/keepalived.conf​ global_defs { #本节点标识 router_id T1 } ​vrrp_instance vrrptest { #都设为BACKUP且开启非抢占模式防止mysql服务波动 state BACKUP nopreempt interface ens33 #虚拟路由的标志,一个集群的必须相同 virtual_router_id 51 #优先级 priority 150 #服务器之间的存活检查时间 advert_int 1 authentication { #认证类型 auth_type PASS #认证密码,一个集群的必须相同 auth_pass 1111 } #VIP virtual_ipaddress { 192.168.179.200 } } #不配置vrrp_script,因为检测的工作由MHA做,停止本机的VIP也由MHA完成建议配置为都是BACKUP的非抢占模式,如果出现master那么非抢占模式就会失效(即使添加了nopreempt非抢占),所以必须都是BACKUP,优先级建议相同当然由于是由MHA启动keepalived,同一时间只会有一个keepalived启动,所以即使配置了抢占或者配置了MASTER也不会影响,但是为了防止MHA出问题导致出现脑裂或者VIP漂错位置,还是建议按照非抢占配置keepalived3.修改master_ip_failovermy $vip = '192.168.192.200/24'; my $ssh_start_vip ="systemctl start keepalived"; my $ssh_stop_vip ="systemctl stop keepalived";4.修改修改master_ip_online_changemy $vip = '192.168.192.200/24'; my $ssh_start_vip ="systemctl start keepalived"; my $ssh_stop_vip ="systemctl stop keepalived";5.添加权限#修改权限 chmod a+x /app/tools/masterha/master_ip_failover6.master手动加VIP注意,只要master开启(如果当前VIP在T1,关闭master后,都开启keepalived后因为设置优先级相同,VIP随机漂移,不一定飘到新的master上,所以必须由MHA开启)#master节点执行 systemctl start keepalived ip a |grep ens337.测试masterha_check_ssh --conf=/app/tools/masterha/app1.cnf masterha_check_repl --conf=/app/tools/masterha/app1.cnf 8.重启mhanohup masterha_manager --conf=/app/tools/masterha/app1.cnf &>/app/tools/masterha/app1/manager.log &9.停止旧mastersystemctl stop mysqld10.检查新master的VIPip a |grep ens33错误1.配置错误错误keepalive启动后,没有VIP,即使单点启动也没有原因经过仔细检查,发现是配置文件中少了个},这个错误keepalived竟然不报错解决修改配置文件
前言复习MHA,搭建binlog-server并测试|NODE|IP|ROLE||-|-|-||T1|192.168.179.191|master,MHA-node||T2|192.168.179.192|slave,MHA-manager,binlog-server||T3|192.168.179.193|slave,MHA-node|配置了binlog-server,减轻主库压力,保持日志的长时间保持(用于主从切换后复制)实践在某节点创建存放binlog的目录(最好是mysql从节点上,否则可能有问题,未验证)#mysql从节点192.168.179.192 mkdir -p /app/data/mysqlbinlog远程备份binlog#指定备份binlog的目标master节点(一般就直接从mysql-bin.000001开始复制) nohup mysqlbinlog --no-defaults --read-from-remote-server --raw --result-file=/app/data/mysqlbinlog/ --host=192.168.179.191 --port=3306 --user=monmha --password='!@#$%^&[]{O2p' --stop-never mysql-bin.000001 --server-id=9999 &>/dev/null & #--read-from-remote-server：从远程服务器直接读取binlog #--raw：输出原始binlog内容 #--result-file：指定输出目录(不是文件名) #经测试,--result-file准确说是文件名前缀(如/data/backcp-->backcpmysql-bin.000001)或者是路径,一定要在末尾加/(如/data/backcp/-->backcp/mysql-bin.000001) #--stop-never:，表示持续监听新事件要在--result-file指定的目录后加/,不可以使用任何文件名,因为MHA只能识别mysql-bin.000001类型的binlog文件,其他的文件名(例如backcpmysql-bin.000001)不认修改mha配置文件#由于本次将一个slave设置为binlog server,在其server中添加以下参数防止其变为master,防止该节点成为master增加宕机概率,导致mha无法获取备份的binlog no_master=1 #在最后添加 [binlog1] hostname=192.168.179.192 master_binlog_dir=/app/data/mysqlbinlog检验#masterha_check_repl会检查 masterha_check_repl --conf=/app/tools/masterha/app1.cnf 启动mhanohup masterha_manager --conf=/app/tools/masterha/app1.cnf &>/app/tools/masterha/app1/manager.log &测试1.关闭从节点IO线程stop slave io_thread; show slave status\G;2.主节点写并更新binlog#主节点写操作,使用flush logs;快速更新日志 create database test; use test; flush logs; create table dept ( deptno int unsigned auto_increment primary key comment '部门编号', dname varchar(15) comment '部门名称' , loc varchar(50) comment '部门所在位置' )engine = innodb default charset=utf8mb4 comment='员工部门表'; flush logs; insert into dept values (10,'ACCOUNTING','NEW YORK'); flush logs; insert into dept values (20,'RESEARCH','DALLAS'); flush logs; insert into dept values (30,'SALES','CHICAGO'); flush logs; insert into dept values (40,'OPERATIONS','BOSTON'); flush logs; show master status\G; 此时slave节点没有变化,因为关闭的IO线程,不会再复制master节点的binlog 3.关闭master节点,模拟宕机systemctl stop mysqld关闭master节点后,经过多次测试,结果如下 关闭了mysqld,会导致mysqlbinlog后台退出,某种程度上这个mysqlbinlog就是一次性的,但是结合mha错误修改的脚本可以实现即使主从切换也可继续备份binlog MHA检测到master节点宕机,立即进行主从切换,因为再T2节点设置了no_master,所以会将T3作为master节点(只有T1,T2,T3节点) MHA设置新master与新slave时会自动设置只读和解除只读,所以只需要按照一般配置从库 虽然日志状态还是binlog1,但是实际上已经完成了原本binlog(master)的写入,这个地方我一直以为没写入,导致重复测试花了不少时间 如果没有该binlog-server,MHA检测到启用GTID模式后并不会去复制master上的binlog,导致IO关闭后master写入的内容没有复制,数据丢失错误mha复制检测报错错误原因因为指定的binlog目录下没有binlog文件binlog文件不是mysql-bin.000001类型的文件名解决修改mysqlbinlog的复制保存路径,不要加文件名,末尾必须是/mysqlbinlog冲突报错错误原因因为我使用xshell的输出到多个终端导致2个slave同时运行了mysqlbinlog命令并且指定为同一个server-id导致的解决避免使用已经存在的server-id
前言复习MHA,练习MHA工具,包括测试,master在线切换主机,master离线切换主机|NODE|IP|ROLE||-|-|-||T1|192.168.179.191|master,MHA-node||T2|192.168.179.192|slave,MHA-manager||T3|192.168.179.193|slave,MHA-node|MHA测试工具检查 MySQL 复制状况 一般在启动MHA之前检查masterha_check_repl --conf=/app/tools/masterha/app1.cnf 检查 MHA 的 SSH 配置状况masterha_check_ssh --conf=/app/tools/masterha/app1.cnf检测当前 MHA 运行状态masterha_check_status --conf=/app/tools/masterha/app1.cnfMHA自动转移(主从切换)流程MHA检查到master节点宕机MHA检查配置文件MHA关闭master节点以防万一新主节点的恢复此步包括选择从slave中按数据最接近master的原则选择新的master(如果某slave配置了candidate_master=1,则必为主节点)新slave节点change master到新的master,然后进行数据差异检查,对齐可以发现其中并没有配置任何用户,还是使用app1.cnf中的配置文件,所以使用的复制账号与监控账户跟之前是一样的,所以才需要在每个节点上(包括slave)都进行相同账号的创建和授权如果旧主重启,默认情况下MHA不会自动将其重新加入集群,也就是说可能需要手动切换,或者通过MHA故障切换脚本(这个脚本要自己写)MHA手动切换手动切换的前提时MHA没有启动,否则会触发自动转移检查MHA状态masterha_check_status --conf=<file>检查MHA监控与配置masterha_check_repl --conf=<file>手动切换masterha_master_switch --master_state=dead --conf=/app/tools/masterha/app1.cnf --dead_master_host=192.168.179.191 --dead_master_port=3306 --new_master_host=192.168.179.192 --new_master_port=3306 --ignore_last_failover #dead_master_host请参考配置文件,只能写配置文件中有的host(必须与配置相同,配置中是IP那么就不能写主机名)MHA在线切换:up:在线切换指的是master与slave都正常工作时进行配置恢复检查MHA状态masterha_check_status --conf=<file>检查MHA监控与配置masterha_check_repl --conf=<file>查看参数#查看Seconds_Behind_Master(从服务器当前与主服务器之间的数据同步滞后时间s) #slave节点 show slave status\G; #查看TIME #master节点 show processlist;在线切换主库masterha_master_switch --master_state=alive --conf=/app/tools/masterha/app1.cnf --new_master_host=192.168.179.193 --orig_master_is_new_slave --running_updates_limit=1000 --interactive=0 #--orig_master_is_new_slave 将原master变成新slave #Seconds_Behind_Master值必须小于--running_updates_limit指定的值 #TIME(show processlist;中的TIME最大值)必须小于--running_updates_limit指定的值切换成功
前言复习mysql,搭建MHA架构,并且对MHA架构进行一定测试,标注出一些关键问题|NODE|IP|ROLE||-|-|-||T1|192.168.179.191|master,MHA-node||T2|192.168.179.192|slave,MHA-manager||T3|192.168.179.193|slave,MHA-node|可以将MHA-manager移至一个比较安全的节点,但是slave本身也可以(会配置no_master禁止T2成为master)实践1.配置域名解析cat >> /etc/hosts <<EOF 192.168.179.191 T1 192.168.179.192 T2 192.168.179.193 T3 EOF2.安装3台mysql请参考mysql一键启动脚本3.设置每个节点间的免密登录(每个节点都需要)ssh-keygen for i in 191 192 193;do ssh-copy-id root@192.168.179.$i;done4.配置用户(推荐所有节点,只有主节点也行)所有节点都需要授权是因为主从可能切换,此时原来的从节点就成为主节点,那么同时也需要复制账户但是实际上只需要主节点就行了,因为并没有配置忽略mysql这个database(user表在其中),所以从节点复制时也会复制授权语句#监控用户monmha,监控数据库变化 grant all privileges on *.* to 'monmha'@'T1' identified by '!@#$%^&[]{O2p'; grant all privileges on *.* to 'monmha'@'T2' identified by '!@#$%^&[]{O2p'; grant all privileges on *.* to 'monmha'@'T3' identified by '!@#$%^&[]{O2p'; grant all privileges on *.* to 'monmha'@'192.168.179.*' identified by '!@#$%^&[]{O2p'; #复制用户mha,用于复制数据库 grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'mha'@'192.168.179.*' identified by '1U@1UK6$1UK6%5n5*}\9M1UK69M5n5n'; grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'mha'@'T1' identified by '1U@1UK6$1UK6%5n5*}\9M1UK69M5n5n'; grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'mha'@'T2' identified by '1U@1UK6$1UK6%5n5*}\9M1UK69M5n5n'; grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'mha'@'T3' identified by '1U@1UK6$1UK6%5n5*}\9M1UK69M5n5n'; flush privileges; #192.168.179.*与192.168.179.%基本相同 #在mysql8.0中,创建用户与授权需要分开来,也就是需要两步 #mysql需要对指定NDS进行解析,即使5.修改配置文件vim /etc/my.cnf [mysqld] # ===== 基础配置 ===== # 唯一ID（主从必须不同，如主=1，从=2） server-id = 1 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid # ===== 二进制日志配置 ===== # 所有节点均需配置（MHA要求） log-bin = /var/lib/mysql/mysql-bin binlog_format = ROW expire_logs_days = 7 max_binlog_size = 100M # 主库建议1，从库可设0 sync_binlog = 1 # ===== 中继日志配置 ===== # 所有节点均需配置（MHA要求） relay_log = /var/lib/mysql/relay-bin # 控制sql线程定时清理中继日志 relay_log_purge = ON # 从库是否将其接收到的来自主库的更新操作记录到自己的二进制日志,必须启用（从库提升为主时需记录binlog） log_slave_updates = ON # ===== GTID 配置（可选）===== # 一旦开启GTID,无法再回退,但是便于MHA定位日志断点(无需指定断点) gtid_mode = ON enforce_gtid_consistency = ON # ===== 数据持久化配置 ===== #innodb_flush_log_at_trx_commit 0:每秒刷到磁盘,1:事务提交时刷到文件系统,再刷到磁盘(性能好);2:事务提交时刷到磁盘 # 主库建议1，从库可设2 innodb_flush_log_at_trx_commit = 1 # 根据内存调整（通常占50-70%） innodb_buffer_pool_size = 4G # ===== 主从角色相关配置 ===== #read_only禁止普通用户写,但是SUPER用户仍能正常写 #super_read_only禁止任何用户写,只有复制线程能绕过该限制的 # 主库=OFF，从库=ON read_only = OFF # MHA切换主从时可以修改read_only,但是无法修改super_read_only导致一旦打开,主从切换后的master无法写入 #super_read_only = OFF # ===== 网络与连接 ===== # 允许远程访问 bind-address = 0.0.0.0 port = 3306 # 禁用DNS解析（提升性能） #注意,MHA使用DNS解析的主机名登录,一定不能开启 #skip_name_resolve = ON # 连接数量调优 max_connections = 1000 # ===== 其他MHA要求配置 ===== # 将中继日志信息存储到表中（提高可靠性） #relay_log_info_repository = TABLE #master_info_repository = TABLE # MHA用于节点识别(指定master的IP,可用于主机名解析存在问题时仍能识别) #report_host = 192.168.179.111 systemctl restart mysqld每个节点需要修改server-id,保证每个节点不同主从节点需要修改read_only ,主库可写,从库只读(MHA切换时自动修改read_only)innodb_flush_log_at_trx_commit建议修改经测试,从节点不能开启super_read_only,MHA在主从切换时不会修改这个参数6.实现一主二从两个从节点执行mysql -uroot -p'MyStr0ngP@ssw0rd!' CHANGE MASTER TO MASTER_HOST='T1', MASTER_USER='mha', MASTER_PASSWORD='1U@1UK6$1UK6%5n5*}\9M1UK69M5n5n', MASTER_AUTO_POSITION = 1; STOP SLAVE; START SLAVE; SHOW SLAVE STATUS\G; 保证IO与SQL线程都启动了,一般IO线程出问题是master节点的问题,SQL线程出问题都是slave节点的问题7.测试主从master节点mysql -uroot -p'MyStr0ngP@ssw0rd!' create database test; show databases;slave节点show databases;master节点#删除测试数据库 drop database test;8.下载MHA-node节点并安装(mysql的所有节点)#依赖(每个节点都需要安装) yum install -y perl-DBD-MySQL #下载(rpm版) wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm #本地安装(rpm) yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm9.链接(mysql的所有节点)#将mysql链接到/usr/bin内,mysqlbinlog链接到/usr/local/bin,否则mha报错(但是这个我并没有验证) #由于是rpm安装的,所以mysql已经链接了 ln -s /usr/bin/mysqlbinlog /usr/local/bin/mysqlbinlog10.安装MHA-manage(非master的任意一个节点,此处为T2节点)#安装依赖 yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager #下载(rpm) wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm #提示,安装MHA的manage的前提是该节点已经安装MHA的客户端(node) yum localinstall -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpmmha-master节点上也要安装mha-node11.配置MHA-mastermkdir -p /app/tools/masterha/manager #该文件需要自己创建,mha启动需要 vim /etc/masterha_default.cnf manager_workdir=/app/tools/masterha/manager remote_workdir=/tmp #manager_workdir为mha工作目录 #remote_workdir放置保留的binlog或relaylog mkdir -p /app/tools/masterha/app1vim /app/tools/masterha/app1.cnf [server default] manager_log= /app/tools/masterha/app1/app.log #mysql监控用户和密码(用于) user=monmha password='!@#$%^&[]{O2p' #mysql复制账户和密码 repl_user=mha repl_password='1U@1UK6$1UK6%5n5*}\9M1UK69M5n5n' #可创建专门的用户用于连接,需要配置登录从节点密钥,这里直接使用root ssh_user=root #主库binlog日志位置(master节点上) master_binlog_dir=/var/lib/mysql #工作目录 manager_workdir=/app/tools/masterha/app1 remote_workdir=/tmp/mha ping_interval=3 #故障切换脚本(最后再添加) #master_ip_failover_script= /app/tools/masterha/app1/master_ip_failover #master_ip_online_change_script= /app/tools/masterha/app1/master_ip_online_change #server1为当前master [server1] hostname=192.168.56.191 port=3306 [server2] hostname=192.168.56.192 port=3306 [server3] hostname=192.168.56.193 port=3306 #candidate_master=1 意味着强制指定为备选master如果是源码安装mysql需要指定master_binlog_dir为安装目录下的binlog目录12.检查测试mha#测试ssh连接,不通过就是没有配置免密登录 masterha_check_ssh --conf=/app/tools/masterha/app1.cnf #测试主从复制 masterha_check_repl --conf=/app/tools/masterha/app1.cnf13.启动nohup masterha_manager --conf=/app/tools/masterha/app1.cnf &>/app/tools/masterha/app1/manager.log &14.检查cat /app/tools/masterha/app1/manager.log15.测试#停止master systemctl stop mysqldT2节点切换T3节点为master,T3节点成为master节点错误1.MySQL拒绝连接报错masterha_check_repl --conf=/app/tools/masterha/app1.cnf原因 经多次测试,发现MHA使用的是主机名连接,必须配置使用主机名连接而不是直接使用IP连接解决配置指定主机授权mysql用户(参考步骤4)配置本地解析(参考步骤1)一定要关闭配置中的skip_name_resolve,如果开启,即使配置了指定主机授权MHA也无法登录mysql2.master的GTID模式未开启报错show slave status\G;原因master上的GTID模式的注释没有解除解决修改master节点配置文件,然后重启vim /etc/my.cnf GTID_MODE=ON systemctl restart mysqld临时修改master节点的GTIDSET GLOBAL GTID_MODE=ON;如果重启出现错误(请先排查配置文件问题),那可能需要删库 危险操作,只能在刚开始配的时候使用#删除日志与数据,如果配置文件中被修改,删除的位置也需要修改 rm -rf /var/lib/mysql/* rm -f /var/log/mysqld.log
前言最近在搭建,经过一段时间,对shell有了更深入的了解,解决了原来的mysql登录问题,并且添加了比较简单的密码复杂性检查脚本#/bin/bash #version: v3 #author: YK #update: 安装完毕直接登录,对密码进行检查 #网络测试 ping -c2 223.109.81.112 &>/dev/null #百度IP if [ $? -ne 0 ]; then #$?代表上一个命令执行结果,0为正确 echo "网络不通"; exit 0; else ping -c2 www.baidu.com &>/dev/null if [ $? -ne 0 ]; then echo "DNS有问题"; exit 0; fi fi #实现有颜色的警告(前景色:31红,32绿,33黄,34蓝,背景色:41红....44蓝) echo -e "\033[31m 本脚本会对之前的mysql进行删库,谨慎执行\033[0m" #存放路径 echo -e "mysql的rpm包存放路径(默认为/usr/local/mysql):\n注意,会清空该目录" read rpm_where locate=${rpm_locate:-"/usr/local/mysql"} if [ ! -d ${locate} ]; then #-d 匹配目录是否存在,存在时为true,使用!可以取反 mkdir -p ${locate} fi rm ${locate}/mysql-* -f groupadd mysql &>/dev/null useradd -r -g mysql mysql &>/dev/null chown -R mysql:mysql /usr/local/mysql chmod -R 755 /usr/local/mysql #安装版本 echo -e "安装mysql5.7?\nyes-->5.7\nno -->8.4 " #使用 -e才能解释转义字符,否则类型\n都不会解释 while true; do read -p "请输入 (默认为yes): " version version=${version:-"yes"} case ${version} in [Yy][Ee][Ss]) #注意选项末尾有个')' #此方法无视大小写且简单易实现 is_mysql57=$(ls ${locate}|grep mysql-5.7.44-1.el7.x86_64.rpm-bundle.tar|wc -l ) if [ ${is_mysql57} -eq 0 ]; then echo "正在下载mysql5.7" wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-1.el7.x86_64.rpm-bundle.tar -P ${locate} &>/devnull fi echo "已经下载mysql5.7" break #此break是对于外循环while的,case本身不需要break ;; [Nn][Oo]) is_mysql84=$(ls ${locate}|grep mysql-8.4|grep el7.x86_64.rpm-bundle.tar|wc -l ) if [ ${is_mysql84} -eq 0 ]; then echo "正在下载mysql8.4" wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.4-1.el7.x86_64.rpm-bundle.tar -P ${locate} &>/dev/null #wget的-P可以指定下载的位置 fi echo "已经下载mysql8.4 " break ;; *) echo "请输入yes/no,或使用'ctrl+c'退出" ;; esac done echo "下载完成" echo "mysql密码(默认密码为MyStr0ngP@ssw0rd!):" read secret_n secret_new=${secret_n:-"MyStr0ngP@ssw0rd!"} echo "mysql密码:$secret_new" #密码验证 check_passwd ${secret_new} if [ $? -eq 1 ]; then echo "退出安装" exit 1 elif [ $? -eq 0 ]; then echo "继续安装" fi echo "正在删除旧安装包" rpm -qa|grep mysql* |xargs yum remove -y >&/dev/null rpm -qa|grep mariadb* |xargs yum remove -y >&/dev/null rpm -qa|grep mysql* |xargs rpm -e >&/dev/null rm -rf /var/lib/mysql/* rm -f /var/log/mysqld.log #安装 echo "正在安装" cd $locate yum -y install libaio net-tools openssl-devel perl-Data-Dumper perl-JSON perl-Test-More >&/dev/null tar xf mysql* #导入认证密钥 sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql #以下安装包严格按顺序安装 rpm -ivh ${locate}/mysql-community-client-plugins-*.el7.x86_64.rpm >&/dev/null rpm -ivh ${locate}/mysql-community-common-*.el7.x86_64.rpm >&/dev/null rpm -ivh ${locate}/mysql-community-libs-*.el7.x86_64.rpm >&/dev/null rpm -ivh ${locate}/mysql-community-client-*.el7.x86_64.rpm >&/dev/null rpm -ivh ${locate}/mysql-community-icu-data-files-8.4*.el7.x86_64.rpm >&/dev/null rpm -ivh ${locate}/mysql-community-devel-*.el7.x86_64.rpm >&/dev/null ls mysql-community-client-*.el7.x86_64.rpm |head -1|xargs rpm -ivh >&/dev/null rpm -ivh ${locate}/mysql-community-server-*.el7.x86_64.rpm >&/dev/null #以下包未安装mysql-community-server-debug-8.4.4-1.el7.x86_64.rpm;mysql-community-test-8.4.4-1.el7.x86_64.rpm;mysql-community-debuginfo-8.4.4-1.el7.x86_64.rpm if [ $? -ne 0 ]; then echo "安装失败" exit 0 fi #5.7和8.4都有一部分组件没有安装 #启动 mysql_run=0 echo "正在启动mysql" systemctl daemon-reload while [ "$mysql_run" -ne 1 ] do mysql_run=$(systemctl status mysqld|grep running |wc -l) systemctl enable --now mysqld #如果没有,可以手动写service或者直接使用命令启动 #/usr/local/mysql/bin/mysqld --initialize --user=mysql sleep 10 done echo "mysql已启动" rm ${locate}/mysql-* -f #改密码 secret_old=$(grep 'temporary password' /var/log/mysqld.log|awk '{print $NF}'| tr -d ';') sleep 3 #自动修改(原本以为passwd两边必须有单引号) mysqladmin -u root -p"${secret_old}" password "${secret_new}" echo -e "#--------------------------------\nmysql -uroot -p'${secret_new}'\n#--------------------------------" mysql -uroot -p${secret_new} check_passwd(){ #需要传入password local password=$1 #最小长度 local min_length=8 #是否有大写字母 local has_upper=0 #是否有小写字母 local has_lower=0 #是否有数字 local has_digit=0 #是否有特殊字符 local has_special=0 #特殊字符数组 local special_chars='!@#$%^&*()-_=+[]{}|;:,.<>?~' # 检查最小长度 # ${#password}表示${password}的长度 if [ ${#password} -lt ${min_length} ]; then echo "错误：密码长度至少需${min_length}个字符" return 1 fi # 检查字符类型 for (( i=0; i<${#password}; i++ )); do #${password:$i:1}截取字符串.,每次往后截取一个 char=${password:$i:1} #因为一般的[]无法使用正则,所以升级为[[]] if [[ ${char} =~ [A-Z] ]]; then has_upper=1 elif [[ ${char} =~ [a-z] ]]; then has_lower=1 elif [[ ${char} =~ [0-9] ]]; then has_digit=1 elif [[ ${special_chars} =~ $char ]]; then has_special=1 fi done # 验证所有规则 local errors=() [[ ${has_upper} -eq 0 ]] && errors+=("至少包含1个大写字母") [[ ${has_lower} -eq 0 ]] && errors+=("至少包含1个小写字母") [[ ${has_digit} -eq 0 ]] && errors+=("至少包含1个数字") [[ ${has_special} -eq 0 ]] && errors+=("至少包含1个特殊字符") #获取数组errors的元素个数,@符号用于表示数组的所有元素 if [ ${#errors[@]} -gt 0 ]; then echo "错误：密码需满足以下要求：" printf '• %s\n' "${errors[@]}" return 1 fi echo "密码验证通过" return 0 }
发现有一些错误,本文已经废弃数据库SQL操作SQL动词DDL数据定义(defind)CREATE,DROP,ALTERDQL数据查询(query)SELECTDML数据操纵(manage)INSERT,UPDATE,DELETEDCL数据控制(contronl)GRANT,REVOKEDDL 数据定义1.库的基本操作1.创建库 create database db1 [if not exists db1]; #判断库是否存在，不存在则创建 2.查看所有数据库(可在任意库中使用) show databases; 3.修改数据库字符信息 alter database db1 character set utf8; 4.删除数据库 drop database db1; 5.进入库(可在任意库中使用) use db1;2.表的基本操作1.创建表 create table student( id int AUTO_INCREMENT NOT NULL, name varchar(32) DEFAULT '小王子', age int NOT NULL, score double(4), birthday date UNIQUE, insert_time timestamp Sex CHAR(1) CHECK (Sex IN ('M', 'F')), PRIMARY KEY (id), FOREIGN KEY (birthday_time) REFERENCES Persons(birthday_time) ); #字段类型,名称,长度,约束 2.修改表 ALTER TABLE <表> ADD COLUMN <新列名> <数据类型> #加列 ADD CONSTRAINT <完整性约束名> 约束 #加约束 DROP COLUMN <列名>[CASCADE|RESTRICT] #删列 DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT] #删约束 RENAME COLUMN <列名> TO <新列名> #重命名 ALTER COLUMN <列名> TYPE <数据类型> #改类型 3.删除表 DROP TABLE 表名［RESTRICT| CASCADE］； RESTRICT：限制(保守)。 如果存在依赖该表的对象，则此表不能被删除 CASCADE：没有限制(连坐)。 在删除基本表的同时，相关的依赖对象一起删除 4.建立索引 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>]…)； 索引可以建立在多列上，升序：ASC，降序：DESC。默认ASC UNIQUE表明此索引的每一个索引值只对应唯一的数据记录 CLUSTER表示要建立的索引是聚簇索引 create unique index A on SC(SNO); 一张表只能创建一个聚簇索引。约束类型 AUTO_INCREMENT #自增,只能是数字,必加NOT NULL且一般作为Pkey DEFAULT "xxx" #默认值,当没有插入该列时,默认 UNIQUE #唯一 NOT NULL #非空即不是NULL CHECK(条件) #检查,可追加在col后面,但这样条件必须只能由col,也可单独成行 #int col CHECK(col IN ('x1','x2'...)), #追加 #CHECK(col1 > 2 AND col2 <100), #单独成行 #条件可以是 IN选择,<>大小,支持AND,OR,不支持通配符 PRIMARY KEY (col) #设为主键,唯一非空,可追加在col后面也可单独成行 FOREIGN KEY (col1) REFERENCES tb(col2) #外键,将tb表的col2作为本表col1上的外键,插入的值必须是col2上存在的 相同不同UNIQUE唯一性可以为NULL(仅一个)一个表可有多个PRIMARY KEY唯一性不可为NULL一个表只能有一个表内元素的数据类型与约束 数字 int,smallint,bigint #范围有差别 小数 numeric(p,d) 精度p,小数点后有d位(数字总数可以小于p,但d位小数必须有) #等价于decimal(p,d) dec(p,d) 精度 float(p,d)<double(p,d)<dec(p,d) #虽然指定了精度与小数位,但实际存储还是因为位数有误差 时间 年月日 date 年月日时分秒 datetime 时区时间timestamp #适用于经常插入或更新 时分秒 time 年份 year 字符串 固定长度的字符串 char(n) 占用n字符 可变长度的字符串 varchar(n) 占用实际字符+一部分存储长度 较长的文本数据 text 大小取决于数据库 存储固定长度的二进制数据 binary 可变长度的二进制数据 varbinary #存图片,音频,视频 DML 数据库操纵1.插入数据insert1.指定列添加 insert into 表名(列名1,列名2) values(值1,值2); 2.全列添加(不指定列名) insert into 表名 values(值1,值2,...值n); 3.导入表 insert into 新表 select 语句(原表)2.删除数据delete1.删除表中某行记录 delete from 表名 where 列名=值; 2.删除表中所有记录(未删除属性列) delete from 表名; 3.删除表中所有记录 truncate table 表名; #删除列需要使用DDL删除多个表的有关数据 delete from 表名1,表名2 join 表2 on 连接条件 ... where 列名=值; 例: DELETE p, spj FROM p JOIN spj ON p.PNO = spj.PNO WHERE p.COLOR = '蓝色';3.修改数据update1.会修改列中所有的值 update 表名 set 列名=值; 2.修改列中特定的值 update 表名 set 列名=值1 where 列名=值2; #将值2修改成值1DQL数据查询1.基础查询select [distinct] 列名 自定义列名 from 表名 [where 表达式]distinct可以去重别名与常量as可以省略 select PNO '学号' ,color '颜色' from P 常量,写在属性列前可以使属性列全显示常量 select 3220 PNO,'红' clolor from P where表达式关键字 1.between and (针对int型数据) SELECT * FROM student WHERE age BETWEEN 20 AND 30; 2.and与or (and等价于&&) SELECT * FROM student WHERE age >= 20 AND age <=30; SELECT * FROM student WHERE age = 22 OR age = 18; 3.in(只要符合其一就选择) SELECT * FROM student WHERE age IN (22,18,25); 4.is null 与is not null (空值判断) SELECT * FROM student WHERE english IS NOT NULL; 5.like(模糊查询) _:单个任意字符 %：多个任意字符 查询姓马的有哪些？ SELECT * FROM student WHERE NAME LIKE '马%';where模糊查询like案例 查询姓马的有哪些？ SELECT * FROM student WHERE NAME LIKE '马%'; 查询姓名第二个字是化的人 SELECT * FROM student WHERE NAME LIKE "_化%"; -- 查询姓名是3个字的人 SELECT * FROM student WHERE NAME LIKE '___'; -- 查询姓名中包含德的人 SELECT * FROM student WHERE NAME LIKE '%德%';2.排序查询select [distinct] 列名 from 表名 order by 列名 #默认升序ASC(从上到下)SELECT * FROM person ORDER BY math DESC; #降序声明DESC3.聚合函数1.count：计算个数 2.max：计算最大值 3.min：计算最小值 4.sum：计算和 5.avg：计算平均数 6.DISTINCT:去重 7.HAVING:查询重复记录函数 只有count不忽略空值1.COUNT 计算数量 SELECT COUNT(AGE) AS 年龄小于30岁的人数 FROM 表名 WHERE AGE<30 2.MAX 最大值 SELECT MAX(AGE) AS 最大年龄 FROM 表名 WHERE SEX='女' 3.MIN 最小值 SELECT MIN(AGE) AS 最小年龄 FROM 表名 WHERE SEX='男' 4.SUM 求和 SELECT SUM(AGE) AS 年龄总和 FROM 表名 5.AUG 平均值 SELECT AVG(AGE) AS 年龄平均值 FROM 表名 6. DISTINCT 去重 SELECT DISTINCT(AGE) AS 唯一年龄 FROM UserInfor 7.HAVING 常常与GROUP BY(聚合函数 分组)一起使用 SELECT AGE,COUNT(AGE) AS 重复数量 FROM UserInfor GROUP BY AGE HAVING COUNT(AGE)>1 #按AGE分组后显示AGE数大于1的组(注意,having中的关键字必须在分组后有的关键字中)4.分组查询select 列名 from 表名 group by 表按照性别分组。分别查询男、女同学的平均分,人数 SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex; 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人 SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。 having后没有要求,既可以用聚合函数,也可用所有的属性(可以不在group by 中) 运行顺序 1.where 2.group by 3.聚合函数 4.having 例: select Cno,avg(Salary) from WORKS group by Cno having avg(Salary) >20000 #此处按Cno分组,但是avg中的属性不是Cno5.分页查询select 列名 from 表名 limit 开始索引,查询数量SELECT * FROM student LIMIT 0,3; -- 第1页 0-3 SELECT * FROM student LIMIT 3,3; -- 第2页 3-6 SELECT * FROM student LIMIT 6,3; -- 第3页 6-96.连接查询a.内连接隐式内连接 select 表.列 from 表1,表2 where 表1.列1=表2.列2 (连接) SELECT A.column1, B.column2 FROM A, B WHERE A.common_column = B.common_column; #内连接的结果是建立在两个表共有的分量上,如果在连接的属性中(此#处是common_column)左表有某分量但右表没有,那么该分量就不会 #出现在连接表中显示内连接 select 列名 from 表名1 join 表名2 on 条件 SELECT A.column1, B.column2 FROM A [INNER] JOIN B ON A.common_column = B.common_column; #inner可省略b.外连接左外连接 select 列名 from 表1 join left 表2 右外连接 select 列名 from 表1 join right 表2 #外连接用于保存某些无法被内连接保存的分量,例如左表有但右表没有连接属性的某分量,可以使用左外连接保存左表有右表没有的连接分量参考案例 1如果是自身连接,需要起别名否则无法分辨 SELECT A1.column1, A2.column2 FROM A A1 JOIN A A2 ON A1.common_column = A2.common_column;7.嵌套查询嵌套查询：一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。 SELECT Sname 外层查询/父查询 FROM Student WHERE Sno IN （SELECT Sno 内层查询/子查询 FROM SC WHERE Cno= ' 2 '）； 子查询的结果是集合要使用in 否则可使用= 1.带有谓词ANY或ALL 的子查询 例: > ANY 大于子查询结果中的某个值 <= ALL 小于等于子查询结果中的所有值 = ANY 等于子查询结果中的某个值 !=（或<>）ANY 不等于子查询结果中的某个值 !=（或<>）ALL 不等于子查询结果中的任何一个值 2.带有谓词EXISTS 的子查询 EDXISTS只会返回false或true 针对"没有某个人"的条件使用NOT EXISTS很有效 针对"选了全部课程的人"的条件转化为没有一门课它不选,只要使用NOT EXISTS判断"没有一门课"和"他不选"这两个条件即可8.条件函数1.case多分支的函数 CASE 测试表达式 WHEN 简单表达式1 THEN 结果表达式1 WHEN 简单表达式2 THEN 结果表达式2 … WHEN 简单表达式n THEN 结果表达式n [ ELSE 结果表达式n+1 ] END实例: SELECT CASE WHEN age<25 OR age IS NULL THEN '25岁以下' WHEN age >= 25 THEN '25岁及以上' END age_cut,COUNT(*)number FROM user_profile GROUP BY age_cut2.ifIF(expr1,expr2,expr3) 如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。(三目表达式)实例: select if(age<25 or age is null,'25岁以下','25岁及以上') age_cut, count(device_id) number from user_profile group by age_cut3.nullifNULLIF(expr1, expr2) #如果 expr1 = expr2 成立，那么返回值为 null，否则返回值为 #expr1 的值4.COALESCECOALESCE(值1,值2...); #用于从一组值中返回第一个非 NULL 值。如果所有的值都是 NULL，那#么 COALESCE 将返回 NULL。 #如果值1为null就继续找下一个直到找到非null值就返回第一个非null值,或没找到非null值返回nullSELECT Sname /*查询一个学生的学号 （在Student表中循环取学生信息）*/ FROM Student WHERE NOT EXISTS( /*不存在课程 (在Course中循环取所有课程信息)*/ SELECT * FROM Course WHERE NOT EXISTS( /*没选修 (在SC表中循环比对) */ SELECT * FROM SC WHERE SC.Sno = Student.Sno AND SC.Cno = Course.Cno ) );DCL数据控制参考数据库安全性管理视图视图的优点 视图隐藏了底层的表结构，简化了数据访问操作 因为隐藏了底层的表结构，所以大大加强了安全性 视图的缺点 如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护创建表 CREATE VIEW 视图名 AS SELECT 属性 FROM 表名; 修改视图 ALTER VIEW 视图名称 AS查询语句 删除视图 DROP VIEW IF EXISTS 视图名称; #修改,删除视图都不会对基本表产生影响1.可跟新视图: 一般的行列式子集视图可以更新 2.不可更新视图,有五种: (1)select目标列包含聚集函数. (2)select字句使用了unique或distinct (3)视图中包括group by字句 (4)视图中包括经算法表达式计算出来的列 (5)由单个表的列构成,但并不包括主键的视图索引索引的优点 索引可以大大提高MySQL的检索速度 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性 索引的缺点 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 索引需要占物理空间 如果某个数据列包含许多重复的内容或者小表，为它建立索引就没有太大的实际效果。优先建立索引的列条件 1.在经常需要搜索的列上，可以加快搜索的速度 22.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构 3.在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度 4.在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的 5.在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 6.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。索引逻辑分类 PRIMARY KEY 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL； ALTER TABLE TableName ADD PRIMARY KEY(column); UNIQUE 唯一索引：数据列不允许重复，允许为NULL值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 ALTER TABLE TableName ADD UNIQUE(column); INDEX 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入； ALTER TABLE TableName ADD INDEX IndexName(column); 例如: 为关系表SPJ创建一个基于QTY列的按降序排列的IX_SPJ索引； ALTER TABLE SPJ ADD INDEX IX_SPJ (QTY DESC); 修改索引index名称 #需要结合drop与add ALTER TABLE SPJ DROP INDEX IX_SPJ, ADD INDEX New_IX_SPJ (QTY DESC);tips 按列数分为单例索引与组合索引物理分类 聚簇索引 提高某个属性的查找效率 非聚簇索引参考一文搞懂MySQL索引（清晰明了）-CSDN博客数据库安全性管理禁止非法用户和非法操作的进入用户创建创建用户 CREATE USER 用户名[@'ip'] IDENTIFIED BY 密码 #给跨服务器的用户授权,默认ip是localhost IDENTIFIED BY可以设置密码,也可以不设置密码 实例: CREATE USER 'xs001'@'localhost' IDENTIFIED BY '123456'; 修改密码 ALTER USER 用户名 IDENTIFIED BY 'new_password'; 实例: ALTER USER 'xs001'@'localhost' IDENTIFIED BY 'new_password'; 这个用户可以用于登录MYSQLtips 用户名中可以指定登录ip如 'peiqi'@'localhost' ,限制用户的登录途径 如果创建时指定了登录ip,那么其他操作用户名要带上ip操作1)自主存取控制通过 SQL 的GRANT 语句和REVOKE 语句实现 用户权限组成：数据对象、操作类型 定义用户存取权限：定义用户可以在哪些数据库对象上进行哪些类型的操作 定义存取权限称为授权授权 GRANT 权限 ON 对象类型 对象名 TO 用户 [WITH GRANT OPTION]; 禁止循环授权 WITH GRANT OPTION表示该用户可以将自己的权限分享给其他用户,例如A用户由查询SC表的权限,那么它就可以创建一个有查询SC表的权限的用户 角色授权 每一个用户都是权限的集合,所以可以用用户代替权限(角色包含权限与权限对象) GRANT 角色 TO 角色 [WITH GRANT OPTION];使用GRANT语句，使角色R1拥有Student表的 SELECT权限 GRANT SELECT ON TABLE Student TO R1; 将这个角色授予王平，张明，赵玲。使他们具有角色R1所包含的全部权限 GRANT R1 TO 王平,张明,赵玲;回收 REVOKE 权限 ON 对象类型 对象名 FROM 用户 [CASCADE | RESTRICT]; CASCADE：级连回收 RESTRICT：受限回收 创建数据库模式 create user [with DBA|RESOURSE|CONNECT(默认)] 没有with默认是CONNECT权限 只有root可以创建模式 创建用户 创建视图 创建表 登录后查询与操作 CONNECT no no no yes,但必须授权 #默认权限 DBA yes yes yes yes #管理员权限 RESOURSE no no yes yes #操作表的权限2)强制存取控制对数据、用户进行标记，进行存取控制。 对于任意一个对象，只有具有合法许可证的用户才能存取。数据库完整性禁止不合语义的数据和不正确的数据进入数据库1.完整性约束实体完整性 primary key # 属性内唯一,不可空,表内唯一 违约策略:拒绝参照完整性 forigen key # 外键,外键属性分量必须在外关联表内存在才能插入 例如: student表 SNO,SNAME,SAGE SC表 SNO,CNO,PNO 其中SNO关联student表内的SNO 如果要插入SC表内(3220,1,2),但3220在studet内的SNO没有,是无法插入的 违约策略: 插入外码:拒绝 ---参照表 修改外码:拒绝 ---参照表 修改主码:拒绝/级联/置空值 ---被参照表 修改主码:拒绝/级联/置空值 ---被参照表可能破坏参照完整性的情况 被参照表 参照表 违约处理 X 插入元组 拒绝 X 修改外码 拒绝 删除元组 X 拒绝/级联删除/设为空值 修改主码 X 拒绝/级联删除/设为空值自定义完整性 check 约束可以用CONSTRAINT声明名称,命名规则同java变量 定义在属性后是属性约束 Sno NUMERIC(6) CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999) 定义在表定义最后是元组约束(约束所有属性) CONSTRAINT S1 PRIMARY KEY(Sno) 删除check(需要先命名) ALTER TABLE Student DROP CONSTRAINT C1; 添加check约束 ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sage<40); 2.触发器触发联级修改或删除 情景:当在Student中修改SNO时,同步修改其他表如SC,Class内的SNO定义触发器 create trigger 触发器名 before|after 触发事件 on 表名 for each {row|statement} [when 触发条件] Begin 触发动作 END 权限控制:表的拥有者才可以在表上定义触发器 触发事件 包括insert,delete,update 或组合insert or delete(组合),update of 属性(指定触发属性) 触发时间 包括before,after 触发类型 包括行级触发for each row和语句级触发for each statement 例如:一句update修改了1000行数据,触发1000次行级触发器,触发1次语句级触发器 触发条件when 某些属性达到了条件 例如: SC.SCORE>95 触发动作 达到触发条件后可以进行触发动作,行级触发器可以引用新旧值 BEGIN END; 特殊变量 如果是行级触发器，用户可以使用NEW和OLD引用UPDATE/ INSERT事件之后的新值与旧值。 如果是语句级触发器，则不能在触发器动作体中使用NEW或OLD进行引用。 可以使用declare声明变量 declare @newname varchar(20) select name into @newname from SC触发器的使用 联级删除 CREATE TRIGGER tr_t_test_main_Del BEFORE DELETE ON t_test_main FOR EACH ROW BEGIN DELETE FROM t_test_sub WHERE main_id = OLD.id; END ;删除触发器 DROP TRIGGER <触发器名> ON <表名>3.存储过程创建存储过程 无参构造 Create proc 存储名 as sql语句 入参与出参构造 Create proc(in 变量名 类型,out 变量名 类型) 存储名 as sql语句删除存储过程 drop proc 存储名
前言本博客基于之前的mysql单主单从容器自动化搭建。架构图补:这里使用nginx不是最优选择,nginx只能通过UDP简单轮询实现mysql的负载均衡,应该使用MySQL Router或ProxySQL实现相较于单主单从节点,双主双从自动化的难点大多数都相同,但是又有新的问题。1.双主的启动顺序问题?因为两个master都要互相change master,这就需要另一个master已经启动,这就会产生冲突,个人想法是先启动master1服务,但是在change master前添加阻塞(sleep或远程连接master2),先让master2完成''启动+change master'',然后再继续master1的change master。具体请参考<启动策略>2.master的ROLE问题?初始化脚本init.sh是按照角色来划分SQL的使用,但是双主的master既做master又做slave,所以我添加了新角色m_s,并且优化了脚本的构成,使用更多的函数,方便理解。3.nginx的负载均衡?用nginx的upstream模块实现mysql集群的负载均衡。启动策略一般情况(即主机安装),mysql集群的启动比较随意,因为手动输入可以控制何时执行命令,大致流程如下1.master1启动,授权2.master2启动,授权3.master1完成复制4.master2完成复制5.slave1启动,复制6.salve2启动,复制相较于单主单从,双主双从更加复杂,如果不加以限制同时启动master1与master2使其同时执行毫无疑问是错的,但是简单使master1先于master2服务启动仍是有问题的,关键就在于master1复制时要求master2已经完成启动并授权,反过来也是。如果只是先启动master1,master1已经到了复制,但是发现master2没有授权甚至没有启动,会直接报错,所以master1与master2的执行SQL顺序更加重要!1.没有限制启动的双主如果没有限制,在'复制'时可能由于另一个master没有启动或没有授权导致错误要求复制时另一个master必须启动且已经授权master执行流程相同(便于写脚本,非硬性要求)2.推导流程图关于双主启动策略这块,个人觉得是mysql双主双从自动化启动的核心问题1.master2的等待1:主要是为了保证master1优先启动,通过docker-compose的healthycheck实现(通过脚本测试master1是否启动也可行,但是不选用)2.master2的等待2:这里其实可以不等待,因为此时master1已经开始,没有实际意义,仅用于使master1于master2流程相同3.master1的等待:这个等待的用处是很大的,主要是通过脚本实现检查并等待master2启动完成+授权,然后再进行复制注意:为了保证init.sh的通用性,使的master1和master2的启动流程相同,我把流程不同的部分分到docker-compose中(没错,就是'等待1'),但实际上,可以选择使用多个init.sh分别启动master1和master2总结:''等待1'会写入docker-compose,作为master1的healthycheck''等待''与''等待2'会写入脚本,用于测试另一个master是否已经启动从节点只需要等待主节点启动即可nginx可以直接启动,不需要控制实操env是环境变量文件init.sh是容器初始化脚本my{1..4}.cnf分别是master1,master2,slave1,slave2的mysql配置文件nginx目录下的都是nginx的配置文件START是启动脚本1.docker-compose.ymlversion: "3.8" services: master1: image: mysql:5.7 container_name: mysql_master1 restart: always ports: - 3306 volumes: - data1:/var/lib/mysql - /app/docker/mysql_cluster/my1.cnf:/etc/mysql/conf.d/my.cnf - /app/docker/mysql_cluster/init.sh:/docker-entrypoint-initdb.d/init.sh networks: - mysql_cluster env_file: - env environment: - ROLE=m_s #双主的master也有担当slave的角色,所以需要传输MASTER - MASTER=mysql_master2 #command: healthcheck: test: [ "CMD","mysqladmin","-u","root","-p${MYSQL_ROOT_PASSWORD}","ping","-h","localhost" ] interval: 3s timeout: 2s retries: 5 master2: image: mysql:5.7 container_name: mysql_master2 #build: restart: always ports: - 3306 #依赖master1,设置master1先启动 depends_on: master1: restart: true condition: service_healthy required: true volumes: - data2:/var/lib/mysql - /app/docker/mysql_cluster/my2.cnf:/etc/mysql/conf.d/my.cnf - /app/docker/mysql_cluster/init.sh:/docker-entrypoint-initdb.d/init.sh networks: - mysql_cluster env_file: - env environment: - ROLE=m_s - MASTER=mysql_master1 #command: healthcheck: test: [ "CMD","mysqladmin","-u","root","-p${MYSQL_ROOT_PASSWORD}","ping","-h","localhost" ] interval: 3s timeout: 2s retries: 5 slave1: image: mysql:5.7 container_name: mysql_slave1 #build: restart: always ports: - 3306 #依赖master1,将master1作为复制对象 depends_on: master1: restart: true condition: service_healthy required: true volumes: - data3:/var/lib/mysql - /app/docker/mysql_cluster/my3.cnf:/etc/mysql/conf.d/my.cnf - /app/docker/mysql_cluster/init.sh:/docker-entrypoint-initdb.d/init.sh networks: - mysql_cluster env_file: - env environment: - ROLE=slave - MASTER=mysql_master1 #command: slave2: image: mysql:5.7 container_name: mysql_slave2 #build: restart: always ports: - 3306 #依赖master2,将master2作为复制对象 depends_on: master2: restart: true condition: service_healthy required: true volumes: - data4:/var/lib/mysql - /app/docker/mysql_cluster/my4.cnf:/etc/mysql/conf.d/my.cnf - /app/docker/mysql_cluster/init.sh:/docker-entrypoint-initdb.d/init.sh networks: - mysql_cluster env_file: - env environment: - ROLE=slave - MASTER=mysql_master2 #command: nginx: image: nginx:alpine container_name: mysql_nginx #build: restart: always ports: #打开80端口作为http的监听端口,打开3306作为stream的监听端口(小坑) - 88:80 - 888:3306 volumes: - /app/docker/mysql_cluster/nginx/nginx.conf:/etc/nginx/nginx.conf - /app/docker/mysql_cluster/nginx/conf.d:/etc/nginx/conf.d - /app/docker/mysql_cluster/nginx/html:/var/www/html - log:/var/log/nginx networks: - mysql_cluster #command: networks: mysql_cluster: driver: bridge volumes: data1: {} data2: {} data3: {} data4: {} log: {}2.env#传入root密码,初始化普通用户+密码+数据库 MYSQL_ROOT_PASSWORD=MyStr0ngP@ssw0rd! MYSQL_USER=slave MYSQL_PASSWORD=123 MYSQL_DATABASE=box3.my1.cnf(master的mysql配置文件)my2.cnf仅需要修改server_id,不再展示[mysqld] datadir=/var/lib/mysql symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server_id=1 ## 开启binlog日志 log_bin=mysql-bin.log binlog_format=ROW max_binlog_size=500M expire_logs_days=7 slave_skip_errors=1062 log-slave-updates=1 auto-increment-increment=2 auto-increment-offset=1 replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema bind-address = 0.0.0.0 4.my3.cnf(slave的mysql配置文件)my4.cnf仅需要修改server_id,不再展示[mysqld] datadir=/var/lib/mysql symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server_id=3 read_only = 1 bind-address = 0.0.0.05.START#!/bin/bash #停止docker-compose docker compose down #删除数据卷(因为仅用于测试) docker volume rm mysql_cluster_log docker volume rm mysql_cluster_data1 docker volume rm mysql_cluster_data1 docker volume rm mysql_cluster_data3 docker volume rm mysql_cluster_data4 #启动 docker compose up -d docker ps6.init.sh#!/bin/bash #需要ROLE(本机身份,用于判断) #需要MASTER指定复制节点容器名 #错误判断 function test_order() { if [ $? -ne 0 ]; then echo "命令执行错误" exit 1 fi } #主节点配置 function master() { #创建用户(在没有传入MYSQL_USER和MYSQL_PASSWORD时启用) #CREATE_SQL="create user "${MYSQL_USER}"@'%' identified by '${MYSQL_PASSWORD}';" #授权复制用户 GRANT_SQL="grant replication slave on *.* to "${MYSQL_USER}"@'%';" #刷新权限 FLUSH_SQL="flush privileges;" mysql -u 'root' -p"${MYSQL_ROOT_PASSWORD}" -e "${GRANT_SQL}" &>/dev/null test_order mysql -u 'root' -p"${MYSQL_ROOT_PASSWORD}" -e "${FLUSH_SQL}" &>/dev/null test_order } #从节点配置 function slave() { SHOW_STATUS_SQL1="SHOW MASTER STATUS;" FILE="$(mysql -u'root' -p"${MYSQL_ROOT_PASSWORD}" -h ${MASTER} -e"${SHOW_STATUS_SQL1}"|grep mysql|awk '{print $1}')" POSITION="$(mysql -u'root' -p"${MYSQL_ROOT_PASSWORD}" -h ${MASTER} -e"${SHOW_STATUS_SQL1}"|grep mysql|awk '{print $2}')" #从节点开启复制 CHANGE_MASTER_SQL="change master to master_host='${MASTER}',master_user='${MYSQL_USER}',master_password='${MYSQL_PASSWORD}',master_log_file='${FILE}',master_log_pos=${POSITION};" START_SLAVE="start slave;" #指定主节点 mysql -u 'root' -p"${MYSQL_ROOT_PASSWORD}" -e "${CHANGE_MASTER_SQL} ${START_SLAVE}" test_order } function check() { #注意,这里有大坑,不可使用while true进行循环,未知错误原因,正在查询 for i in {1..5} do mysqladmin -u root -p"${MYSQL_ROOT_PASSWORD}" -h ${MASTER} ping $? && return 0 || exit 1 done } #实现主函数 function main() { #角色为master if [ "${ROLE}" = "master" ]; then echo "1" master #角色为slave elif [ "${ROLE}" = "slave" ]; then echo "2" slave #双从身份(双主的master既作为master又作为slave) elif [ "${ROLE}" = "m_s" ]; then echo "3" master check slave else echo "没有指定角色或角色错误" fi } main7.nginx.confworker_processes 1; events { worker_connections 1024; } http { include mime.types; default_type application/octet-stream; sendfile on; keepalive_timeout 65; gzip on; include /etc/nginx/conf.d/*; server { listen 80; server_name localhost; location / { root html; index index.html index.htm; } error_page 500 502 503 504 /50x.html; location = /50x.html { root html; } } } #负载均衡 stream { upstream mysql_cluster { #使用容器名作为转发目标 server mysql_master1:3306 max_fails=3 fail_timeout=10s; server mysql_master2:3306 max_fails=3 fail_timeout=10s; } server { listen 3306; proxy_pass mysql_cluster; proxy_connect_timeout 10s; proxy_timeout 300s; } } 优化方向nginx负载均衡-->MySQL Router使用slave账号进行测试(可以直接使用slave账号进行测试,因为修bug的原因忘改了没改)
关于'mysql主从自动容器化'的错误集合版本字符类型错误错误veriosn: "3.8"原因 docker-compose的version字段必须为int类型而不能是string解决删除双引号2.使用同一存储路径导致InnoDB无法使用表文件错误 原因 因为master与slave节点同时挂载了同一个数据卷到/var/lib/mysql上,这是mysql存储数据的地方,某些文件(例如表文件)已经被使用,无法被第二个服务使用解决挂载不同的数据卷3.无法非交互式使用密码错误 原因-p与'密码'之间不可以有空格,如果有空格就会导致必须交互式输入密码,其他非-p的参数没有这种限制解决删除-p与'密码'之间的空格4.MASTER状态表为空错误原因没有开启二进制日志,只要开启二进制日志,就会看见二进制日志状态解决修改配置文件至少添加log_bin=mysql-bin.log开启二进制日志,其他可以默认5.多单词赋值变量错误错误原因一般值是一个完整的内容,不需要添加内容,一旦值是由多个单词组成,中间有空格,必须使用引号括起来,否则只能赋值第一个单词,后面会直接报错解决加引号6.bin_log的断点引号问题错误原因bin_log的断点master_log_pos不可以加引号,它是int型的解决删除引号7.函数使用错误错误if [ ${ROLE} -eq "master" ]; then MASTER() #角色为slave elif [ ${ROLE} -eq "slave" ]; then SLAVE() else echo "没有指定角色或角色错误" >>/dev/stderr fi原因以上脚本有两个问题错误的使用函数MASTER,正确的使用应该为MASTER而不是MASTER()错误的使用-eq,这是用于整数比较的,无法用于字符串判断,应该使用=,需要注意,普通的[]是不支持==的,[[]]支持==解决使用=进行字符串判断删除使用函数后的括号8.mysql的-e参数分隔问题错误mysql -u 'root' -p"${MYSQL_ROOT_PASSWORD}" -e "${CREATE_SQL}" "${GRANT_SQL}" "${FLUSH_SQL}"会报错登录失败原因因为-e后面只能跟一个值,这里直接根了3个值导致错误解决连续使用3个mysql+'-e'完成3个操作或直接使用引号将3个操作都括起来9.SLQ重复创建错误错误原因由于已经传入MYSQL_USER与MYSQL_PASSWORD给容器了,mysql自动创建了用户已经存在slave用户了,无法再创建解决不要创建或删除再创建10.slave无法连接master错误原因虽然设置了depends_on,但是它不保证服务完全启动,仅保证启动顺序,这就导致虽然master容器启动了,但是其中的mysql服务没有开启,但同时slave已经开启请求master节点的数据了!解决强制slave节点后启动使用sleep或者使用depends_on的condition设置服务必选完全启动sleep有些粗糙,没有验证master节点是否已经启动,可写一个循环连接master的脚本直至达到N次停止或连接上,然后开始初始化,补:现可以结合dockercompose的depends_on和healthcheck强制要求必须先完成master的启动11.docker-compose修改前未down问题错误脚本内容为:,停止docker-compose-->删除数据卷-->启动docker-compose原因刚刚我修改了docker-compose却没有停止旧的docker-compose导致原本'停止docker-compose'无法停止旧的docker-compose启动的容器,因此无法删除正在使用中的数据卷解决手动停止容器并删除修改docker-compose之前需要先停止它启动的容器12.depends_on错误指定容器名错误原因depends_on需要指定服务名而不是容器名解决修改容器名为服务名13.变量引用顺序问题错误CHANGE_MASTER_SQL="....master_log_file=${FILE},master_log_pos=${POSITION};" FILE="$(xxx)" POSITION="$(xxx)" 原因可以发现,FIEL与POSITION的定义在CHANGE_MASTER_SQL后,这就导致CHANGE_MASTER_SQL在使用FIEL与POSITION时他们还没定义,就为空导致错误解决更换位置,将FILE与POSITION的位置更换到上面新发现1.关于引用变量加单引号的问题关于单引号如何加在引用变量上,我思考了很久,有常常一大段笔记描述的思考过程,测出好几个可用的方式然后,我发现原来在双引号中的单引号不会对中间的内容进行转义,那好,那么白想了,就是这么简单"echo '${NAME}'"此方法用于为授权密码添加单引号2.docker-compose的depends_on配置depends_on普通版depends_on: - <service_A>depends_on升级版depends_onrestart: true(默认)/false 是否在更新被依赖容器后重启被依赖容器condition:service_started 只保证容器启动顺序(默认)service_healthy 结合被依赖容器的healthcheck,需要完成健康检查才能启动依赖容器service_completed_successfully 被依赖容器服务启动才启动依赖容器,保证服务启动顺序required: true(默认)/false 是否在被依赖容器未启动的情况下阻止依赖容器的启动3.关于*是否被shell翻译替换的问题关于这个问题的解决,我花了至少2h,原因是我一直发现*.*在shell环境中被翻译为xxx.xxx形式的文件,并且mysql不允许在*.*两边加上任何引号,原本想试试能不能不翻译,但是如果不翻译echo出来就必须带引号(加上\转义也不行),问题似乎无解了,但是峰回路转,我发现mysql不会翻译*.*,这是为什么?原来,只要在${变量}两边加上双引号就不会进行二次翻译但是为什么呢?这需要用shell对变量的展开解释如果通配符出现在没有引号包围的字符串中时，Bash 会在尝试执行命令之前对它们进行展开,这表示会匹配通配符如果通配符被单引号包围,Bash不会对它们进行展开,但是其他变量同样不会翻译,直接输出如果通配符被双引号包围,Bash会翻译变量,但是无视通配符,即仅翻译变量仔细一看,这是刚学bash脚本时候学的单引号,双引号,反引号等等,没想到到现在给我造成如此大的困扰,果然,细节决定成败,换句话说,细节真的很难找!
环境服务版本centos7.9mysql5.7docker26.1.4docker-compose2.27.1使用docker-compose结合脚本进行自动化部署mysql一主一从主节点即master节点容器从节点即slave节点容器'双主双从'等待未来博客思路首先理清一般非容器化mysql主从部署思路:在多主机上安装mysql(统一版本)修改配置文件(区分master与slave)主节点需要创建复制用户并授权,进行测试从节点需要指定主节点并开启复制线程但是容器化就要有N个问题,主要集中在3,4点上如何保证主节点先于从节点开启?如何从主节点容器获取binlog的文件与断点?如何非交互的使用mysql?如何进行mysql初始化?解答:1.如何保证主节点先于从节点开启?这个问题如果单独使用dockerfile或直接docker启动其实没什么问题,只要先启动master容器就行了,但是如果使用docker-compose呢?我的第一个想法就是使用depends_on,salve容器依赖master容器不就行了吗?那就太easy,不会放这里了,确实可以使用depends_on设置优先级,但是docker-compose启动了容器不代表服务已经启动了!举个简单的例子,如果docker运行比较多就会发现有的容器刚启动还没ps就挂了,有的启动后需要等一会才挂掉,为什么不直接挂断?这是因为容器虽然启动了,但是服务还正在启动,然后发现启动不了,容器就挂了docker-compose这里也是一样,depends_on只能设置master优先启动,但此时服务可能正在启动,此时slave如果直接设置复制change master..会直接报错(经测试,即使设置了depends_on,master服务启动也比slave服务慢!)实际上需要的不是master容器先于slave容器启动,而是master的mysql服务先于slave的mysql服务启动,我想到的最简单的方法就是在slave的初始化脚本中使用sleep,这样就可以强制salve的mysql服务启动后于master的mysql服务启动补:仅使用depends不能保证服务完全启动,只能保证启动顺序,但是depends_on是可以设置需要被依赖容器完全启动才启动依赖容器2.如何从主节点获取binlog的文件名与断点并传入从节点?通过mysql的-h可以通过容器名实现跨节点的登录mysql,然后使用show master status结合三剑客过滤出FILE与POSTION字段,这样就可以直接在从节点直接获取binlog的文件名与断点3.如何非交互的使用mysql?通过mysql的-e参数可以实现非交互式的操作mysql,需要注意的是,如果需要连续输入多个命令可以使用双引号将SLQ扩起来,这样就不用使用多个mysql命令重复登录操作就可以一次实现操作了4.如何进行mysql初始化?使用mysql的初始化脚本,初始化脚本docker-entrypoint.sh会执行docker-entrypoint-initdb.d中的脚本,只要将脚本传入其中即可作为初始化脚本最终使用文件master的配置文件my1.cnfslave的配置文件my2.cnfdocker-compose.yml初始化脚本init.sh启动命令脚本START目录层级如下实现1.master的配置文件my1.cnf[mysqld] datadir=/var/lib/mysql symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server_id=1 #开启binlog日志 log_bin=mysql-bin.log binlog_format=ROW max_binlog_size=500M expire_logs_days=7 slave_skip_errors=1062 log-slave-updates=1 auto-increment-increment=2 auto-increment-offset=1 replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema 2.slave的配置文件my2.cnf[mysqld] datadir=/var/lib/mysql symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #server_id必须与master不同 server_id=2 #开启只读 read_only = 13.docker-compose.ymlversion: "3.8" services: master: image: mysql:5.7 container_name: mysql_master #build: restart: always ports: - 3306 volumes: - data1:/var/lib/mysql - /app/docker/mysql_cluster/my1.cnf:/etc/mysql/conf.d/my.cnf - /app/docker/mysql_cluster/init.sh:/docker-entrypoint-initdb.d/init.sh networks: - mysql_cluster env_file: - env environment: - ROLE=master #command: slave: image: mysql:5.7 container_name: mysql_slave #build: restart: always ports: - 3306 #注意,depends_on是可以实现完全服务启动再往后执行的,本次使用sleep(脚本中)实现 depends_on: - master volumes: - data2:/var/lib/mysql - /app/docker/mysql_cluster/my2.cnf:/etc/mysql/conf.d/my.cnf - /app/docker/mysql_cluster/init.sh:/docker-entrypoint-initdb.d/init.sh networks: - mysql_cluster env_file: - env #传入ROLE与MASTER environment: - ROLE=slave - MASTER=mysql_master #command: networks: mysql_cluster: driver: bridge volumes: data1: {} data2: {}4.初始化脚本init.sh(适用于主+从节点)#!/bin/bash #需要传入变量ROLE(本机身份,用于判断),MASTER(从节点需要传入用于指定主节点容器名) #错误判断 function test_order { if [ $? -ne 0 ]; then echo "命令执行错误" exit 1 fi } function MASTER { #创建用户(在没有传入MYSQL_USER和MYSQL_PASSWORD时启用) #CREATE_SQL="create user "slave"@'%' identified by '123';" #授权复制用户 GRANT_SQL="grant replication slave on *.* to "${MYSQL_USER}"@'%';" #刷新权限 FLUSH_SQL="flush privileges;" mysql -u 'root' -p"${MYSQL_ROOT_PASSWORD}" -e "${GRANT_SQL} ${FLUSH_SQL}" test_order } function SLAVE { sleep 10 SHOW_STATUS_SQL1="SHOW MASTER STATUS;" FILE="$(mysql -u'root' -p"${MYSQL_ROOT_PASSWORD}" -h ${MASTER} -e"${SHOW_STATUS_SQL1}"|grep mysql|awk '{print $1}')" &>>/dev/stdout POSITION="$(mysql -u'root' -p"${MYSQL_ROOT_PASSWORD}" -h ${MASTER} -e"${SHOW_STATUS_SQL1}"|grep mysql|awk '{print $2}')" &>>/dev/stdout #从节点开启复制 CHANGE_MASTER_SQL="change master to master_host='${MASTER}',master_user='${MYSQL_USER}',master_password='${MYSQL_PASSWORD}',master_log_file='${FILE}',master_log_pos=${POSITION};" START_SLAVE="start slave;" #指定主节点 mysql -u 'root' -p"${MYSQL_ROOT_PASSWORD}" -e "${CHANGE_MASTER_SQL} ${START_SLAVE}" test_order } #角色为master if [ "${ROLE}" = "master" ]; then MASTER #角色为slave elif [ "${ROLE}" = "slave" ]; then SLAVE else exit 1 fi5.环境变量文件env(通用变量,主+从挂载)#env.txt MYSQL_ROOT_PASSWORD=MyStr0ngP@ssw0rd! MYSQL_USER=slave MYSQL_PASSWORD=123 6.启动命令脚本START#!/bin/bash docker compose down &>/dev/null docker volume rm mysql_cluster_data1 docker volume rm mysql_cluster_data2 docker compose up -d docker ps7.一点注意事项master节点需要传入ROLE用于判断角色slave节点需要传入ROLE用于判断角色,需要传入MASTER用于确定master节点的容器名如果执行出问题,可以尝试进入容器手动执行init.sh初始化脚本启动START脚本即可最终总结搭了两天才搭起来,一方面是mysql好久没搭过主从,另一方面是之前没有接触过mysql容器的自动化,几乎都是花时间在脚本的调错上配置过程中的错误参考mysql主从自动容器化(2) 错误已推送至gitee仓库.可直接通过gitee仓库下载配置文件 my-sqlauto-replica仓库