Install and Use MySQL/MariaDB

Learn how to get started with the popular relational database MySQL/MariaDB. This quickstart guide will cover the installation of both, and an introduction to basic MySQL/MariaDB commands.

    Requirements

    • A Cloud Server running Linux (CentOS 7 or Ubuntu 16.04)
    Note

    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. It was acquired by Sun Microsystems in 2008, and then 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 installed by default on a standard Cloud Server running Ubuntu 16.04. Use the sudo mysql --version command to verify that MySQL is installed:

    user@localhost:~# sudo mysql --version
    mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

    If MySQL is not installed, you can install it by first updating your packages:

    sudo apt-get update

    Then install MySQL:

    sudo apt-get install mysql-server

    Follow the prompts to install MySQL.

    MySQL should start itself automatically when installed. If it does not start, you can start it with the command:

    sudo systemctl start mysql

    Enable MySQL to start at boot with the command:

    sudo systemctl enable mysql

    If you need to stop or restart MySQL, use the commands:

    sudo systemctl stop mysql
    sudo systemctl restart mysql

    vServer (VPS) from IONOS

    Low-cost, powerful VPS hosting for running your custom applications, with a personal assistant and 24/7 support.

    100 % SSD storage
    Ready in 55 sec.
    SSL certificate

    Install MariaDB on CentOS 7

    MariaDB is installed by default on a standard Cloud Server running CentOS 7. Use the sudo mysql --version command to verify that MariaDB is installed:

    [user@localhost ~]# sudo mysql --version
    mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

    If MariaDB is not installed, you can install it by first updating your system:

    sudo yum update

    Then install MariaDB:

    sudo yum install mariadb-server

    MariaDB should start itself automatically when installed. If it does not start, you can start it with the command:

    sudo systemctl start mariadb

    Enable MariaDB to start at boot with the command:

    sudo systemctl enable mariadb

    If you need to stop or restart MariaDB, use the commands:

    sudo systemctl stop mariadb
    sudo systemctl restart mariadb

    Log In to the MySQL/MariaDB Client

    From the command line, enter the MySQL/MariaDB client with the command:

    mysql -u root -p

    For a default MySQL/MariaDB installation, use the default 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.

    [root@localhost ~]# mysql -u root -p
    Enter password:
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 83
    Server version: 5.5.52-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]>

    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:

    CREATE DATABASE testdb;

    List and Select a Database

    Use SHOW DATABASES; to list all available databases:

    MariaDB [(none)]> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | testdb             |
    +--------------------+
    4 rows in set (0.00 sec)

    Use USE [database name]; to connect to a database and select it for use:

    MariaDB [(none)]> USE testdb;
    Database changed

    Drop a Database

    Use DROP DATABASE [database name] to delete a database. For example, to delete the testdb database, the command is:

    DROP DATABASE testdb;

    To exit the client, type:

    quit;

    Then hit Enter.

    Cloud backup from IONOS

    Make costly downtime a thing of the past and back up your business the easy way!

    Simple
    Secure
    Integrated

    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:

    CREATE TABLE testtable (
      id char(5) PRIMARY KEY,
      name varchar(40)
      ); 

    Use SHOW TABLES; to verify that your table was created:

    MariaDB [testdb]> SHOW TABLES;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | testtable        |
    +------------------+
    1 row in set (0.00 sec)

    Drop a Table

    Use DROP TABLE [table name]; to delete a table. For example, to delete the testtable table, the command is:

    DROP TABLE testtable;

    Use SHOW TABLES; to verify that your table was deleted:

    MariaDB [testdb]> SHOW TABLES;
    Empty set (0.00 sec)

    Working With Records: Insert, Select, Update, 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 testtable the command is:

    INSERT INTO testtable VALUES (1, 'Alice');
    INSERT INTO testtable VALUES (2, 'Bob');
    Note

    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. 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:

    SELECT * from testtable;

    This will return all of the table contents.

    You can also specify matching conditions. For example, use SELECT * from testtable where id = '1'; to select only the record with ID of 1:

    MariaDB [testdb]> SELECT * from testtable;
    +----+-------+
    | id | name  |
    +----+-------+
    | 1  | Alice |
    | 2  | Bob   |
    +----+-------+
    2 rows in set (0.00 sec)

    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:

    MariaDB [testdb]> SELECT name FROM testtable;
    +-------+
    | name  |
    +-------+
    | Alice |
    | Bob   |
    +-------+
    2 rows in set (0.00 sec)

    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:

    UPDATE testtable SET name = 'Carl' WHERE id = '2';

    Use SELECT to verify that the record was updated correctly:

    MariaDB [testdb]> SELECT * FROM testtable;
    +----+-------+
    | id | name  |
    +----+-------+
    | 1  | Alice |
    | 2  | Carl  |
    +----+-------+
    2 rows in set (0.00 sec)

    Wait! We’ve got something for you!
    Have a look at our great prices for different domain extensions.


    Enter the web address of your choice in the search bar to check its availability.
    .club
    $1/1st year
    then $15/year
    .com
    $1/1st year
    then $15/year
    .info
    $1.60/1st year
    then $20/year
    .org
    $1/1st year
    then $25/year