One place for hosting & domains


      How to Implement Microsoft SQL Servers in a Private Cloud for Maximum Performance

      There are many considerations to take into account when implementing a Microsoft SQL Server in a private cloud environment. Today’s SQL dependent applications have different performance and high availability (HA) requirements. As a solutions architect, my goal is to provide our customers the best performing, highly available designs while managing budgetary concerns, scalability, supportability and total cost of ownership. Like so many tasks in IT infrastructure strategy, success is all about planning. There are many moving pieces and balancing everything to reach our goal becomes a challenge if we don’t ask the right questions up front.

      In this two-part series, I’ll share my approach to scoping, sizing and designing private cloud infrastructure capable of migrating or standing up new Microsoft SQL Server environments. In part one, I’ll identify performance considerations and provide real world examples to make sure that your SQL Server environment is ready to meet application, growth and DR requirements of your organization. In part two, I’ll focus on SQL Server deployment options with high availability.

      If you need a review on SQL server basics before we dive into the private cloud design, you can brush up here.

      Here’s what we’ll cover in this post, with links if you’d prefer to jump ahead:

      SQL Server Performance Considerations: RAM, IOPS, CPU and More

      What follows are the basic performance considerations to take into account when designing a Microsoft SQL Server environment.

      RAM—These requirements are based on database size and developer recommendations. Ideally, you’ll have enough RAM to put the entire database into RAM. However, that’s not always possible with large DB sizes. RAM is delicious to SQL and the server will eat it up, so be generous if your budget allows. Leave 20 percent of RAM reserved on the server for OS and other services.

      IOPS—The SQL Server is mostly a read/write machine, and its performance is dependent on disk IO and storage latency. With SSDs becoming more affordable, many SQL DBAs now prefer to run on SSDs due to high IOPS provided by SSD and NVMe drives. In the past, many 15K SAS disks in RAID10 were the norm for data volumes.

      Low latency is essential to SQL performance. By today’s standards, keeping latency below 5ms per IO is the norm. Sub 1ms latency is very common with local SSD storage. However, 10ms is still a good response time for most medium performance applications.

      CPU—Bare metal CPU allocation is easy. Your server has a number of CPUs and all of them can be allocated to SQL with no negative considerations, other than licensing costs. However, allocating CPU to a SQL Server in a VMWare private cloud environment should be done with caution. Licensing is based on CPU cores. Too much assigned, but unused, vCPU GHz in a VMWare VM can negatively impact performance. Rightsizing is key.

      The SQL Server is not normally a CPU hog. Unexpected and prolonged high CPU usage during production time means something is not right with the database or SQL code. Adding CPU in those cases may not solve the issue. These unexpected conditions should be checked by SQL DBAs and developers. Higher than normal CPU utilization is to be expected during maintenance time. In instances where a database may require high CPU utilization as a norm, a developer or SQL DBA should check the database for missing indexes or other issues before adding more vCPUs to virtual servers.

      SQL as a VM

      Based on the above considerations, when designing a SQL Server environment, budgetary and licensing considerations may start leaning your design toward SQL as a VM on a private cloud with a restricted number of assigned vCPUs. Running SQL on a private cloud is a great way to save on licensing costs. It’s also a good performer for most application loads, and because SQL is more of an IO-dependent system, its performance will greatly depend on the latency and IO availability of your storage system.

      Storage Systems IO Availability

      SAN storage will normally provide great amounts of IO based on the amount of disks and disk types on the SAN. The norm to expect from many SAN systems is 5ms to 15ms of latency per IO. When your application desires sub 1ms latency, such as gaming, financial or ad-tech systems, a local SSD RAID10 disk array will save the day. These local disk arrays are easy to set up and use with both VMs and bare metal server implementations. You may ask, “What about the HA and extra redundancy features of using a SAN instead of local disk?” I will be discussing High Availability in a performance-demanding environment in Part 2: Deploying Microsoft SQL Servers in a Private Cloud with High Availability. Check back soon.

      In the end, your application’s performance requirements and budget will drive your decision whether to run a private cloud or a bare metal SQL deployment. Both VM and bare metal deployments can be configured with high availability and will easily integrate into your private cloud environments.

      With these performance considerations in mind, let’s discuss other scoping and sizing matrices that we’ll need to design our high-performing and future-proofed SQL deployment.

      Scoping and Sizing for Microsoft SQL Deployments in a Private Cloud

      As a Solution Engineer at INAP, I collect numerous measurements during the SQL scoping process. Before delving into numbers, I start by evaluating pain points clients may have with their current SQL Servers. I want to know what hurts.

      Asking these and other questions helps me understand how to resolve issues and future proof your next SQL deployment:

      • Is it performing to your expectation?
      • Are you meeting your SQL database maintenance time window?
      • Do your users complain that their SQL dependent application freezes sometimes for no reason?
      • When was the last time you restored a database from your backup and how long did that take?
      • What’s your failover plan in case your production database server dies?
      • What backup software is being used for SQL?
      • Are your databases running in simple or full restore mode?

      Once I know the exact problem, or if I’m designing for a new application, I start by collecting specific technical details. The most common specific measurements and requirements collected during the scoping phase are:

      • DB sizes for all databases per SQL instance
      • Instances (Is there more than one SQL instance, and why?)
      • Growth requirements (Usually measured in daily change rate to help with other considerations like backups and replication for DR purposes)
      • RAM requirements & CPU requirements
      • Performance requirements, such as latency/ IO and IOPS requirements
      • HA requirements (Can your customer base wait for you to restore your SQL Server in case of an outage? How long does it take to restore?)
      • Regulatory/Compliance requirements such as HIPAA and PCI
      • Maintenance schedule (Do the maintenance jobs complete on time every day)
      • Replication requirements
      • Reporting requirements (Does this deployment need a reporting server so as to not interrupt production workloads)
      • Backup (What is used today to backup production data? Has it been tested? What are the challenges?)

      In environments requiring high performance database response times, we utilize native Windows Server tools, such as perfmon, to collect very detailed performance matrixes from exiting SQL Servers to identify performance bottlenecks and other considerations that help us resolve these issues in current or future database deployments. Because a good SQL Server’s performance is heavily dependent on the disk sub-systems, we will go deeper in disk design recommendations for private clouds, VMs and bare metal deployments.


      SQL Server Disk Layout for Performance, HA and DR

      Separating database files into different disks is a best practice. It helps performance and helps your DBA easily identify performance issues when troubleshooting. For example, you could have a runaway query beating up your TempDB. If your TempDB is on a separate disk from your prod database files, you can easily identify that the TempDB disk is being thrashed and that same TempDB issue is not stepping on other workloads in your environments.

      For high performance requirements, SSDs are recommended. The following is a basic disk layout for performance:

      • Data (MDF, NDF files): Fast read/write disk, many drives in an array preferred for best performance
      • Index (NDF files, not often used): Fast read/write disk, many drives in an array preferred for best performance
      • Log (LDF files): Fast write performance, not much reading happens here
      • TempDB (Temp database to crunch numbers and formulas): Should be a fast disk. SSD is preferred. Do not combine with other data or log files.
      • Page File (NTFS): Keep this on a separate disk LUN or Array if possible. C: drive is not a good place for a page file on a SQL Server.

      In a SQL deployment that requires DR, the above disk layout will allow you to granularly replicate just the SQL data and the OS that needs to be replicated, leaving TempDB and pagefile out of the replication design since those files are reset during reboot. TempDB and pagefile also produce lots of noisy IO. Replicating those files will result in unnecessarily heavy bandwidth and disk utilization.

      Check back soon for Part 2: Deploying Microsoft SQL Servers in a Private Cloud with High Availability

      Rob Lerner


      Source link

      How to Benchmark the Performance of a Redis Server on Ubuntu 18.04


      Benchmarking is an important practice when it comes to analyzing the overall performance of database servers. It’s helpful for identifying bottlenecks as well as opportunities for improvement within those systems.

      Redis is an in-memory data store that can be used as database, cache and message broker. It supports from simple to complex data structures including hashes, strings, sorted sets, bitmaps, geospatial data, among other types. In this guide, we’ll demonstrate how to benchmark the performance of a Redis server running on Ubuntu 18.04, using a few different tools and methods.


      To follow this guide, you’ll need:

      Note: The commands demonstrated in this tutorial were executed on a dedicated Redis server running on a 4GB DigitalOcean Droplet.

      Redis comes with a benchmark tool called redis-benchmark. This program can be used to simulate an arbitrary number of clients connecting at the same time and performing actions on the server, measuring how long it takes for the requests to be completed. The resulting data will give you an idea of the average number of requests that your Redis server is able to handle per second.

      The following list details some of the common command options used with redis-benchmark:

      • -h: Redis host. Default is
      • -p: Redis port. Default is 6379.
      • -a: If your server requires authentication, you can use this option to provide the password.
      • -c: Number of clients (parallel connections) to simulate. Default value is 50.
      • -n: How many requests to make. Default is 100000.
      • -d: Data size for SET and GET values, measured in bytes. Default is 3.
      • -t: Run only a subset of tests. For instance, you can use -t get,set to benchmark the performance of GET and SET commands.
      • -P: Use pipelining for performance improvements.
      • -q: Quiet mode, shows only the average requests per second information.

      For instance, if you want to check the average number of requests per second that your local Redis server can handle, you can use:

      You will get output similar to this, but with different numbers:


      PING_INLINE: 85178.88 requests per second PING_BULK: 83056.48 requests per second SET: 72202.16 requests per second GET: 94607.38 requests per second INCR: 84961.77 requests per second LPUSH: 78988.94 requests per second RPUSH: 88652.48 requests per second LPOP: 87950.75 requests per second RPOP: 80971.66 requests per second SADD: 80192.46 requests per second HSET: 84317.03 requests per second SPOP: 78125.00 requests per second LPUSH (needed to benchmark LRANGE): 84175.09 requests per second LRANGE_100 (first 100 elements): 52383.45 requests per second LRANGE_300 (first 300 elements): 21547.08 requests per second LRANGE_500 (first 450 elements): 14471.78 requests per second LRANGE_600 (first 600 elements): 9383.50 requests per second MSET (10 keys): 71225.07 requests per second

      You can also limit the tests to a subset of commands of your choice using the -t parameter. The following command shows the averages for the GET and SET commands only:

      • redis-benchmark -t set,get -q


      SET: 76687.12 requests per second GET: 82576.38 requests per second

      The default options will use 50 parallel connections to create 100000 requests to the Redis server. If you want to increase the number of parallel connections to simulate a peak in usage, you can use the -c option for that:

      • redis-benchmark -t set,get -q -c 1000

      Because this will use 1000 concurrent connections instead of the default 50, you should expect a decrease in performance:


      SET: 69444.45 requests per second GET: 70821.53 requests per second

      If you want detailed information in the output, you can remove the -q option. The following command will use 100 parallel connections to run 1000000 SET requests on the server:

      • redis-benchmark -t set -c 100 -n 1000000

      You will get output similar to this:


      ====== SET ====== 1000000 requests completed in 11.29 seconds 100 parallel clients 3 bytes payload keep alive: 1 95.22% <= 1 milliseconds 98.97% <= 2 milliseconds 99.86% <= 3 milliseconds 99.95% <= 4 milliseconds 99.99% <= 5 milliseconds 99.99% <= 6 milliseconds 100.00% <= 7 milliseconds 100.00% <= 8 milliseconds 100.00% <= 8 milliseconds 88605.35 requests per second

      The default settings use 3 bytes for key values. You can change this with the option -d. The following command will benchmark GET and SET commands using 1MB key values:

      • redis-benchmark -t set,get -d 1000000 -n 1000 -q

      Because the server is working with a much bigger payload this time, a significant decrease of performance is expected:


      SET: 1642.04 requests per second GET: 822.37 requests per second

      It is important to realize that even though these numbers are useful as a quick way to evaluate the performance of a Redis instance, they don't represent the maximum throughput a Redis instance can sustain. By using pipelining, applications can send multiple commands at once in order to improve the number of requests per second the server can handle. With redis-benchmark, you can use the -P option to simulate real world applications that make use of this Redis feature.

      To compare the difference, first run the redis-benchmark command with default values and no pipelining, for the GET and SET tests:

      • redis-benchmark -t get,set -q


      SET: 86281.27 requests per second GET: 89847.26 requests per second

      The next command will run the same tests, but will pipeline 8 commands together:

      • redis-benchmark -t get,set -q -P 8


      SET: 653594.81 requests per second GET: 793650.75 requests per second

      As you can see from the output, there is a substantial performance improvement with the use of pipelining.

      Checking Latency with redis-cli

      If you'd like a simple measurement of the average time a request takes to receive a response, you can use the Redis client to check for the average server latency. In the context of Redis, latency is a measure of how long does a ping command take to receive a response from the server.

      The following command will show real-time latency stats for your Redis server:

      You'll get output similar to this, showing an increasing number of samples and a variable average latency:


      min: 0, max: 1, avg: 0.18 (970 samples)

      This command will keep running indefinitely. You can stop it with a CTRL+C.

      To monitor latency over a certain period of time, you can use:

      • redis-cli --latency-history

      This will track latency averages over time, with a configurable interval that is set to 15 seconds by default. You will get output similar to this:


      min: 0, max: 1, avg: 0.18 (1449 samples) -- 15.01 seconds range min: 0, max: 1, avg: 0.16 (1449 samples) -- 15.00 seconds range min: 0, max: 1, avg: 0.17 (1449 samples) -- 15.00 seconds range min: 0, max: 1, avg: 0.17 (1444 samples) -- 15.01 seconds range min: 0, max: 1, avg: 0.17 (1446 samples) -- 15.01 seconds range min: 0, max: 1, avg: 0.17 (1449 samples) -- 15.00 seconds range min: 0, max: 1, avg: 0.16 (1444 samples) -- 15.00 seconds range min: 0, max: 1, avg: 0.17 (1445 samples) -- 15.01 seconds range min: 0, max: 1, avg: 0.16 (1445 samples) -- 15.01 seconds range ...

      Because the Redis server on our example is idle, there's not much variation between latency samples. If you have a peak in usage, however, this should be reflected as an increase in latency within the results.

      If you'd like to measure the system latency only, you can use --intrinsic-latency for that. The intrinsic latency is inherent to the environment, depending on factors such as hardware, kernel, server neighbors and other factors that aren't controlled by Redis.

      You can see the intrinsic latency as a baseline for your overall Redis performance. The following command will check for the intrinsic system latency, running a test for 30 seconds:

      • redis-cli --intrinsic-latency 30

      You should get output similar to this:


      … 498723744 total runs (avg latency: 0.0602 microseconds / 60.15 nanoseconds per run). Worst run took 22975x longer than the average latency.

      Comparing both latency tests can be helpful for identifying hardware or system bottlenecks that could affect the performance of your Redis server. Considering the total latency for a request to our example server has an average of 0.18 microseconds to complete, an intrinsic latency of 0.06 microseconds means that one third of the total request time is spent by the system in processes that aren't controlled by Redis.

      Memtier is a high-throughput benchmark tool for Redis and Memcached created by Redis Labs. Although very similar to redis-benchmark in various aspects, Memtier has several configuration options that can be tuned to better emulate the kind of load you might expect on your Redis server, in addition to offering cluster support.

      To get Memtier installed on your server, you'll need to compile the software from source. First, install the dependencies necessary to compile the code:

      • sudo apt-get install build-essential autoconf automake libpcre3-dev libevent-dev pkg-config zlib1g-dev

      Next, go to your home directory and clone the memtier_benchmark project from its Github repository:

      • cd
      • git clone

      Navigate to the project directory and run the autoreconf command to generate the application configuration scripts:

      • cd memtier_benchmark
      • autoreconf -ivf

      Run the configure script in order to generate the application artifacts required for compiling:

      Now run make to compile the application:

      Once the build is finished, you can test the executable with:

      • ./memtier_benchmark --version

      This will give you the following output:


      memtier_benchmark 1.2.17 Copyright (C) 2011-2017 Redis Labs Ltd. This is free software. You may redistribute copies of it under the terms of the GNU General Public License <>. There is NO WARRANTY, to the extent permitted by law.

      The following list contains some of the most common options used with the memtier_benchmark command:

      • -s: Server host. Default is localhost.
      • -p: Server port. Default is 6379.
      • -a: Authenticate requests using the provided password.
      • -n: Number of requests per client (default is 10000).
      • -c: Number of clients (default is 50).
      • -t: Number of threads (default is 4).
      • --pipeline: Enable pipelining.
      • --ratio: Ratio between SET and GET commands, default is 1:10.
      • --hide-histogram: Hides detailed output information.

      Most of these options are very similar to the options present in redis-benchmark, but Memtier tests performance in a different way. To simulate common real-world environments better, the default benchmark performed by memtier_benchmark will test for GET and SET requests only, on a ratio of 1 to 10. With 10 GET operations for each SET operation in the test, this arrangement is more representative of a common web application using Redis as a database or cache. You can adjust the ratio value with the option --ratio.

      The following command runs memtier_benchmark with default settings, while providing only high-level output information:

      • ./memtier_benchmark --hide-histogram

      Note: if you have configured your Redis server to require authentication, you should provide the -a option along with your Redis password to the memtier_benchmark command:

      • ./memtier_benchmark --hide-histogram -a your_redis_password

      You'll see output similar to this:


      ... 4 Threads 50 Connections per thread 10000 Requests per client ALL STATS ========================================================================= Type Ops/sec Hits/sec Misses/sec Latency KB/sec ------------------------------------------------------------------------- Sets 8258.50 --- --- 2.19800 636.05 Gets 82494.28 41483.10 41011.18 2.19800 4590.88 Waits 0.00 --- --- 0.00000 --- Totals 90752.78 41483.10 41011.18 2.19800 5226.93

      According to this run of memtier_benchmark, our Redis server can execute about 90 thousand operations per second in a 1:10 SET/GET ratio.

      It's important to note that each benchmark tool has its own algorithm for performance testing and data presentation. For that reason, it's normal to have slightly different results on the same server, even when using similar settings.


      In this guide, we demonstrated how to perform benchmark tests on a Redis server using two distinct tools: the included redis-benchmark, and the memtier_benchmark tool developed by Redis Labs. We also saw how to check for the server latency using redis-cli. Based on the data obtained from these tests, you'll have a better understanding of what to expect from your Redis server in terms of performance, and what are the bottlenecks of your current setup.

      Source link

      How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04


      As your application or website grows, there may come a point where you’ve outgrown your current server setup. If you are hosting your web server and database backend on the same machine, it may be a good idea to separate these two functions so that each can operate on its own hardware and share the load of responding to your visitors’ requests.

      In this guide, we’ll go over how to configure a remote MySQL database server that your web application can connect to. We will use WordPress as an example in order to have something to work with, but the technique is widely applicable to any application backed by MySQL.


      Before beginning this tutorial, you will need:

      • Two Ubuntu 18.04 servers. Each should have a non-root user with sudo privileges and a UFW firewall enabled, as described in our Initial Server Setup with Ubuntu 18.04 tutorial. One of these servers will host your MySQL backend, and throughout this guide we will refer to it as the database server. The other will connect to your database server remotely and act as your web server; likewise, we will refer to it as the web server over the course of this guide.
      • Nginx and PHP installed on your web server. Our tutorial How To Install Linux, Nginx, MySQL, PHP (LEMP stack) in Ubuntu 18.04 will guide you through the process, but note that you should skip Step 2 of this tutorial, which focuses on installing MySQL, as you will install MySQL on your database server.
      • MySQL installed on your database server. Follow “How To Install MySQL on Ubuntu 18.04” to set this up.
      • Optionally (but strongly recommended), TLS/SSL certificates from Let’s Encrypt installed on your web server. You’ll need to purchase a domain name and have DNS records set up for your server, but the certificates themselves are free. Our guide How To Secure Nginx with Let’s Encrypt on Ubuntu 18.04 will show you how to obtain these certificates.

      Step 1 — Configuring MySQL to Listen for Remote Connections

      Having one’s data stored on a separate server is a good way to expand gracefully after hitting the performance ceiling of a one-machine configuration. It also provides the basic structure necessary to load balance and expand your infrastructure even more at a later time. After installing MySQL by following the prerequisite tutorial, you’ll need to change some configuration values to allow connections from other computers.

      Most of the MySQL server’s configuration changes can be made in the mysqld.cnf file, which is stored in the /etc/mysql/mysql.conf.d/ directory by default. Open up this file with root privileges in your preferred editor. Here, we’ll use nano:

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

      This file is divided into sections denoted by labels in square brackets ([ and ]). Find the section labeled mysqld:


      . . .
      . . .

      Within this section, look for a parameter called bind-address. This tells the database software which network address to listen for connections on.

      By default, this is set to, meaning that MySQL is configured to only look for local connections. You need to change this to reference an external IP address where your server can be reached.

      If both of your servers are in a datacenter with private networking capabilities, use your database server’s private network IP. Otherwise, you can use its public IP address:


      . . .
      bind-address = db_server_ip

      Because you’ll connect to your database over the internet, it’s recommended that you require encrypted connections to keep your data secure. If you don’t encrypt your MySQL connection, anybody on the network could sniff sensitive information between your web and database servers. To encrypt MySQL connections, add the following line after the bind-address line you just updated:


      . . .
      require_secure_transport = on
      . . .

      Save and close the file when you are finished. If you’re using nano, do this by pressing CTRL+X, Y, and then ENTER.

      For SSL connections to work, you will need to create some keys and certificates. MySQL comes with a command that will automatically set these up. Run the following command, which creates the necessary files. It also makes them readable by the MySQL server by specifying the UID of the mysql user:

      • sudo mysql_ssl_rsa_setup --uid=mysql

      To force MySQL to update its configuration and read the new SSL information, restart the database:

      • sudo systemctl restart mysql

      To confirm that the server is now listening on the external interface, run the following netstat command:

      • sudo netstat -plunt | grep mysqld


      tcp 0 0 db_server_ip:3306* LISTEN 27328/mysqld

      netstat prints statistics about your server’s networking system. This output shows us that a process called mysqld is attached to the db_server_ip at port 3306, the standard MySQL port, confirming that the server is listening on the appropriate interface.

      Next, open up that port on the firewall to allow traffic through:

      Those are all the configuration changes you need to make to MySQL. Next, we will go over how to set up a database and some user profiles, one of which you will use to access the server remotely.

      Step 2 — Setting Up a WordPress Database and Remote Credentials

      Even though MySQL itself is now listening on an external IP address, there are currently no remote-enabled users or databases configured. Let's create a database for WordPress, and a pair of users that can access it.

      Begin by connecting to MySQL as the root MySQL user:

      Note: If you have password authentication enabled, as described in Step 3 of the prerequisite MySQL tutorial, you will instead need to use the following command to access the MySQL shell:

      After running this command, you will be asked for your MySQL root password and, after entering it, you'll be given a new mysql> prompt.

      From the MySQL prompt, create a database that WordPress will use. It may be helpful to give this database a recognizable name so that you can easily identify it later on. Here, we will name it wordpress:

      • CREATE DATABASE wordpress;

      Now that you've created your database, you next need to create a pair of users. We will create a local-only user as well as a remote user tied to the web server’s IP address.

      First, create your local user, wordpressuser, and make this account only match local connection attempts by using localhost in the declaration:

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

      Then grant this account full access to the wordpress 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. With this in mind, create a companion account that will match connections exclusively from your web server. For this, you'll need your web server's IP address.

      Please note that you must use an IP address that utilizes the same network that you configured in your mysqld.cnf file. This means that if you specified a private networking IP in the mysqld.cnf file, you'll need to include the private IP of your web server in the following two commands. 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';

      After creating your remote account, give it the same privileges as your local user:

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

      Lastly, flush the privileges so MySQL knows to begin using them:

      Then exit the MySQL prompt by typing:

      Now that you've set up a new database and a remote-enabled user, you can move on to testing whether you're able to connect to the database from your web server.

      Step 3 — Testing Remote and Local Connections

      Before continuing, it's best to verify that you can connect to your database from both the local machine — your database server — and from your web server with each of the wordpressuser accounts.

      First, test the local connection from your database server by attempting to log in with your new account:

      • mysql -u wordpressuser -p

      When prompted, enter the password that you set up for this account.

      If you are given a MySQL prompt, then the local connection was successful. You can exit out again by typing:

      Next, log into your web server to test remote connections:

      You'll need to install some client tools for MySQL on your web server in order to access the remote database. First, update your local package cache if you haven't done so recently:

      Then install the MySQL client utilities:

      • sudo apt install mysql-client

      Following this, connect to your database server using the following syntax:

      • mysql -u wordpressuser -h db_server_ip -p

      Again, you must make sure that you are using the correct IP address for the database server. If you configured MySQL to listen on the private network, enter your database's private network IP. Otherwise, enter your database server's public IP address.

      You will be asked for the password for your wordpressuser account. After entering it, and if everything is working as expected, you will see the MySQL prompt. Verify that the connection is using SSL with the following command:

      If the connection is indeed using SSL, the SSL: line will indicate this, as shown here:


      -------------- mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper Connection id: 52 Current database: Current user: wordpressuser@ SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu) Protocol version: 10 Connection: via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 3 hours 43 min 40 sec Threads: 1 Questions: 1858 Slow queries: 0 Opens: 276 Flush tables: 1 Open tables: 184 Queries per second avg: 0.138 --------------

      After verifying that you can connect remotely, go ahead and exit the prompt:

      With that, you've verified local access and access from the web server, but you have not verified that other connections will be refused. For an additional check, try doing the same thing from a third server for which you did not configure a specific user account in order to make sure that this other server is not granted access.

      Note that before running the following command to attempt the connection, you may have to install the MySQL client utilities as you did above:

      • mysql -u wordpressuser -h db_server_ip -p

      This should not complete successfully, and should throw back an error that looks similar to this:


      ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server

      This is expected, since you haven't created a MySQL user that's allowed to connect from this server, and also desired, since you want to be sure that your database server will deny unauthorized users access to your MySQL server.

      After successfully testing your remote connection, you can proceed to installing WordPress on your web server.

      Step 4 — Installing WordPress

      To demonstrate the capabilities of your new remote-capable MySQL server, we will go through the process of installing and configuring WordPress — the popular content management system — on your web server. This will require you to download and extract the software, configure your connection information, and then run through WordPress's web-based installation.

      On your web server, download the latest release of WordPress to your home directory:

      • cd ~
      • curl -O

      Extract the files, which will create a directory called wordpress in your home directory:

      WordPress includes a sample configuration file which we'll use as a starting point. Make a copy of this file, removing -sample from the filename so it will be loaded by WordPress:

      • cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php

      When you open the file, your first order of business will be to adjust some secret keys to provide more security to your installation. WordPress provides a secure generator for these values so that you do not have to try to come up with good values on your own. These are only used internally, so it won't hurt usability to have complex, secure values here.

      To grab secure values from the WordPress secret key generator, type:

      • curl -s

      This will print some keys to your output. You will add these to your wp-config.php file momentarily:

      Warning! It is important that you request your own unique values each time. Do not copy the values shown here!


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

      Copy the output you received to your clipboard, then open the configuration file in your text editor:

      • nano ~/wordpress/wp-config.php

      Find the section that contains the dummy values for those settings. It will look something like this:


      . . .
      define('AUTH_KEY',         'put your unique phrase here');
      define('SECURE_AUTH_KEY',  'put your unique phrase here');
      define('LOGGED_IN_KEY',    'put your unique phrase here');
      define('NONCE_KEY',        'put your unique phrase here');
      define('AUTH_SALT',        'put your unique phrase here');
      define('SECURE_AUTH_SALT', 'put your unique phrase here');
      define('LOGGED_IN_SALT',   'put your unique phrase here');
      define('NONCE_SALT',       'put your unique phrase here');
      . . .

      Delete those lines and paste in the values you copied from the command line.

      Next, enter the connection information for your remote database. These configuration lines are at the top of the file, just above where you pasted in your keys. Remember to use the same IP address you used in your remote database test earlier:


      . . .
      /** The name of the database for WordPress */
      define('DB_NAME', 'wordpress');
      /** MySQL database username */
      define('DB_USER', 'wordpressuser');
      /** MySQL database password */
      define('DB_PASSWORD', 'password');
      /** MySQL hostname */
      define('DB_HOST', 'db_server_ip');
      . . .

      And finally, anywhere in the file, add the following line which tells WordPress to use an SSL connection to our MySQL database:



      Save and close the file.

      Next, copy the files and directories found in your ~/wordpress directory to Nginx's document root. Note that this command includes the -a flag to make sure all the existing permissions are carried over:

      • sudo cp -a ~/wordpress/* /var/www/html

      After this, the only thing left to do is modify the file ownership. Change the ownership of all the files in the document root over to www-data, Ubuntu's default web server user:

      • sudo chown -R www-data:www-data /var/www/html

      With that, WordPress is installed and you're ready to run through its web-based setup routine.

      Step 5 — Setting Up WordPress Through the Web Interface

      WordPress has a web-based setup process. As you go through it, it will ask a few questions and install all the tables it needs in your database. Here, we will go over the initial steps of setting up WordPress, which you can use as a starting point for building your own custom website that uses a remote database backend.

      Navigate to the domain name (or public IP address) associated with your web server:

      You will see a language selection screen for the WordPress installer. Select the appropriate language and click through to the main installation screen:

      WordPress install screen

      Once you have submitted your information, you will need to log into the WordPress admin interface using the account you just created. You will then be taken to a dashboard where you can customize your new WordPress site.


      By following this tutorial, you've set up a MySQL database to accept SSL-protected connections from a remote WordPress installation. The commands and techniques used in this guide are applicable to any web application written in any programming language, but the specific implementation details will differ. Refer to your application or language's database documentation for more information.

      Source link