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
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:
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 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