Hi, please see comments below.. thank you Mauricio
On Wed, 2006-04-05 at 09:02, Prasanna Raj wrote: > Hi All > > Correct me if iam wrong > > You might have run the create table syntax from hrdb database .So it gives > you the create table also when you do mysqlbinlog . > > CREATE TABLE intra.table1 AS SELECT * FROM <NO DATABASE NAME>table2 , which > means you are under hrdb database. > Thast exactly the situation. > > Else you can use below syntax which wont get loaded under hrdb > > CREATE TABLE intra.table1 AS SELECT * FROM hrdb.table2 ( OR ) > use intra ; CREATE TABLE table1 AS SELECT * FROM hrdb.table2 > Would this prevent this kind of querys from appearing in the source ? > > --Praj > > > On Wed, 05 Apr 2006 08:44:52 -0300 > Mauricio Pellegrini <[EMAIL PROTECTED]> wrote: > > > Hi , > > > > Yesterday our main database was dropped by mystake. > > This has never happened before. > > > > Furtunately we had a daily backup performed with mysqldump at about > > 06:00 am that day. > > > > As the problem ocurred at about 12:00am we had to restore the > > transactions from the binary log ( we have several binary log files for > > each day ) > > > > For this pourpose we used mysqlbinlog in order to prepare a source file > > with the transactions to be restored, but as we needed to restore > > transactions for only one database, we used the option -d win > > mysqlbinlog like this > > > > mysqlbinlog -d hrdb --start-date-time="2006-04-04 06:00:00" > src.sql > > > > after that we used the resulting file like this > > > > mysql -e "source src.sql" > > > > all the process took about 1 hour to complete and our data was perfectly > > restored. > > > > But examinig the source file src.sql we noted that there were some > > transactions pertaining other databases than hrdb which we think > > shouldn't be there because of the -d option used with mysqlbinlog. > > > > > > These transaction were table creation querys like > > > > CREATE TABLE intra.table1 AS > > SELECT * FROM table2 > > > > In this query table1 is located in database intra > > and table2 belongs to database hrdb > > since the creation of table1 occurs in database intra > > we thought this query shouldn't be in the source > > > > Am I right ? > > > > > > The problem with this is that the time for the whole restore operation > > would have been considerabily lower if all of these useless transactions > > were excluded ( this write operations were created as a result from > > executing reports not data modification ) > > > > thanks > > Mauricio > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
