Database 101

MySQL installation

As with any package to be installed: use aptitude, update your APT, then make sure all your (other) software is up-to-date. Then find package mysql-server - under Lenny it'll be version 5.0.51a-something. Installing it will also automatically install some dependent packages, like mysql-common, mysql-client etcetera. So after downloading some 40MiB of files, MySQL is installed. Should you consult the official MySQL documentation, you might see mention of the mysql_install_db script - do not worry about that, since Debian has run it for you when it was setting up the database.

To now configure the MySQL server, please take the following steps: Think up a strong password for the MySQL root user; something like waYacUbaT2uW. DON'T use this example password, generate your own one!. Start the MySQL client from the command line:

mysql -u root

Then, from the mysql client prompt, set the password for the MySQL root user. At installation time, two accounts "root" were created, each with an empty password - NOT safe! One account is used to connect to the MySQL server from localhost, one to connect from the IP address of the machine. They can be treated as two different accounts, but we don't recommend that. We thus give both accounts the same password:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('waYacUbaT2uW');
mysql> SET PASSWORD FOR 'root'@'<hostname>' = PASSWORD('waYacUbaT2uW');

Furthermore, there are two anonymous accounts created. These also have no password, and get full permission on the test database, as well as on any database who's name begins with test_. These accounts can be fitted with a password (like we did for root - just use two quotes with nothing inbetween them, instead of two quotes with root inbetween), or they can be removed - which we recommend. The (single) command for this is:

mysql> DROP USER '';

To verify the installation you might want to take some of the following steps:

• run mysqlshow -u root -p, and see if that command outputs at least two databases (information_schema and mysql);
• check out the settings of your MySQL server by running mysqladmin -u root -p variables (piping the output to less or a file, if necessary);
• have a peek in /var/lib/mysql; the database data files should be there;
• check /var/log for two logfiles mysql.err and mysql.log.

Moving the MySQL databases

By default the location of your Debian MySQL databases is /var/lib/mysql/<database>. However, sometimes you wish your databases to be in a different location. E.g. /data/mysql, where /data is a mounted dedicated RAID array. Or perhaps even /data/mysql is its own array.

Whatever the reason, to move all MySQL databases from /var/lib/mysql to another location, you can follow these steps:

• Create the new directory, e.g. /data/mysql
• Make this directory owned by user/group mysql

cd /data
mkdir mysql
chown mysql:mysql mysql

• Shut down the database server with one of the following commands:

mysqladmin -u root -p shutdown
invoke-rc.d mysql stop

• Make a backup copy of /etc/mysql/my.cnf, then edit this file. Find the section [mysqld] and change the line datadir = /var/lib/mysql to

datadir         = /data/mysql

• As root, move (or better yet: copy) all of the content of /var/lib/mysql over to /data/mysql. Make sure you don't accidentally change the ownership or permissions of the files and folders in the /var/lib/mysql folder. We expect each and every file and folder to be owned by mysql:mysql. Copy command would be (as root):

cp -p -r /var/lib/mysql/* /data/mysql

• Check and doublecheck your my.cnf settings and your database file owners, attributes and size. After the next step, there may be no way back!
• Start up your database server:

invoke-rc.d mysql start

• Check the working of your MySQL server:
  • issue the following command to get status information (to see if MySQL is running after your start command):

invoke-rc.d mysql status

• • look under /var/log for the default MySQL log files mysql.log and mysql.err
  • check your SysLog (standard: /var/log/syslog) for MySQL error messages, since the default Debian configuration is to log MySQL errors there, rather than in the previously mentioned MySQL logfiles.