Practice: mysql 5.6 replication latency monitoring, mysql5.6

Practice: mysql 5.6 replication latency monitoring, mysql5.6

# Repdelay. sh #! /Bin/sh # ocpyang@126.com # view the replication delay specific event # set mysql slave = root MYSQL_PASS_MASTER = 'Password' MYSQL_HOST_MASTER = 192.168.2.188MYSQL _ USER_SLAVE = root MYSQL_PASS_SLAVE = 'Password' slave = 192.168.2.14tmpfile _ 01 = "tmp01. 'date between" tmpfile_02 = "tmp02. 'date between" mysql-h $ {MYSQL_HOST_MASTER}-u $ {MYSQL_USER_MASTER}-p $ {MYSQL_PASS_MASTER}-e "SHOW BINARY LOGS; ">$ {Tmpfile_01} mysql-h $ {MYSQL_HOST_SLAVE}-u $ {MYSQL_USER_SLAVE}-p $ {MYSQL_PASS_SLAVE}-e "show slave status \ G; ">$ {tmpfile_02} # tail-1 $ {tmpfile_01} | grep-v" Log_name "# cat $ {tmpfile_02} | grep-E 'master _ Log_File | Read_Master_Log_Pos | Exec_Master_Log_Pos' | grep-v "Relay_Master_Log_File" | sed's/^ [] * // g'a = 'Tail-1 $ {tmpfile_01} | grep-v "Log_name" | awk' {print $1} '| awk-F ". "'{print $2}' 'B = 'sed-n "/\ <Master_Log_File \>/p" $ {tmpfile_02} | sed's/^ [] * // G' | awk-F ": "'{print $2}' | awk-F ". "'{print $2}'' bhtime = 'sed-n "/\ <Seconds_Behind_Master \>/p" $ {tmpfile_02} | sed's/^ [] * // g' | awk-F ": "'{print $2}'' if ["$ B" = "$ a"]; thenc = 'Tail-1 $ {tmpfile_01} | grep-v "Log_name" | awk '{print $2} 'd = 'sed-n "/\ <Read_Master_Log_Pos \> /p "$ {tmpfile_02} | sed's/^ [] * // G' | awk-F ": "'{print $2}'' e = 'exp R $ c-$ d 'if ["$ {e}"-eq 0-a "$ {bhtime}"-eq 0]; thenecho "************************************** ***************************************" echo-e "\ e [1; 31 m & Synchronization has been completed! & \ E [0 m "echo "************************* **************************************** * *********** "elif [" $ {e} "-eq 0-o" $ {bhtime} "-eq 0]; thenecho "************************************** ***************************************" echo-e "\ e [1; 31 m Has been synchronized to the same log file! Wait a moment \ e [0 m "echo-e" \ e [1; 31 m Not synchronized binlog events is :$ {e }, behind master tims is $ {bhtime} \ e [0 m "echo "************************ **************************************** * ************ "fielif [$ {B}-lt $ {a}]; thenf = 'sed-n "/\ <Read_Master_Log_Pos \>/p" $ {tmpfile_02} | sed's/^ [] * // G' | awk-F ": "'{print $2}' g = 'awk' $1 >=" $ B" {print $2} '$ {tmpfile_01} | awk' BEGIN {total = 0} {total + = $1} END {print total} ''re = 'expr $ g-$ f'echo-e" \ e [1; 31 m There are multiple log files are not synchronized, the events is :$ {re} \ e [0 m "firm-rf $ {tmpfile_01} rm-rf $ {tmpfile_02}

 

What is the difference between mysql 55 and earlier versions (after 50, before 55)

Major differences between MySQL 4.1/5.0/5.1/5.5/5.6 versions

1. 4.1 added support for subqueries, UTF-8 for character sets, ROLLUP for group by statements, and better encryption algorithms for mysql. user tables.

2. Added support for Stored procedures, Views, Cursors, Triggers, and XA transactions and added the INFORATION_SCHEMA system database.

3. Added support for Event scheduler, Partitioning, Pluggable storage engine APIs, Row-based replication, Global-level dynamic modification of general query log and slow query log.

4. New Features of 5.5

1) Change the default storage engine to InnoDB

2) improve performance and scalability

A. Increase the default thread concurrency (innodb_thread_concurrency)

B. Background input/output thread control (innodb_read_io_threads, innodb_write_io_threads)

C. Master thread input/output rate control (innodb_io_capacity)

D. Operating System memory allocation program usage control (innodb_use_sys_malloc)

E. adaptive Hash Index control. You can disable the adaptive Hash function.

F. Insert Buffering control. You can disable innodb's Insert buffer function.

G. Improve scalability through the quick lock algorithm. innodb does not use a proxy (posix) thread, but uses native independent operations to complete mutex and read/write locks.

H. Restore Group Commit (Restored Group Commit)

I. Improved recovery performance

J. Multi-buffer pool instance

K. Multiple Rollback Segments (Multiple Rollback Segments). The previous innodb version can handle a maximum of 1023 concurrent processing operations. Now mysql5.5 can handle up to concurrent transactions,

L. asynchronous input/output inherent in the Linux system, and the mysql5.5 database system also increases the number of input/output requests in the linux system.

M. Extended change Buffer: added Delete buffer and clear Buffer

N. improves the mutex of the log system and the mutex of the Flush list.

O. Improve the purge progress. In mysql5.5, it is clear that the Operation thread is an independent thread and supports concurrency. You can use the innodb_purge_treads configuration.

P. Improve metadata lock in transaction processing. For example, if a statement in a transaction needs to lock a table, it will release the table at the end of the transaction, rather than releasing the table at the end of the statement.

3) Improve Practicality

A. Semi-synchronous Replication (Semi-synchronous Replication)

B. Copy Heartbeat

C. Automatic Recovery of Relay logs (Automatic Relay Log Recovery)

D. Replication Based on Server Filtering items (Replication Per Server Filtering)

E. Replication Slave Side Data Type Conversions)

4) improve ease of management and efficiency

A ...... the remaining full text>

A large number of MYSQL connection delays execution

1. select the most suitable field attribute

MySQL can support access to large data volumes, but generally, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width in the table as small as possible to achieve better performance. For example, if you set it to CHAR (255) when defining the zip code field, it is obvious that unnecessary space is added to the database, and even the VARCHAR type is redundant, because CHAR (6) can well complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.

Another way to improve efficiency is to set the field to not null whenever possible, so that the database does NOT need to compare NULL values during future queries.

Some text fields, such as "Province" or "gender", can be defined as ENUM. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the database performance.

2. Use JOIN instead of Sub-Queries)

MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single column query result, and then use this result as a filter condition in another query. For example, if you want to delete a customer who has no orders in the basic customer information table, you can use the subquery to retrieve the customer IDs of all orders from the sales information table, then pass the result to the primary query, as shown below:

Delete from customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

Subqueries can be used to complete SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be prevented from being locked and can be easily written. However, in some cases, subqueries can be replaced by more efficient JOIN. For example, if we want to retrieve all users without order records, we can use the following query:

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

If you use JOIN... to complete this query, the speed will be much faster. Especially when the salesinfo table has an index on CustomerID, the performance will be better. The query is as follows:

SELECT * FROM customerinfo
Left join salesinfoON customerinfo. CustomerID = salesinfo.
CustomerID
WHERE salesinfo. CustomerID IS NULL

JOIN... it is more efficient because MySQL does not need to create a temporary table in the memory to perform the query in two steps.

3. Use UNION instead of creating a temporary table manually

MySQL 4.0 and later versions support UNION queries. It can merge two or more SELECT queries in a temporary table. When the query Session on the client ends, the temporary table is automatically deleted to ensure the database is neat and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. Note that the number of fields in all SELECT statements must be the same. The following example demonstrates a query using UNION.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author ...... remaining full text>