Comments on: Parameters to use on mysqldump https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump Blog by Shlomi Noach Tue, 09 Dec 2014 22:52:25 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: darkfader https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-295263 Tue, 09 Dec 2014 22:52:25 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-295263 Hi,

thank you for the good info, i.e. compared the mysql documentation. I had forgotten how to set the character set and most people still don’t do it. Yours is the first google hit that is not idiotic.

]]> By: Sunny Blog » mysql in case of https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-215870 Sat, 27 Jul 2013 23:01:13 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-215870 […] –routines –single-transaction  –skip-add-locks –skip-lock-tables –default-character-set=utf8 (https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump) restore:# mysql -u root -p[root_password] [database_name] < […]

]]> By: Trent https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-78170 Mon, 19 Mar 2012 07:30:34 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-78170 @shiomi OK so I have serious issues then 🙂

I think I am going to explore a slave solution as the Database is going to continue to grow, and I need to ensure the backups safely happen.

Thank you for your advise

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-78168 Mon, 19 Mar 2012 07:04:06 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-78168 @Trent,
This is due to InnoDB’s MVCC implementation, where a first write to some row makes a version of that row, the second one waiting on the first. If a long SELECT is taking place, the first can never merge in, and the second locks.

]]> By: Trent https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-78156 Mon, 19 Mar 2012 04:19:36 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-78156 Thank you Shiomo,

My DB is 18 Gb and the main table that causes the freezing issue on the site is around 8 GB

I ignore myissam table in the dump to ensure all tables are innodb.

I use in a perl script:

mysqldump –ignore-table=dbname.table1 -ignore-table=dbname.table2 -ignore-table=dbname.table3 -ignore-table=dbname.table4 –single-transaction –quick dbname > /backup/day_`date +%d`_innodb.sql

It starts to freeze about half way through the large table dumping. The table takes inserts, updates, and can take some deletes while it’s being dumped.

I will look into alternative as suggested, however if you know of any other reason this may be happeneing I would be very interested.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-78016 Sun, 18 Mar 2012 15:11:02 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-78016 single-transaction implies skip-lock-tables.
nothing comes out of sync (we are talking about InnoDB tables, right?), and update can still occur while dump is being made, and no inconsistencies are introduced.
If your dataset is too big for mysqldump (usually at the whereabouts of a few dozen GB) then you should consider Xtrabackup or LVM snapshot as an alternative.

]]> By: Trent https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-78009 Sun, 18 Mar 2012 14:55:14 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-78009 Hi, thanks for this I’ve been search for somthing like this.

I am having issues dumping a large table which in turns freezes the site until the table is locked. I use single transaction but not skip lock tables which I will try tomorrow.

Question: using skip lock tables. Will this mean the data between tables can become out of sync? And can updates still occur without freezing the site waiting for response from MySQL while table is being dumped.

]]> By: Web Hosting Nuggets https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-17088 Sat, 28 Aug 2010 09:13:03 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-17088 Thanks! Finally a full explanation of all important parameters for a non locking MySQL dump using InnoDB tables!

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-1811 Wed, 06 May 2009 09:24:18 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-1811 @George – these are the primary flags I use.

]]> By: George https://shlomi-noach.github.io/blog/mysql/parameters-to-use-on-mysqldump/comment-page-1#comment-1810 Wed, 06 May 2009 09:05:32 +0000 https://shlomi-noach.github.io/blog/?p=4#comment-1810 Explanations are okay but please define more.

]]>