In some environments, you may need a database server. This article will cover the basic installation and setting up of a database and table.
Let's get started and get the Database Server up and running.
Installation
For CentOS, the major Database Service would be MariaDB. On an Ubuntu Server, MySQL is more predominant. It is best to keep in mind that MariaDB is compatible with MySQL, so we will worry more about MariaDB.
NOTE: The commands are pretty well the same, so you can use most of the same commands on either system.
To install the server services, execute the following on CentOS:
sudo yum install mariadb mariadb-server
For Ubuntu to install MariaDB, use:
sudo apt install mariadb-client mariadb-server
If you want to install MySQL, then use the command:
sudo apt install mysql-client mysql-server
NOTE: Installing MySQL on CentOS 7 requires a more in-depth installation procedure. There is no 'yum' command to install it from a repository. It requires you to download the RPM file and install it.
After performing the installation, you need to start the service. On both Operating Systems (OS), the commands to start and enable the service are the same:
sudo systemctl start mariadb
sudo systemctl enable mariadb
Now that the service is installed and running, we need to secure the service.
Securing the Database Service
The command is the same on either OS and will ask the same questions:
mysql_secure_installation
Even though we are using 'MariaDB', the command is correct even if it begins with 'mysql'.
The command can be run at any time to change settings. You can tell this from the first question that asks for the 'Enter current password of root'. This is not the Root password of the OS but of the Database Service. Since we just installed the service, there is no password. Just press enter and do not enter anything for the question.
NOTE: On an Ubuntu system, start the command with Root privileges using 'sudo' or you will get an error.
The second question will ask if you want to set the Root password for the Database Service. Enter 'y' to set the password. This will help anyone from accessing the database and making changes. You should then be asked for the password you want to use and to repeat the entry.
The third question asks if you want to remove the 'anonymous' user that is created when the service is installed. You should answer 'y' for this as well.
The fourth question wants to know if you want to disallow remote access for the 'root' user to the database. Again, this should be answered 'y', but if you log in as administrator remotely, then you might answer 'n'.
For the fifth question, you'll be asked if you want to remove the test databases and access them. Answer 'y', unless you plan on doing some testing with existing databases.
The sixth and final question asks if you want to reload the privilege tables now. This ensures that any changes you make will take effect. If you answer 'n', then you'll need to restart the service.
Now, the service should be secure for use. You can verify that the service is listening on Port 3306 by running the command 'netstat -lnt'.
On an Ubuntu system, you may notice that the service is only listening locally on port 127.0.0.1. You can change this by editing the file '/etc/mysql/mariadb.conf.d/50-server.cnf' and changing the line 'bind-address = 127.0.0.1' to 'bind-address = 0.0.0.0'. Restart the service and then rerun 'netstat' to the change.
Create a Database User
To access a database managed by the service, you need to have a valid user account. We previously set up a password for the 'root' user in the database service. I doubt anyone would want to be giving this out to everyone, plus it is set up for local access only (unless you answered otherwise).
We will set up a user called 'dbuser' and make a password that is 'password'. You can change the username and password as you need.
On the Database Server, enter the following:
mysql -u root -p
create user 'dbuser'@localhost IDENTIFIED BY 'password';
select user from mysql.user;
flush privileges;
NOTE: For Ubuntu, you need to run 'mysql' as sudo or use the sudo command at the beginning.
The first line lets you log into the database server as the user 'root' and prompts you for the password.
The second line creates the new user with the specified password.
The third line lists all of the users.
The fourth line reloads all of the privileges so you do not need to restart the service.
To exit the database server, just enter 'quit'.
You can add all of the users you need to access the database server. Verification of all the users can be checked with the third command.
At this point, the new user has no privileges to any databases, so we will change that in a bit when we have a database to give it full access. When a user is created, it has the usage of all databases.
Create a Database
If you log into MariaDB, you can issue the command 'show databases;' to see a list of the current databases managed by the server. You should see something similar to the list of 'information_schema', 'mysql', and 'performance_schema'.
So, to create a database, you can use the command 'create database <name>;'. The '<name>' is the name of the new database.
Once you create a database, you can create a new user to have full privileges to the new database. The command is:
grant all privileges on <db-name>.* to '<username>'@localhost identified by '<password>';
In this command, there are three entries that you need to make: the database name that already exists, The new user is created for the database server, and the password of the new user. Of course, this command must be run locally on the Database Server.
Keep in mind that the new user will have 'all privileges' on the database. These privileges include all tables in the database.
Create a Table
A database can consist of multiple tables or a single table. Multiple tables can be relational, such as an ID that will link multiple tables together. For example, let's assume a retail store that takes orders.
One table will be made to hold the orders. The table will be connected to another 'Customer' Table by a 'Customer ID'. This way the business knows who placed the order and can easily retrieve the address for delivery. Addresses can be retrieved for mailing information for catalogs.
If you perform a 'show datbases;' command, you should see that 'os' exists. Notice in the prompt that there is listed 'none'. This is the current database being managed. To change to a new database, use the command 'use <database>'. Switch to the 'os' database and you should see the prompt change.
Similar to the 'show databases;' command, you can issue a 'show tables;' command to any existing tables in the database. It should be an empty set right now.
So, let's create a table named 'os_names_table' to store some names of OS Distributions:
create table os_names_table (
os_id int unsigned not null auto_increment primary key,
os_name varchar(30) not null);
NOTE: I separated the command into lines for readability. This can be all on one line or entered as is since the command is not finished until you put in a semicolon.
The command 'show tables;' will show the table you just created.
Now we need to populate the table a little. I will only add five entries to keep this short. The command is:
insert into <table name> (<table label>) values ('value1'), ('value2'), ('valuex');
The actual command is similar to:
insert into os_names_table (os_name) values ('Ubuntu'), ('CentOS'), ('Debian'), ('Linux Mint'), ('Slackware');
This should place the five entries into the table. You can issue another 'insert' statement to add more entries if needed.
You can see the contents of the table with the command 'select * from os_names_table;'.
To see specific information about the fields, and not the contents, use the command 'describe <table name>;'.
You can also see the command that created a table by issuing 'show create table <table name>;'.
Conclusion
This is a basic understanding of the MariaDB Database Server or MySQL.
The test doesn't get too deep into the workings of a Database Server but does hit the basics. Try all of this and make sure you understand it. If you want more information, it exists at www.mariadb.com.
Let's get started and get the Database Server up and running.
Installation
For CentOS, the major Database Service would be MariaDB. On an Ubuntu Server, MySQL is more predominant. It is best to keep in mind that MariaDB is compatible with MySQL, so we will worry more about MariaDB.
NOTE: The commands are pretty well the same, so you can use most of the same commands on either system.
To install the server services, execute the following on CentOS:
sudo yum install mariadb mariadb-server
For Ubuntu to install MariaDB, use:
sudo apt install mariadb-client mariadb-server
If you want to install MySQL, then use the command:
sudo apt install mysql-client mysql-server
NOTE: Installing MySQL on CentOS 7 requires a more in-depth installation procedure. There is no 'yum' command to install it from a repository. It requires you to download the RPM file and install it.
After performing the installation, you need to start the service. On both Operating Systems (OS), the commands to start and enable the service are the same:
sudo systemctl start mariadb
sudo systemctl enable mariadb
Now that the service is installed and running, we need to secure the service.
Securing the Database Service
The command is the same on either OS and will ask the same questions:
mysql_secure_installation
Even though we are using 'MariaDB', the command is correct even if it begins with 'mysql'.
The command can be run at any time to change settings. You can tell this from the first question that asks for the 'Enter current password of root'. This is not the Root password of the OS but of the Database Service. Since we just installed the service, there is no password. Just press enter and do not enter anything for the question.
NOTE: On an Ubuntu system, start the command with Root privileges using 'sudo' or you will get an error.
The second question will ask if you want to set the Root password for the Database Service. Enter 'y' to set the password. This will help anyone from accessing the database and making changes. You should then be asked for the password you want to use and to repeat the entry.
The third question asks if you want to remove the 'anonymous' user that is created when the service is installed. You should answer 'y' for this as well.
The fourth question wants to know if you want to disallow remote access for the 'root' user to the database. Again, this should be answered 'y', but if you log in as administrator remotely, then you might answer 'n'.
For the fifth question, you'll be asked if you want to remove the test databases and access them. Answer 'y', unless you plan on doing some testing with existing databases.
The sixth and final question asks if you want to reload the privilege tables now. This ensures that any changes you make will take effect. If you answer 'n', then you'll need to restart the service.
Now, the service should be secure for use. You can verify that the service is listening on Port 3306 by running the command 'netstat -lnt'.
On an Ubuntu system, you may notice that the service is only listening locally on port 127.0.0.1. You can change this by editing the file '/etc/mysql/mariadb.conf.d/50-server.cnf' and changing the line 'bind-address = 127.0.0.1' to 'bind-address = 0.0.0.0'. Restart the service and then rerun 'netstat' to the change.
Create a Database User
To access a database managed by the service, you need to have a valid user account. We previously set up a password for the 'root' user in the database service. I doubt anyone would want to be giving this out to everyone, plus it is set up for local access only (unless you answered otherwise).
We will set up a user called 'dbuser' and make a password that is 'password'. You can change the username and password as you need.
On the Database Server, enter the following:
mysql -u root -p
create user 'dbuser'@localhost IDENTIFIED BY 'password';
select user from mysql.user;
flush privileges;
NOTE: For Ubuntu, you need to run 'mysql' as sudo or use the sudo command at the beginning.
The first line lets you log into the database server as the user 'root' and prompts you for the password.
The second line creates the new user with the specified password.
The third line lists all of the users.
The fourth line reloads all of the privileges so you do not need to restart the service.
To exit the database server, just enter 'quit'.
You can add all of the users you need to access the database server. Verification of all the users can be checked with the third command.
At this point, the new user has no privileges to any databases, so we will change that in a bit when we have a database to give it full access. When a user is created, it has the usage of all databases.
Create a Database
If you log into MariaDB, you can issue the command 'show databases;' to see a list of the current databases managed by the server. You should see something similar to the list of 'information_schema', 'mysql', and 'performance_schema'.
So, to create a database, you can use the command 'create database <name>;'. The '<name>' is the name of the new database.
Once you create a database, you can create a new user to have full privileges to the new database. The command is:
grant all privileges on <db-name>.* to '<username>'@localhost identified by '<password>';
In this command, there are three entries that you need to make: the database name that already exists, The new user is created for the database server, and the password of the new user. Of course, this command must be run locally on the Database Server.
Keep in mind that the new user will have 'all privileges' on the database. These privileges include all tables in the database.
Create a Table
A database can consist of multiple tables or a single table. Multiple tables can be relational, such as an ID that will link multiple tables together. For example, let's assume a retail store that takes orders.
One table will be made to hold the orders. The table will be connected to another 'Customer' Table by a 'Customer ID'. This way the business knows who placed the order and can easily retrieve the address for delivery. Addresses can be retrieved for mailing information for catalogs.
If you perform a 'show datbases;' command, you should see that 'os' exists. Notice in the prompt that there is listed 'none'. This is the current database being managed. To change to a new database, use the command 'use <database>'. Switch to the 'os' database and you should see the prompt change.
Similar to the 'show databases;' command, you can issue a 'show tables;' command to any existing tables in the database. It should be an empty set right now.
So, let's create a table named 'os_names_table' to store some names of OS Distributions:
create table os_names_table (
os_id int unsigned not null auto_increment primary key,
os_name varchar(30) not null);
NOTE: I separated the command into lines for readability. This can be all on one line or entered as is since the command is not finished until you put in a semicolon.
The command 'show tables;' will show the table you just created.
Now we need to populate the table a little. I will only add five entries to keep this short. The command is:
insert into <table name> (<table label>) values ('value1'), ('value2'), ('valuex');
The actual command is similar to:
insert into os_names_table (os_name) values ('Ubuntu'), ('CentOS'), ('Debian'), ('Linux Mint'), ('Slackware');
This should place the five entries into the table. You can issue another 'insert' statement to add more entries if needed.
You can see the contents of the table with the command 'select * from os_names_table;'.
To see specific information about the fields, and not the contents, use the command 'describe <table name>;'.
You can also see the command that created a table by issuing 'show create table <table name>;'.
Conclusion
This is a basic understanding of the MariaDB Database Server or MySQL.
The test doesn't get too deep into the workings of a Database Server but does hit the basics. Try all of this and make sure you understand it. If you want more information, it exists at www.mariadb.com.
