如果您想了解mssql用cp实现数据表按年月归档备份的相关知识，那么本文是一篇不可错过的文章，我们将对sqlserver按月份汇总进行全面详尽的解释，并且为您提供关于centos下docker自动备份

如果您想了解mssql 用 cp 实现数据表按年月归档备份的相关知识，那么本文是一篇不可错过的文章，我们将对sql server 按月份汇总进行全面详尽的解释，并且为您提供关于centos下docker自动备份 mysql、mssql、ibatis 自动生成键 selectkey（Oracle、MYSQL、MSSQL、SQLITE）、Linux 系统下 ThinkPHP5 链接 MsSQL、lnmp 环境里安装 mssql 及 mssql 的 php 扩展的有价值的信息。

• mssql 用 cp 实现数据表按年月归档备份（sql server 按月份汇总）
• centos下docker自动备份 mysql、mssql
• ibatis 自动生成键 selectkey（Oracle、MYSQL、MSSQL、SQLITE）
• Linux 系统下 ThinkPHP5 链接 MsSQL
• lnmp 环境里安装 mssql 及 mssql 的 php 扩展

mssql 用 cp 实现数据表按年月归档备份（sql server 按月份汇总）

工作上用到了 mssql，为了实现数据表按年月归档备份，特意写了个 python 的 demo 测试了一下，效率不错，归档备份规则、归档记录采用的是 sqlite3 数据库，可以实现任意表的归档操作，只需要在配置表内添加一条记录即可。以下是 python 实现的源码：

import os
import logging
import pymssql
import sys
import decimal
import sqlite3
import time
import datetime
import uuid


def mylogger():
    # 加入日志
    logger = logging.getLogger(__name__)
    formatter = logging.Formatter(''%(asctime)s %(levelname)s :%(message)s'')
    file_handler = logging.FileHandler("archive.log")
    file_handler.setFormatter(formatter)
    # 控制台日志
    console_handler = logging.StreamHandler(sys.stdout)
    console_handler.setFormatter(formatter)
    # 为logge添加具体的日志处理器
    logger.addHandler(file_handler)
    logger.addHandler(console_handler)
    logger.setLevel(logging.INFO)
    return logger

class MSSQLArchive:

    # 创建数据库连接
    def createconn(dbtype,db_server,db_database,db_user,db_password):
        try:
            if dbtype == ''sqlite3'':
                conn = sqlite3.connect(db_database)#''DATAARCHIVE.DB3''
            elif dbtype == ''mssql'':
                conn = pymssql.connect(server=db_server,user=db_user,password=db_password,database=db_database,timeout=20)
            else:
                conn = None
            return conn
        except Exception as err:
            logger.error(err)
            return None
    #关闭数据库连接。
    def closeconn(conn):
        conn.close()
    # 查询数据
    def queryRows(conn,sql):
        try:
            cursor = conn.cursor()
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            return rows
        except Exception as err:
            logger.error(err)

    def mkdir(szpath):
        try:
            szpath = szpath.strip()
            szpath = szpath.rstrip("/")
            if not os.path.exists(szpath):
                os.makedirs(szpath)
            return True
        except Exception as err:
            logger.error(err)
            return False


def archive_all():
    logger.info("Start archive all tables...")
    szsql = "select DB_IP,DB_NAME,DB_USER,DB_PASSWORD from TC_DATA_ARCHIVE_DB where USED_FLAG=1"
    db_rows = msarchive.queryRows(sqlite_conn,szsql)
    szsql = "select CONFIG_ID,TABLE_NAME,TABLE_NOTE,PRIMAY_FIELD,ARCHIVE_FIELD,ARCHIVE_METHOD," \
            "AECHIVE_PERIOD,ARCHIVE_WHERE,ARCHIVED_UPDATE,ARCHIVE_INTERVAL,MUST_ENCRYPT," \
            "FILE_TYPE,ARCHIVED_PATH,LAST_DT,ARCHIVED_DELETE,IS_ENABLED," \
            "ARCHIVE_ORDER,ARCHIVE_SCHEDULED_TIMES,ARCHICE_FINISHED_TIMES " \
            "from TC_DATA_ARCHIVE_CONFIG where IS_ENABLED=1 order by ARCHIVE_ORDER"
    table_rows = msarchive.queryRows(sqlite_conn,szsql)
    for db_row in db_rows:
        logger.info("Start connecting mssql:"+db_row[0]+ " "+db_row[1])
        try:
            db_conn = msarchive.createconn(dbtype=''mssql'',db_database=db_row[1],db_server=db_row[0],db_user=db_row[2],db_password=db_row[3])
        except Exception as err:
            logger.error(err)
            continue
        for table_row in table_rows:
            logger.info("Start archiving table:" + table_row[1])
            szsql = "select max(FIELD_END_DT) from TD_DATA_ARCHIVE_RECORD where CONFIG_ID="+str(table_row[0])+" and TABLE_NAME=''"+table_row[1]+"''"
            #print(szsql)
            rows = msarchive.queryRows(sqlite_conn, szsql)
            sdt1 = rows[0][0]
            #print("std1=",sdt1)
            if sdt1 == None:
                szsql = "select min(" + table_row[4]+") from "+table_row[1]+""
                #print(szsql)
                rows = msarchive.queryRows(db_conn, szsql)
                #print(rows)
                dt1 = rows[0][0]
                if dt1 == None:
                    logger.info("Table:" + table_row[1] +" no record!")
                    continue

            else:
                dt1 = datetime.datetime.strptime(sdt1, ''%Y-%m-%d %H:%M:%S'').date()
            #print("1111",dt1)
            archive_dt = datetime.datetime.now()

            #print(dt1.year)
            if table_row[5] == 2:
                szsql = "select dateadd(month,-"+str(table_row[9])+",getdate())"
                rows = msarchive.queryRows(db_conn, szsql)
                dt2 = rows[0][0]
                #print("dt2=",dt2)
                while (int(dt1.year)*100 + int(dt1.month)) < (int(dt2.year)*100 + int(dt2.month)):
                    #print("std11111=", sdt1)
                    if sdt1 != None:
                        szsql = "select dateadd(month,1,''"+str(dt1)+"'')"
                        #print(szsql)
                        rows = msarchive.queryRows(db_conn, szsql)
                        dt1 = rows[0][0]
                    sdt1 = ''deal''

                    #print("dt1=",dt1)
                    szsql = "select count(*) from " + table_row[1] + " where datediff(month," + table_row[4] + ",''" + str(dt1) + "'')=0"
                    rows = msarchive.queryRows(db_conn, szsql)
                    rows_num = rows[0][0]
                    if rows_num > 0:
                        logger.info("Table:" + table_row[1] +" have "+str(rows_num)+" records to archived")

                        szsql = "select min(" + table_row[4]+") from " + table_row[1] + " where datediff(month," + table_row[4] + ",''" + str(dt1) + "'')=0"
                        rows = msarchive.queryRows(db_conn, szsql)
                        dt_begin = rows[0][0]
                        szsql = "select max(" + table_row[4]+") from " + table_row[1] + " where datediff(month," + table_row[4] + ",''" + str(dt1) + "'')=0"
                        rows = msarchive.queryRows(db_conn, szsql)
                        dt_end = rows[0][0]
                        #print(''TOtal:='',rows[0][0])
                        myuid = str(uuid.uuid4())
                        myuid = ''''.join(myuid.split(''-''))
                        szsql = "select * from "+table_row[1]+" where datediff(month," + table_row[4]+",''"+str(dt1)+"'')=0"
                        szpath = str(table_row[12]) + "/" + str(archive_dt.year) + "/"+ str(archive_dt.month) + "/" + str(archive_dt.day) + "/" + str(db_row[0]) + "/" + str(db_row[1])
                        #print(szpath)
                        msarchive.mkdir(szpath)
                        szfile = szpath + "/"+table_row[1]+ "_" + myuid +".fmt"

                        r = os.popen(''BCP "%s" format nul -f %s -c -S"%s" -U"%s" -P"%s" -d"%s"'' % (table_row[1], szfile, db_row[0], db_row[2], db_row[3], db_row[1]))
                        #print(r.read())
                        r.close()
                        szfile = szpath + "/" +table_row[1]+ "_" + myuid + ".bcp"
                        szsql1 = "insert into TD_DATA_ARCHIVE_RECORD(ARCHIVE_ID,CONFIG_ID,TABLE_NAME,ORG_CODE,ARCHIVED_FILE,FILE_TYPE," \
                                "FILE_SIZE,ARCHIVE_BEGIN_DT,ARCHIVE_END_DT,ARCHIVE_USER," \
                                "ARCHIVE_METHOD,ARCHIVE_WHERE,ARCHIVE_TOTAL_ROWS,DEALED_ROWS,ARCHIVE_FIELD," \
                                "FIELD_BEGIN_DT,FIELD_END_DT,ARCHIVE_STATUS,TRANSFER_MARK) values(" \
                                "''"+myuid+"'',"+str(table_row[0])+",''"+table_row[1]+"'',0,''"+szfile+"'',''TXT'',0," \
                                "''"+str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))+"'','''',''SYSTEM'',"+str(table_row[5])+",'''',"+str(rows_num)+",0,''"+table_row[4]+"''," \
                                "''"+str(dt_begin)+"'',''"+str(dt_end)+"'',0,0)"
                        sqlite_conn.cursor().execute(szsql1)
                        sqlite_conn.commit()
                        r = os.popen(''BCP "%s" queryout %s -c -S"%s" -U"%s" -P"%s" -d"%s"'' % (szsql,szfile,db_row[0],db_row[2],db_row[3],db_row[1]))
                        r.close()
                        size = os.path.getsize(szfile)
                        szsql1 = "update TD_DATA_ARCHIVE_RECORD set ARCHIVE_END_DT=''"+str(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))+"''," \
                                "ARCHIVE_STATUS=2,TRANSFER_MARK=0,FILE_SIZE="+str(size)+",DEALED_ROWS="+str(rows_num)+" " \
                                "where ARCHIVE_ID=''"+myuid+"''"
                        sqlite_conn.cursor().execute(szsql1)
                        sqlite_conn.commit()
                    else:
                        logger.info("Table:" + table_row[1] + " have null record to archived")
                    continue

            logger.info("Archive table finished:" + table_row[1])

        msarchive.closeconn(db_conn)
        logger.info("Archive Database Finished:" + db_row[0] + " " + db_row[1])

if __name__ == ''__main__'':
    logger = mylogger()
    msarchive = MSSQLArchive
    try:
        sqlite_conn = msarchive.createconn(dbtype=''sqlite3'',db_database=''DATAARCHIVE.DB3'',db_server='''',db_user='''',db_password='''')
        szsql = "select DB_IP,DB_NAME,DB_USER,DB_PASSWORD from TC_DATA_ARCHIVE_DB where USED_FLAG=1"
        db_rows = msarchive.queryRows(sqlite_conn, szsql)

        if len(db_rows) == 0:
            mssql_connected = False
        else:
            str_input = input("Do you want to reset your mssql connection?[No/Yes]")
            if str_input == "Yes":
                mssql_connected = False
            else:
                mssql_connected = True
        while mssql_connected == False:
            print("MSSQL Config error!")
            ip = input("Please Input MSSQL Server IP:")
            user = input("Please Input MSSQL Database User:")
            pass_str = input("Please Input MSSQL Database Password:")
            db = input("Please input MSSQL Database Name:")
            #print(ip, user, pass_str, db)
            try:
                db_conn = msarchive.createconn(dbtype=''mssql'', db_database=db, db_server=ip, db_user=user,
                                               db_password=pass_str)
                if db_conn != None:
                    mssql_connected = True
                    msarchive.closeconn(db_conn)
                    szsql = "delete from TC_DATA_ARCHIVE_DB"
                    sqlite_conn.cursor().execute(szsql)
                    sqlite_conn.commit()
                    szsql = "insert into TC_DATA_ARCHIVE_DB(DB_IP,DB_NAME,DB_USER,DB_PASSWORD,USED_FLAG) values(" \
                            "''" + ip + "'',''" + db + "'',''" + user + "'',''" + pass_str + "'',1)"
                    sqlite_conn.cursor().execute(szsql)
                    sqlite_conn.commit()

            except Exception as err:
                logger.error(err)

        while True:
            archive_all()
            logger.info(''sleep 5 sec...'')
            time.sleep(5)
        msarchive.closeconn(sqlite_conn)
    except Exception as err:
        logger.error(err)

centos下docker自动备份 mysql、mssql

# mysql

docker exec -i mysql bash <<''EOF''
 
mkdir /backup/mysql/$(date +%Y%m%d)
 
# 备份指定数据库
mysqldump -uroot -proot test > /backup/mysql/$(date +%Y%m%d)/test_$(date +%Y%m%d_%H%M%S).sql
 
# 备份所有数据库
# mysqldump --no-defaults --events --all-databases -uroot -padmin > /backup/mysql/$(date +%Y%m%d)/all_$(date +%Y%m%d_%H%M%S).sql
 
exit
 
EOF
 
mkdir /backup/mysql/$(date +%Y%m%d)
 
docker cp mysql:/backup/mysql/$(date +%Y%m%d) /backup/mysql/



# 新增crontab任务：
# crontab -e
# 每天2点执行脚本
# 0 2 * * * bash /mnt/docker/backup/mysql.sh
# 重启crontabd 服务
# service crond restart

# https://blog.csdn.net/MR1269427885/article/details/82978311

或

db_user="root"
db_passwd="root"
db_name="db_test"
# the directory for story your backup file.you shall change this dir
backup_dir="/usr/software/backup/mysqlbackup"
# date format for backup file (dd-mm-yyyy)
time="$(date +"%Y%m%d%H%M%S")"     

mysqldump -u$db_user  -p$db_passwd $db_name  > "$backup_dir/$db_name"_"$time.sql"

mysql备份文件定期删除

------------------------------附加一个mysql备份定时删除--------------------------

1.准备一个脚本文件 rmbak.sh

backup_dir="/data/backup222/4a_db_backup"
#删除七天之前的备份
#find $backup_dir -name $db_name"*.sql.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1
#删除一分钟之前的备份
find $backup_dir -name $db_name"*.sql.gz" -type f -mmin +1 -exec rm -rf {} \; > /dev/null 2>&1

-type f 表示查找普通类型的文件，f 表示普通文件，可不写
-mtime +7 按照文件的更改时间来查找文件，+7表示文件更改时间距现在7天以前;如果是-mmin +7表示文件更改时间距现在7分钟以前
-exec rm {} ; 表示执行一段shell命令，exec选项后面跟随着所要执行的命令或脚本，然后是一对{ }，一个空格和一个\，最后是一个分号;
/dev/null 2>&1 把标准出错重定向到标准输出，然后扔到/DEV/NULL下面去。通俗的说，就是把所有标准输出和标准出错都扔到垃圾桶里面；其中的& 表示让该命令在后台执行

2.编辑crontab -e,添加下面，每分钟，如，每分钟一次删除

*/1 * * * * /data/backup222/rmbak.sh

3.重启：service crond restart

注意，rmbak.sh文件的权限，r，保证 定时任务能读取脚本内容，x,保证脚本有可执行权限

MSSQL:

#!/bin/bash
#设置mysql备份目录
folder=/var/opt/mssql/backup
cd $folder
day=`date +%Y%m%d`
#rm -rf $day
#mkdir $day
#cd $day
#数据库服务器，一般为localhost
host=localhost
#用户名
user=sa
#密码
password=''123456''
#要备份的数据库
db=MTS

#数据要保留的天数
days=7

#由于crontab命令是没环境变量，所以sqlcmd命令，要用全路径,否则定时执行会执行失败
/opt/mssql-tools/bin/sqlcmd -H$host -U$user -P$password -Q "
BACKUP DATABASE TESTDB  
TO DISK = ''/var/opt/mssql/backup/TESTDB"$day".bak''   
WITH FORMAT;
GO" 


#删除之前的备份
#cd ..
day=`date -d "$days days ago" +%Y%m%d`
rm -rf "TESTDB"$day".bak"
echo "remove TESTDB"$day".bak"

# https://blog.csdn.net/fuck487/article/details/79295314
# https://blog.51cto.com/svsky/2121967

ps:

分　时　日　月　周　执行命令
第 1 列分钟 1～59，每分钟用 * 或者*/1表示，整点分钟数为00或0
第 2 列小时 1～23（0 表示 0 点）
第 3 列日 1～31
第 4 列月 1～12
第 5 列星期 0～6（0 表示星期天）
第 6 列要运行的命令
0 3 * * * /backup.sh，此命令表示在每天的凌晨三点执行一次脚本，可自行调整时间

ibatis 自动生成键 selectkey（Oracle、MYSQL、MSSQL、SQLITE）

        我们在数据库插入一条数据的时候，经常是需要返回插入这条数据的主键。但是数据库供应商之间生成主键的方式都不一样。有些是预先生成 (pre-generate) 主键的，如 Oracle 和 PostgreSQL；有些是事后生成 (post-generate) 主键的，如 MySQL 和 SQL Server。但不管是哪种方式，我们都可以用 ibatis 的节点来获取语句所产生的主键。

oracle 例子:

<insert id="insertProduct-ORACLE" parameterClass="product">  

    <selectKey resultClass="int" type="pre" keyProperty="id" >  

        SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL  

    </selectKey>  

    insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values (#id#,#description#)  

</insert>

sql-server 例子:

<insert id="insertProduct-MS-SQL" parameterClass="product">  

    insert into PRODUCT (PRD_DESCRIPTION) values (#description#)  

    <selectKey resultClass="int" type="post" keyProperty="id" >  

        select @@IDENTITY as value  

    </selectKey>  

</insert>

mysql 例子:

<insert id="insertProduct-MYSQL" parameterClass="product">  

    insert into PRODUCT (PRD_DESCRIPTION) values (#description#)  

    <selectKey resultClass="int" type="post" keyProperty="id" >  

        select LAST_INSERT_ID() as value  

    </selectKey>  

</insert>

SQLite 例子:

<insert id="Create" parameterClass="Subject">
      INSERT INTO SUBJECT
      (SubjectName,QuestionCount,IsNowPaper)
      VALUES(#SubjectName#,#QuestionCount#,#IsNowPaper#)
      <selectKey resultClass="int" type="post" property="SubjectId">
        SELECT seq
        FROM sqlite_sequence
        WHERE (name = ''SUBJECT'')
      </selectKey>
    </insert>
注意：name = ''SUBJECT''中SUBJECT为表名称

Linux 系统下 ThinkPHP5 链接 MsSQL

案例

CentOS6.8 系统 ThinkPHP5 链接 MsSQL 数据库。

分析

ThinkPHP5 提供了 Mysql、Pgsql、Sqlite 和 Sqlsrv 四种数据库驱动。Window 系统下有现成的 php_sqlsrv.dll 扩展可用，但 Linux 系统中没有 (本人没有找到)。

尝试 1 未成功，写来以备其它用途

安装 freetds

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
tar -zxvf freetds-patched.tar.gz
cd freetds-*
./configure --prefix=/usr/local/freetds --with-tdsver=auto --enable-msdblib --with-gnu-ld --enable-shared --enable-static
make && make install

安装 mssql

wget http://cn2.php.net/distributions/php-5.6.22.tar.gz
tar -zxvf php-5.6.22.tar.gz
cd php-*/ext/mssql
/usr/local/php/bin/phpize
./configure --with-php-config=/usr/local/php/bin/php-config --with-mssql=/usr/local/freetds
make && make install 

修改 php.ini，添加如下配置 (路径请根据环境不同而定)

extension="/usr/local/php/lib/php/extensions/no-debug-non-zts-20131226/mssql.so"

修改 ThinkPHP5 配置文件 database.php

// 数据库类型
''type''           => ''sqlsrv'',
// 服务器地址
''hostname''       => ''192.168.1.210'',
// 数据库名
''database''       => ''dbname'',
// 用户名
''username''       => ''sa'',
// 密码
''password''       => ''123456'',
// 端口
''hostport''       => ''1433'',

测试

这种方式在 ThinkPHP 中是行不通的，在其它应用环境可以。

尝试 2 成功

安装 freetds

wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
tar -zxvf freetds-patched.tar.gz
cd freetds-*
./configure --prefix=/usr/local/freetds --with-tdsver=auto --enable-msdblib --with-gnu-ld --enable-shared --enable-static
make && make install

安装 pdo_dblib

wget http://cn2.php.net/distributions/php-5.6.22.tar.gz
tar -zxvf php-5.6.22.tar.gz
cd php-*/ext/pdo_dblib
/usr/local/php/bin/phpize
./configure --with-php-config=/usr/local/php/bin/php-config --with-pdo-dblib=/usr/local/freetds
make && make install 

修改 php.ini，添加如下配置 (路径请根据环境不同而定)

extension="/usr/local/php/lib/php/extensions/no-debug-non-zts-20131226/pdo_dblib.so"

修改 ThinkPHP5 配置文件 database.php

// 数据库类型
''type''           => ''dblib'',
// 服务器地址
''hostname''       => ''192.168.1.210'',
// 数据库名
''database''       => ''dbname'',
// 用户名
''username''       => ''sa'',
// 密码
''password''       => ''123456'',
// 端口
''hostport''       => ''1433'',

下载 ThinkPHP 数据库驱动 Dblib 文件

将文件拷贝到 thinkphp/library/think/db 目录。

测试

成功。

lnmp 环境里安装 mssql 及 mssql 的 php 扩展

小活中用到 mssql, 于是在自己 lnmp 环境中安装各 mssql 数据库

步骤如下： 

源码编译安装

cd /download (把php-mssql扩展下载到download目录里)
wget http://cn2.php.net/distributions/php-5.6.30.tar.gz （下载扩展文件，这里要根据你环境中运行的php版本选择对应的扩展版本下载，我这里php是5.6.30的 所以php-mssql扩展下载对应的版本）
tar -zxvf php-5.6.30.tar.gz
cd /php-5.6.30/ext/mssql

 /usr/local/php/bin/phpize

./configure --with-php-config=/usr/local/php/bin/php-config --with-mssql=/usr/local/freetds/ 

 make && make install 

我们今天的关于mssql 用 cp 实现数据表按年月归档备份和sql server 按月份汇总的分享已经告一段落，感谢您的关注，如果您想了解更多关于centos下docker自动备份 mysql、mssql、ibatis 自动生成键 selectkey（Oracle、MYSQL、MSSQL、SQLITE）、Linux 系统下 ThinkPHP5 链接 MsSQL、lnmp 环境里安装 mssql 及 mssql 的 php 扩展的相关信息，请在本站查询。