While MySQL 5.5 is available as a standard Ubuntu package, the default configuration needs some tweeks to be usable. These are my notes from a recent install.
The default is user root with no password but who can only login from the local machine.
Run mysql -u root and enter the following commands:
GRANT ALL ON *.* TO 'root'@'%' identified by 'rootpswd' WITH GRANT OPTION;
The first entry won't be used when we're done, but it is good to put a password on it anyway. To check that it worked, enter
use mysql;
select host, user from user;
You should see the new entry with host = %. Enter quit to exit.
The default is to allow only access from the local machine, and to use very little memory. Edit the file /etc/mysql/my.cnf.
To enable networking, comment out (by putting a # at the beginning of the line):
bind-address = 127.0.0.1
Some simple performance tweeks (key_buffer is already in the file, the other lines are new):
log_slow_queries
key_buffer = 256M
innodb_buffer_pool_size = 512M
I am partial to UTF-8 for everything, and always use InnoDB:
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-storage-engine=InnoDB
innodb_file_per_table
I always disable DNS, since my clients are all behind a firewall and don't resolve:
skip-name-resolve
Be sure to restart the database to have the changes take effect:
service mysql restart
I use mysqldump to create a backup. If the script is small enough (less than 64K), you can just paste the
script into Query Browser, but otherwise you have to use the command line mysql tool. Assuming you are on
the old server, start it with:
mysql -h newmysql.example.com -u root -p
Then restore each database:
create database xxx;
use xxx;
SOURCE backup_xxx.sql