Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.