It wasn’t until my most recent visit for an upcoming project that things looked, well, a little different. It was mostly the same, but subtle differences meant it was no longer compatible with how I’d been familiar with setting it up.

Therefore this quick post is to capture the old method of setting it up for posterity.

The below steps are to replicate all MySQL databases on another server.

Prepare MySQL .conf files

To allow MySQL to replicate, the servers must be arranged in a hirarchy and logging enabled so each system can keep track and update any changes made.

The .conf files can be found in the following locations:

/etc/my.cnf.d/server.cnf

Or on newer distros:

/etc/mysql/mariadb.conf.d/50-server.cnf

Source/Master Server

On the source server (server you want to copy from), add the following lines below and within the [mysqld] section:

bind-address            = {local ip address},{remote ip address}
server-id               = 1
log_bin                 = /var/log/mariadb/mysql-bin.log

binlog-format = mixed
sync_binlog=1

Replica/Slave Server

On the replica server (server that databases are copied to), add the following lines below and within the [mysqld] section:

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
relay-log               = /var/log/mysql/mysql-relay-bin.log
binlog-format           = mixed
read-only               = 1

On both servers, MySQL requires a restart to pick up the config changes:

systemctl restart mariadb

Create a replication user

A replication needs to be authenticated, rather than using the root user its preferred to create a user that is only able to perform the replication.

On the source server, login to MySQL:

mysql -u root -p

Create the user, and give it replication duties with the following commands:

CREATE USER '{replica_user}'@'{replica_ip}' IDENTIFIED BY '{password}';
GRANT REPLICATION SLAVE ON *.* TO '{replica_user}'@'{replica_ip}';
FLUSH PRIVILEGES;

Copy current databases

Before the replication can begin, we’ll need a copy of the existing data captured within a known point of time. This allows the replica to know where to pick up from where the imported databases left off.

Source Server

If not already, log into mysql :

mysql -u root -p

Lock the databases from being able to be written to:

FLUSH TABLES WITH READ LOCK;

Display the current state of the logfiles while the databases are frozen in time:

SHOW MASTER STATUS;

This will produce something similar to this:

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 |      342 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Make a note of the File and Position values, we’ll need this as a reference point for the replica. I’d recommend saving it as a file for future reference.

Now, open up new SSH session to the source server. Its important that the current MySQL session is kept open and in its locked state.

Take a mysqldump of the entire database:

mysqldump -u root -p --all-databases > mysql_replication.sql

When this completes, return to the original session to return the databases to normal:

UNLOCK TABLES;

Transfer SQL file to Replica

Move the generated mysqldump file to the replica server, I usually transfer the master status txt file too for future reference:

scp mysql_replication.sql mysql_master.txt root@{replica_ip}:/root/

Setup Replication

Import the newly transferred file to the replica’s MySQL:

mysql -u root -p < mysql_replication.sql

Then log in to MySQL:

mysql -u root -p

Time to setup the replication, using the replica user setup previously, and the logfile positions captured during the mysqldump. The SHOW MASTER STATUS; output from above is referenced in this command example:

 CHANGE MASTER TO MASTER_HOST='{source_ip}', MASTER_USER='{replica_user}', MASTER_PASSWORD='{password}', MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=342;

Reference points now in place, start the replication process:

START SLAVE;

You can check it is running with:

SHOW SLAVE STATUS\G;

In the output you’re looking for Slave_IO_Running and Slave_SQL_Running to both be Yes:

With both reporting Yes, the replication is running. All changes made to the source server is automatically pushed to the replica.

Notes

Ports

The two databases communicate over its MySQL port, default is 3306. Ensure that both servers are able to communicate on this port.

Authentication

Immediately after setting up replication, you’ll be able to log in to the replica server as normal. However, if the service/server was to be restarted you may not be able to login as normal.

As this guide replicates everything on the source MySQL, it will also replicate the users and permissions.

Following a replication start, the “old” credentials are still usable But following a service restart, the credentials of the source are the new normal on the replica, and should be used for any future login.

This will also apply to any instances of phpMyAdmin.

A report came in on this issue and a few checks of the domain DNS revealed it was on a platform we use for domains and web hosting.

Not overly familiar with the hosting company from a website standpoint and even less with Joomla, it was time to first fathom out how it works, and them find the problem and fix

At this stage of the initial report there’s not much that can be investigated on the fly as the whole website was offline. The only clue of the website showing a forbidden error when visiting suggests that there is a permissions conflict o the web server. Having access to the files, this could be tested by trying to browse to specific image files by using the directory path as the reference, however every file tried returned a 403 Forbidden error.

Joomla

The file structure had a lot of references to Joomla, and this was found to be an alternative blogging platform to WordPress. During the website’s commission in 2012, it seemed a choice between Joomla and WordPress, however with hindsight it seems the WordPress won the war. Essentially it’s pretty similar with an administration portal and driven by PHP and a MySQL database.

Of consideration from my experiences of WordPress is the number of updates a site will need over a year due to the vulnerabilities that constantly crop up, noting that the site has not been touched in the two years that I’ve been here, it could have been knocked offline as a result of an exploit.

With this is mind, I had to confirm that behind the 403 error was still a functioning website that could be restored.

Restoring to a New Server

Wanting to see if the site was still there and concerned about vulnerabilities, a fresh install of Windows Server 2012 in a VM was created to download the site and test.

In Windows, the Internet Information Services role was added, and the Web Platform Components 5.1 installed on top.

As a Joomla site is PHP and MySQL, I find the quickest way to get these installed and integrated with IIS is to install a new instance of Joomla via the Web Platform Installer, as it will also install all required packages in order to run on a Windows Server.

The WPI is followed to get the MySQL database and other components installed, the opening of the Joomla setup webpage is as far as is needed to go.

Adding the Website

A copy of the public_html files were restored to the emptied C:\inetpub\wwwroot folder so it can be run by the IIS default website,

To get the SQL database restored easily I used HeidiSQL, this is a great alternative to the go to MySQL Workbench CE, especially as I faced incompatibility issues with SQL version numbers and kept receiving “Bad Handshake” errors when connecting with Workbench.

In HeidiSQL, log in via the credentials entered during the initial Joomla setup. Create a new database in the left hand pane and name it the same as the backed up database.

Then with the database highlighted, choose File > Load SQL File, select the backup .sql file and open, when prompted choose to run immediately. The table structure and data will now import to the database.

Next was to create a user for the website to connect to the database, in Tools > User Manager, add a new user and give it access to Global Privileges.

Connecting

With the fundimentals now in place, the last step is to reconfigure the Joomla site so it connects to the newly restored database.

In Joomla, the database connection settings are stored in configuration.php located on in the root folder. Open this file in a text editor and amend the $user and $password fields to match the user created in HeidiSQL.

As in this instance the MySQL server and web server are on the same machine, $host can be changed to ‘localhost’

Now is the moment of truth, open a web browser on the server and navigate to http://localhost , deep breath encouraged.

Result

To my surprise it was actually there, and visibly intact. While the issue is not solved I at least know that I still have something to work with, and can continue working out what went wrong.

This also confirmed that it was indeed a permissions issue on the live web server, but without access to this it was time to raise a support ticket with the web space provider.

Update

Turns out from raising the support ticket the suspicions of an exploit reigned true. The website was blocked from the web by the provider as it was reported for being involved in a phishing attack, and it would have been nice to be notified of this!

A quick clean and a lot of patching later the site is now unblocked and back online.

The biggest change was to change the blogging software to WordPress. Although my previous software (BlogEngine.net) was built on ASP.Net and SQL Server, my current choice of language and database that I am most familiar with, I felt that BE was way to bloated and it felt unrefined for my needs.

So I went with WordPress, it seems most blogs I read use this and they look slick, to I wanted a piece of the action. This meant installing PHP and MySQL on IIS, something I really did not want to do due to the resource hog on my server, but I bit the bullet and ordered more RAM for the server.

So far, so good!