Member Avatar for Zingar

hi, im making a vb.net app to make a backup of a mysql database on a remote server.
i could do it easily with just one select and then a loop for all the inserts but the thing is, single inserts for lots of rows are really slow and here i need to copy millions of rows. also this has to be automatic and itll be used on many machines
the query i have on my mind would be somthing like "select * from table1 insert into (remoteserver.remotedatabase.remotetable1)"

im not sure if its possible to access 2 servers in one query? im also assuming that ill need to put all the 2nd connection data in the query (server ip, username, pass)

ive found some stuff online( like openquery) but nothing seemed to help (kept getting syntax errors)
btw the mysql version is 4.1


thanks in advance

Edited by Zingar because: n/a
  • 3 Contributors
  • 3 Replies
  • 100 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jaasaria
Member Avatar for urtrivedi

If you want to synchronize two database on separte server then
you need to write some code which will read and update data on another server

OR

if you just want to keep backup of database on difference location then
below is the one way of doing it.
1) You can take backup using mysql command on your database server which will take backup of your database in sql file or any format you prefer.
2) Set default folder for db backup file.
3) you may schedule this command to execute daily.

4) Now through ftp you may download that file everyday from another server or pc

5) You may also write ftp access code using .net to download that file from database server to you another server/pc.

Member Avatar for Zingar

ya, thats the option i was thinking, to export the db to a file and then get the file from the remote pc. thing is that its a little more complicated than just doing everything with a single query, but i guess theres no way to do that right?
anyways thanks for answering

edit: about synchronizing both databases, that is also an option but the server pc is normally very busy (lots of DB activity) so i dont want it to make it any more busy

Edited by Zingar because: n/a
Member Avatar for jaasaria

1) You can take backup using mysql command on your database server which will take backup of your database in sql file or any format you prefer.

how can i do this .. can u give me a procedure?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.