Magento Replication: Master-Slave Installation

What is Magento replication?

Replication is the creation of exact copies of certain files which are supposed to exchange data. Correspondingly, replications are the copies of system files, the files of themes and extensions for Magento ®, etc.

Magento ® platform can properly resist the database replication which is not always necessary for a web store. But if you use it to correct some customers’ data or to manage your database remotely, then it is a very useful option.

There are several replication types in Magento ®:

1. Bag replication;

2. Programmed replication;

3. Replication with the help of third-party software (this type is almost never used in Magento ® or used very rarely).

How to configure replication

The replication configuration in Magento ® is performed with the help of a Master-Slave installation for a web store. This is very convenient due to the easiness of many performance options. Also, it is necessary to mention that, when Master-Slave is used, the Magento ® system sends waiting for queries to all servers while correct queries are sent to the database.

Database ranging in Magento ® is quite a difficult task. If you wish to execute it without having corresponding skills and experience it would be better to consult with the experts in Magento ®.

Preparing for replication

First of all, you need to configure two MySQL servers. You will also need a hostname, IP address, username, and password.

Preparing for configuration:

1. Open file my.cnf which is in /etc/mysql directory (this only works for those who use Debian distribution, e.g. Ubuntu). In case you work with XAMPP you will need the file my.ini from xampp/bin directory.

2. Now you need to find the part of the code you see below and comment it (put a # sign at the beginning), thus you will open access to your MySQL server for other networks:

#bind-address = 127.0.0.1

3. Paste the following code below [mysqld] section; you can change the parameters to the ones you need:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = magentodb

4. Then restart MySQL server. For Debian distribution the command looks like this:

sudo /etc/init.d/mysql restart

5. After that re-login to MySQL console:

sudo mysql -u root -p

6. And now in MySQL shell enter the following commands:

GRANT ALL PRIVILEGES ON `magentodb` . * TO 'slave_user’@’%’ WITH
GRANT OPTION IDENTIFIED BY 'slave_password’;
FLUSH PRIVILEGES;
QUIT;

7. Re-login to MySQL root shell, enter the following commands and check the work of Master-Slave:

USE magentodb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

8. You will see a black screen with some white text on it;

9. Save the data that you see, you will need it to work with Master-Slave;

10. Use the command below in MySQL shell to unlock database tables:

UNLOCK TABLES;
quit;

11. That’s it, the main part of the job is finished.

Now we configure Master-Slave:

1. Go to the root directory of Master-Slave by entering the following command in its interface:

mysql -u root –p

2. Now create a new database magentodb and import the database which is being replicated into your new database;

3. Open file my.cnf or my.ini from folders /etc/mysql or xampp/mysql/bin. Add [mysqld] above the following code. Then you can change the values according to your server requirements:

[mysqld]
server-id = 2
master-host = 192.168.3.12
master-user = slave_user
master-password = slave_password
master-connect-retry = 60
replicate-do-db = magentodb

4. Re-start MySQL server again:

/etc/init.d/mysql restart

5. Go to the root panel of Slave MySQL by entering the following in its interface:

mysql -u root -p

6. Now enter the following command in MySQL. You need to use the file master log and the position ‘master log’ in the same way as in the server settings:

CHANGE MASTER T
O MASTER_HOST=’192.168.3.12’, MASTER_USER=’slave_
user’, MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE=’mysqlbin.
000001’, MASTER_LOG_POS=88;

7. Now start the server on which the replications will be stored:

START SLAVE;

8. Check the status of Slave using the command below:

SHOW SLAVE STATUS \G

9. You will see a black screen with white text again.

Now your configuration of Magento ® replication is complete. If you have any additional questions please feel free to contact GoMage specialists directly.