March 10, 2012

Utak-Atik Root-nya Mysql

Ceritanya saya habis install ulang netbook Turquoise, upgrade arsitektur ke X86_64. Selesai install ulang MySQL, saya shutdown MySQL-nya dan me-replace datanya dengan backupan sebelum install ulang, maka user debian-sys-maint ga bisa masuk MySQL karena user ini passwordnya auto-generated dan beda dengan yang sebelumnya.

root@turquoise:/tmp# ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
File konfigurasi debian.cf sudah direplace menggunakan backupan yang sebelumnya, juga ngga berhasil. #facepalm

Saya coba untuk masuk lewat CLI mysql denga user root. Begitu masuk saya coba ubah passwordnya:
update user set password=PASSWORD("passwordanyaran") where user='debian-sys-maint';
Trus dicoba… ga bisa. Lalu saya experimen mengubah password user root:
mysql> update user set Password=PASSWORD("passwodnyaroot") where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
Curiga… lha kok 4 rows affected? *menegakkanposisiduduk
mysql> select User,Password from user;
+------------------+-------------------------------------------+
| User             | Password                                  |
+------------------+-------------------------------------------+
| root             | *5CA658046F0DE55DB766A3770F50180522BADAC9 |
| root             | *5CA658046F0DE55DB766A3770F50180522BADAC9 |
| root             | *5CA658046F0DE55DB766A3770F50180522BADAC9 |
| root             | *5CA658046F0DE55DB766A3770F50180522BADAC9 |
|                  |                                           |
|                  |                                           |
| debian-sys-maint | *08CA1B91AFBA2ABB0232E3E4261B786D5E878FA8 |
| redmine          | *017E3D0B95613E831A338035E5D8E66D7C8092C8 |
+------------------+-------------------------------------------+

8 rows in set (0.00 sec)
Iya ada 4 user root-nya…
Hmmm…
mysql> delete from user where User='root'
    -> ;
Query OK, 4 rows affected (0.00 sec)
Aaaaaaargh… Whad hav I done…???
mysql> select User,Password from user;
+------------------+-------------------------------------------+
| User             | Password                                  |
+------------------+-------------------------------------------+
|                  |                                           |
|                  |                                           |
| debian-sys-maint | *08CA1B91AFBA2ABB0232E3E4261B786D5E878FA8 |
| redmine          | *017E3D0B95613E831A338035E5D8E66D7C8092C8 |
+------------------+-------------------------------------------+

4 rows in set (0.00 sec)
Ilang… (O__O;)

Bersambung di bagian #2...

BAGIAN #2

Ternyata nemu caranya dari internet. MySQL itu bisa d start di mode safemode dgn menanggalkan GRANT table-nya, jadi bisa masuk tanpa pengecekan privilege.
root@turquoise:/home/ardhyw# /usr/bin/mysqld_safe --skip-grant-tables
120310 15:09:11 mysqld_safe Logging to syslog.
120310 15:09:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
...
OK, bisa masuk tanpa password dan segera saya buat user root yang baru…
root@turquoise:/var/lib# mysql -u debian-sys-maint
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.20-1~dotdeb.0 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create user 'root'@'localhost' identified by 'passwodterbaroe';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
ERROR??? (O___O;)

Googling lagi, ini error kenapa ya??

Akhirnya nemu dari stackoverflow.com, mereka bilang ini memang bug MySQL, jadi jalan keluarnya adalah dengan menganggap si user tadi masih ada lalu DROP user-nya pake perintah biasa, flush privileges lalu buat lagi dari awal. OK, coba deh…
mysql> drop user 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'root'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'root'@'%'

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'root'@'localhost' identified by 'passwodterbaroe';
Query OK, 0 rows affected (0.00 sec)

ysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
OK, restart ulang MySQL servicenya…
root@turquoise:/home/ardhyw# service mysql start
Starting MySQL database server: mysqld ..
Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..
root@turquoise:/home/ardhyw# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.20-1~dotdeb.0 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> \(>0<)/ yeayeayea...