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!

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 »