How to create a managed database in Lightsail and connect your website to it

Almost every website keeps its data in a database (MySQL nearly always, PostgreSQL sometimes) that lives inside the same machine. It works, but when the machine goes down, everything goes down; when the disk fills up, it fills up for both; and backups of the database depend on someone making them. A managed database is a database that Amazon installs, updates, backs up every day and monitors for you, on a separate machine you never see. Lightsail offers it at a fixed monthly price, just like its machines.

This guide creates one, connects the website to it and explains how to move the data. If your website is small and a single machine is more than enough, it is not compulsory; if the website matters, it is one of the best decisions you can make.

Step by step

1. Create the database

   In the Lightsail console, Databases tab, Create database. Choose the same region as your machine, the engine (MySQL for WordPress and most others; PostgreSQL if your application asks for it) and the recommended version. Let Lightsail generate the master password or set a long one of your own. Choose the smallest plan to start with, unless you know you need more. Name: bd-web. Create. It takes a few minutes to become available.

2. Write down the connection details

   Open the database, Connect tab. You will see the endpoint (a long address ending in rds.amazonaws.com), the port (3306 for MySQL, 5432 for PostgreSQL), the master user and a button to show the password. Leave public mode off: your Lightsail machine can reach it without it because they are in the same region.

3. Check the connection from the machine

   Open your machine's browser terminal and try (MySQL):

   mysql -h TU-PUNTO-DE-ENLACE -u USUARIO -p

   Type the password when prompted. If you get in and see the mysql> prompt, the connection works. Type exit. If the mysql command does not exist, install the client (sudo apt install mysql-client on Ubuntu or Debian).

4. Copy the data from the current database to the new one

   If the website already has data in the local database, put the website into maintenance mode for a moment and dump them:

   mysqldump -u root -p nombre_bd > copia.sql
   mysql -h TU-PUNTO-DE-ENLACE -u USUARIO -p -e "CREATE DATABASE nombre_bd"
   mysql -h TU-PUNTO-DE-ENLACE -u USUARIO -p nombre_bd < copia.sql

   On a Bitnami machine (Lightsail WordPress) the local user is root and its password is in the bitnami_application_password file or in the application's configuration. The dump takes longer or shorter depending on size; with a normal website, seconds.

5. Change the website's configuration

   Edit your application's configuration file and change the database server from localhost to the endpoint, and the user and password to those of the managed database. In WordPress it is wp-config.php: the DB_HOST, DB_USER and DB_PASSWORD lines. Save and open the website: it should work just as before. Turn off maintenance mode.

6. Stop the local database and review the backups

   Once it has been running on the new one for a few days, stop the machine's database service (sudo systemctl disable --now mysql or the Bitnami command) to free up memory. In the managed database, Snapshots tab: Lightsail makes automatic copies daily; you can also create a manual one before important changes. And look at the Metrics tab from time to time: if CPU or connections are at their limit, it is time to move up a plan, which for databases really is done with a button.

What can go wrong