MySQL – Master-Slave Configuration

MySQLreplication

Sorry for the wonky title if it got you confused.

I want to know if a server can be a slave and master at the same time. Our problem is that we have lots of mobile units that need to be synced to the master but they only need 6 out of the 100s of tables on the master. All the extra tables serve no purpose on the slave except for delaying synchronization and adding data costs.

Desired Configuration:

Server A: Master to Server B.

Server B: Slave to Server A, Master to Server C.

Server C(n): Slave to Server B.

Server A: masterSchema, slaveSchema.

slaveSchema is a sync'd subset of masterSchema.

Server B(n): Slave to Server A.slaveSchema

If the answer is no would anyone have any alternate solutions to propose. We're trying to avoid having to sync the different schemas/databases manually as that can get real ugly real fast. We have a lot of critical live data flying around so both performance and integrity are of concern.

Best Answer

Actually both configurations are possible.

Remarks to your ideas

For configuration 1, just set up the first Master->Slave replication. See that you should activate --log-slave-updates on the slave so that the slave creates an appropriate binary log for the propagation of the next slave in the chain. Then set up the new slave with the previous slave as a master. This should be straight forward.

For configuration 2 you can use --replicate-do-table (or --replicate-wild-do-table) or --replicate-ignore-table options on the slave. This alters the behaviour of the slave executing received binary logs. Another way is to use --binlog-do-db or --binlog-ignore-db on the master. This alters the logged binary statements on the master directly. There are some pitfalls thought when using. You might want to read the manual yourself (basically it is unsafe to use the --replicate...--db or --binlog...--db options, because they may have unintended side effects).

These problems are all mentioned in this blog post of Percona too. Since there are no --binlog-do-table options, one does not have the chance to safely restrict the written statements on the master. In this setting the whole binary log would need to be transferred to all n slaves.

Another solution

They do propose a different solution for cases when there should become n slaves propagated, but only with a partial binary log. Setting up a replication chain like you did in configuration 1, but setting the storage engine to all tables on the first slave to BLACKHOLE. BLACKHOLE is just /dev/null, so all written data will be immediately lost. This allows to replicate everything from the first master to the first slave. Then the replication rules from configuration 2 (--replicate-ignore-table) are applied on the slave. All other clients are slaves from this "filter-slave". So the first slave is just responsible for filtering the binary log and propagating this log to (maybe) a lot of slaves. Thus the full binary log must be transferred only once, and not to all of the n slaves.

Maybe the last configuration is something you want to take into account.

STEP01 : Activate Binary Logging on the Old Server

Step01-a) Add this to /etc/my.cnf on the Master

[mysqld]
server-id=101120
log-bin=mysql-bin

Step01-b) # service mysql restart

After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql

STEP02 : Perform mysqldump

On the Master, you can mysqldump the data and record from what point in time it happened.

# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart

What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:

# head -22 MySQLData.sql | tail -1

STEP03 : Load the mysqldump into the Slave

Execute the mysql client loading the mysqldump into the Slave's mysql instance

# mysql -h10.1.1.30 -uroot -p < MySQLData.sql

STEP04 : Create MySQL Replication User on the Master

# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"

STEP05 : Setup the Slave with a Separate Server ID

Add this to /etc/my.cnf on the Slave

[mysqld]
server-id=101130

and # service mysql restart

STEP06 : Setup the Replication on the Slave

Goto the mysql client and run the following command

mysql> CHANGE MASTER TO
    MASTER_HOST='10.1.1.20',
    MASTER_PORT=3306,
    MASTER_USER='replicator',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1;

STEP07 : Setup the point-in-time Master Log and Position

Back in STEP02, I mentioned viewing the point-in-time position using

# head -22 MySQLData.sql | tail -1

You should see something like this:

#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Run it as command in the mysql client on the Slave

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Afterwards, run this command

mysql> SHOW SLAVE STATUS\G

You should see something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.113.232
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 122957100
               Relay_Log_File: relay-bin.003666
                Relay_Log_Pos: 122957100
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 106
              Relay_Log_Space: 106
              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)

STEP08 : Launch Replication

Start up replication with this:

mysql> START SLAVE;

Afterwards, run this command again

mysql> SHOW SLAVE STATUS\G

If you see this

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

CONGRATULATIONS, MySQL Replication is Working !!!