为WordPress网站配置远程数据库-牛奇网

安装 MariaDB

首先在服务器上安装MariaDB。

安装MariaDB：

sudo apt install mariadb-server

2、设置root用户密码：

sudo mysql_secure_installation

允许远程连接

按照下面的步骤，进行配置：

编辑下面的文件，将文件中“bind-address” 的值更改为数据库服务器的IP地址，这样就允许MariaDB接受远程连接：

/etc/mysql/mariadb.conf.d/50-server.cnf

bind-address = 192.0.2.100

2.重新启动MariaDB，并设置防火墙允许连接到3306端口。

sudo systemctl restart mysql

sudo ufw allow mysql

3.以root用户身份登录MariaDB，创建数据库和远程用户，并授予远程用户对数据库的访问权限。将192.0.2.255替换为Web服务器的IP：

sudo mysql -u root -p

CREATE DATABASE wordpress;

CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON wordpress.* TO 'wpuser'@'localhost';

CREATE USER 'wpuser'@'192.0.2.255' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON wordpress.* TO 'wpuser'@'192.0.2.255';

FLUSH PRIVILEGES;

exit

4.使用新用户测试登录:

mysql -u wpuser -p status; exit

从web服务器连接远程数据库

在web服务器上，按照如下步骤进行：

1.Web服务器需要安装MariaDB。如果还没有安装，执行如下命令安装MariaDB和php-MySQL：

sudo apt update && sudo apt install mariadb-client php-mysql

2.使用新建的远程用户测试远程登录。将 192.0.2.100 替换为数据库的IP：

mysql -u wpuser -h 192.0.2.100 -p status;

exit

如果配置没有问题，应该可以从web服务器连接到远程数据库服务器了。

配置 WordPress 使用远程数据库

首次通过Web界面安装和配置Wordpress的时候，WordPress会创建一个名为wp-config.php的文件，通过配置该文件就可以连接远程数据库了。

1.进入Wordpress网站的根目录，通过拷贝Wordpress网站配置文件样本，创建Wordpress网站配置文件。

cd /var/www/html/example.com/public_html

sudo cp wp-config-sample.php wp-config.php

2.将以下各个变量替换为远程服务器的相关内容，将 “192.0.2.100” 替换为远程数据库的IP地址：

/var/www/html/example.com/public_html/wp-config.php

/** The name of the database for WordPress */
define('DB_NAME', 'wordpress'); 
** MySQL database username */
define('DB_USER', 'wpuser'); 
/** MySQL database password */
define('DB_PASSWORD', 'password'); 
/** MySQL hostname */
define('DB_HOST', '192.0.2.100');

在配置了网站SSL证书后，我们在该段信息前面添加如下的代码，强制Wordpress使用SSL连接远程数据库。

define（'MYSQL_CLIENT_FLAGS'，MYSQLI_CLIENT_SSL）;

添加登录密码

使用 WordPress 安全密钥生成器随机生成一个复杂的密码，然后将密码拷贝到wp-config.php文件中：

/var/www/html/example.com/public_html/wp-config.php

/**#@+
 * Authentication Unique Keys and Salts.
 *
 * Change these to different unique phrases!
 * You can generate these using the {@link https://api.wordpress.org/secret-key/1.1/salt/ WordPress.org secret-key service}
 * You can change these at any point in time to invalidate all existing cookies. This will force all users to have to log in again.
 *
 * @since 2.6.0
 */
define('AUTH_KEY',         'put your unique phrase here');
define('SECURE_AUTH_KEY',  'put your unique phrase here');
define('LOGGED_IN_KEY',    'put your unique phrase here');
define('NONCE_KEY',        'put your unique phrase here');
define('AUTH_SALT',        'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT',   'put your unique phrase here');
define('NONCE_SALT',       'put your unique phrase here');
/**#@-*/

配置WordPress通过SSL连接数据库

1.在web服务器上创建一个SSL证书目录：

mkdir ~/certs

2.在远程数据库服务器上，也创建一个SSL证书目录，并进入该目录：

mkdir ~/certs && cd ~/certs

3.生成一个CA密钥并创建证书和私钥。此示例中的密钥有效期为100年。在接下来的步骤中，根据需要将-days 36500设定为自己想要的有效期限：

sudo openssl genrsa 4096 > ca-key.pem

sudo openssl req -new -x509 -nodes -days 36500 -key ca-key.pem -out cacert.pem

You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:PA
Locality Name (eg, city) []:Phila
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:MariaDB
Email Address []:

4.创建服务器证书并写入RSA密钥。通用名称（Common Name）填写Web服务器的FQDN或IP地址：

sudo openssl req -newkey rsa:4096 -days 36500 -nodes -keyout server-key.pem -out server-req.pem

Generating a 4096 bit RSA private key
......................+++
.............................+++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:PA
Locality Name (eg, city) []:Phila
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:203.0.113.15
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

sudo openssl req -newkey rsa:4096 -days 36500 -nodes -keyout server-key.pem -out server-req.pem

5.签署证书:

sudo openssl x509 -req -in server-req.pem -days 36500 -CA cacert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

6.将密钥和证书移至固定的位置:

sudo mkdir /etc/mysql/ssl 
sudo mv *.* /etc/mysql/ssl && cd /etc/mysql/ssl

7.生成客户端密钥。根据需要填写信息，并将“通用名称（Common Name）”设置为Web服务器的FQDN或IP地址：

sudo openssl req -newkey rsa:2048 -days 36500 -nodes -keyout client-key.pem -out client-req.pem

Generating a 4096 bit RSA private key
....................+++
............................................................................................+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:PA
Locality Name (eg, city) []:Phila
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:203.0.113.15
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

8.写入RSA密钥:

sudo openssl rsa -in client-key.pem -out client-key.pem

9.签署客户证书:

sudo openssl x509 -req -in client-req.pem -days 36500 -CA cacert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

10.验证证书:

openssl verify -CAfile cacert.pem server-cert.pem client-cert.pem

11.配置MariaDB服务器以使用证书。查找以下行，并删除＃以取消注释。输入证书所在的目标路径：

File: /etc/mysql/mariadb.conf.d/50-server.cnf

ssl-ca=/etc/mysql/ssl/cacert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

12.登录到MariaDB，并配置通过SSL才能登录数据库。将192.0.2.255替换为Web服务器IP地址：

sudo mysql -u root -p
GRANT ALL PRIVILEGES ON wordpress.* TO 'wpuser'@'192.0.2.255' REQUIRE SSL;
FLUSH PRIVILEGES;
exit

13.重启 MariaDB:

sudo systemctl restart mysql

14.将证书和密钥复制到Web服务器。将example_user替换为Web服务器用户，并将192.0.2.255替换为Web服务器IP地址：

scp cacert.pem client-cert.pem client-key.pem example_user@192.0.2.255:~/certs

On the web server

1.创建目录并将证书和密钥移动到/ etc / mysql / ssl目录：

sudo mkdir /etc/mysql/ssl && sudo mv ~/certs/*.* /etc/mysql/ssl

2.配置Web服务器的MariaDB客户端使用SSL。找到[mysql]部分，并添加证书和密钥的路径：

/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

[mysql]
ssl-ca=/etc/mysql/ssl/cacert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

注意：如果Web服务器使用MySQL，则可以在/etc/mysql/mysql.conf.d/mysqld.cnf中找到配置文件。

3.登录到远程数据库，以测试通过SSL登录：

mysql -u wpuser -h 192.0.2.100 -p

4.查看状态:

status;

5.退出 MariaDB:

exit

6.在wp-config文件的远程数据库信息之前添加一个指令，该指令强制WordPress使用SSL进行数据库连接：

/var/www/html/example.com/public_html/wp-config.php

...
define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );

/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wpuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', '192.0.2.100');
...