How to Configure MySQL 5.1 Master Slave Database Replication

Overview

A master-slave configuration allows you to maintain a secondary copy of your application’s data on a separate database server. The difference between a master server and a slave server is that only the master is permitted to write changes to a replicated database. The slave server will only have read access to it.

Having a read-only copy on your slave server may seem limiting at first, and you may be wondering “How does this help me if my application still goes down when the master database server goes offline?” The goal here isn’t high availability, but it is to decrease your recovery time as much as possible. We now have a complete and live database set available without having to restore from backups. The slave database server can then be promoted to a master database server for the replicated database copies, or we can replicate from the slave to a third server that will then host the databases as a master.

Lab Configuration

To make the tutorial easier to follow, the following configurations were used.

Hostname	Role	Public IP	Private IP
db01.serverlab.intra	Master Server	64.54.200.32	10.2.0.15
db02.serverlab.intra	Slave Server	64.54.200.33	10.2.0.16

Configure the Master Server

The first few tasks will be creating the database that will be replicated to our slave and to configure the server to act as the master.

Configure Server as a Master

In order for the server to behave as a master server we need to add a few lines to MySQL configuration.

Open the MySQL configuration file into a text editor.
```
nano /etc/my.cnf
```
Under the [mysqld] section of the configuration file, add the following lines.
```
server-id=1
binlog_do_db=newapp1
```
Save your changes and exit the text editor.

Create Replication Service Account

We need to create an account that will be used by the slave to replicate our database. This account should be for replication only.

Log into the MySQL client.
```
mysql -u root -p
```
Create a service account for the replication process. In this example, we will create an account called repl1.
```
CREATE USER 'repl1'@'db%.serverlab.intra' IDENTIFIED BY 'password';
```

Grant the service account replication privileges.

GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'db%.serverlab.intra';

Flush the privileges to enable the new ones.
```
FLUSH PRIVILEGES;
```
Exit the MySQL client.
```
quit;
```

Create the Database

We start by creating a new database. This database will hold our application’s data.

Log onto the database server using the MySQL client. When prompted, enter your password.
```
mysql -u root -p
```
Create a new database.
```
create database newapp1;
```
Flush the tables and create a read lock.
```
flush tables with read lock;
```
End your MySQL connection.
```
quit;
```
Dump the new database to a file on the server.
```
mysqldump -u root -p newapp1 > newapp1.sql
```
Copy the dump file to the slave server. In this example, the slave server is db02 and we will connect to it using its private IP address.
```
scp newapp1.sql [email protected]:/root/
```

Restart MySQL Services

Restart the services
```
service mysqld restart
```

Record Position of the Binary Log

The binary log is used by the slave to ensure it has the most recent changes replicated to it. For the slave to know where to start, we need to record to current position.

Log into the MySQL client.
```
mysql -u root -p
```
Run the following command.
```
show master status;
```

You should see the following output. Make sure you record the value under the Position column. In our example, the value is 564.

+------------------+----------+------------------+------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000003 |      564 | newdatabase,app1 |                  |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)

Restart the services
```
service mysqld restart
```

Configure the MySQL Slave Server

The following instruction will allow our application’s database to be replicated to our slave server. Our example uses only a single slave servers, however, you can follow these instructions to add several more servers. Each server will replicate from the master.

Configure Server as Slave

Open the MySQL configuration file into a text editor.
```
nano /etc/my.cnf
```
Under the [mysqld] section, add the following lines.
```
server-id=2
```
Save your changes and exit the text editor.
Restart the MySQL services to apply the changes.
```
service mysqld restart
```

Import the Database

Log onto the slave server.
Log into MySQL using the MySQL client.
```
mysql -u root -p
```
Create a database with the same name as the one we created on the master.
```
create database newapp1;
```
Exit MySQL.
```
quit;
```
Import the database dump from the master into the newly created database on the slave.
```
mysql -u root -p newapp1 < newapp1.sql
```

Initialize Replication

Our slave is almost ready to start replication of our application’s database. We now need to configure the connection settings between our slave and the master.

Make sure you have to following information available, as it will be needed to configure the connection. As a review I am listing the values that we entered and recorded from the steps above.

Master Hostname db01.serverlab.intra

Username repl1

Password password

Binary Logfile newapp1

Log Position 564
Execute the change master to command using the information we used above. Remember, the values listed above will be different in your environment. Ours are for demostration only.
```
CHANGE MASTER TO
MASTER_HOST='db01.serverlab.intra',
MASTER_USER='repl1',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='newapp1',
MASTER_LOG_POS=564;
```

Master Hostname	db01.serverlab.intra
Username	repl1
Password	password
Binary Logfile	newapp1
Log Position	564

No related posts found