The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Remote mysql

Discussion in 'General Discussion' started by Sergiu Tot, Nov 14, 2007.

  1. Sergiu Tot

    Sergiu Tot Well-Known Member

    Joined:
    Jul 17, 2007
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Cluj, Romania
    cPanel Access Level:
    Root Administrator
    Hello !

    I have one server that is in a situation that the hardware cannot be upgraded. My company doesn't want to buy another big server yet, so I was thinking of buying a smaller server to use it just for MySQL. The investment will be very small for a P3 (1000MHz, 512MB RAM) used as a database server and I think it is going to handle the situation.

    Now is the big question, if I do this how can I transfer all the databases to the new MySQL server ? Should I do it before or after seting a remote MySQL server in WHM ? Unfortunately the manual doesn't say much about this.

    Another question, how is the right way to do this without the users to notice the diference ? And how can they still connect to the localhost ? Is it ok if I just make a firewall rule that forwards all the packets that come to localhost:3306 to the remote MySQL server ? Or should I do something more ?
     
  2. mpi

    mpi Well-Known Member

    Joined:
    Mar 29, 2006
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    hmm

    1. backup your databases:
    mysqldump database_name > backup.sql

    2. copy the backup to the new server

    3. import the database on the 2nd server
    mysql - u username -p databasename < filename.sql

    and yes, you will first need to setup mysql on the 2nd server.

    now in regards to your other questions:

    lets assume that you are trying to connect to mysql through php,

    instead of using:
    mysql_connect("localhost", "username, "password");

    you would use:
    mysql_connect("192.168.99.99:3306", "username, "password");

    IMPORTANT NOTE:
    this took me awhile to figure out, but when you are trying to connect to the database from the external server, you are supposed to use the cpanel username and password, not the database's username and password.

    p.s. you will also need to edit iptables to open incoming port 3306:

    vi /etc/sysconfig/iptables
    -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
    service iptables restart

    good luck!
     
  3. Sergiu Tot

    Sergiu Tot Well-Known Member

    Joined:
    Jul 17, 2007
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Cluj, Romania
    cPanel Access Level:
    Root Administrator
    Helo, mpi !

    Thanks for your answer.

    I cannot ask my clients to do this. There are more then 600 accounts on that server and I cannot ask my client to change their scripts - most of then don't even know how to do that. This is why I was wondering, if I can make a port forwarding will everything work ok ?

    That is good to know. Thanks for the tip !
     
  4. mpi

    mpi Well-Known Member

    Joined:
    Mar 29, 2006
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    np;)

    assuming that your mysql is running on port 3306 locally, you "might" be able to forward it to port 3306 on another ip.

    but unless you want all your 600 users to change their database passwords to their cpanel passwords, i think you might be able to somehow configure the new server to allow them to access their databases through their current(database and not cpanel) passwords.
     
    #4 mpi, Nov 14, 2007
    Last edited: Nov 14, 2007
  5. freedman

    freedman Well-Known Member

    Joined:
    Feb 13, 2005
    Messages:
    312
    Likes Received:
    1
    Trophy Points:
    18
    This should work and you can still use the databse user/passwords. I don't see why it would matter. You're connecting to the database, and the database is doing the user authentication.
    it might be necessary to have the users/groups in the remote systems /etc/passwd,group files, but you shouldn't need to change the actual database users.

    My solution to this, and what you should consider moving forward:

    add a "db" subdomain line in the default named templates. In mine I use:
    db 7201 IN A 127.0.0.1

    instruct the customers to access their databases via: mysql_connect("db.DOMAINNAME.COM", "username, "password");

    This gives you the most flexibility. At any time down the road, you can move any or all of them to a remote DB server(s), and your customers needn't be bothered with the details. Simply move the database, and update their dns records.
     
  6. Sergiu Tot

    Sergiu Tot Well-Known Member

    Joined:
    Jul 17, 2007
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Cluj, Romania
    cPanel Access Level:
    Root Administrator
    Thank you guys for your time and for your answers. All it remains now is to do some tests and after that to start working on the new environment.

    Now, I have another question. I was thinking of using the same MySQL server for more hosting servers. I want to make a strong MySQL server that should be used as remote MySQL server by more then one (cPanel) hosting server. As I saw on some threads, I understand that this is possible. The only thing that I do not understand is how will the MySQL server solve the identical user names.

    Let's take an example. If I have two web hosting servers. On both servers I have more accounts. On one server I create an account for thisismyfirstdomain.com and on the other server I create an account for thisismyseconddomain.com. On both servers the cPanel will generate the username to be thisismy (or something like this). This means that both servers will have one username for two different domains. As the MySQL database is created using the cPanel username, how will the MySQL server know that one username is from server A and the other (identical) username is from server B ? Is there a way to change this ? Or is there a way to change the convention used by cPanel/MySQL so that databases will be created as serverID_user_dbname instead of user_dbname ?
     
  7. Sergiu Tot

    Sergiu Tot Well-Known Member

    Joined:
    Jul 17, 2007
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Cluj, Romania
    cPanel Access Level:
    Root Administrator
    Sorry for bumping up the topic, but is there anyone who can give an answer regiarding the problem mentioned (identical usernames) ?
     
  8. wizzy420

    wizzy420 Well-Known Member

    Joined:
    Nov 13, 2007
    Messages:
    125
    Likes Received:
    2
    Trophy Points:
    18
    My questions would be with 600 accounts, and the monthly income generated, how could they not justify buying a new server to keep the customers happy ...
     
  9. freedman

    freedman Well-Known Member

    Joined:
    Feb 13, 2005
    Messages:
    312
    Likes Received:
    1
    Trophy Points:
    18
    I don't think there's a real good solution for this in cpanel.
    The only thing I could think of is to make sure that there is an entry in the /etc/passwd & /etc/group file corresponding to any username on any other system.
    This way, when an account for thisismyseconddomain.com gets created on another system, cpanel will think the username "thisismy" is unavailable and wont create the account.
     
  10. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    The only way to do this currently is to put something in place on all the cPanel member servers that ensures an account name is unique among all the servers. Of course, for accounts that already exist, that might not be feasible. What you might have to do is identify all the username collisions and ensure each uses a different MySQL server.

    Or you could rename the other accounts....
     
Loading...

Share This Page