Buy Percona ServicesBuy Now!

Master/slave replication with percona server and partitioning

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Master/slave replication with percona server and partitioning

    Hello,
    I have a percona server 5.7 node and i want to add another node for master/slave replication.
    I want to use partitioning for my Mysql tables, my question is:
    If you replicate (using Mysql replication) from a master with no partitioned tables on a slave with partitioned tables, do the replication will break the partitioning or only the data will be replicated?
    Thanks.

  • #2
    DML events will not break replication, but if you execute DDL statements which are not suitable for partitions replication will break.
    for eg :

    I have created one table on master (without partition) and slave (with partition).

    Master

    5.7.18>show create table t1\G
    *************************** 1. row ***************************
    Table: t1
    Create Table: CREATE TABLE `t1` (
    `id` int(11) NOT NULL,
    `t1_id` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

    5.7.18>

    Slave

    slavenode> show create table t1\G
    *************************** 1. row ***************************
    Table: t1
    Create Table: CREATE TABLE `t1` (
    `id` int(11) NOT NULL,
    `t1_id` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY RANGE (t1_id)
    (PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
    PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
    1 row in set (0.00 sec)

    slavenode>


    Replication will break if I change column t1_id integer type to varchar type.

    Master

    5.7.18>alter table t1 change column t1_id t1_id varchar(10);
    Query OK, 7 rows affected (0.58 sec)
    Records: 7 Duplicates: 0 Warnings: 0

    5.7.18>

    Slave

    slavenode> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 127.0.0.1
    Master_User: repl
    Master_Port: 16031
    Connect_Retry: 60
    Master_Log_File: binlog.000001
    Read_Master_Log_Pos: 3673
    Relay_Log_File: qaserver-05-relay-bin.000003
    Relay_Log_Pos: 3689
    Relay_Master_Log_File: binlog.000001
    Slave_IO_Running: Yes
    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: 1659
    Last_Error: Error 'Field 't1_id' is of a not allowed type for this type of partitioning' on query. Default database: 'test'. Query: 'alter table t1 change column t1_id t1_id varchar(10)'
    Skip_Counter: 0
    Exec_Master_Log_Pos: 3482
    [..]
    Master_TLS_Version:
    1 row in set (0.00 sec)

    slavenode>

    Comment


    • #3
      Interesting, thanks!

      Comment

      Working...
      X