Re: Restore 20 gigabytes of binary logfiles
I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? I am considering this: mysqlbinlog --database=mydb logfiles | mysql -f There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. Would that be faster than having converted the binary logs to sql commands in one file and then use that? Thanks for your help, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore 20 gigabytes of binary logfiles
On Thu, May 13, 2004 at 10:28:26AM +0200, JFL wrote: I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? I am considering this: mysqlbinlog --database=mydb logfiles | mysql -f There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. Would that be faster than having converted the binary logs to sql commands in one file and then use that? It would be, yes. It's hard to say how much faster though. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore 20 gigabytes of binary logfiles
I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. How should relay-log.info look like? Like this: linuxweb1-bin.001 linuxweb1-bin.002 linuxweb1-bin.003 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore 20 gigabytes of binary logfiles
On Thu, May 13, 2004 at 10:16:52PM +0200, Jacob Friis Larsen wrote: I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. How should relay-log.info look like? Like this: linuxweb1-bin.001 linuxweb1-bin.002 linuxweb1-bin.003 ... That looks right. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restore 20 gigabytes of binary logfiles
I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? I am considering this: mysqlbinlog --database=mydb logfiles | mysql -f Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore 20 gigabytes of binary logfiles
JFL wrote: I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? I am considering this: mysqlbinlog --database=mydb logfiles | mysql -f There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
