Posts Under Category: MySQL

Step 1:

Login to your server via SSH as root user.

Step 2

Use below command to create / export a database.

mysqldump database_name > /backup/database_name.sql


database_name -> The name of database of which you want to create backup.
/backup/database_name.sql -> Location and file name of database backup file.


Step 1

Login to your server as root via SSH.

Step 2

By default, MySQL root password is saved in the /root/.my.cnf file.
Open this file using command.

cat /root/.my.cnf

Step 3

Here you can see your server mysql root password.
mysql-root password

Our Recommended VPS Hosting

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