分类 安全之路 下的文章

GORM不使用数据绑定获取任意SQL获取结果

在gorm中,想获取sql执行的结果,必须将返回结果绑定到slice或者struct中,但是如果我们想通用执行任意语句,在事先不知道表结构的情况下是没办法使用这个方式的,最后通过查阅资料发现一个曲线救国的方式

rows, err := db.Raw(sql).Rows()
    if err != nil {
        result = append(result, err.Error())
        return result
    }
    var colums []string
    for rows.Next() {
        //先获取所有的column
        if colums == nil {
            colums, _ = rows.Columns()
        }
        result = append(result, colums)
        //建立俩个interface数组,columnPointers中存在columns的地址
        columns := make([]interface{}, len(colums))
        columnPointers := make([]interface{}, len(colums))
        for i, _ := range columns {
            //赋值地址
            columnPointers[i] = &columns[i]
        }
        //扫描结果
        rows.Scan(columnPointers...)
        m := make(map[string]interface{})
        for i, colName := range colums {
            val := columnPointers[i].(*interface{})
            m[colName] = *val
        }

        //log.Println(m)
        result = append(result, m)
        //result = append(result, row)
    }
    return result
    //log.Println(result)

}

一开始不太好理解,然后去看了gorm Scan部分的代码
sql/sql.go

func (rs *Rows) Scan(dest ...interface{}) error {
    rs.closemu.RLock()
    ...一些不重要的代码...
    for i, sv := range rs.lastcols {
        //重点看convertAssignRows,是如何赋值的
        err := convertAssignRows(dest[i], sv, rs)
        if err != nil {
            return fmt.Errorf(`sql: Scan error on column index %d, name %q: %v`, i, rs.rowsi.Columns()[i], err)
        }
    }
    return nil
}

func convertAssignRows(dest, src interface{}, rows *Rows) error {
    // 通过反射找到具体类型
    var sv reflect.Value

    switch d := dest.(type) {
    //字符串指针
    case *string:
        .....
    case *[]byte:
        .....
    case *RawBytes:
        ......
    case *bool:
        .....
    //就是这里了,我们传进来的是指向columns的interface的指针,所以直接给columns的值修改了
    case *interface{}:
        *d = src
        return nil
    }

    ...一些不相关的代码...
}

参考资料

https://kylewbanks.com/blog/query-result-to-map-in-golang

https://gocn.vip/topics/2121

GO SMTP SSL链接出错问题

Golang自带了一个smtp模块,正常登录流程如下

hostname := fmt.Sprintf("%s:%s", host, port)  
domain := strings.Split(email, "@")[1]  
auth := smtp.PlainAuth("", email, password, domain)
smtpClient, err = smtp.Dial(hostname)  
if err != nil {  
   log.Println("Dial: ", err)  
   return false  
}
err = smtpClient.Auth(auth)  
if err != nil {  
   log.Println("Auth: ", err)  
   return false  
}

但是我发现在链接465即配置了SSL的smtp服务器会导致30秒之后直接返回一个EOF错误

最后通过翻阅smtp/smtp.go代码发现如下:

//默认使用该方法
func Dial(addr string) (*Client, error) {
    //先创建tcp链接
    conn, err := net.Dial("tcp", addr)
    if err != nil {
        return nil, err
    }
    host, _, _ := net.SplitHostPort(addr)
    //在进入NewClient
    return NewClient(conn, host)
}

// NewClient returns a new Client using an existing connection and host as a
// server name to be used when authenticating.
func NewClient(conn net.Conn, host string) (*Client, error) {
    text := textproto.NewConn(conn)
    _, _, err := text.ReadResponse(220)
    //其实这里已经失败了,所以返回一个EOF
    if err != nil {
        text.Close()
        return nil, err
    }
    c := &Client{Text: text, conn: conn, serverName: host, localName: "localhost"}
    _, c.tls = conn.(*tls.Conn)
    return c, nil
}

那么我们只需要创建一个tls.Dial,然后在通过tls的Conn去创建一个NewClient即可。

最后修复代码如下

func SmtpLogin(host, port, email, password string, ssl bool) bool {
    hostname := fmt.Sprintf("%s:%s", host, port)
    domain := strings.Split(email, "@")[1]
    auth := smtp.PlainAuth("", email, password, domain)
    var smtpClient *smtp.Client
    var err error

    if ssl {
        tlsconfig := &tls.Config{
            InsecureSkipVerify: true,
            ServerName:         host,
        }
        //创建一个tls链接
        if conn, err := tls.Dial("tcp", hostname, tlsconfig); err != nil {
            log.Println("tls.Dial: ", err)
            return false
        } else {
            smtpClient, err = smtp.NewClient(conn, domain)
        }

    } else {

        if tcpConn, err := net.Dial("tcp", hostname); err != nil {
            log.Println("net.Dial: ", err)
            return false
        } else {
            smtpClient, err = smtp.NewClient(tcpConn, domain)
        }
    }

    if err != nil {
        log.Println("smtp.NewClient: ", err)
        return false
    }

    err = smtpClient.Auth(auth)
    if err != nil {
        log.Println("Auth: ", err)
        return false
    }
    return true
}

参考资料

https://gist.github.com/jim3ma/b5c9edeac77ac92157f8f8affa290f45
https://bastengao.com/blog/2019/11/go-smtp-ssl.html
https://gist.github.com/chrisgillis/10888032
https://stackoverflow.com/questions/57063411/go-smtp-unable-to-send-email-through-gmail-getting-eof

yabai是一个macOS的上的平铺桌面程序,可以达到类似linux上i3wm之类的效果。

-w1920

需要开启显示器具有单独的空间

安装

# yabai
brew install koekeishiya/formulae/yabai

# spacebar 用于代替原生status bar,需在设置中设置为自动隐藏
brew install cmacrae/formulae/spacebar

# skhd 快捷键管理工具,可以用其他的程序代理
brew install koekeishiya/formulae/skhd

sudo yabai --install-sa

# 如果是Big Sur则还需要执行该行

sudo yabai --load-sa

启动

brew services start yabai
brew services start skhd
brew services start spacebar

配置文件

yabai配置

touch ~/.yabairc
chmod +x ~/.yabairc

配置文件内容

# bsp or float (default: bsp)
yabai -m config layout bsp

# Override default layout for space 2 only
# yabai -m config --space 2 layout float

# New window spawns to the left if vertical split, or top if horizontal split
yabai -m config window_placement first_child

# New window spawns to the right if vertical split, or bottom if horizontal split
yabai -m config window_placement second_child


# Set all padding and gaps to 20pt (default: 0)
yabai -m config top_padding    20
yabai -m config bottom_padding 20
yabai -m config left_padding   20
yabai -m config right_padding  20
yabai -m config window_gap     20

# Override gaps for space 2 only
# yabai -m config --space 2 window_gap 0

# on or off (default: off)
yabai -m config auto_balance on

# set mouse interaction modifier key (default: fn)
yabai -m config mouse_modifier fn

# set modifier + left-click drag to resize window (default: move)
yabai -m config mouse_action1 move

# set modifier + right-click drag to resize window (default: resize)
yabai -m config mouse_action2 resize

# set focus follows mouse mode (default: off, options: off, autoraise, autofocus)
yabai -m config focus_follows_mouse autoraise

# set mouse follows focus mode (default: off)
# yabai -m config mouse_follows_focus on

# floating windows are always on top (default: off)
yabai -m config window_topmost on

# modify window shadows (default: on, options: on, off, float)
# example: show shadows only for floating windows
yabai -m config window_shadow float

# window opacity (default: off)
# example: render all unfocused windows with 90% opacity
# 窗口透明度设置
yabai -m config window_opacity off
yabai -m config active_window_opacity 1.0
yabai -m config normal_window_opacity 0.95

# add 20 padding to the top and 0 padding to the bottom of every space located on the main display
yabai -m config external_bar main:20:0
# add 20 padding to the top and bottom of all spaces regardless of the display it belongs to
yabai -m config external_bar all:20:20


# 临时性的App 也就是manage=off不受到yabai平铺式的限制,之前怎么弹出来就怎么弹出来
otherApp='^(IINA|Calendar|日历|System Preferences|系统偏好设置|Xnip|GoLand|微信)$'
yabai -m rule --add app="${otherApp}" manage=off


space_1='(iTerm2)'
yabai -m rule --add label=space_1 app=$(space_1) space=2

# space_1='(iTerm2)'
# yabai -m rule --add label=space_1 app=$(space_1) space=1

yabai -m rule --add label=safari app="^Safari$" space=2

# ====== Border settings =======================
normal_border_color="010101"
focused_border_color="FFD700"
preselect_border_color="2d74da"


yabai -m config window_border               on
yabai -m config window_border_width         2
yabai -m config active_window_border_color  "0xE0${focused_border_color}"
yabai -m config insert_window_border_color  "0xE0${preselect_border_color}"
yabai -m config normal_window_border_color  "0x00${normal_border_color}"


skhd 配置

touch ~/.skhdrc
chmod +x ~/.skhdrc

配置内容

# opens iTerm2(暂时隐藏了,因为权限太高了,导致AS快捷键都捕获不到,热键冲突)
#alt - return : "${HOME}"/.config/yabai/scripts/open_iterm2.sh


# Show system statistics


# Navigation
alt - h : yabai -m window --focus west
alt - j : yabai -m window --focus south
alt - k : yabai -m window --focus north
alt - l : yabai -m window --focus east

# Moving windows
shift + alt - h : yabai -m window --warp west
shift + alt - j : yabai -m window --warp south
shift + alt - k : yabai -m window --warp north
shift + alt - l : yabai -m window --warp east


# Resize windows
shift+ cmd - h : \
    yabai -m window --resize left:-20:0 ; \
    yabai -m window --resize right:-20:0

shift + cmd - j : \
    yabai -m window --resize bottom:0:20 ; \
    yabai -m window --resize top:0:20

shift + cmd - k : \
    yabai -m window --resize top:0:-20 ; \
    yabai -m window --resize bottom:0:-20

shift + cmd - l : \
    yabai -m window --resize right:20:0 ; \
    yabai -m window --resize left:20:0

# Float and center window
shift + alt - c : yabai -m window --toggle float;\
                  yabai -m window --grid 4:4:1:1:2:2

# Equalize size of windows
lctrl + alt - 0 : yabai -m space --balance

# Enable / Disable gaps in current workspace
lctrl + alt - g : yabai -m space --toggle padding; yabai -m space --toggle gap

# Rotate windows clockwise and anticlockwise
alt - r         : yabai -m space --rotate 90
shift + alt - r : yabai -m space --rotate 270

# Rotate on X and Y Axis
shift + alt - x : yabai -m space --mirror x-axis
shift + alt - y : yabai -m space --mirror y-axis

# Set insertion point for focused container
shift + lctrl + alt - h : yabai -m window --insert west
shift + lctrl + alt - j : yabai -m window --insert south
shift + lctrl + alt - k : yabai -m window --insert north
shift + lctrl + alt - l : yabai -m window --insert east

# Float / Unfloat window (更换 layout 方式) w
shift + alt - space : yabai -m window --toggle float

# Restart Yabai
shift + cmd + alt - r :
    /usr/bin/env osascript <<< \
        "display notification \"Restarting Yabai\" with title \"Yabai\""; \
    launchctl kickstart -k "gui/${UID}/homebrew.mxcl.yabai"

# Make window native fullscreen
alt - f         : yabai -m window --toggle zoom-fullscreen
# lctrl + alt - s       : yabai -m window --toggle split
shift + alt - f : yabai -m window --toggle native-fullscreen

# toggle window split type
alt - e : yabai -m window --toggle split

# increase window size 竖直方向才能用 top  水平方向才能用 left
shift + alt - d : yabai -m window --resize left:-20:0
shift + alt - w : yabai -m window --resize top:0:-20


# decrease window size
shift + alt - a : yabai -m window --resize left:20:0
shift + alt - s : yabai -m window --resize top:0:20


# fast focus desktop (切换空间焦点)
 cmd + alt - x : yabai -m space --focus recent
 cmd + alt - z : yabai -m space --focus prev
 cmd + alt - c : yabai -m space --focus next
# alt - 1 : yabai -m space --focus 1
# alt - 2 : yabai -m space --focus 2
# alt - 3 : yabai -m space --focus 3
# alt - 4 : yabai -m space --focus 4
# alt - 5 : yabai -m space --focus 5
# alt - 6 : yabai -m space --focus 6
# alt - 7 : yabai -m space --focus 7
# alt - 8 : yabai -m space --focus 8
# alt - 9 : yabai -m space --focus 9
# alt - 0 : yabai -m space --focus 10

# create new space
ctrl - n : yabai -m space --create
ctrl - m : yabai -m space --destroy

# destroy empty spaces
shift + cmd + alt - w : echo "destroy empty spaces";\
                        yabai -m query --spaces \
                        | jq 'reverse | .[] | select((.windows | length) == 0) | .index' \
                        | xargs -I{} yabai -m space {} --destroy \;

# send window to space
ctrl + shift - 1 : yabai -m window --space 1
ctrl + shift - 2 : yabai -m window --space 2
ctrl + shift - 3 : yabai -m window --space 3
ctrl + shift - 4 : yabai -m window --space 4
ctrl + shift - 5 : yabai -m window --space 5
ctrl + shift - 6 : yabai -m window --space 6
ctrl + shift - 7 : yabai -m window --space 7
ctrl + shift - 8 : yabai -m window --space 8
ctrl + shift - 9 : yabai -m window --space 9
ctrl + shift - 0 : yabai -m window --space 10

# Move focus container to workspace
shift + cmd - m : yabai -m window --space last && yabai -m space --focus last
shift + cmd - b : yabai -m window --space prev && yabai -m space --focus prev
shift + cmd - n : yabai -m window --space next && yabai -m space --focus next
shift + cmd - 1 : yabai -m window --space 1 && yabai -m space --focus 1
shift + cmd - 2 : yabai -m window --space 2 && yabai -m space --focus 2
shift + cmd - 3 : yabai -m window --space 3 && yabai -m space --focus 3
shift + cmd - 4 : yabai -m window --space 4 && yabai -m space --focus 4
shift + cmd - 5 : yabai -m window --space 5 && yabai -m space --focus 5
shift + cmd - 6 : yabai -m window --space 6 && yabai -m space --focus 6


alt + ctrl - f : yabai -m space --layout float && \
                 yabai -m config window_border off
alt + ctrl - b : yabai -m space --layout bsp && \
                 yabai -m config window_border on



spacebar配置

需要先安装Font-Awesome字体

 mkdir -p ~/.config/spacebar
 touch ~/.config/spacebar/spacebarrc
 chmod +x ~/.config/spacebar/spacebarrc
 vscode ~/.config/spacebar/spacebarrc

配置文件内容

spacebar -m config position           top
spacebar -m config height             26
spacebar -m config spacing_left       25
spacebar -m config spacing_right      15
spacebar -m config text_font          "Helvetica Neue:Bold:13.0"
spacebar -m config icon_font          "Hack Nerd Font Mono:Regular:13.0"
spacebar -m config background_color   0xff202020
spacebar -m config foreground_color   0xffa8a8a8
spacebar -m config space_icon_color   0xff458588
spacebar -m config power_icon_color   0xffcd950c
spacebar -m config battery_icon_color 0xffd75f5f
spacebar -m config dnd_icon_color     0xffa8a8a8
spacebar -m config clock_icon_color   0xffa8a8a8
spacebar -m config space_icon_strip   1 2 3 4 5 6 7 8 9 10
spacebar -m config power_icon_strip    
spacebar -m config space_icon         
spacebar -m config clock_icon         
spacebar -m config dnd_icon           
spacebar -m config clock_format       "%Y-%m-%d %R:%S"

重启

brew services restart yabai
brew services restart skhd
brew services restart spacebar

Vim学历之路

最近闲来无事,想学习一下vim的使用;之前在服务器上编辑文件大部分都是使用的nano进行编辑。远程开发基本也是vscode+remote SSH插件进行开发,其实体验已经非常棒了,但是始终就是觉得不够cool,加上下次出门的话就可以只待一个ipad+键盘,装个ish,使用vim配合sshfs进行远程开发/维护也方便一些。

学习过程是痛苦的,vim的快捷键可太多了,一开始直接去看了SpaceVim,确实炫酷,但是感觉根本用不起来;可能是因为对vim压根没一个概念,然后去看了俩本书《Vim 8文本处理实战》,《Vim实用技巧》,看完之后总算对vim有一个概念了。

然后用原生Vim按照《Vim 8文本处理实战》中所写的配置挨个配置了一遍,代码补全倒是折腾了一会其他还算顺利,快捷键实际用起来除了hjkl不顺手,其他的还算适应的快。

然后去mooc找到个不错的视频,跳着看了大概2-3集,巩固了一下基础知识:
Vim快速增删改查
https://www.imooc.com/video/19450
Vim快速移动大法
https://www.imooc.com/video/19449
Vim多文件操作
https://www.imooc.com/video/19452

最后算是能上手了,最后重新装会SpaceVim,发现瞬间就能明白他的概念和操作模式了,映射了一些比较常用的快捷键,采用空格键作为触发键,还是很方便的。

-w1920

最后总结了一个自己的vim操作的思维导图

记录一次Mysql主从与Mysql-Proxy读写分离配置

首先环境如下

IP类型
10.255.0.1Master
10.255.0.2Slave
10.255.0.3Proxy

目前208上已经运行一个业务Mysql,并且大约有20G左右的数据量,因为读写效率低下的问题,所以加了一台新的机器做读写分离。

配置修改

首先要对俩台mysql的配置文件进行修改,分别要设置server-id
Master在原有配置上增加配置,一些需要优化的参数可以自己调整,比如选用mixed还是row等模式

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
binlog-do-db = jd # 需要同步的数据库

Slave增加一行

[mysqld]
server-id=2 #设置server-id

默认my.cnf可能会默认设置一个server-id,需要找到并修改他

Master配置

导出数据

正常下数据导入是在Master机器上进行锁表,然后导出数据,然后在解锁;这里说另一种方法,就是导出的时候增加--master-data=2参数,这样在导出的文件中就会记录mysql-bin与pos参数的值。

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -u root -p db > db.sql

存储的参数信息大致如下

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000307', MASTER_LOG_POS=120;

--

增加同步用户

#创建用户
CREATE USER 'repl'@'10.255.0.2' IDENTIFIED BY 'passwd';
#分配权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.255.0.2';
#刷新权限
flush privileges;   

Slave配置

创建账户与数据库

首先需要创建一个同名数据库与master同名的账户密码,比如数据库的名字是db,用户是db密码是pass,需要创建一个完全相同的即可。

导入数据

mysql -u db -p db < db.sql

链接Master

 CHANGE MASTER TO
        MASTER_HOST='10.255.0.1',
        MASTER_USER='repl',
        MASTER_PASSWORD='password',
        MASTER_LOG_FILE='mysql-bin.000307', # 这里写导出的sql中的参数
        MASTER_LOG_POS=120; # 这里写导出的sql中的参数
start slave; # 开启同步

然后执行一下

show  slave status\G

看看是否有什么错误,如果一切正常,大概可以看到这样的输出
-w762

Mysql-Proxy配置

安装

wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz

tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
cd /usr/local/mysql-proxy
mkdir lua
mkdir logs
cp share/doc/mysql-proxy/rw-splitting.lua ./lua
cp share/doc/mysql-proxy/admin-sql.lua ./lua

配置文件

创建/etc/mysql-proxy.cnf文件,并写入

user=root
admin-username=db # 俩台机器相同的mysql账户密码
admin-password=123456 
proxy-address=0.0.0.0:3306 
proxy-read-only-backend-addresses=10.255.0.2:3306  # Slave机器
proxy-backend-addresses=10.255.0.1:3306 # Master机器
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua  # 读写分离脚本
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log 
log-level=info 
daemon=true
keepalive=true

实际使用时Mysql-proxy不支持注释,需要删除所有中文注释使用。

负载设置

打开/usr/local/mysql-proxy/lua/rw-splitting.lua文件
修改

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 2, //单机最小链接,默认4
                max_idle_connections = 4, // 最大链接,默认8

                is_debug = false
        }
end

启动

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

结束

到这里就已经配置结束了,可以直接链接10.255.0.3这台机器,会根据你的读写语句自动切换到不同的机器,这样就可以不需要去修改任何业务代码实现读写分离了。