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/Server 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/Server.
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/Server, or:
## apt-get install mysql-server
if you have an Ubuntu VPS
Before going even further, you may want to create a backup of your existing databases so you can proceed with this tutorial safely.
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.
