Install and Configure MySQL on CentOS 8

MySQL is an open-source database engine, suitable for small to large sites.

This article walks you through a basic installation of MySQL on a CentOS 8 server.

Prerequisites

You need access to a Linux server, with sudoers or root privileges.

Installation

Install MySQL

Install the MariaDB server package:

#sudo dnf install mariadb-server

...
Complete!

Verify installation

To verify that MariaDB is installed and started on your system, run:

# sudo systemctl status mariadb.service
● mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/

In our example, CentOS installed MariaDB 10.3 by default. MariaDB is a MySQL drop-in replacement, with additional features.

You can see that CentOS neither starts, nor enables mariadb for auto-start on reboot (vendor preset: disabled)

We’ll need to do both.

#sudo systemctl start mariadb.service
#sudo systemctl enable mariadb

Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

Now, check the status again:

#sudo systemctl status mariadb.service

mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2020-03-20 15:15:55 UTC; 25s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 29427 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 30 (limit: 5016)
   Memory: 79.9M
   CGroup: /system.slice/mariadb.service
           └─29427 /usr/libexec/mysqld --basedir=/usr● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-12-13 19:23:15 UTC; 6min ago
 Main PID: 32025 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─32025 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─32187 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6...

You should see Active: active (running) as in the example above.

Enter the mysql shell to ensure that you have access. Then type exit to exit the shell.

#mysql

MariaDB [(none)]> exit
Bye

Now you can reach your MySQL from another remote host by using:

mysql -h host_name -u user -p

Run the Secure Installation Tool (Optional)

Finally, we recommend that you run MariaDB Server’s built-in secure installation tool. This will walk you through several steps intended to close some known vulnerabilities with the default installation, and will prompt you on which changes it should make at every step:

[root@drab-eagle-9 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

...

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Conclusion

In this article, you learned how to install MariaDB on CentOS 8. Next steps should include configuring your firewall to properly allow only the needed traffic to your MariaDB server instance, and configuring users with the correct permissions for your needs.

Install and Configure MySQL on Fedora 31

MariaDB is a MySQL drop-in replacement, with additional features. MySQL is an open-source database engine, suitable for small to large sites.

This article walks you through a basic installation of MariaDB on a Fedora 31 server.

Prerequisites

You need access to a Linux server, with sudoers or root privileges.

Installation

Install MySQL

Install the MariaDB server package:

#sudo dnf install mariadb mariadb-server

Verify installation

To verify that MariaDB is installed and started on your system, run:

#sudo systemctl status mariadb.service

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

You can see that Fedora neither starts, nor enables mariadb for auto-start on reboot (vendor preset: disabled)

We’ll need to do both.

#sudo systemctl start mariadb.service
#sudo systemctl enable mariadb.service

Now, check the status again:

#sudo systemctl status mariadb.service

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-12-13 19:23:15 UTC; 6min ago
 Main PID: 32025 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─32025 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─32187 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6...

You should see Active: active (running) as in the example above.

Enter the mysql shell to ensure that you have access. Then type exit to exit the shell.

#mysql

MariaDB [(none)]> exit
Bye

Now you can reach your MySQL from another remote host by using:

mysql -h host_name -u user -p

Run the Secure Installation Tool (Optional)

Finally, we recommend that you run MariaDB Server’s built-in secure installation tool. This will walk you through several steps intended to close some known vulnerabilities with the default installation, and will prompt you on which changes it should make at every step:

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

...

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Conclusion

In this article, you learned how to install MariaDB on Fedora 31. Next steps should include configuring your firewall to properly allow only the needed traffic to your MariaDB server instance, and configuring users with the correct permissions for your needs.

Install and Configure MySQL on Debian 10

MariaDB is a MySQL drop-in replacement, with additional features. MySQL is an open-source database engine, suitable for small to large sites.

This article walks you through a basic installation of MariaDB on a Debian 10 server.

Prerequisites

You need access to a Linux server, with sudoers or root privileges.

Installation

Update package information

Update your package information before you get started. For many of these Linux commands, you will receive multiple screens of output that this guide will shorten to ‘‘, but here are the relevant messages to look out for:

#sudo apt update

...
Complete!

Install MySQL

Install the MariaDB server package:

#sudo apt-get install mariadb-server mariadb-client

Verify installation

To verify that MariaDB is installed and started on your system, run:

#sudo systemctl status mariadb.service

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

You can see that Debian neither starts, nor enables mariadb for auto-start on reboot (vendor preset: disabled)

We’ll need to do both.

#sudo systemctl start mariadb.service
#sudo systemctl enable mariadb.service

Now, check the status again:

#sudo systemctl status mariadb.service

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-12-13 19:23:15 UTC; 6min ago
 Main PID: 32025 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─32025 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─32187 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6...

You should see Active: active (running) as in the example above.

Enter the mysql shell to ensure that you have access. Then type exit to exit the shell.

#mysql

MariaDB [(none)]> exit
Bye

Now you can reach your MySQL from another remote host by using:

mysql -h host_name -u user -p

Run the Secure Installation Tool (Optional)

Finally, we recommend that you run MariaDB Server’s built-in secure installation tool. This will walk you through several steps intended to close some known vulnerabilities with the default installation, and will prompt you on which changes it should make at every step:

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

...

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Conclusion

In this article, you learned how to install MariaDB on Debian 10. Next steps should include configuring your firewall to properly allow only the needed traffic to your MariaDB server instance, and configuring users with the correct permissions for your needs.

Using WordPress with MySQL 8.0

If you’ve recently attempted to get WordPress and MySQL 8 to work together, you’ve likely experienced a connection error:

Error establishing a database connection.

Cause

The cause of the issue is that with MySQL 8.0, Oracle has changed the default encryption plugin. The new plugin is called caching_sha2_password. The original plugin, called mysql_native_password, which uses SHA1, is still there, and usable, it’s just not configured as the default.

When a client connects to MySQL 8.0, MySQL assumes the client to be connecting with caching_sha2_password. Many current clients are unable to perform this newer authentication mechanism.

Multiple solutions to the authentication issue

There are multiple ways to solve this problem in the short term.

Arguably, the “best” way to resolve this would be to migrate your WordPress servers to PHP 7.4 or higher. Unfortunately, PHP 7.4 isn’t yet available in many Linux distribution’s software repositories. That means that upgrading PHP, for the time being, is a painful way to get this working. Once PHP 7.4 becomes widely available in the software repositories, that will be the preferred method to solve this issue.

Another way to resolve this would be to change your MySQL config options to use the older method. This may or may not be available to you in a cloud environment, though.

The third way is to create the WordPress user and simply allow it to utilize the older, less secure mechanism. This third option is what we will step through in this article.

Configure WordPress to use mysql_native_password

Modify your WordPress user to use the older, less secure mechanism using the following steps.

Prerequisites

  • You’ll need a MySQL 8.0 database to connect to.
  • You’ll also need root enabled on your Database server.

Log into your Cloud Server

ssh root@your.ip.address

Ensure MySQL Client is installed

apt install mysql-client

Connect from your server to your Database

mysql -h your.database.ip -u root -p

Create your WordPress database and user

mysql> CREATE DATABASE wpdb;
mysql> CREATE USER 'wpuser'@'%' IDENTIFIED WITH mysql_native_password BY 'USE_A_SECURE_PASSWORD_HERE';
mysql> FLUSH PRIVILEGES;

Ensure your new user is created using mysql_native_password

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user WHERE user = 'wpuser';
+--------+-------------------------------------------+-----------------------+------+
| user   | authentication_string                     | plugin                | host |
+--------+-------------------------------------------+-----------------------+------+
| wpuser | *REDACTED                                 | mysql_native_password | %    |
+--------+-------------------------------------------+-----------------------+------+
1 row in set (0.00 sec)

Grant new user appropriate permissions

mysql> GRANT ALL ON wpdb.* TO wpuser;
Query OK, 0 rows affected (0.00 sec)

Verify grants on new user

mysql> SHOW GRANTS FOR wpuser;
+--------------------------------------------------+
| Grants for wpuser@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `wpuser`@`%`               |
| GRANT ALL PRIVILEGES ON `wpdb`.* TO `wpuser`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)

Conclusion

You should now be able to install WordPress using this newly created database, with the user and password you’ve also created here. The auth method that MySQL will allow for the new user has been changed so that older versions of PHP can still connect to MySQL 8.0.

Note that if you create any further users, you’ll have to adjust the auth method for them as well, if they are connecting from software that doesn’t know how to use the new mechanism.

MySQL vs. MariaDB: Choosing one based on your needs

History

MySQL (pronounced My-S-Q-L) is an open source relational database management system (RDBMS). MySQL is free and open-source software under the terms of the GNU General Public License and is also available under a variety of proprietary licenses. MySQL AB, the company, was purchased by Sun Microsystems (now Oracle) in 2009.

MariaDB is a community-developed, commercially supported fork of MySQL, intended to remain free and open-source. A fork is when the developers take the original-source code from a product and begin independent development on it. Development is led by the original creators of MySQL, and the MariaDB fork was created when Oracle purchased Sun Microsystems in 2010. MariaDB intended to maintain high compatibility with MySQL, but with recent releases has included new features that diverge more.

Both MySQL and MariaDB are named after Michael Widenius daughters, My and Maria. Michael (Monty) was the lead developer of MySQL before Oracle’s acquisition and is now the lead developer of MariaDB and CTO of MariaDB Corporation AB.

Who Uses MySQL and MariaDB

Both MySQL and MariaDB boast large followings and large customer bases. Among MySQL’s larger customers are NASA, Tesla, and Youtube. Among MariaDBs larger customers are Redhat, Samsung, and Walgreens.

Some very popular Linux Distributions default to shipping with MariaDB, namely Debian (starting with Debian 9), RedHat Enterprise Linux (starting with RHEL 7), and Ubuntu (starting with Ubuntu 14.04 LTS).

Versioning

MariaDB was originally forked during the MySQL 5.1 timeframe, and as such, the first version carries the 5.1 number. Two additional versions were released between 5.1 and MySQL’s 5.5 version, 5.2 and 5.3. MySQL 5.5 and MariaDB 5.5 were released very close to each other and were meant for high compatibility.

After 5.5, though, the developers of MariaDB decided to make a major version number change, to denote that they were adding specific divergent features to the codebase. They named this release 10.0.

Since that time, MySQL has released 5.6 and 5.7, before doing a major version renumbering themselves, with 8.0 (instead of 6.0) in 2018.

The latest major versions as of this writing are MySQL 8.0 and MariaDB 10.5.

Compatibility

MySQL, being an RDBMS, uses tables, rows, and columns, to store data, as well as queries, triggers, roles, and stored procedures to work with the data. MySQL also uses primary keys to uniquely identify each row, or record, in a table.

Since both MySQL and MariaDB come from the same original code base, they still maintain a high degree of compatibility. Since both the API and underlying protocol are compatible, this means that all connectors, libraries, and applications that work with MySQL should also work with MariaDB. Even most command-line tools are similar, and most have the same names. In general, this allows MariaDB to be a drop-in replacement for MySQL.

That said, MySQL is offered under two versions: the open-source MySQL Community Server, and the proprietary MySQL Enterprise Server. Enterprise is a subscription-based service provided by Oracle Corporation, targeted primarily at the commercial market.

Gradually, the MySQL and MariaDB feature set and internals have diverged. MariaDB has taken the time to document hundreds of incompatibilities between the two, on differing versions, so while many originally hoped that MariaDB would remain a simple branch of MySQL, it has in truth become a full fork, with different specialties and goals.

Licensing and Development

MariaDB is licensed as GPLv2, while MySQL has dual licensing, GPLv2 for the Community Edition, and a proprietary license for the Enterprise Edition. As stated previously, MariaDB is fully open-source, while only the community edition of MySQL is truly open-source.

Oracle’s MySQL development team is the exclusive provider of MySQL, while on the other hand, the development process for MariaDB is more open, where anyone can submit patches to the source tree for consideration to be added to the main code.

Updates

In general, MariaDB has more frequent updates than MySQL. This is both good and bad, in that while newer features and bug fixes are released more quickly, managing more frequent updates can make it a challenge to keep your software up to date.

Features

Both databases offer different features that might appeal to you based on your use cases. Listed here are some features that are exclusive to one or the other.

Authentication

Both MySQL and MariaDB offer mysql_native_password, however, starting with MySQL 8.0, the default is now caching_sha2_password. This enhancement should improve security.

Encryption

MariaDB supports binary log and temporary table encryption, while MySQL does not.

JSON

While both MySQL and MariaDB offer JSON related functions, they differ in their methods of implementation. MySQL, starting with 5.7 implements a native JSON data type, whereas MariaDB only defines an alias, and actually utilizes the column type LONGTEXT.

Keys

MariaDB offers AWS Key management via plugin by default, while you must use the Enterprise Edition if you want this functionality with MySQL.

MySQL Shell

MySQL Shell utilizes the MySQL X protocol, which is not available on MariaDB, to offer an advanced CLI and code editor, as well as scripting capabilities for JavaScript and Python.

Sys schema

Starting with MySQL 8.0, sys schema objects can be utilized for optimization and diagnostic use cases. MariaDB does not have this enhancement.

Threadpool

MariaDB supports connection thread pools, while you must use the Enterprise Edition if you want this functionality with MySQL.

Performance

Benchmarking

As both MySQL and MariaDB have been around for some time now, you can find many public listings of benchmarks with a couple of simple searches. We won’t be doing that here. Rather, we’ll impress upon you the need to perform your own benchmarks, on the solution you’ve chosen, to understand how performance affects your application.

Check out High-Performance MySQL: Optimization, Backups, and Replication by Baron Schwartz, Peter Zaitsev, and Vadim Tkachenko, available at Amazon in multiple formats.

Indexes

Indexes increase performance while at the same time adding a small bit of overhead to the database. Without indexing, searching the database involves reading through entire tables to find relevant rows. With proper indexing, the database engine will find and retrieve specific rows far faster.

MariaDB

MariaDB has several performance optimizations compared to MySQL.

Database Views

Views are virtual tables that can be queried like regular tables. MariaDB is optimized so that only relevant tables are queried when a view is queried, as opposed to MySQL, which queries all tables attached to the view.  

Flash Storage

MariaDB provides the MyRocks storage engine, which is specifically tuned for better performance when using flash storage.

Parallel Execution

Starting with 10.0, MariaDB provides for parallel execution of several queries, automatically.

Thread Pooling

As mentioned before, thread pooling was exclusive to MariaDB and allows for a single thread to handle multiple connections to the database. MySQL has added thread pooling, but only in the Enterprise Edition.

Replication

Both MySQL and MariaDB support replication, allowing you to replicate data from one server to another.

A replica allows you several advantages. You can query the replica for long-running queries without impacting your production instance. You can perform backups on your replica, again without impacting your production instance.

It’s worth noting a couple of caveats, though.

While MariaDB allows you to replicate from MySQL, for example, to migrate your data from MySQL to MariaDB, most MySQL versions will not allow you to replicate from MariaDB.

Additionally, MySQL GTID and MariaDB GTID are different implementations, so when you do replicate, the GTIDs will be adjusted.

Storage Engines

MariaDB supports more storage engines by far, but what’s important is that you choose the storage engine that meets the needs of your application and performance goals.

Documentation

MySQL documentation is owned and managed by the Oracle Corporation.

MariaDB documentation is stewarded by the MariaDB Foundation, but others can contribute.

Conclusion

Both MySQL and MariaDB are powerful databases that you can use to power your applications.

MySQL is a proven database with a strong following.

MariaDB provides a drop-in replacement for MySQL while allowing people to contribute to its open-source product and documentation.

Each has features that are useful that the other does not have.

Before choosing, ask yourself the following questions:

  1. Are you planning to use a feature that is exclusive to one or the other?
  2. Are you planning to use one of the database engines that is exclusive to one or the other?
  3. Is it important for you to have a voice in the community or an impact on the development process of the database you choose?

At this point, you probably have a pretty good idea which you will choose.

References

Wikipedia

Vendor Sites

MariaDB Corporation AB

MariaDB Foundation

MySQL (Oracle)

How to Install SQLite on Fedora 30

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It’s a popular solution for applications that need to use on-disk files formatted as lightweight databases to run efficiently.

Prerequisites

  • Access to a Fedora Linux server, with sudoers or root privileges.

Installation

Update packages and information

Update your packages before you get started. For many of these Linux commands, you will receive multiple screens of output that this guide will shorten to ‘…’, but here are the relevant messages to look out for:

# yum update
...
Fedora Modular 30 - x86_64 - Updates
...
Install   10 Packages
Upgrade  175 Packages
...
Is this ok [y/N]:

Press ‘y’

Install SQLite

# yum install sqlite
...
Install  1 Package
...
Is this ok [y/N]:

Press ‘y’

Installed:
  sqlite-3.26.0-7.fc30.x86_64

Complete!

Verify Installation

Once that’s complete, check the version, as of this writing, we installed 3.26.0:

# sqlite3 --version
3.26.0 2018-12-01 12:34:55

Start sqlite3:

# sqlite3
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Exit from the sqlite3 shell using .exit or .quit.

Conclusion

In this article, you learned how to install SQLite on Fedora. Next steps should include opening your desired sqlite files in its shell, or configuring your application to use your new SQLite instance.

How to Install SQLite on CentOS 7

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It’s a popular solution for applications that need to use on-disk files formatted as lightweight databases to run efficiently.

Prerequisites

  • Access to an CentOS 7 Linux server, with sudoers or root privileges.

Installation

Update packages and information

Update your server’s packages before you get started. For many of these Linux commands, you will receive multiple screens of output that this guide will shorten to ‘…’, but here are the relevant messages to look out for:

# yum update
...
Determining fastest mirrors
...
Resolving Dependencies
...
Install    1 Package  (+6 Dependent packages)
...
Is this ok [y/d/N]:

Press ‘y’

...
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
...
Is this ok [y/N]:

Press ‘y’

...
Complete!

Install SQLite

# yum install sqlite
...
Package sqlite-3.7.17-8.el7.x86_64 already installed and latest version

Verify Installation

Once that’s complete, check the version, as of this writing, we installed 3.7.17:

# sqlite3 --version
SQLite version 3.7.17 2013-05-20 00:56:22

Start sqlite3:

# sqlite3
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Exit from the sqlite3 shell using .exit or .quit.

Conclusion

In this article, you learned how to install SQLite on CentOS 7. Next steps should include opening your desired sqlite files in its shell, or configuring your application to use your new SQLite instance.

How to Install SQLite on Debian 10

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It’s a popular solution for applications that need to use on-disk files formatted as lightweight databases to run efficiently.

Prerequisites

  • Access to an Debian Linux server, with sudoers or root privileges.

Installation

Update package information

Update your server’s package information before you get started. For many of these Linux commands, you will receive multiple screens of output that this guide will shorten to ‘…’, but here are the relevant messages to look out for:

# apt-get update
...
Fetched 9981 kB in 2s (4052 kB/s)
Reading package lists... Done

Install SQLite

# apt install sqlite3
...
Unpacking sqlite3 (3.27.2-3) ...
Setting up sqlite3 (3.27.2-3) ...

Verify Installation

Once that’s complete, check the version, as of this writing, we received 3.22.0:

# sqlite3 --version
3.27.2 2019-02-25 16:06:06

Start sqlite3:

# sqlite3
SQLite version 3.27.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Exit from the sqlite3 shell using .exit or .quit.

Conclusion

In this article, you learned how to install SQLite on Debian 10. Next steps should include opening your desired sqlite files in its shell, or configuring your application to use your new SQLite instance.

How to Install SQLite on Ubuntu 18.04

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It’s a popular solution for applications that need to use on-disk files formatted as lightweight databases to run efficiently.

Prerequisites

  • Access to an Ubuntu Linux server, with sudoers or root privileges.

Installation

Update package information

Update your server’s package information before you get started. For many of these Linux commands, you will receive multiple screens of output that this guide will shorten to ‘…’, but here are the relevant messages to look out for:

# apt-get update
...
Fetched 9981 kB in 2s (4052 kB/s)
Reading package lists... Done

Install SQLite

# apt install sqlite3
...
Unpacking sqlite3 (3.22.0-1ubuntu0.2) over (3.22.0-1) ...
...
Setting up sqlite3 (3.22.0-1ubuntu0.2) ...

Verify Installation

Once that’s complete, check the version. As of this writing, we installed 3.22.0:

# sqlite3 --version
3.22.0 2018-01-22 18:45:57

Start sqlite3:

# sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Exit from the sqlite3 shell using .exit or .quit.

Conclusion

In this article, you learned how to install SQLite on Ubuntu 18.04 LTS. Next steps should include opening your desired sqlite files in its shell, or configuring your application to use your new SQLite instance.

Install and configure MySQL on CentOS 7

MySQL is an open-source database engine, suitable for small to large sites.

This article walks you through a basic installation of MySQL on a CentOS 7 server.

Prerequisites

You need access to a Linux server, with sudoers or root privileges.

Installation

Update package information

Update your package information before you get started. For many of these Linux commands, you will receive multiple screens of output that this guide will shorten to ‘‘, but here are the relevant messages to look out for:

#yum update

...
Complete!

Install MySQL

Install the MariaDB server package:

#sudo yum install mariadb-server

...
Installed:
  mariadb-server.x86_64 1:5.5.64-1.el7

Dependency Installed:
  mariadb.x86_64 1:5.5.64-1.el7                      perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
  perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7        perl-DBD-MySQL.x86_64 0:4.023-6.el7
  perl-DBI.x86_64 0:1.627-4.el7                      perl-Data-Dumper.x86_64 0:2.145-3.el7
  perl-IO-Compress.noarch 0:2.061-2.el7              perl-Net-Daemon.noarch 0:0.48-5.el7
  perl-PlRPC.noarch 0:0.2020-14.el7

Complete!

In our example, CentOS installed MariaDB 5.5 by default. MariaDB is a MySQL drop-in replacement, with additional features.

Verify installation

To verify that MariaDB is installed and started on your system, run:

#sudo systemctl status mariadb.service

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: inactive (dead)

You can see that CentOS neither starts nor enables MariaDB for auto-start on reboot (vendor preset: disabled)

We’ll need to do both.

#sudo systemctl start mariadb.service
#sudo systemctl enable mariadb

Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

Now, check the status again:

#sudo systemctl status mariadb.service

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-12-13 19:23:15 UTC; 6min ago
 Main PID: 32025 (mysqld_safe)
   CGroup: /system.slice/mariadb.service
           ├─32025 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─32187 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6...

You should see Active: active (running) as in the example above.

Enter the MySQL shell to ensure that you have access. Then type exit to exit the shell.

#mysql

MariaDB [(none)]> exit
Bye

Now you can reach your MySQL from another remote host by using:

mysql -h host_name -u user -p

Run the Secure Installation Tool (Optional)

Finally, we recommend that you run MariaDB Server’s built-in secure installation tool. This will walk you through several steps intended to close some known vulnerabilities with the default installation, and will prompt you on which changes it should make at every step:

[root@drab-eagle-9 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

...

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Conclusion

In this article, you learned how to install MariaDB on CentOS 7. The next steps should include configuring your firewall to properly allow only the needed traffic to your MariaDB server instance and configuring users with the correct permissions for your needs.