如何在 Ubuntu 18.04 上使用 MySQL 设置远程数据库以优化网站性能

1sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2. . .
3[mysqld]
4. . .

1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[mysqld]
3. . .
4bind-address = db_server_ip

1[label /etc/mysql/mysql.conf.d/mysqld.cnf]
2[mysqld]
3. . .
4require_secure_transport = on
5. . .

1sudo mysql_ssl_rsa_setup --uid=mysql

1sudo systemctl restart mysql

1sudo netstat -plunt | grep mysqld

1[secondary_label Output]
2tcp 0 0 db_server_ip:3306 0.0.0.0:*               LISTEN 27328/mysqld

1sudo ufw allow mysql

1sudo mysql

1mysql -u root -p

1CREATE DATABASE wordpress;

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

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

1CREATE USER 'remotewpuser'@'web_server_ip' IDENTIFIED BY 'password';

1GRANT ALL PRIVILEGES ON wordpress.* TO 'remotewpuser'@'web_server_ip';

1FLUSH PRIVILEGES;

1exit

1mysql -u wpuser -p

1exit

1[environment local]
2ssh sammy@web_server_ip

1[environment second]
2sudo apt update

1[environment second]
2sudo apt install mysql-client

1[environment second]
2mysql -u remotewpuser -h db_server_ip -p

1[environment second]
2status

 1[secondary_label Output]
 2[environment second]
 3--------------
 4mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper
 5
 6Connection id:		52
 7Current database:
 8Current user:		[email protected]
 9SSL:			Cipher in use is DHE-RSA-AES256-SHA
10Current pager:		stdout
11Using outfile:		''
12Using delimiter:	;
13Server version:		5.7.18-0ubuntu0.16.04.1 (Ubuntu)
14Protocol version:	10
15Connection:		203.0.113.111 via TCP/IP
16Server characterset:	latin1
17Db characterset:	latin1
18Client characterset:	utf8
19Conn. characterset:	utf8
20TCP port:		3306
21Uptime:			3 hours 43 min 40 sec
22
23Threads: 1 Questions: 1858 Slow queries: 0 Opens: 276 Flush tables: 1 Open tables: 184 Queries per second avg: 0.138
24--------------

1[environment second]
2exit

1[environment third]
2mysql -u wordpressuser -h db_server_ip -p

1[secondary_label Output]
2[environment third]
3ERROR 1130 (HY000): Host '203.0.113.12' is not allowed to connect to this MySQL server

1[environment second]
2cd ~
3curl -O https://wordpress.org/latest.tar.gz

1[environment second]
2tar xzvf latest.tar.gz

1[environment second]
2cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php

1[environment second]
2curl -s https://api.wordpress.org/secret-key/1.1/salt/

 1[secondary_label Output]
 2[environment second]
 3define('AUTH_KEY',         'L4|2Yh(giOtMLHg3#] DO NOT COPY THESE VALUES %G00o|te^5YG@)');
 4define('SECURE_AUTH_KEY',  'DCs-k+MwB90/-E(=!/ DO NOT COPY THESE VALUES +WBzDq:7U[#Wn9');
 5define('LOGGED_IN_KEY',    '*0kP!|VS.K=;#fPMlO DO NOT COPY THESE VALUES +&[%8xF*,18c @');
 6define('NONCE_KEY',        'fmFPF?UJi&(j-{8=$- DO NOT COPY THESE VALUES CCZ?Q+_~1ZU~;G');
 7define('AUTH_SALT',        '@qA7f}2utTEFNdnbEa DO NOT COPY THESE VALUES t}Vw+8=K%20s=a');
 8define('SECURE_AUTH_SALT', '%BW6s+d:7K?-`C%zw4 DO NOT COPY THESE VALUES 70U}PO1ejW+7|8');
 9define('LOGGED_IN_SALT',   '-l>F:-dbcWof%4kKmj DO NOT COPY THESE VALUES 8Ypslin3~d|wLD');
10define('NONCE_SALT',       '4J(<`4&&F (WiK9K#] DO NOT COPY THESE VALUES ^ZikS`es#Fo:V6');

1[environment second]
2nano ~/wordpress/wp-config.php

 1[label /wordpress/wp-config.php]
 2[environment second]
 3. . .
 4define('AUTH_KEY',         'put your unique phrase here');
 5define('SECURE_AUTH_KEY',  'put your unique phrase here');
 6define('LOGGED_IN_KEY',    'put your unique phrase here');
 7define('NONCE_KEY',        'put your unique phrase here');
 8define('AUTH_SALT',        'put your unique phrase here');
 9define('SECURE_AUTH_SALT', 'put your unique phrase here');
10define('LOGGED_IN_SALT',   'put your unique phrase here');
11define('NONCE_SALT',       'put your unique phrase here');
12. . .

 1[label /wordpress/wp-config.php]
 2[environment second]
 3. . .
 4/** The name of the database for WordPress */
 5define('DB_NAME', 'wordpress');
 6
 7/** MySQL database username */
 8define('DB_USER', 'remotewpuser');
 9
10/** MySQL database password */
11define('DB_PASSWORD', 'password');
12
13/** MySQL hostname */
14define('DB_HOST', 'db_server_ip');
15. . .

1[label /wordpress/wp-config.php]
2[environment second]
3define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

1[environment second]
2sudo cp -a ~/wordpress/* /var/www/html

1[environment second]
2sudo chown -R www-data:www-data /var/www/html

1http://example.com