VestaCP is a free alternative web server management solution that rivals cPanel in its ease of use and cost, however it is not without its limitations, and moving your MySQL database can be irritating. Here is a quick and simple guide to getting your database set up properly on your new server.
Step 1
On your cPanel server, log into phpmyadmin for your selected domain and use the Export tool at the top of the page to download a copy of your database. If it’s a large database, you may want to compress it as a zip file. Either way, you will get a .sql file which contains MySQL queries for restoring your data.
Step 2
In VestaCP, click on the User section on the top-left of the page and make sure you are logged into the correct user for your domain. You may need to hover over the user panel to log out and choose the right user. If you don’t have a designated user for your domain’s database, create a new user by clicking the green plus sign on the left.
Step 3
Once logged in to the correct user for your domain, click on the DB section at the top center of the page to create a new database. Vesta will automatically use the username you are logged in with as a prefix for the database and user. Enter whatever suffix you want for database and user, then created a password and click Add.
Step 4
Open the exported database file you downloaded in step 2 in your favorite text editor. In the first few lines you should see a line regarding the database name. If it is different than the name you just created, then select it and perform a “find and replace” action with your text editor (every text editor should have this feature, check under the Edit menu or try Ctrl-H) and replace it with your new database name. This should replace the old database name.
Step 5
Be sure to update the configuration files for whatever applications are using MySQL that you have migrated. For example, if you are using WordPress, open wp-config.php in your domain’s public_html folder and change the database, password and username located towards the beginning of the file.
Step 6
Visit phpmyadmin for your domain on your vesta server. This should be located at http://yourdomain.com/phpmyadmin unless you made a custom configuration. Alternatively, you can go back to the DB screen in Vesta and click on PHPMYADMIN in the center of the screen. Once in phpmyadmin, click on Import at the top of the page and upload your edited database file.
Step 7
Check your domain to ensure the database is loading correctly.