PRESOON.COM

How to change Mysql database directory to another partition in cPanel. /var patition full

Hi guys,

you might have faced the issue of /var partition gettting full regularly due to database directory. If you have a larger partition with free space, then it is possible to move the database directory to the larger partition.

Here are the steps

Switch off the database server while we are moving the databases.

/etc/rc.d/init.d/mysql stop

I am considering that I have enough space in /home partition. Here goes my new database data directory as /home/mysql

Now it is better to copy the database first, rather than move.

cp -pr /var/lib/mysql /home
mv /var/lib/mysql /var/lib/mysql-bk

We are copying the database to the new location since it is better to revert back the settings with minimum downtime, if anything goes wrong.

move to /tmp

cd /tmp
unlink mysql.sock
ln -s /home/mysql/mysql.sock /tmp/mysql.sock

Take a backup of /etc/my.cnf

Now edit /etc/my.cnf

vi /etc/my.cnf

add the line
datadir=/home/mysql

If the socket file is specified, comment it out.

Now move to /var/lib/mysql and create a symblink

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

(Please note that you don’t specify the socket file location in my.cnf since it causes issues with phpMyadmin)

For, cPanel server, edit the phpMyadmin configuration
take a backup of “/usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php”

edit this file /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

add the following lines. If they already exist, edit as below.
(the connect_type usually exist at “tcp” change it to “socket”)

$cfg['Servers'][$i]['socket'] = ‘/home/mysql/mysql.sock’;
$cfg['Servers'][$i]['connect_type'] = ’socket’;

Now start the database server.

/etc/rc.d/ini.d/mysql start

If it starts fine, you are done. Check the database connections of your site.
You can now remove the directory /var/lib/mysql-bk

Suggestions, questions are welcome.

PhpMyAdmin Installation

PhpMyAdmin Installation for a domain is so easy.

Enter into your webdirectory (public_html)

Download the source into it.

From shell

wget http://jaist.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-3.1.1-english.tar.gz

or download the source from http://jaist.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-3.1.1-english.tar.gz and upload it via FTP to your public_html

extract the contents

tar -zxf phpMyAdmin-3.1.1-english.tar.gz

Alternatively you may use the filemanger extract option for the purpose.

you can now access your phpmyadmin using the link

www.yourdomain.com/phpMyAdmin-3.1.1-english/

Before that you need to configure that.

You need to have

1 database

2 database user

3 database user password.

Now navigate to the directory libraries under /phpMyAdmin-3.1.1-english/ you will have a file named config.default.php.

You need to edit this file

===================
$cfg['Servers'][$i]['host'] = ‘1002-2.lowesthosting.com’; // MySQL hostname or IP address
$cfg['Servers'][$i]['port'] = ‘4455′; // MySQL port – leave blank for default port
$cfg['Servers'][$i]['socket'] = ‘/tmp/mysqlt.sock’; // Path to the socket – leave blank for default socket
$cfg['Servers'][$i]['connect_type'] = ’socket’; // How to connect to MySQL server (‘tcp’ or ’socket’)
$cfg['Servers'][$i]['extension'] = ‘mysql’; // The php MySQL extension to use (‘mysql’ or ‘mysqli’)
$cfg['Servers'][$i]['compress'] = FALSE; // Use compressed protocol for the MySQL connection
// (requires PHP >= 4.3.0)
$cfg['Servers'][$i]['controluser'] = ‘username’; // MySQL control user settings
// (this user must have read-only
$cfg['Servers'][$i]['controlpass'] = ‘password’; // access to the “mysql/user”
// and “mysql/db” tables).
// The controluser is also
// used for all relational
// features (pmadb)
$cfg['Servers'][$i]['auth_type'] = ‘http’; // Authentication method (config, http or cookie based)?
$cfg['Servers'][$i]['user'] = ‘username’; // MySQL user
$cfg['Servers'][$i]['password'] = ‘password’; // MySQL password (only needed
// with ‘config’ auth_type)
$cfg['Servers'][$i]['only_db'] = ‘trial’; // If set to a db-name, only
// this db is displayed in left frame
====================

Update the above with database name, username, password, control user and password which can be the same as db user and password.

Now try accessing the link www.yourdomain.com/phpMyAdmin-3.1.1-english/
enter the login credentials.

Thats all.

MySql >= 4.1 Client does not support authentication protocol…

If you upgrade your MySql server to >= 4.1 you might get the following error:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

This happens because the latest versions of MySql uses a new format for the password (it’s a longer hash). In order for old clients to continue to use the newer server, you have to set the passwords on the server to their old format or upgrade your client. Because upgrading the client can sometimes be a pain, it’s often easier to just update the passwords to the old format on the server.

Run mysql and login as root:
mysql -u root -p

Then, paste the following command, editing as necessary, to change the password of the user to the old format.

UPDATE mysql.user
SET password=OLD_PASSWORD(’somepassword’)
WHERE user=’someuser’
AND host=’somehost’;

After you have set the passwords to the old format, flush the tables.

flush privileges;

Then exit the mysql client with “quit” and you are set.

For windows.

In my.ini file in the mySQL directory (C:\Program Files\MySQL\MySQL Server 4.1\my.ini),
add the following line after [mysqld]

old_passwords

Mysql History Commands typed

Check your Mysql history file

======================
vi /root/.mysql_history
======================

If you don’t find the file under /root, find out the file using

======================
locate .mysql_history
======================