I got “You can’t use locks with log tables” error when I’m trying to delete contents from the slow_log table which is always be opened on MySQL Engine in a MySQL 5.5 server. My idea is take a table dump and move the data to another remote DB server for later analysis.
I also get the similar errors while I’m dumping only the data from this “slow_log table”
My purpose is to delete all the records from “slow-log” table which a MySQL_built-in table where slow sql queries are being logged.
Solution : Use “truncate” command to delete the table. truncate command destroy the table and recreated it automatically.
[root@SQL04 temp]# mysql -e 'use mysql; TRUNCATE TABLE slow_log;'
here is the script which will move the slow query log data from MySQL production server to another remote MySQL server.
mysqldump --lock-tables=0 --no-create-info mysql slow_log -r /sqldata/temp/slow_log.sql
mysql -h 192.168.10.15 -udbauser -p'mypass' -e 'use slow_log; source /sqldata/temp/slow_log.sql;'
echo -n > /sqldata/temp/slow_log.sql
mysql -e 'use mysql; TRUNCATE TABLE slow_log;'
mysql -h 192.168.10.15 -udbauser -p'mypass' -e 'use slow_log; source /sqldata/temp/slow_log.sql;'
echo -n > /sqldata/temp/slow_log.sql
mysql -e 'use mysql; TRUNCATE TABLE slow_log;'
Leave a Reply