Tuesday, January 06, 2009

Allowing external root access to your MySQL database

...is recommended against in the MySQL documentation, but it can be oh so handy for running scripts and adding databases, tables and views when you can't (or don't want to) log into that remote system on the command line to update it. However, when you try it (at least on a fresh Ubuntu install), you may be greeted with an Error 2003. This happens when your system does not allow root external access. To enable external access, you can run this query (as root) on your server from the command line (must be localhost) :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ('myrootpassword');


* EDIT * : The above statement does not necessarily copy all privileges over to the new entry for 'root'@'%' in the mysql.user table. A better way to accomplish full external root access is the following :


DELETE FROM `mysql`.`user` WHERE User = 'root' AND Host = '%';
INSERT INTO `mysql`.`user` (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) (SELECT '%', User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections FROM `mysql`.`user` WHERE User = 'root' AND Host = 'localhost');
FLUSH PRIVILEGES;

No comments: