[MySQL]: Filtering replication by Column is possible ?

August 10, 2015 · by dbversity · in MySQL

We can restrict table level replication with below parameters in MySQL

Solutions for column level are …
1) Custom script
• Export data with required fields to a csv from Master server ( with SELECT INTO OUTFILE syntax)
• Create table with required fields on the slave sever
• Load the exported csv file to Slave server table (We can do incremental load/full load as per the feasibility and requirement)
• Add script to cronjob with the required frequency.

2) Create a view with required columns and give read privileges to a user to the view on slave.
[ root @ dbversity1host : /var/lib/mysql ] grep “replicate” /etc/my.cnf
replicate-do-db = my_db
replicate-ignore-table = my_db.my_tbl
replicate-do-db = my_dbversity_db
replicate-ignore-table = my_dbversity_db.my_dbversity_tbl
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]

[ root @ dbversity1host : /var/lib/mysql ] service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[ root @ dbversity1host : /var/lib/mysql ]
At Master run below commands.

create database my_dbversity_db;
use my_dbversity_db

create table my_dbversity_tbl (
id int(10),
name varchar(30),
gender varchar(10),
email varchar(50),
phone varchar(50),
pin varchar(20),
ssn varchar(20),
state varchar(30),
country varchar(20)
);

insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 1, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 2, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 3, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 4, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 5, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 6, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 7, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 8, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 9, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 10, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
create view my_dbversity_tbl_view as select id,name,state from my_dbversity_tbl;
create table test (
id int(10),
name varchar(30),
gender varchar(10),
email varchar(50),
phone varchar(50),
pin varchar(20),
ssn varchar(20),
state varchar(30),
country varchar(20)
);
create table new_tbl1 (
id int(10),
name varchar(30),
state varchar(30));
At Master :
mysql> create database my_dbversity_db;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> use my_dbversity_db
Database changed
mysql>
mysql> create table my_dbversity_tbl (
-> id int(10),
-> name varchar(30),
-> gender varchar(10),
-> email varchar(50),
-> phone varchar(50),
-> pin varchar(20),
-> ssn varchar(20),
-> state varchar(30),
-> country varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 1, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
country ) values ( 2, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 3, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 4, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 5, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 6, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 7, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 8, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 9, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 10, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );Query OK, 1 row affected (0.00 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 2, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 3, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 4, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 5, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.00 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 6, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 7, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.00 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 8, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 9, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_dbversity_tbl (id, name, gender, email, phone, pin, ssn, state, country ) values ( 10, ‘srini’ , ‘M’, ‘sri@dbversity.com’, ‘9876543210’, ‘533250’, ‘987654232’, ‘MH’, ‘INDIA’ );
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql>
mysql> create table test (
-> id int(10),
-> name varchar(30),
-> gender varchar(10),
-> email varchar(50),
-> phone varchar(50),
-> pin varchar(20),
-> ssn varchar(20),
-> state varchar(30),
-> country varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)

mysql> create view my_dbversity_tbl_view as select id,name,state from my_dbversity_tbl;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> select * from my_dbversity_tbl_view;
+——+——-+——-+
| id | name | state |
+——+——-+——-+
| 1 | srini | MH |
| 2 | srini | MH |
| 3 | srini | MH |
| 4 | srini | MH |
| 5 | srini | MH |
| 6 | srini | MH |
| 7 | srini | MH |
| 8 | srini | MH |
| 9 | srini | MH |
| 10 | srini | MH |
+——+——-+——-+
10 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> create table my_dbversity_tbl_bkp as select id,name,state from my_dbversity_tbl;
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql>
mysql>
mysql> select * from my_dbversity_tbl_bkp;
+——+——-+——-+
| id | name | state |
+——+——-+——-+
| 1 | srini | MH |
| 2 | srini | MH |
| 3 | srini | MH |
| 4 | srini | MH |
| 5 | srini | MH |
| 6 | srini | MH |
| 7 | srini | MH |
| 8 | srini | MH |
| 9 | srini | MH |
| 10 | srini | MH |
+——+——-+——-+
10 rows in set (0.00 sec)

mysql> create table new_tbl (
-> id int(10),
-> name varchar(30),
-> state varchar(30));
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> create table new_tbl1 (
-> id int(10),
-> name varchar(30),
-> state varchar(30));
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql>
mysql>
mysql>
mysql> insert into new_tbl1 select id,name,state from my_dbversity_tbl;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql>
mysql>
mysql>

At Slave :

mysql> stop slave; CHANGE MASTER TO MASTER_HOST=’10.42.105.100′, MASTER_LOG_FILE = ‘mysql-bin.000003’, MASTER_LOG_POS = 18766; start slave; show slave status\G
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
Slave_IO_State: Checking master version
Master_Host: 10.42.105.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 18766
Relay_Log_File: dbversity1host-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: my_db,my_dbversity_db
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: my_dbversity_db.my_dbversity_tbl,my_db.my_tbl
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 18766
Relay_Log_Space: 120
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: a6adc565-35c5-11e5-ac29-005056b560fc
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: cc3a3014-1e36-11e5-928c-005056008cb1:1-1418
Auto_Position: 0
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.42.105.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 18766
Relay_Log_File: dbversity1host-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: my_db,my_dbversity_db
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: my_dbversity_db.my_dbversity_tbl,my_db.my_tbl
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 18766
Relay_Log_Space: 463
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: a6adc565-35c5-11e5-ac29-005056b560fc
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: cc3a3014-1e36-11e5-928c-005056008cb1:1-1418
Auto_Position: 0
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql>

[ root @ dbversity1host : /var/lib/mysql ] grep “replicate” /etc/my.cnf
replicate-do-db = my_db
replicate-ignore-table = my_db.my_tbl
replicate-do-db = my_dbversity_db
replicate-ignore-table = my_dbversity_db.my_dbversity_tbl
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ]
[ root @ dbversity1host : /var/lib/mysql ] mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.25-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.42.105.100
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 18766
Relay_Log_File: dbversity1host-relay-bin.000002
Relay_Log_Pos: 445
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: my_db,my_dbversity_db
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: my_dbversity_db.my_dbversity_tbl,my_db.my_tbl
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error ‘Table ‘my_dbversity_db.my_dbversity_tbl’ doesn’t exist’ on query. Default database: ‘my_dbversity_db’. Query: ‘insert into new_tbl1 select id,name,state from my_dbversity_tbl’
Skip_Counter: 0
Exec_Master_Log_Pos: 18500
Relay_Log_Space: 891
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error ‘Table ‘my_dbversity_db.my_dbversity_tbl’ doesn’t exist’ on query. Default database: ‘my_dbversity_db’. Query: ‘insert into new_tbl1 select id,name,state from my_dbversity_tbl’
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: a6adc565-35c5-11e5-ac29-005056b560fc
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 150810 07:09:54
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: cc3a3014-1e36-11e5-928c-005056008cb1:1-1418
Auto_Position: 0
1 row in set (0.00 sec)

mysql>
c

Tags: [MySQL]: Filtering replication by Column is possible ?

[MySQL]: Filtering replication by Column is possible ?

Leave a Reply

Categories

Categories