I have a huge MySQL table, 2.1 million records, 200MB. Once a week I
need to dump it in CSV format and zip the file.
This is not on my server, and it's in production, so I don't want to
risk testing different methods and possibly hanging up their server
for a period of time, so I wanted to
[snip]
I have a huge MySQL table, 2.1 million records, 200MB. Once a week I
need to dump it in CSV format and zip the file.
This is not on my server, and it's in production, so I don't want to
risk testing different methods and possibly hanging up their server
for a period of time, so I
I use a script like this:
#!/bin/bash
DATE=`date +%A`
DESTFILE=/home/mysql-backups/mysql-dump-$DATE
/usr/bin/mysqldump --skip-extended-insert -uroot -ppassword
mydatabase $DESTFILE.sql
/usr/bin/zip -qjTm $DESTFILE.zip $DESTFILE.sql
I end up with:
mysql-dump-Friday.zip
A few observations:
1 - if the table is in the InnoDB format, you aren't going to lock up their
server, as InnoDB doesn't do table locking. SHOW TABLE STATUS LIKE
'tablename' will tell you.
2 - Ryan's mysqldump script looks useful - also, there's a little-used
option with mysqldump that lets
The table is MyISAM, does that matter?
On May 2, 2007, at 7:28 AM, Dan Buettner wrote:
A few observations:
1 - if the table is in the InnoDB format, you aren't going to lock
up their server, as InnoDB doesn't do table locking. SHOW TABLE
STATUS LIKE 'tablename' will tell you.
2 -
MyISAM does table level locking, which is to say that read (select) and
write (insert/update/delete) cannot happen at the same time. One will wait
for the other.
If your select takes 10 seconds, then any write operations will block for
those 10 seconds. Other read processes should be
Thanks to everyone who answered, think I've got enough info now to
handle it. :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
