手动安装 Let’s Encrypt SSL 证书 HTTPS

CentOS 6、7,先执行:

yum install epel-release
cd /root/
wget https://dl.eff.org/certbot-auto --no-check-certificate
chmod +x ./certbot-auto
./certbot-auto -n

单域名生成证书:

./certbot-auto certonly --email jollyfon@gmail.com --agree-tos --no-eff-email --webroot -w /home/wwwroot/wanai.unetu.net -d wanai.unetu.net

安装成功返回

IMPORTANT NOTES:
 - Congratulations! Your certificate and chain have been saved at:
   /etc/letsencrypt/live/wanai.unetu.net/fullchain.pem
   Your key file has been saved at:
   /etc/letsencrypt/live/wanai.unetu.net/privkey.pem
   Your cert will expire on 2019-01-30. To obtain a new or tweaked
   version of this certificate in the future, simply run certbot-auto
   again. To non-interactively renew *all* of your certificates, run
   "certbot-auto renew"
 - Your account credentials have been saved in your Certbot
   configuration directory at /etc/letsencrypt. You should make a
   secure backup of this folder now. This configuration directory will
   also contain certificates and private keys obtained by Certbot so
   making regular backups of this folder is ideal.
 - If you like Certbot, please consider supporting our work by:

   Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
   Donating to EFF:                    https://eff.org/donate-le

多域名单目录生成单证书:(即一个网站多个域名使用同一个证书)

./certbot-auto certonly --email jollyfon@gmail.com --agree-tos --no-eff-email --webroot -w /home/wwwroot/wanai.unetu.net -d wanai.unetu.net www.unetu.net

多域名多目录生成一个证书:(即一次生成多个域名的一个证书)

./certbot-auto certonly --email jollyfon@gmail.com --agree-tos --no-eff-email --webroot -w /home/wwwroot/wanai.unetu.net -d wanai.unetu.net www.unetu.net -w /home/wwwroot/wanaioa.unetu.net -d wanaioa.unetu.net -d unetu.net

安装完成后证书文件位置

/etc/letsencrypt/live

有四个文件

/etc/letsencrypt/live/wanai.unetu.net/cert.pem
/etc/letsencrypt/live/wanai.unetu.net/chain.pem
/etc/letsencrypt/live/wanai.unetu.net/fullchain.pem
/etc/letsencrypt/live/wanai.unetu.net/privkey.pem

Nginx 配置

listen 443 ssl;   
server_name wanai.unetu.net;     
index index.html index.htm index.php default.html default.htm default.php;
root /home/wwwroot/wanai.unetu.net;          
ssl_certificate /etc/letsencrypt/live/wanai.unetu.net/fullchain.pem;    #前面生成的证书,改一下里面的域名就行
ssl_certificate_key /etc/letsencrypt/live/wanai.unetu.net/privkey.pem;   #前面生成的密钥,改一下里面的域名就行
SSLCertificateChainFile /etc/letsencrypt/live/wanai.unetu.net/chain.pem; #Apache 2.2版本需要加入该中间证书,否则浏览器可能不信任
ssl_ciphers "EECDH+CHACHA20:EECDH+CHACHA20-draft:EECDH+AES128:RSA+AES128:EECDH+AES256:RSA+AES256:EECDH+3DES:RSA+3DES:!MD5";
ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
ssl_prefer_server_ciphers on;
ssl_session_cache shared:SSL:10m;

Let’s Encrypt 证书的有效期为 90 天,可自动续期

打开 crontab 

crontab -e

添加规则

0 3 */5 * * /root/certbot-auto renew --disable-hook-validation --renew-hook "/etc/init.d/nginx reload"

我的示例

server
    {
        listen 443 ssl http2;
        #listen [::]:443 ssl http2;
        server_name wanai.unetu.net ;
        index index.html index.htm index.php default.html default.htm default.php;
        root  /home/wwwroot/wanai.unetu.net;
        ssl on;
        ssl_certificate /etc/letsencrypt/live/wanai.unetu.net/fullchain.pem;
        ssl_certificate_key /etc/letsencrypt/live/wanai.unetu.net/privkey.pem;
        ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
        ssl_prefer_server_ciphers on;
        ssl_session_cache shared:SSL:10m;

        include rewrite/codeigniter.conf;

        location  ~ [^/]\.php(/|$)
        {
            fastcgi_pass  127.0.0.1:9001; #注意此端口
            fastcgi_index index.php;
            fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
            include        fastcgi_params;

        }

        location ~ .*\.(gif|jpg|jpeg|png|bmp|swf)$
        {
            expires      30d;
        }

        location ~ .*\.(js|css)?$
        {
            expires      12h;
        }

        location ~ /.well-known {
            allow all;
        }

        location ~ /\.
        {
            deny all;
        }

        access_log  /home/wwwlogs/wanai.unetu.net.log;
    }

参考文献:

CodeIgnter 报错 The Encrypt library requires the Mcrypt extension.

An Error Was Encountered
The Encrypt library requires the Mcrypt extension.

CI 框架 缺少 mcrypt 扩展

yum 安装 mcrypt

yum install libmcrypt libmcrypt-devel mcrypt mhash

源码编译没试,可参考这篇文章 

https://www.cnblogs.com/huangzhen/archive/2012/09/12/2681861.html

安装 php 的 mcrypt 扩展

扩展在 php 安装包的 ext 目录下

[root@instance-jewlel2q ~]# cd php-5.6.30/
[root@instance-jewlel2q php-5.6.30]# cd ext/mcrypt/
[root@instance-jewlel2q mcrypt]# /usr/local/php5/bin/phpize 

返回

Configuring for:
PHP Api Version:         20131106
Zend Module Api No:      20131226
Zend Extension Api No:   220131226
[root@instance-jewlel2q mcrypt]# ./configure --with-php-config=/usr/local/php5/bin/php-config

返回

creating libtool
appending configuration tag "CXX" to libtool
configure: creating ./config.status
config.status: creating config.h

编译

[root@instance-jewlel2q mcrypt]# make
[root@instance-jewlel2q mcrypt]# make install
Installing shared extensions:     /usr/local/php5/lib/php/extensions/no-debug-non-zts-20131226/

查看扩展目录

[root@instance-jewlel2q mcrypt]# cd  /usr/local/php5/lib/php/extensions/no-debug-non-zts-20131226/
[root@instance-jewlel2q no-debug-non-zts-20131226]# ll
total 2092
-rwxr-xr-x 1 root root  170080 Nov  1 17:02 mcrypt.so
-rwxr-xr-x 1 root root 1340288 Nov  1 14:59 opcache.a
-rwxr-xr-x 1 root root  623648 Nov  1 14:59 opcache.so

安装成功

向 php.ini 写入扩展目录

vim /usr/local/php5/lib/php.ini

重启,查看 phpinfo

采坑记录

php 的配置文件应放在 lib 目录下,自己在 ext 目录下一直不生效,换到 lib 目录下重载后生效了

重装 LNMP 环境 MySQL 5.7 的初始配置

安装 lnmp.org

wget http://soft.vpser.net/lnmp/lnmp1.5.tar.gz -cO lnmp1.5.tar.gz && tar zxf lnmp1.5.tar.gz && cd lnmp1.5 && ./install.sh lnmp

其中 MySQL 版本 5.7 安装位置 /usr/local/mysql

添加用户用于外部访问,设置 3306 防火墙白名单,安全组白名单 范围% 重启主机

vim /etc/my.cnf

在 [mysqld] 下,添加

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

否则可能出现 [Err] 1067 – Invalid default value for ‘xxxxx’,因为 MySQL 5.7 datetime 不允许默认值为 ‘0000-00-00 00:00:00’ 

https://blog.csdn.net/achuo/article/details/54618990

导入备份的 SQL

[Err] 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

原因是 MySQL 默认读取执行的 SQL 文件最大为 1M,有些为 16M,我这个 SQL 文件大于这个值

vim /etc/my.cnf

在 [mysqld] 下,添加或修改

max_allowed_packet=400M

微信小程序 invalid page hint

微信小程序消息推送在测试可以发送成功,正式发送失败

错误信息如下

{
"errcode": 41030,
"errmsg": "invalid page hint: [YJmNKa07043954]"
}
https://developers.weixin.qq.com/miniprogram/dev/framework/open-ability/notice.html?search-key=41030

page 不正确

错误 /pages/index/index

正确 pages/index/index

PHPExcel 超过 26 列 解决方法

ThinkPHP 3.2 使用 PHPExcel

使用 chr(ord(‘A’)++) 的方法进行 Excel 列的自增,当列数超过26列时,会报如下错误

Invalid cell coordinate [1
错误位置
FILE: E:\wwwroot\wanaioa.a.cc\ThinkPHP\Library\Vendor\Excel\PHPExcel\Cell.php  LINE: 539

原因是 ord(‘Z’) 为 90,当 chr(91) 时为 string(1) “[“

但在 Excel 中应为 AA

解决方法是使用 PHPExcel 自带函数 stringFromColumnIndex

调用方法

\PHPExcel_Cell::stringFromColumnIndex(27);

源码为

/**
 *	String from columnindex
 *
 *	@param	int $pColumnIndex Column index (base 0 !!!)
 *	@return	string
 */
public static function stringFromColumnIndex($pColumnIndex = 0)
{
	//	Using a lookup cache adds a slight memory overhead, but boosts speed
	//	caching using a static within the method is faster than a class static,
	//		though it's additional memory overhead
	static $_indexCache = array();

	if (!isset($_indexCache[$pColumnIndex])) {
		// Determine column string
		if ($pColumnIndex < 26) {
			$_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
		} elseif ($pColumnIndex < 702) {
			$_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .
										  chr(65 + $pColumnIndex % 26);
		} else {
			$_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .
										  chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .
										  chr(65 + $pColumnIndex % 26);
		}
	}
	return $_indexCache[$pColumnIndex];
}

路径 

ThinkPHP/Library/Vendor/Excel/PHPExcel/Cell.php

封装一个 PHPExcel 的导出方法

本篇以 ThinkPHP 为示例

public function getExcel($property,$headArr,$data){

	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$objPHPExcel
		-> getProperties()
		-> setCreator($property['creator'])
		-> setLastModifiedBy($property['last'])
		-> setTitle($property['title'])
		-> setSubject($property['subject'])
		-> setDescription($property['description'])
		-> setKeywords($property['keywords'])
		-> setCategory($property['category']);

	$column = 1;
	$span = ord("A");
	foreach($headArr as $v){
		$j = chr($span);
		$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($j.$column,$v);
		$span ++;
	}

	$column ++;
	$objActSheet = $objPHPExcel->getActiveSheet();
	foreach($data as $key => $rows){
		$span = ord('A');
		foreach($rows as $keyName => $value){
			$j = chr($span);
			$objActSheet->setCellValue($j.$column, $value);
			$span ++;
		}
		$column ++;
	}

	$objPHPExcel->setActiveSheetIndex(0);

	ob_end_clean();
	ob_start();

	header("Content-Type: application/force-download");
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($fileName)).'.xlsx');
	header('Cache-Control: max-age=0');

	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter -> save('php://output');
	exit ;
}

调用

public function _index_export(){
	$property = array('title'=>'测试');
	$headArr = array('好','坏');
	$data = array(
		array(
			3,4
		),
		array(
			5,6
		)
	);
	$this->getExcel($property,$headArr,$data);
}

优化当超过 26 列

public function getExcel($property,$headArr,$data){

	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$objPHPExcel
		-> getProperties()
		-> setCreator($property['creator'])
		-> setLastModifiedBy($property['last'])
		-> setTitle($property['title'])
		-> setSubject($property['subject'])
		-> setDescription($property['description'])
		-> setKeywords($property['keywords'])
		-> setCategory($property['category']);

	$column = 1;
	$span = 0;
	foreach($headArr as $v){
		$letter = \PHPExcel_Cell::stringFromColumnIndex($span);
		$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($letter.$column,$v);
		$span ++;
	}

	$objActSheet = $objPHPExcel->getActiveSheet();
	foreach($data as $key => $rows){
		$column ++;
		$span = 0;
		foreach($rows as $keyName => $value){
			$letter = \PHPExcel_Cell::stringFromColumnIndex($span);
			$objActSheet->setCellValue($letter.$column, $value);
			$span ++;
		}
	}

	$objPHPExcel->setActiveSheetIndex(0);

	ob_end_clean();
	ob_start();

	header("Content-Type: application/force-download");
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($property['title'])).'.xlsx');
	header('Cache-Control: max-age=0');

	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter -> save('php://output');
	exit ;
}

可以设置列宽的一种方法

public function exportExcel($expTitle,$expCellName,$expTableData){
	$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
	$fileName = $expTitle.date('_Ymd_His');//or $xlsTitle 文件名称可根据自己情况设定
	$cellNum = count($expCellName);
	$dataNum = count($expTableData);
	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
	// $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
	// $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));//第一行标题
	for($i=0;$i<$cellNum;$i++){
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
		$objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][2]); //设置宽度
	}
	// Miscellaneous glyphs, UTF-8
	for($i=0;$i<$dataNum;$i++){
		for($j=0;$j<$cellNum;$j++){
			$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
		}
	}
	header('pragma:public');
	header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
	header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	exit;
}

调用

public function dumpLogsExcel(){
	//查询数据
	$where['member_card'] = 123456;
	$xlsData = array();//二维数组
	//构建excel
	$xlsName  = "用户评价信息表";
	$xlsCell  = array(
		array('GongSiMc','门店名称',20),
		array('order_num','保养单号',20),
		array('wx_name','微信名',20),
		array('member_card','会员卡号',15),
		array('is_good_name','是否满意',10),
		array('evaluate_item','具体评价',40),
		array('score','得分',10),
		array('create_time','创建时间',20),
	);
	$this->exportExcel($xlsName,$xlsCell,$xlsData);
}