I was just wondering if there is an easy way to move all mysql databases to an external mysql server.
I know how to setup virtualmin to use an external mysql server on a new clean system, but this is a server with a number of websites on it already and am looking to offload mysql to a remote server. I don’t mind having to change the config files for the websites (mostly wordpress) by hand to point to the new server.
For background, the server is getting busy, but I’m on a budget and can’t justify the jump to the next size up for the server, so am looking to get another smaller server to take some of the strain.
The method outlined by @shillongserver assumes that you would want to mirror your Virtualmin users on the secondary Virtualmin server. Using Virtualmin’s backup - restore module, rather than the export database option proposed by @shillongserver , has the advantage of migrating databases quickly and easily to the secondary Virtualmin server by creating Virtualmin users automatically as part of the restore process.
However, if you want to use something like Amazon RDS, which might prove to be cheaper than paying for a secondary server (RDS is part of AWS’ free tier - see Fully Managed Relational Database - Amazon RDS Pricing - Amazon Web Services ) and also more scalable should you need more resources in future, then database migration from Virtualmin to such a remote database service is simple too:
mysqldump -h localhost -u virtualmin_user -ppassword db_name | mysql -h aws_ip -u user -ppassword db_name