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;

Setting up MySQL server on Ubuntu

...is really easy. Run the command :

apt-get install -y mysql-server

Once that's done, you'll need to perform some additional configuration and setup the root password. My added configuration involves making table name comparisons all lower case (convenient for queries) and making the server run entirely in utf8. In order to configure the server as such, you'll need to make the following changes :

Add the following lines to the [mysqld] section of the my.cnf configuration :

default-character-set=utf8
default-collation=utf8_general_ci
lower_case_table_names=1

Add the following line to the [mysql] section of my.cnf (note that this is not the same as the [mysqld] section, the difference being the 'd' on the end) :

default-character-set=utf8

In order to have the Ubuntu MySQL installation properly run on external hosts, you'll have to comment out the line that looks like :

bind-address     127.0.0.1

After you've configured the my.cnf file, you'll have to stop the server, and restart it with --skip-grant-tables enabled so that you can have free, unfettered access to the system with privileges disabled. This is required so that you can set the initial root password. Run the following commands as root :

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables --skip-networking &

The latter command will start the MySQL server with privileges disabled.
Then log in with the command

mysql -u root -h localhost

Once logged in, run the command :

UPDATE `mysql`.`user` SET Password = PASSWORD('thepassword') WHERE User = 'root';

This will set the user password to 'thepassword' (though I recommend you don't use that exact password). Once you've set the root password, exit mysql and run the command

/etc/init.d/mysql restart

This will restart the server as usual, so that you'll no longer have unlimited privileges (and neither will anyone else).

Setting up an SSH Server on Ubuntu

Ubuntu is usually pretty good about coming pre-configured with everything you need for your system, but one thing I've found that it's lacking (at least the server distribution is, anyway) is a pre-setup SSH server. Fortunately though, it's quite easy to set one up. All you need to do is issue the command:

apt-get install openssh-server

...as root. If you get an error to the effect of :

Package does not exist

...then you'll have to run :

apt-get update

...to update your system's packages first.