MySQL

From Consultancy.EdVoncken.NET

Jump to: navigation, search

MySQL is a popular Open Source database, mostly used in web environments.

Overview

Relevant files and directories on Linux:

/etc/my.cnf: Configuration file for mysqld startup, parsed by /etc/init.d/mysqld
/var/lib/mysql: Default location for databases
/ver/log/mysqld.log: Default error-log

Additional information:

Postinstall actions

Initial actions on the database

Before handing over a MySQL database server to the client make sure that the default settings are altered since these are fairly insecure.

On unix MySQL will listen both to a socket file and a network interface. Unless needed the network interface should be disabled since it is often not needed and posses a security risk.

To do this login to the database

 # mysql -u root
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 6635 to server version: 5.0.27
 
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
 mysql>

Then remove the test database

 mysql> drop database test;
 Query OK, 0 rows affected (0.00 sec)

Remove all users except root on localhost

 mysql> use mysql
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 mysql> delete from user where not (host="localhost" and user="root");
 Query OK, 3 rows affected (0.01 sec)

Change the database user from root to mysqldba (or anything else).

 mysql> update user set user="mysqldba" where user="root";
 Query OK, 1 row affected (0.01 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 
 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)

NOTE the flush is required

 mysql> SET PASSWORD FOR 'mysqldba'@'localhost' = PASSWORD('S3cr3t!');
 Query OK, 0 rows affected (0.00 sec)

Backup

MySQL has multiple storage engines, one of these is InnoDB unlike MyISAM (which is the default) it stores all data in a single (binary) file. This means that on an active database you run a high risk that the file is in flux during backup. The solutions to this is failry simple: Dump the database localy and backup the dump.

Example dump script

 #!/bin/bash
 #
 #
 export PATH=/usr/bin:$PATH
 TS_START=`date +%s`
 BACKUPDIR="/var/backup"
 FILETS=$(date +%d%B%Y_%H%M)
 
 # Set the database name
 DATABASE="databasename"
 
 # Set the output file
 OUTFILE="${BACKUPDIR}/${DATABASE}_${FILETS}.sql"
 
 # Make the actual dump
 mysqldump --add-drop-database --password=dbpasswd --quick \
 --quote-names --user=dbuser --result-file=$OUTFILE $DATABASE
 
 # compress the dump (tends to be a bit bloated)
 /bin/gzip $OUTFILE
 
 TS_END=`date +%s`
 
 echo "$TS_START $TS_END" | awk '{ printf "backup took %d seconds", ( $2 - $1 ) }' | logger

Stick this in e.g. '/etc/cron.daily/backupdb.sh' with permissions 500. Then a daily dump of the database is made in '/var/backup'. This can then be used either to restore a database or to write a decent backup to tape.

A thing often overlooked is the permissions within MySQL also called GRANTS. To dump this in a usable way you can use mysqldumpall, described below.

MySQL Management and Monitoring Tools

phpMyAdmin

phpMyAdmin

myTop

mytop

mytop requires the Perl module Term::Readkey. This is installed from CPAN, the Comprehensive Perl Archive Network:

perl -MCPAN -e shell
...
cpan> install Term::ReadKey
cpan> quit

mysqldumpall

Solid local backups of you MySQL databases http://www.biomechs.nl/scripts/mysqldumpall (docu)

Common Issues

Temporary directory too small for MySQL

The systemwide TMP-directories are max. 2GB in size. MySQL uses the TMP-directory for table copies, for example during "optimize". This implies that the maximum size would be limited to around 2GB, which is not acceptable.

Solution: I've created a TMP-directory specifically for MySQL, and added a setting in /etc/my.cnf to make these change take effect:

 [root@nlvud140 /]# ls -ld /var/lib/mysql-tmp/
 drwxr-x---  2 mysql mysql 4096 Jan 24 11:39 /var/lib/mysql-tmp/

 [root@nlvud140 /]# cat /etc/my.cnf
 [mysqld]
 datadir=/var/lib/mysql
 tmpdir=/var/lib/mysql-tmp
 socket=/var/lib/mysql/mysql.sock
 # Default to using old password format for compatibility with mysql 3.x
 # clients (those using the mysqlclient10 compatibility package).
 old_passwords=1
 
 [mysql.server]
 user=mysql
 basedir=/var/lib
 
 [mysqld_safe]
 err-log=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

Host is not allowed to connect to this MySQL server

When trying to connect to the database server remotely, you may encounter the following error:

 [root@nlvud141 ~]# mysql -h mysql.gdc1.ce.philips.com
 ERROR 1130 (00000): #HY000Host 'nlvud141.gdc1.ce.philips.com' is not allowed to connect to this MySQL server

This error is confusing at first; we're not even at the user/password stage, as was verified using telnet:

 [root@nlvud141 ~]# telnet mysql 3306
 Trying 130.144.23.140...
 Connected to nlvud140.gdc1.ce.philips.com (130.144.23.140).
 Escape character is '^]'.
 [#HY000Host 'nlvud141.gdc1.ce.philips.com' is not allowed to connect to this MySQL server
 Connection closed by foreign host.

It turns out that MySQL checks if there is any user with sufficient privileges to access MySQL from that remote host, even before presenting the user/password dialog.

Solution: create a dummy user named "remote-access", with no privileges at all. I set a generated password on this account, to prevent anonymous logins.

Navigation