When you already altering your table using FULLTEXT in column, it's mean you can doing MATCH AGAINST query. In this example, i have a table with car content inside. This table have title & description coloumn that already ALTERING with FULLTEXT. After doing "SELECT * FROM your_tables WHERE MATCH(title,description) AGAINST('car')" suddenly you have empty results. Why? In my column there a lot word of "car" and MySQL can't find it easily? Then try using BOOLEAN MODE! example : "SELECT * FROM your_tables WHERE MATCH(title,description) AGAINST('car' IN BOOLEAN MODE)".

Uninstalling MySQL can give high risk that make us re-installing the OS if we don't know the right to do. Here are how to completely un-installing MySQL in Ubuntu :

sudo apt-get --purge remove mysql-server mysql-common mysql-client libmysqlclient15-dev libmysql-ruby

Then to re-installing, do this :

sudo apt-get install mysql-server mysql-common mysql-client libmysqlclient15-dev libmysql-ruby phpmyadmin

Done!

We have one big files all-databases.sql that generated from mysqldump --all-databases which contain many databases inside. We want to split and get separate SQL files by each databases. To do this, we can use perl script. We create splitmysqldump files :

When we import database .sql into our mysql in Mac OS X, suddenly get error like Got a packet bigger than 'max_allowed_packet' bytes. This is because max allowed packet in our MySQL doesn't fit with the .sql that we want imported. The solution is simple :

1. Open /etc/my.cnf
Edit this max_allowed_packet = 1M and change into large number, example : max_allowed_packet = 1000M

2. Go to System Preferences -> Other -> MySQL
Stop services and Start again.

Usually we use DISTINCT or GROUP BY to fetch field by unique content. On some condition, we need to remove duplicate content in table. In MySQL there is no single command to do that. We need two queries to remove duplicate content. First, we create some table and fill it with unique result from old table. Second, we delete old table and rename the new table as the old.

Here is the code :
CREATE TABLE your_new_table as SELECT * FROM your_old_table WHERE 1 GROUP BY your_column_to_remove_duplicates;

After excute this code, check your id coloumn and set if it have primary keys and auto increment, because this script just create structure table on the fly.
Then, you just remove old and rename the new.

We want to select some tables where it DISTINCT some field but will show all fields within. Then we should use DISTINCT and GROUP BY.
For example, you have table insurances with fields are id, name, insurance_id. Some duplicate content located in name field.
So, you want to get all field which DISTINCT by name field :

SELECT DISTINCT * FROM `insurances` GROUP BY name

Here are command to convert all tables in databases from MyISAM into InnoDB using bash script :

Thanks to MySql Performance Blog

Mysql slow query give bunch of line reporting what queries are slow. Reading one by one takes a long time, while we need to know what slowest mysql queries in our server right now. Then, we need mysqlsla to show to us what top slow query. Here are step by step to install and track slow query :

1. Enable myql slow query and build-essential
Make sure we have enable slow query in MySQL configuration by editing /etc/mysql/my.cnf :

# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Can't login after apt-get upgrade on Ubuntu Lucid ? Do you use Amazon EC 2?
Solution to fixing this problem is uninstall mysql packets completely.
Then we can install mysql as new again.

Here steps to fixing mysql on Ubuntu 10.04 :

1. Preventing loss data when remove mysql packet
MySql data usually stored in /var/lib/mysql on Ubuntu. Stop MySql services first by sudo /etc/init.d/mysql stop .
Then you can backup it by using command sudo cp -Rp /var/lib/mysql/ <path to backup>