今天，產(chǎn)品那邊發(fā)來(lái)需求，說(shuō)有個(gè) APP 的 IOS 版本下載包需要新增 https 協(xié)議，在景安購(gòu)買了免費(fèi)的 SSL 證書。當(dāng)我往 nginx 上新增 ssl 時(shí)，發(fā)現(xiàn)服務(wù)器上的 nginx 居然沒(méi)編譯 SSL 模塊！

看了下舊版本 nginx 的 configure 選項(xiàng)：

  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

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

下面記錄一下平滑升級(jí)和新增模塊的過(guò)程。

一、半自動(dòng)平滑升級(jí)

所謂半自動(dòng)，其實(shí)就是在最后遷移的時(shí)候使用源碼自帶的升級(jí)命令：make upgrade 來(lái)自動(dòng)完成。

①、按需編譯新版本的 nginx

根據(jù)需求，常規(guī)編譯新版本 nginx，不過(guò)只要執(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    #根據(jù)實(shí)際需要新增的模塊，先準(zhǔn)備所需文件(其實(shí)只需要解壓即可，全部安裝，后面編譯就可以不指定路徑了)：  #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    #加上所需參數(shù)開始編譯：  ./configure --user=www --group=www   --prefix=/usr/local/nginx   --with-http_ssl_module   --with-openssl=/usr/local/src/openssl-1.0.1c  #對(duì)應(yīng)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 舊版本二進(jìn)制文件，即 sbin 目錄下的 nginx（期間 nginx 并不會(huì)停止服務(wù)！）：

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

③、然后拷貝一份新編譯的二進(jìn)制文件：

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

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

  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`     #最后確認(rèn)一下nginx進(jìn)程,可以發(fā)現(xiàn)有2個(gè)主進(jìn)程，并且有正在關(guān)閉的進(jìn)程（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     #過(guò)一段時(shí)間后，再次確認(rèn)nginx進(jìn)程，可以發(fā)現(xiàn)老進(jìn)程已自動(dòng)退出了（存在一段時(shí)間是因?yàn)榕f進(jìn)程還有未結(jié)束的服務(wù)）  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

完成后，最后確認(rèn)一下 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

正常了，平滑升級(jí)成功！

二、純手動(dòng)平滑升級(jí)

純手動(dòng)模式，指的是在最后做遷移的時(shí)候，全部使用手動(dòng)命令來(lái)搞定，避免編譯可能存在不一致的參數(shù)啥的。

實(shí)際上，在 make 之后，我們可以查看 nginx 源碼目錄下的 Makefile 內(nèi)容如下：

  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`

所以，說(shuō)白了純手動(dòng)就是執(zhí)行 upgrade 標(biāo)簽下的命令行而已，實(shí)際上只要確認(rèn) Makefile 下的命令路徑都是正確的，用命令自動(dòng)遷移是沒(méi)有任何問(wèn)題的。

總是有人會(huì)不放心的，喜歡手動(dòng)一步一步的搞定，我也來(lái)整理下純手動(dòng)步驟：

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

④、測(cè)試新版本的 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,沒(méi)有問(wèn)題！  nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful

⑤、給舊 nginx 發(fā)送平滑遷移信號(hào)（若不清楚 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：后面其實(shí)就是舊 nginx 的 pid，所以先用 ps aux 找到正在運(yùn)行的 nginx 主進(jìn)程 pid,再執(zhí)行 kill -USR2 PID 值亦可。

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

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

⑦、 從容關(guān)閉舊版本的 Nginx 進(jìn)程

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

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

⑧、此時(shí)，不重載配置啟動(dòng)舊工作進(jìn)程（個(gè)人感覺(jué)是為了將任務(wù)完全切換到新的 nginx 上）

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

⑨、結(jié)束工作進(jìn)程，完成此次升級(jí)操作：

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

⑩、最后，驗(yàn)證 nginx 是否升級(jí)成功：

  linux-gz215:~ # /usr/local/nginx/sbin/nginx -V  nginx version: nginx/1.6.0 #沒(méi)問(wèn)題  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

特意測(cè)試了下純手動(dòng)的做法，下面是我的操作記錄，僅供參考：

  linux-gz215:/usr/local/nginx # cd sbin/  linux-gz215:/usr/local/nginx/sbin # ll  總計(jì) 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  總計(jì) 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 簡(jiǎn)單安裝部署步驟：

win10安裝mysql數(shù)據(jù)庫(kù)

下載 mysql-5.5.61-winx64，按提示默認(rèn)安裝，相關(guān)的安裝目錄 ：

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

創(chuàng)建數(shù)據(jù)庫(kù)及更改權(quán)限：

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

導(dǎo)入數(shù)據(jù)庫(kù)文件：

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

運(yùn)行python操作數(shù)據(jù)庫(kù)的代碼：

  import pandas as pd  import MySQLdb  import pymysql  pymysql.install_as_MySQLdb()  from sqlalchemy import create_engine  def page199(i): #自定義統(tǒng)計(jì)函數(shù)      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èi)發(fā)布法律咨詢' )] = u'免費(fèi)發(fā)布法律咨詢'      j['type'][j['pageTitle'].str.contains(u'法律快搜')] = u'快搜'      j['type'][j['pageTitle'].str.contains(u'法律快車法律經(jīng)驗(yàn)')] = u'法律快車法律經(jīng)驗(yàn)'      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對(duì)象就需要重新訪問(wèn)一下數(shù)據(jù)庫(kù)  engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/democharset=utf8mb4')  sql = pd.read_sql('all_gzdata', engine, chunksize = 10000)# 分塊讀取數(shù)據(jù)庫(kù)信息     counts4 = [page199(i) for i in sql] # 逐塊統(tǒng)計(jì)  counts4 = pd.concat(counts4)  d1 = counts4['type'].value_counts()  print (d1)  d2 = counts4[counts4['type']==u'其他']  print (d2)  d2 = pd.DataFrame(d2)    # 先自定義函數(shù)將表格寫入數(shù)據(jù)庫(kù)里，以備操作過(guò)程中有些數(shù)據(jù)要寫入數(shù)據(jù)庫(kù)  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的網(wǎng)頁(yè)中的“其他”類型的數(shù)據(jù)存到數(shù)據(jù)庫(kù)中  

python寫入數(shù)據(jù)庫(kù)報(bào)錯(cuò)：

        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 字符集編碼出錯(cuò)，檢查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服務(wù)中重啟數(shù)據(jù)庫(kù)

  [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已有的數(shù)據(jù)庫(kù)和表的字符集更改使用以下語(yǔ)句：

  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數(shù)據(jù)庫(kù)插入成功：

  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àn)證平滑升級(jí)確實(shí)不影響在線業(yè)務(wù)，我特意在升級(jí)的時(shí)候，利用 ab 命令一直在發(fā)送請(qǐng)求：

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

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