Dumping a mysql database excluding one or several tables

If we need to dump a MySQL database and want to exclude table(s) we should use the option:

--ignore-table=db_name.tbl_name

Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option also can be used to ignore views. Citation.

Example:

mysqldump --ignore-table=cars.brands cars > cars.dump

References

How to install MySQL Server on Debian Linux

While installing MySQL Server it is always good to keep in mind that the logs and data folders will potentially have a big size. By default MySQL keeps them in the root mount point (i.e. ‘/’). That may cause your database server system disk to get full, which is never a good idea.

This article describes how to move these two folders to ‘/home’ which is ideally mounted into another disk and has enough space to keep your database data and logs.

First, I install the required apt-get packages as follows:

apt-get update
apt-get install mysql-server

To check the status:

/etc/init.d/mysql status

/usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.51a-24
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 sec

Threads: 1  Questions: 78  Slow queries: 0  Opens: 23  Flush tables: 1
Open tables: 17  Queries per second avg: 26.000.

Now, stop MySQL, move the folders to the right location, reconfigure MySQL and start again:

# Stop MySQL
/etc/init.d/mysql stop

# Move and reconfigure data
mkdir /home/mysql
mv /var/lib/mysql /home/mysql/mysql-data
ln -s /home/mysql/mysql-data/ /var/lib/mysql

# Move and reconfigure logs
mv /var/log/mysql/ /home/mysql/mysql-logs
ln -s /home/mysql/mysql-logs/ /var/log/mysql

# Start MySQL and check that everything is OK
/etc/init.d/mysql start
/etc/init.d/mysql status
/usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.51a-24
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 14 sec

Threads: 1  Questions: 78  Slow queries: 0  Opens: 23  Flush tables: 1
Open tables: 17  Queries per second avg: 5.571.

These are some settings that I usually put on the /etc/mysql/my.cnf configuration file:

# Here you can see queries with especially long duration
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 1
log-queries-not-using-indexes

# A server-id unique
server-id                = 177
log-bin                  = /var/log/mysql/mysql-bin.log
log-bin-index            = /var/log/mysql/mysql-bin.log
innodb_file_per_table
# Unique log names (this prevents replication breaking upon hostname change :-) 
relay-log                = iamalsounique98127-relay-bin
relay-log-index          = iamalsounique98127-relay-bin

# Taking care of the auto-increment values (for multi-master replication)
auto_increment_increment      = 10
auto_increment_offset         = 1

For these changes to take effect, you would need to restart MySQL:

/etc/init.d/mysql restart

If you want to ignore databases or tables you may use the following options:

binlog_ignore_db        = information_schema
replicate_ignore_db     = information_schema
binlog_ignore_db        = mysql
replicate_ignore_db     = mysql

# Ignore all the cache* tables which have caused DUPLICATE
# ENTRY issues. Unai.
replicate_wild_ignore_table = exampledb.cache%

Having ‘binlog_ignore_db’ is enough to exclude databases from replication BUT having ‘replicate_ignore_db’ as well will make things clearer since the databases that are being ignored will appear in both the ‘SHOW SLAVE STATUSG’ and ‘SHOW MASTER STATUSG’.

Having HAProxy check mysql status through a xinetd script

HAProxy is able to load balance MySQL wonderfully. The main issue is how to make sure that the backend MySQL server to forward the request to is up and running (I mean not just to establish a connection to port 3306, I mean something more “complete”, that performs a little operation against the MySQL server).

It is possible to make haproxy check the status of a mysql server using a small shell script managed through the xinetd daemon.

What this script basically does is performs a basic operation against the mysql database then returns http status 200 if the operation was successful or http status 500 if it there was any error (i.e. mysql was not available).

Script

The script looks like this:

#!/bin/bash
#
# This script checks if a mysql server is healthy running on localhost. It will
# return:
#
# "HTTP/1.x 200 OKr" (if mysql is running smoothly)
#
# - OR -
#
# "HTTP/1.x 500 Internal Server Errorr" (else)
#
# The purpose of this script is make haproxy capable of monitoring mysql properly
#
# Author: Unai Rodriguez
#
# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#
# mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' 
#     -> IDENTIFIED BY '257retfg2uysg218' WITH GRANT OPTION;
# mysql> flush privileges;

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="secret"

TMP_FILE="/tmp/mysqlchk.out"
ERR_FILE="/tmp/mysqlchk.err"

#
# We perform a simple query that should return a few results :-p
#
/usr/bin/mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME 
	--password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
	# mysql is fine, return http 200
	/bin/echo -e "HTTP/1.1 200 OKrn"
	/bin/echo -e "Content-Type: Content-Type: text/plainrn"
	/bin/echo -e "rn"
	/bin/echo -e "MySQL is running.rn"
	/bin/echo -e "rn"
else
	# mysql is fine, return http 503
	/bin/echo -e "HTTP/1.1 503 Service Unavailablern"
	/bin/echo -e "Content-Type: Content-Type: text/plainrn"
	/bin/echo -e "rn"
	/bin/echo -e "MySQL is *down*.rn"
	/bin/echo -e "rn"
fi

Steps on the MySQL server

First, you should create the script somewhere, and assign proper permissions:

chown nobody /opt//mysqlchk
chmod   744  /opt//mysqlchk

Then, set permissions into the mysql server:

mysql> GRANT SELECT on mysql.* TO 'mysqlchkusr'@'localhost' 
    -> IDENTIFIED BY 'secret' WITH GRANT OPTION;
mysql> flush privileges;
mysql> exit

Test:

/opt/mysqlchk
HTTP/1.x 200 OK

Now, configure xinetd by adding this line at the bottom of /etc/services:

mysqlchk        9200/tcp                        # mysqlchk

Then add this file /etc/xinetd.d/mysqlchk:

# default: on
# description: mysqlchk
service mysqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 9200
        wait            = no
        user            = nobody
        server          = /opt/mysqlchk
        log_on_failure  += USERID
        disable         = no
        only_from       = 0.0.0.0/0 # recommended to put the IPs that need
                                    # to connect exclusively (security purposes)
        per_source      = UNLIMITED # Recently added (May 20, 2010)
                                    # Prevents the system from complaining
                                    # about having too many connections open from
                                    # the same IP. More info:
                                    # http://www.linuxfocus.org/English/November2000/article175.shtml
}

Restart xinetd (you can watch for issues on /var/log/syslog):

/etc/init.d/xinetd stop
/etc/init.d/xinetd start

Test:

telnet localhost 9200
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
HTTP/1.1 200 OK

Content-Type: Content-Type: text/plain

MySQL is running.

Connection closed by foreign host.

Steps on the HAProxy server
Now, in order to make haproxy check the status of the mysql service through the xinetd-managed-script, we should add something similar to this on the haproxy.cfg file:

listen  MySQL 10.135.2.67:3306
        mode    tcp
	option  httpchk
        server  10.135.2.69:3306 10.135.2.69:3306 check port 9200 inter 12000 rise 3 fall 3
        source  10.135.2.67

What is important?

  1. option httpchk.- tells haproxy to check for full http response (i.e. http headers: 2xx OK or 5xx ERROR)
  2. check port XXXX.- tells haproxy to check the status of the service by sending an http request on that port

Using ejabberd with MySQL native driver

Get mysql driver (if ejabberd < 2.0.0)

If you are using an ejabberd version previous to 2.0.0 (about end of 2007) then you need to put the MySQL .beam files somewhere in your Erlang path (possibly with your ejabberd .beam files):

cd /opt/
wget https://support.process-one.net/doc/download/attachments/415/mysql_beam.tar.gz
tar xvfz mysql_beam.tar.gz
cp -v *.beam /var/lib/ejabberd/ebin/

Mysql initialization

mysql> GRANT ALL ON ejabberd.* TO 'ejabberd'@'' IDENTIFIED BY '';
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Empty database creation:

mysql> CREATE DATABASE ejabberd;
Query OK, 1 row affected (0.00 sec)

Schema creation:

cd /tmp
wget http://svn.process-one.net/ejabberd/trunk/src/odbc/mysql.sql
mysql ejabberd &lt; mysql.sql -p
Enter password:

Check that the database structure has been correctly created:

echo "show tables;" | mysql -D ejabberd -uroot -p
Enter password:
Tables_in_ejabberd
last
privacy_default_list
privacy_list
privacy_list_data
private_storage
rostergroups
rosterusers
spool
users
vcard
vcard_search

Ejabberd configuration

If you installed ejabberd from sources, you would probably need to proceed compiling again but using:

./configure --enable-odbc

Comment out the following line in ejabberd.cfg:

{auth_method, internal}.

Add the following lines in ejabberd.cfg:

{auth_method, odbc}.
{odbc_server, {mysql, "localhost", "ejabberd", "ejabberd", "password"}}.

Note: The MySQL configuration description is of the following form:

{mysql, Server, DB, Username, Password}

When you have done that user accounts are stored in MySQL. You can define extra information that you might want to store in MySQL. Change the module used in ejabberd.cfg to change the persistence from the Mnesia database to MySQL:

* Change mod_last to mod_last_odbc to store the last seen date in MySQL.
* Change mod_offline to mod_offline_odbc to store offline messages in MySQL.
* Change mod_roster to mod_roster_odbc to store contact lists in MySQL.
* Change mod_vcard to mod_vcard_odbc to store user description in MySQL.

References

Showing MySQL Permissions

Usage:

mysql> show grants for 'USERNAME'@'SERVER';

Example:

mysql> show grants for 'root'@'localhost';

+---------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '2e71fce803407b4a' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See Also

Granting MySQL Permissions

Usage

mysql> grant PERMISSIONS on DBNAME.* TO 'USERNAME'[@'SERVER'] 
    -> identified by 'PASSWORD' [with grant option];
mysql> flush privileges;

Examples

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on exampledb.* 
    -> TO 'exampleuser'@'10.10.10.10' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.07 sec)
mysql> GRANT ALL PRIVILEGES on exampledb.* TO 'exampleuser'@'10.10.10.10' 
    -> IDENTIFIED BY 'secret' WITH GRANT OPTION;
Query OK, 0 rows affected (0.06 sec)

Notes

  • WITH GRANT OPTION should only be added when really needed; this privilege allows a user to grant to others (more info).
  • The location from where a user can connect (i.e. ‘username’@'server’) can be also a network if specified as  ‘username’@’10.120.%.%’

See Also