I know the post is a little old, but it seems that many people are having issues with federated engines.

When the mysql binaries are installed via yum, you already have the HA (High Availability) plugins. You simply need to load the plugins within the mysql CLI.

Here is the basic process:

Start mysqld if it is not already started. Make sure ‘federated’ is NOT in /etc/my.cnf at this point.

EX: At this time, /etc/my.cnf will look like this from a standard YUM install….

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Log into the mysql CLI with root (or another account with sufficient privilege).

Type: show engines;

You should see no FEDERATED engine at this point, like this:

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

To enable the federate engine, type the following:

install plugin federated soname 'ha_federated.so'

NOW, when you ‘show engines’ you will see the FEDERATED Engine, but turned off…

It will look like this:

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED  | NO      | Federated MySQL storage engine                             | NULL         | NULL | NULL       |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

You can now safely add the line ‘federated’ to the /etc/my.cnf file like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
federated

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart mysqld (service mysqld restart, etc…)

After the restart, go back in to the mysql CLI.

Type show engines;

You should now see the FEDERATED Engine available and with SUPPORT as YES.

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED  | YES     | Federated MySQL storage engine                             | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

And you are done…go forth and create federate tables…

Good luck!

On ML with the MySQL 5.5 (current) all I needed to do was:

Copy one of the existing files from /usr/local/mysql/support-files/ to /etc/my.cnf
Add ‘federated'(without quotes) to the [mysqld] section (not the end of the my.cnf file)
Restart MySQL

Ever get these errors?

Mysql reporting error when accessing plesk. Zend_Db_Adapter_Exception: SQLSTATE[08004] [1040] Too many connections.

You can set-variable=max_connections=250 to /etc/my.cnf and restart and use mysql close statements for php code.

Or, set up mysql to close sleeping processes.

Mysql Config. (/etc/my.cnf)
wait_timeout variable represents the amount of time that MySQL will wait before killing an idle connection.
The default wait_timeout variable is 28800 seconds, which is 8 hours. That’s a lot.

Sometimes, putting wait_timeout too low (e.g. 30, 60, 90) can result in MySQL has gone away error messages. You’ll have to decide for your configuration.

How to change it.

Step 1) Edit your /etc/my.cnf file and enter the following 2 values.

[mysqld]
interactive_timeout=300
wait_timeout=300

Step 2) run the command and enter your root password

mysql -uroot -p -e"SET GLOBAL wait_timeout=300; SET GLOBAL interactive_timeout=300;"

If you are connected from the mysql console
e.g. mysql> you can run this command which will show you global and session variables.

SELECT @@global.wait_timeout, @@global.interactive_timeout, @@session.wait_timeout, @@session.interactive_timeout;

+-----------------------+------------------------------+------------------------+-------------------------------+
| @@global.wait_timeout | @@global.interactive_timeout | @@session.wait_timeout | @@session.interactive_timeout |
+-----------------------+------------------------------+------------------------+-------------------------------+
|                   300 |                          300 |                  28800 |                         28800 |
+-----------------------+------------------------------+------------------------+-------------------------------+
1 row in set (0.00 sec)

To see the the current values you can run this command

mysql> show global variables like '%timeout%';

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 300      |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 300      |
+----------------------------+----------+
10 rows in set (0.00 sec)

Mysql Recovery. Error logs shows:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed

When page in clustered key index is corrupted. It is worse compared to having data corrupted in secondary indexes, in which case simple OPTIMIZE TABLE could be enough to rebuild it, but it is much better compared to table dictionary corruption when it may be much harder to recover the table.

Manually edited test.ibd file replacing few bytes so corruption is mild.

First I should note CHECK TABLE in INNODB is pretty useless. For my manually corrupted table I am getting:

mysql check table test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> check table test;
+-----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | check | status | OK |
+-----------+-------+----------+----------+
1 row in set (0.69 sec)

First run is check table in normal operation mode – in which case Innodb simply crashes if there is checksum error (even if we’re running CHECK operation). In second case I’m running with innodb_force_recovery=1 and as you can see even though I get the message in the log file about checksum failing CHECK TABLE says table is OK. This means You Can’t Trust CHECK TABLE in Innodb to be sure your tables are good.

In this simple corruption was only in the data portion of pages so once you started Innodb with innodb_force_recovery=1 you can do the following:

mysql> CREATE TABLE `test2` (
    ->   `c` char(255) DEFAULT NULL,
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2 select * from test;
Query OK, 229376 rows affected (0.91 sec)
Records: 229376  Duplicates: 0  Warnings: 0

Now you got all your data in MyISAM table so all you have to do is to drop old table and convert new table back to Innodb after restarting without innodb_force_recovery option. You can also rename the old table in case you will need to look into it more later. Another alternative is to dump table with MySQLDump and load it back. It is all pretty much the same stuff. I’m using MyISAM table for the reason you’ll see later.

You may think why do not you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode Innodb becomes read only for data operations and so you can’t insert or delete any data (though you can create or drop Innodb tables):

mysql> optimize table test;
+-----------+----------+----------+----------------------------------+
| Table     | Op       | Msg_type | Msg_text                         |
+-----------+----------+----------+----------------------------------+
| test.test | optimize | error    | Got error -1 from storage engine |
| test.test | optimize | status   | Operation failed                 |
+-----------+----------+----------+----------------------------------+
2 rows in set, 2 warnings (0.09 sec)

That was easy, right ?

I also thought so, so I went ahead and edited test.ibd a little more wiping one of the page headers completely. Now CHECK TABLE would crash even with innodb_force_recovery=1

080704 0:22:53 InnoDB: Assertion failure in thread 1158060352 in file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even

If you get such assertion failures most likely higher innodb_force_recovery values would not help you – they are helpful in case there is corruption in various system areas but they can’t really change anything in a way Innodb processes page data.

The next comes trial and error approach:

mysql> insert into test2 select * from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

You may think will will scan the table until first corrupted row and get result in MyISAM table ? Unfortunately test2 ended up to be empty after the run. At the same time I saw some data could be selected. The problem is there is some buffering taking place and as MySQL crashes it does not store all data it could recover to MyISAM table.

Using series of queries with LIMIT can be handly if you recover manually:

mysql> insert ignore into test2 select * from test limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
 
mysql> insert ignore into test2 select * from test limit 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20  Duplicates: 10  Warnings: 0
 
mysql> insert ignore into test2 select * from test limit 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100  Duplicates: 20  Warnings: 0
 
mysql> insert ignore into test2 select * from test limit 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200  Duplicates: 100  Warnings: 0
 
mysql> insert ignore into test2 select * from test limit 300;
ERROR 2013 (HY000): Lost connection to MySQL server during query

As you can see I can get rows from the table in the new one until we finally touch the row which crashes MySQL. In this case we can expect this is the row between 200 and 300 and we can do bunch of similar statements to find exact number doing “binary search”

Note even if you do not use MyISAM table but fetch data to the script instead make sure to use LIMIT or PK Rangers when MySQL crashes you will not get all data in the network packet you potentially could get due to buffering.

So now we found there is corrupted data in the table and we need to somehow skip over it. To do it we would need to find max PK which could be recovered and try some higher values

mysql> select max(id) from test2;
+---------+
| max(id) |
+---------+
|     220 |
+---------+
1 row in set (0.00 sec)
 
mysql> insert ignore into test2 select * from test where id>250;
ERROR 2013 (HY000): Lost connection to MySQL server during query
 
mysql> insert ignore into test2 select * from test where id>300;
Query OK, 573140 rows affected (7.79 sec)
Records: 573140  Duplicates: 0  Warnings: 0

So we tried to skip 30 rows and it was too little while skipping 80 rows was OK. Again using binary search you can find out how many rows do you need to skip exactly to recover as much data as possible. Row size can be good help to you. In this case we have about 280 bytes per row so we get about 50 rows per page so not a big surprise 30 rows was not enough – typically if page directory is corrupted you would need to skip at least whole page. If page is corrupted at higher level in BTREE you may need to skip a lot of pages (whole subtree) to use this recovery method.

It is also well possible you will need to skip over few bad pages rather than one as in this example.

Another hint – you may want to CHECK your MyISAM table you use for recovery after MySQL crashes to make sure indexes are not corrupted.

So we looked at how to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to block purging activity, insert buffer merge or recovery from transactional logs all together. Though the lower recovery mode you can run your recovery process with better data you’re likely to get.

In some cases such as if data dictionary or “root page” for clustered index is corrupted this method will not work well – in this case you may wish to use Innodb Recovery Toolkit which is also helpful in cases you’ve want to recover deleted rows or dropped table.

tcpdump -i eth0 ‘port 3306’

Migrate MySQL from Slaved Drive

1. Mount the slave drive. We’ll assume you mounted it at /media/slave

Find the drive:

# fdisk -l
Disk /dev/sda: 1000.2 GB, 1000204886016 bytes
255 heads, 63 sectors/track, 121601 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000374d4

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64         587     4194304   82  Linux swap / Solaris
Partition 2 does not end on cylinder boundary.
/dev/sda3             587      121602   972054528   83  Linux

Disk /dev/sdb: 1000.2 GB, 1000204886016 bytes
255 heads, 63 sectors/track, 121601 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1   *           1          62      497983+  83  Linux
/dev/sdb2              64         584     4184932+  82  Linux swap / Solaris
/dev/sdb3             585      121598   972044955   82  Linux swap / Solaris

sdb is the drive – the slaved drive after the reinstall
Check to see if it is ext3 or ext4

# blkid /dev/sdb3
/dev/sdb3: UUID="52721885-a9af-45e9-89f5-5f26ffca55dd" TYPE="ext3"

Mount according to ext3 or ext4

mount -t ext3 /dev/sdb3 /media/slave

Add to fstab

# nano /etc/fstab
/dev/sdb3   /media/slave   ext3   default 0   1 

2. Edit the MySQL config file to point to the slave’s mysql databases

nano /etc/my.cnf

# datadir = /var/lib/mysql
datadir = /media/slave/var/lib/mysql

service mysqld restart

3. Export the required database

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

for plesk:

mysqldump -u admin -p`cat /etc/psa/.psa.shadow` [database_name] > dumpfilename.sql

4. Reset the Mysql path and import the file

nano /etc/my.cnf

datadir = /var/lib/mysql
# datadir = /media/slave/var/lib/mysql

service mysqld restart

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

&#91;/bash&#93;

Plesk:
&#91;bash&#93;

mysql -u admin -p`cat /etc/psa/.psa.shadow` &#91;database_name&#93; < dumpfilename.sql

&#91;/bash&#93;

Hotkey for Plesk specifically mounted to /olddrive/:

Before migrating, make sure the database you are migrating has already been created in Plesk with the correct username and password.

1. Log onto your server as root using ssh.

2. Edit the mysql config file to use the slave drive
nano /etc/my.cnf

3. Comment out the current path, add your slaved drive’s path and save the file
&#91;bash&#93;

# datadir = /var/lib/mysql
datadir = /olddrive/var/lib/mysql


&#91;/bash&#93;

(save the file using <ctrl><o> then <ctrl><x> to exit.

4. Restart mysql to load the new settings


service mysqld restart

5. Create a dump file of the desired database

mysqldump -u admin -p`cat /etc/psa/.psa.shadow` [database_name] > /tmp/database_name.sql

(Repeat this step for all databases that need to be imported)

6. Repeat step 2-4 and reset the original setting

datadir = /var/lib/mysql
# datadir = /olddrive/var/lib/mysql

7. Import the database

mysql -u admin -p`cat /etc/psa/.psa.shadow` [database_name] < /tmp/database_name.sql [/bash] (Repeat this step for all .sql files created in step 5)

Did you know that mysql comes with configuration files for better optimization? These files are located at:

/usr/share/doc/mysql-server-5.1.71/my-huge.cnf
/usr/share/doc/mysql-server-5.1.71/my-innodb-heavy-4G.cnf
/usr/share/doc/mysql-server-5.1.71/my-large.cnf
/usr/share/doc/mysql-server-5.1.71/my-medium.cnf
/usr/share/doc/mysql-server-5.1.71/my-small.cnf
/usr/share/man/man5/openssl.cnf.5ssl.gz

Or

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Stop mysql

# service mysqld stop
Stopping mysqld:  [  OK  ]

backup the original my.cnf file.

# mv /etc/my.cnf /etc/my.cnf.original

Move the new config file to /etc based on your needs:

# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

Restart MySQL:

# service mysqld start

Other Resources:
http://geekdecoder.com/using-mysqltuner/
http://www.hardwaresecrets.com/article/How-to-Optimize-a-MySQL-Server/1747
http://www.thegeekstuff.com/2011/03/sar-examples/
http://www.cyberciti.biz/tips/top-linux-monitoring-tools.html
http://www.codero.com/knowledge-base/questions/319/How+to+install+mytop+for+database+performance+monitoring%3A
http://httpd.apache.org/docs/current/misc/perf-tuning.html
http://dev.mysql.com/doc/refman/5.0/en/optimization.html

Install the repository
CentOS 5.x

rpm -Uhv http://apt.sw.be/redhat/el5/en/i386/rpmforge/RPMS/rpmforge-release-0.3.6-1.el5.rf.i386.rpm

CentOS 6.x 64 bit

rpm -Uhv http://apt.sw.be/redhat/el6/en/i386/rpmforge/RPMS/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm

Install mytop

yum -y install mytop

Configuration

Odds are, you will get an error initially running this program unless you configure it first.

whereis mytop

Example: /usr/bin/mytop or /usr/local/bin/mytop

nano /usr/bin/mytop or nano /usr/local/bin/mytop

Locate the following lines:

my %config = (
delay => 5,
host => ‘localhost’,
db => ‘test’,
user => ‘root’, …

change the following line and save the file:

db => ‘mysql’,
Run mytop

Plesk

mytop -u admin -p`cat /etc/psa/.psa.shadow`

WHM / cPanel

mytop

Now – go here to learn how to use it!
http://linux.die.net/man/1/mytop

Have you ever seen connection in the SHOW PROCESSLIST output which is in “Sleep” state for a long time and you have no idea why this would happen ?

I see if frequently with web applications and it is often indication of trouble. Not only it means you may run out of MySQL connections quicker than you expected but it also frequently indicates serious problems in the application. If you do not use persistent connections and you have connection in Sleep stage for 600 seconds what could it be ? It may mean some of your pages take that long to generate (or might be the code simply gets into the tight loop and page never gets generated) it also could mean some of external Web Services are slow or not available and you’re not dealing with timeouts properly. Or may be you have several connections to MySQL server and right now running query which takes that long ? In any case it is something frequently worth looking at.

First task is to find to which process the connection belongs. Using different user names for different application is a good practice however it will not tell you which of apache children is handling request in question. If you just want to fix it, ie by restarting apache it is enough but if you want to figure our why it is happening you need more info.

You my notice in the “Host” filed of SHOW PROCESSLIST output not only host but also port is specified, showing you something like “192.168.1.70:58555″ This port can be used to identify the process which owns connection in question:

[root@w1 ~]# netstat -ntp | grep :45384
tcp        0      0 192.168.1.70:45384          192.168.1.82:3306           ESTABLISHED 28540/php-cgi

As you can see in this case we can find php-cgi is holding connection in question (this is lighttpd based system with fastcgi)

Now you know the process and you can use your favorite tools to check what that process is doing.

[root@w1 ~]# netstat -ntp | grep 28540
tcp        0      0 192.168.1.70:58555          192.168.1.90:11211          ESTABLISHED 28540/php-cgi
tcp        0      0 192.168.1.70:52711          192.168.1.88:8080           ESTABLISHED 28540/php-cgi
tcp        0      0 192.168.1.70:45384          192.168.1.82:3306           ESTABLISHED 28540/php-cgi
tcp        0      0 192.168.1.70:45399          192.168.1.82:3306           ESTABLISHED 28540/php-cgi
tcp        0      0 192.168.1.70:45407          192.168.1.82:3306           ESTABLISHED 28540/php-cgi
tcp        0      0 192.168.1.70:45408          192.168.1.82:3306           ESTABLISHED 28540/php-cgi
tcp        0      0 192.168.1.70:35556          192.168.1.92:11211          ESTABLISHED 28540/php-cgi

Using same netstat command and filtering on the PID we can find which connections does this process have. Here you can see it has couple of memcached connections. Few MySQL connections (to the same host, which if usually bad idea) and connection to some external web server.

You can use strace -p to see what host is doing, it often gives a clue. In this case I for example found the process is stuck in pool() system call reading from network. Using netstat can give you an idea what it can be but if you do not like guessing you can use gdb -p . It will not print you exact line of code in PHP which is running but can give you some good ideas – for example in this case I could find stack trace originated from php stream functions not from libmysql or memcache.so, which means it is not MySQL or memcache connections leaving last candidate as the only choice. I also could see some of the variables in GDB “bt” command output which also hinted what could be the problem.

By the way does anyone know any debugger which can connect to PHP process or apache with mod_php and provide backtrace in PHP terms not the one for zend engine ? That would be pretty cool.

Yet another great tool which you can use is server-status if you’re running apache. This way you will see the URL which that process is processing and so get few more hints on what may be happening or even get repeatable example in some cases.

The tools I mentioned regarding figuring our what is happening with the process are not only helpful to debug sleeping connections with MySQL but many other cases when you see web application locking up or starting to runs in the tight loop consuming too much CPU time.

If you know any other tools which could be helpful in this regard would appreciate your comments. There might be some smarter tools out where for production tracing.