Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
Force MySQL to use a specific index during query processing
Answer:
Sometimes, the index picked by MySQL might not be the most optimized one, and you want to pick the index to use by yourself.
To do so, you can provide the index hints by doing something like the following.
mysql> SELECT * FROM user FORCE INDEX user_name WHERE name = 'John' AND user_name = 'john2';
So you tell MySQL to use the index user_name, instead of name (if also available).
That's all.
Disable Query Cache at runtime when querying MySQL database
Answer:
When you want MySQL to execute a SQL statement without looking at the query cache first, you can use the keyword SQL_NO_CACHE
E.g.
mysql> SELECT SQL_NO_CACHE name FROM user;
That's all.
Create MySQL Dump that ignore duplicate key error when import back to MySQL
Answer:
When you import a dump file created by the mysqldump command, to a non-empty database, duplicate key error such as "Duplicate entry 'X' for key 'Y'" might occur.
To ignore this error, simply use the following command:
# mysqldump my_db my_table --insert-ignore
Solving the error "Packet too large" in MySQL
Answer:
When you try to insert a very large row of data (e.g. BLOB) into MySQL using a single insert statement, MySQL might give you the error "Packet too large". You might need to adjust the value of max_allowed_packet as needed.
1. Edit the MySQL configurations, e.g. /etc/my.cnf
..
max_allowed_packet=32M
..Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Reference: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
Setting the MySQL's Binary Log Format
Answer:
For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default as the binary log format. If you want to change it, e.g. to use the "row-based" format, do the following:
1. Edit the MySQL configurations, e.g. /etc/my.cnf
..
binlog_format=row
..Don't forget to restart MySQL to take effect.
# /sbin/service mysqld restart
Reference: http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
