0

i have a mysql server on a local machine but the connection is not got for users outside the local network. I want that database to be available on another server wich can handle way more requests and is more stable than the local server. My question is: What is the best way to replicate the data from the local server into the web server? I've readed a little about slave servers, are they made for this case? If i use a mysql slave server and the internet on the main server goes offline, what happens to the slave? If the main server makes some database changes while offline will these changes be written on the slave when the master goes online again? Are there any performace loss for using a slave server?

I do not want a backup server, i want a server to redirect ouside queries into it, so the main server wont be affected for huge traffic and etc.

Improve this question
2
  • MySQL replication is one way only. Master replicates data to Slave. If you do any direct insert/update on the Slave, you will break the replication as Master won't know about it.
    – Marki555
    Jun 18 '15 at 20:04
  • Hello marki, so basically i cant touch the slave with anything but selects on the tables that exists on the master server, since that will break the sync with the master. What i got now is that basically the master server enables logging events and the slave goes through these events and executes them on itself in order to replicate the database. Thats why it is not heavy. Am I right?
    – paulo
    Jun 19 '15 at 14:47
1

Replication sounds like what you need, as long as the slave server you envision will be used as read-only.

Connecting a slave (replica) server to a master imposes very little load on the master. If the connection between the two is severed for whatever reason, the slave will continue to answer queries, and normally will reconnect to the master as soon as practical and pick up exactly where it left off. Certain disruptions can cause you to have to take steps to get replication going again, but ordinarily this is automatically done.

Using a replica this way is common.

I produce several hundred scheduled reports every day, many of which evaluate many millions of rows... and the reports are run against slave servers, to relieve the master of this additional workload. In some cases, the slave is many thousands of miles away from the master, so, naturally, the connections are periodically lost... but they restart themselves.

Improve this answer
4
  • Thank you for the clarification. This is what indeed im looking for. The set up sounds easy, ill start making tests next week since now i know what i gotta do. Thanks guys :)
    – paulo
    Jun 19 '15 at 14:40
  • 1
    N.B. do not try to replicate only certain schemas or tables. Replicate everything. Partial replication has very complex rules. Replicating everything is much simpler.
    – Michael - sqlbot
    Jun 19 '15 at 20:11
  • Hello michael, alright then, what i want is to create compiled tables with the joins/counts and etc that i need so i do not have to do it on realtime. The tables out of sync will be only the result of a select of the sync tables. The compiled table will never come from the master server. Is that okay?
    – paulo
    Jun 22 '15 at 19:45
  • You can create additional tables on a slave server without causing a problem, as long as there are no conflicting table names, and you never modify the replicated tables directly on the slave. You could use the MySQL event scheduler (again, only on the replica) to populate the tables. It's not entirely clear what you are trying to accomplish but it sounds viable.
    – Michael - sqlbot
    Jun 23 '15 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.