How to change file upload size in phpmyadmin!

For lesser mortals in the universe of databases, who still inadvertently use lot of MySql, a tool like PHPMyadmin is the holy grail!

This web browser based, php querier accesses MySql tables and also gives tremendous functionalities to tinker with the databases. While most of it is intuitive, sometimes a small feature requirement needs the options beyond the defaults.

Before I delve into these non-default configurations, just for the record, here is how you get your phpmyadmin to access your mysql tables on your Debian based machines.

fsmk@gnu:~$ sudo apt-get install phpmyadmin

Once, you have installed, as a dependency Apache also gets installed. To access phpmyadmin, open your favourite web browser and type on "http://localhost/phpmyadmin"

Now, that settled let me get into the issue of the post.
SQL backup, taken from different servers or simply for the purpose of backing up data are the most precious files on my computer, at least. Now, to move in and out of different machines with the mysql data, phpmyadmin offers a cool import/export option.

While export is flawless, import has a default option which restricts the maximum file upload size to 2M. Once you encounter sql's which are more than 2M you know you are handling quite a lot of data, and in such cases how to restore these >2M files?

The configuration file for the phpmyadmin which checks this option is a php.ini file, which resides in the /etc/php5/apache2/ directory.

Get hold of this file, and search for "upload_max_filesize" in the php.ini file. After you've got it change it to whatever that your maximum file size might be.
Restart your apache service to upload those massive sqls into your database.

Resetting mysql root password

As I had mentioned in my previous post, I was composing a post about installing and configuring Cacti. It so happened that I had forgotten my mysql root password on my system. And, as mysql is an essential back-end application for Cacti to run, I had to retrieve it. But, even after multiple attempts I didn't seem to be able to recollect it!

So, I had to reset the root password. After reading a couple of forums I was able to do it. As it seemed non-trivial, I decided to put it up here.

Follow these steps to reset the mysql root password in a GNU/Linux machine.

1. Log into the GNU/Linux machine where the mysql is running.

2. Stop the mysql daemon

raghu@fossphosis:~$ sudo service mysql stop 
mysql stop/waiting

3. After stopping the mysql daemon, create a text file with the following content

UPDATE mysql.user SET Password=PASSWORD('$new_password') WHERE User='root';
FLUSH PRIVILEGES;

The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

4. Save this text file which now has the new password, call it maybe mysql-reset.

5. Start mysql with --init-file option:

raghu@fossphosis:$ sudo mysqld_safe --init-file=/home/raghu/mysql-reset &

By doing this we are getting the mysql server to execute the contents of the mysql-reset text file, wherein the root passwords will be reset and assigned with the new value specified in the text file i.e., $new_password.

6. After the server starts successfully, delete the mysql-reset text file, for it has the new password in plain text.

7. You should be now able to log in to mysql as root with the new password.

raghu@fossphosis:~$ sudo mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 82
Server version: 5.1.41-3ubuntu12.6 (Ubuntu)


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 


This is a very helpful procedure for someone like me, who keeps forgetting the paswwords ;-)