hand replication

I made two copies of one not too big database, wherof the transaction rate is conveniently noted in inverse weeks. I have been using textfile-differencing to copy changes, but would like something more automatic for this by-hand replication, but real automatic replication is out of the question: there will be no such communication channel. I use flash drive.

Is there a log from which I can get SQL statements that record changes from some time, copy that to flash drive, and mark the time when I did that, by truncating the log or starting a new one or ...?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: hand replication

----- Original Message -----
> From: "Halász Sándor"
>
> Is there a log from which I can get SQL statements that record
> changes from some time, copy that to flash drive, and mark the time
> when I did that, by truncating the log or starting a new one or ...?

Yes, the MySQL binary log can be read (and thus, re-executed) by the mysqlb=
inlog utility.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: hand replication

>>>> 2011/08/10 08:16 +0200, Johan De Meersman >>>>
Yes, the MySQL binary log can be read (and thus, re-executed) by the mysqlbinlog utility.
<<<<<<<<
Yes, but what is the best means of picking up the changes from the instance where there were changes to the instance that is a copy? Is it best to copy the log and that so use "msqlbinlog"? Or is it better so to use "msqlbinlog" that it makes SQL statements that I copy to the other instance?

No TCP/IP here, only a flash drive.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: hand replication

On 8/15/2011 09:06, wrote:
>>>>> 2011/08/10 08:16 +0200, Johan De Meersman>>>>
> Yes, the MySQL binary log can be read (and thus, re-executed) by the mysqlbinlog utility.
> <<<<<<<<
> Yes, but what is the best means of picking up the changes from the instance where there were changes to the instance that is a copy? Is it best to copy the log and that so use "msqlbinlog"? Or is it better so to use "msqlbinlog" that it makes SQL statements that I copy to the other instance?
>
> No TCP/IP here, only a flash drive.
>
>

If you are going to pretend to be the MySQL replication system, it
wouldn't hurt you to understand the process before you start. First,
read the replication chapter in the manual. It will describe the theory
behind replication.

Next, you need to realize that you will be replacing both the SLAVE IO
thread and the SLAVE SQL thread with your process.

The SLAVE IO thread you replace when you get the statements the slave
needs to replicate onto the flashdrive. You can do that two different ways:

1) extract the statements from the binary log.
2) get the master to sent you the statements just as if you were a slave.

mysqlbinlog will do either - (again, read the manual on how to use the
tool)

Once you have collected the statements you need the slave to apply (and
put them on your flash drive), now it's your turn to replay those
statements on the slave. The easiest tool for that will probably be the
mysql client (a command-line tool). This is where you become the SLAVE
SQL thread.

Beyond that, all you really need to keep up with is the binary log
position you replicated last (again, pretending to be the SLAVE IO thread).

Best of luck! what you are doing is definitely labor intensive.

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: hand replication

Thank you for the advice, and I read about "mysqlbinlog"....

>>>> 2011/08/15 10:22 -0400, Shawn Green (MySQL) >>>>
1) extract the statements from the binary log.
2) get the master to sent you the statements just as if you were a slave.

mysqlbinlog will do either - (again, read the manual on how to use the tool)

Once you have collected the statements you need the slave to apply (and put them on your flash drive), now it's your turn to replay those statements on the slave. The easiest tool for that will probably be the mysql client (a command-line tool). This is where you become the SLAVE SQL thread.

Beyond that, all you really need to keep up with is the binary log position you replicated last (again, pretending to be the SLAVE IO thread).

Best of luck! what you are doing is definitely labor intensive.
<<<<<<<<

I do not understand 1) or 2). Playing with "mysqlbinlog" I see that I can get earlier or later bunches of SQL commands if not all of them, but it seems to me that it is not safe to use part of its output unless carefully done, for, say, I use ANSI mode and its output uses the character ere the small "a" for enclosing variable names, and local "sql_mode" is changed. I also started "mysqld" with the "log-bin" argument something convenient for me to read.

I suppose you mean that for finishing the replication I make the "mysqlbinlog" output "mysql" s standard input.

I find a later starting point by noting the greatest "Xid" value?

In this case it is not very labor-intenstive, for the transaction rate on these databases is most readily noted in inverse weeks, or maybe inverse longer whiles.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org