Ubuntu升级后恢复WordPress

昨天手欠将Ubuntu升级到了16.04.5 LTS,结果导致博客无法访问。大致看了下,主要的原因可能是:

  • nginx替代了apache2,成为了默认的http server
  • php升级到了7.0,需要启用新的libapache2-mod-php模块,还有php-mysql等

第一次尝试

暂时先继续使用apache,并重新下载了WordPress5.0,且仍然沿用之前旧的mysql数据库表,dbName以及tableName均保持一致,但站点仍然无法正常显示。

由于之前的WordPress是从4.*升级到5.0的,很多配置文件都不太一样,所以还是需要重新初始化。

第二次尝试

mysql数据库表启用新的表名前缀,终于站点可以正常打开了,但是之前的文章全部都没有了。插件以及主题可以从之前目录的wp-content目录中拷贝出来,文章全部是存在mysql表中的,因此需要将文章从之前的表中恢复出来。

第三次尝试

尝试通过表数据的完全复制来进行恢复

delete from wp_posts;
delete from wp_options;
insert into wp_posts select * from old_posts;
insert into wp_options select * from old_options;

但是站点又是无法正常显示,尝试失败。

第四次尝试

尝试了数据订正的方式,即先随便发一篇文章,然后将表中这条记录的关键字段用旧表中的记录进行update,从而“订正”为之前的博客文章。比如:

update wp_posts a join ngtnf_posts b set a.post_date = b.post_date, a.post_date_gmt = b.post_date_gmt, a.post_content = b.post_content, a.post_title = b.post_title, a.post_modified = b.post_modified, a.post_modified_gmt = b.post_modified_gmt where a.id = 45 and b.id = 314;

但是却出现了乱码。原来Ubuntu升级后,mysql的版本也进行了升级,从而造成表的默认charset也发生了改变

之前的默认charset为latin1
CREATE TABLE `ngtnf_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(255) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`),
  KEY `post_name` (`post_name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=318 DEFAULT CHARSET=latin1

新的默认charset为utf8mb4
CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci 

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,post_title from ngtnf_posts where id = 123;
+-----+---------------+
| id  | post_title    |
+-----+---------------+
| 123 | 首页        |
+-----+---------------+
1 row in set (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,post_title from ngtnf_posts where id = 123;
+-----+------------+
| id  | post_title |
+-----+------------+
| 123 | 首页       |
+-----+------------+
1 row in set (0.00 sec)

第五次尝试

因此先要解决不同charset表之间数据复制的问题,通过mysqldump将表的记录导出,再导入到一张临时表中,即可完成latin1到utf8mb4之间的转换。

mysqldump -uroot --default-character-set=latin1 wordpress ngtnf_posts > posts.sql

将posts.sql中的表名替换为临时表名,将latin1替换为utf8mb4

mysql -uroot -Dwordpress < posts.sql 

数据导入到临时表后,即可沿用之前的数据订正的方式进行恢复了

update wp_posts a 
join ngtnf_posts_backup b 
set a.post_date = b.post_date, a.post_date_gmt = b.post_date_gmt, a.post_content = b.post_content, a.post_title = b.post_title, a.post_modified = b.post_modified, a.post_modified_gmt = b.post_modified_gmt where a.id = 45 and b.id = 314;

Let’s Encrypt升级流程

今天收到了Let’s Encrypt的邮件,大意就是以后不支持TLS-SNI-01,需要替换为其他校验方式。

You need to update your ACME client to use an alternative validation method (HTTP-01, DNS-01 or TLS-ALPN-01) before this date or your certificate renewals will break and existing certificates will start to expire.

参考官方的教程,开始进行升级。首先将certbot升级到0.28.0

ubuntu@ip-172-31-12-237:~$ certbot --version
 certbot 0.19.0

然后按步骤升级,dry-run通过

root@ip-172-31-12-237:~# sudo certbot renew --dry-run
Saving debug log to /var/log/letsencrypt/letsencrypt.log

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Processing /etc/letsencrypt/renewal/njujiang.tech.conf
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Cert not due for renewal, but simulating renewal for dry run
Plugins selected: Authenticator apache, Installer apache
Renewing an existing certificate
Performing the following challenges:
http-01 challenge for njujiang.tech
Waiting for verification...
Cleaning up challenges

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
new certificate deployed with reload of apache server; fullchain is
/etc/letsencrypt/live/njujiang.tech/fullchain.pem
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
** DRY RUN: simulating 'certbot renew' close to cert expiry
**          (The test certificates below have not been saved.)

Congratulations, all renewals succeeded. The following certs have been renewed:
  /etc/letsencrypt/live/njujiang.tech/fullchain.pem (success)
** DRY RUN: simulating 'certbot renew' close to cert expiry
**          (The test certificates above have not been saved.)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

然后强制更新

root@ip-172-31-12-237:~# sudo certbot renew
Saving debug log to /var/log/letsencrypt/letsencrypt.log

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Processing /etc/letsencrypt/renewal/njujiang.tech.conf
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Cert not yet due for renewal

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The following certs are not due for renewal yet:
  /etc/letsencrypt/live/njujiang.tech/fullchain.pem expires on 2019-03-30 (skipped)
No renewals were attempted.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
root@ip-172-31-12-237:~# 
root@ip-172-31-12-237:~# 
root@ip-172-31-12-237:~# certbot renew --force-renewal
Saving debug log to /var/log/letsencrypt/letsencrypt.log

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Processing /etc/letsencrypt/renewal/njujiang.tech.conf
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plugins selected: Authenticator apache, Installer apache
Renewing an existing certificate
Performing the following challenges:
http-01 challenge for njujiang.tech
Waiting for verification...
Cleaning up challenges

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
new certificate deployed with reload of apache server; fullchain is
/etc/letsencrypt/live/njujiang.tech/fullchain.pem
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Congratulations, all renewals succeeded. The following certs have been renewed:
  /etc/letsencrypt/live/njujiang.tech/fullchain.pem (success)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

免费启用https

浏览到coolshell上的一篇博客,可以免费启用https,赶紧按步骤体验了下。

总体流程比较顺利,也顺利创建了crontab任务

ubuntu@:~$ cat /etc/cron.d/certbot
# /etc/cron.d/certbot: crontab entries for the certbot package
#
# Upstream recommends attempting renewal twice a day
#
# Eventually, this will be an opportunity to validate certificates
# haven't been revoked, etc.  Renewal will only occur if expiration
# is within 30 days.
SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

0 */12 * * * root test -x /usr/bin/certbot -a \! -d /run/systemd/system &amp;&amp; perl -e 'sleep int(rand(3600))' &amp;&amp; certbot -q renew

但是http自动跳转到https并没有起作用,后来在网上找到了类似的问题,用下面的配置启用了http强制跳转

ServerName njujiang.tech
ServerAdmin njujiang@163.com
DocumentRoot /var/www/html/wordpress
Redirect / https://njujiang.tech/

WordPress插件列表

* All In One WP Security

安全插件,同时兼有一些资源的备份恢复功能

* Custom Favicon

自定义网站的图标

* Google Analytics for WordPress by MonsterInsights

该插件可以方便地将GA的埋点JS代码注入到网页中,并且有dashboard直接展示访问统计数据

* Google Font Fix

有些主题使用了Google的字体库,需要从font.googleapis.com下载,异常缓慢,从而导致网页无法打开。该插件可以将字体的下载地址替换为国内的镜像

* Markdown Editor

将文章的编辑框替换为Markdown的编辑器,并支持双栏编辑模式,所见即所得

* SyntaxHighlighter Evolved

code highlighter

* PDF Embedder

支持在文章中直接浏览PDF,支持翻页等常见操作

* Table of Contents Plus

在文章的最开头展示目录结构(TOC,Table of Content)