Posts Under Category: MySQL

In this tutorial I will explain how to install and setup LAMP on a RHEL/CentOS Linux 6.x server.
Before I start the tutorial, let’s know what is LAMP. LAMP stack is a group of open source software used to get web servers up and running. LAMP stands for Linux, Apache, MySQL, and PHP.
read more

Following command will install MySQL on a CentOS server / VPS.

Login to your server as root via SSH, and run following command.

Step 1:

yum install mysql-server
service mysqld start

Step 2:

Set MySQL server root password.

sudo /usr/bin/mysql_secure_installation

MySQL server will ask MySQL server root password. Since you just installed mysql, there is no MySQL root password. So, just press enter.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

MySQL will prompt you to set a root password. Press Y to set mySQL server root password.

Set root password? [Y/n]

Enter and re-enter root password for MySQL server. Please use a strong password and do not use your server root password.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

Now MySQL will ask a series of questions. Press Y for all.

Remove anonymous users? [Y/n]
Disallow root login remotely? [Y/n]
Remove test database and access to it? [Y/n]
Reload privilege tables now? [Y/n]

At the end MySQL will show success message.

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


Our Recommended VPS Hosting

Using below commands you can start, stop and restart MySQL from SSH.
Login to your server as root via SSH, and run following commands.

Start MySQL

service mysql start


/etc/init.d/mysql start

Stop MySQL

service mysql stop


/etc/init.d/mysql stop

Restart MySQL

service mysql restart


/etc/init.d/mysql restart

Status of MySQL

If you want to know the status of MySQL on your server, run below command.

service mysql status

read more

If you want to see the list of MySQL errors on your server, please follow below steps.
You can use tail or cat command to read the error log file. Tail command will display last 10 lines while cat command will display entire log file.

Login to your server as root via SSH and run below command.

tail /var/lib/mysql/hostname.err


cat /var/lib/mysql/hostname.err

In above command change hostname to your server hostname. Like:

If you do not know the hostname of your server, run below command to get the hostname.



Our Recommended VPS Hosting

MySQLTuner is great script to optimize mysql database of a server. It reports a large number of statistics and settings from your MySQL database engine, and then offers general recommendations to improve performance.

To run MySQLTuner on a serve / VPS with cPanel, run following command in SSH


Our Recommended VPS Hosting

This tutorial explains how you can change or move MySQL /tmp directory to a new location on a server / VPS. Login as root to your server via SSH.

Step 1: Open /etc/my.cnf file for editing

nano /etc/my.cnf

Step 2: Add below line under the [mysqld] section and save the file.


To save file press: Ctrl + O
To exit from nano editor press: Ctrl + X

This will set new location of MySQL tmp to /home/mysqltmp. You may change to any different location.

Step 3: Create new directory

mkdir /home/mysqltmp
chmod 1777 /home/mysqltmp

Step 4: Restart MySQL

/etc/init.d/mysql restart

Step 5: Check new location

mysqladmin var | grep tmpdir

This should show following return.

| slave_load_tmpdir                       | /home/mysqltmp
| tmpdir                                  | /home/mysqltmp


Our Recommended Dedicated Server

By default all MySQL database on a server is saved in /var/lib/mysql directory. If you don’t have enough space left in /var directory or if its filling up quickly, you can move your MySQL directory to /home partition.

Assuming you have enough space in /home partition, follow below steps to move MySQL to a new location in /home/var_mysql directory.

Login in server as root via SSH and run following command:

mysqldump --all-databases | gzip > /home/alldatabases.sql.gz
/etc/init.d/mysql stop
mkdir /home/var_mysql
mv /var/lib/mysql /home/var_mysql
chown -R mysql:mysql /home/var_mysql/mysql
ln -s /home/var_mysql/mysql /var/lib/mysql
/etc/init.d/mysql start

You should now have your MySQL at a new location /home/var_mysql

Explanation of above commands

mysqldump --all-databases | gzip > /home/alldatabases.sql.gz

This will create a full backup of all MySQL database. If anything goes wrong, you can restore all MySQL database using this backup file. Name of this file will be alldatabases.sql.gz and it will be saved in /home directory.

/etc/init.d/mysql stop

This will stop mySQL.

mkdir /home/var_mysql

This will create a new directory var_mysql in /home

mv /var/lib/mysql /home/var_mysql

This will move /var/lib/mysql to /home/var_mysql

chown -R mysql:mysql /home/var_mysql/mysql

This will give ownership of /home/var_mysql/mysql directory to user mysql.

ln -s /home/var_mysql/mysql /var/lib/mysql

This will symlinking the old /var/lib/mysql to the new location.

/etc/init.d/mysql start

This will start the MySQL.

What if anything goes wrong

We have create a full MySQL backup in our first step. If anything goes wrong, you will always have the full mysql backup to restore all database. Please refer to below tutorial to restore your databases using the full MySQL backup file.

Our Recommended Dedicated Server

Server root MySQL password can be different from server root password. Here is how you can get root MySQL password.

Login as root via SSH and execute following command

tail /root/.my.cnf


cat /root/.my.cnf

This will show you the root MySQL password of your server.

Our Recommended Dedicated Server

If you have many websites / accounts on your server, and you want to make backup of all mySQL via a single command, here is how you can do. Creating regular full mySQL backup of all accounts is a very good practice. Creating full MySQL backup is also recommended before performing any mysql upgrade.

How to create full MySQL backup

Login as root via SSH and execute following command:

mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases.sql.gz

This will create a SQL-based backup of everything in MySQL and save it into your /home directory. The name of your backup file will be alldatabases.sql.gz

You may change the backup file name and location where it will be saved.

How to restore full MySQL backup

To restore a full MySQL backup file, you will need root MySQL password. Run following command to get root mySQL password

tail /root/.my.cnf

The above command will show the root MySQL password. When you have password, run following command:

gunzip < /home/alldatabases.sql.gz | mysql -u root -p

If your saved full mysql backup is at different location, change the path in above command. If the name of your full mysql backup file is different, please change in above command.

You will be asked to enter password. So, enter the password that you have got above from tail /root/.my.cnf command.

Done!! It will take sometime to restore all database depending on size of database file.

Our Recommended Dedicated Server

You can find the MySQL version on your server using a simple SSH command.

Login in server via SSH, and execute following command.

mysql_config --version

This command will show the current installed version of MySQL on your server. Like




Our Recommended Dedicated Server