Shulou(Shulou.com)06/01 Report--

-

Installation preparation

Configuration / etc/my.cnf

Master master grant assigns replication account

From slave change; open slave; to view status

Verify consistency

-

When a transaction is executed and committed on the main database side, a GTID is generated and recorded in the binlog log.

After the binlog is transferred to slave and stored in slave's relaylog, read this GTID value and set the gtid_next variable to tell slave the next GTID value to execute. The sql thread gets the GTID from the relay log and then compares whether the binlog on the slave side has the GTID. If there is a record, indicating that the transaction for the GTID has been executed, slave will ignore it. If there is no record, slave executes the GTID transaction, records the GTID to its own binlog, and checks that other session holds the GTID before reading the execution transaction to ensure that it is not repeated. During the parsing process, it will determine whether there is a primary key, if not, use a secondary index, and if not, use a full scan.

-

Mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar

Tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar

Yum install-y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm

/ etc/init.d/mysqld start # # start the service, initialize, get a uuid and temporary secret

2017-03-28T00:46:21.597691Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f6701f68-134f-11e7-94e7-52540022095a.

2017-03-28T00:46:21.664060Z 1 [Note] A temporary password is generated for root@localhost: Imurs > yshos7JT

[root@server3 ~] # mysql-uroot-p # # change password

Enter password:

Mysql > alter user root@localhost identified by 'Redhat-1024'

Query OK, 0 rows affected (0.09 sec)

Vim / etc/my.cnf # # Editing files for configuration

Server-id=3

Gtid-mode=ON

Enforce-gtid-consistency=1 # # some security restrictions for enabling gtid

Log-bin=mysql-bin

Binlog-do-db=test

Binlog-ignore-db=mysql

Log-slave-updates

/ etc/init.d/mysqld restart # # change the configuration and restart the service

Mysql > show master status; # # View the status of the master node

+-+

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

+-+

| | mysql-bin.000004 | 154 | test | mysql |

+-

Mysql > grant replication slave on *. * to ly@'172.25.38.%' identified by 'Redhat-1024';## assign replication account

Query OK, 0 rows affected, 1 warning (0.10 sec)

-

[root@server4 ~] # / etc/init.d/mysqld start

2017-03-28T00:47:40.538466Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 257d874a-1350-11e7-b4ab-525400e717cb.

2017-03-28T00:47:40.661188Z 1 [Note] A temporary password is generated for root@localhost: Aozj4uC:4iff

[root@server4] # mysql-uroot-p

Enter password:

Mysql > alter user root@localhost identified by 'Redhat-1024'

Query OK, 0 rows affected (0.40 sec)

[root@server4 ~] # / etc/init.d/mysqld restart

[root@server4] # mysql-uroot-p

Enter password:

Mysql > change master to master_host='172.25.38.3',master_user='ly',master_password='Redhat-1024',master_auto_position=1;##

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Retrieved_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1

Executed_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1

[root@server3 mysql] # mysql-p

Enter password:

[root@server3 mysql] # cat auto.cnf

[auto]

Server-uuid=f6701f68-134f-11e7-94e7-52540022095a

Mysql > show global variables like'% gtid%'

+-+

| | Variable_name | Value |

+-+

| | binlog_gtid_simple_recovery | ON |

| | enforce_gtid_consistency | ON |

| | gtid_executed | f6701f68-134f-11e7-94e7-52540022095a:1-7 |

| | gtid_executed_compression_period | 1000 | |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

| | session_track_gtids | OFF |

+-+

[root@server4 mysql] # cat auto.cnf

[auto]

Server-uuid=257d874a-1350-11e7-b4ab-525400e717cb

Mysql > show global variables like'% gtid%'

+-+

| | Variable_name | Value |

+-+

| | binlog_gtid_simple_recovery | ON |

| | enforce_gtid_consistency | ON |

| | gtid_executed | f6701f68-134f-11e7-94e7-52540022095a:1-7 |

| | gtid_executed_compression_period | 1000 | |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged | f6701f68-134f-11e7-94e7-52540022095a:1-6 |

| | session_track_gtids | OFF |

+-+

Mysql > grant replication slave on *. * to ly@'172.25.38.%' identified by 'Redhat-1024'

Mysql > change master to master_host='172.25.38.3',master_user='ly',master_password='Redhat-1024',master_auto_position=1

Mysql > start slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Mysql > insert into usertb values (0004)

Query OK, 1 row affected (0.13 sec)

Master_Host: 172.25.38.3

Master_User: ly

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 445

Relay_Log_File: server4-relay-bin.000002

Relay_Log_Pos: 658

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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

Relay_Log_Space: 867

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

Master_UUID: f6701f68-134f-11e7-94e7-52540022095a

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 more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1

Executed_Gtid_Set: f6701f68-134f-11e7-94e7-52540022095a:1

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Mysql > select * from test.usertb

->

+-+ +

| | id | name |

+-+ +

| | 1 | zhangsan |

| | 2 | wang |

| | 3 | lisi |

+-+ +

Mysql > insert into usertb values (0004)

Query OK, 1 row affected (0.13 sec)

Mysql > select * from test.usertb

+-+ +

| | id | name |

+-+ +

| | 1 | zhangsan |

| | 2 | wang |

| | 3 | lisi |

| | 4 | xiao |

+-+ +

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.