Overview
In this tutorial, you will deploy a database on Amazon Lightsail, which can be used by any Lightsail instances or applications deployed in the same AWS Region. This allows you to build applications and systems within Lightsail without needing to use additional AWS services. The tutorial will also help you add databases to a single instance, add users, create snapshots of your data, and understand how to view database metrics.
What you will accomplish
In this tutorial, you will:
- deploy a Lightsail MySQL database instance
- deploy a Lightsail Ubuntu instance
- connect the Ubuntu instance to the database instance
- create additional users and databases on the database instance
- enable logging and snapshots of the database instance
Prerequisites
Before starting this tutorial, you will need:
- An AWS account: If you don't already have an account, follow the Setting Up Your AWS Environment guide for a quick overview.
AWS experience
Minimum time to complete
20 minutes
Cost to complete
Free Tier eligible (see Amazon Lightsail Pricing page for more details).
Requires
An AWS account
Services used
Last updated
March 14, 2023
Implementation
In this tutorial, you will deploy and configure a Lightsail MySQL database and learn how to manage the database.
1. To begin: Navigate to the Amazon Lightsail console.
2. Select the Databases tab.
3. Choose Create database.
4. Choose Change AWS Region and Availability Zone and select the Region and Availability Zone where you want your database to reside.
5. Select your database. Currently Lightsail supports MySQL and PostgreSQL. For this tutorial, select MySQL and use the latest version.
6. Choose Specify login credentials. You can create a database user name such as dbmasteruser or you can leave it blank and you will see default credentials after the database is deployed.
7. By default, Lightsail will create a strong password for your database instance, which will be shown in the console after the database is deployed. Clear the Create a strong password for me check box and enter Sunny2DAY! or a password of your choice.
8. Give your database a name. When deploying a Lightsail database, you are actually deploying both a database instance (a server running MySQL) and a new database on the instance. This step is naming the database running on the instance.
Once this is deployed, you can log in to the database instance and create additional databases on the single instance without having to deploy additional compute. You will see this in Step 30. Additionally, the name you give your database here will be pre-pended with the letters db. Enter main, which will be created as dbmain.
9. With Lightsail databases, you can choose between a Standard plan and a High Availability plan. The High Availability plan provisions two database servers and will keep your database running even if something happens to the primary database. High Availability plans are twice the cost because the number of resources are double those of the Standard plan.
Select the $15 USD Standard plan.
10. Enter DB-Instance-1. This is the name of the database instance (server running MySQL), which you will see in the Lightsail console under your database resources.
11. Choose Create database. It will take 10–15 minutes to deploy the database server. Until it is ready, the resource will be grayed out with a status of Creating.
12. While you wait for your database to be created, we will deploy a Lightsail Ubuntu instance that we can use to remote into our database instance and perform some administrative tasks. Choose the Lightsail icon or the Home button at the top of the Lightsail console, which will take you to the Instances page.
13. Choose Create instance.
14. Ensure that your instance is deploying to the same Region as your database. If it is showing a different Region, expand the Region selection and select the corresponding Region. Otherwise, choose OS Only under Select a blueprint and select the latest Ubuntu blueprint.
15. Choose Add launch script. This will run code at the end of deploying this instance. You will use the launch script to add the MySQL client, allowing you to remotely connect to your database instance and perform administrative tasks.
Paste the following code into the launch script textbox:
# Update the package index
sudo apt update
# Install mysql client
sudo apt-get install mysql-client-core-8.0 -y16. For this example, select the $3.50 USD per month instance plan. For larger applications or websites, you can select the instance plan according to your requirements.
17. To identify your instance, enter Ubuntu-1, then choose Create instance.
18. Wait until the database instance is available. You can watch for the status change by navigating back to the Databases tab by choosing the Lightsail icon or the Home button in the Lightsail console:
20. Your page will look similar to this screenshot. You will see the resources, MySQL version, and Region, followed by the Endpoint and Port. The endpoint and port number are what you will use to connect an application to the database.
Below that, you will see a number of tabs that we will walk through in the upcoming steps. Below the tabs, you will see the username and password to connect to the database instance. You can add additional users as well, which is not covered in this tutorial.
21. Select the Logs & history tab, which provides additional tools for troubleshooting your database instance. By default, these logs are disabled but can be enabled by selecting the toggle buttons. Enable all logs on this page by selecting the toggle buttons.
22. Once these logs are enabled, you can choose the Open log buttons to see what is being logged and use this for troubleshooting as needed.
23. Next, select the Metrics tab. Here you can select various metrics and see a graphical representation of resource usage. This is helpful when troubleshooting or determining if a larger instance is needed.
24. Next, select the Networking tab. You have the ability to enable Public mode. However, this opens your database up to be connected to from anywhere in the world and is highly discouraged, other than for temporary troubleshooting purposes. If you select the Public mode toggle, it will take 10–15 minutes to reconfigure the instance to be enabled. Similarly, if you clear the Public mode selection, it will take a number of minutes to modify the instance to the latest setting.
25. Select Snapshots & restore. Here you can create a manual snapshot (point-in-time copy) of your database. Additionally, Lightsail creates backups of your database in 5-minute increments. If you need to restore to an earlier snapshot, you could choose Restore to new database and deploy it as a new database with a different instance name that you could then re-point your applications to. Choose Create snapshot.
26. Give your snapshot a name. In this case, enter DB-Instance-1-Fresh-Deployment and choose Create. Your snapshot will be created within a few minutes.
27. Once the snapshot has been created, select the 3-dot button to the right of the snapshot. This provides the option of creating a new database from this snapshot.
28. Copy the database endpoint to your clipboard, then navigate back to your Ubuntu-1 instance. Choose the remote terminal icon.
A new SSH window will pop up and you can now connect from this instance into your database instance. Enter the following command and replace DATABASE_URL with the database endpoint you copied to your clipboard in the previous step. (Note: Make sure there is no space between the -p and the password.)
mysql -h DATABASE_URL -u dbmasteruser -pSunny2DAY!
29. You will now see that you are connected to the MySQL instance. Run the command:
SHOW DATABASES;You will see dbmain, which you created during database deployment, along with several others used by the Lightsail database service.
30. Create another database on this instance using the following command:
CREATE DATABASE secondDB;
31. Give your user dbmasteruser privileges on the new database by running the following command:
GRANT ALL PRIVILEGES ON secondDB.* TO 'dbmasteruser'@'%';The above code grants your user all privileges on the secondDB database.
32. Finally, we can exit the remote SQL session by entering:
exit;Conclusion
Congratulations on deploying and configuring your Lightsail database instance. You should now feel comfortable using Lightsail databases to power any applications you build or deploy on Lightsail.
In this tutorial, you deployed your database instance, added an additional database and user, connected remotely from a Lightsail Ubuntu instance, and configured additional database settings and snapshots.
Next steps
You can continue your journey with Lightsail by following the next steps section below.