MySQL Questions & Answers

Content

• Search with special characters
• Why is InnoDB disabled?
• How to find MySQL system information?
• What is the difference between MySQL certified server and community server?
• MySQL monitoring
• MySQL backup
• Corrupt MyISAM table
• How to compile MySQL

Search with special characters

Question: How can I search the following string in a text field: '%newline,tabluator,b)%'?

Answer:

CREATE TABLE spec(txt VARCHAR(255));

INSERT INTO spec values ('bla\tbla\nbla');
INSERT INTO spec values ('\n\tb)');
INSERT INTO spec values ('abc\n\tb)xyz');

SELECT * FROM spec;

SELECT * FROM spec WHERE txt LIKE '\n\tb)';
SELECT * FROM spec WHERE txt LIKE '%\n\tb)%';
SELECT * FROM spec WHERE txt REGEXP '^\n\tb)$';
SELECT * FROM spec WHERE txt REGEXP '\n\tb)';

Why is InnoDB disabled?

Question: After reconfiguring the my.conf InnoDB was disabled. Why?

Answer: This can happen when the InnoDB logfile size (innodb_log_file_size) was set to an new value which is not compatible with the old value.
To avoid this problem shut-down MySQL properly (mysqladmin --user=root shutdown). Then backup and after remove the logfiles, configure the my.cnf with the new logfile size. Start MySQL again and check the error.log.

How to find MySQL system information?

Question: How can I find MySQL stytem information?

Answer:

Operating System

Linux:

$ uname -a
$ cat /etc/SuSE-release
$ cat /proc/version

MySQL libraries

$ ldconfig -p | grep -i mysql

MySQL client

$ mysql --version

MySQL server

mysql> STATUS;
mysql> SELECT VERSION();
mysql> SHOW VARIABLES LIKE 'version%';

$ mysqladmin version -p

MySQL Table versions

SELECT table_schema, table_name, engine, version, row_format
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
 ORDER BY table_schema, table_name
;

What is the difference between MySQL certified server and community server?

Question: What is the difference between MySQL certified server and community server?

Answer:

MySQL monitoring

Question: How can I monitor MySQL related system information, status and activities?

Answer: MySQL monitoring solutions

MySQL backup

Question: How to do backup?

Answer:

# for both
cp /etc/my.cnf $BACKUP_DIR/my_$BACKUP_TIMESTAMP.cnf

# for MyISAM
BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
BACKUP_DIR='/mybackupdir'
mysqldump --user=root --all-databases --lock-all-tables --master-data=2 --quick \
--flush-logs --triggers --routines --events > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

# for InnoDB
BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
BACKUP_DIR='/mybackupdir'
mysqldump --user=root --all-databases --single-transaction --master-data=2 --quick \
--flush-logs --triggers --routines --events > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

Caution: If you have a mixed environment (MySQL AND InnoDB) it becomes a little bit more complicated!

Question: Is LVM snapshot a feasible way to take MySQL/InnoDB backups?

Answer: It depends! If you stop MySQL it should work well. If you have MyISAM tables only, then FLUSH TABLES WITH READ LOCK should guarantee a consistent backup. With InnoDB a LVM snapshot should work as well because it is the same situation as in a sever crash. It becomes a problem when the InnoDB log files are located on a different disk than the data files. FLUSH TABLES WITH READ LOCK is in this situation not sufficient for InnoDB because InnoDB still may write some data in the background which can corrupt your LVM snapshot over 2 devices.

There were some cases reported where MySQL/InnoDB refused to recover from a LVM snapshot backup with core dump:

InnoDB: Progress in percents: 0 1 2 3 mysqld got signal 11;

Innobase never approved LVM snapshots as valid way of taking backups! If these are technical or marketing reasons I do not know.

If you choose LVM snapshot as a backup method we recommend to do a restore-test of the backup and restart the database to see if it recovers successfully. Then you are sure your backup is a valid one.

Literature

[1] MySQL Backups using LVM Snapshots
[2] MySQL Server Backup, Restoration, And Disaster Recovery Planning Presentation
[3] Using LVM for MySQL Backup and Replication Setup
[4] Logical volume management
[5] Backup of MySQL Databases on Logical Volumes

Corrupt MyISAM table

Question: How does a corrupt MyISAM table look like?

Answer: InnoDB tables should not get corrupted at all. MyISAM tables can get courrupted after system failure. You should NEVER run corrupted MyISAM tables. I can even get worse! Do always a check after a crash. How you can find if a table got corrupted and how you can repare it again is shown below:

mysql> CHECK TABLE test;
+-----------+-------+----------+-------------------------------------------------------+
| Table     | Op    | Msg_type | Msg_text                                              |
+-----------+-------+----------+-------------------------------------------------------+
| test.test | check | warning  | 1 client is using or hasn't closed the table properly |
| test.test | check | error    | Key in wrong position at page 3072                    |
| test.test | check | error    | Corrupt                                               |
+-----------+-------+----------+-------------------------------------------------------+
3 rows in set (0.05 sec)

mysql> SELECT COUNT(*) FROM test;
ERROR 145 (HY000): Table './test/test' is marked as crashed and should be repaired

mysql> REPAIR TABLE test;
+-----------+--------+----------+--------------------------------------------------+
| Table     | Op     | Msg_type | Msg_text                                         |
+-----------+--------+----------+--------------------------------------------------+
| test.test | repair | warning  | Number of rows changed from 11000000 to 10000000 |
| test.test | repair | status   | OK                                               |
+-----------+--------+----------+--------------------------------------------------+
2 rows in set (29 min 17.80 sec)

061004  9:43:50 [ERROR] /usr/local/bin/mysqld: Table './test/test' is marked as crashed and should be repaired
061004 10:13:24 [Note] Found 10000000 of 11000000 rows when repairing './test/test'

How to compile MySQL

Question: How do I compile MySQL on platforms were no binaries are provided?

Answer: Recently we wanted to run MySQL on a 64bit PPC Linux platform. We compiled it as follows:

CC="gcc" CFLAGS="-O3 -mpowerpc -m64 -mcpu=powerpc" CXX="gcc" \
CXXFLAGS="-O3 -m64 -mpowerpc -mcpu=powerpc" \
./configure --prefix=/app/mysql/5.0.37

make

make install