Killing MySQL Processes

Once in a while you might face problems related to MySQL being not responding. Most of the time it’s due to large database size or you’re running a select query on an un-indexed table. Instead of waiting for the process to complete (which may take hours), you can cancel the stalling processes using the following method.

First, open up another MySQL session and call the following query:

SHOW PROCESSLIST;

Next, you will see results like this:

+—-+—————–+———–+——+———+——+————————+——————+———-+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+—-+—————–+———–+——+———+——+————————+——————+———-+
| 254772 | event_scheduler | localhost | NULL | Daemon | 2693 | Waiting on empty queue | NULL | 0.000 |
| 435342 | root | localhost | NULL | Query | 0 | Table lock | SHOW PROCESSLIST | 0.000 |
+—-+—————–+———–+——+———+——+————————+——————+———-+

Look for the ID of the query which you wish to terminate and use it in the following query, for example:

KILL 435342;

That’s it, the process will then be terminated by MySQL. Hope it helps.

Import and Export Large MySQL Database

For most of the time, web-based database management control panel such as phpMyAdmin does the job well for importing and exporting backups. However, as your database grows larger and larger, it will reach to a point where it can no longer be done using any web-based control panels anymore.

For instance, our database has reached 500mb just a couple of months ago and phpMyAdmin would stop halfway when doing the job and showing a timeout message. It’s a standard for web server to stop loading a page and declare as timeout once it waited for a certain amount of time. You can of course change the web server configuration to make the timeout really really long but that is not practical and may break your web server.

A better way would be using the command line tools came together with MySQL. Let’s try to import a large database using the command line tool. The first thing you need to do is to open up the command prompt, or terminal. For Windows operating system, you need to access to the bin folder in your MySQL installation directory, for example:

cd C:\xampp\mysql\bin

Then, use the following command to import the SQL file to your database.

mysql -u username -p database_name < file.sql

Then, a message will pop out and ask for the database password. Insert the correct password and wait for a while until all the data is being loaded to the database. If you see your mysql directory is showing in the next line, it means the operation has be done. Your command prompt should look something like this:

Next, we will export our database using the command line tool. This time, we will be using the mysqldump utility. To export your database, type in this command line:

mysqldump -u username -p database_name > file.sql

If you don’t specify a target destination for the file, it will be saved to the bin folder, in this case, C:\xampp\mysql\bin.

That’s all! It’s very easy and quick once you’re familiar with the command line.