WordPress 通过mysql触发器,同步不同子站文章

字体大小: [] [] []

对拥有多个wordpress站点的站长来说,如何在一个网站上更新内容,然后根据不同的分类,同步到不同的子站上去可能是一个问题。当然,你可以通过wordpress的远程发布接口或者wordpress的导入导出文章功能来实现,但操作起来比较麻烦,所以这里推荐用mysql的触发器,在mysql检测到有文章插入时,同步将数据写入到其他wordpress的数据库中。

本文介绍的是同一个mysql实例上不同的wordpress数据库的数据同步。

一个数据库是wordpress,一个是meinv。要实现的功能是,当wordpress数据库中有分类ID是4的文章新增时,把这些文章写入到meinv库中。其中wp_term_relationships,wp_posts,wp_term_taxonomy这三张表的数据要同步。

在wordpress库中执行以下sql:

DROP TRIGGER IF EXISTS ‘t_afterinsert_on_wp_term_relationships’;
CREATE DEFINER = `root`@`10.1.106.27` TRIGGER `t_afterinsert_on_wp_term_relationships` AFTER INSERT ON `wp_term_relationships`
FOR EACH ROW
IF new.term_taxonomy_id in (4) then
INSERT INTO meinv.wp_term_relationships(object_id,term_taxonomy_id,term_order) VALUES (new.object_id,new.term_taxonomy_id,new.term_order);
INSERT INTO meinv.wp_posts(
ID,
post_author,
post_date,
post_date_gmt,
post_content,
post_title,
post_excerpt,
post_status,
comment_status,
ping_status,
post_password,
post_name,
to_ping,
pinged,
post_modified,
post_modified_gmt,
post_content_filtered,
post_parent,
guid,
menu_order,
post_type,
post_mime_type,
comment_count
)
SELECT ID,post_author,post_date,post_date_gmt,post_content,post_title,post_excerpt,post_status,comment_status,ping_status,post_password,post_name,to_ping,pinged, post_modified,post_modified_gmt,post_content_filtered,post_parent,guid,menu_order,post_type,post_mime_type,comment_count from wp_posts where ID = new.object_id;
INSERT INTO meinv.wp_postmeta(
meta_id,
post_id,
meta_key,
meta_value
)
SELECT meta_id,post_id,meta_key,meta_value from wp_postmeta where post_id = new.object_id;
UPDATE meinv.wp_term_taxonomy SET count = (SELECT count(*) FROM wp_term_relationships where term_taxonomy_id = new.term_taxonomy_id) where term_taxonomy_id = new.term_taxonomy_id;
end IF;

 

打开wordpress的站点,新增一篇文章分类选ID为4的,要不不会同步的奥。

 

83
甜筒冰激凌 的头像
[共196篇]

个人空间: 点击进入

You must be logged in to post a comment.