Mysql – How to kill all the processes in Mysql “show processlist”

databaseMySQL

Because I see a lot of processes there, and the "time" column shows big values for all of them.

Best Answer

Mass killing operation saves time. Do it in MySql itself:

Run these commands

mysql> select concat('KILL ',id,';') from information_schema.processlist
where user='root' and time > 200 into outfile '/tmp/a.txt';

mysql> source /tmp/a.txt;

Reference

---------edit------------

if you do not want to store in file, store in a variable

Just run in your command prompt

> out1=$(mysql -B test -uroot -proot --disable-column-names  -e "select concat('KILL ',id,';') from information_schema.processlist where user='root' and time > 200;")

> out2= $(mysql -B test -uroot -proot --disable-column-names  -e "$out1")

Related Solutions

Sql – Insert into … values ( SELECT … FROM … )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

Oracle
MS SQL Server
MySQL
Postgres
SQLite v3
Teradata
DB2
Sybase
Vertica
HSQLDB
H2
AWS RedShift
SAP HANA
Google Spanner

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

Best Answer

Related Solutions

Sql – Insert into … values ( SELECT … FROM … )

Mysql – How to quickly rename a MySQL database (change schema name)

Related Topic