RE: backup InnoDB db to another server
-Original Message- From: js [mailto:[EMAIL PROTECTED] Sent: Saturday, December 01, 2007 8:11 PM To: Jeff Mckeon Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server You might want to use --single-transaction option when mysqldumping innodb We have a mix of InnoDB and MyIsam tables so that's really not an option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: Osvaldo Sommer [mailto:[EMAIL PROTECTED] Sent: Saturday, December 01, 2007 8:23 AM To: 'Jeff Mckeon'; 'David Campbell'; mysql@lists.mysql.com Subject: RE: backup InnoDB db to another server Jeff: Mysqldump don't back up your index, that's your data only. Osvaldo Sommer Actually I think it's more than that. We have cleaning scripts put place to delete records older than 3 months from certain tables. I think the users have been running these without optimizing the tables afterwards and therefore never reclaiming the space the created with the deletes. These tablename_Old tables were huge. On the main systems I did a mysqldump of just these tables, then dropped the originals from the db and restored them. The entire db size went from 65G to 20G. The database was already screwed up and I have another master running for our applications so there was no risk if I screwed something up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
Jeff: Mysqldump don't back up your index, that's your data only. Osvaldo Sommer -Mensaje original- De: Jeff Mckeon [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 30 de Noviembre de 2007 03:24 p.m. Para: 'David Campbell'; mysql@lists.mysql.com Asunto: RE: backup InnoDB db to another server -Original Message- From: David Campbell [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:29 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server Jørn Dahl-Stamnes wrote: On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or scp dump.sql [EMAIL PROTECTED]:. Onliner mysqldump DB1 -uroot -ppassword dump.sql | ssh 10.10.0.2 cat dump.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The Mysqldump has finished but I've only got a 10gig .sql file. The db is about 65gig in raw size. Does this sound right? Is there a filesize limit for mysqldump .sql files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.10/1160 - Release Date: 29/11/2007 08:32 p.m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
You might want to use --single-transaction option when mysqldumping innodb On Dec 1, 2007 1:20 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:16 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok so on 10.10.0.2 (destination server) issue a: % mysqldump DB1 -h10.10.0.1 -C -uroot -ppassword /DATA/DB01bacup.sql -- 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: backup InnoDB db to another server
Jørn Dahl-Stamnes wrote: On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or scp dump.sql [EMAIL PROTECTED]:. Onliner mysqldump DB1 -uroot -ppassword dump.sql | ssh 10.10.0.2 cat dump.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: David Campbell [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:29 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server Jørn Dahl-Stamnes wrote: On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or scp dump.sql [EMAIL PROTECTED]:. Onliner mysqldump DB1 -uroot -ppassword dump.sql | ssh 10.10.0.2 cat dump.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The Mysqldump has finished but I've only got a 10gig .sql file. The db is about 65gig in raw size. Does this sound right? Is there a filesize limit for mysqldump .sql files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Baron Schwartz Sent: Friday, November 30, 2007 11:06 AM To: Jeff Mckeon Cc: mysql list Subject: Re: backup InnoDB db to another server On Nov 30, 2007 10:55 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: I'm trying to use mysqldump to backup an innoDB based db from one server to an sql file on another. It doesn't seem to be working however... Here is the command I'm using on the source server mysqldump DB1 -uroot -ppassword | mysql --compress -h 10.10.0.1 - uroot -ppassword DB1 /DATA/DB1backup.sql I see a /DATA/DB1backup.sql file created on the source server with 0 size, but nothing on the destination server. What am I screwing up here? Your command is actually telling mysql on 10.01.0.1 to execute the dumped output. It is doing so without creating any ouput, but the is creating an output file anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
On Nov 30, 2007 10:55 AM, Jeff Mckeon [EMAIL PROTECTED] wrote: I'm trying to use mysqldump to backup an innoDB based db from one server to an sql file on another. It doesn't seem to be working however... Here is the command I'm using on the source server mysqldump DB1 -uroot -ppassword | mysql --compress -h 10.10.0.1 -uroot -ppassword DB1 /DATA/DB1backup.sql I see a /DATA/DB1backup.sql file created on the source server with 0 size, but nothing on the destination server. What am I screwing up here? Your command is actually telling mysql on 10.01.0.1 to execute the dumped output. It is doing so without creating any ouput, but the is creating an output file anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup InnoDB db to another server
-Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Friday, November 30, 2007 11:16 AM To: mysql@lists.mysql.com Subject: Re: backup InnoDB db to another server On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Ok so on 10.10.0.2 (destination server) issue a: % mysqldump DB1 -h10.10.0.1 -C -uroot -ppassword /DATA/DB01bacup.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]