Basic MySQL database administration on a Linux VPS

MySQL is one of the most popular relational database management systems (RDBMS). It is open-source and it is used by many individuals and organizations. If you are using our Linux VPS hosting services, you are most likely using MySQL as a database system. Therefore, it is important for you to know how to operate with your database system when you need to make some changes in your databases. Today, we will have a very simple task – to teach you how to work with your MySQL database system using these simple commands from your Linux VPS.

The first thing you need to do is to check your MySQL version using the command:

## mysql -h localhost -V

If by any chance you do not have MySQL database server installed on your VPS, you can install it using the commands:

## yum install mysql-server

if you have a CentOS VPS, or:

## apt-get install mysql-server

if you have an Ubuntu VPS or Debian VPS.

Before going even further, you may want to create a backup of your existing databases so you can proceed with this tutorial safely. Please  check our blog post on How to backup and restore your MySQL database

Now, let’s start with the actual tutorial.

To log in to your MySQL database server you can run the command:

## mysql -u username -p

For instance, if you like to log in as ‘root’, you can run the command:

## mysql -u root -p

You will be prompted for your database ‘root’ password. Please note, your database ‘root’ password may not be the same as your SSH ‘root’ password.

Once you successfully log in to your MySQL database server, you can list all the databases using the command:

mysql> SHOW DATABASES;

If you like to create a database, you can use the command:

mysql> CREATE DATABASE db_name;

Replace the ‘db_name’ with the name of the database you like to create.

Additionally, if you like to delete some of the databases, you can simply enter the command:

mysql> DROP DATABASE db_name;

Again, replace the ‘db_name’ with the name of the database you like to delete.

Next, if you like to use some of the databases, type:

mysql> USE db_name;

Once you select the database you like to use, you can list all the tables using the command;

mysql> SHOW TABLES;

For instance, let’s create a ‘test’ database and use it:

mysql> CREATE DATABASE test;
mysql> USE test;

You can now create a table ‘test_table’ using the command:

mysql> CREATE TABLE test_table (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
comment VARCHAR (100)
);

To show all data in a table you can use the command:

mysql> SELECT * FROM db_name;

If you run the command:

mysql> SELECT * FROM test_table;

You will get an ‘Empty set’ message. This is because the ‘test_table’ is new and does not hold any data.

In the next tutorial, we will show you how to insert and manipulate data into your tables.

Of course, you don’t have to do any of this if you use one of our Linux VPS hosting services, in which case you can simply ask our expert Linux admins to do this for you. They are available 24×7 and will take care of your request immediately. For more options, kindly read Basic MySQL Database Administration on Linux VPS – Part 2.

PS. If you liked this post please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.