How to Set Up Replication – MySQL

Posted on Posted in Mysql

After install the Mysql Cluster we only have one pending thing, mount a CDR server in read-only mode that will replicate the information from the primary database. And This is one possible application:

Master Node’s

In the master node’s mysqldb1 and mysqldb2:

Activate the binary log

Activate the binary logs, that contains “events”, describe database changes such as table creation operations or changes to table data. Edit /etc/my.cnf:

.......
[mysqld]
log-bin=mysql-bin
server-id = 1
......
...
.

 Replicator User

We will need a new user, that will be the replication responsible

mysql> grant REPLICATION SLAVE on *.* to 'replicator'@'mysqldbCDR.snaider.dom' identified by '********';

Full Backup

Make a full backup of all Databases, that will to be used to configure the slave server.

[root@mysqldb1]# /usr/local/etc/mysql/bin/mysqldump --defaults-file=/etc/my.cnf -uroot -p --all-databases --master-data > mysql.all.backup

Slave Node

My.cnf configuration

Configure the read-only mode the server-id the log-bin and the relay-log.
Relay-log like log-bin are binary files containing events that describe database changes. I’ll configure with the general syntaxis “mysql-relay-bin” because by default is the name of the server (host_name-relay-bin.nnnnnn)changing a slave’s host name after replication can cause replication to fail with the errors Failed to open the relay log and Could not find target log during relay log initialization.

...
[mysqld]
read_only
log-bin=mysql-bin
server-id = 2
relay-log = mysql-relay-bin
.....
..

Configure master information

Now introduce the information about the master node:

mysql> change master to
-> master_host='mysqldb.snaider.dom',   # HA NAME. because is a cluster
-> master_port=3306,
-> master_user='replicator',            # The replicator user
-> master_password='password';

Check:

mysql> show slave status\G
...
Master_Host: mysqldb.snaider.dom
Master_User: replicator
Master_Port: 3306

Import

Now import the FULL backup

mysql> source /home/mysql/mysql.all.backup;

Check log-bin & Start

Before start the slave, ensure that the log-bin information are correctly loaded in the slave

mysql> show slave status\G
...
Master_Log_File: mysql-bin.000025
Read_Master_Log_Pos: 12598410

If the information appear, start the slave

mysql> start slave\G

Check the Operation

Master

In the Master node, check if the replicator user is connected and the replication process is started

mysql> show processlist;
...
| 584702 | replicator | mysqldbCDR.snaider.dom:42075 | NULL
| Binlog Dump | 1584 | Master
has sent all binlog to slave; waiting for binlog to be updated | NULL

Slave

Check that don’t appear errors in the mysql.log

[root@webunavdb-cdr]# tail -f /var/log/mysql/mysql.log
...
120612 11:23:00 [Note] Slave SQL thread initialized, starting replication in log
'mysql-bin.000025' at position 12598410, relay log './mysql-relay-bin.000006' position: 157117
120612 11:23:00 [Note] Slave I/O thread: connected to master
'replicator@mysqldbCDR.snaider.dom:000025',replication started in log 'mysql-bin.000036' at position
12598410

And the Mysql Slave information:

mysql> show slave status \G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event                   Master_Host: mysqldb.snaider.dom
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000036
          Read_Master_Log_Pos: 12598410
               Relay_Log_File: mysql-relay-bin.016078
                Relay_Log_Pos: 764918
        Relay_Master_Log_File: mysql-bin.000036
            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: 12598410
              Relay_Log_Space: 774773
              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 
               Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
               Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *