How to create slave database?

Discussion in 'MySQL' started by bouncer69, Aug 12, 2007.

0
  1. #1
    I have just added a second server for my website to minimize the downtime. As I update my website is regularly but I have no idea how to update both database together. I know I can do it in vbulletin with slave settings. Any help to solve this problem will be appreciate.
     
    bouncer69, Aug 12, 2007 IP
  2. aGig

    aGig Well-Known Member

    Messages:
    428
    Likes Received:
    5
    Best Answers:
    1
    Trophy Points:
    118
    #2
    Most of the time when you run two servers and both of them running the entire site (http and mysql) you setup a load balancer.

    There are some MySQL cluster options you can use to keep data very close to the same on both boxes but there is a chance one box will have a little bit of difference.

    There are some scripts that have slave database support such as vBulletin. Tell me a little more about your project :).

    Mark
     
    aGig, Aug 12, 2007 IP
  3. bouncer69

    bouncer69 Peon

    Messages:
    978
    Likes Received:
    43
    Best Answers:
    0
    Trophy Points:
    0
    #3
    My project is a custom php based website where I have used simple mysql connect command.

    <?php

mysql_connect("localhost","user","password")
or die ("Unable to do query" . mysql_error());

mysql_select_db("database") 
or die ("Unable to do query2" . mysql_error());

?>
    PHP:
     
    bouncer69, Aug 12, 2007 IP
  4. aGig

    aGig Well-Known Member

    Messages:
    428
    Likes Received:
    5
    Best Answers:
    1
    Trophy Points:
    118
    #4
    The current MySQL cluster solution is not good. Both servers would need enough extra ram as the size of the DB.

    There is the replication option, but nothing we be automated if your main server goes down.

    Are you running both HTTP and MySQL on both servers?
     
    aGig, Aug 13, 2007 IP
  5. Fahd

    Fahd Well-Known Member

    Messages:
    2,152
    Likes Received:
    44
    Best Answers:
    0
    Trophy Points:
    100
    #5
    Fahd, Aug 13, 2007 IP
  6. ErectADirectory

    ErectADirectory Guest

    Messages:
    656
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Off the wall question here but would something like this be a decent solution to traffic balancing and keeping data the same on multiple servers?

    1 server for nothing but sql queries --> text file output only.

    1-100 servers pulling and pushing SQL commands into that one server and themselves used to process the programming language and displaying the HTML. This way they only have to speak php (asp, cfml or whatever), the sql engine is never used (therefore faster?).

    I do not claim to be a server expert but I do use an API that is just flat text with some statistics for sites. The files are tiny (like .5k) and it seems like the entire process is insanely quick, almost like it's just 1 server doing the whole thing.

    By doing this, you will be able to scale your site on many servers and never have to worry about databases that don't contain the same information. Again this might be a terrible idea but I figured I'd ask since it is semi-relevant.
     
    ErectADirectory, Aug 13, 2007 IP
  7. aGig

    aGig Well-Known Member

    Messages:
    428
    Likes Received:
    5
    Best Answers:
    1
    Trophy Points:
    118
    #7
    The main problem with having the same data on two servers is keeping it up to date. Currently the only way to do it is with the MySQL cluster option that needs as much ram as the size of the DB.

    The replication option works, but data can be off a little as its not instant updates.

    A lot of larger sites setup one server for MySQL and one server for httpd. However when you out grow this load balancers work well :).

    Mark
     
    aGig, Aug 14, 2007 IP