Accueil > Bases de données, Tutoriel > MySQL Master / Slave Replication

MySQL Master / Slave Replication

29/01/2016 Categories: Bases de données, Tutoriel Tags: , , ,

Source: Uptime Made Easy

Master Slave MySQL Replication Summary

Master / Slave replication in MySQL is a great way to store an exact replica of your database on another machine in another location as part of a disaster recovery plan.  Before setting up Master / Slave replication there are a few things to remember.

  • Writes – Writes to the master database should make it to the slave.  But writes to the slave will not make it to the master.  If you do write records to the slave database directly, be prepared to have to either recreate the records or back them up separately and recover them if the replication breaks.  Many times the only way to get the databases to replicate again is to backup the master and recover it over the top of the slave deleting anything that was in the slave database before.
  • Broken Replication – Writes made directly to the slave can cause the replication to break due to duplicate key rows, etc..  Always write to the master.
  • Reads – Reads should be possible from either server.  Many organizations will use replication so as to create another database to read from thereby taking the load of all of their select statements and reports off the master server.

 

MySQL Replication schemeMaster Slave MySQL Replication

Steps to Setup MySQL Master / Slave Replication

Prerequisites

We will be assuming that the following prerequisites are done prior to beginning the steps listed below:

  • MySQL has been installed on both the master and the slave servers
  • The slave server is able to communicate directly to the mysqld port (typically 3306) on the master server, meaning that there is no firewall, routing, NAT or other problems preventing communication.
  • You have an administrator MySQL user that can create users on both the master and the slave machines.
  • You have permissions to edit the /etc/my.cnf files on both machines and enough privileges to restart mysql.

That should be it!  Let’s begin setting it up.

Steps to Setup MySQL Master / Slave Replication

Step 1 – Create a user on the master with replication slave rights for the slave user to login and get the logged updates.

mysql> grant replication slave on *.* to ‘repslave’@'<ipaddress-of-slave-machine>’ identified by ‘password’;

Step 2 – On the master server, edit the /etc/my.cnf file, restart mysqld, and verify the status of the master server:

Edit the /etc/my.cnf file and add these lines under the [mysqld] region:

server-id=1
log-bin=slave_1log
binlog-do-db=myimportantdb

restart the mysqld service:

# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Verify the status of the master server:

mysql> show master status;
+———————————————————+——————————+———————————————+——————————————————+
| File              | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
+———————————————————+——————————+———————————————+——————————————————+
| slave_1log.000002 | 106      | myimportantdb |                  |
+———————————————————+——————————+———————————————+——————————————————+
1 row in set (0.00 sec)

Step 3 – Create the database on the master server.  If the database already exists, then create a backup and copy it to the slave server.

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

Dump the database if it existed:

mysqldump -uroot -p –routines –singletransaction myimportantdb > myimportantdb.sql

Copy the database to the slave machine:

scp myimportantdb.sql <userid>@<mysqlslave-ip-addresss>:~/

Step 4 – Create the database on the slave server and restore the database if we backed it up in step 3 above.

create the database

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

restore the database if we backed it up in step 3 above:

mysql -uroot -p myimportantdb < ./myimportantdb.sql

Step 5 – On the slave server, edit the /etc/my.cnf file and restart mysqld, and verify the status of the slave server:

Edit the /etc/my.cnf file on the slave server and put the following under [mysqld] section:

server-id=2
master-host=<put master server’s FQDN or IP here>
master-user=repslave
master-password=<put the repslave’s mysql password here>

Restart the mysqld on the slave server:

# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Check the slave status:

mysql> show slave status;
+———————————-+——————–+————-+————-+—————+——————-+———————+————————-+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+
| Slave_IO_State                   | Master_Host        | Master_User | Master_Port | Connect_Retry | Master_Log_File   | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+———————————-+——————–+————-+————-+—————+——————-+———————+————————-+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+
| Waiting for master to send event | uptimemadeeasy.com | repslave    |        3306 |            60 | slave_1log.000001 |                 106 | mysqld-relay-bin.000004 |           252 | slave_1log.000001     | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 106 |             408 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |
+———————————-+——————–+————-+————-+—————+——————-+———————+————————-+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+
1 row in set (0.00 sec)

If the slave status reported an error like the following, then there is likely a problem with your firewall(s) or other connectivity issue blocking the slaves communication to the master:

error connecting to master ‘repslave@<slavehostname.com>:3306’ – retry-time: 60  retries: 86400

If there were no errors, then we should be replicating just fine and We are UP!!

Test the MySQL Master / Slave Replication

To test the replication, we will create a test table and some data in the table and see if they make it to the slave server.  On the master server do:

mysql> create table myimportanttable ( importantnum int, importantchar varchar(20) );
Query OK, 0 rows affected (0.01 sec)mysql> insert myimportanttable values ( 1, ‘some text here’ );
Query OK, 1 row affected (0.00 sec)

Now, lets verify that the table and data were replicated.  On the slave server do:

mysql> select * from myimportanttable;
+——————————————+————————————————+
| importantnum | importantchar  |
+——————————————+————————————————+
| 1            | some text here |
+——————————————+————————————————+
1 row in set (0.00 sec)

You can see that our table and data was replicated without any issues.  Our MySQL Master / Slave Replication is complete.

Print Friendly, PDF & Email

Related Post

Les commentaires sont fermés.