今天，產品那邊發(fā)來需求，說有個 APP 的 IOS 版本下載包需要新增 https 協(xié)議，在景安購買了免費的 SSL 證書。當我往 nginx 上新增 ssl 時，發(fā)現(xiàn)服務器上的 nginx 居然沒編譯 SSL 模塊！

看了下舊版本 nginx 的 configure 選項：

  1 linux-gz215:# /usr/local/sbin/nginx -V  2 nginx version: nginx/1.0.11  3 built by gcc 4.1.2 20070115 (prerelease) (SUSE Linux)  4 configure arguments: --prefix=/usr/local/nginx

可能是出于最小化安裝的考慮，就只有一個 prefix 參數，而版本也挺低的，干脆就升級一下好了！由于服務器處于在線服務狀態(tài)，為了避免升級帶來的不良影響，我決定給 nginx 來個平滑升級，結果發(fā)現(xiàn)還真是如絲般順滑。。。

下面記錄一下平滑升級和新增模塊的過程。

一、半自動平滑升級

所謂半自動，其實就是在最后遷移的時候使用源碼自帶的升級命令：make upgrade 來自動完成。

①、按需編譯新版本的 nginx

根據需求，常規(guī)編譯新版本 nginx，不過只要執(zhí)行到 make 就打住，不要 make install！

  #下載1.5.7版本，并解壓  cd /usr/local/src  wget http://nginx.org/download/nginx-1.6.0.tar.gz  tar zxvf nginx-1.6.0.tar.gz  cd nginx-1.6.0    #根據實際需要新增的模塊，先準備所需文件(其實只需要解壓即可，全部安裝，后面編譯就可以不指定路徑了)：  #1. 安裝pcre：  wget ftp://ftp.csx.cam.ac.uk/pub/software/programming/pcre/pcre-8.34.tar.gz   tar -zxvf pcre-8.34.tar.gz  cd pcre-8.34  ./configure && make && make install    #2. 安裝zlib：  cd /usr/local/src  wget http://zlib.net/zlib-1.2.8.tar.gz  tar -zxvf zlib-1.2.8.tar.gz  cd zlib-1.2.8  ./configure && make && make install    #3. 安裝openssl：  cd /usr/local/src  wget http://www.openssl.org/source/openssl-1.0.1c.tar.gz  tar -zxvf openssl-1.0.1c.tar.gz  cd openssl-1.0.1c  ./configure && make && make install    #加上所需參數開始編譯：  ./configure --user=www --group=www   --prefix=/usr/local/nginx   --with-http_ssl_module   --with-openssl=/usr/local/src/openssl-1.0.1c  #對應openssl源碼解壓后的路徑,下同(pcre,zlib)  --with-http_stub_status_module   --with-pcre   --with-pcre=/usr/local/src/pcre-8.21   --with-zlib=/usr/local/src/zlib-1.2.8    #執(zhí)行make編譯，但是不要執(zhí)行make install    make

②、重命名 nginx 舊版本二進制文件，即 sbin 目錄下的 nginx（期間 nginx 并不會停止服務?。?/h3>

  linux-gz215:/usr/local/src/nginx-1.6.0 # mv /usr/local/nginx/sbin/nginx /usr/local/nginx/sbin/nginx.old

③、然后拷貝一份新編譯的二進制文件：

  linux-gz215:/usr/local/src/nginx-1.6.0 # cp objs/nginx /usr/local/nginx/sbin/

④、在源碼目錄執(zhí)行 make upgrade 開始升級：

  linux-gz215:/usr/local/src/nginx-1.6.0 # make upgrade     #下面是make upgrade命令的打印信息：  /usr/local/nginx/sbin/nginx -t  nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok  nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful  kill -USR2 `cat /usr/local/nginx/logs/nginx.pid`  sleep 1  test -f /usr/local/nginx/logs/nginx.pid.oldbin  kill -QUIT `cat /usr/local/nginx/logs/nginx.pid.oldbin`     #最后確認一下nginx進程,可以發(fā)現(xiàn)有2個主進程，并且有正在關閉的進程（shutting down）：  linux-gz215:/usr/local/src/nginx-1.6.0 # ps aux | grep nginx  root 969 0.0 0.3 8260 1844  Ss Dec09 0:01 nginx: master process /usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf  www 4196 0.1 2.5 19112 12872  S 14:52 0:00 nginx: worker process is shutting down  www 4260 0.1 2.5 19112 12872  S 14:52 0:00 nginx: worker process is shutting down  www 4257 0.1 2.5 19112 12872  S 14:52 0:00 nginx: worker process is shutting down  root 4663 0.0 0.3 5488 1900  S 14:58 0:00 nginx: master process /usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf     #過一段時間后，再次確認nginx進程，可以發(fā)現(xiàn)老進程已自動退出了（存在一段時間是因為舊進程還有未結束的服務）  root 969 0.0 0.3 8260 1844  Ss Dec09 0:01 nginx: master process /usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf  www       4665  0.1  2.4  16508 12444         S    14:58   0:01 nginx: worker process

完成后，最后確認一下 nginx -V ：

  linux-gz215:~ # /usr/local/nginx/sbin/nginx -V  nginx version: nginx/1.6.0  built by gcc 4.1.2 20070115 (prerelease) (SUSE Linux)  TLS SNI support enabled  configure arguments: --user=www --group=www --prefix=/usr/local/nginx --with-http_ssl_module --with-openssl=/usr/local/src/openssl-1.0.1c --with-http_stub_status_module --with-pcre --with-pcre=/usr/local/src/pcre-8.21 --with-zlib=/usr/local/src/zlib-1.2.8

正常了，平滑升級成功！

二、純手動平滑升級

純手動模式，指的是在最后做遷移的時候，全部使用手動命令來搞定，避免編譯可能存在不一致的參數啥的。

實際上，在 make 之后，我們可以查看 nginx 源碼目錄下的 Makefile 內容如下：

  default:        build    clean:          rm -rf Makefile objs    build:          $(MAKE) -f objs/Makefile          $(MAKE) -f objs/Makefile manpage    install:          $(MAKE) -f objs/Makefile install    upgrade:          /usr/local/nginx/sbin/nginx -t            kill -USR2 `cat /usr/local/nginx/logs/nginx.pid`          sleep 1          test -f /usr/local/nginx/logs/nginx.pid.oldbin            kill -QUIT `cat /usr/local/nginx/logs/nginx.pid.oldbin`

所以，說白了純手動就是執(zhí)行 upgrade 標簽下的命令行而已，實際上只要確認 Makefile 下的命令路徑都是正確的，用命令自動遷移是沒有任何問題的。

總是有人會不放心的，喜歡手動一步一步的搞定，我也來整理下純手動步驟：

①~③和半自動一樣，按常規(guī)步驟先編譯 nginx，不過只執(zhí)行到 make 就打住，然后將舊的 sbin 下的 nginx 文件移走，再將編譯得到的 objs 目錄下的 nginx 文件放到原來的 sbin 目錄。

④、測試新版本的 nginx 是否正常：

  [root@Mars_Server nginx-1.6.0]# /usr/local/nginx/sbin/nginx -t  nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok #OK,沒有問題！  nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful

⑤、給舊 nginx 發(fā)送平滑遷移信號（若不清楚 pid 路徑就用可用命令(2)）：

  #可用命令(1)：  kill -USR2 `cat /usr/local/nginx/logs/nginx.pid`    #可用命令(2)：  kill -USR2 `ps aux | grep "nginx: master process" | grep -v grep | awk '{print $2}'`

Ps：后面其實就是舊 nginx 的 pid，所以先用 ps aux 找到正在運行的 nginx 主進程 pid,再執(zhí)行 kill -USR2 PID 值亦可。

⑥、等待舊版本 Nginx 的 pid 變?yōu)?oldbin（執(zhí)行如下命令查看是否生成）

  test -f /usr/local/nginx/logs/nginx.pid.oldbin && echo OK!

⑦、 從容關閉舊版本的 Nginx 進程

  kill –WINCH `cat /usr/local/nginx/log/nginx.oldbin`

此時，舊的工作進程就都會慢慢隨著任務執(zhí)行完畢而退出，新版的 Nginx 的工作進程會逐漸取代舊版工作進程。

⑧、此時，不重載配置啟動舊工作進程（個人感覺是為了將任務完全切換到新的 nginx 上）

  kill –HUP `cat /url/local/nginx/log/nginx.oldbin`

⑨、結束工作進程，完成此次升級操作：

  kill –QUIT `cat /usr/local/nginx/log/nginx.oldbin`

⑩、最后，驗證 nginx 是否升級成功：

  linux-gz215:~ # /usr/local/nginx/sbin/nginx -V  nginx version: nginx/1.6.0 #沒問題  built by gcc 4.1.2 20070115 (prerelease) (SUSE Linux)  TLS SNI support enabled  configure arguments: --user=www --group=www --prefix=/usr/local/nginx --with-http_ssl_module --with-openssl=/usr/local/src/openssl-1.0.1c --with-http_stub_status_module --with-pcre --with-pcre=/usr/local/src/pcre-8.21 --with-zlib=/usr/local/src/zlib-1.2.8

特意測試了下純手動的做法，下面是我的操作記錄，僅供參考：

  linux-gz215:/usr/local/nginx # cd sbin/  linux-gz215:/usr/local/nginx/sbin # ll  總計 6828  -rwxr-xr-x 1 root root 6975582 2014-12-23 16:44 nginx  linux-gz215:/usr/local/nginx/sbin # mv nginx nginx.old  linux-gz215:/usr/local/nginx/sbin #   linux-gz215:/usr/local/nginx/sbin # cp /usr/local/src/nginx-1.5.7/objs/  autoconf.err        nginx               ngx_auto_config.h   ngx_modules.c       src/                Makefile            nginx.8             ngx_auto_headers.h  ngx_modules.o         linux-gz215:/usr/local/nginx/sbin # cp /usr/local/src/nginx-1.5.7/objs/nginx .  linux-gz215:/usr/local/nginx/sbin # ll  總計 13656  -rwxr-xr-x 1 root root 6975582 2014-12-23 16:57 nginx  -rwxr-xr-x 1 root root 6975582 2014-12-23 16:44 nginx.old  linux-gz215:/usr/local/nginx/sbin # /usr/local/nginx/sbin/nginx -t -c /usr/local/nginx/conf/nginx.conf  nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok  nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful  linux-gz215:/usr/local/nginx/sbin # ps aux | grep nginx   root     18152  0.0  0.0   9264  2588         S    16:45   0:00 nginx: master process ./nginx  nobody   18331  1.0  0.0  13360  5488         S    16:52   0:04 nginx: worker process  nobody   18332  1.2  0.0  13360  5488         S    16:52   0:05 nginx: worker process  nobody   18333  0.6  0.0  13360  5488         S    16:52   0:02 nginx: worker process  nobody   18334  0.8  0.0  13360  5488         S    16:52   0:03 nginx: worker process  nobody   18335  0.4  0.0  13360  5488         S    16:52   0:01 nginx: worker process  nobody   18336  0.1  0.0  13360  5488         S    16:52   0:00 nginx: worker process  nobody   18337  0.3  0.0  13828  5844         S    16:52   0:01 nginx: worker process  nobody   18338  0.2  0.0  13360  5488         S    16:52   0:01 nginx: worker process  root     18473  0.0  0.0   4952   796 pts/1    S+   16:58   0:00 grep nginx  linux-gz215:/usr/local/nginx/sbin # kill -USR2 18152  linux-gz215:/usr/local/nginx/sbin # ps aux | grep nginx  root     18152  0.0  0.0   9264  2588         S    16:45   0:00 nginx: master process ./nginx  nobody   18331  0.9  0.0  13360  5488         S    16:52   0:04 nginx: worker process  nobody   18332  1.2  0.0  13360  5488         S    16:52   0:05 nginx: worker process  nobody   18333  0.5  0.0  13360  5488         S    16:52   0:02 nginx: worker process  nobody   18334  0.8  0.0  13360  5488         S    16:52   0:03 nginx: worker process  nobody   18335  0.4  0.0  13360  5488         S    16:52   0:01 nginx: worker process  nobody   18336  0.2  0.0  13792  5840         S    16:52   0:01 nginx: worker process  nobody   18337  0.5  0.0  13464  5504         S    16:52   0:02 nginx: worker process  nobody   18338  0.2  0.0  13360  5488         S    16:52   0:01 nginx: worker process  root     18474  0.0  0.0   9124  2460         S    16:59   0:00 nginx: master process ./nginx  nobody   18475  5.0  0.0  13364  5424         S    16:59   0:00 nginx: worker process  nobody   18476  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18477  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18478  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18479  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18480  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18481  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18482  0.0  0.0  13136  5032         S    16:59   0:00 nginx: worker process  root     18484  0.0  0.0   4960   812 pts/1    S+   16:59   0:00 grep nginx  linux-gz215:/usr/local/nginx/sbin # kill -WINCH 18152  linux-gz215:/usr/local/nginx/sbin # ps aux | grep nginx  root     18152  0.0  0.0   9264  2588         S    16:45   0:00 nginx: master process ./nginx  nobody   18334  0.7  0.0  13360  5488         S    16:52   0:03 nginx: worker process is shutting down  nobody   18337  0.5  0.0  13360  5488         S    16:52   0:02 nginx: worker process is shutting down  root     18474  0.0  0.0   9124  2460         S    16:59   0:00 nginx: master process ./nginx  nobody   18475  2.3  0.0  13672  5724         S    16:59   0:01 nginx: worker process  nobody   18476  0.0  0.0  13136  5240         S    16:59   0:00 nginx: worker process  nobody   18477  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18478  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18479  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18480  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18481  0.0  0.0  13136  5240         S    16:59   0:00 nginx: worker process  nobody   18482  0.0  0.0  13136  5240         S    16:59   0:00 nginx: worker process  root     18486  0.0  0.0   4956   796 pts/1    S+   16:59   0:00 grep nginx  linux-gz215:/usr/local/nginx/sbin # ps aux | grep nginx  root     18152  0.0  0.0   9264  2588         S    16:45   0:00 nginx: master process ./nginx  root     18474  0.0  0.0   9124  2460         S    16:59   0:00 nginx: master process ./nginx  nobody   18475  2.8  0.0  13792  5908         S    16:59   0:01 nginx: worker process  nobody   18476  0.0  0.0  13136  5240         S    16:59   0:00 nginx: worker process  nobody   18477  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18478  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18479  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18480  0.0  0.0  13136  5040         S    16:59   0:00 nginx: worker process  nobody   18481  0.0  0.0  13136  5240         S    16:59   0:00 nginx: worker process  nobody   18482  0.0  0.0  13136  5240         S    16:59   0:00 nginx: worker process  root     18488  0.0  0.0   4956   796 pts/1    S+   16:59   0:00 grep nginx  linux-gz215:/usr/local/nginx/sbin # kill -HUP 18152  linux-gz215:/usr/local/nginx/sbin # ps aux | grep nginx  root     18152  0.0  0.0   9264  2588         S    16:45   0:00 nginx: master process ./nginx  root     18474  0.0  0.0   9124  2460         S    16:59   0:00 nginx: master process ./nginx  nobody   18475  3.1  0.0  13256  5376         S    16:59   0:02 nginx: worker process  nobody   18476  0.0  0.0  13256  5336         S    16:59   0:00 nginx: worker process  nobody   18477  0.8  0.0  13420  5532         S    16:59   0:00 nginx: worker process  nobody   18478  0.2  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18479  0.2  0.0  13580  5656         S    16:59   0:00 nginx: worker process  nobody   18480  0.0  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18481  0.3  0.0  13412  5532         S    16:59   0:00 nginx: worker process  nobody   18482  0.0  0.0  13256  5320         S    16:59   0:00 nginx: worker process  nobody   18570  2.0  0.0  13276  5380         S    17:00   0:00 nginx: worker process  nobody   18571  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18572  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18573  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18574  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18575  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18576  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18577  0.0  0.0  13276  5164         S    17:00   0:00 nginx: worker process  root     18579  0.0  0.0   4960   812 pts/1    S+   17:00   0:00 grep nginx  linux-gz215:/usr/local/nginx/sbin # kill -QUIT 18152  linux-gz215:/usr/local/nginx/sbin # ps aux | grep nginx  root     18474  0.0  0.0   9124  2460         S    16:59   0:00 nginx: master process ./nginx  nobody   18475  2.2  0.0  13256  5376         S    16:59   0:02 nginx: worker process  nobody   18476  0.0  0.0  13280  5400         S    16:59   0:00 nginx: worker process  nobody   18477  1.5  0.0  13256  5376         S    16:59   0:01 nginx: worker process  nobody   18478  0.1  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18479  0.2  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18480  0.0  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18481  0.2  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18482  0.0  0.0  13256  5320         S    16:59   0:00 nginx: worker process  nobody   18570  3.2  0.0  13644  5672         S    17:00   0:00 nginx: worker process  nobody   18571  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18572  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18573  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18574  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18575  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18576  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18577  0.6  0.0  13360  5412         S    17:00   0:00 nginx: worker process  root     18582  0.0  0.0   4956   804 pts/1    R+   17:00   0:00 grep nginx  linux-gz215:/usr/local/nginx/sbin # ps aux | grep nginx  root     18474  0.0  0.0   9124  2460         S    16:59   0:00 nginx: master process ./nginx  nobody   18475  2.2  0.0  13256  5376         S    16:59   0:02 nginx: worker process  nobody   18476  0.2  0.0  13452  5520         S    16:59   0:00 nginx: worker process  nobody   18477  1.5  0.0  13256  5376         S    16:59   0:01 nginx: worker process  nobody   18478  0.1  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18479  0.2  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18480  0.0  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18481  0.2  0.0  13256  5376         S    16:59   0:00 nginx: worker process  nobody   18482  0.0  0.0  13256  5320         S    16:59   0:00 nginx: worker process  nobody   18570  3.2  0.0  13644  5672         S    17:00   0:01 nginx: worker process  nobody   18571  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18572  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18573  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18574  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18575  0.0  0.0  13276  5172         S    17:00   0:00 nginx: worker process  nobody   18576  0.0  0.0  13

Python +Mysql 簡單安裝部署步驟：

win10安裝mysql數據庫

下載 mysql-5.5.61-winx64，按提示默認安裝，相關的安裝目錄 ：

  g:MySQL Datafiles  C:ProgramDataMicrosoftWindowsStart MenuProgramsMySQLMySQL Server 5.5  C:Program FilesMySQLMySQL Server 5.5bin

創(chuàng)建數據庫及更改權限：

  create database demo;  grant 	all on *.* to root@localhost identified by '';  exit

導入數據庫文件：

  mysql -u root -p demo < all_gzdata.sql  mysql -u root -p demo   mysql> show tables;

運行python操作數據庫的代碼：

  import pandas as pd  import MySQLdb  import pymysql  pymysql.install_as_MySQLdb()  from sqlalchemy import create_engine  def page199(i): #自定義統(tǒng)計函數      j = i[['fullURL','pageTitle']][(i['fullURLId'].str.contains('199')) & (i['fullURL'].str.contains(''))]      j['pageTitle'].fillna(u'空',inplace=True)      j['type'] = u'其他' # 添加空列      j['type'][j['pageTitle'].str.contains(u'法律快車-律師助手')]= u'法律快車-律師助手'      j['type'][j['pageTitle'].str.contains(u'咨詢發(fā)布成功')]= u'咨詢發(fā)布成功'      j['type'][j['pageTitle'].str.contains(u'免費發(fā)布法律咨詢' )] = u'免費發(fā)布法律咨詢'      j['type'][j['pageTitle'].str.contains(u'法律快搜')] = u'快搜'      j['type'][j['pageTitle'].str.contains(u'法律快車法律經驗')] = u'法律快車法律經驗'      j['type'][j['pageTitle'].str.contains(u'法律快車法律咨詢')] = u'法律快車法律咨詢'      j['type'][(j['pageTitle'].str.contains(u'_法律快車')) | (j['pageTitle'].str.contains(u'-法律快車'))] = u'法律快車'      j['type'][j['pageTitle'].str.contains(u'空')] = u'空'            return j  # 注意：獲取一次sql對象就需要重新訪問一下數據庫  engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/democharset=utf8mb4')  sql = pd.read_sql('all_gzdata', engine, chunksize = 10000)# 分塊讀取數據庫信息     counts4 = [page199(i) for i in sql] # 逐塊統(tǒng)計  counts4 = pd.concat(counts4)  d1 = counts4['type'].value_counts()  print (d1)  d2 = counts4[counts4['type']==u'其他']  print (d2)  d2 = pd.DataFrame(d2)    # 先自定義函數將表格寫入數據庫里，以備操作過程中有些數據要寫入數據庫  def savetosql(DF,tablename):      import pandas as pd      from sqlalchemy import create_engine      yconnect = create_engine('mysql+mysqldb://root:@127.0.0.1:3306/democharset=utf8mb4')        pd.io.sql.to_sql(DF,tablename, yconnect, schema='demo', if_exists='append')   savetosql(d2,'199elsepercentage')# 將199的網頁中的“其他”類型的數據存到數據庫中  

python寫入數據庫報錯：

        File "", line 40, in       savetosql(d2,'199elsePercentage')      File "", line 38, in savetosql      pd.io.sql.to_sql(DF,tablename, yconnect, schema='demo', if_exists='append')      File "G:ProgramDataAnaconda3libsite-packagespandasiosql.py", line 450, in to_sql      chunksize=chunksize, dtype=dtype)      File "G:ProgramDataAnaconda3libsite-packagespandasiosql.py", line 1149, in to_sql      table.insert(chunksize)      File "G:ProgramDataAnaconda3libsite-packagespandasiosql.py", line 663, in insert      self._execute_insert(conn, keys, chunk_iter)      File "G:ProgramDataAnaconda3libsite-packagespandasiosql.py", line 638, in _execute_insert      conn.execute(*self.insert_statement(data, conn))      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyenginebase.py", line 948, in execute      return meth(self, multiparams, params)      File "G:ProgramDataAnaconda3libsite-packagessqlalchemysqlelements.py", line 269, in _execute_on_connection      return connection._execute_clauseelement(self, multiparams, params)      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyenginebase.py", line 1060, in _execute_clauseelement      compiled_sql, distilled_params      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyenginebase.py", line 1200, in _execute_context      context)      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyenginebase.py", line 1413, in _handle_dbapi_exception      exc_info      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyutilcompat.py", line 203, in raise_from_cause      reraise(type(exception), exception, tb=exc_tb, cause=cause)      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyutilcompat.py", line 186, in reraise      raise value.with_traceback(tb)      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyenginebase.py", line 1193, in _execute_context      context)      File "G:ProgramDataAnaconda3libsite-packagessqlalchemyenginedefault.py", line 507, in do_execute      cursor.execute(statement, parameters)      File "G:ProgramDataAnaconda3libsite-packagespymysqlcursors.py", line 170, in execute      result = self._query(query)      File "G:ProgramDataAnaconda3libsite-packagespymysqlcursors.py", line 328, in _query      conn.query(q)      File "G:ProgramDataAnaconda3libsite-packagespymysqlconnections.py", line 516, in query      self._affected_rows = self._read_query_result(unbuffered=unbuffered)      File "G:ProgramDataAnaconda3libsite-packagespymysqlconnections.py", line 727, in _read_query_result      result.read()      File "G:ProgramDataAnaconda3libsite-packagespymysqlconnections.py", line 1066, in read      first_packet = self.connection._read_packet()      File "G:ProgramDataAnaconda3libsite-packagespymysqlconnections.py", line 683, in _read_packet      packet.check_error()      File "G:ProgramDataAnaconda3libsite-packagespymysqlprotocol.py", line 220, in check_error      err.raise_mysql_exception(self._data)      File "G:ProgramDataAnaconda3libsite-packagespymysqlerr.py", line 109, in raise_mysql_exception      raise errorclass(errno, errval)    InternalError: (pymysql.err.InternalError) (1366, "· 1") [SQL: 'INSERT INTO demo.`199elsePercentage`......

mysql 字符集編碼出錯，檢查mysql字符集：

  mysql> show variables like 'character_set_database';  +------------------------+--------+  | Variable_name          | Value  |  +------------------------+--------+  | character_set_database | latin1 |  +------------------------+--------+  1 row in set (0.00 sec)    mysql> show variables like 'character%';  +--------------------------+---------------------------------------------------------+  | Variable_name            | Value                                                   |  +--------------------------+---------------------------------------------------------+  | character_set_client     | utf8mb4                                                 |  | character_set_connection | utf8mb4                                                 |  | character_set_database   | latin1                                                  |  | character_set_filesystem | binary                                                  |  | character_set_results    | utf8mb4                                                 |  | character_set_server     | utf8mb4                                                 |  | character_set_system     | utf8                                                    |  | character_sets_dir       | C:Program FilesMySQLMySQL Server 5.5sharecharsets |  +--------------------------+---------------------------------------------------------+  8 rows in set (0.00 sec)

更改編碼alter database demo CHARACTER SET utf8mb4;插入仍失敗，未生效：

  mysql> alter database demo CHARACTER SET utf8mb4;  Query OK, 1 row affected (0.00 sec)    mysql> show variables like 'character%';  +--------------------------+---------------------------------------------------------+  | Variable_name            | Value                                                   |  +--------------------------+---------------------------------------------------------+  | character_set_client     | utf8mb4                                                 |  | character_set_connection | utf8mb4                                                 |  | character_set_database   | utf8mb4                                                 |  | character_set_filesystem | binary                                                  |  | character_set_results    | utf8mb4                                                 |  | character_set_server     | utf8mb4                                                 |  | character_set_system     | utf8                                                    |  | character_sets_dir       | C:Program FilesMySQLMySQL Server 5.5sharecharsets |  +--------------------------+---------------------------------------------------------+  8 rows in set (0.00 sec)    mysql> show variables like 'character_set_database';  +------------------------+---------+  | Variable_name          | Value   |  +------------------------+---------+  | character_set_database | utf8mb4 |  +------------------------+---------+

修改my.cnf文件（C:Program FilesMySQLMySQL Server 5.5）,將編碼改為utf8mb4，在windows服務中重啟數據庫

  [mysql]    #default-character-set=latin1  default-character-set=utf8mb4    #character-set-server=latin1  character-set-server=utf8mb4  collation-server = utf8mb4_unicode_ci    init_connect='SET NAMES utf8mb4'  

mysq已有的數據庫和表的字符集更改使用以下語句：

  mysql> ALTER DATABASE demo CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;  Query OK, 1 row affected (0.00 sec)    mysql>  mysql> ALTER TABLE  199elsePercentage  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;  Query OK, 0 rows affected (0.30 sec)  Records: 0  Duplicates: 0  Warnings: 0

編碼更改以后，mysql數據庫插入成功：

  mysql> select count(1) from 199elsepercentage;  +----------+  | count(1) |  +----------+  |      359 |  +----------+  1 row in set (0.00 sec)  

276 5172 S 17:00 0:00 nginx: worker process nobody 18577 0.5 0.0 13360 5412 S 17:00 0:00 nginx: worker process root 18584 0.0 0.0 4956 812 pts/1 S+ 17:00 0:00 grep nginx linux-gz215:/usr/local/nginx/sbin #

為了驗證平滑升級確實不影響在線業(yè)務，我特意在升級的時候，利用 ab 命令一直在發(fā)送請求：

  ab -n1000000 -c10 http://domain.com/

直到升級完成，使用 ctrl +C 終止并查看 ab 結果，可以發(fā)現(xiàn)幾十萬次的請求全部成功，沒有失??！證明平滑升級的可行性！