introduce
As your app or website grows, you may find that you have exceeded your current settings. If you are currently hosting your web server and database backend on the same VPS, a good idea is to separate the two features so that each feature can run and grow on your own machine.
In this guide, we will discuss how to configure a remote database server so that your web server can connect to dynamic content. We will use WordPress as an example so that we have something to manipulate. We will use an Ubuntu 12.04 VPS instance in our demo, configure Nginx on our web server, and then connect it to the MySQL database on the remote machine.
Install MySQL on the database server
First, we will configure a VPS instance as the MySQL server. Storing our data on a separate computer is a great way to expand gracefully when you reach your limit on a stand-alone configuration. It also provides the basic structure needed to scale and load balance your setup more later.
First, we need to install some basic software packages on our database server. These are basically most of the same steps required to set up a database for a traditional LEMP stack, but we don't need all the components (some will be on other servers).
First update the package cache and install the MySQL server:
sudo apt-get update sudo apt-get install mysql-server
During the installation process, you will be asked to select and confirm the root password for MySQL.
After the installation is complete, you need to run the database installation command, which will generate the appropriate directory structure to manage your components:
sudo mysql_install_db
After that, we should strengthen some security by running a script that will ask us if we disable some unsafe default settings
sudo mysql_secure_installation
You will need to enter the MySQL administrator password that you set in the above steps. After that, it will ask you if you want to change the password. If you are satisfied with your current password, enter "N" to indicate that it will not change.
For all other questions, you should just press ENTER to select the default option, which will remove some test databases and lock access.
Configure MySQL to allow remote access
Now that your database is running, we need to change some values to allow other computers to connect.
Use root permissions to open the main configuration file for MySQL in the editor:
sudo nano /etc/mysql/
The file is divided into parts represented by words in square brackets ([ and ]). Found the tag asmysqldPart:
[mysqld]
In this section (within the area between this marker and the next section marker), you need to find a parameter named bind-address. This basically tells the database software which network address to listen to.
Currently, MySQL is configured to only look for connections from your own computer. We need to change it to reference the external IP address that your server can access.
If you host this service in a data center with private network capabilities, use your server's private network IP. Otherwise, you can use the public IP address here:
bind-address = your_database_IP
Save and close the file when finished.
To force MySQL to read the new changes we just implemented, we can restart the database:
sudo service mysql restart
Set up remote WordPress credentials and databases
Now that we have configured MySQL to listen for external addresses, we need to create a database and create a remote user. Although MySQL itself is now listening for IP addresses that other machines can connect to, there is currently no database to access.
This is also an opportunity to establish different permissions based on where the user is connected. We can create two "users", which can actually be the same username but are associated with different hosts.
What I'm saying is that we can create a user that binds to the database server itself and grants it very extensive permissions. We can then use the same username but associated with our web server and give it only the minimum permissions that WordPress requires.
This will allow us to do important work when logging in to our database server while providing only the minimum permissions that our web server needs to do the job. This is a good security policy that partially protects the database server if the web server is compromised.
First connect to MySQL using the root account and administrative password you configured:
mysql -u root -p
You will be asked to enter your MySQL root password and you will get a MySQL prompt.
Let's start creating the database that WordPress will use. We will simply call itwordpress, so that it can be easily identified later:
CREATE DATABASE wordpress;
Now that we have a database, we need to create our local user, if we need to do more database operations. We will call this userwordpressuser, and by using it in the statementlocalhostMatch only connections initiated from the database server itself:
CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password';
Let's grant this account full access to our database:
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost';
Now this user can do anything on WordPress's database, but this account cannot be used remotely because it only matches connections from the local computer.
Let's create a companion account that will only match connections from our web server. To do this, you will need the IP address of your web server. We can name this account any name, but for a more consistent experience, we will use the exact same username as above, just modifying the host part.
Remember that you must use the same IP address as the one you configured in the file. This means that if you use a private network IP, you will want to create the following rules to use your web server's private IP. If you configure MySQL to use the public Internet, you should use the public IP address of the web server.
CREATE USER 'wordpressuser'@'web_server_IP' IDENTIFIED BY 'password';
Now that we have a remote account, we can provide it with a subset of available permissions required for WordPress to operate under normal circumstances. These are select, delete, insert, and update.
While this is the ultimate goal, we are actually unable to implement this at the moment. This is because during some operations you will have to temporarily adjust permissions to allow more access. One of them is actually the initial installation. The easiest way is to grant all permissions at the moment and then we will restrict them after the installation is complete.
For reference, the command we will use to lock the account (don't worry, we will provide you with this command again when you need it) is:
GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';
But now, we will grant all permissions temporarily, which makes it actually the same as the local account at the moment:
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'web_server_ip';
When done, refresh the permissions to write them to disk and start using them:
FLUSH PRIVILEGES;
You can now exit the MySQL prompt by typing:
exit
Test remote and local connections
Before continuing, it is best to verify whether you can use it.wordpressuserThe account connects to the database from the local machine and the web server.
First, test the connection on the database machine by trying to log in from the database server using our new account:
mysql -u wordpressuser -p
Enter the password you set when prompted.
If you see the MySQL prompt, the local connection is successful. You can exit by typing the following command:
exit
Log in to your web server to test the remote connection.
On your web server, you need to install some MySQL client tools to access the remote database. Update the local package cache and install the client tools:
sudo apt-get update sudo apt-get install mysql-client
Now we can connect to our database server using the following syntax:
mysql -u wordpressuser -h database_server_IP -p
Again make sure you are using the correct IP address of the database server. If you have configured MySQL to listen to private networks, enter the private network IP of the database, otherwise enter the public IP address of the database server.
You should be asked to enter the password for your wordpressuser account, and if everything goes well, you should see a MySQL prompt.
If this step succeeds, then you can exit the prompt because you have verified that you can connect remotely.
As an additional check, you can try to do the same from a third server to make sure that other servers are not granted access. You have verified local access and access from the web server, but you have not verified that other connections will be denied.
Try to perform the same steps on a server for which you do not configure a specific user account. You may need to install the client tool like above:
mysql -u wordpressuser -h database_server_IP -p
This should not succeed. It should return an error similar to the following:
ERROR 1130 (HY000): Host '11.111.111.111' is not allowed to connect to this MySQL server
This is the result we expect and what we want.
Setting up a web server
Now that we have verified that our web server has access to the WordPress database, we need to truly turn it into a web server by configuring Nginx, PHP and the necessary components.
Since you updated the package index before running the tests above, we don't need to perform this step again. Let's install all the packages we need:
sudo apt-get install nginx php5-fpm php5-mysql
After all the software is installed, you can start configuring the software.
Configure PHP
Let's start with PHP, because it's simple.
Openphp-fpmPHP configuration file that will handle our dynamic content. We only need to modify one value in this file:
sudo nano /etc/php5/fpm/
searchcgi.fix_pathinfoParameters. It may be commented out and set to "1". We need to uncomment and set it to "0":
cgi.fix_pathinfo=0
This is a safety measure. By setting this option, we tell PHP not to try to guess what the user is trying to access if the exact match cannot be found. If we don't set this up, malicious users can take advantage of this opportunity to let our server execute code that we don't want to execute.
Save and close the file when finished.
Next, we need to open another file to modify how our PHP processor and the web server communicate:
sudo nano /etc/php5/fpm//
Findlistendirective, it should be set to127.0.0.1:9000. We will use a Unix domain socket instead of a port:
listen = /var/run/
Save and close the file when finished.
Now that we have our value, restart our PHP processor:
sudo service php5-fpm restart
Configure Nginx
Now we are ready to configure Nginx. We can start working by copying the default virtual host file to a new file. We will name this file according to the domain name of our website. I'll use the placeholder "":
sudo cp /etc/nginx/sites-available/default /etc/nginx/sites-available/
In the file, we will modify our server block (included inserverThe part in braces). First, uncomment the instruction to listen to port 80. We will also change the root directory and use Nginx to provide PHP index files by default:
server {
listen 80;
root /var/www/;
index ;
Next, we will modifyserver_nameDirective to use our domain name to ensure ourtry_filesis set correctly (if the file is not found, the request is passed to PHP), and our error page is configured:
server {
listen 80;
root /var/www/;
index ;
server_name ;
location / {
try_files $uri $uri/ /?q=$uri&$args;
}
error_page 404 /;
error_page 500 502 503 504 /;
location = / {
root /usr/share/nginx/www;
}
Finally, we need to set up the actual PHP processing by using a location block that will match all our PHP requests. If an exact match is not found, we will immediately return 404. We will also use the sockets we configured for PHP:
server {
listen 80;
root /var/www/;
index ;
server_name ;
location / {
try_files $uri $uri/ /?q=$uri&$args;
}
error_page 404 /;
error_page 500 502 503 504 /;
location = / {
root /usr/share/nginx/www;
}
location ~ \.php$ {
try_files $uri =404;
fastcgi_pass unix:/var/run/;
fastcgi_index ;
include fastcgi_params;
}
}
This is the end of our server block configuration. Save and close the file.
Now, we link this file to our "enabled" directory and delete the link to the default server block file:
sudo rm /etc/nginx/sites-enabled/default sudo ln -s /etc/nginx/sites-available/ /etc/nginx/sites-enabled/
Install WordPress
Now that we have set up a web server with PHP processing, and with a database server, we need to install an application to take advantage of this and connect to our database. As you know now, we will demonstrate this feature using WordPress in this tutorial.
First, download the latest WordPress zip package to your home directory:
cd ~ wget /
Unzip the file, which will create a directory called "wordpress" in your home directory:
tar xzvf
WordPress contains a sample configuration file, but is not used by default. We will rename this file so that it can be read correctly, and then we can open it in a text editor to modify it:
cp ~/wordpress/ ~/wordpress/ nano ~/wordpress/
In it, we need to enter the correct value of our remote database. Remember to use the same IP address you used in your previous remote database tests.
/** Name of WordPress database */
define('DB_NAME', 'wordpress');
/** MySQL database username */
define('DB_USER', 'wordpressuser');
/** MySQL database password */
define('DB_PASSWORD', 'password');
/** MySQL hostname */
define('DB_HOST', 'database_server_ip');
Close the file when finished. In fact, this is the only part of the entire configuration that explicitly connects our web server to the database server.
Next, we need to create the directory structure we set in the Nginx server block configuration. If you remember, I used "" in the demo, but you should use whatever you specified in your Nginx configuration:
sudo mkdir -p /var/www/
Then we will copy it in our~/wordpressThe files and directories found in the directory are to the root of the new document we just created:
sudo cp -r ~/wordpress/* /var/www/
All the files are now in place. The only thing left is to slightly modify our permissions and file ownership. We should first enter the document root directory of the server:
cd /var/www/
We will hand over all the files in this directory to our web server user, i.e. www-data:
sudo chown -R www-data:www-data *
However, we still want to be able to edit these files as our regular non-root user, so we can add our regular non-root account to the web server group. Then, we can give the group permissions to modify the file in this directory:
sudo usermod -a -G www-data your_user sudo chmod -R g+rw /var/www/
Set up a website through the web interface
Now, you only need to complete the installation through the web interface.
Access the domain name (or public IP address) associated with your web server:
You should see the WordPress installation screen, where you need to fill in the relevant information:
!WordPress Management Settings
After you have completed the configuration, you need to log in to the application using the account you just created:
!WordPress Management Login
You will be taken to the admin dashboard, from which you can start configuring your website:
!WordPress Management Dashboard
Restrict remote database permissions
When you have finished configuring WordPress, you should return and revoke permissions to some remote database users.
Most database permissions are not required in daily operations and only need to be enabled when updated or plug-in installation. Keep this in mind in case errors occur when performing administrative operations after performing these steps.
Some plugins may require additional permissions. Investigate each plugin to see what it needs and consider choosing the one that requires minimal extra access.
Log in to your database server. Then, use the MySQL root account to log in to MySQL:
mysql -u root -p
Enter your password to gain access.
You can view the current permissions of the remote user by entering the following command:
show grants for 'wordpressuser'@'web_server_IP';
+---------------------------------------------------------------------------------------------------------------------------+ | Grants for wordpressuser@ | +---------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wordpressuser'@'' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' | | GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wordpressuser'@'' | +---------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
The "usage" permission actually means there is no real permission, so we don't have to worry about it. The second permission line was originally set by us, allowing it to bewordpressAll rights reserved on the database.
The process of applying new permissions that are more stringent than the current permissions is actually two steps.
First, we need to revoke all current permissions. Do this by typing the following command:
REVOKE ALL PRIVILEGES on wordpress.* FROM 'wordpressuser'@'web_server_IP';
If now we request the current authorization again, we will see that the second line has disappeared:
show grants for 'wordpressuser'@'web_server_IP';
+---------------------------------------------------------------------------------------------------------------------------+ | Grants for [email protected] | +---------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'wordpressuser'@'' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' | +---------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Now we can add permissions we wish to re-add to the account. we need toUPDATE、INSERT、SELECTandDELETEPermissions for daily use:
GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';
If we check again, we can see that our fine-grained permissions are now set.
To tell MySQL to reread the permission table to implement our changes, we can enter:
FLUSH PRIVILEGES;
After that, exit MySQL again:
exit
in conclusion
If you've been following, you should now have a good understanding of how to get the remote database to communicate with your application. While we discussed some WordPress-specific steps, general ideas, especially those related to MySQL configuration and user permissions, are applicable in most remote MySQL situations.
The above is the detailed operation steps for setting up a remote database using MySQL to optimize website performance. For more information about setting up a remote database in MySQL, please pay attention to my other related articles!