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

Master-slave replication:

One-way, two-way, ring, cascade, one master and multiple slaves

Five cases of dual-computer replication

1. Asynchronous master-slave (default general)

2. Double write (the previous program writes two databases at the same time, and both sides must be implemented before the program returns successfully)

3. Using plug-in software to capture the Binlog log of the real-time master library, so that the slave library can be completed when the computer is down.

4. Semi-synchronous plug-ins developed by Google

5 、 DRBD

Separation of master and slave reading and writing

1, through the program (performance, efficiency is the best, recommended)

Programs such as php,java can easily achieve master-slave read-write separation by setting up multiple connection files.

2. The separation of reading and writing is realized by software.

MySQL-proxy, Amoeba and other agent software can also achieve read-write separation function, but it is best to program implementation.

3. Develop dbproxy

Read-write separation logic diagram shows:

Principle of master-slave synchronization:

In fact, it is asynchronous, that is, only after the log is written by the master library, the log can be applied from the library.

Master slave

Thread: IO thread: IO/SQL

The startup parameter that needs to be set to enable log-bin: CHANGE MASTER TO

How to delete and modify how to write log-bin MASTER_HOST='192.168.1.111'

MASTER_PORT=3308

MASTER_USER='alrinrep'

MASTER_PASSWORD='password123'

MASTER_LOG_FILE='mysql-bin.000047'

MASTER_LOG_POS='5632'

Enable synchronization method: start slave

A copy request is made from the library IO to the master database, and the master database begins to authenticate the slave database. After passing the master database IO, it starts to send bin-log to the slave database relay-log according to the requirements of the slave log. After one cycle, update the master.info from the slave library and re-initiate the request to the master database. The SQL thread discovers new data from relay-log and writes to slave's data file and logging system. Related statement:-- master-data=1, CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=1191; establishes the relevant account, that is, alrinrep, and grants due permissions

Principle process of MySQL master-slave replication

1. Execute start slave on the Slave server and turn on the master-slave replication switch.

2. The IO thread of the Slave server connects to the Master server through a request for replication user rights authorized on the Master, and requests that the location be specified from the specified bin-log log file.

Send binlog log after (parameters related to change master to command)

3. After the Master server receives the request from the IO thread of the Slave server, the Master server is responsible for replicating the information requested by the IO thread according to the IO thread of the Slave server.

Reads the binlog log information after the specified location of the binlog log file and returns it to the IO thread on the Slave side. In addition to the contents of the binlog log, the information returned

There is also the new binlog file name on the Master server side and the next specified update location in the binlog after the log content is returned this time.

4. When the IO thread of the Slave server obtains the log sent by the IO thread on the Master server, as well as the log file and location point, write the contents of the binlog log to the

The last end of the Relay log (relay log) file (MySQL-relay-bin.xxxxxx) on the slave side, and record the new binlog file name and location to the master-info file

So that the next time you read the new Binlog log on the Master side, you can tell the Master server which file you need to start the new binlog content from in the new binlog log.

5. The SQL thread on the Slave server will detect the new log content in the local relay log in real time, and then parse the content in the LOG file in time that has been executed by the master side.

And execute these SQL sequentially on your own SLAVE server Clean up the applied logs after playing with the application.

6. After going through the above process, you can ensure that MASTER and SLAVE perform the same SQL. When replication is normal, MASTER and SLAVE data are exactly the same.

Master-slave replication on the computer experiment:

0, environment: 3308amp 3309 on the same machine, IP is 192.168.199.151

1. Modify my.cnf and check server-id and log-bin. Master and slave server-id must be different. Bin-log must be enabled for the main library

2. Add replication account and corresponding replication slave permission

Mysql > select user,host,password from user +-+ | user | host | password | +- -- + | root | localhost | * A0F874BC7F54EE086FCE60A37CE7887D8B31086B | | alrin |% | * A0F874BC7F54EE086FCE60A37CE7887D8B31086B | +-- -+ 2 rows in set (0.00 sec) mysql > grant replication slave on *. * to alrinrep@'192.168.199.%' identified by 'password123' Query OK, 0 rows affected (0.00 sec) mysql > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql > show variables like 'log_bin' +-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec) mysql >

3. Make a backup on the main database. Lock the table before backing up

Mysql > flush table with read lock; Query OK, 0 rows affected (0.03 sec mysql > show master status) +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000004 | 423 | | +-+ 1 row in set (0.00 sec) perform backup after a new session window is opened:

[root@localhost backup] # mysqldump-uroot-ppassword123-S / data/mysqldata/3308/mysql.sock-A-B-- events-- master-data=2 | gzip > / data/mysqldata/backup/rep.sql.gz

Check after backup: mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000004 | 423 | +- -+ 1 row in set (0.00 sec) unlock table: mysql > unlock tables Query OK, 0 rows affected (0.00 sec) mysql >

4, operate from the library

Mysql > change master to-> MASTER_HOST='192.168.199.151',-> MASTER_PORT=3308,-> MASTER_USER='alrinrep',-> MASTER_PASSWORD='password123',-> MASTER_LOG_FILE='mysql-bin.000004',-> MASTER_LOG_POS=423; Query OK, 0 rows affected, 2 warnings (0.05 sec)

5. Enable replication

Mysql > start slave; Query OK, 0 rows affected (0.25 sec)

6. Check the slave status:

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.199.151 Master_User: alrinrep Master_ Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 423 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 283 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: 423 Relay_Log_Space: 456 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: 1 Master_UUID: 16b758c5-2455-11e9-8fc6 -080027339667 Master_Info_File: / data/mysqldata/3309/data/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) mysql >

7. Try DDL in the main library to check the replication status

Main library:

Mysql > create database alrin; Query OK, 1 row affected (0.00 sec) Slave Library: mysql > show databases +-+ | Database | +-+ | information_schema | | alrin | | mysql | | performance_schema | | test | +- -+ 5 rows in set (0.00 sec)

Command summary:

1,grant replication slave on. To alrinrep@'192.168.199.%' identified by 'password123'

2,flush table with read lock

3,show master status

4,unlock tables

5,change master to

MASTER_HOST='192.168.199.151'

MASTER_PORT=3308

MASTER_USER='alrinrep'

MASTER_PASSWORD='password123'

MASTER_LOG_FILE='mysql-bin.000004'

MASTER_LOG_POS=423

6,start slave

7 show slave status\ G

8, about log_slave_updates:

By default, after reading the replay_bin log from the SLAVE SQL of the library and applying it, the database has content, but does not write its own BINLOG, that is, if the slave wants to drag another slave, the so-called cascading replication will fail because the second slave will read the binlog of the first slave. So is there any way to get the first SLAVE to write binlog? the way is to open log_slave_updates.

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.