Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
Optimize table in MySQL
Answer:
OPTIMIZE TABLE command is useful to defragment the database tables. It is particularly useful when you have deleted a large portion of a table or if you have made massive changes to a table with variable-length rows.
To do so, execute the following statement
mysql> OPTIMIZE TABLE my_table;
That's all.
How to show the warnings during mysqlimport
Answer:
When you use mysqlimport to import data from text file, the number of warnings will be displayed at the end of import. But there is no way to show the actual warnings message.
To show the warnings, the only method is get into the mysql client to insert, instead of using mysqlimport.
E.g.
mysql> LOAD DATA INFILE '/tmp/user.csv' REPLACE INTO TABLE user
Query OK, 100000 rows affected, 192 warnings (2.30 sec)
mysql> SHOW WARNINGS;
How to create a transaction in MySQL
Answer:
A simple transaction in MySQL looks like the following:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO my_table VALUES (1, 2, 3);
Query OK, 1 row affected (0.02 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO my_table VALUES (4, 5, 6);
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
At the end of the queries, the row (4, 5, 6) was inserted into the table, but not (1, 2, 3).
How to show MySQL binlog contents in Linux
Answer:
To view the MySQL binlog file, you can use the mysqlbinlog command.
E.g.
# mysqlbinlog localhost-bin.000002
Show all tables started with a prefix in MySQL
Answer:
If your database has a lot of tables, and you want to list all the tables started with a name prefix, you can try:
mysql> SHOW TABLES LIKE 'cache%';
+--------------------------------+
| Tables_in_mydb (cache%) |
+--------------------------------+
| cache |
| cache_block |
| cache_bootstrap |
| cache_field |
| cache_filter |
| cache_form |
| cache_image |
| cache_menu |
| cache_page |
| cache_path |
| cache_update |
+--------------------------------+
11 rows in set (0.00 sec)
