To err is human. Humans forget things, MySQL root password is one of those things. However, its not very difficult to reset the root password if you have root access to the machine.

The tutorial does seem a bit long because of all the alternate ways to kill and start the mysqld, but trust me its simple.

Here’s in short what we will be going to do:

• Step 1: Stop mysql server process
• Step 2: Start mysqld with --skip-grant-tables option.
• Step 3: Run mysql without any parameters.
• Step 4: Change the root admin password (old password not required.)
• Step 5: Close mysql and restart mysql server.

And here are the detailed instructions to reset MySQL root password: Read the rest of this entry »

My host recently started allowing remote connections to its mysql database. It was a really useful feature – I could not change database settings from my own computer. Since it started allowing remote connections from my IP, I could use any mysql client to connect to it. I fired up terminal and tried to connect using the command:

mysql --host=69.89.31.242 --user=username --password=password

It connected. This was just to test the connection. I wanted to connect my phpmyadmin with this remote host. For that I edited /etc/phpmyadmin/config-db.php file and changed:

$dbserver=”;

to

$dbserver=’69.89.31.242′;

I loaded the url http://localhost/phpmyadmin, entered the database username and password and I got in. I could administer my database from my local phpmyadmin installation.

Cheers!

One of my clients got this error in his wordpress:

Error establishing a database connection

It is one of those vague errors which give very little information about what caused it. On going to /wp-admin, it gave error:

One or more database tables are unavailable. The database may need to be repaired.

I added “define(‘WP_ALLOW_REPAIR’, true);” in wp-config.php and clicked on ‘repair’, it showed that all tables have been repaired but I was still getting the error about wordpress not being able to connect with database. It was actually able to connect but there was some other problem. I checked if any table was missing from the database, but it was not.

Finally after 2 hours of searching for a solution, I found it. In wp-options table, just make sure the option_value for the option_name ‘siteurl’ is your site’s url. You can also do this by executing the following sql statement (you can use phpmyadmin):
Read the rest of this entry »

Yesterday I got the error:

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

when I ran the command mysql through terminal in Ubuntu 10.10

This error can also be linked to error in phpmyadmin:

Connection for controluser as defined in your configuration failed.

I figured out it was due to mysqld not running. I simply did

sudo /etc/init.d/mysql start

to start mysql daemon. This solved the error.

To backup all or some of your MySQL databases, you’ll need mysqldump which comes bundled with mysql. If you have MySQL installed, you probably have mysqldump installed already.

To backup all databases use the following command:

In linux:

mysqldump -uroot -ppassword --all-databases | gzip > /media/disk-2/db.sql.gz

In Windows:

mysqldump -uroot -ppassword --all-databases > db.sql

This will backup all your databases from a single command.
Read the rest of this entry »

I had been wondering this for few days. I wanted a solution which could comvert first letter of text in some fields to uppercase and the rest to lower. I got time today and experimented a bit and found a very simple solution.
Read the rest of this entry »

Many a times we require the next Auto Increment value from a MySQL table. Most of the people would do something like this:

$query = "Select MAX(id) from users";
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);
$maxid = $row['MAX(id)'];
echo $maxid;

This would give correct results but if we delete the latest row, it will start to give wrong results.

We’ve got another query which can be used to get correct ‘Auto Increment’ values from the table.
Read the rest of this entry »

Seriously guys, I got this error and I was laughing hard. The error is just too funny, don’t you think? Yes, it is but not for developers like me. Problem to this error is almost undetectable and there are more than a dozen reasons as to why this occurs and this often baffles developers.

This error generally comes when :

MySQL connection times out.

The MySQL server shuts down.

The connection is interrupted.

The query you are performing is too large for server to handle.

The query you are performing takes more space than allowed (default: 16MB).

While these are some common reasons, there maybe some unknown reasons to come across this error. I faced one of those unknown reasons. Read the rest of this entry »