All Entries Tagged With: "Mysql"

MySQL database not listed in Plesk interface

Issue :
A MySQL database was created and still does exist but it is not being listed in the Plesk interface.
Solution :
This usually happens when the database is not created via Plesk. Instead, it might have been created via SSH.
To get around this, there are two ways :
1. This is the simpler of [...]

Resetting MySQL password

Issue :
How to reset MySQL ‘root’ password ?
Solution :
-> Stop the mysql daemon.
/etc/init.d/mysqld stop
-> Run the following commands :
mysqld_safe –skip-grant-tables &
mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(”newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
-> Restart the service.
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

MySQL:Got a packet bigger than ‘max_allowed_packet’ bytes

Issue :
While trying to restore a database through phpmyadmin, following error occurs:
#1153 – Got a packet bigger than ‘max_allowed_packet’ bytes
Solution :
This error occurs if the packet size is more than the default value OR the value defined in the my.cnf file.
my.cnf might be blank on a cPanel server by default. You can [...]

Database backup generates an empty file

Issue :
After downloading a database backup through cPanel (Download a MySQL database back-up) , the tar file generated is empty!
Solution :
This usually happens when you are logged in to cPanel with the root/reseller password. Log in with the user password and then try to take a back-up. That should work.

Disabling remote access to MySQL databases

Issue :
How to prevent anybody from accessing MySQL databases hosted on the server from any local PC ?
Solution :
This can be achieved in two ways :
1. By blocking the port 3306 . You can use iptables in order to do this :
/sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j DROP
[...]

No space left on device

Issue :
Following error shows up on the websites which run with mysql.
Warning: session_write_close() [function.session-write-close]: write failed: No
space left on device (28)
in /usr/local/cpanel/base/3rdparty/phpMyAdmin/index.php on line 42
Restarting MySQL fixes the issue temporarily.
Fix :
This usually occurs when /tmp runs out of space. You can check the space by using the [...]

Logging in to PHPMyAdmin directly

Issue :
How to login to PHPMyAdmin directly (without logging in to the cPanel interface) ?
Solution :
This can be achieved by the following steps :
First, download and untar the latest version in to your public_html folder.
wget http://downloads.sourceforge.net/project/phpmyadmin/phpMyAdmin/2.11.9.5/phpMyAdmin-2.11.9.5-english.tar.gz
tar -zxvf phpMyAdmin-2.11.9.5-english.tar.gz
cd phpMyAdmin-2.11.9.5-english
mv phpMyAdmin-2.11.9.5-english phpMyAdmin
Then, create a file named config.inc.php and put the following entries in it [...]

Automating check and repair on MySQL Databases

Issue:
Cron job required for scheduling automatic checks and repairs on MySQL Databases.
Solution :
Use the following cron :

0 1 * * * /usr/bin/mysqlcheck -Aao –auto-repair -u -p’
‘
Please note that this will show your MySQL password in the process list. You can get around this by writing the password to ~root/.db_shadow, chowned root, [...]

Error #1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

Issue :
While trying to import a database via PhpMyAdmin , following error occurred :
#1153 – Got a packet bigger than ‘max_allowed_packet’ bytes
Fix:
Get into the file /etc/my.cnf
Change the following to the required value :
================
max_allowed_packet = 16M
================
This should resolve the issue.

Changing MySQL user password via shell

Issue :
To list the mysql users and change the password for a particular user via shell.
Solution :
You need to be logged in as ‘root’ in order to do this.
To list the users:
# mysql -u root -p
mysql> SELECT User from mysql.user;
To change the password :
# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@’hostname’ [...]

ERROR 2002 (HY000): Can’t connect to local MySQL server

Issue :
After upgrading from MySQL4.1 to MySQL5 on a cPanel server, MySQL is failing to start with the error:
ERROR 2002 (HY000): Can’t connect to local MyS QL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
Fix :
To fix this, run the following command:
mv /etc/my.cnf /etc/my.cnf.OLD
Then force update MySQL :
/scripts/mysqlup –force

Tutorial on clearing Disk Space on a Server

Running out of disk space is a very common issue.  Be it a web-hosting service provider or the end-user.  I wanted to share a few tips that the technical support staff  at InstaCarma use to resolve disk-space issues on a cPanel server. As we all know, there are various partitions on a server which are [...]

PhpMyadmin errror “#2002

Issue :
The phpMyadmin errror “#2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured)”
Fix :
1.First check whether mysql is working in the server or not.
2. Check whether there is a symbolic link from mysql.sock to /tmp. If not, create a symlink between /var/lib/mysql/mysql.sock and /tmp/mysql.sock.
~~~~~~~~~
ln -s [...]

Mysql Version Mismatch in cPanel and Backend(Shell)

Issue:
cPanel says that the version of MySQL is 4.1.20. However, when I check the version within Shell it tells me I’m running MySQL-5.0.
Why is cPanel not reporting the correct version?
Fix:
Steps:
* just remove the file at /home/username/.cpanel/datastore/_usr_sbin_mysqld_–version
* Then log back into your cPanel.
It will then display the correct version.

How to find the MySQL slow queries?

MySQL can log the queries which are taking longer than X seconds .
This feauture is disabled by default.
If you want to enable this option, proceed with the following  steps:-
1) Login to the server via ssh.
2) Open the file /etc/my.cnf and put the following entries:-
[root@localhost ~]#vi /etc/my.cnf
Locate the [mysqld] section and add the following entries:-
===================
log_slow_queries=/var/log/mysql_slow_queries.log
long_query_time = [...]