Basic MySQL database administration on a Linux

logo-mysqlMySQL 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.

Basic MySQL database administration on a Linux
Written by: AFRIDA YANTI
Reting: 4.9


Cheap Best Hosting is a leading provider of web hosting, shared hosting, resellers hosting, virtual private servers, vps hosting, dedicated servers and domain name registration.


Follow me on Google+ | Twitter | Facebook | Linkedin | YouTube

Share This Post

Recent Articles

Leave a Reply

© 2017 Cheap Best Hosting Blog. All rights reserved. · Entries RSS · Comments RSS
Hosting By Cheap Best Hosting