Building a MySQL instance on CentOS 7

Austin B.

Published: 5 May 2020

You might need a larger database - for large ecommerce stores or complex sites, for example. To do this, you can configure a CentOS 7 VPS as a MySQL instance on an Unmanaged virtual private server.

This guide will show you how to build a MySQL instance using a 20i VPS.

Step 1 - Install MariaDB

You'll first need to install MariaDB - here's a guide: https://www.tecmint.com/install-mariadb-in-centos-7/

Once you've installed MariaDB you can test the installation by running the following:

[root@vps-b92a95 ~]# mysql -V

Which should give you:

mysql Ver 15.1 Distrib 10.1.44-MariaDB, for Linux (x86_64) using readline 5.1

Step 2 - Access MySQL

You can access MySQL with:

[root@vps-b92a95 /]# mysql -u root -p

You will then be prompted for the password, set when following the guide above. It might be easier to use the VPS password for convenience at this stage.

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.1.44-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Step 3 - Create the database

So now that MariaDB is installed, you will want to create the databases and database users within MariaDB.

To create a database:

[root@vps-b92a95 ~]# create database DATABASE_NAME;

If the site is to be hosted on the shared platform and the database is to be hosted on the VPS, you should create a user with remote access privileges.

So in this format:

[root@vps-b92a95 ~]# create user 'DATABASE_USER'@'%' identified by 'PASSWORD';

For example:

[root@vps-b92a95 ~]# create user 'wordpress_user'@'%' identified by 'password123!';

Now that the user and databases are created, you will want to grant all privileges.

[root@vps-b92a95 ~]# grant all privileges on DATABASE_NAME.* TO 'USER_NAME'@'%';

For example:

[root@vps-b92a95 ~]# grant all privileges on wordpress_testing.* TO 'wordpress_user'@'%';

Then let the privileges take effect:

[root@vps-b92a95 ~]# flush privileges;

You can check to see if the users and databases are accessible:

[root@vps-b92a95 /]# mysql -u wordpress_user -p wordpress_testing
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.1.44-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [wordpress_testing]>

Step 4 - Upload the .SQL file

Once you're happy with the database and user access, you can now look at uploading the database file to the server. You can use WinSCP if you're a Windows user. When the file is uploaded, you'll need to import the file:

[root@vps-b92a95 ~]# mysql -u DATABASE_USERNAME -p DATABASE < importedfile.sql

For example:

[root@vps-b92a95 ~]# mysql -u wordpress_user -p wordpress_testing < wordpress-3132333666.sql

Now that the database is imported you should open the firewall for the incoming MariaDB connections:

[root@vps-b92a95 ~]# firewall-cmd --permanent --add-service=mysql && firewall-cmd --reload

Conclusion

So that's the server all set up and ready. You would need to update the configuration file to connect to the database on the VPS, and you should be good to go.