The Construction method of Mysql 5.6Database-level Table-level replication

The main content of this article is to explain "the construction method of Mysql 5.6database-level table-level replication". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "Mysql 5.6database-level table-level replication building method" it!

0. Architecture information

Main library: 192.168.56.100

Slave library: 192.168.56.200

1. Main library operation

Shut down the database

[root@localhost test] # mysqladmin-uroot-p shutdown

Enter password:

Modify the configuration file

[root@localhost test] # vim / etc/my.cnf

[mysqld]

Server-id=100

Log-bin=/var/lib/mysql/mysqld-bin

Binlog_format=row

# replicated database

Binlog-do-db=test

Start the database

[root@localhost test] # mysqld_safe-- defaults-file=/etc/my.cnf &

You can view the database to be replicated with the following command

[root@localhost test] # mysql-uroot-p

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

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysqld-bin.000003 | 120 | test | |

+-+

1 row in set (0.00 sec)

Create replication user

Mysql > GRANT REPLICATION SLAVE ON *. * to 'repliform'@'%' identified by' repliform'

Query OK, 0 rows affected (0.00 sec)

two。 Operation from the library

Shut down the database

[root@localhost tmp] # mysqladmin-uroot-p shutdown

Enter password:

Add replication parameters to the configuration file

[root@localhost tmp] # vim / etc/my.cnf

[mysqld]

Server-id=200

Log-bin=/var/lib/mysql/mysqld-bin

Binlog_format=row

# apply replicated tables

Replicate_do_table= test.emp

Replicate_do_table= test.dept

Configure replication

Mysql > CHANGE MASTER TO

-> MASTER_HOST='192.168.56.100'

-> MASTER_PORT=3306

-> MASTER_USER='repliform'

-> MASTER_PASSWORD='repliform'

-> master_log_file='mysqld-bin.000001'

-> master_log_pos=120

Query OK, 0 rows affected, 2 warnings (0.06 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.56.100

Master_User: repliform

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File: localhost-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysqld-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table: dept.dept,dept.emp

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 120

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: NULL

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: 0

Master_UUID:

Master_Info_File: / var/lib/mysql/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:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.100

Master_User: repliform

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqld-bin.000001

Read_Master_Log_Pos: 901

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 1065

Relay_Master_Log_File: mysqld-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table: test.dept,test.emp

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 901

Relay_Log_Space: 1242

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: 100

Master_UUID: 04ebf096-10cf-11e6-8077-080027e76b2b

Master_Info_File: / var/lib/mysql/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:

Auto_Position: 0

1 row in set (0.00 sec)

3. Test synchronization effect

Insert data on top of the main library

Mysql > insert into emp values (60), (70)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > insert into dept values (60), (70)

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Check the synchronization effect from the library.

Mysql > select * from test.emp

+-+

| | id |

+-+

| | 10 |

| | 20 |

| | 30 |

| | 40 |

| | 50 |

| | 60 |

| | 70 |

+-+

7 rows in set (0.00 sec)

Mysql > select * from test.dept

+-+

| | deptno |

+-+

| | 10 |

| | 20 |

| | 30 |

| | 40 |

| | 50 |

| | 60 |

| | 70 |

+-+

7 rows in set (0.00 sec)

At this point, I believe you have a deeper understanding of the "Mysql 5.6database-level table-level replication method". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!