One place for hosting & domains


      How To Optimize MySQL with Query Cache on Ubuntu 18.04

      The author selected the Apache Software Foundation to receive a donation as part of the Write for DOnations program.


      Query cache is a prominent MySQL feature that speeds up data retrieval from a database. It achieves this by storing MySQL SELECT statements together with the retrieved record set in memory, then if a client requests identical queries it can serve the data faster without executing commands again from the database.

      Compared to data read from disk, cached data from RAM (Random Access Memory) has a shorter access time, which reduces latency and improves input/output (I/O) operations. As an example, for a WordPress site or an e-commerce portal with high read calls and infrequent data changes, query cache can drastically boost the performance of the database server and make it more scalable.

      In this tutorial, you will first configure MySQL without query cache and run queries to see how quickly they are executed. Then you’ll set up query cache and test your MySQL server with it enabled to show the difference in performance.

      Note: Although query cache is deprecated as of MySQL 5.7.20, and removed in MySQL 8.0, it is still a powerful tool if you’re using supported versions of MySQL. However, if you are using newer versions of MySQL, you may adopt alternative third-party tools like ProxySQL to optimize performance on your MySQL database.


      Before you begin, you will need the following:

      Step 1 — Checking the Availability of Query Cache

      Before you set up query cache, you’ll check whether your version of MySQL supports this feature. First, ssh into your Ubuntu 18.04 server:

      • ssh user_name@your_server_ip

      Then, run the following command to log in to the MySQL server as the root user:

      Enter your MySQL server root password when prompted and then press ENTER to continue.

      Use the following command to check if query cache is supported:

      • show variables like 'have_query_cache';

      You should get an output similar to the following:


      +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.01 sec)

      You can see the value of have_query_cache is set to YES and this means query cache is supported. If you receive an output showing that your version does not support query cache, please see the note in the Introduction section for more information.

      Now that you have checked and confirmed that your version of MySQL supports query cache, you will move on to examining the variables that control this feature on your database server.

      Step 2 — Checking the Default Query Cache Variables

      In MySQL, a number of variables control query cache. In this step, you'll check the default values that ship with MySQL and understand what each variable controls.

      You can examine these variables using the following command:

      • show variables like 'query_cache_%' ;

      You will see the variables listed in your output:


      +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.00 sec)

      The query_cache_limit value determines the maximum size of individual query results that can be cached. The default value is 1,048,576 bytes and this is equivalent to 1MB.

      MySQL does not handle cached data in one big chunk; instead it is handled in blocks. The minimum amount of memory allocated to each block is determined by the query_cache_min_res_unit variable. The default value is 4096 bytes or 4KB.

      query_cache_size controls the total amount of memory allocated to the query cache. If the value is set to zero, it means query cache is disabled. In most cases, the default value may be set to 16,777,216 (around 16MB). Also, keep in mind that query_cache_size needs at least 40KB to allocate its structures. The value allocated here is aligned to the nearest 1024 byte block. This means the reported value may be slightly different from what you set.

      MySQL determines the queries to cache by examining the query_cache_type variable. Setting this value to 0 or OFF prevents caching or retrieval of cached queries. You can also set it to 1 to enable caching for all queries except for ones beginning with the SELECT SQL_NO_CACHE statement. A value of 2 tells MySQL to only cache queries that begin with SELECT SQL_CACHE command.

      The variable query_cache_wlock_invalidate controls whether MySQL should retrieve results from the cache if the table used on the query is locked. The default value is OFF.

      Note: The query_cache_wlock_invalidate variable is deprecated as of MySQL version 5.7.20. As a result, you may not see this in your output depending on the MySQL version you're using.

      Having reviewed the system variables that control the MySQL query cache, you'll now test how MySQL performs without first enabling the feature.

      Step 3 — Testing Your MySQL Server Without Query Cache

      The goal of this tutorial is to optimize your MySQL server by using the query cache feature. To see the difference in speed, you're going to run queries and see their performance before and after implementing the feature.

      In this step you're going to create a sample database and insert some data to see how MySQL performs without query cache.

      While still logged in to your MySQL server, create a database and name it sample_db by running the following command:

      • Create database sample_db;


      Query OK, 1 row affected (0.00 sec)

      Then switch to the database:


      Database changed

      Create a table with two fields (customer_id and customer_name) and name it customers:

      • Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;


      Query OK, 0 rows affected (0.01 sec)

      Then, run the following commands to insert some sample data:

      • Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
      • Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
      • Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
      • Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
      • Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
      • Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
      • Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
      • Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
      • Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
      • Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');


      Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ...

      The next step is starting the MySQL profiler, which is an analysis service for monitoring the performance of MySQL queries. To turn the profile on for the current session, run the following command, setting it to 1, which is on:


      Query OK, 0 rows affected, 1 warning (0.00 sec)

      Then, run the following query to retrieve all customers:

      You'll receive the following output:


      +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | JANE DOE | | 2 | JANIE DOE | | 3 | JOHN ROE | | 4 | MARY ROE | | 5 | RICHARD ROE | | 6 | JOHNNY DOE | | 7 | JOHN SMITH | | 8 | JOE BLOGGS | | 9 | JANE POE | | 10 | MARK MOE | +-------------+---------------+ 10 rows in set (0.00 sec)

      Then, run the SHOW PROFILES command to retrieve performance information about the SELECT query you just ran:

      You will get output similar to the following:


      +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00044075 | Select * from customers | +----------+------------+-------------------------+ 1 row in set, 1 warning (0.00 sec)

      The output shows the total time spent by MySQL when retrieving records from the database. You are going to compare this data in the next steps when query cache is enabled, so keep note of your Duration. You can ignore the warning within the output since this simply indicates that SHOW PROFILES command will be removed in a future MySQL release and replaced with Performance Schema.

      Next, exit from the MySQL Command Line Interface.

      You have ran a query with MySQL before enabling query cache and noted down the Duration or time spent to retrieve records. Next, you will enable query cache and see if there is a performance boost when running the same query.

      Step 4 — Setting Up Query Cache

      In the previous step, you created sample data and ran a SELECT statement before you enabled query cache. In this step, you'll enable query cache by editing the MySQL configuration file.

      Use nano to edit the file:

      • sudo nano /etc/mysql/my.cnf

      Add the following information to the end of your file:


      query_cache_size = 10M

      Here you've enabled query cache by setting the query_cache_type to 1. You've also set up the individual query limit size to 256K and instructed MySQL to allocate 10 megabytes to query cache by setting the value of query_cache_size to 10M.

      Save and close the file by pressing CTRL + X, Y, then ENTER. Then, restart your MySQL server to implement the changes:

      • sudo systemctl restart mysql

      You have now enabled query cache.

      Once you have configured query cache and restarted MySQL to apply the changes, you will go ahead and test the performance of MySQL with the feature enabled.

      Step 5 — Testing Your MySQL Server with Query Cache Enabled

      In this step, you'll run the same query you ran in Step 3 one more time to check how query cache has optimized the performance of your MySQL server.

      First, connect to your MySQL server as the root user:

      Enter your root password for the database server and hit ENTER to continue.

      Now confirm your configuration set in the previous step to ensure you enabled query cache:

      • show variables like 'query_cache_%' ;

      You'll see the following output:


      +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 262144 | | query_cache_min_res_unit | 4096 | | query_cache_size | 10485760 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.01 sec)

      The variable query_cache_type is set to ON; this confirms that you enabled query cache with the parameters defined in the previous step.

      Switch to the sample_db database that you created earlier.

      Start the MySQL profiler:

      Then, run the query to retrieve all customers at least two times in order to generate enough profiling information.

      Remember, once you've run the first query, MySQL will create a cache of the results and therefore, you must run the query twice to trigger the cache:

      • Select * from customers;
      • Select * from customers;

      Then, list the profiles information:

      You'll receive an output similar to the following:


      +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00049250 | Select * from customers | | 2 | 0.00026000 | Select * from customers | +----------+------------+-------------------------+ 2 rows in set, 1 warning (0.00 sec)

      As you can see the time taken to run the query has drastically reduced from 0.00044075 (without query cache in Step 3) to 0.00026000 (the second query) in this step.

      You can see the optimization from enabling the query cache feature by profiling the first query in detail:



      +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000025 | | Waiting for query cache lock | 0.000004 | | starting | 0.000003 | | checking query cache for query | 0.000045 | | checking permissions | 0.000008 | | Opening tables | 0.000014 | | init | 0.000018 | | System lock | 0.000008 | | Waiting for query cache lock | 0.000002 | | System lock | 0.000018 | | optimizing | 0.000003 | | statistics | 0.000013 | | preparing | 0.000010 | | executing | 0.000003 | | Sending data | 0.000048 | | end | 0.000004 | | query end | 0.000006 | | closing tables | 0.000006 | | freeing items | 0.000006 | | Waiting for query cache lock | 0.000003 | | freeing items | 0.000213 | | Waiting for query cache lock | 0.000019 | | freeing items | 0.000002 | | storing result in query cache | 0.000003 | | cleaning up | 0.000012 | +--------------------------------+----------+ 25 rows in set, 1 warning (0.00 sec)

      Run the following command to show profile information for the second query, which is cached:



      +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000024 | | Waiting for query cache lock | 0.000003 | | starting | 0.000002 | | checking query cache for query | 0.000006 | | checking privileges on cached | 0.000003 | | checking permissions | 0.000027 | | sending cached result to clien | 0.000187 | | cleaning up | 0.000008 | +--------------------------------+----------+ 8 rows in set, 1 warning (0.00 sec)

      The outputs from the profiler show that MySQL took less time on the second query because it was able to retrieve data from the query cache instead of reading it from the disk. You can compare the two sets of output for each of the queries. If you look at the profile information on QUERY 2, the status of sending cached result to client shows that data was read from the cache and no tables were opened since the Opening tables status is missing.

      With the MySQL query cache feature enabled on your server, you'll now experience improved read speeds.


      You have set up query cache to speed up your MySQL server on Ubuntu 18.04. Using features like MySQL's query cache can enhance the speed of your website or web application. Caching reduces unnecessary execution for SQL statements and is a highly recommended and popular method for optimizing your database. For more on speeding up your MySQL server, try the How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 tutorial.

      Source link

      How To Optimize Docker Images for Production

      The author selected to receive a donation as part of the Write for DOnations program.


      In a production environment, Docker makes it easy to create, deploy, and run applications inside of containers. Containers let developers gather applications and all their core necessities and dependencies into a single package that you can turn into a Docker image and replicate. Docker images are built from Dockerfiles. The Dockerfile is a file where you define what the image will look like, what base operating system it will have, and which commands will run inside of it.

      Large Docker images can lengthen the time it takes to build and send images between clusters and cloud providers. If, for example, you have a gigabyte-sized image to push every time one of your developers triggers a build, the throughput you create on your network will add up during the CI/CD process, making your application sluggish and ultimately costing you resources. Because of this, Docker images suited for production should only have the bare necessities installed.

      There are several ways to decrease the size of Docker images to optimize for production. First off, these images don’t usually need build tools to run their applications, and so there’s no need to add them at all. By using a multi-stage build process, you can use intermediate images to compile and build the code, install dependencies, and package everything into the smallest size possible, then copy over the final version of your application to an empty image without build tools. Additionally, you can use an image with a tiny base, like Alpine Linux. Alpine is a suitable Linux distribution for production because it only has the bare necessities that your application needs to run.

      In this tutorial, you’ll optimize Docker images in a few simple steps, making them smaller, faster, and better suited for production. You’ll build images for a sample Go API in several different Docker containers, starting with Ubuntu and language-specific images, then moving on to the Alpine distribution. You will also use multi-stage builds to optimize your images for production. The end goal of this tutorial is to show the size difference between using default Ubuntu images and optimized counterparts, and to show the advantage of multi-stage builds. After reading through this tutorial, you’ll be able to apply these techniques to your own projects and CI/CD pipelines.

      Note: This tutorial uses an API written in Go as an example. This simple API will give you a clear understanding of how you would approach optimizing Go microservices with Docker images. Even though this tutorial uses a Go API, you can apply this process to almost any programming language.


      Before you start you will need:

      Step 1 — Downloading the Sample Go API

      Before optimizing your Docker image, you must first download the sample API that you will build your Docker images from. Using a simple Go API will showcase all the key steps of building and running an application inside a Docker container. This tutorial uses Go because it’s a compiled language like C++ or Java, but unlike them, has a very small footprint.

      On your server, begin by cloning the sample Go API:

      • git clone

      Once you have cloned the project, you will have a directory named mux-go-api on your server. Move into this directory with cd:

      This will be the home directory for your project. You will build your Docker images from this directory. Inside, you will find the source code for an API written in Go in the api.go file. Although this API is minimal and has only a few endpoints, it will be appropriate for simulating a production-ready API for the purposes of this tutorial.

      Now that you have downloaded the sample Go API, you are ready to build a base Ubuntu Docker image, against which you can compare the later, optimized Docker images.

      Step 2 — Building a Base Ubuntu Image

      For your first Docker image, it will be useful to see what it looks like when you start out with a base Ubuntu image. This will package your sample API in an environment similar to the software you're already running on your Ubuntu server. Inside the image, you will install the various packages and modules you need to run your application. You will find, however, that this process creates a rather heavy Ubuntu image that will affect build time and the code readability of your Dockerfile.

      Start by writing a Dockerfile that instructs Docker to create an Ubuntu image, install Go, and run the sample API. Make sure to create the Dockerfile in the directory of the cloned repo. If you cloned to the home directory it should be $HOME/mux-go-api.

      Make a new file called Dockerfile.ubuntu. Open it up in nano or your favorite text editor:

      • nano ~/mux-go-api/Dockerfile.ubuntu

      In this Dockerfile, you'll define an Ubuntu image and install Golang. Then you'll proceed to install the needed dependencies and build the binary. Add the following contents to Dockerfile.ubuntu:


      FROM ubuntu:18.04
      RUN apt-get update -y 
        && apt-get install -y git gcc make golang-1.10
      ENV GOROOT /usr/lib/go-1.10
      ENV GOPATH /root/go
      ENV APIPATH /root/go/src/api
      COPY . .
        go get -d -v 
        && go install -v 
        && go build
      EXPOSE 3000
      CMD ["./api"]

      Starting from the top, the FROM command specifies which base operating system the image will have. Then the RUN command installs the Go language during the creation of the image. ENV sets the specific environment variables the Go compiler needs in order to work properly. WORKDIR specifies the directory where we want to copy over the code, and the COPY command takes the code from the directory where Dockerfile.ubuntu is and copies it over into the image. The final RUN command installs Go dependencies needed for the source code to compile and run the API.

      Note: Using the && operators to string together RUN commands is important in optimizing Dockerfiles, because every RUN command will create a new layer, and every new layer increases the size of the final image.

      Save and exit the file. Now you can run the build command to create a Docker image from the Dockerfile you just made:

      • docker build -f Dockerfile.ubuntu -t ubuntu .

      The build command builds an image from a Dockerfile. The -f flag specifies that you want to build from the Dockerfile.ubuntu file, while -t stands for tag, meaning you're tagging it with the name ubuntu. The final dot represents the current context where Dockerfile.ubuntu is located.

      This will take a while, so feel free to take a break. Once the build is done, you'll have an Ubuntu image ready to run your API. But the final size of the image might not be ideal; anything above a few hundred MB for this API would be considered an overly large image.

      Run the following command to list all Docker images and find the size of your Ubuntu image:

      You'll see output showing the image you just created:


      REPOSITORY TAG IMAGE ID CREATED SIZE ubuntu latest 61b2096f6871 33 seconds ago 636MB . . .

      As is highlighted in the output, this image has a size of 636MB for a basic Golang API, a number that may vary slightly from machine to machine. Over multiple builds, this large size will significantly affect deployment times and network throughput.

      In this section, you built an Ubuntu image with all the needed Go tools and dependencies to run the API you cloned in Step 1. In the next section, you'll use a pre-built, language-specific Docker image to simplify your Dockerfile and streamline the build process.

      Step 3 — Building a Language-Specific Base Image

      Pre-built images are ordinary base images that users have modified to include situation-specific tools. Users can then push these images to the Docker Hub image repository, allowing other users to use the shared image instead of having to write their own individual Dockerfiles. This is a common process in production situations, and you can find various pre-built images on Docker Hub for almost any use case. In this step, you'll build your sample API using a Go-specific image that already has the compiler and dependencies installed.

      With pre-built base images already containing the tools you need to build and run your app, you can cut down the build time significantly. Because you're starting with a base that has all needed tools pre-installed, you can skip adding these to your Dockerfile, making it look a lot cleaner and ultimately decreasing the build time.

      Go ahead and create another Dockerfile and name it Dockerfile.golang. Open it up in your text editor:

      • nano ~/mux-go-api/Dockerfile.golang

      This file will be significantly more concise than the previous one because it has all the Go-specific dependencies, tools, and compiler pre-installed.

      Now, add the following lines:


      FROM golang:1.10
      WORKDIR /go/src/api
      COPY . .
          go get -d -v 
          && go install -v 
          && go build
      EXPOSE 3000
      CMD ["./api"]

      Starting from the top, you'll find that the FROM statement is now golang:1.10. This means Docker will fetch a pre-built Go image from Docker Hub that has all the needed Go tools already installed.

      Now, once again, build the Docker image with:

      • docker build -f Dockerfile.golang -t golang .

      Check the final size of the image with the following command:

      This will yield output similar to the following:


      REPOSITORY TAG IMAGE ID CREATED SIZE golang latest eaee5f524da2 40 seconds ago 744MB . . .

      Even though the Dockerfile itself is more efficient and the build time is shorter, the total image size actually increased. The pre-built Golang image is around 744MB, a significant amount.

      This is the preferred way to build Docker images. It gives you a base image which the community has approved as the standard to use for the specified language, in this case Go. However, to make an image ready for production, you need to cut away parts that the running application does not need.

      Keep in mind that using these heavy images is fine when you are unsure about your needs. Feel free to use them both as throwaway containers as well as the base for building other images. For development or testing purposes, where you don't need to think about sending images through the network, it's perfectly fine to use heavy images. But if you want to optimize deployments, then you need to try your best to make your images as tiny as possible.

      Now that you have tested a language-specific image, you can move on to the next step, in which you will use the lightweight Alpine Linux distribution as a base image to make your Docker image lighter.

      Step 4 — Building Base Alpine Images

      One of the easiest steps to optimize your Docker images is to use smaller base images. Alpine is a lightweight Linux distribution designed for security and resource efficiency. The Alpine Docker image uses musl libc and BusyBox to stay compact, requiring no more than 8MB in a container to run. The tiny size is due to binary packages being thinned out and split, giving you more control over what you install, which keeps the environment as small and efficient as possible.

      The process of creating an Alpine image is similar to how you created the Ubuntu image in Step 2. First, create a new file called Dockerfile.alpine:

      • nano ~/mux-go-api/Dockerfile.alpine

      Now add this snippet:


      FROM alpine:3.8
      RUN apk add --no-cache 
      ENV GOPATH /go
      ENV PATH $GOPATH/bin:/usr/local/go/bin:$PATH
      ENV APIPATH $GOPATH/src/api
      RUN mkdir -p "$GOPATH/src" "$GOPATH/bin" "$APIPATH" && chmod -R 777 "$GOPATH"
      COPY . .
          go get -d -v 
          && go install -v 
          && go build
      EXPOSE 3000
      CMD ["./api"]

      Here you're adding the apk add command to use Alpine's package manager to install Go and all libraries it requires. As with the Ubuntu image, you need to set the environment variables as well.

      Go ahead and build the image:

      • docker build -f Dockerfile.alpine -t alpine .

      Once again, check the image size:

      You will receive output similar to the following:


      REPOSITORY TAG IMAGE ID CREATED SIZE alpine latest ee35a601158d 30 seconds ago 426MB . . .

      The size has gone down to around 426MB.

      The small size of the Alpine base image has reduced the final image size, but there are a few more things you can do to make it even smaller.

      Next, try using a pre-built Alpine image for Go. This will make the Dockerfile shorter, and will also cut down the size of the final image. Because the pre-built Alpine image for Go is built with Go compiled from source, its footprint is significantly smaller.

      Start by creating a new file called Dockerfile.golang-alpine:

      • nano ~/mux-go-api/Dockerfile.golang-alpine

      Add the following contents to the file:


      FROM golang:1.10-alpine3.8
      RUN apk add --no-cache --update git
      WORKDIR /go/src/api
      COPY . .
      RUN go get -d -v 
        && go install -v 
        && go build
      EXPOSE 3000
      CMD ["./api"]

      The only differences between Dockerfile.golang-alpine and Dockerfile.alpine are the FROM command and the first RUN command. Now, the FROM command specifies a golang image with the 1.10-alpine3.8 tag, and RUN only has a command for installing Git. You need Git for the go get command to work in the second RUN command at the bottom of Dockerfile.golang-alpine.

      Build the image with the following command:

      • docker build -f Dockerfile.golang-alpine -t golang-alpine .

      Retrieve your list of images:

      You will receive the following output:


      REPOSITORY TAG IMAGE ID CREATED SIZE golang-alpine latest 97103a8b912b 49 seconds ago 288MB

      Now the image size is down to around 288MB.

      Even though you've managed to cut down the size a lot, there's one last thing you can do to get the image ready for production. It's called a multi-stage build. By using multi-stage builds, you can use one image to build the application while using another, lighter image to package the compiled application for production, a process you will run through in the next step.

      Ideally, images that you run in production shouldn't have any build tools installed or dependencies that are redundant for the production application to run. You can remove these from the final Docker image by using multi-stage builds. This works by building the binary, or in other terms, the compiled Go application, in an intermediate container, then copying it over to an empty container that doesn't have any unnecessary dependencies.

      Start by creating another file called Dockerfile.multistage:

      • nano ~/mux-go-api/Dockerfile.multistage

      What you'll add here will be familiar. Start out by adding the exact same code as with Dockerfile.golang-alpine. But this time, also add a second image where you'll copy the binary from the first image.


      FROM golang:1.10-alpine3.8 AS multistage
      RUN apk add --no-cache --update git
      WORKDIR /go/src/api
      COPY . .
      RUN go get -d -v 
        && go install -v 
        && go build
      FROM alpine:3.8
      COPY --from=multistage /go/bin/api /go/bin/
      EXPOSE 3000
      CMD ["/go/bin/api"]

      Save and close the file. Here you have two FROM commands. The first is identical to Dockerfile.golang-alpine, except for having an additional AS multistage in the FROM command. This will give it a name of multistage, which you will then reference in the bottom part of the Dockerfile.multistage file. In the second FROM command, you'll take a base alpine image and COPY over the compiled Go application from the multistage image into it. This process will further cut down the size of the final image, making it ready for production.

      Run the build with the following command:

      • docker build -f Dockerfile.multistage -t prod .

      Check the image size now, after using a multi-stage build.

      You will find two new images instead of only one:


      REPOSITORY TAG IMAGE ID CREATED SIZE prod latest 82fc005abc40 38 seconds ago 11.3MB <none> <none> d7855c8f8280 38 seconds ago 294MB . . .

      The <none> image is the multistage image built with the FROM golang:1.10-alpine3.8 AS multistage command. It's only an intermediary used to build and compile the Go application, while the prod image in this context is the final image which only contains the compiled Go application.

      From an initial 744MB, you've now shaved down the image size to around 11.3MB. Keeping track of a tiny image like this and sending it over the network to your production servers will be much easier than with an image of over 700MB, and will save you significant resources in the long run.


      In this tutorial, you optimized Docker images for production using different base Docker images and an intermediate image to compile and build the code. This way, you have packaged your sample API into the smallest size possible. You can use these techniques to improve build and deployment speed of your Docker applications and any CI/CD pipeline you may have.

      If you are interested in learning more about building applications with Docker, check out our How To Build a Node.js Application with Docker tutorial. For more conceptual information on optimizing containers, see Building Optimized Containers for Kubernetes.

      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