Penguin
Note: You are viewing an old revision of this page. View the current version.

If you're stuck, for whatever reason, using MySQL then beware:

http://sql-info.de/mysql/gotchas.html

Resetting a forgotten MySQL root password

You first need to make MySQL forget the current credentials. This can only be done by the SuperUser or the user mysqld runs as, because the daemon must be stopped, which requires sending it a kill(1) signal (don't use -9!!). The PID is found in the .pid file normally found in the MySQL database directory
# kill `cat /path/to/mysql/datadirectory/hostname.pid`

Now you can restart mysqld with the --skip-grant-tables option so that you can set a new password. There are two alternative ways to do so: either the mysqladmin tool, by way of

$ mysqladmin -u root password 'mynewpassword' $ mysqladmin -h hostname flush-privileges

or using the mysql client
$ mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root' ; mysql> FLUSH PRIVILEGES ;

You should now be able to connect using the new password. Test this!

One final step remains: stop mysqld and restart it normally.

See also: MySQL Manual: A.4.2 How to reset the Root Password

Clearing Unwanted Binary Log Files

mysql in some configurations can log all database activity to a binary log file (usually in /var/lib/mysql). If you have a busy site this can grow quite large. The recommended management solution in the mysql manual is to remove binary log files that you no longer want. They suggest removing files that are older than 3 days. To remove all binary log files you can use the following command

RESET MASTER ;

PHP says the table handler is returning error 28

If the mysql_error() PHP function returns

Got error 28 from table handler

this means your disk runneth over. Probably /tmp. Go clear it out.

LOAD DATA INFILE makes SELECT return garbled crap

The file you are importing from probably has dos line endings. Run dos2unix or similar over it and try the import again