SHELL内涵段子

1. 字符掩码，过滤掉不需要的 Database

DATABASE_MASK="information_schema|mysql|sys|performance_schema"
context=mysql  -u "${USENAME}" -p"${PASSWD}" -h ${HOST} -P ${PORT}  -e "show databases"  | sed -n "2,\$p" |grep -E -v $DATABASE_MASK)

echo $context
1
2
3
4

2. 不想解压 gz 压缩包，但是前100行里有自己需要的内容，需要提取出来

后台把需要的部分弄出来，然后使用另一个进程 kill 掉后台进程

NEED_BACKUP_FILE=xxxx.sql.gz
zcat $NEED_BACKUP_FILE | grep "CHANGE MASTER" > pos.txt &

ps -aux | grep "gzip -cd $NEED_BACKUP_FILE" | grep -v "grep"
sleep 1
PID=$(ps -aux | grep "gzip -cd $NEED_BACKUP_FILE" |grep -v "grep" |  awk '{print $2}')
echo $PID
kill -n 9 $PID

cat pos.txt
1
2
3
4
5
6
7
8
9
10

3. 如下指定文件以后的的文件数量

有如下目录，期望获取期望得到mysql-bin.000004及以后的文件数量 19

root@8ecafab5563f:/var/lib/mysql# ls -la | grep "mysql-bin"
-rw-r-----   1 mysql mysql      177 Nov 24 22:01 mysql-bin.000001
-rw-r-----   1 root  root   3094352 Nov 24 22:01 mysql-bin.000002
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000003
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000004
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000005
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000006
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000007
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000008
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000009
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000010
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000011
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000012
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000013
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000014
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000015
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000016
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000017
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000018
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000019
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000020
-rw-r-----   1 root  root       201 Nov 24 22:04 mysql-bin.000021
-rw-r-----   1 mysql mysql      154 Nov 24 22:04 mysql-bin.000022
-rw-r-----   1 mysql mysql      418 Nov 24 22:04 mysql-bin.index
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

num=$(ls -la | grep "mysql-bin" | grep -v "mysql-bin.index" | awk '{print $9}' | grep "mysql-bin" | sort -r | grep -n "000004" | awk -F ':' '{print $1}')

echo $num
1
2
3

4. 参数拼接，直接使用 my2sql 举例

#!/bin/bash

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASS="pass@1234"

MY2SQL_WORK_TYPE="2sql"         # 2sql|rollback
MY2SQL_MODE="file"              # repl|file
MY2SQL_OUTPUT_DIR="./result"



MY2SQL_SQL=                     # insert,update,delete
MY2SQL_START_FILE="mysql-bin.000257"
MY2SQL_STOP_FILE="mysql-bin.000267"

MY2SQL_START_DATETIME=          # like "2020-01-01 01:00:00"
MY2SQL_STOP_DATETIME=           # like "2020-01-01 01:00:00"
MY2SQL_START_POS=30936419
MY2SQL_STOP_POS=

MY2SQL_DATABASES="lek_health"
MY2SQL_TABLES=
MY2SQL_THREADS=4


MY2SQL_OPT="-add-extraInfo -work-type $MY2SQL_WORK_TYPE  -mode $MY2SQL_MODE -start-file $MY2SQL_START_FILE -user $MYSQL_USER -password $MYSQL_PASS -host $MYSQL_HOST -port $MYSQL_PORT"

mkdir -p result

# 以下是参数拼接部分
[[ "$MY2SQL_MODE" = "file" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -local-binlog-file ./$MY2SQL_START_FILE"

[[ "$MY2SQL_DATABASES" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -databases $MY2SQL_DATABASES"
[[ "$MY2SQL_TABLES" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -tables $MY2SQL_TABLES"
[[ "$MY2SQL_SQL" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -sql $MY2SQL_SQL"
[[ "$MY2SQL_THREADS" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -threads $MY2SQL_THREADS"


[[ "$MY2SQL_START_DATETIME" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -start-datetime $MY2SQL_START_DATETIME"
[[ "$MY2SQL_STOP_DATETIME" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -stop-datetime $MY2SQL_STOP_DATETIME"

[[ "$MY2SQL_START_POS" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -start-pos $MY2SQL_START_POS"
[[ "$MY2SQL_STOP_POS" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -stop-pos $MY2SQL_STOP_POS"

[[ "$MY2SQL_STOP_FILE" ]] &&  MY2SQL_OPT="$MY2SQL_OPT -stop-file $MY2SQL_STOP_FILE"

MY2SQL_OPT="$MY2SQL_OPT -local-binlog-file $MY2SQL_START_FILE  -output-dir $MY2SQL_OUTPUT_DIR"

echo "$MY2SQL_OPT"

./my2sql $MY2SQL_OPT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

5. mysqldump 备份数据直接打包(节省磁盘空间)

实测 19 GB的逻辑备份 gzip 打包后2 GB多，如果分两步走，将需要 21 GB 的磁盘空间

mysqldump  --single-transaction --set-gtid-purged=OFF  --master-data -u "${USENAME}" -p"${PASSWD}" -h ${HOST} -P ${PORT} --databases $BACKUP_DB_NAME | gzip > dump-$(date +%s).sql.gz
1

6. mysqldump “备份+压缩+传输”一条龙

使用场景，本地磁盘不够，直接丢到远程服务器上去

mysqldump  --single-transaction --set-gtid-purged=OFF  --master-data -u "${USENAME}" -p"${PASSWD}" -h ${HOST} -P ${PORT} --databases $BACKUP_DB_NAME | gzip - ｜ ssh root@192.168.2.222 "cat - > dump-$(date +%s).sql.gz"
1

参考

1. 《Linux Shell 命令行及脚本编程实例详解》

2. https://blog.csdn.net/liuxiao723846/article/details/91041629

3. https://github.com/liuhr/my2sql

4. https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html