Using logs to update MySQL

Junior WebHelper Joined: 15 Apr 2003 Posts: 35

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

Posted: Tue May 13, 2003 4:48 pm (21 years, 7 months ago)

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.

Junior WebHelper Joined: 15 Apr 2003 Posts: 35

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

Posted: Tue May 13, 2003 6:37 pm (21 years, 7 months ago)

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

Junior WebHelper Joined: 15 Apr 2003 Posts: 35

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!!

Posted: Tue May 13, 2003 7:25 pm (21 years, 7 months ago)

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?

Junior WebHelper Joined: 15 Apr 2003 Posts: 35

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?

Posted: Tue May 13, 2003 9:06 pm (21 years, 7 months ago)

I think it should work in phpMyAdmin

Posted: Wed May 14, 2003 4:52 pm (21 years, 7 months ago)

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.