Synchronising large tables

[Kwaka]

I've just been pointed to the latest enterprise version of SQLyog, after being a basic SQLyog user for many years.

After the santy worm and potentially loosing all of my the data in the mySQL db's that are behind the phpBB forums that I run, I've decided to review my backup strategy.

I've been relying on my ISP to do a backup but having asked them how often they do one to be told a few times a week it doesn't sound very fixed in my opinion.

So I've been looking in to what I can do.

After plenty of mucking around with php scripts and failing someone mentioned your latest version which does data synchronisation.

I've installed the demo version and I'm very very impressed.

However I've been having a problem due to the size of some of the tables and I wondered if there was some tips to overcoming these problems.

I have overcome all the PK problems and have now got it synching well but for a couple of tables.

I'm using the php tunnelling as the mechanism to connect (source) and am running sqlYog on the windows 2K server its synching to (destination).

As I say on all smaller tables it has worked well.

My large tables tend to be about 10Mb's or so and it has problems with these.

My theory is that if I can get them copied the once then synch should be ok as there are minimal differences to deal with.

I don't get an error on synch it just stops and says finish with nothing in the error log. I think when I did it on Xp I got an error though and it crashed the agent.

So I have 2 questions:

1. Can I synch large tables. Do I have to tweak a setting to get this to work?

2. Assuming I can't, I'm also having problems getting the data across in the first place. When I try to export data as SQL inserts a HTTP response window opens up with some stuff in it, but no error. I can see though that the network connection is still going flat out so is it still doing things? Exporting to csv also fails. Anything I can do about this?

Many thanks.

[Kwaka]

Quote:

2. Assuming I can't, I'm also having problems getting the data across in the first place. When I try to export data as SQL inserts a HTTP response window opens up with some stuff in it, but no error. I can see though that the network connection is still going flat out so is it still doing things? Exporting to csv also fails. Anything I can do about this?

Just to add to this.

If I click on the OK button of the HTTP response window I get the following message:

Error No 1: Error in tunnelling. Please send the HTTP response to [email protected]

I can't do that however as I've only got the message after I clicked OK unless its behind the HTTP response window?

[peterlaursen]

When you are sync'ing you must have a Mysql-server installed locally.

I guess you should check the my.ini/my.cnf -filer for that server and the settings for

max_allowed_packet

I beliveve most (all ?) sql servers install with a default value of “max_allowed_packet = 1M”

Try rasing that significantly.

I'm using max_allowed_packet = 100M (and have tried higher) and no problems in that.

[Kwaka]

Quote:

When you are sync'ing you must have a Mysql-server installed locally.

You do? I have been sync'ing quite happily using a laptop that doesn't have mysql installed on at all!

Quote:

I guess you should check the my.ini/my.cnf -filer for that server and the settings for

I can find a my.ini file. I've added the max_allowed_packet in there and seeing if it makes a difference.

Quote:

I beliveve most (all ?) sql servers install with a default value of “max_allowed_packet = 1M”

I haven't got SQL Server. I'm using a windows version of MySQL.

Thanks for the advice.

[peterlaursen]

it's the same … just a matter of words!!

mysql is a server program! If you say you have installed mysql for windows – that's the same as saying that you are running a mysql server!!

” I have been sync'ing quite happily using a laptop that doesn't have mysql installed on at all!”

pardon ??? syncing means making to mysql databases identical!

But of course if you are networked you can sync two remote servers.

“I can find a my.ini file.”

then the mysql server must be installed on that computer!

” I've added the max_allowed_packet in there and seeing if it makes a difference.!

did it ?

[Kwaka]

I have a networked set-up with multiple pc's.

I know which PC has MySQL installed on! That's the one with the my.ini file. However it said it had been created MySQLAdmin I think which made me think twice about it.

Anyway I've tried adding the max_allowed_packet to the file and I'm not sure if I need to reboot or not for it to kick in. Assuming it doesn't then it doesn't make any difference as it still bombs out.

I'll schedule a reboot for later just in case it does and then recheck tomorrow.

[peterlaursen]

you won't have to reboot but you'll have to stop and restart mysql.

you can use “Winmysqladmin” or “Mysql Administrator” or Command Line (“net stop mysql” and “net start mysql”)

[Ritesh]

It looks like you are using HTTP Tunneling while syncing two MySQL DBs? Recently, we have fixed a system crashing bug that effects DB sync over HTTP Tunneling. You can download the bug-fix release i.e. SQLyog Enterprise v4.01 from:

http://www.webyog.com/sqlyog/SQLyog401Ent.exe

Just install 4.01 and run the sync again. I am sure it will work as expected.

[Ritesh]

BTW, you dont require a MySQL server to be installed locally on your machine. You can sync *any two* MySQL DBs that you are able to connect to.

For a big table, it is recommended that you import the data in the target server for the first time and then sync it.

[Kwaka]

Ritesh wrote on Jan 5 2005, 03:24 AM:

It looks like you are using HTTP Tunneling while syncing two MySQL DBs? Recently, we have fixed a system crashing bug that effects DB sync over HTTP Tunneling. You can download the bug-fix release i.e. SQLyog Enterprise v4.01 from:

http://www.webyog.com/sqlyog/SQLyog401Ent.exe

Just install 4.01 and run the sync again. I am sure it will work as expected.

Tried the update but its now broken the installation although it seems to still be working?

Any ideas?

[Kwaka]

Ritesh wrote on Jan 5 2005, 03:28 AM:

For a big table, it is recommended that you import the data in the target server for the first time and then sync it.

But I can't seem to do that either.

[Kwaka]

peterlaursen wrote on Jan 4 2005, 10:14 PM:

” I've added the max_allowed_packet in there and seeing if it makes a difference.!

did it ?

Changing the max_allowed_packet seems to have done the trick.

Many thanks for your help.

[Ritesh]

Kwaka wrote on Jan 5 2005, 08:34 PM:

Ritesh wrote on Jan 5 2005, 03:24 AM:

It looks like you are using HTTP Tunneling while syncing two MySQL DBs? Recently, we have fixed a system crashing bug that effects DB sync over HTTP Tunneling. You can download the bug-fix release i.e. SQLyog Enterprise v4.01 from:

http://www.webyog.com/sqlyog/SQLyog401Ent.exe

Just install 4.01 and run the sync again. I am sure it will work as expected.

Tried the update but its now broken the installation although it seems to still be working?

Any ideas?

Thanks for the report. I have forwarded the issue to my development team. This will be fixed in the next release of v4.01.

BTW, its good to know that things are working for you.

[Author]

Posts