Set Up Remote Database

Now that we have MySQL configured listening on an external address, we need to create a database and establish a remote user. Even though MySQL itself is now listening on an IP address that other machines can connect to, there are not currently any databases that it can access. This is also an opportunity for us to establish different privileges based on where a user is connecting from. We can create two “users” that can actually just be the same username, but associated with different hosts. What I mean by this is that we can create a user that is bound to the database server itself and grant it very broad permissions. We can then use the same username, but associated with our web server and give it only the permissions that WordPress requires. This will allow us to do heavy duty work while logged into our database server, while only providing our web server with the bare minimum of permissions it needs to accomplish its job. This is a good security policy that will partially shield the database server in the event that the web server is compromised. Begin by connecting to MySQL using the root account and administrative password you configured:

mysql -u root -p

You will be asked for your MySQL root password and then you’ll be given a MySQL prompt. To get this started, let’s create the database that WordPress will use. We will just call this wordpress so that we can easily identify it in later:

CREATE DATABASE wordpress;

Now that we have a database, we need to create our local user, which will be used to do more intense database operations if ever need be. We will call this user wordpressuser and make this account only match connection attempts originating from the database server itself by using localhost in the declaration:

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

Let’s go ahead and grant this account full access to our database:

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

This user can now do any operation on the database for WordPress, but this account cannot be used remotely, as it only matches connections from the local machine. Let’s create a companion account that will match connections exclusively from our web server. For this, you’ll need your web server’s IP address. We could name this account anything, but for a more consistent experience, we’re going to use the exact same username as we did above, with only the host portion modified. Keep in mind that you must use an IP address that utilizes the same network that you configured in yourmy.cnf file. This means that if you used a private networking IP, you’ll want to create the rule below to use the private IP of your web server. If you configured MySQL to use the public internet, you should match that with the web server’s public IP address.

CREATE USER 'wordpressuser'@'web_server_IP' IDENTIFIED BY 'password';

Now that we have our remote account, we can give it the subset of available privileges that WordPress requires to operate under normal circumstances. These are select, delete, insert, and update. While this is the final goal, we are actually not able to implement this at this point. This is because during certain operations, you will have to adjust permissions temporarily to allow more access. One of these is actually the initial installation. It is easiest to just grant all privileges at the moment and then we will restrict them after we are finished with the installation. For reference, the command we will be using to lock down the account (don’t worry, we will give you this command again when you need it) is this:

GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';

But for now, we will temporarily grant all privileges, which makes it effectively identical to the local account for the time being:

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

We will come back to this after we have configured WordPress. If you are not actually installing WordPress and are using this guide simply to learn how to separate your web server from your database, you may be able to use the more restrictive settings now. It depends on your web application, so look up the minimum database privileges needed for your application. Flush the privileges to write them to disk and begin using them:

FLUSH PRIVILEGES;

Now, you can exit the MySQL prompt by typing:

exit

Other Codes：

To set root as a remote user:

CREATE USER 'root'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

Reference：

How To Set Up a Remote Database to Optimize Site Performance with MySQL | DigitalOcean Connect to mysql on Amazon EC2 from a remote server – Stack Overflow