python3使用pandas備份mysql資料表

作業系統：CentOS 7.6_x64

Python版本：3.9.12

MySQL版本：5.7.38

日常開發過程中，會遇到mysql資料表的備份需求，需要針對單獨的資料表進行備份並定時清理資料。

今天記錄下python3如何使用pandas進行mysql資料表的備份，我將從以下幾個方面進行展開：

資料表備份邏輯描述
使用的相關介面及檔案
以FreeSWITCH的cdr表為例進行範例
提供範例程式碼及執行效果視訊

一、資料表表備份邏輯

大致流程如下：

備份邏輯是「定時處理」部分的功能。

業務表A：

定義最大預留天數；
定義最大預留條數；

達到最大預留天數後，按時間（6小時為跨度）來刪除，直到滿足最大預留條數的要求。

備份表B：

預留時間可以hard code為2年；
2小時一檢查，當前時間為設定時間（2、3、4、5、6）時，才執行備份操作；

資料搬遷時需要批次提交，以提高效能。

二、相關介面及檔案

pandas版本：2.1.4

sqlalchemy 版本：1.4.39

pymysql 版本：1.0.2

CentOS7環境原始碼安裝python3.9可參考如下文章：

https://www.cnblogs.com/MikeZhang/p/centos7-install-py39-20220704.html

1、使用pandas庫的read_sql_query進行資料讀取，可參考如下檔案：

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html

2、pymysql是一個純python實現的mysql操作庫，安裝及使用起來比較方便，且可跨平臺使用。

檔案地址：https://pymysql.readthedocs.io/en/latest/

3、SQLAlchemy是一個功能強大的Python ORM 工具包，藉助該工具可更便捷的實現資料備份。

官方網址： https://www.sqlalchemy.org/

三、以FreeSWITCH的cdr為例進行範例

FreeSWITCH版本：1.10.9

1、FreeSWITCH設定CDR

fs模組： mod_odbc_cdr

預設設定： conf/autoload_configs/odbc_cdr.conf.xml

如果沒有該模組及組態檔，需要在編譯時安裝該模組，並將原始碼裡面的組態檔複製到conf/autoload_configs目錄，原始碼裡面的組態檔路徑如下：

freeswitch-1.10.9.-release/src/mod/event_handlers/mod_odbc_cdr/conf/autoload_configs/odbc_cdr.conf.xml

FreeSWICH通過ODBC方式支援MySQL可參考這篇文章的第二部分：

https://www.cnblogs.com/MikeZhang/p/dockerFS20230716.html

/etc/odbc.ini 設定範例：

[fsdb]
Description=MySQL freeswitch database
Driver=MySQL
SERVER =192.168.137.1
PORT =3306
USER=root
PASSWORD=123456
DATABASE = fsdb32
OPTION =67108864
CHARSET = UTF8

odbc_cdr.conf.xml設定範例(可根據情況調整所需欄位)：

<configuration name="odbc_cdr.conf" description="ODBC CDR Configuration">
  <settings>
    <!-- <param name="odbc-dsn" value="database:username:password"/> -->
    <param name="odbc-dsn" value="fsdb:root:123456"/>
    <!-- global value can be "a-leg", "b-leg", "both" (default is "both") -->
    <param name="log-leg" value="both"/>
    <!-- value can be "always", "never", "on-db-fail" -->
    <param name="write-csv" value="on-db-fail"/>
    <!-- location to store csv copy of CDR -->
    <param name="csv-path" value="/usr/local/freeswitch/log/odbc_cdr"/>
    <!-- if "csv-path-on-fail" is set, failed INSERTs will be placed here as CSV files otherwise they will be placed in "csv-path" -->
    <param name="csv-path-on-fail" value="/usr/local/freeswitch/log/odbc_cdr/failed"/>
    <!-- dump SQL statement after leg ends -->
    <param name="debug-sql" value="true"/>
  </settings>
  <tables>

    <table name="call_detail">
      <field name="uuid" chan-var-name="uuid"/>
      <field name="call_uuid" chan-var-name="call_uuid"/>
      <field name="caller_number" chan-var-name="caller_id_number"/>
      <field name="callee_number" chan-var-name="destination_number"/>
      <field name="start_time" chan-var-name="start_stamp"/>
      <field name="answer_time" chan-var-name="answer_stamp"/>
      <field name="hangup_time" chan-var-name="end_stamp"/>
      <field name="billsec" chan-var-name="billsec"/>
      <field name="hangup_cause" chan-var-name="hangup_cause"/>
    </table>

  </tables>
</configuration>

需要建立對應的資料表，建表語句如下：

CREATE TABLE `call_detail` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(50) NOT NULL DEFAULT '0',
    `call_uuid` VARCHAR(50) NOT NULL DEFAULT '0',
    `caller_number` VARCHAR(20) NOT NULL DEFAULT '0',
    `callee_number` VARCHAR(50) NOT NULL DEFAULT '0',
    `start_time` DATETIME NULL DEFAULT NULL,
    `answer_time` DATETIME NULL DEFAULT NULL,
    `hangup_time` DATETIME NULL DEFAULT NULL,
    `billsec` INT(11) NOT NULL DEFAULT '0',
    `hangup_cause` VARCHAR(50) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

2、使用pandas進行資料備份

2.1 建立備份表

建表語句如下：

CREATE TABLE `call_detail_history` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(50) NOT NULL DEFAULT '0',
    `call_uuid` VARCHAR(50) NOT NULL DEFAULT '0',
    `caller_number` VARCHAR(20) NOT NULL DEFAULT '0',
    `callee_number` VARCHAR(50) NOT NULL DEFAULT '0',
    `start_time` DATETIME NULL DEFAULT NULL,
    `answer_time` DATETIME NULL DEFAULT NULL,
    `hangup_time` DATETIME NULL DEFAULT NULL,
    `billsec` INT(11) NOT NULL DEFAULT '0',
    `hangup_cause` VARCHAR(50) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

2.2 進行資料表備份

1）編寫備份指令碼

檔名：dataBack.py

範例程式碼如下：

說明：

ConfigData類
讀取組態檔
dataBack函數
以天為單位進行資料備份
dataClean函數
執行資料清理功能（業務表和備份表）
dataCount函數
統計業務表裡面的資料條目
getDbConnStr函數
生成資料庫連線字串
dataRotateBase函數
資料迴圈備份功能的具體實現，執行資料備份、資料清理操作。
dataRotateByDays函數
按天迴圈備份
dataRotateByHours函數
按小時迴圈備份
dataBackTask函數
執行具體的備份任務

完整程式碼可從如下渠道獲取：
關注微信公眾號（聊聊博文，文末可掃碼）後回覆 20231209 獲取。

2）新增組態檔

檔名：default.xml

組態檔範例如下：

<config>
    <cdrReserve>
        <maxDays>15</maxDays>
        <maxItems>100000</maxItems>
    </cdrReserve>
    
    <mysql>
        <host>192.168.137.1</host>
        <port>3306</port>
        <user>root</user>
        <password>123456</password>
        <dbname>fsdb32</dbname>        
    </mysql>
    
</config>

說明：

cdrReserve/maxDays : 最大預留天數

cdrReserve/maxItems : 最大預留條數

mysql ： mysql連線引數

3）編寫啟動指令碼

檔名稱：start.sh

範例如下：

#! /bin/bash

pydir=/root/py39env
export CFLAGS="-I$pydir/include"
export LDFLAGS="-L$pydir/lib"
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$pydir/lib

$pydir/bin/python3.9 dataBack.py -f default.xml

說明：

這裡使用的是自定義python環境，python版本是3.9.12。

CentOS7環境原始碼安裝python3.9可參考如下文章：

https://www.cnblogs.com/MikeZhang/p/centos7-install-py39-20220704.html

四、執行效果

執行效果如下：

執行效果視訊可從如下渠道獲取（修改資料時間進行測試）：

關注微信公眾號（聊聊博文，文末可掃碼）後回覆 2023120901 獲取。

好，就這麼多了，希望對你有幫助。