Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
How to perform hot backup for InnoDB?
Answer:
You can buy the official backup tool - "InnoDB Hot Backup" if you have the money. But you can also use the free alternative - the Percona XtraBackup
To download and install, you can refer to their web site: http://www.percona.com/docs/wiki/percona-xtrabackup:installation:binaries
To perform a quick and simple backup, you need:
# sudo xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --target-dir=/tmp/backup --datadir=/var/lib/mysql
Dump the table schema of a MySQL database
Answer:
To dump the schema of a MySQL database (hence, no data), you need just one command:
E.g.
# mysqldump my_db my_table --no-data
Order by predefined order in MySQL
Answer:
Assume you have a SQL statement like:
SELECT id FROM user WHERE id IN (5, 10, 6);
How to make sure the returned rows are in the exact order as 5, 10, 6?
You can do the following:
SELECT id FROM user WHERE id IN (5, 10, 6) ORDER BY FIELD (id, 5, 10, 6);
Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
Explain query execution plan in MySQL
Answer:
To get a better understanding of the performance of your MySQL query, you can use the explain statement.
E.g.
mysql> explain select * from user where host = 'localhost';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | PRIMARY | PRIMARY | 180 | const | 12 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+Usually you need to look at the column "key" to see if index was being used, and the column "rows" to see how many rows are scanned for the query.
List all the database size in MySQL
Answer:
The following command will list out all the databases size in MySQL
mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema Output:
+--------------------+----------------------+
| Data Base Name | Data Base Size in MB |
+--------------------+----------------------+
| test | 152.59375000 |
| information_schema | 0.00781250 |
| mysql | 0.61196423 |
+--------------------+----------------------+
