MySQL replication is the process by which a single data set, stored in a MySQL database, will be live-copied to a second server. This configuration, called "master-slave" replication, is a typical setup. Our setup will be better than that, because master-master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform mysql read or writes from either server.
The examples Server A and Server B.
Server A: 10.0.0.1
Server B: 172.16.31.1
Step 1 - Install and Configure MySQL on Server A
The first thing we need to do is to install the mysql-server and mysql-client packages on our server. We can do that by typing the following:
sudo apt-get install mysql-server mysql-client
We need to edit /etc/mysql/my.cnf on Server A. There are four lines that we need to change, which are currently set to the following:
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#binlog_do_db = include_database_name
bind-address = 127.0.0.1
We need to uncomment that line, by removing the "#" before it. The second line indicates the file in which changes to any mysql database or table will be logged.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = example
# bind-address = 127.0.0.1
Now we need to restart mysql:
sudo service mysql restart
We can get to our root mysql user by typing the following:
mysql -u root -p
mysql>
Once we are logged in, we need to run a few commands.
create user 'harish'@'%' identified by 'password';
Next, we need to give this user permissions to replicate our mysql data:
grant replication slave on *.* to 'harish'@'%';
We will need to make note of:
show master status;
The output will looking similiar to the following, and will have two pieces of critical information:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | example | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
We need to make a note of the file and position which will be used in the next step.
Step 2 - Install and Configure MySQL on Server B
We need to repeat the same steps that we followed on Server A. First we need to install it, which we can do with the following command:
sudo apt-get install mysql-server mysql-client
We will start by editing the /etc/mysql/my.cnf file.
sudo nano /etc/mysql/my.cnf
We need to change the same four lines in the configuration file as we changed earlier.
The defaults are listed below, followed by the changes we need to make.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#binlog_do_db = include_database_name
bind-address = 127.0.0.1
We need to change these four lines to match the lines below. Please note, that unlike Server A, the server-id for Server B cannot be set to 1.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = example
# bind-address = 127.0.0.1
After you save and quit that file, you need to restart mysql:
sudo service mysql restart
It is time to go into the mysql shell and set some more configuration options.
mysql -u root -p
Replace "password" with the password you wish to use.
create user 'harish'@'%' identified by 'password';
Next, we need to create the database that we are going to replicate.
create database example;
And we need to give our newly created 'replication' user permissions to replicate it.
grant replication slave on *.* to 'harish'@'%';
The next step involves taking the information that we took a note of earlier and applying it to our mysql instance.
slave stop;
CHANGE MASTER TO MASTER_HOST = '10.0.0.1', MASTER_USER = 'harish', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
slave start;
You need to replace 'password' with the password that you have chosen for replication. Your values for MASTER_LOG_FILE and MASTER_LOG_POS may differ than those above. You should copy the values that "SHOW MASTER STATUS" returns on Server A.
The last thing we have to do before we complete the mysql master-master replication is to make note of the master log file and position to use to replicate in the other direction (from Server A to Server B).
We can do that by typing the following:
SHOW MASTER STATUS;
The output will look similiar to the following:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 107 | example | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Take note of the file and position, as we will have to enter those on server C, to complete the two-way replication.
The next step will explain how to do that.
Step 3 - Completing Replication on Server A
Back on Server A, we need to finish configuring replication on the command line. Running this command will replicate all data from Server B.
slave stop;
CHANGE MASTER TO MASTER_HOST = '172.16.31.1', MASTER_USER = 'harish', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 107;
slave start;
Keep in mind that your values may differ from those above. Please also replace the value of MASTER_PASSWORD with the password you created when setting up the replication user.
The output will look similiar to the following:
Query OK, 0 rows affected (0.01 sec)
The last thing to do is to test that replication is working on both VPS. The last step will explain an easy way to test this configuration.
Step 4 - Testing Master-Master Replication
We are going to test it now. To do this, we are going to Create/Delete a table in our example database on Server A and check on Server B to see if it shows up.
We now need to create the database that will be replicated between the servers. We can do that by typing the following at the mysql shell:
create database example;
Once that's done, let's create a dummy table on Server C:
create table example.dummy (`id` varchar(10));
We now are going to check Server D to see if our table exists.
show tables in example;
We should see output similiar to the following:
+-------------------+
| Tables_in_example |
+-------------------+
| micra |
+-------------------+
1 row in set (0.00 sec)
The last test to do is to delete our dummy table from Server B. It should also be deleted from Server A.
We can do this by entering the following on Server B:
DROP TABLE mica;
To confirm this, running the "show tables" command on Server C will show no tables:
Empty set (0.00 sec)
Note - Any Query please post happy to help.
No comments:
Post a Comment