Guides - Deploy MySQL/MariaDB through the Linode Marketplace

MySQL is an open-source database management system that uses a relational database and SQL (Structured Query Language) to manage its data. In Debian 9, MySQL is replaced with MariaDB as the default database system. MariaDB is an open-source, multi-threaded relational database management system, backward compatible replacement for MySQL. It is maintained and developed by the MariaDB Foundation.

Deploying a Marketplace App

The Linode Marketplace allows you to easily deploy software on a Compute Instance using the Cloud Manager. See Get Started with Marketplace Apps for complete steps.

1. Log in to the Cloud Manager and select the Marketplace link from the left navigation menu. This displays the Linode Create page with the Marketplace tab pre-selected.

2. Under the Select App section, select the app you would like to deploy.

3. Complete the form by following the steps and advice within the Creating a Compute Instance guide. Depending on the Marketplace App you selected, there may be additional configuration options available. See the Configuration Options section below for compatible distributions, recommended plans, and any additional configuration options available for this Marketplace App.

4. Click the Create Linode button. Once the Compute Instance has been provisioned and has fully powered on, wait for the software installation to complete. If the instance is powered off or restarted before this time, the software installation will likely fail.

To verify that the app has been fully installed, see Get Started with Marketplace Apps > Verify Installation. Once installed, follow the instructions within the Getting Started After Deployment section to access the application and start using it.

Configuration Options

• Supported distributions: Ubuntu 20.04 LTS
• Recommended plan: Depends on the size of your MySQL database and the amount of traffic you expect.

MySQL/MariaDB Options

• MySQL or MariaDB (required): Select which database service you’d like to use.
• MySQL Root Password (required): The root password for your MySQL database.
• MySQL User (required): The user for your MySQLDB database.
• MySQL User Password (required): The user password for your MySQL database.
• Create Database (required): The database on your MySQL.

Limited User (Optional)

You can optionally fill out the following fields to automatically create a limited user for your new Compute Instance. This is recommended for most deployments as an additional security measure. This account will be assigned to the sudo group, which provides elevated permission when running commands with the sudo prefix.

• Limited sudo user: Enter your preferred username for the limited user.
• Password for the limited user: Enter a strong password for the new user.
• SSH public key for the limited user: If you wish to login as the limited user through public key authentication (without entering a password), enter your public key here. See Creating an SSH Key Pair and Configuring Public Key Authentication on a Server for instructions on generating a key pair.
• Disable root access over SSH: To block the root user from logging in over SSH, select Yes (recommended). You can still switch to the root user once logged in and you can also log in as root through Lish.

Custom Domain (Optional)

If you wish to automatically configure a custom domain, you first need to configure your domain to use Linode’s name servers. This is typically accomplished directly through your registrar. See Use Linode’s Name Servers with Your Domain. Once that is finished, you can fill out the following fields for the Marketplace App:

• Linode API Token: If you wish to use the Linode’s DNS Manager to manage DNS records for your custom domain, create a Linode API Personal Access Token on your account with Read/Write access to Domains. If this is provided along with the subdomain and domain fields (outlined below), the installation attempts to create DNS records via the Linode API. See Get an API Access Token. If you do not provide this field, you need to manually configure your DNS records through your DNS provider and point them to the IP address of the new instance.
• Subdomain: The subdomain you wish to use, such as www for www.example.com.
• Domain: The domain name you wish to use, such as example.com.

Getting Started after Deployment

Access MySQL/MariaDB

After MySQL has finished installing, you will be able to access MySQL from the console via ssh with your Linode’s IPv4 address:

1. SSH into your Linode and create a limited user account.

2. Log out and log back in as your limited user account.

3. Update your server:

   sudo apt-get update && apt-get upgrade
   

Using MySQL/MariaDB

The standard tool for interacting with MySQL is the mysql client which installs with the mysql-server package. The MySQL client is used through a terminal.

Root Login

1. To log in to MySQL as the root user:

   sudo mysql -u root -p
   

2. When prompted, enter the MySQL root password that you set when launching the Marketplace App. You’ll then be presented with a welcome header and the MySQL prompt as shown below:

   MariaDB [(none)]>
   

3. To generate a list of commands for the MySQL prompt, enter \h. You’ll then see:

   List of all MySQL commands:
   Note that all text commands must be first on line and end with ';'
   ?         (\?) Synonym for `help'.
   clear     (\c) Clear command.
   connect   (\r) Reconnect to the server. Optional arguments are db and host.
   delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
   edit      (\e) Edit command with $EDITOR.
   ego       (\G) Send command to mysql server, display result vertically.
   exit      (\q) Exit mysql. Same as quit.
   go        (\g) Send command to mysql server.
   help      (\h) Display this help.
   nopager   (\n) Disable pager, print to stdout.
   notee     (\t) Don't write into outfile.
   pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
   print     (\p) Print current command.
   prompt    (\R) Change your mysql prompt.
   quit      (\q) Quit mysql.
   rehash    (\#) Rebuild completion hash.
   source    (\.) Execute an SQL script file. Takes a file name as an argument.
   status    (\s) Get status information from the server.
   system    (\!) Execute a system shell command.
   tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
   use       (\u) Use another database. Takes database name as argument.
   charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
   warnings  (\W) Show warnings after every statement.
   nowarning (\w) Don't show warnings after every statement.
   
   For server side help, type 'help contents'
   
   MariaDB [(none)]>
   

4. Grant access to the database that you created when launching the Marketplace App for MySQL User. In this example, the database is called webdata, the user webuser, and password of the user is password. Be sure to enter your own password. This should be different from the root password for MySQL:

   GRANT ALL ON webdata.* TO 'webuser' IDENTIFIED BY 'password';
   

5. To Exit MySQL/MariaDB type:

   exit
   

Create a Sample Table

1. Log back in as MySQL User that you set when launching the Marketplace App. In the following example the MySQL User is webuser.

   sudo mysql -u webuser -p
   

2. Create a sample table called customers. This creates a table with a customer ID field of the type INT for integer (auto-incremented for new records, used as the primary key), as well as two fields for storing the customer’s name. In the following example webdata is the database that you created when launching the Marketplace App.

   use webdata;
   create table customers (customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT);
   

3. To view the contents of the table that you created:

   describe customers;
   

   The output would be:

   +-------------+---------+------+-----+---------+----------------+
   | Field       | Type    | Null | Key | Default | Extra          |
   +-------------+---------+------+-----+---------+----------------+
   | customer_id | int(11) | NO   | PRI | NULL    | auto_increment |
   | first_name  | text    | YES  |     | NULL    |                |
   | last_name   | text    | YES  |     | NULL    |                |
   +-------------+---------+------+-----+---------+----------------+
   

4. Then exit MySQL/MariaDB.

    exit
   

Next Steps

For more on MySQL/MariaDB, checkout the following guides:

• MariaDB Clusters with Galera

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

• MySQL 5.6 Reference Manual
• PHP MySQL Manual
• MySQLdb User’s Guide