RE: A question about mysql database backup.
Fred, Jim, Thank you very much for your kind help! I still have other questions about the MySQL operation. If a service host is redundant, one is in active status, the other is standby. I want to make the following operation . 1. database backup. This is done on the standby one with the following command. mysqldump --opt --quote-names --flush-logs database_name database.backup 2. database restore. Restore the database with the previous backup. I am not sure what to do. I guess there will be two methods to finish this. * disconnect the two host and make operation on each one individually as following mysql database_namve database.backup Then restart Mysql. * do restore on the active one with the following command. Because the standby host are the backup of the active one. So the data on both of the hosts will finally be identical in the end. I don't know whether it will be in such case. mysql database_namve database.backup If my description is wrong, Would you please let me know the correct methods? Thanks! Lenny Li == LSS-MI Development, RD, Lucent Qingdao [EMAIL PROTECTED] 86-532-8702000-5033 http://gdcsr60.gdc.lucent.com/~lennyli -Original Message- From: Frederic Wenzel [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 4:13 PM To: Li, Lenny Yong Bo (Lenny) Cc: [EMAIL PROTECTED] Subject: Re: A question about mysql database backup. Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about mysql database backup.
on 02/27/2004 12:11 AM, Li, Lenny Yong Bo (Lenny) at [EMAIL PROTECTED] wrote: 1. database backup. This is done on the standby one with the following command. mysqldump --opt --quote-names --flush-logs database_name database.backup How does this operations handle binary fields, password fields, blob fields etc? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about mysql database backup.
On 02/25/2004 09:13 AM Frederic Wenzel wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Are back-ticks actually part of ANSI SQL? Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about mysql database backup.
Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A question about mysql database backup.
Fred, Thanks for your quick response! Do you mean that only one step is enough for a backup mysqldump -F --opt --quote-names database_name database_name.backup? The second step is not needed at all. Am I right? I added the option -Q, it failed, but the --quote-names option works well. Maybe the version number is not high enough. Regards, Lenny Li == LSS-MI Development, RD, Lucent Qingdao [EMAIL PROTECTED] 86-532-8702000-5033 http://gdcsr60.gdc.lucent.com/~lennyli -Original Message- From: Frederic Wenzel [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 4:13 PM To: Li, Lenny Yong Bo (Lenny) Cc: [EMAIL PROTECTED] Subject: Re: A question about mysql database backup. Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A question about mysql database backup.
Ms./Sir, Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup 2. stop mysqld if is running. Then start it with --log-bin option. The purpose of the second step is to update the database from the binary log file since the point I began to do backup. Then it will work normally. Am I right? But I once did a test on the database. I used command LOCK TABLES table_name READ to lock a table as root login, and then insert one row as lss login in another terminal. The insert operation hang there until the table was unlocked. I run command select * from table_name in both terminal finding the result are the same and new row was inserted. So I am confused about the functionality of the second step. If the above procedure is not correct, Would you please let me know the correct ones? I once read the mysql administration document and found some command about backup. But they are executed on mysql prompt like mysql LOCK TABLES, I need the procedures that can be run on command line. Thanks! Regards, Lenny Li == LSS-MI Development, RD, Lucent Qingdao [EMAIL PROTECTED] 86-532-8702000-5033 http://gdcsr60.gdc.lucent.com/~lennyli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL DATABASE BACKUP
Yes and no If you are taking info from a live database you would have it in a steady state to copy it. This means locking the tables. But once you have a copy of the table all you would need to do is update the changes from the logs. Using replication for this I is the most simple way but would mean a second copy of MySQL but you would only need a low spec system.. I hope this helps... Simon -Original Message- From: Kathy Reyes [mailto:[EMAIL PROTECTED]] Sent: 19 March 2002 21:37 To: mysql Subject: MYSQL DATABASE BACKUP I am trying to backup a table in mysql without locking my tables it this posible - 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
MYSQL DATABASE BACKUP
I am trying to backup a table in mysql without locking my tables it this posible - 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: MYSQL DATABASE BACKUP
Yes. Setup replication and peform the backup on the slave. Provided this is your only purpose for having a slave, and there are no queries running on the slave, the specs can be a lot lower than your master/production machine. -Original Message- From: Kathy Reyes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 1:37 PM To: mysql Subject: MYSQL DATABASE BACKUP I am trying to backup a table in mysql without locking my tables it this posible - 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: MYSQL DATABASE BACKUP
not quite sure what you mean by locking. each table will be locked during readout for the dump automaticly for the time the select runs and delivers data to the dumping program. it is not needed to lock the tables (more than one) in advance for a dump, but that, might better for dataintegrety. Lars Kathy Reyes wrote: I am trying to backup a table in mysql without locking my tables it this posible - 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
mysql database backup issue
Hello, could anyone explain me how to backup and in case of system crash restore the mysql database (main database where data about other databases, grants,users, etc. is kept)? Thanks in advance ! Regards, Saulius Gurklys mailto:[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