View Source: MySQLNotes - Waikato Linux Users Group

Edit PageHistory Diff Info LikePages

!!! If you're stuck using [MySQL] for whatever reason, 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 <tt>-9</tt>!!). The [PID] is found in the <tt>.pid</tt> file normally found in the [MySQL] database directory:

<pre>
__#__ kill `cat /path/to/mysql/datadirectory/''hostname''.pid`
</pre>

Now you can restart <tt>mysqld</tt> with the <tt>--skip-grant-tables</tt> option, in order to able able set a new password. There are two alternative ways to set it: either the <tt>mysqladmin</tt> tool, by way of

<pre>
__$__ mysqladmin -u root password 'mynewpassword'
__$__ mysqladmin -h hostname flush-privileges
</pre>

or using the <tt>mysql</tt> client:

<pre>
__$__ mysql -u root mysql
__mysql>__ UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root' ;
__mysql>__ FLUSH PRIVILEGES ;
</pre>

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

One final step remains: stop <tt>mysqld</tt> and restart it normally.

See also: [MySQL Manual: A.4.2 How to reset the Root Password | http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html]

!!! Clearing unwanted binary log files

In some configurations, [MySQL] can log all database activity to a binary log file (usually in <tt>/var/lib/mysql</tt>). 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 you no longer want. They suggest removing files that are older than 3 days. To remove all binary log files issue <tt>RESET MASTER;</tt>

!!! [PHP]'s [mysql_error() | http://php.net/mysql-error] returns <tt>Got error 28 from table handler</tt>

Your disk runneth over. Probably <tt>/tmp</tt>. Go clear it out.

!!! <tt>LOAD DATA INFILE</tt> makes <tt>SELECT</tt> return garbled crap

The file you are importing from might have [DOS] line endings. Run dos2unix(1) or similar over it and try the import again.

One page links to MySQLNotes:

UserSubmittedNotes

Last edited on Wednesday, January 12, 2005 1:49:38 pm by AristotlePagaltzis

Edit PageHistory Diff Info LikePages