re: Re: How to backup without blocking?
On Tuesday 10 December 2002 07:44, Jason Brooke wrote:
> That's a client-only option, we're not actually having any troubles with
> clients ignoring anything in the binary log - the problem I'm describing is
> that when a database is not first explictly selected, the master refuses to
> write the query to the binary log.
>
> Example 1:
>
> $sql = "insert into database.table values ('somevalue')";
> mysql_query($sql);
>
> Query is not written to binary log, so slaves never pick it up.
> Example 2:
>
> $sql = "insert into table values ('somevalue')";
> // or even "insert into database.table values ('somevalue')";
> mysql_select_db("database");
> mysql_query($sql);
>
> Query is written to binary log fine, slaves pick it up as long as database
> is 'selected'. I can duplicate this issue using the bundled (with mysql)
> mysql client by using the '\u database' command, and also not using it, and
> I see the same result.
>
> In my original email regarding this I mentioned that if it's by design the
> that's fine, from now on we just remember to select the database rather
> than referring to it only in the sql string - but I figured it probably
> isn't by design and might be worth being looked at.
Thanks for bug report, but this bug is already known.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
> Are you using 'replicate-wild-do-table = DBName.%' ??
Thanks for the reply.
That's a client-only option, we're not actually having any troubles with
clients ignoring anything in the binary log - the problem I'm describing is
that when a database is not first explictly selected, the master refuses to
write the query to the binary log.
Example 1:
$sql = "insert into database.table values ('somevalue')";
mysql_query($sql);
Query is not written to binary log, so slaves never pick it up.
Example 2:
$sql = "insert into table values ('somevalue')";
// or even "insert into database.table values ('somevalue')";
mysql_select_db("database");
mysql_query($sql);
Query is written to binary log fine, slaves pick it up as long as database
is 'selected'. I can duplicate this issue using the bundled (with mysql)
mysql client by using the '\u database' command, and also not using it, and
I see the same result.
In my original email regarding this I mentioned that if it's by design the
that's fine, from now on we just remember to select the database rather than
referring to it only in the sql string - but I figured it probably isn't by
design and might be worth being looked at.
Regards,
Jason
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
On Fri, Dec 06, 2002 at 08:36:32AM -0600, Cal Evans wrote: > Run your database from a RAID 0. At some point during the night. Down one of > the drives, mount it and copy the data. Then unmount it bring it back > on-line. The RAID card should re-sync them without any down time. FWIW, you mean RAID 1 ... RAID 0 is non-redundant; it stripes data across disks to get better speed. RAID 1 gives you two disk that are identical. If you have Linux or NT with software RAID, you can easily set two partitions on one drive to be mirrors of each other too, with the same effect. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, SQL, etc) http://www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
On Sat, Dec 07, 2002 at 02:34:01PM +1000, Jason Brooke wrote: > ...and then do the insert query it's written to the binary log fine, and of > course picked up by the slaves. Similarly when using mysql via Php, if I > don't use mysql_select_db() and instead, simply use mysql_query() and > specify the database.table in the sql string passed to it, the query is > never written to the binary log. Are you using 'replicate-wild-do-table = DBName.%' ?? -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, SQL, etc) http://www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
On 7 Dec 2002, at 14:34, Jason Brooke wrote: > I just connected to one of my masters, inserted a record into a table by > referencing database.table in the insert query and the query is never > written to the binary log, thus the slaves never replicate it. Are you using binlog-do-db or binlog-ignore-db on the master (or replicate-do-db or replicate-ignore-db on the slave)? Those are documented to work as you describe: http://www.mysql.com/doc/en/Replication_Options.html Maybe the difference is that Jeremy is replicating everything and you're replicating only some databases. When you make your initial connection, are you specifying the same database that you specify in the USE statement for the other test? -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
> I have a test case that says otherwise. > > I just connected to my master, inserted a record into a table (never > having done a USE), and then checked a slave. It was there as > expected. I have an array of test cases that says otherwise. I just connected to one of my masters, inserted a record into a table by referencing database.table in the insert query and the query is never written to the binary log, thus the slaves never replicate it. However, if I type: \u database ...and then do the insert query it's written to the binary log fine, and of course picked up by the slaves. Similarly when using mysql via Php, if I don't use mysql_select_db() and instead, simply use mysql_query() and specify the database.table in the sql string passed to it, the query is never written to the binary log. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
On Fri, Dec 06, 2002 at 08:36:32AM -0600, Cal Evans wrote: > Run your database from a RAID 0. At some point during the night. Down one of > the drives, mount it and copy the data. Then unmount it bring it back > on-line. The RAID card should re-sync them without any down time. I think you mean RAID-1. RAID-0 can't lose a disk and still function. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 16 days, processed 491,589,012 queries (340/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
On Fri, Dec 06, 2002 at 07:51:54PM +1000, Jason Brooke wrote: > > The problem with this idea is that mysql replication doesn't always > work so you'd end up with data missing from the backup. What? I've never had that happen. If it does, it's a bug. File a bug report if it's not documented as "this doesn't work with replication." > If you don't explicitly select or 'use' a database, the sql query is > never written to the binary log and thus the slaves don't pick it > up. I have a test case that says otherwise. I just connected to my master, inserted a record into a table (never having done a USE), and then checked a slave. It was there as expected. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 16 days, processed 491,528,980 queries (340/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to backup without blocking?
Run your database from a RAID 0. At some point during the night. Down one of the drives, mount it and copy the data. Then unmount it bring it back on-line. The RAID card should re-sync them without any down time. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Philip Mak [mailto:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 3:16 AM To: [EMAIL PROTECTED] Subject: How to backup without blocking? sql, query Right now, every day I run the equivalent of a mysqlhotcopy on my database (read-lock the tables, copy the files, unlock the tables). This freezes up everything (websites, etc.) that uses the database for 10 minutes. That is too long. Is there a way to get a snapshot of the database without locking the tables? Do I need to convert from MyISAM to InnoDB perhaps? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
Dobrý den, sexta-feira, 6 de dezembro de 2002, 07:15:43, napsal jste: PM> sql, query PM> Right now, every day I run the equivalent of a mysqlhotcopy on my PM> database (read-lock the tables, copy the files, unlock the tables). PM> This freezes up everything (websites, etc.) that uses the database for PM> 10 minutes. That is too long. PM> Is there a way to get a snapshot of the database without locking the PM> tables? Do I need to convert from MyISAM to InnoDB perhaps? To execute backup without compromisse the speed and without LOCKS you need change your teables to InnoDB and try use the InnoDB HotBackup Tool ! ( non-free but very functional ) http://www.innodb.com/hotbackup.html or Use the MYSQLDUMP to dump the database. ps: Change to MySQL 4.0 and try the "Query Cache". This is wonderful to websites. - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into "my eyes" look: cannot open my eyes - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
The problem with this idea is that mysql replication doesn't always work so you'd end up with data missing from the backup. If you don't explicitly select or 'use' a database, the sql query is never written to the binary log and thus the slaves don't pick it up. - Original Message - From: "Joseph Bueno" <[EMAIL PROTECTED]> To: "Philip Mak" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, December 06, 2002 7:36 PM Subject: Re: How to backup without blocking? > Hi, > > You can also use mysql replication: you install a slave server and > run mysqlhotcopy on it. This way, you never freeze the master server. > > Hope this helps > Joseph Bueno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to backup without blocking?
Hi, You can also use mysql replication: you install a slave server and run mysqlhotcopy on it. This way, you never freeze the master server. Hope this helps Joseph Bueno Philip Mak wrote: > sql, query > > Right now, every day I run the equivalent of a mysqlhotcopy on my > database (read-lock the tables, copy the files, unlock the tables). > > This freezes up everything (websites, etc.) that uses the database for > 10 minutes. That is too long. > > Is there a way to get a snapshot of the database without locking the > tables? Do I need to convert from MyISAM to InnoDB perhaps? > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to backup without blocking?
sql, query Right now, every day I run the equivalent of a mysqlhotcopy on my database (read-lock the tables, copy the files, unlock the tables). This freezes up everything (websites, etc.) that uses the database for 10 minutes. That is too long. Is there a way to get a snapshot of the database without locking the tables? Do I need to convert from MyISAM to InnoDB perhaps? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
