{"id":1107,"date":"2018-08-01T10:11:46","date_gmt":"2018-08-01T04:41:46","guid":{"rendered":"https:\/\/www.coimbatorewebhosting.com\/blog\/?p=1107"},"modified":"2019-04-09T16:21:49","modified_gmt":"2019-04-09T10:51:49","slug":"configure-wp-to-remote-database","status":"publish","type":"post","link":"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/","title":{"rendered":"How to configure WP to use a Remote Database"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Configure_WP_to_use_a_Remote_Database\" >Configure WP to use a Remote Database<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_1_Log_in_via_SSH_on_both_servers\" >Step 1: Log in via SSH on both servers:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_2_Update_all_packages\" >Step 2: Update all packages<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_3_Install_MariaDB_server_on_the_Database_VPS\" >Step 3: Install MariaDB server\u00a0on the Database VPS<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_4_Install_LAMP_stack_on_a_Web_VPS\" >Step 4: Install LAMP stack on a Web VPS<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#To_accept_remote_connections\" >To accept remote connections<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_5_Create_a_MariaDB_database_for_WordPress_on_the_Database_VPS\" >Step 5: Create a MariaDB database for WordPress on the Database VPS<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_6_Configure_the_MariaDB_server_on_database_VPS_to_listen_on_public_IP_or_all_interfaces\" >Step 6: Configure the MariaDB server on database VPS to listen on public IP (or all interfaces)<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_7_Install_WordPress_on_the_Web_VPS\" >Step 7: Install WordPress on the Web VPS<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/coimbatorewebhosting.com\/blog\/configure-wp-to-remote-database\/#Step_8_Configure_Apache_to_serve_WordPress\" >Step 8: Configure Apache to serve WordPress<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"Configure_WP_to_use_a_Remote_Database\"><\/span><span style=\"text-decoration: underline;\">Configure WP to use a Remote Database<\/span><span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>WordPress is the most popular content management system (CMS) based on PHP and MySQL. When your WordPress CMS grows in traffic and you have outgrown your current server resources, a professional solution is to host your database on a separate database server.<\/p>\n<p>With this solution, you can optimize your database and your web server independently and both servers can grown on it\u2019s own machine. In this tutorial we will guide you on how to configure WordPress to use a remote database, on a\u00a0CentOS 7 based VPS.<\/p>\n<p>Prerequisite:<\/p>\n<p>In order to run WordPress on your CentOS 7 VPS and configure it to use a remote database, we need the following requirements pre-installed:<\/p>\n<ul>\n<li>A Web VPS on which we will install the WordPress instance.<\/li>\n<li>A Database VPS with MariaDB or MySQL installed on it. The database will be hosted on this VPS.<\/li>\n<\/ul>\n<p>WordPress requires the following:<\/p>\n<ul>\n<li>Web server: Apache, Nginx<\/li>\n<li>PHP version 7.2 or newer, with JSON support, mbstring, zip and GD2 extensions.<\/li>\n<li>MariaDB version 10.0 or greater or MySQL database server version 5.6 or newer<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Step_1_Log_in_via_SSH_on_both_servers\"><\/span><strong>Step 1: Log in via SSH on both servers:<br \/>\n<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Log in to each VPS via SSH as user root<\/p>\n<pre>ssh roo@IP_Address -p Port_number<\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Step_2_Update_all_packages\"><\/span><strong>Step 2: Update all packages<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Once you are logged, run the following command on both servers to make sure that all installed RPM packages are up to date<\/p>\n<pre>yum -y update<\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Step_3_Install_MariaDB_server_on_the_Database_VPS\"><\/span><strong>Step 3: Install MariaDB server\u00a0<\/strong><strong>on the Database VPS<\/strong><strong><br \/>\n<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<pre>yum -y mariadb mariadb-server<\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Step_4_Install_LAMP_stack_on_a_Web_VPS\"><\/span><strong>Step 4: Install LAMP stack on a Web VPS<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Go through the steps in the URL to install<\/p>\n<p><a href=\"https:\/\/www.coimbatorewebhosting.com\/blog\/easy-steps-to-install-and-configure-lamp-in-centos-7\/\">https:\/\/www.coimbatorewebhosting.com\/blog\/easy-steps-to-install-and-configure-lamp-in-centos-7\/<\/a><\/p>\n<p>Next, install PHP 7.2 along with the required PHP extensions:<\/p>\n<pre>yum -y install php php-cli php-mbstring php-gd php-mysqlnd php<code>-xmlrpc\u00a0php-xml\u00a0php-zip\u00a0php-curl<\/code><\/pre>\n<p>And finally, complete the LAMP installation by installing MariaDB client package:<\/p>\n<pre>yum -y install mariadb mariadb-server<\/pre>\n<p>Start the service and set it to start on reboot<\/p>\n<pre>systemctl start mariadb\r\nsystemctl enable mariadb<\/pre>\n<h2><span class=\"ez-toc-section\" id=\"To_accept_remote_connections\"><\/span>To accept remote connections<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Edit the MariaDB configuration file (\/etc\/my.cnf.d\/server.cnf) and change the following line:<\/p>\n<pre>bind-address = xxx.0.0.1<\/pre>\n<p>with:<\/p>\n<pre>bind-address = web_server_IP_address<\/pre>\n<p>Do not forget to replace \u2018web_server_IP_address\u2019 with the public IP of the web server.<\/p>\n<p>If you want to configure MariaDB to listen on all interfaces on the web VPS, set:<\/p>\n<pre>bind-address =\u00a00.0.0.0<\/pre>\n<p>Restart MariaDB for the changes to take effect:<\/p>\n<pre>systemctl restart mariadb.service<\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Step_5_Create_a_MariaDB_database_for_WordPress_on_the_Database_VPS\"><\/span>Step 5: Create a MariaDB database for WordPress on the Database VPS<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Log in to MariaDB console with the root user account:<\/p>\n<pre># mysql -u root -p<\/pre>\n<p>Create a new MariaDB database for WordPress using the following query:<\/p>\n<pre>mysql&gt; CREATE DATABASE wpdb;<\/pre>\n<p>Create a new MariaDB user for WordPress using the following query:<\/p>\n<pre>mysql&gt; CREATE USER 'wpuser'@'localhost';\r\nmysql&gt; CREATE USER 'wpuser'@'database_VPS_IP';<\/pre>\n<p>Then execute the following query to add a separate user for WordPress that will interact with the MariaDB database:<\/p>\n<pre>mysql&gt; GRANT ALL PRIVILEGES ON wpdb.* to 'wpuser'@'localhost' IDENTIFIED BY '5tr0ng_Pa55w0rd';\r\nmysql&gt; GRANT ALL PRIVILEGES ON wpdb.* to 'wpuser'@'database_VPS_IP' IDENTIFIED BY '5tr0ng_Pa55w0rd';<\/pre>\n<p>Do not forget to replace database_VPS_IP with the actual IP address of the database VPS .<\/p>\n<p>Execute the following command to apply the privileges we set:<\/p>\n<pre>mysql&gt; FLUSH PRIVILEGES;<\/pre>\n<p>Now we can exit the MariaDB session:<\/p>\n<pre>mysql&gt; quit<\/pre>\n<h3><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1328\" src=\"https:\/\/www.coimbatorewebhosting.com\/blog\/wp-content\/uploads\/2018\/08\/2.jpg\" alt=\"remote database\" width=\"799\" height=\"483\" srcset=\"https:\/\/coimbatorewebhosting.com\/blog\/wp-content\/uploads\/2018\/08\/2.jpg 799w, https:\/\/coimbatorewebhosting.com\/blog\/wp-content\/uploads\/2018\/08\/2-300x181.jpg 300w, https:\/\/coimbatorewebhosting.com\/blog\/wp-content\/uploads\/2018\/08\/2-768x464.jpg 768w, https:\/\/coimbatorewebhosting.com\/blog\/wp-content\/uploads\/2018\/08\/2-99x60.jpg 99w\" sizes=\"auto, (max-width: 799px) 100vw, 799px\" \/><\/h3>\n<h3><span class=\"ez-toc-section\" id=\"Step_6_Configure_the_MariaDB_server_on_database_VPS_to_listen_on_public_IP_or_all_interfaces\"><\/span>Step 6: Configure the MariaDB server on database VPS to listen on public IP (or all interfaces)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Edit the MariaDB configuration file (\/etc\/my.cnf.d\/server.cnf) and change the following line:<\/p>\n<pre>bind-address = xxx.0.0.1<\/pre>\n<p>with:<\/p>\n<pre>bind-address = database_server_IP_address<\/pre>\n<p>Or, configure MariaDB to listen on all interfaces on the database VPS:<\/p>\n<pre>bind-address =\u00a00.0.0.0<\/pre>\n<p>Restart MariaDB for the changes to take effect:<\/p>\n<pre>systemctl restart mariadb.service<\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Step_7_Install_WordPress_on_the_Web_VPS\"><\/span><strong>Step 7: Install WordPress on the Web VPS<br \/>\n<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Download the latest WordPress version available at\u00a0<a href=\"https:\/\/wordpress.org\/download\/\" rel=\"nofollow noopener\" target=\"_blank\">https:\/\/wordpress.org\/download\/<\/a> and extract it in a directory on your server:<\/p>\n<pre>wget\u00a0https:\/\/wordpress.org\/latest.zip\r\n\r\nunzip -d\u00a0\/var\/www\/html\/ latest.zip<\/pre>\n<p>Set proper permissions on WordPress files and directories:<\/p>\n<pre>chown apache:apache -R \/var\/www\/html\/wordpress\/<\/pre>\n<p>Rename wp-config-sample.php WordPress configuration file to wp-config.php:<\/p>\n<pre>mv \/var\/www\/html\/wordpress\/wp-config-sample.php \/var\/www\/html\/wordpress\/wp-config.php<\/pre>\n<p>Edit the wp-config.php file and modify the following lines<\/p>\n<pre>vi\u00a0\/var\/www\/html\/wordpress\/wp-config.php<\/pre>\n<pre>\/** The name of the database for WordPress *\/\r\ndefine('DB_NAME', 'wpdb');\r\n\r\n\/** MySQL database username *\/\r\ndefine('DB_USER', 'wpuser');\r\n\r\n\/** MySQL database password *\/\r\ndefine('DB_PASSWORD', '5tr0ng_Pa55w0rd');\r\n\r\n\/** MySQL hostname *\/\r\ndefine('DB_HOST', 'database_VPS_IP');<\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Step_8_Configure_Apache_to_serve_WordPress\"><\/span>Step 8: Configure Apache to serve WordPress<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Now we will have to setup the Apache configuration so it can serve the WordPress directory. Add the contents below in the \/etc\/httpd\/conf.d\/wordpress.conf file using vi or your favorite editor:<\/p>\n<pre># vi \/etc\/httpd\/conf.d\/wordpress.conf<\/pre>\n<p>Add the following lines:<\/p>\n<pre>&lt;VirtualHost *:80&gt;\r\nServerAdmin admin@your-domain.com\r\nDocumentRoot \/var\/www\/html\/wordpress\r\nServerName your-domain.com\r\nServerAlias www.your-domain.com\r\n\r\nAlias \/matomo \u201c\/var\/www\/html\/wordpress\/\u201d\r\n&lt;Directory \/var\/www\/html\/wordpress\/&gt;\r\nOptions +FollowSymlinks\r\nAllowOverride All\r\n\r\n&lt;\/Directory&gt;\r\n\r\nErrorLog \/var\/log\/httpd\/wordpress-error_log\r\nCustomLog \/var\/log\/httpd\/wordpress-access_log common\r\n&lt;\/VirtualHost&gt;\r\n<\/pre>\n<p>Save the changes and restart Apache for the changes to take effect:<\/p>\n<pre>systemctl restart httpd<\/pre>\n<p>Open http:\/\/your-domain.com in your favorite web browser and finish the WordPress installation.<\/p>\n<p>You have successfully configured WordPress to use a remote database on\u00a0a\u00a0CentOS 7server.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Configure WP to use a Remote Database WordPress is the most popular content management system (CMS) based on PHP and MySQL. When your WordPress CMS grows in traffic and you have outgrown your current server resources, a professional solution is to host your database on a separate database server. With [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1112,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[152],"tags":[99,22,97,98],"class_list":["post-1107","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","tag-configuration-file","tag-content-management-system","tag-mysql-database","tag-remote-connections"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/coimbatorewebhosting.com\/blog\/wp-content\/uploads\/2018\/08\/remote-database.png","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pa2YU7-hR","_links":{"self":[{"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/posts\/1107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/comments?post=1107"}],"version-history":[{"count":0,"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/posts\/1107\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/media\/1112"}],"wp:attachment":[{"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/media?parent=1107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/categories?post=1107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/coimbatorewebhosting.com\/blog\/wp-json\/wp\/v2\/tags?post=1107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}