4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 Using logs to update MySQL
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
GunstarHero
Junior WebHelper
Junior WebHelper


Joined: 15 Apr 2003
Posts: 35

PostPosted: Tue May 13, 2003 2:47 pm (14 years, 7 months ago) Reply with QuoteBack to Top

Hi,

I have a local MySQL dbase that is kept uptodate (on Win2K), and I'd like to keep the online dbase updated once or twice a week too to keep in synch with my local copy.

I've started using --log-bin to create updates to the local dbase. How do I apply these updates, via phpmyadmin, to the online dbase?

Do I run an SQL query with phpmyadmin? If so, what's the syntax including filepath?

Lastly, is this the best method?

Cheers all!

Anil
OfflineView User's ProfileFind all posts by GunstarHeroSend Personal Message
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Tue May 13, 2003 4:48 pm (14 years, 7 months ago) Reply with QuoteBack to Top

I'm not entirely sure I understand, but I think it would probably be better to run the mysql client to login to mysql on the remote server, rather than using phpmyadmin.

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
GunstarHero
Junior WebHelper
Junior WebHelper


Joined: 15 Apr 2003
Posts: 35

PostPosted: Tue May 13, 2003 6:00 pm (14 years, 7 months ago) Reply with QuoteBack to Top

Sorry for any confusion, I'll try and break it down! Very Happy

I develop PHP/MySQL on Win2K and I test it on localhost running Apache.

I then export the database structure/data using phpmyadmin (localhost) and import it into phpmyadmin (remote).

If I make changes to the localhost dbase, I'd like to apply those changes to the remote dbase without having to export/import the entire dbase again.

I thought using MySQL --log-bin would keep track of changes made to the localhost dbase and allow me to apply those logfile updates to the remote dbase, which would make the updates. It would save me a lot of time.

I hope this is clearer, but if I've misunderstood anything, then please let me know!

Cheers,

Anil
OfflineView User's ProfileFind all posts by GunstarHeroSend Personal Message
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Tue May 13, 2003 6:37 pm (14 years, 7 months ago) Reply with QuoteBack to Top

ahh I understand now...can you give me some of what --log-bin outputs? I'm not familiar with it.

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
GunstarHero
Junior WebHelper
Junior WebHelper


Joined: 15 Apr 2003
Posts: 35

PostPosted: Tue May 13, 2003 6:56 pm (14 years, 7 months ago) Reply with QuoteBack to Top

from the MySQL documentation:

Quote:
4.9.4 The Binary Update Log

The intention is that the binary log should replace the update log, so we recommend you to switch to this log format as soon as possible!

The binary log contains all information that is available in the update log in a more efficient format. It also contains information about how long each query took that updated the database. It doesn't contain queries that don't modify any data. If you want to log all queries (for example to find a problem query) you should use the general query log. See section 4.9.2 The General Query Log.

The binary log is also used when you are replicating a slave from a master. See section 4.10 Replication in MySQL.

When started with the --log-bin[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no file name is given, it defaults to the name of the host machine followed by -bin. If file name is given, but it doesn't contain a path, the file is written in the data directory.

If you supply an extension to --log-bin=filename.extension, the extension will be silenty removed.

To the binary log filename mysqld will append an extension that is a number that is incremented each time you execute mysqladmin refresh, execute mysqladmin flush-logs, execute the FLUSH LOGS statement or restart the server. A new binary log will also automatically be created when it reaches max_binlog_size. You can delete all not active binary log files with the RESET MASTER command.



With this in mind I thought simply using the incremental binary logs from the localhost dbase could be applied to the remote copy and it would be updated. I have the incremental files, but it doesn't appear to be plain text so I have to use mysqlbinlog to check it. I'm not having any luck with mysqlbinlog at the moment!

I wonder if this is the right way to go about it. Me thinks I'll have to do some checking around!!
OfflineView User's ProfileFind all posts by GunstarHeroSend Personal Message
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Tue May 13, 2003 7:25 pm (14 years, 7 months ago) Reply with QuoteBack to Top

ok...so you have a binary data file containing query information for the changes made to your database, and you want to apply the changes to a different server. got it! Very Happy

now...as far as I can tell, mysqlbinlog will return an ascii version of that. so, assuming thats right, you should be able to create a mysql dump file and send that to the server...are you able to connect to the remote server using the command-line mysql client?

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
GunstarHero
Junior WebHelper
Junior WebHelper


Joined: 15 Apr 2003
Posts: 35

PostPosted: Tue May 13, 2003 7:32 pm (14 years, 7 months ago) Reply with QuoteBack to Top

aha, sounds promising. Very Happy

The only access I have to the remote server dbase though is via phpmyadmin (on 34SP). Is this not possible through the normal import route?
OfflineView User's ProfileFind all posts by GunstarHeroSend Personal Message
SfCommand
Senior WebHelper
Senior WebHelper


Joined: 10 Nov 2002
Posts: 143
Location: UK

PostPosted: Tue May 13, 2003 9:06 pm (14 years, 7 months ago) Reply with QuoteBack to Top

I think it should work in phpMyAdmin

________________________________
Miguel

http://community.34sp.com
http://www.miguel.me.uk | http://www.sfcommand.co.uk | http://www.ssdg.org.uk
OfflineView User's ProfileFind all posts by SfCommandSend Personal MessageVisit Poster's Website
adam
Forum Moderator & Developer



Joined: 26 Jul 2002
Posts: 704
Location: UK

PostPosted: Wed May 14, 2003 4:52 pm (14 years, 7 months ago) Reply with QuoteBack to Top

the problem with phpmyadmin is that the data is probably sent using the POST method, not GET...so I dunno how'd you do it automatically.

________________________________
It's turtles all the way down...
OfflineView User's ProfileFind all posts by adamSend Personal MessageVisit Poster's Website
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot edit your posts in this forum.
You cannot delete your posts in this forum.
You cannot vote in polls in this forum.


Page generation time: 0.05408 seconds :: 17 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme