Join the community today
Become a Member

Installing MySQL on a separate server

Discussion in 'Install & Upgrades or Pre-Install Questions' started by Abbas Jaffar Ali, Sep 14, 2014.

  1. Abbas Jaffar Ali

    Abbas Jaffar Ali New Member

    13
    4
    3
    Jun 18, 2014
    Ratings:
    +4
    Local Time:
    6:25 AM
    What guide can I follow to have MariaDB installed on a separate server and everything else on another. Also, what specs do you recommend for the DB server? Will the 1GB Linode be good for it and the 2GB for the Web Server for a Wordpress install that gets roughly 4000 uniques per day?
     
    • Like Like x 1
  2. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:25 AM
    1.11
    10.2
    2GB plan for Database server and 2GB plan for PHP, NGinx.
     
  3. eva2000

    eva2000 Administrator Staff Member

    30,157
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    1:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    Check out my reply at MariaDB - Separate DB Server from Web | Centmin Mod Community :)

    As to specs hard to say with more in depth server stats/usage numbers. But for considerable traffic sites, you'd want more cpu cores/threads to work with so Linode 4GB for web and Linode 2GB or 4GB for db server just so you get at least 2-4 cpu cores to work with. Wordpress with Wordpress Super Cache will help too.
     
    • Like Like x 1
  4. Abbas Jaffar Ali

    Abbas Jaffar Ali New Member

    13
    4
    3
    Jun 18, 2014
    Ratings:
    +4
    Local Time:
    6:25 AM
    If I have 4GB RAM, wouldn't FastCGI be a better option than WP Super Cache?
     
  5. Abbas Jaffar Ali

    Abbas Jaffar Ali New Member

    13
    4
    3
    Jun 18, 2014
    Ratings:
    +4
    Local Time:
    6:25 AM
    I have set things up according to the guides but I cannot seem to connect from my Web Server to my DB server with the Wordpress setup. When running Wordpress setup, database connection can't be established. I've made sure Port 3306 is set in both TCP_IN and TCP_OUT sections as well as whitelisted the IP of the web server on the DB server. What else can be done?
     
  6. eva2000

    eva2000 Administrator Staff Member

    30,157
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    1:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    yes if you can get fastcgi_cache to work for you then better, but wp super cache is fool proof and works very well :)

    ensure both web and db have CSF whitelisted for each server's IP and you need to grant your mysql user access to db server from it's web ip address and not localhost.
     
  7. Abbas Jaffar Ali

    Abbas Jaffar Ali New Member

    13
    4
    3
    Jun 18, 2014
    Ratings:
    +4
    Local Time:
    6:25 AM
    |I believe I am granting access. I'm using the following command on my Database server:

    update db set Host='xxx.xxx.xxx.xxx' where Db=‘db_name';

    I've put the IP address abd db name where required above. And I'm just using the root user for mysql which already has remote access privileges, no?
     
  8. eva2000

    eva2000 Administrator Staff Member

    30,157
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    1:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    no root user by default doesn't have remote access, you need to grant it even for root user
     
  9. Abbas Jaffar Ali

    Abbas Jaffar Ali New Member

    13
    4
    3
    Jun 18, 2014
    Ratings:
    +4
    Local Time:
    6:25 AM
    Hmm- when I try to add root using
    update user set Host='xx.xx.x.x' where user='root';

    I get the following error:
    Duplicate entry '80.85.87.60-root' for key 'PRIMARY'
     
  10. eva2000

    eva2000 Administrator Staff Member

    30,157
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    1:25 PM
    Nginx 1.13.x
    MariaDB 5.5
  11. Abbas Jaffar Ali

    Abbas Jaffar Ali New Member

    13
    4
    3
    Jun 18, 2014
    Ratings:
    +4
    Local Time:
    6:25 AM
    Ugh- a bit too technical for someone not very familiar with all this.

    How big is the hit if use MySQL on the same server? Should I increase resources on that?
     
  12. eva2000

    eva2000 Administrator Staff Member

    30,157
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    1:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    Depends on what plugins you add to wordpress to increase mysql complexity and load and size of database.

    You should read that documentation so you understand how to properly grant user permissions on a database whether it be local or remote.

    Here's the query though but you're on your own for the rest. If unsure read up and test on test server first

    Code:
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'ipaddress' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
    ipaddress is ip of web server that is to connect to db server
     
  13. Abbas Jaffar Ali

    Abbas Jaffar Ali New Member

    13
    4
    3
    Jun 18, 2014
    Ratings:
    +4
    Local Time:
    6:25 AM
    Thanks! That worked
     
    • Like Like x 1
  14. Mike Guista

    Mike Guista New Member

    20
    10
    3
    Sep 15, 2014
    Ratings:
    +10
    Local Time:
    8:25 PM
    1.7.6
    10.x
    I am using centminmod on a 512MB OpenVZ VPS which has visitors like this. A wordpress based site.

    [​IMG]
     
    • Like Like x 1
  15. eva2000

    eva2000 Administrator Staff Member

    30,157
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    1:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    You're welcome
    wow nice 78,000 visitor sessions/day !
     
    • Like Like x 1
  16. Mike Guista

    Mike Guista New Member

    20
    10
    3
    Sep 15, 2014
    Ratings:
    +10
    Local Time:
    8:25 PM
    1.7.6
    10.x
    I am pretty happy with the centminmod stack. It doesn't consume more than 450MB memory and the site is fast. All credit goes to @eva2000 for great tweak and this amazing script.
     
    • Like Like x 2
  17. eva2000

    eva2000 Administrator Staff Member

    30,157
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,136
    Local Time:
    1:25 PM
    Nginx 1.13.x
    MariaDB 5.5
    brings a big smile to my face :D
     
    • Like Like x 1
  18. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:25 AM
    1.11
    10.2
    What!
    :D
    70K Unique user's yet it only serve by a 512MB droplet :cautious:

    What caching implementation did you use?
     
  19. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:25 AM
    1.11
    10.2
    And how much is your database size?
    I'm thinking of 1GB database :whistle:
    For that user base.
     
  20. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    11:25 AM
    1.11
    10.2
    I got this:
    upload_2014-9-15_5-2-5.png

    and it's consuming 5-7GB RAM :rolleyes: