I am planning to move all databases of a busy Virtualmin server (s1) to an external server (s2) (on the same LAN) following this guide:
https://www.virtualmin.com/documentation/database/remote
In the guide it says that it is recommended to do this on a fresh install - but since this is not my use case, I am curious if the following would work - and please let me know if there are simpler ways.
I am setting this up using two Ubuntu 14.04 instances in a cloud setup - i.e. they are on the same local network. Webmin v. 1.810.
(EDIT: The following is a more or less accurate description of what I did to reach a working configuration :)
- Setup s2 following the guide mentioned above
- On s2: In
/etc/mysql/my.cfn change bind-address = 127.0.0.1 to bind-address = 0.0.0.0 to make the MySQL server accept connections from s1
- Configure firewalls on both instances to allow connections between them on port 3306
- s1: Stop mysql service and make an archive of the contents of /var/lib/mysql
- s2: Stop mysql service, delete the contents of /var/lib/mysql and extract the contents of the /var/lib/mysql archive from s1
- s1: Configure Virtualmin to connect to s2 as described in the guide
- s1: Open Virtualmin and go to "Webmin" > "Servers" > "MySQL database Server". "User permissions" and "Database permissions" for all users and databases need to be adjusted to reflect the new setup: "User permissions": make sure that the users are allowed to connect from the correct host, s1. "Database permissions": likewise - make sure that the databases can be accessed from the correct host, s1
- s1: Go through all the websites and add the new MySQL hostname in the config files
Howdy,
While I haven't tried doing what you're looking to do on an existing server recently -- I do believe what you're describing should work.
I'll offer that, if you're using a similar MySQL version on both systems, and can afford to stop MySQL on both servers temporarily -- you might be able to save yourself some time by stopping the service and just copying the /var/lib/mysql directories from one server to the other.
Also, note that "Step 8" would be to go through your various websites, and edit the config so that rather than connecting to MySQL on "localhost", they look for MySQL on your new second server.
-Eric
Have set up mysql many times that way. This is also why you always tell users to use dbxxx.yourhost.com for DB connections. Can move DB without users changing anything. Just point the dbxxx to the new database machine via bind.
I'd suggest binding mysql to only 1 IP. Not all with 0.0.0.0. If for some reason you change network interfaces,all of a sudden the DB is wide open. Then I'd suggest setting firewall on new S2 to drop all traffic except from S1 on mysql port. And of course allow webmin connections from S1 to S2. And I'd suggest the DB not be pingable from the outside world. Remove all ping rules from firewall. OR set to allow ping from only S1.
Hi Eric, thanks for your input - the copying method you suggested seems to work fine. However...
I managed to get a connection from s1 to s2 via the mysql command line logging in with root. But when adding the new server name in the website config files, I got a no access allowed error when trying to load the webpage.
PDOException: SQLSTATE[28000] [1045] Access denied for user 'myuser'@'someid.someotherid.internal' (using password: YES) in lock_may_be_available() (line 167 of /home/myuser/public_html/includes/lock.inc).Should I make any changes to the individual users in the database on s2 to make it work?
Best regards, Ben
Ok, think I got it now: on s2 the MySQL "User permissions" and "Database permissions" for all users and databases need to be adjusted to reflect the new setup.
"User permissions": make sure that the users are allowed to connect from the correct host, s1
"Database permissions": likewise - make sure that the databases can be accessed from the correct host, s1