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

1sudo apt-get update
2sudo apt-get install mysql-server

1systemctl status mysql

1[secondary_label Output]
2● mysql.service - MySQL Community Server
3   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
4   Active: active (running) since Tue 2017-05-23 14:54:04 UTC; 12s ago
5 Main PID: 27179 (mysqld)
6   CGroup: /system.slice/mysql.service
7           └─27179 /usr/sbin/mysqld

1mysql_secure_installation

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

1[label mysqld.cnf]
2. . .
3[mysqld]

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

1[label /etc/mysql/my.cnf]
2. . .
3require_secure_transport = on

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

1mysql -u root -p

1CREATE DATABASE wordpress;

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

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

1CREATE USER 'wordpressuser'@'web-server_ip' IDENTIFIED BY 'password';

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

1FLUSH PRIVILEGES;

1exit

1mysql -u wordpressuser -p

1exit

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

1[environment second]
2mysql -u wordpressuser -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',         '1jl/vqfs<XhdXoAPz9 DO NOT COPY THESE VALUES c_j{iwqD^<+c9.k<J@4H');
 4define('SECURE_AUTH_KEY',  'E2N-h2]Dcvp+aS/p7X DO NOT COPY THESE VALUES {Ka(f;rv?Pxf})CgLi-3');
 5define('LOGGED_IN_KEY',    'W(50,{W^,OPB%PB<JF DO NOT COPY THESE VALUES 2;y&,2m%3]R6DUth[;88');
 6define('NONCE_KEY',        'll,4UC)7ua+8<!4VM+ DO NOT COPY THESE VALUES #`DXF+[$atzM7 o^-C7g');
 7define('AUTH_SALT',        'koMrurzOA+|L_lG}kf DO NOT COPY THESE VALUES 07VC*Lj*lD&?3w!BT#-');
 8define('SECURE_AUTH_SALT', 'p32*p,]z%LZ+pAu:VY DO NOT COPY THESE VALUES C-?y+K0DK_+F|0h{!_xY');
 9define('LOGGED_IN_SALT',   'i^/G2W7!-1H2OQ+t$3 DO NOT COPY THESE VALUES t6**bRVFSD[Hi])-qS`|');
10define('NONCE_SALT',       'Q6]U:K?j4L%Z]}h^q7 DO NOT COPY THESE VALUES 1% ^qUswWgn+6&xqHN&%');

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

 1[label 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 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', 'wordpressuser');
 9
10/** MySQL database password */
11define('DB_PASSWORD', 'password');
12
13/** MySQL hostname */
14define('DB_HOST', 'db_server_ip');
15. . .

1[label 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