SUSE Conversations


MySQL Replication



By: DamianMyerscough

June 19, 2008 5:33 pm

Reads:217

Comments:2

Rating:0

MySQL Replication

In this article we will look at how to configure MySQL replication. I will assume you have already installed the MySQL package from the SUSE Linux Enterprise DVD/CD. The topics that we will cover in this article are creating a database that will be replicated by a slave server, configuring a master server, configuring a slave server, testing the server to make sure that replication is working correctly.

Creating a database

In this section of the article we will create a database called: “hrDepartment” which will house a table called: “employees” that will hold employee information. The first task is to create the “hrDepartment” database this can be done by executing the ‘create database‘ SQL statement followed by the name of the database as shown in Figure 2.1.

mysql> create database hrDepartment;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| hrDepartment       | 
| mysql              | 
+--------------------+
3 rows in set (0.00 sec)

mysql> 

Figure 2.1: Creating the “hrDepartment” database.

Once you have created the “hrDepartment” database you can create the “employee” table as shown in Figure 2.2.

mysql> USE hrDepartment;
Database changed

mysql> CREATE TABLE employee (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> forename VARCHAR(20),
    -> surname VARCHAR(20),
    -> email VARCHAR(40),
    -> tel VARCHAR(11),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

Figure 2.2: Creating the “employee” table.

Once the “employee” table has been successfully created you can populate the table with fictitious employee details as shown in Figure 2.3.

mysql> INSERT INTO employee VALUE (NULL, "Damian", "Myerscough", "Damian.Myerscough@example.com", "01234567890");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUE (NULL, "Chisa", "Hasegawa", "Chisa.Hasegawa@example.com", "01234567890");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUE (NULL, "Jason", "Myerscough", "Jason.Myerscough@example.com", "01234567890");
Query OK, 1 row affected (0.00 sec)

Figure 2.3: Creating fictitious employee data.

Once you have finished adding the fictitious employee details you can begin configuring the MySQL master replication server.

Configuring Master Server

In this section of the article we will look at configuring the master replication server. The first task that you will need to do is check to see if binary logging has been enabled, this can be done by issuing the mysqladmin command followed by the keyword ‘variables’ as shown in Figure 3.1.

Server1:/etc # mysqladmin  variables
+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| auto_increment_increment        | 1                          |
| auto_increment_offset           | 1                          |
| automatic_sp_privileges         | ON                         |
| back_log                        | 50                         |
| basedir                         | /usr/                      |
....
| locked_in_memory                | OFF                        |
| log                             | OFF                        |
| log_bin                         | OFF                        |
| log_bin_trust_function_creators | OFF                        |
| log_error                       |                            |
....
....

Figure 3.1: Checking to see if “bin-log” is enabled”.

As you can see from Figure 3.1 the variable ‘log_bin’ is set to off you will need to enable this variable by editing the ‘my.cnf’ file located within the /etc directory. The two variables that need to be modified in the ‘my.cnf‘ file are “log-bin” and “server-id”. The “log-bin” variable needs to be uncommented as the ‘my.cnf‘ configuration file has this variable commented out by default. The second variable “server-id” should have the value set to one within the ‘my.cnf‘ configuration file as shown in Figure 3.2. The “server-id” variable must be a unique value throughout your whole replication setup.

...
log-bin=mysql-bin
server-id = 1
...

Figure 3.2: Enabling binary logging.

Once you have modified the ‘my.cnf’ configuration file you will need to restart the MySQL daemon as shown in Figure 3.3. Once you have restarted the MySQL daemon you can login to the MySQL server and issue the “SHOW MASTER STATUS” SQL statement as shown in Figure 3.4.

Server1:/etc # service mysql restart
Restarting service MySQL 
Shutting down service MySQL                                           done
Starting service MySQL                                                done

Figure 3.3: Restarting the MySQL daemon.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Figure 3.4: Checking the status of the master server.

As you can see from Figure 3.4 the master server status now shows the filename of the binary log that MySQL is using also you can see the position of the binary log file. This information is very important to the success of MySQL replication working you will require the binary log filename and position for the slave server later on in this article.

Creating Replication User

In this section of the article we will need to create a replication user on the master server which will allow replication to occur. The SQL statement that you will need to use is “GRANT“, Figure 4.1 shows the whole SQL statement and Table 1 explains each section of the SQL statement.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.25.147.175' IDENTIFIED BY 's3cur3p4ssw0rd';
Query OK, 0 rows affected (0.00 sec)

Figure 4.1: Creating the “replica” user.

SQL Description
GRANT REPLICATION SLAVE This section of the SQL statement sets the privilege type to “REPLICATION SLAVE”.
ON *.* This section of the SQL statement specifies which database and tables to apply these privileges.
TO ‘replica’@’172.25.147.175′ This section of the SQL statement specifies the new users username and where the can connect from.
IDENTIFIED BY ‘s3cur3p4ssw0rd’; This section of the SQL statement sets a password for the user ‘replica’.

Table 1: Figure 4.1 SQL statement explained.

In the SQL statement shown in Figure 4.1 you may be wondering why I specified an IP address instead of a wild card. The reason for this is just to tighten security as this will restrict only connections for the slave server and nowhere else. If you are in a secure environment and you can guarantee the risk of no one tampering with your system you could use a wild card (%).

Gathering Master Data

Once you have created the replication user you will need to gather some information about the master server before configuring the slave server. The first task that you will need to do is lock all the tables using the “FLUSH” SQL statement as shown in Figure 5.1.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Figure 5.1: Locking all tables on the master server.

Once you have locked all the tables no other users will be able to modify or add data until you have release this lock (if you are on a busy server wait until its off peak time). Once you have locked all the tables you will need to display the status of the master server this can be done by executing the “SHOW MASTER STATUS” SQL statement as shown in Figure 3.4. You will need to make reference of the binary log filename and the position as these two pieces of information are required by the slave server.

Once you have made a note of the binary log filename and the position you will need to create a snapshot of the “hrDepartment” database this can be done using the tar command as shown in Figure 5.2. Table 2 explains what each qualifier does that is supplied with the tar command.

Server1:/var/lib/mysql # tar -cjf hrDepartment.tar.bz2 /var/lib/mysql/hrDepartment

Figure 5.2: Compressing the hrDepartment database.

Qualifier Description
c Create a new archive.
j Filter the archive through bzip2.
f Use archive file.

Table 2: tar qualifiers explained.

Once you have created a backup of the “hrDepartment” database you will need to copy it to the slave server using the scp command as shown in Figure 5.3.

Server1:/var/lib/mysql # scp hrDepartment.tar.bz2 root@172.25.147.175:/var/lib/mysql
Password: 
hrDepartment.tar.bz2                                                                                                       100%  945     0.9KB/s   00:00  

Figure 5.3: Copying the hrDepartment backup to the slave server.

Once you have copied the “hrDepartment” archive to the slave server you will need to decompress the “hrDepartment” archive as shown in Figure 5.4.

Server2:/var/lib/mysql # tar jvxf hrDepartment.tar.bz2 
var/lib/mysql/hrDepartment/
var/lib/mysql/hrDepartment/employee.MYD
var/lib/mysql/hrDepartment/employee.MYI
var/lib/mysql/hrDepartment/employee.frm
var/lib/mysql/hrDepartment/db.opt
Server2:/var/lib/mysql # mv var/lib/mysql/hrDepartment .
Server2:/var/lib/mysql # rm -rf var/

Figure 5.4: Decompressing the hrDepartment archive.

As you can see in Figure 5.4 a new ‘var’ directory was created. The ‘var’ directory contains the hrDepartment directory that needs to be move into the /var/lib/mysql directory in order for MySQL to read the “hrDepartment” database. Once you have successfully copied the “hrDepartment” to your slave server and decompress the archive you can unlock the the tables on the master server using the “UNLOCK” SQL statement as shown in Figure 5.5.

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)

Figure 5.5: Unlocking all the tables.

Configuring Slave Server

In this section of the article we will configure the slave server to connect to the master server and replicate the “hrDepartment” database. The first task that we need to do is edit the ‘my.cnf’ configuration file located within the /etc directory, the variable that needs to be modified is “server-id” as this is set to one by default which has already been taken by the master server. The “server-id” variable needs to have a unique value so we will set the “server-id” variable to the number two.

Once you have modified the “server-id” variable you will need to start or restart the MySQL daemon using the service command as shown in Figure 6.1.

Server2:~ # service mysql start
Starting service MySQL                                                done

Figure 6.1: Starting the MySQL daemon.

Once you have restarted the MySQL server you will need to connect as the root user and issue the “CHANGE MASTER TO” SQL statement as shown in Figure 6.2. Table 3 explains what each section of the SQL statement does.

mysql> CHANGE MASTER TO MASTER_HOST='172.25.147.168', MASTER_USER='replica', MASTER_PASSWORD='s3cur3p4ssw0rd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=248;
Query OK, 0 rows affected (0.03 sec)

Figure 6.2: Setting the master replication.

SQL Description
CHANGE MASTER TO This section changes the parameters that the slave server uses for connecting to and communicating with the master server.
MASTER_HOSTNAME=’172.25.147.168′, This section sets the host name of the master server.
MASTER_USER=’replica’, This section sets the user who exists on the master server and has replication privileges.
MASTER_PASSWORD=’s3cur3p4ssw0rd’, This section sets the password for the user replica.
MASTER_LOG_FILE=’mysql-bin.000001′, This sections sets the filename of the binary logs which are stored on the master server.
MASTER_LOG_POS=248; This section sets the position of the binary logs this information is also available on the master server.

Table 3: Figure 6.2 SQL statement explained.

Once you have set the master replication details you will need to issue the “START SLAVE” SQL statement for the server to begin acting as a slave server as shown in Figure 6.3.

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

Figure 6.3: Starting the slave server.

Once you have issue the “START SLAVE” SQL statement you will be able to view the status of the slave server by issuing the “SHOW SLAVE STATUS” SQL statement as shown in Figure 6.4.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 172.25.147.168
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 248
             Relay_Log_File: Server2-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 248
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Figure 6.4: Checking the slaves status.

As you can see the slave replication server is running fine. The two rows that are of most importance are “Slave_IO_Running” and “Slave_SQL_Running” if these two fields report ‘no’ then you will need to check your MySQL log files.

Testing Slave Server

In this section of the article we will test the slave server to see if it is functioning properly. The test that we will perform is to insert some new employee data on the master server as shown in Figure 7.1.

mysql> INSERT INTO employee VALUE (NULL, "Tina", "Martins", "Tina.Martins@example.com", "01234567890");
Query OK, 1 row affected (0.00 sec)

Figure 7.1: Adding a new employee.

Once you have added the new employee to the “employee” table on the master server you can then go to the slave server and see if the new employee has been replicated to the slave server as shown in Figure 7.2.

mysql> select * from employee;

+----+----------+------------+-------------------------------+-------------+
| id | forename | surname    | email                         | tel         |
+----+----------+------------+-------------------------------+-------------+
|  1 | Damian   | Myerscough | Damian.Myerscough@example.com | 01234567890 | 
|  2 | Chisa    | Hasegawa   | Chisa.Hasegawa@example.com    | 01234567890 | 
|  3 | Jason    | Myerscough | Jason.Myerscough@example.com  | 01234567890 | 
|  4 | Tina     | Martins    | Tina.Martins@example.com      | 01234567890 | 
+----+----------+------------+-------------------------------+-------------+
4 rows in set (0.00 sec)

Figure 7.1: Adding a new employee.

As you can see from Figure 7.2 the employee ‘Tina’ was replicated to the slave server. This is a clear sign that replication is functioning correctly.

Final Thoughts

In this article we have looked over the basics of setting up MySQL replication. I recommend that you visit the MySQL website to find more about replication and what other features replication can provide.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Tags:
Categories: SUSE Linux Enterprise Server, Technical Solutions

Disclaimer: As with everything else at SUSE Conversations, this content is definitely not supported by SUSE (so don't even think of calling Support if you try something and it blows up).  It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test, test, test before you do anything drastic with it.

2 Comments

  1. By:jmarton

    Instead of typing the changer master to statement, you can include the following parameters in my.cnf on the slave:

    master-host =
    master-user =
    master-password =

    If the master is running on a port other than the default of 3306, you can tell the slave the correct port:

    master-port =

    Another important tidbit about show slave status is verifying that it is replicating data. You can verify this by looking at these paramters:

    Master_Log_File:
    Exec_Master_Log_Pos:

    The Master_Log_File should correspond to the current binary log file in use by the master. If multiple files exist, the one with the highest number is the current one. Also the Exec_Master_Log_Pos value should increase as data as being inserted/updated/deleted on the master as this parameter shows the actual position within the master’s binary log that the slave is currently executing. Eventually this value will reset if the master begins writing to a new binary log file.

    Here’s another interesting item with replication that may prove useful. Let’s say you have a specific table you don’t want to replicate for whatever reason. In the slave’s my.cnf you can specify which tables to ignore during replication. In the above example of a database named hrDepartment, let’s say there is a table called ConfidentialInfo you don’t want to replicate. Here is the line you would use to have the slave ignore this table:

    replicate-ignore-table=hrDepartment.ConfidentialInfo

    You would simply add one line for each table you don’t wish to replicate.

    Hopefully this helps to clarify MySQL replication.

  2. By:DamianMyerscough

    Hello jmarton,

    I would not recommend writing the master-host, master-user and master-password straight away. I would recommend testing to see if replication is working correctly before committing the changes to the my.cnf file, you can check to see if the replication is working by entering the master-host etc manually within MySQL.

Comment

RSS