# MySQL High Availability- P9

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
54
lượt xem
3

## MySQL High Availability- P9

Mô tả tài liệu

MySQL High Availability- P9: A lot of research has been done on replication, but most of the resulting concepts are never put into production. In contrast, MySQL replication is widely deployed but has never been adequately explained. This book changes that. Things are explained here that were previously limited to people willing to read a lot of source code and spend a lot of time debugging it in production, including a few late-night sessions.

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: MySQL High Availability- P9

1. Administrator. We will look at the MySQL Administrator in “Replication Monitoring with MySQL Administrator” on page 381. Monitoring Commands for the Slave The SHOW SLAVE STATUS command displays information about the slave’s binary log, its connection to the server, and replication activity, including the name and offset position of the current binlog file. This information is vital in diagnosing slave performance, as we have seen in previous chapters. Example 10-5 shows the result of a typical SHOW SLAVE STATUS command executed on a server running MySQL version 5.5. Example 10-5. The SHOW SLAVE STATUS command mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: rpl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 39016226 Relay_Log_File: relay-bin.000004 Relay_Log_Pos: 9353715 Relay_Master_Log_File: mysql-bin.000002 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: 25263417 Relay_Log_Space: 39016668 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: 66 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Monitoring Slaves | 377 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3. Connect_Retry The number of seconds that expire between retry connect attempts. This value should always be low, but you may want to set it higher if you have a case where the slave is having issues connecting to the master. Exec_Master_Log_Pos This shows the position of the last event executed from the master’s binary log. Relay_Log_Space The total size of all of the relay logfiles. You can use this to determine if you need to purge the relay logs in the event you are running low on disk space. Seconds_Behind_Master The number of seconds between the time an event was executed and the time the event was written in the master’s binary log. A high value here can indicate signif- icant replication lag. We discuss replication lag in an upcoming section. The value for Seconds_Behind_Master could become stale when replication stops due to network failures, loss of heartbeat from the master, etc. It is most meaningful when replication is running. If your slave has binary logging enabled, the SHOW BINARY LOGS command displays the list of binlog files available on the slave and their sizes in bytes. Example 10-6 shows the results of a typical SHOW BINARY LOGS command. Example 10-6. The SHOW BINARY LOGS command on the slave mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | slave-bin.000001 | 5151604 | | slave-bin.000002 | 1030108 | | slave-bin.000003 | 1030044 | +------------------+-----------+ 3 rows in set (0.00 sec) You can rotate the relay log on the slave with the FLUSH LOGS command. You can also use the SHOW BINLOG EVENTS command to show events in the binary log on the slave if the slave has binary logging enabled. The difference between showing events on the slave and showing them on the master is you want to specify the binlog filename on the slave as shown in the SHOW BINARY LOGS output. Example 10-7 shows the binlog events from a typical replication configuration. Monitoring Slaves | 379 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4. Example 10-7. The SHOW BINLOG EVENTS command (statement-based) mysql> SHOW BINLOG EVENTS IN 'slave-bin.000001' FROM 2701 LIMIT 2 \G *************************** 1. row *************************** Log_name: slave-bin.000001 Pos: 2701 Event_type: Query Server_id: 1 End_log_pos: 3098 Info: use employees; CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, from_date) ) *************************** 2. row *************************** Log_name: slave-bin.000001 Pos: 3098 Event_type: Query Server_id: 1 End_log_pos: 3405 Info: use employees; INSERT INTO departments VALUES ('d001','Marketing'),('d002','Finance'), ('d003','Human Resources'),('d004','Production'), ('d005','Development'),('d006','Quality Management'), ('d007','Sales'),('d008','Research'), ('d009','Customer Service') 2 rows in set (0.01 sec) In MySQL versions 5.5 and later, you can also inspect the slave’s relay log with SHOW RELAYLOG EVENTS. Slave Status Variables There are only a few status variables for monitoring the slave. These include counters that indicate how many times a slave-related command was issued on the master and statistics for key slave operations. The first four listed here are simply counters of the various slave-related commands. The values should correspond with the frequency of the maintenance of your slaves. If they do not, you may want to investigate the possi- bility that there are more slaves in your topology than you expected or that a particular slave is being restarted too frequently. 380 | Chapter 10: Replication Monitoring Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
6. Figure 10-1. The MySQL Administrator running on the master Figure 10-2. The MySQL Administrator running on the slave 382 | Chapter 10: Replication Monitoring Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
7. In Figures 10-1 and 10-2, the information displayed includes the hostname, server ID, port, kind (master or slave), a general status, the logfile (binlog filename), and the current log position. Figure 10-1 shows the replication topology listing all of the con- nected slaves. This report can be handy when you want to get an at-a-glance status of your servers. Other Items to Consider This section discusses some additional considerations for monitoring replication. It includes special networking considerations and monitoring lag (delays in replication). Networking If you have limited networking bandwidth, high contention for the bandwidth, or simply a very slow connection, you can improve replication performance by using compression. You can configure compression using the slave_compressed_protocol variable. In cases where network bandwidth is not a problem but you have data that you want to protect while in transit from the master to the slaves, you can use an SSL connection. You can configure the SSL connection using the CHANGE MASTER command. See the sec- tion titled “Setting Up Replication Using SSL” in the online MySQL Reference Man ual for details on using SSL connections in replication. Another networking configuration you may want to consider is using master heart- beats. You have seen where this information is shown on the SHOW SLAVE STATUS com- mand. A heartbeat is a mechanism to automatically check connection status between a master and a slave. It can detect levels of connectivity in milliseconds. Master heart- beat is used in replication scenarios where the slave must be kept in sync with the master with little or no delay. Having the capability to detect when a threshold expires ensures the delay is identified before replication is halted on the slave. You can configure master heartbeat using a parameter in the CHANGE MASTER command with the master_heartbeat_period= setting (added in MySQL version 5.4.4), where the value is the number of seconds at which you want the heartbeat to occur. You can monitor the status of the heartbeat with the following commands: SHOW STATUS like 'slave_heartbeat period' SHOW STATUS like 'slave_received_heartbeats' Monitor and Manage Slave Lag Periods of massive updates, overburdened slaves, or other significant network per- formance events can cause your slaves to lag behind the master. When this happens, the slaves are not processing the events in their relay logs fast enough to keep up with the changes sent from the master. Other Items to Consider | 383 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.