How to install and use MySQL/MariaDB
MySQL/MariaDB are used to create databases. In Linux, installation takes just a few steps in the terminal.
- Simple registration
- Premium TLDs at great prices
- 24/7 personal consultant included
- Free privacy protection for eligible domains
Requirements
- A Cloud Server running Linux (CentOS 7 or Ubuntu 16.04)
For any Cloud Server with Plesk, databases should always be installed and managed through the Plesk interface.
MySQL vs. MariaDB
MySQL was first developed in 1995 and acquired by Sun Microsystems in 2008, and then again by Oracle in 2010. MariaDB was developed as a fork of the MySQL project in 2009, due to concerns about Oracle’s proprietary requirements. Although MySQL’s source code is publicly available under the terms of the GNU General Public License, MariaDB is a fully open source project.
MariaDB was developed as a “drop-in” replacement for MySQL. As such, both software packages are functionally equivalent and interchangeable.
MySQL is the default on Ubuntu systems, while MariaDB is the default on CentOS systems. Therefore, this guide will cover installing and updating MySQL on Ubuntu 16.04 and MariaDB on CentOS 7.
Install MySQL on Ubuntu 16.04
MySQL is usually installed by default on a standard Cloud Server running Ubuntu 16.04. Use the sudo mysql --version command to verify that MySQL is already installed:
If MySQL is not installed, you can install it by first updating your packages:
Then install MySQL:
Follow the prompts to install MySQL.
MySQL should start itself automatically when installed. If it does not start, you can start it with the following command:
Enable MySQL to start at boot with the command:
If you need to stop or restart MySQL, use the commands:
- Unlimited traffic
- Fast SSD NVMe storage
- Free Plesk Web Host Edition
Install MariaDB on CentOS 7
In contrast to MySQL, MariaDB is usually installed by default on a standard Cloud Server running CentOS 7. Use the sudo mysql –version command to verify that MariaDB is installed:
If MariaDB is not installed, you can install the database management system by first updating your system:
Then install MariaDB:
MariaDB should start itself automatically when installed. If it does not start, you can start it with the command:
Enable MariaDB to start at boot with the command:
If you need to stop or restart MariaDB, use the following commands:
Log in to the MySQL/MariaDB client
From the command line, enter the following command to register with the MySQL/MariaDB client:
For a default MySQL/MariaDB installation, use the root password which was set when the server was created. If you installed MySQL/MariaDB, enter the password which you set for the root user during the installation process.
After entering the password, you will be taken to the MySQL/MariaDB client prompt.
Create, select, and drop a database
Create a Database
Use CREATE DATABASE[database name]; to create a database. For example, to create a database named testdb the command is:
List and Select a Database
Use SHOW DATABASES; to list all available databases:
Use USE[database name]; to connect to a database and select it for use:
Drop a Database
If you want to delete a database, use DROP DATABASE[database name]. For example, to delete the testdb database, enter the following command in the terminal:
To exit the client, type:
Hit Enter to confirm exiting the client.
- Automatic backup & easy recovery
- Intuitive scheduling and management
- AI-based threat protection
Create and drop a table
Use CREATE TABLE[table name] (column definitions); to create a table. A full list of CREATE TABLE parameters can be found in the CREATE TABLE chapter of the official MySQL reference manual.
For example, to create a table testtable with two basic columns, the command is:
Use SHOW TABLES; to verify that your table was created:
Drop a Table
Use DROP TABLE[table name]; to delete a table. For example, to delete the testtable table, the command is:
Use SHOW TABLES; to verify that your table was deleted:
Working with records: insert, select, and update data
Insert Data into a Table
Use INSERT INTO[table name] VALUES (data, data...); to insert data into a table. A full list of INSERT parameters can be found in the "INSERT Syntax" chapter of the official MySQL Reference Manual.
For example, to insert a record into the table testable the command is:
It is important to list the values in the same order as the columns of the table. In our example, the table’s first column is id and the second column is name. Therefore, we need to insert the ID as the first value, and the name as the second.
Select Table Data
Use SELECT to select data from a table and generate different queries for a database. A full list of SELECT parameters can be found in the "SELECT Syntax" chapter of the official MySQL Reference Manual.
For example, to list all of the contents of our testtable the command is:
This will return all of the table contents.
You can also filter out which columns you want to select. For example, use SELECT name FROM testtable; to see only the name field for all records:
Update a Record
Use UPDATE[table name] SET [new values] WHERE [matching condition] to update a record. A full list of UPDATE parameters can be found in the "UPDATE Syntax" chapter of the official MySQL Reference Manual.
For example, to change the record with ID of 2 from Bob to Carl the command is:
Use SELECT to verify that the record was updated correctly: