1.Edit the /etc/my.cnf file and look for this line:

2.Add the following entry in the /etc/my.cnf file

3. Save the file, and restart mysql

4. You can use the following command to check the “storage engine” used in the server.

If you are getting the following error while trying to start MySQL service;

And if you are getting the following error in the MySQL log;

The issue might be due to the lack of space in the ‘/var’ partition. So you need to reduce the disk space, which should fix the issue.

Using the above commands, you can see that files and folders which consumes more size.  You need to delete the unwanted files or folders and try reduce the size.

Try restart the MySQL service. 🙂

Some times the ‘/var’ partition get crowded and we might need to relocate the database to some other location where there is enough space. Below are the steps given for a trouble free relocation.

Note: Let ‘/test/mysql’ be the directory to which we want to relocate the database.

Steps to change the mysql database directory:

The mysqldump can be used to dump a database or a collection of databases for backup or for transferring the data to another MySQL server.

Execute the following command to get the dump of a table.

This table can be added to the database using the following command.

In order to take dump of N tables named table1, table2.table3….tableN use the following syntax:

This error means no space left on hard disk. If you get his error, you need to check all filesystems where MySQL operates.

a) Stop mysql server
# /etc/init.d/mysql stop
OR
# /etc/init.d/mysqld stop

b) Check filesystem and /tmp directories:
$ df -h
$ cd /tmp
$ df -h /tmp

c) Remove files from /tmp to free up space:
# cd /tmp
# rm -rf *

d) Look into /var/log directory and remove or compress logs file.

e) Use myisamchk command to check and repair of ISAM table:
# cd /var/lib/mysql
# myisamchk

f) Increase disk space (add new hard disk or  remove unwanted softwares)

g) Start the mysql server:
# /etc/init.d/mysql start
OR
# /etc/init.d/mysqld start

Following is the error message in mysql server log file /var/log/mysqld.log:

/usr/libexec/mysqld: Can’t create/write to file ‘/tmp/ibCfJwf1? (Errcode: 13)

070420 10:07:58 InnoDB: Error: unable to create temporary file; errno: 13
070420 10:07:58 [ERROR] Can’t init databases
070420 10:07:58 [ERROR] Aborting

070420 10:07:58 [Note] /usr/libexec/mysqld: Shutdown complete

While trying to start it, following is the error.

Initializing MySQL database: [ OK ]
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]

This error occurs when MySQL is not able to access the /tmp directory to write and create temporary files. Make sure /tmp is owned by root and 1777 permisssion is set on /tmp directory.

Following commands will fix the problem.

# chown root:root /tmp
# chmod 777 /tmp
# /etc/init.d/mysqld start


Now MySQL should start without a problem.

When you try to create a remote MySQL user, sometimes  you may get the following the error.

Why this error occurs?

You can eliminate this error by executing the command in command prompt. mysql_fix_privilege_tables –password=root_password

This command will update MySQL tables and its structure.

Note: Before executing this command, make sure that you have taken the full backup of MySQL.

MySQL Error : Error connecting to MySQL: Access denied for user: ‘root@localhost’ (Using password: YES)

Situation:
If we try to connect mysql, sometimes we get an error as  given below.
          

This is mainly caused due to the fact that the user root does not have enough privileges to access the mysql databases or the password set for the user root to connect mysql was changed.

Solution:

Follow the steps below to fix this error.

1. Start mysql using mysqld_safe

Note:
mysqld_safe is used to start mysql server by disabling certain feature that restrict a user to access mysql.
The option –skip-grant-tables is used to neglect the permission grant to different users of mysql.

3. Kill all mysql processes and start mysqld

4. Verify the result