Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to DROP = TABLE on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] mysql ... This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql show variables like '%timeout%' - ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the scp of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to DROP = TABLE on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] mysql ... This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql show variables like '%timeout%' - ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
How about if you dumped to a compressor and stored the result? Steve Williams said: Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the scp of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to DROP = TABLE on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] mysql ... This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql show variables like '%timeout%' - ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
well then. perhaps you can add/modify the sshd.conf file, is there a default timout there? that would be my next shot. dan William R. Mussatto wrote: How about if you dumped to a compressor and stored the result? Steve Williams said: Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the scp of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to DROP = TABLE on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] mysql ... This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql show variables like '%timeout%' - ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
Hi, This only delays the agony. I have done that (using bzip2), but eventually this will not work. I would rather engineer a solution that will just work and will scale properly (given infrastructure support ie: network/disk). Thanks, Steve Williams William R. Mussatto wrote: How about if you dumped to a compressor and stored the result? Steve Williams said: Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the scp of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to DROP = TABLE on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] mysql ... This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql show variables like '%timeout%' - ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
