go访问sqlite3删除数据

来个增删改查完整版了

删除数据代码如下

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "./foo.db")
	checkErr(err)

	stmt, err := db.Prepare("delete from userinfo  where uid=?")
	checkErr(err)
	res, err := stmt.Exec(3)
	checkErr(err)
	affect, err := res.RowsAffected()
	checkErr(err)
	fmt.Println(affect)
}
func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

 

go访问sqlite3更新数据

前面两个文章说了插入,和读取,这个例子演示如何更新数据

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "./foo.db")
	checkErr(err)

	stmt, err := db.Prepare("update userinfo set username=? where uid=?")
	checkErr(err)
	res, err := stmt.Exec("kkkkk", 2)
	checkErr(err)
	affect, err := res.RowsAffected()
	checkErr(err)
	fmt.Println(affect)
}
func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

 

go访问sqlite3读取数据

前一文说了如何插入数据,现在可以把数据记录读成一个二维数组,真的太方便了。

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
)

type userinfo struct {
	id                            int
	username, departname, created string
}

func main() {
	db, err := sql.Open("sqlite3", "./foo.db")
	checkErr(err)
	rows, err := db.Query("select * from userinfo")
	checkErr(err)
	var userlist []userinfo
	var u userinfo
	for rows.Next() {
		rows.Scan(&u.id, &u.username, &u.departname, &u.created)
		userlist = append(userlist, u)
	}
	fmt.Printf("%#v\n", userlist)
	fmt.Printf("len %d\n", len(userlist))
}
func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

输出记录如下

E:\golang\pj2>sqlite4.exe
[]main.userinfo{main.userinfo{id:2, username:"fy", departname:"mydep", created:"2017-01-14T00:00:00Z"}, main.userinfo{id:3, username:"fy", departname:"mydep", created:"2017-01-14T00:00:00Z"}, main.userinfo{id:4, username:"fy", departname:"mydep", created:"2017-01-14T00:00:00Z"}}
len 3

 

go访问sqlite3插入数据

用go访问sqlite真的非常简单,先用sqlite工具创建库,执行sql创建表

   CREATE TABLE `userinfo` (
        `uid` INTEGER PRIMARY KEY AUTOINCREMENT,
        `username` VARCHAR(64) NULL,
        `departname` VARCHAR(64) NULL,
        `created` DATE NULL
    );

    CREATE TABLE `userdeatail` (
        `uid` INT(10) NULL,
        `intro` TEXT NULL,
        `profile` TEXT NULL,
        PRIMARY KEY (`uid`)
    );

go代码如下

package main

import (
	"database/sql"
	"fmt"
	"flag"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "./foo.db")
	checkErr(err)
	sth, err := db.Prepare("INSERT INTO userinfo(username, departname, created) values(?,?,?)")
	checkErr(err)
	res, err := sth.Exec("fy", "www.yiyou.org", "2017-01-14")
	checkErr(err)
	id, _ := res.LastInsertId()

	fmt.Println(id)
	db.Close()
}
func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

 

 

tengine启动脚本

如果你的tengine是独立安装的,想保留原来的nginx,那么这个脚本就可以帮你运行tengine

#! /bin/sh
# chkconfig: 2345 55 25
# Description: Startup script for tengine webserver on Debian. Place in /etc/init.d and
# run 'update-rc.d -f tengine defaults', or use the appropriate command on your
# distro. For CentOS/Redhat run: 'chkconfig --add tengine'

### BEGIN INIT INFO
# Provides:          tengine
# Required-Start:    $all
# Required-Stop:     $all
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: starts the tengine web server
# Description:       starts tengine using start-stop-daemon
### END INIT INFO

# Author:   fy
# website:  http://www.yiyou.org

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
NAME=nginx
NGINX_BIN=/usr/local/tengine/sbin/$NAME
CONFIGFILE=/usr/local/tengine/conf/$NAME.conf
PIDFILE=/usr/local/tengine/logs/$NAME.pid

case "$1" in
    start)
        echo -n "Starting $NAME... "

        if netstat -tnpl | grep -q nginx;then
            echo "$NAME (pid `pidof $NAME`) already running."
            exit 1
        fi

        $NGINX_BIN -c $CONFIGFILE

        if [ "$?" != 0 ] ; then
            echo " failed"
            exit 1
        else
            echo " done"
        fi
        ;;

    stop)
        echo -n "Stoping $NAME... "

        if ! netstat -tnpl | grep -q nginx; then
            echo "$NAME is not running."
            exit 1
        fi

        $NGINX_BIN -s stop

        if [ "$?" != 0 ] ; then
            echo " failed. Use force-quit"
            exit 1
        else
            echo " done"
        fi
        ;;

    status)
        if netstat -tnpl | grep -q nginx; then
            PID=`pidof nginx`
            echo "$NAME (pid $PID) is running..."
        else
            echo "$NAME is stopped"
            exit 0
        fi
        ;;

    force-quit)
        echo -n "Terminating $NAME... "

        if ! netstat -tnpl | grep -q nginx; then
            echo "$NAME is not running."
            exit 1
        fi

        kill `pidof $NAME`

        if [ "$?" != 0 ] ; then
            echo " failed"
            exit 1
        else
            echo " done"
        fi
        ;;

    restart)
        $0 stop
        sleep 1
        $0 start
        ;;

    reload)
        echo -n "Reload service $NAME... "

        if netstat -tnpl | grep -q nginx; then
            $NGINX_BIN -s reload
            echo " done"
        else
            echo "$NAME is not running, can't reload."
            exit 1
        fi
        ;;

    configtest)
        echo -n "Test $NAME configure files... "

        $NGINX_BIN -t
        ;;

    *)
        echo "Usage: $0 {start|stop|force-quit|restart|reload|status|configtest}"
        exit 1
        ;;

esac

 

nginx忽略URL大小写

从windows转到linux 时,发现很多URL连接到web目录是随意的,但是在linux下是区分大小写的,为了解决这个问题,网上也提供很多方案,比如用perl模块,但是nginx官网已经提示用perl模块有内存溢出的危险,所以第一时间放弃了。另一个是使用第三方模块https://github.com/replay/ngx_http_lower_upper_case 因为nginx不支持动态加载,所以还要重新编译一个nginx,第三种方法是通过lua来实现,类似perl,不过lua 比perl要好很多,看看 http://openresty.org/cn/ 就知道有多火了。既然重新编译nginx那么干脆试试tengine 吧。下面是操作的命令,下载,解压,安装,就不详细描述了

yum install -y lua lua-devel

wget http://tengine.taobao.org/download/tengine-2.2.0.tar.gz
tar zxf tengine-2.2.0.tar.gz
cd tengine-2.2.0

./configure --prefix=/usr/local/tengine  --with-http_lua_module
make
make install

 

安装后,在配置文件里加上下面内容,即可(注意这个只是一个参考)

        location / {
            root   /wwwroot/web/;
            index  index.html index.htm;
			if ( $uri ~ [A-Z] ){
				 rewrite_by_lua 'return ngx.redirect(string.lower(ngx.var.uri),ngx.HTTP_MOVED_PERMANENTLY)'; 
			}
        }

参考

https://segmentfault.com/q/1010000000265229

https://my.oschina.net/kisops/blog/151087

zabbix检查硬盘S.M.A.R.T状态[go语言实现]

因为服务器比较多,以前都是喜欢用perl写程序,后来发现部署的时候还要安装perl环境,实在太麻烦了,所以改用go编程,把go编译发后,复制到服务器就可以运行,还真的挻方便的。

之前已经用go写了一个mysql 主从同步,检查从服务器是否出错的程序,如果同步出错就可以在zabbix里警告了,把mysql密码写到程序里,再编译,这样比脚本语言要安全很多,而且不用添加一个用户。

原因是这样的,有很多服务器,有很大一部分是普通硬盘,普通硬盘就很容易出问题,因为服务器上面没有办法做badblock 检查(太耗IO资源了),但是检查S.M.A.R.T状态可以提前预知硬盘故障,还是有点用,好过没有,对吧。

程序是基于linux的,window 改改也能用,主要是先检查/dev/sda…b…c…d  循环检查系统有多少个硬盘,再检查状态,如果程序返回1 则正常,程序返回0 表示有硬盘出问题了。

package main

import (
	"bytes"
	"flag"
	"fmt"
	"log"
	"os"
	"os/exec"
	"strings"
)

func PathExists(path string) (bool, error) {
	_, err := os.Stat(path)
	if err == nil {
		return true, nil
	}
	if os.IsNotExist(err) {
		return false, nil
	}
	return false, err
}
func smartctl(dev string, debug int) int {
	path, err := exec.LookPath("smartctl")
	if err != nil {
		log.Fatal(err)
	}

	cmd := exec.Command(path, "-H", dev)
	var out bytes.Buffer
	cmd.Stdout = &out
	err = cmd.Run()
	if err != nil {
		log.Fatal(err)
	}
	//fmt.Printf("%s\n", out.String())
	if strings.Contains(out.String(), "OK") || strings.Contains(out.String(), "PASSED") {
		if debug == 1 {
			fmt.Printf("%s test ok\n", dev)
		}

		return 1
	}

	return 0
}
func main() {
	debug := flag.Int("debug", 0, "to debug -debug 1 ")
	flag.Parse()

	for _, drive := range "abcd" {
		//dev:=fmt.Sprintf("/dev/sd%s",drive)
		dev := "/dev/sd" + string(drive)
		//fmt.Println(dev)
		if ok, _ := PathExists(dev); ok {
			mystat := smartctl(dev, *debug)
			if mystat ==0 {
				fmt.Print(0)
				os.Exit(-1)
			}
		}

	}
	fmt.Print(1)
}

 

go处理命令行参数

GO实在太方便了,处理命令行参数已经有现成的包,看看下面的例子,用 command -h 还会自动生成使用方法。

package main
 
import (
    "flag"
    "fmt"
)
 
func main() {
    ok := flag.Bool("ok", false, "is ok")
    id := flag.Int("id", 0, "id")
    port := flag.String("port", ":8080", "http listen port")
    var name string
    flag.StringVar(&name, "name", "123", "name")
 
    flag.Parse()
 
    fmt.Println("ok:", *ok)
    fmt.Println("id:", *id)
    fmt.Println("port:", *port)
    fmt.Println("name:", name)
}

 

golang获取硬盘分区剩余空间大小

需求是这样的,想远程执行这个命令,返回各个硬盘分区的剩余空间的大小,当然,剩余空间太小可能就要处理了。

package main

import (
	"fmt"
	"syscall"
//	"strings"
	gofstab "github.com/deniswernert/go-fstab"
)

type DiskStatus struct {
	All  uint64 `json:"all"`
	Used uint64 `json:"used"`
	Free uint64 `json:"free"`
}
const (
	B  = 1
	KB = 1024 * B
	MB = 1024 * KB
	GB = 1024 * MB
)
// disk usage of path/disk
func DiskUsage(path string) (disk DiskStatus) {
	fs := syscall.Statfs_t{}
	err := syscall.Statfs(path, &fs)
	if err != nil {
		return
	}
	disk.All = fs.Blocks * uint64(fs.Bsize)
	disk.Free = fs.Bfree * uint64(fs.Bsize)
	disk.Used = disk.All - disk.Free
	return
}
func main(){
	mounts ,_ :=gofstab.ParseSystem()
	
	for _,val := range mounts{
		//fmt.Printf("%v\n",val.File)
		if val.File == "swap"||val.File == "/dev/shm"||val.File == "/dev/pts"||val.File == "/proc"||val.File =="/sys"{
			continue
		}
		disk := DiskUsage(val.File)
		//fmt.Printf("All: %.2f GB\n", float64(disk.All)/float64(GB))
		//fmt.Printf("Used: %.2f GB\n", float64(disk.Used)/float64(GB))
		//fmt.Printf("Free: %.2f GB\n", float64(disk.Free)/float64(GB))
		diskall:=float64(disk.All)/float64(GB)
		diskfree:= float64(disk.Free)/float64(GB)
		
		dfpercent:=float64(diskfree/diskall)
		fmt.Printf("%s %.2f%%\n",val.File, dfpercent*100)
	}
}

 

golang获取linux硬盘分区空间大小

获得当前分区空间大小

package main

import (
	"fmt"
	"syscall"
)

type DiskStatus struct {
	All  uint64 `json:"all"`
	Used uint64 `json:"used"`
	Free uint64 `json:"free"`
}

// disk usage of path/disk
func DiskUsage(path string) (disk DiskStatus) {
	fs := syscall.Statfs_t{}
	err := syscall.Statfs(path, &fs)
	if err != nil {
		return
	}
	disk.All = fs.Blocks * uint64(fs.Bsize)
	disk.Free = fs.Bfree * uint64(fs.Bsize)
	disk.Used = disk.All - disk.Free
	return
}

const (
	B  = 1
	KB = 1024 * B
	MB = 1024 * KB
	GB = 1024 * MB
)

func main() {
	disk := DiskUsage("/")
	fmt.Printf("All: %.2f GB\n", float64(disk.All)/float64(GB))
	fmt.Printf("Used: %.2f GB\n", float64(disk.Used)/float64(GB))
	fmt.Printf("Free: %.2f GB\n", float64(disk.Free)/float64(GB))
}