Now a days most of the websites are being built using scripting language, So it is predicted that there should be a database system running on Shared, VPS or Dedicated server.

MySQL is one of the world’s popular (open source Relational Database Management Systems (RDBMS)) database, because of its superior speed, easy to use and reliablilty. It is a crucial part of LAMP (Linux, Apache, MySQL, PHP, Perl, Python) family and more companies are using it as an alternative, because of its low cost and platform independence as compared to other expensive database system.

Generally most of the webmaster uses graphical user interface to manage their databases. The GUI based web tool is phpMyAdmin and desktop tool is MySQL Administrator. But for a System admin its more easy to use simple MySQL shell commands via SSH.

Note: This is for the customers who have SSH access on their Linux VPS or dedicated server.

Add/Delete Mysql user via SSH

You can use the following commands to easily add and delete users in MySQL database.

1> Login to your server through SSH

2> Run this command to login into MySQL command interface.

# mysql -u username  -p  (For Eg: mysql -u phil_dbname -p password)

Once you run the command, it will ask you to enter password. Type in password and press enter, you will see the prompt:

# mysql>

3> To add a user in MySQL, run the following command:

# mysql> CREATE USER user@host IDENTIFIED BY ‘password’;

Note: Replace the “user” by the username you wish to create and “host” by the server IP address.

4> To delete or remove a user from the MySQL, run the following command:

# mysql> DROP USER user@host;

Note: Replace the “user” by the username you wish to delete and “host” by the server IP address.

Backup/Restore Mysql database via SSH

If your database is big, then It is not suggested to use PHPMyAdmin web based tool to backup or restore MySQL database, because it will time out.

You can login into SSH of your VPS/Dedicated server and follow the steps to backup and restore MySQl database in Linux VPS server.

1> Run the following command to take backup of database

# mysqldump -u username -ppassword databasename > databasename.sql

Note: Always put “-u & -p” before the username and password. Please do not forget to change the username, password and database with actual detail in the command.

You can also compress the backup generated, so that you can transfer it more easily. Here you can run the following command to compress it.

# tar -cvzf databasename.tgz databasename.sql

To untar the compressed backup, run the following command in SSH.

# tar -zxvf databasename.tgz

The backup file will be extracted into a folder of the same name as tar file:

# cd databasename

# ls

You will see the following file “databasename.sql”

To Restore the above file into another database, run the following command via SSH on your Linux VPS server.

# mysql  -u username -ppassword databasename < databasename.sql

Note: You will need to adjust database file path if you are running the above command from another directory.

Congratulations! you have restored the database successfully.
You can also check the following URLs for more Mysql commands:

http://www.pantz.org/software/mysql/mysqlcommands.html

https://dev.mysql.com/doc/refman/5.6/en/