Running a web application and database in two different servers in the same datacentre is a good way to scale the environment to handle more traffic and add more uniformity. For this there needs to be a database server (MySQL server installed) that can handle all database functions of the web application server.
To carry out this we need to
- a) Migrate existing database in the web server to new database server
- b) Reconfigure the web application to connect to new database
Migrating Existing Database To Database Server
For this the database server needs to have MySQL database server running in it.To know how to install MySQL server click here
By default the MySQL database is configured to listen to the localhost (127.0.0.1) only. To configure this database server to listen all public addresses that can connect to it, open MySQL configuration for editing:
$ sudo vi /etc/mysql/my.cnf
Edit the line file:
bind-address = 127.0.0.1
Replace 127.0.0.1 with asterisk
bind-address = *
If you’re configuring the database server for only this web-application server, it’s more secure to give the IP address as bind-address. In that case
bind-address = 'web-server-IP'
To apply the changes, save the configuration file and restart the MySQL service.
$ sudo service mysql restart
Export Backup of Original MySQL Database
We need to export a backup of original MySQL database in the web-server, which will be used to migrate to the new database. Before taking the backup of the data, stop the web-application server to prevent attempted updates to the existing database during the migration process.
For this you need to create a data snapshot. There are different methods to create the database snapshot, depending on the size of the database and the location of the files.
- a) Create a snapshot using mysqldump
- b) By copying the data files directly.
Before that flush all the tables and block write statements by executing
mysql > flush tables with read lock;
mysql > SET GLOBAL read only = ON ;
To create data snapshot we’re going to use mysqldump
$ sudo mysqldump -uroot -p web-database > backup.sql
Here web- database being the original MySQL database in the web-server you want to migrate to database server
Copy tkup database file to the database server using scp:
$ sudo scp backup.sql user@database-server:/tmp
Import Original Database Into Database Server
To import the original MySQL web-server database into database server
On Database server,
mysql -u root -p < /tmp/backup.sql
All of the original MySQL database data and users are copied over to the database server. For Web-server to access the database server, the database server needs to have users that have the same privileges as the original ones in web-server.
You will have to create new users with a “host” value that matches the web-server’s IP address.
For this, create a new user with the same name as in the web-server but change its host to the IP address of the web-server. Password should remain the same.
CREATE USER 'inouser'@'web-server-IP’ IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON 'web-database'.* TO 'inouser'@'web-server-IP';
The new database server is now migrated and configured. To connect to the database server, the web application configuration file needs to be updated. The configuration files reside in different locations for different applications. For inoERP application, the configuration file is seen in /var/www/html/inoERP-master/inoerp/includes/basics/settings as dbsettings.inc file.
Open inoERP configuration:
$ sudo vi /var/www/html/inoERP-master/inoerp/includes/basics/settings/dbsettings.inc
Look for the following lines:
Replace localhost with the IP address of the database server.
There’s no need to change the user or password as they were recreated in the database server.
The database migration is complete and the application now should be able to use the MySQL database running in a different server (Database server).
To verify that, access the application using the web browser.