Usually you ignore tables when dumping your database. But when importing you can not ignore certain tables by the mysql command line tool.

I just had the challenge to find an easy way to do this or wait houres until the import completes and found an easy workaround. Just before you import the sql file, you can create a dummy table, which does not match the original schema. When you start the import an error will occur when processing the insert statements. If you supply a --force to your command line, mysql will ignore the error and continue with the import.

grep -v "DROP TABLE IF EXISTS" mysql-backup.sql | mysql --force

A slave recently began to report syntax errors in replicated queries. This happend about once a day and was completly random. As the master ran fine and the connection between the two hosts has no errors, the error must be somewhere located on the slave. I began testing the server including the discs without result. Later I accidently did something that made a service on the slave crash. In the error message I could see that the kernel discovered some kind of memory corruption in the process and terminated it.

After running memtest for a couple of houres on the system some memory errors were found. The server was tested before deployment and was running without errors for about two years so memory was the last thing I thought about. But now I can imagine why enterprise servers use ECC memory.

When connection to a mysql you can either connect by a TCP/IP connection or through a socket.

If you are connection though a socket the host value for the connecting user will always be 'localhost'. When using TCP/IP the host will always be '127.0.0.1'.

This is very important when specifiying privileges. If you allow a user to connect from 127.0.0.1 ('user'@'127.0.0.1') this user will not be able to connection through sockets:

# mysqladmin --user=zabbix_agent --password=xxx ping
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'zabbix_agent'@'localhost' (using password: YES)'

mysql> show grants for 'zabbix_agent'@'127.0.0.1';
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for zabbix_agent@127.0.0.1                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO 'zabbix_agent'@'127.0.0.1' IDENTIFIED BY PASSWORD '*xxx' |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Note that the mysql client will automatically use a socket if you use -h localhost (default). So if you pass -h 127.0.0.1 things will work:

# mysqladmin -h 127.0.0.1 --user=zabbix_agent --password=x ping
mysqld is alive

If you get the error

MySQL said: Access denied; you need the SUPER privilege for this operation

when inserting data into an table with a trigger, you should check definier of the trigger. Mine was empty and caused this error. Re-Creating the trigger solved the problem and a proper definier was set.

To dump all you triggers you can use the following query

SELECT CONCAT_WS( " ", "CREATE TRIGGER",  TRIGGER_NAME,  ACTION_TIMING,  EVENT_MANIPULATION,  "ON",  EVENT_OBJECT_TABLE,  "FOR EACH ROW",  ACTION_STATEMENT) 'create_trigger' 
FROM information_schema.triggers

MySQL just released a brief overview of MySQL 5.4:

• Scalability Improvements
• Subquery Optimizations and Join Improvements
• Improved Stored Procedure Management
• Information Schema Additions
• OUT Parameter Support in Prepared Statements

There is a bug in MySQL <5.0.48 that prevents dumping damaged tables. If something really fucked up you have to use the innodb_force_recovery parameter to get the InnoDB engine into some kind of repair mode. But when using this option, a select fails with the following error:

mysql> select * from acknowledges limit 1;
ERROR 1034 (HY000): Incorrect key file for table 'acknowledges'; try to repair it

Everybody should know, that NULL values can produce results you dont expect. Usually you are aware of what NULL values are and cause. But if NULL values occure and you didn't expect them, prepare for "Unforeseen Consequences".

Here an example what can happen.

Recently, a customer had the following problem: As his web site's user count increased over time, it became slower and slower. As the hardware is fast enough to handle a lot more visitors, I began looking for what could cause this problem.

lesen Sie mehr