MySQL Commands for Hosting in Linux VPS

MySQL is the most popular open source database platform for running a site in your Linux VPS. Here is the basic knowledge one should keep it handy.

How to install MySQL

Run the following commands from your debian based (e.g. Ubuntu) VPS terminal to install MySQL.

sudo apt-get install mysql-server
sudo mysql_install_db
sudo mysql_secure_installation

Restart MySQL

sudo service mysql restart

How to Create a Database

Login to MySQL using root user and root password.

mysql -u root -p

You will be prompted to enter password for root. Provide the password.

To view existing databases run:

show databases;

To create a new database run following command. Database name has been chosen as exampledb here.

create database exampledb;

For any operation on this database you need to create a user and grant necessary privileges to the user.

How to Create User

Before creating user at first view the existing users:

use mysql;
select user,host from mysql.user;

You will be shown few usernames.

Now create your own user

create user 'exuser'@'localhost' identified by 'expwd';

The above will create an user named ‘exuser’ with password as ‘expwd’

Grant Privileges

grant all privileges on exampledb.* to 'exuser'@'localhost';
flush privileges;

Now your database is ready for all types of operation by exuser.

Now suppose you have already a running website and you want to host this site to another hosting provider. Then you have to first export the existing database and then import the database to a newly created database.

First Export the existing database from your old host.

Export MySQL Database

mysqldump -u root -p olddb > olddb.sql

A file named olddb.sql will be created to the home directory. You need to copy this file to your new host home directory. You may use FTP programme to download the sql file from old host and then upload the sql file to new host.

Now Import to new database

Import MySQL Database

mysql -u username -p newdb < olddb.sql