You can transfer MySQL data to a different server and continue to use it from the primary OpenPanel server, where Docker containers are operating, if you have a large number of users—with millions of domains and websites.
To use a separate server for MySQL only, follow these steps:
UPDATE: Starting OpenPanel 0.1.7 all mysql data is stored inside a docker volume openpanel_mysql_data. To use remote mysql, run the docker container with that volume on a new server and just edit the login information in /etc/my.cnf file.
1. Whitelist servers
On the MySQL server first whitelist the IP address of the OpenPanel server, then on OpenPanel server whitelist the MySQL server IP address.
ufw allow from IP_ADDRESS_HERE
2. Prepare the MySQL server
If MySQL is not installed on the server, first install it:
apt-install mysql-server -y
Then edit the configuration file and in it replace bind-address= 127.0.0.1 with bind-address=0.0.0.0
nano /etc/mysql/mysql.conf.d/mysqld.cnf
save the file and exit. Then restart mysql service to apply changes:
service mysql restart
Create the MySQL database, new user and allow it access to that database.
In this example, I will use panel for username, panel for database name and nXJlftnqImdlSULl as a password.
- Create database:
mysql -u root -e "CREATE DATABASE panel;"
- Create new user:
mysql -u root -e "CREATE USER 'panel'@'%' IDENTIFIED BY 'nXJlftnqImdlSULl';"
- Assign user to the database:
mysql -u root -e "GRANT ALL PRIVILEGES ON panel.* TO 'panel'@'%';"
On OpenPanel sever export database, transfer the file to the new MySQL server and import it:
- On old server export database:
mysqldump -u root -p panel > dbexport.sql
- Then copy the file to the new server.
- And import the file on the new server:
mysql -u root panel < dbexport.sql
3. Test the connection from the OpenPanel server to the MySQL server:
mysql -u MYSQL_USER_HERE -p'PASSWORD_HERE' -h IP_ADDRESS_HERE -P 3306 -D DATABASE_NAME_HERE
example:
mysql -u panel -p'nXJlftnqImdlSULl' -h 18.19.90.29 -P 3306 -D panel
if connection is successful, next step is to change the OpenPanel configuration to use the new database.
4. Edit OpenPanel configuration to use the new server:
Edit the json file and change localhost to IP address and logins:
nano /usr/local/admin/config.json
{
"mysql_user": "panel",
"mysql_password": "nXJlftnqImdlSULl",
"mysql_host": "localhost",
"mysql_database": "panel"
}
Edit the .cnf file and also replace localhost with IP address and set the new logins:
nano /usr/local/admin/db.cnf
[client]
user = "panel"
password = "nXJlftnqImdlSULl"
host = "localhost"
save the files and restart OpenPanel services to apply changes.
service panel restart
service admin restart
That's it! Test both the OpenAdmin and OpenPanel interfaces and everything should be working as expected.