I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376).
My dump command:
mysqldump -uxx -pxx -h192.168.1.xxx --single-transcation sys_data > /home/sys_data.sql
mysqldump -uxx -pxx -h192.168.1.xxx --single-transcation sys_data > /home/sys_data.sql
When the dump was blocked:
show processlist;
5306612 | root | 192.168.1.161:57180 | sys_data | Query | 23955 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_trade_376`
show processlist;
5306612 | root | 192.168.1.161:57180 | sys_data
| Query | 23955 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_trade_376`
On the other hand I can dump the table tb_trade_376 successfully if I just dump the table only.
mysqldump -uxx -pxx -h192.168.1.xxx --single-transcation sys_data tb_trade_376 > /home/tb_trade_376.sql
mysqldump -uxx -pxx -h192.168.1.xxx \
--single-transcation sys_data tb_trade_376 > /home/tb_trade_376.sql
This works well and quickly!
The table tb_trade_376 has about 700,000-800,000 rows.
What is the next step in investigating why I can't dump the whole database? How can I make it work?
