深海游弋的鱼 – 第 4 页

ubuntu 16.04系统wordpress-4.9.4修改表引擎报告错误“Invalid default value for 'comment_date'”

最近在捣鼓wordpress主从同步的时候（ubuntu 16.04配置MySQL主从同步），需要把wp_comments的数据库引擎从MyISAM切换到INNODB（MyISAM不支持主从同步）。

在执行

$ mysql -u root -p -e "use wordpress; ALTER TABLE wp_comments ENGINE=INNODB;"

1	$ mysql -u root -p -e "use wordpress; ALTER TABLE wp_comments ENGINE=INNODB;"

的时候报告错误：

Invalid default value for 'comment_date'

1	Invalid default value for 'comment_date'

原因出在类似这样的建表语句

DROP TABLE IF EXISTS `wp_comments`;
CREATE TABLE `wp_comments`  (
  `comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT 0,
  `comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`comment_ID`) USING BTREE,
  INDEX `comment_post_ID`(`comment_post_ID`) USING BTREE,
  INDEX `comment_approved_date_gmt`(`comment_approved`, `comment_date_gmt`) USING BTREE,
  INDEX `comment_date_gmt`(`comment_date_gmt`) USING BTREE,
  INDEX `comment_parent`(`comment_parent`) USING BTREE,
  INDEX `comment_author_email`(`comment_author_email`(10)) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `wp_comments`;

CREATE TABLE `wp_comments` (

`comment_ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT 0,

`comment_author` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,

`comment_author_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_author_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_author_IP` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

`comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

`comment_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,

`comment_karma` int(11) NOT NULL DEFAULT 0,

`comment_approved` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '1',

`comment_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',

`comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,

`user_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,

PRIMARY KEY (`comment_ID`) USING BTREE,

INDEX `comment_post_ID`(`comment_post_ID`) USING BTREE,

INDEX `comment_approved_date_gmt`(`comment_approved`, `comment_date_gmt`) USING BTREE,

INDEX `comment_date_gmt`(`comment_date_gmt`) USING BTREE,

INDEX `comment_parent`(`comment_parent`) USING BTREE,

INDEX `comment_author_email`(`comment_author_email`(10)) USING BTREE

) ENGINE = MyISAM AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci ROW_FORMAT = Dynamic;

这种报错多是mysql升级到5.7而引起的默认值不兼容的问题。看看你的字段名是什么，我的是时间字段，类型是datetime。想到可能是类型的默认值被限制了，查看sql_mode。果然：NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制时间不能为0。

可以使用如下语句查看建表命令：

$ mysql -u root -p -e "use wordpress;show create table wp_comments\G;“

1	$ mysql -u root -p -e "use wordpress;show create table wp_comments\G;“

注意上面的

`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00', 
`comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

1 2	`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_date_gmt` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

这两句受到NO_ZERO_IN_DATE,NO_ZERO_DATE的影响。

查看 sql_mode

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> show variables like 'sql_mode';

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

| Variable_name | Value |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

临时修改：

mysql> set session
 -> sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> set session

-> sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

永久修改：

修改配置文件

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

1	$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

在[mysqld]下面添加如下列：

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1	sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

参考链接

导入数据库时报错1067 – Invalid default value for ‘字段名’

ubuntu 16.04配置MySQL主从同步

准备工作

1.主从数据库版本最好一致

2.主从数据库内数据保持一致

主数据库：121.199.27.227 /ubuntu 16.04 MySQL 5.7.21 (阿里云）

从数据库：182.254.149.39 /ubuntu 16.04 MySQL 5.7.21 (腾讯云）

防火墙配置

配置主服务器只允许特定IP访问数据库的端口，避免不必要的攻击。

主库防火墙配置

# iptables -A INPUT -p tcp -s slave_ip --dport 3306 -j ACCEPT

#删除可能已经存在的配置，避免出现多条重复记录
$ sudo iptables -D INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT 
$ sudo iptables -D INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT 
$ sudo iptables -D INPUT -p tcp --dport 3306 -j DROP 
$ sudo iptables -D INPUT -p udp --dport 3306 -j DROP 
$ sudo iptables -D INPUT -p sctp --dport 3306 -j DROP

#增加配置，只允许特定地址访问数据库端口
$ sudo iptables -A INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT
$ sudo iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
$ sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
$ sudo iptables -A INPUT -p udp --dport 3306 -j DROP
$ sudo iptables -A INPUT -p sctp --dport 3306 -j DROP

$ sudo iptables -L -n -v

#保存配置
$ sudo apt-get install iptables-persistent

#注意，iptables-persistent 与 ufw 冲突, 
#现象就是系统重启后执行 sudo ufw status 显示 inactive，
#但是sudo systemctrl ufw status 或sudo service ufw status 显示服务正常，
#实际上ufw并没有正常工作。
#如果两者同时安装，需要参考 https://www.mobibrw.com?p=29330 进行配置

$ sudo netfilter-persistent save

#配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面，
#最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下，
#可能会记录了多余的规则，需要手工删除

# iptables -A INPUT -p tcp -s slave_ip --dport 3306 -j ACCEPT

#删除可能已经存在的配置，避免出现多条重复记录

$ sudo iptables -D INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT

$ sudo iptables -D INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT

$ sudo iptables -D INPUT -p tcp --dport 3306 -j DROP

$ sudo iptables -D INPUT -p udp --dport 3306 -j DROP

$ sudo iptables -D INPUT -p sctp --dport 3306 -j DROP

#增加配置，只允许特定地址访问数据库端口

$ sudo iptables -A INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT

$ sudo iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 3306 -j DROP

$ sudo iptables -A INPUT -p udp --dport 3306 -j DROP

$ sudo iptables -A INPUT -p sctp --dport 3306 -j DROP

$ sudo iptables -L -n -v

#保存配置

$ sudo apt-get install iptables-persistent

#注意，iptables-persistent 与 ufw 冲突,

#现象就是系统重启后执行 sudo ufw status 显示 inactive，

#但是sudo systemctrl ufw status 或sudo service ufw status 显示服务正常，

#实际上ufw并没有正常工作。

#如果两者同时安装，需要参考 https://www.mobibrw.com?p=29330 进行配置

$ sudo netfilter-persistent save

#配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面，

#最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下，

#可能会记录了多余的规则，需要手工删除

从库防火墙配置

# iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT

#删除可能已经存在的配置，避免出现多条重复记录
$ sudo iptables -D OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT

#增加配置
$ sudo iptables -A OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT

$ sudo iptables -L -n

#保存配置
$ sudo apt-get install iptables-persistent

#注意，iptables-persistent 与 ufw 冲突, 
#现象就是系统重启后执行 sudo ufw status 显示 inactive，
#但是sudo systemctrl ufw status 或sudo service ufw status 显示服务正常，
#实际上ufw并没有正常工作。
#如果两者同时安装，需要参考 https://www.mobibrw.com?p=29330 进行配置

$ sudo netfilter-persistent save

#配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面，
#最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下，
#可能会记录了多余的规则，需要手工删除

# iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT

#删除可能已经存在的配置，避免出现多条重复记录

$ sudo iptables -D OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT

#增加配置

$ sudo iptables -A OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT

$ sudo iptables -L -n

#保存配置

$ sudo apt-get install iptables-persistent

#注意，iptables-persistent 与 ufw 冲突,

#现象就是系统重启后执行 sudo ufw status 显示 inactive，

#但是sudo systemctrl ufw status 或sudo service ufw status 显示服务正常，

#实际上ufw并没有正常工作。

#如果两者同时安装，需要参考 https://www.mobibrw.com?p=29330 进行配置

$ sudo netfilter-persistent save

#配置被保存到/etc/iptables/rules.v4 /etc/iptables/rules.v6这两个文件下面，

#最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下，

#可能会记录了多余的规则，需要手工删除

主数据库master配置

1.修改mysql配置

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

1	$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

在[mysqld]部分进行如下修改：

[mysqld]
#开启二进制日志，默认是注释掉的，我们去掉注释
log-bin = /var/log/mysql/mysql-bin.log 

#设置server-id
server-id = 1 

#默认是127.0.0.1，此处我们设置为任意地址，放开远程访问，这么操作之前一定要确保防火墙配置正确，否则会产生安全风险
bind-address = 0.0.0.0 

#如果数据库是从5.7版本之前升级的，并且是wordpress那么会遇到无法更改数据库的情况，
#NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制的，我们需要去掉这个限制，原因在于
#wordpress创建的表中存在
#`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
#这样的定义是没办法进行后续的操作的，因此我们需要重新定义sql_mode来解除这个限制
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysqld]

#开启二进制日志，默认是注释掉的，我们去掉注释

log-bin = /var/log/mysql/mysql-bin.log

#设置server-id

server-id = 1

#默认是127.0.0.1，此处我们设置为任意地址，放开远程访问，这么操作之前一定要确保防火墙配置正确，否则会产生安全风险

bind-address = 0.0.0.0

#如果数据库是从5.7版本之前升级的，并且是wordpress那么会遇到无法更改数据库的情况，

#NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制的，我们需要去掉这个限制，原因在于

#wordpress创建的表中存在

#`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

#这样的定义是没办法进行后续的操作的，因此我们需要重新定义sql_mode来解除这个限制

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.修改需要同步的表的引擎为INNODB，只有INNODB支持主从，MyISAM不支持

# 此处以wordpress为例,默认情况下wordpress中的wp_options表为MyISAM引擎，
# 这会导致数据同步失败,可能出现的错误信息如下：
# Last_Errno: 1032
# Last_Error: Could not execute Delete_rows event on table wordpress.wp_options; Can't find record in 'wp_options', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 3678486
# 使用“show create table wp_options\G;”查看表的引擎信息
# 单个表修改的命令如下
#mysql -u root -p -e "use wordpress; ALTER TABLE wp_options ENGINE=INNODB;"

#整个数据库修改的命令如下,下面的语句只是生成执行语句，具体执行，还需要把结果拷贝出来执行
$ mysql -u root -p -e "USE wordpress; SET @DATABASE_NAME = 'wordpress'; SELECT GROUP_CONCAT(CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB; ') SEPARATOR '' ) FROM information_schema.TABLES AS t WHERE TABLE_SCHEMA = @DATABASE_NAME AND TABLE_TYPE = 'BASE TABLE' AND ENGINE = 'MyISAM';"

#我这边的例子如下
$ mysql -u root -p -e "USE wordpress; ALTER TABLE wp_IPBLC_blacklist ENGINE=InnoDB; ALTER TABLE wp_IPBLC_login_failed ENGINE=InnoDB; ALTER TABLE wp_IPBLC_usernames ENGINE=InnoDB; ALTER TABLE wp_commentmeta ENGINE=InnoDB; ALTER TABLE wp_comments ENGINE=InnoDB; ALTER TABLE wp_links ENGINE=InnoDB; ALTER TABLE wp_options ENGINE=InnoDB; ALTER TABLE wp_postmeta ENGINE=InnoDB; ALTER TABLE wp_posts ENGINE=InnoDB; ALTER TABLE wp_term_relationships ENGINE=InnoDB; ALTER TABLE wp_term_taxonomy ENGINE=InnoDB; ALTER TABLE wp_terms ENGINE=InnoDB; ALTER TABLE wp_usermeta ENGINE=InnoDB; ALTER TABLE wp_users ENGINE=InnoDB;"

# 此处以wordpress为例,默认情况下wordpress中的wp_options表为MyISAM引擎，

# 这会导致数据同步失败,可能出现的错误信息如下：

# Last_Errno: 1032

# Last_Error: Could not execute Delete_rows event on table wordpress.wp_options; Can't find record in 'wp_options', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos 3678486

# 使用“show create table wp_options\G;”查看表的引擎信息

# 单个表修改的命令如下

#mysql -u root -p -e "use wordpress; ALTER TABLE wp_options ENGINE=INNODB;"

#整个数据库修改的命令如下,下面的语句只是生成执行语句，具体执行，还需要把结果拷贝出来执行

$ mysql -u root -p -e "USE wordpress; SET @DATABASE_NAME = 'wordpress'; SELECT GROUP_CONCAT(CONCAT( 'ALTER TABLE ' ,TABLE_NAME ,' ENGINE=InnoDB; ') SEPARATOR '' ) FROM information_schema.TABLES AS t WHERE TABLE_SCHEMA = @DATABASE_NAME AND TABLE_TYPE = 'BASE TABLE' AND ENGINE = 'MyISAM';"

#我这边的例子如下

$ mysql -u root -p -e "USE wordpress; ALTER TABLE wp_IPBLC_blacklist ENGINE=InnoDB; ALTER TABLE wp_IPBLC_login_failed ENGINE=InnoDB; ALTER TABLE wp_IPBLC_usernames ENGINE=InnoDB; ALTER TABLE wp_commentmeta ENGINE=InnoDB; ALTER TABLE wp_comments ENGINE=InnoDB; ALTER TABLE wp_links ENGINE=InnoDB; ALTER TABLE wp_options ENGINE=InnoDB; ALTER TABLE wp_postmeta ENGINE=InnoDB; ALTER TABLE wp_posts ENGINE=InnoDB; ALTER TABLE wp_term_relationships ENGINE=InnoDB; ALTER TABLE wp_term_taxonomy ENGINE=InnoDB; ALTER TABLE wp_terms ENGINE=InnoDB; ALTER TABLE wp_usermeta ENGINE=InnoDB; ALTER TABLE wp_users ENGINE=InnoDB;"

3.重启mysql，创建用于同步的用户账号

创建用户并授权：用户：repl 密码：slavepass

$ sudo service mysql restart

$ mysql -u root -p -e "CREATE USER 'repl'@'182.254.149.39' IDENTIFIED BY 'slavepass';" #创建用户

$ mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'182.254.149.39';" #分配权限

$ mysql -u root -p -e "flush privileges;"  #刷新权限

$ mysql -u root -p -e "SELECT User, Host FROM mysql.user;"  #查看用户

$ sudo service mysql restart

$ mysql -u root -p -e "CREATE USER 'repl'@'182.254.149.39' IDENTIFIED BY 'slavepass';" #创建用户

$ mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'182.254.149.39';" #分配权限

$ mysql -u root -p -e "flush privileges;" #刷新权限

$ mysql -u root -p -e "SELECT User, Host FROM mysql.user;" #查看用户

4.查看master状态，记录二进制文件名(mysql-bin.000001)和位置(333802)：

# 阻止数据库记录写入，避免后期我们备份数据库的时候数据发生变动
# 该命令对于普通账号的只读模式，root 账号无效，因此访问数据库的账号
# 尽量不要使用root账号，如果是root 账号，只能暂时停止所有访问数据库的服务了
$ mysql -u root -p -e "set global read_only=1;"  

$ mysql -u root -p -e "SHOW MASTER STATUS;"
Enter password: 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   333802 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 阻止数据库记录写入，避免后期我们备份数据库的时候数据发生变动

# 该命令对于普通账号的只读模式，root 账号无效，因此访问数据库的账号

# 尽量不要使用root账号，如果是root 账号，只能暂时停止所有访问数据库的服务了

$ mysql -u root -p -e "set global read_only=1;"

$ mysql -u root -p -e "SHOW MASTER STATUS;"

Enter password:

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 333802 | | | |

+------------------+----------+--------------+------------------+-------------------+

5.主库备份，为从库的第一次数据同步准备数据

使用如下脚本产生数据库备份文件

#此处以备份wordpress数据库为例子

datadump=`which mysqldump`

mysqluser="root"

userpass="password" 

wordpressdb="wordpress"

backupwordpress_sql=$wordpressdb.`date +%Y%m%d`.sql

# 注意，如果是MySQL 8.x那么 --master-data 需要修改成 --source-data 否则在备份的第一句会出现如下警告 
#“WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.”

ver=`mysqldump -V | sed -nre 's/^[^0-9]*(([0-9]+\.)*[0-9]+).*/\1/p'`

args='--master-data'

if [ "${ver}"\>="8.0.0" ]; then
  args='--source-data'
fi

if $datadump $args --single-transaction -u $mysqluser --password=$userpass -h localhost --opt $wordpressdb > $backupwordpress_sql 2>&1
then
  echo " backup $wordpressdb success"
else
  echo " backup $wordpressdb error"
  exit 1
fi

#检验文件尾部是否存在 “-- Dump completed on”，如果存在不存在，则说明备份出错了。
if [ 0 -eq "$(sed '/^$/!h;$!d;g' $backupwordpress_sql | grep -c "Dump completed on")" ]; 
then
  echo " backup $wordpressdb error"
  exit 1	
else
  echo " backup $wordpressdb success"
fi

#此处以备份wordpress数据库为例子

datadump=`which mysqldump`

mysqluser="root"

userpass="password"

wordpressdb="wordpress"

backupwordpress_sql=$wordpressdb.`date +%Y%m%d`.sql

# 注意，如果是MySQL 8.x那么 --master-data 需要修改成 --source-data 否则在备份的第一句会出现如下警告

#“WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.”

ver=`mysqldump -V | sed -nre 's/^[^0-9]*(([0-9]+\.)*[0-9]+).*/\1/p'`

args='--master-data'

if [ "${ver}"\>="8.0.0" ]; then

args='--source-data'

if $datadump $args --single-transaction -u $mysqluser --password=$userpass -h localhost --opt $wordpressdb > $backupwordpress_sql 2>&1

then

echo " backup $wordpressdb success"

else

echo " backup $wordpressdb error"

exit 1

#检验文件尾部是否存在 “-- Dump completed on”，如果存在不存在，则说明备份出错了。

if [ 0 -eq "$(sed '/^$/!h;$!d;g' $backupwordpress_sql | grep -c "Dump completed on")" ];

then

echo " backup $wordpressdb error"

exit 1

else

echo " backup $wordpressdb success"

执行脚本,确保最后输出备份成功

$ cd ~

$ sudo bash backup_wordpress.sh

# 取消普通账号的只读模式
$ mysql -u root -p -e "set global read_only=0;"

$ cd ~

$ sudo bash backup_wordpress.sh

# 取消普通账号的只读模式

$ mysql -u root -p -e "set global read_only=0;"

从服务器slave配置

1.修改mysql配置

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

1	$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

修改server-id，每个数据库的server-id要求是唯一的，不能相互冲突

[mysqld]
#设置server-id，必须唯一
server-id = 2

# 根据业务需要配置数据库是否只读
#read_only = on
#super_read_only = on
#tx_read_only = on

#日志也最好打开
log_bin                 = /var/log/mysql/mysql-bin.log

#如果日志开启了，最好把日志格式设置为row格式，这样如果主从数据不一致，可以尝试mysql flashback功能
binlog-format    = row 

#如果数据库是从5.7版本之前升级的，并且是wordpress那么会遇到无法更改数据库的情况，
#NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制的，我们需要去掉这个限制，原因在于
#wordpress创建的表中存在
#`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
#这样的定义是没办法进行后续的操作的，因此我们需要重新定义sql_mode来解除这个限制
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysqld]

#设置server-id，必须唯一

server-id = 2

# 根据业务需要配置数据库是否只读

#read_only = on

#super_read_only = on

#tx_read_only = on

#日志也最好打开

log_bin = /var/log/mysql/mysql-bin.log

#如果日志开启了，最好把日志格式设置为row格式，这样如果主从数据不一致，可以尝试mysql flashback功能

binlog-format = row

#如果数据库是从5.7版本之前升级的，并且是wordpress那么会遇到无法更改数据库的情况，

#NO_ZERO_IN_DATE,NO_ZERO_DATE这两个参数限制的，我们需要去掉这个限制，原因在于

#wordpress创建的表中存在

#`comment_date` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',

#这样的定义是没办法进行后续的操作的，因此我们需要重新定义sql_mode来解除这个限制

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.首次还原数据库：

#停止可能访问数据库的应用
$ sudo service apache2 stop

$ sudo service php7.0-fpm stop

$ sudo service mysql restart

# 确保无应用访问数据库的情况下，记录主库日志位置，并执行备份脚本
 mysql -u root -p -e "SHOW MASTER STATUS;"
Enter password: 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   333802 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

$ sudo bash backup_wordpress.sh

# 此处已经可以启动刚刚关闭的服务了
$ sudo service php7.0-fpm start

$ sudo service apache2 start

$ scp -P 22 -r root@121.199.27.227:~/wordpress.*.sql ./

#删除可能存在的一行警告信息，这行警告信息可能导致我们无法恢复数据
$ sed -i "/^mysqldump: \[Warning\] Using a password on the command line interface can be insecure\./d" wordpress.*.sql

$ mysql -u root -p -e "stop slave;"

$ mysql -u root -p -e "drop database wordpress;"

$ mysql -u root -p -e "create database wordpress;"

$ mysql -u root -p wordpress < wordpress.*.sql

#停止可能访问数据库的应用

$ sudo service apache2 stop

$ sudo service php7.0-fpm stop

$ sudo service mysql restart

# 确保无应用访问数据库的情况下，记录主库日志位置，并执行备份脚本

mysql -u root -p -e "SHOW MASTER STATUS;"

Enter password:

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 333802 | | | |

+------------------+----------+--------------+------------------+-------------------+

$ sudo bash backup_wordpress.sh

# 此处已经可以启动刚刚关闭的服务了

$ sudo service php7.0-fpm start

$ sudo service apache2 start

$ scp -P 22 -r root@121.199.27.227:~/wordpress.*.sql ./

#删除可能存在的一行警告信息，这行警告信息可能导致我们无法恢复数据

$ sed -i "/^mysqldump: \[Warning\] Using a password on the command line interface can be insecure\./d" wordpress.*.sql

$ mysql -u root -p -e "stop slave;"

$ mysql -u root -p -e "drop database wordpress;"

$ mysql -u root -p -e "create database wordpress;"

$ mysql -u root -p wordpress < wordpress.*.sql

还原完成后，把数据库设置成只读模式,如果从库可写会出现冲突导致同步失败

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

1	$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加如下语句：

[mysqld]
#数据库只读 
#read_only = on 

super_read_only = on 

#tx_read_only = on

[mysqld]

#数据库只读

#read_only = on

super_read_only = on

#tx_read_only = on

3.重启mysql，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

$ sudo service mysql restart

#只读模式
$ mysql -u root -p -e "set global read_only=1;"

#最好使用如下命令获得主库的起始位置
$ grep 'CHANGE MASTER TO MASTER_LOG_FILE'  wordpress.*.sql | more

$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='121.199.27.227', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=333802;"

$ sudo service mysql restart

#只读模式

$ mysql -u root -p -e "set global read_only=1;"

#最好使用如下命令获得主库的起始位置

$ grep 'CHANGE MASTER TO MASTER_LOG_FILE' wordpress.*.sql | more

$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='121.199.27.227', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=333802;"

4.启动slave同步进程：

$ mysql -u root -p -e "start slave;"

1	$ mysql -u root -p -e "start slave;"

5.查看slave状态：

$ mysql -u root -p -e "show slave status\G;"
Enter password:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 121.199.27.227
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 9448236
               Relay_Log_File: VM-114-251-ubuntu-relay-bin.000002
                Relay_Log_Pos: 17780
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
        ...

$ mysql -u root -p -e "show slave status\G;"

Enter password:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 121.199.27.227

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 9448236

Relay_Log_File: VM-114-251-ubuntu-relay-bin.000002

Relay_Log_Pos: 17780

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

...

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
  
# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = game

# 不同步哪些数据库

binlog-ignore-db = mysql

binlog-ignore-db = test

binlog-ignore-db = information_schema

# 只同步哪些数据库，除此之外，其他不同步

binlog-do-db = game

如之前查看master状态时就可以看到只记录了test库，忽略了manual和mysql库。

注意，偶尔在系统升级的时候，从库可能会丢失同步状态配置。这时候，我们需要重新同步，此时我们从从设备/var/lib/mysql/master.info中找到被中断的同步点。

里面的内容一般如下：

25
mysql-bin.000582
4765083
121.199.27.227
xxxx
xxxx
3306
60
1
/etc/mysql/ssl/ca.pem
/etc/mysql/ssl
/etc/mysql/ssl/client-cert.pem

/etc/mysql/ssl/client-key.pem
0
30.000

0
0b674082-f01d-11e9-8f8c-00163e0a4ffe
86400


0

mysql-bin.000582

4765083

121.199.27.227

xxxx

3306

/etc/mysql/ssl/ca.pem

/etc/mysql/ssl

/etc/mysql/ssl/client-cert.pem

/etc/mysql/ssl/client-key.pem

30.000

0b674082-f01d-11e9-8f8c-00163e0a4ffe

86400

注意mysql-bin.000582下面的4765083就是同步位置，在恢复的时候，就是这两个关键数据。

参考链接

解决WordPress更新提示无法连接到FTP服务器

最近WordPress更新到最新版本之后，更新插件的时候提示如下图所示的错误信息（以前一直使用FTPS更新插件）：

观察系统日志，没有发现异常，倒是登陆认证日志出现异常

$ tail -f /var/log/auth.log

1	$ tail -f /var/log/auth.log

出现如下内容：

Mar 15 17:23:27 xxxx sshd[9476]: Did not receive identification string from 121.199.27.227

1	Mar 15 17:23:27 xxxx sshd[9476]: Did not receive identification string from 121.199.27.227

解决方法如下：
在WordPress目录下找到wp-config.php文件并编辑，在最后一行加上：

define('FS_METHOD', "direct");

				1

						define('FS_METHOD', "direct");

貌似这样操作之后，效率快了很多的样子。

参考链接

在指定的页面中阻止WordPress插入额外换行

众所周知WordPress会对编辑器里的内容再格式化一遍，比如自动分段。但有些时候这些添加的格式反而也会让人很头疼。最近遇到WordPress在input,select,pre前会插入额外的换行即<br>，从而破坏页面样式的问题。

继续阅读在指定的页面中阻止WordPress插入额外换行

ubuntu 16.04上调整使用Innodb存储引擎的MySQL性能

最近WordPress上使用的WP Statistics打开的时候，整个网站都几乎处于卡顿的状态，无法正常访问。

使用top命令发现主要是mysqld的CPU占用很高。于是使用mytop分析当前执行的查询语句，发现主要是在查询wp_statistics_visitor表导致的问题（我设置了不要删除浏览记录，因此会导致表内数据偏多）。

大致估计是由于分配给MySQL的内存不足导致频繁的磁盘交换引起的性能问题。

解决方法比较简单，就是增大MySQL可以使用的内存即可。

首先确认服务器上的MySQL使用的存储引擎是否为Innodb（缺省情况下已经是Innodb了）：

$ mysql -p -e "show variables like '%storage_engine%';"

1	$ mysql -p -e "show variables like '%storage_engine%';"

如果确定是Innodb，则查询Innodb被限制使用的内存大小，如下：

$ mysql -p -e "SELECT @@innodb_buffer_pool_size;"

1	$ mysql -p -e "SELECT @@innodb_buffer_pool_size;"

默认情况下是128MB，鉴于内存已经不够了，我们扩大到256MB,这部分内存原则上越大越好，越大与磁盘的交互越少，性能越高。

如下命令调整Innodb的内存：

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

1	$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

在[mysqld]部分增加（如果存在则修改）：

innodb_buffer_pool_size = 256M

1	innodb_buffer_pool_size = 256M

然后重启MySQL服务：

$ sudo service mysql restart

1	$ sudo service mysql restart

上面的调整之后，性能提升还是比较明显的。

参考链接

WordPress 4.9.1无法更新翻译文件

最近更新到WordPress 4.9.1版本，结果出现了系统一直提示“翻译更新”，但是却在更新页面中找不到更新列表的问题，如下图：

继续阅读WordPress 4.9.1无法更新翻译文件

让WordPress 4.9支持上传更多文件类型

WordPress对上传的文件类型是有限制的，在这里，我们以在子主题twentyfifteen-child中增加扩展名为.bz2的文件类型为例子。

首先编辑子主题的functions.php文件

$ sudo vim /var/www/wordpress/wp-content/themes/twentyfifteen-child/functions.php

1	$ sudo vim /var/www/wordpress/wp-content/themes/twentyfifteen-child/functions.php

在文件尾部增加如下内容：

<?php
	/*BUG Fix for after wordpress https://core.trac.wordpress.org/ticket/21299*/
	add_filter( 'wp_check_filetype_and_ext', 'upload_mimes_fix_after_47', 10, 4 );
	function upload_mimes_fix_after_47($data, $file, $filename, $mimes) {
		global $wp_version;
		if( $wp_version == '4.7' || ( (float) $wp_version < 4.7 ) ) { return $data; }
		$filetype = wp_check_filetype( $filename, $mimes );
		return [ 'ext' => $filetype['ext'], 'type' => $filetype['type'], 'proper_filename' => $data['proper_filename'] ];
	}

	/*add upload file type*/
	add_filter('upload_mimes', 'custom_upload_mimes');
	function custom_upload_mimes ( $existing_mimes=array() ) {
		$existing_mimes['bz2'] = 'application/octet-stream';
		return $existing_mimes;
	}
?>

<?php

/*BUG Fix for after wordpress https://core.trac.wordpress.org/ticket/21299*/

add_filter( 'wp_check_filetype_and_ext', 'upload_mimes_fix_after_47', 10, 4 );

function upload_mimes_fix_after_47($data, $file, $filename, $mimes) {

global $wp_version;

if( $wp_version == '4.7' || ( (float) $wp_version < 4.7 ) ) { return $data; }

$filetype = wp_check_filetype( $filename, $mimes );

return [ 'ext' => $filetype['ext'], 'type' => $filetype['type'], 'proper_filename' => $data['proper_filename'] ];

}

/*add upload file type*/

add_filter('upload_mimes', 'custom_upload_mimes');

function custom_upload_mimes ( $existing_mimes=array() ) {

$existing_mimes['bz2'] = 'application/octet-stream';

return $existing_mimes;

}

参考链接

使用Polylang插件让你的WordPress站点支持多语言

如果你的WordPress站点是针对全球用户的，那就很有必要为它添加多语言支持，使用多语言插件Polylang就能很好地实现这个功能。你可以通过Polylang添加所需的语言，然后就可以将网站标题、文章、页面、分类、标签、菜单、小工具等等翻译为多种语言。

该插件可以根据浏览器的语言来自动切换到对应的语言版本。比如你可以像平常一样发布简体中文文章，然后为它添加一个英语版本（英语版的标题和正文内容），那么，如果一个使用英语浏览器的人访问你的网站，就会自动为他显示英语版本的文章。

注：该插件不会自动生成多语言版本的文章，需要你自己翻译

Polylang不仅支持单独的WordPress站点，还支持多站点模式。而且用户可以自己设置网站后台的语言。

参考链接

Ubuntu 16.04 LTS下通过PHP7-FPM配置的服务器增大WordPress文件上传大小限制

一般来说你使用的主机会限制你上传文件的大小，在WordPress的媒体文件上传可以看到，大多数都是2MB。如果是图片的话可能会还够用，但是如果是其他文件就不一定够用了。

增大可以上传的文件大小的话,则执行如下命令

$ sudo vim /etc/php/7.0/fpm/php.ini

1	$ sudo vim /etc/php/7.0/fpm/php.ini

搜索"upload_max_filesize"，把默认的2M修改为需要的大小。
搜索"post_max_size",把默认的8M修改为需要的大小。

重启PHP-FPM服务

$ sudo service php7.0-fpm restart

1	$ sudo service php7.0-fpm restart

重新刷新页面，上传文件即可。

移除WordPress-4.7.5自动加载的emoji脚本

WordPress 4.2版本之后，查看网页源代码你会发现WordPress会自动在加载一段用于支持emjo表情的脚本（JS+CSS）。对于大部分人来说，这个是十分鸡肋的功能，反而影响加载速度,仔细观察一下就会发现，这部分功能引入了非常多的脚本以及代码，实际上绝大部分人从来不使用这部分功能,另外这部分加载脚本在Internet Explorer 11 + HTTPS的时候，会出现警告访问不安全的内容，原因就在于emjo加载脚本在HTTPS的情况下依旧会使用HTTP去请求数据。

继续阅读移除WordPress-4.7.5自动加载的emoji脚本

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30