NiyasHussain.com

For increasing mysql packet size add the following entry in my.cnf and restart mysql server.

[mysqld]
max_allowed_packet=16M

Steps: Overview:

1. -Start MySql
(you will need version 3.23.29 or higher)

2. -Run the SQL query

SHOW VARIABLES LIKE ‘have_innodb’;

3. -This should show

+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_innodb | YES |
+—————+——-+

Usually you will be interested in changing your MySQL collation in order to solve problems with foreign character encodings. The most common case is to change your MySQL collation from latin1 to utf8. This can be done by using phpMyAdmin and following the instructions below:

1. Enter your cPanel and click on the phpMyAdmin icon in the Databases box.

2. Select the database you wish to manage from the drop-down menu on the left

3. Click on the Operations tab in the top menu of your phpMyAdmin

4. At the bottom of the page you will see the collation option. You can now select a collation from the drop down menu and click on the Go button.

Please note that after your change the collation of a database only the new tables will be created with the new collation. All other tables remain with the collation, they were initially created.

mysql> delete from user where user=’username’;
mysql> FLUSH PRIVILEGES;

If you forgot your mysql root password you can get it from
/root/.my.cnf

grant all privileges on *.* to username@<server ip> identified by ’somepassword’;

Stop mysql server

/etc/init.d/mysql.server stop

Start mysql in safe mode

/usr/local/mysql/bin/mysqld_safe –user=root –skip-grant-tables –skip-networking &

NOw the mysql will be running in the background in safe mode. You will be able to klogin as root by just using

mysql -u root

Once you got in you can use the following commands to reset the root password.

UPDATE mysql.user SET Password=PASSWORD(’qwert123′) WHERE User=’root’; //Here password is qwert123

FLUSH PRIVILEGES;

Now just quit from the mysql prompt and try using the new password

mysql -u root -p

When it ask for passwprd, provide the new password. It will work.

Sometimes such behavior of apache/httpd (taking more and more memory until it dies or crashes the server) can be caused by corrupted MySQL database. Try to do the following:

1) Kill the mysql server
/etc/rc.d/init.d/mysql stop

2) Repair all SQL databases:
myisamchk -r /var/lib/mysql/*/*.MYI

3) Start mysql again:
/etc/rc.d/init.d/mysql start

Please put the following code in a php file in the server and access it through a browser to view the result.

Change the username of database user from “root” to your “database-user”, followed by the password of the database user from “testuser” to your “database-access-password”.

If the connection was successful the page will display Success. Other wise it will display Could not select DB.

Code:

<?php
$con=mysql_connect(”localhost”,”root”,”testuser”) or die(”Could not connect”);
$db=mysql_select_db(”test”,$con) or die(”Could not select DB”);
echo “Success”;
?>

If you have lost your mysql root password and you are unable to login to mysql, then as a last resort you can relogin by performing the following steps.

step1
——

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname of the .pid file in the following command:

Stop mysql service on the server. Do it in this way

shell> kill `cat /mysql-data-directory/host_name.pid` //note that ` is not single quotes it a back tab found in the top row of key
board (number row)

step2
——

Create a text file and place the following command within it on a single line:

SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(’123456′);

Save the file with any name. For this example the file will be mysql-init.

Step 3
——–

#

Restart the MySQL server with the special –init-file=~/mysql-init option:

shell> mysqld_safe –init-file=~/mysql-init &

The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init.

Step 4
——–

You should be able to connect using the new password.

shell> mysql -u root -p

Then it will ask for password and give the password 123456

Login and reset the mysql password. Dont forget to delete the mysql-init file that we have created before restarting mysql.