Annotated edit history of
MySQLNotes version 9, including all changes.
View license author blame.
| Rev |
Author |
# |
Line |
| 9 |
AristotlePagaltzis |
1 |
!!! If you're stuck using [MySQL] for whatever reason, then beware: |
| 7 |
PhilMurray |
2 |
|
| |
|
3 |
[http://sql-info.de/mysql/gotchas.html] |
| |
|
4 |
|
| 9 |
AristotlePagaltzis |
5 |
!!! Resetting a forgotten [MySQL] root password |
| 5 |
AristotlePagaltzis |
6 |
|
| 9 |
AristotlePagaltzis |
7 |
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: |
| 5 |
AristotlePagaltzis |
8 |
|
| 9 |
AristotlePagaltzis |
9 |
<pre> |
| |
|
10 |
__#__ kill `cat /path/to/mysql/datadirectory/''hostname''.pid` |
| |
|
11 |
</pre> |
| 5 |
AristotlePagaltzis |
12 |
|
| 9 |
AristotlePagaltzis |
13 |
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 |
| 5 |
AristotlePagaltzis |
14 |
|
| 9 |
AristotlePagaltzis |
15 |
<pre> |
| |
|
16 |
__$__ mysqladmin -u root password 'mynewpassword' |
| |
|
17 |
__$__ mysqladmin -h hostname flush-privileges |
| |
|
18 |
</pre> |
| 5 |
AristotlePagaltzis |
19 |
|
| 9 |
AristotlePagaltzis |
20 |
or using the <tt>mysql</tt> client: |
| 5 |
AristotlePagaltzis |
21 |
|
| 9 |
AristotlePagaltzis |
22 |
<pre> |
| |
|
23 |
__$__ mysql -u root mysql |
| |
|
24 |
__mysql>__ UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root' ; |
| |
|
25 |
__mysql>__ FLUSH PRIVILEGES ; |
| |
|
26 |
</pre> |
| 5 |
AristotlePagaltzis |
27 |
|
| |
|
28 |
You should now be able to connect using the new password. __''Test this!''__ |
| |
|
29 |
|
| 9 |
AristotlePagaltzis |
30 |
One final step remains: stop <tt>mysqld</tt> and restart it normally. |
| 6 |
AristotlePagaltzis |
31 |
|
| |
|
32 |
See also: [MySQL Manual: A.4.2 How to reset the Root Password | http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html] |
| 5 |
AristotlePagaltzis |
33 |
|
| 9 |
AristotlePagaltzis |
34 |
!!! Clearing unwanted binary log files |
| 5 |
AristotlePagaltzis |
35 |
|
| 9 |
AristotlePagaltzis |
36 |
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> |
| 5 |
AristotlePagaltzis |
37 |
|
| 9 |
AristotlePagaltzis |
38 |
!!! [PHP]'s [mysql_error() | http://php.net/mysql-error] returns <tt>Got error 28 from table handler</tt> |
| 5 |
AristotlePagaltzis |
39 |
|
| 9 |
AristotlePagaltzis |
40 |
Your disk runneth over. Probably <tt>/tmp</tt>. Go clear it out. |
| 5 |
AristotlePagaltzis |
41 |
|
| 9 |
AristotlePagaltzis |
42 |
!!! <tt>LOAD DATA INFILE</tt> makes <tt>SELECT</tt> return garbled crap |
| 5 |
AristotlePagaltzis |
43 |
|
| 9 |
AristotlePagaltzis |
44 |
The file you are importing from might have [DOS] line endings. Run dos2unix(1) or similar over it and try the import again. |
