Tuesday, January 06, 2009

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).

No comments: