"MySQL big line" MySQL cluster architecture

Original address (): "Technical article" "MySQL Cluster Architecture"

This address: http://www.cnblogs.com/aiweixiao/p/7258444.html

Click to follow the public number

1. Main content

1) MySQL architecture, locks, best practices, transactions

2) MySQL command line, master-slave copy, index, data backup/restore

3) MySQL cluster, MySQL load balancer, monitor, MySQL proxy

4) The MMM architecture of MySQL, MySQL source, DBA

mysql-from Delete to run

2. Specific content 2.1) "Architecture":

"Architecture Diagram":

architecture diagram for Mysql-mysql

"Overview": MySQL is composed of SQL interface, parser, optimizer, cache, storage engine

2.2) "Lock":

"Concept": in order to ensure consistent integrity of the data, any database has a locking mechanism. Locking mechanism of the advantages and disadvantages of a database system can directly think of the concurrent processing capability and performance, so the implementation of the locking mechanism has become one of the core technologies of various databases.

"Classification": MySQL each storage engine uses three types (levels) of locking mechanisms: row-level locking, page-level locking, and table-level locking

1) Row-level locking: The granularity of locked objects is very small, consumes the most, row-level locking is also the most prone to deadlock, but the concurrency is good

2) Table-level locking: The deadlock situation is low, but the concurrency is not good,

3) page-level locking: Between the previous two.

"Code":

1) Locking lock TABLES Tbl_name [as Alias]

2) Unlock UNLOCK TABLES

2.3) "Best practices":

"Optimize your query for query caching":

"MySQL enforces indexing and disables an index":

1) MySQL mandatory use index: Force index (index name or primary key PRI): SELECT * FROM Table Force index (PRI,ZIDUAN1_INDEX) limit 2; (Forced use index "PRI and ziduan1_ Index ")

2) MySQL prohibit an index: Ignore index (index name or primary key PRI): SELECT * FROM table Ignore index (PRI,ZIDUAN1_INDEX) limit 2; (Prohibit use of index "pri,ziduan1 _index ")

2.4) "Transaction":

"Concept": a transaction is a logical set of operations that make up the units of this set of operations, either completely or unsuccessfully. It is characterized by acid (atomicity, consistency, isolation, durability)

"Implementation":

1) Transaction open: Start TRANSACTION; or BEGIN;

2) Transaction submission: Commit;

3) transaction rollback: ROLLBACK;

2.5) "MySQL command line":

"Command line execution MySQL": The use of the MySQL-e parameter can perform a variety of SQL (create, delete, add, delete, change, check) and other operations

For example: #创建数据库

Create_db_sql= "CREATE database IF not EXISTS ${dbname}"

Mysql-h${hostname}-p${port}-u${username}-p${password}-E "${create_db_sql}"

2.6) "Master-slave Copy":

"One master multi-slave": In a master multi-slave database system, multiple from the server asynchronous way to update the main database changes, the Business Server in the execution of write or related modifications to the database operation is on the primary server, read operations on each slave server

mysql-mysql master-slave replication

"principle": the basis of data replication between MySQL is binary log file (binary logfile). A MySQL database once the binary log is enabled, as master, all operations in its database are recorded as "events" in the binary log, and other databases as slave communicate with the primary server through an I/O thread. and monitor the master binary log file changes, if the master binary log file changes, will change the changes to their own trunk log, and then slave a SQL thread will be related to the "event" into its own database, This implementation of the consistency from the database and the primary database, but also the implementation of master-slave replication.

2.7) "Index":

Overview: Indexes have a critical impact on the speed of queries, and understanding indexes is also a starting point for tuning database performance

"Kind": Normal index, unique index, full-text index, single/multi-column index, combined index (leftmost prefix)

"B + Tree":

mysql-index B + tree structure

1) in detail B + tree: A light blue block we call a disk block, you can see each disk block contains several data items (shown in dark blue) and pointers (shown in yellow), such as disk Block 1 contains data items 17 and 35, contains pointers P1, P2, P3,P1 represents a disk block less than 17, P2 represents a disk block between 17 and 35, and P3 represents a disk block larger than 35. Real data exists at leaf nodes 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Non-leaf nodes do not store real data, only data items that guide the direction of the search, such as 17 and 35, do not exist in the data table.

2) Find a B + tree: If you want to find data item 29, then the disk Block 1 will be loaded into memory, the first time Io, in memory with a binary lookup determined 29 between 17 and 35, locking disk Block 1 P2 pointer, memory time because of very short (compared to the disk IO) can be ignored, Disk Block 1 through disk address of the P2 pointer to the disk block 3 is loaded into memory, the second io,29 between 26 and 30, locking disk block 3 of the P2 pointer, loading disk blocks 8 through the pointer to memory, a third Io, while in-memory binary find found 29, the end of the query, a total of three IO. The real situation is, the 3-tier B + tree can represent millions of data, if millions of data to find only three Io, the performance will be huge, if there is no index, each data item will occur once IO, then a total of millions of Io, it is obviously very expensive.

2.8) "Data backup/Restore":

"Data Backup":

1) Use the mysqldump command to back up mysqldump-u username-p--databases dbname2 dbname2 > Backup.sql

2) Copy the entire database directory directly: need to stop service, do not support InnoDB engine

3) Hot backup: Use the Mysqlhotcopy tool to quickly back up without stopping the service. mysqlhotcopy [option] dbname1 dbname2 backupdir/

"Data Restore":

1) Restore the database backed up using the mysqldump command: Mysql-u root-p < C:\backup.sql

2) Restore the backup of a direct copy directory: You must ensure that the version number of the two MySQL databases is the same. A table of type MyISAM is valid and is not available for tables of type InnoDB

2.9) "MySQL cluster":

"Concept": MySQL cluster (MySQL Cluster) is a highly practical, highly redundant version of MySQL suitable for distributed computing environments. It employs the NDB Cluster storage engine, allowing multiple MySQL servers to run in 1 Cluster. The storage engine is available in binary versions of MYQL 5.0 and above, and in RPM compatible with the latest Linux versions.

mysql-mysql cluster architecture diagram

Classification: Divided into synchronous cluster and asynchronous cluster.

1) Sync cluster: (mysql cluster)

Structure: (Data + SQL + MGM node)

Characteristics:

1.1) memory level, the hardware requirements are low, but the memory requirements are large. The conversion ratio is: 1:1.1;

1.2) data is placed on several servers at the same time, redundancy is better;

1.3) General speed;

1.4) The build table needs to be declared as Engine=ndbcluster

1.5) strong extensibility;

1.6) High availability and load balancing can be achieved, support for large-scale applications;

1.7) must be a specific MySQL version, such as: The version of Max that has been compiled;

1.8) Easy to configure and manage, no data loss

2) Asynchronous cluster (MySQL replication)

Structure: (Master + slave)

Characteristics:

2.1) Master-slave database asynchronous data;

2.2) data placed on several servers, redundancy in general;

2.3) faster;

2.4) Poor extensibility;

2.5) inability to achieve high availability and load balancing (read-write separation can be achieved at the program level, reducing stress on the primary database);

2.6) Poor configuration and management, data may be lost

2.10) "MySQL Load balancer":

Definition: Distributes a user's request to a real server server through a director, and then returns it to the user. Load balancing deployment is flexible and can meet a variety of needs.

mysql-lvs+keepalived for MySQL load Balancing

"Implementation Mode":

Hardware: BIG/IP, Cisco, IBM (expensive)

Software: LVS (free)

The LVS system encapsulates and forwards the user's request packets in the data layer and network layer, and satisfies various requirements in three ways.

1) DR: Direct Routing

2) TUNING:TCP/IP Tunnel

3) NAT: Network address Translation

2.11) "MySQL monitoring":

"Monitoring content": monitor the daily session of the database. Debris, remaining table space monitoring, and timely understanding of table space expansion, as well as the distribution of remaining space

"Code implementation": Https://github.com/wozhuzaisi/shell-work/blob/master/mysqlmonitor.sh

2.12) "MySQL proxy":

"Definition": MySQL proxy is a simple program between your client side and the MySQL server that can monitor, analyze, or change their communications. It uses flexible, unlimited, common uses include: load balancing, failure, query analysis, query filtering and modification, etc.

"Implementing read-Write separation":

1) Install and configure the MySQL proxy.

2) Test: master server mysql> Grant all on * * to ' lin3615 ' @ ' 192.168.179.142 ' identified by ' 123456 ';

2.14) "MySQL MMM architecture":

"Overview": A set of scripts that support dual-master failover and dual-master daily management. Because MMM does not fully guarantee the consistency of data, MMM is suitable for the data consistency requirements are not very high, but also want to maximize the business availability of the scene. For businesses that have a high level of conformance to data, it is highly recommended that you use MMM as a highly available architecture.

mysql-mmm Architecture Diagram

2.15) "MySQL Source code":

"Content": MySQL is written by C + +, the entry function main () directly called Mysqld_main (). The server-initiated code is here until the last Handle_connections_sockets ().

Mysql-mysql's source code first book

"Information":

Download PDF: "Deep MySQL source code"

Official manual: https://dev.mysql.com/doc/internals/en/

Read Source: https://zhuanlan.zhihu.com/p/26118810

Book: "Deep understanding of MySQL core technology"

2.16) "DBA":

"Responsibilities":

mysql-dba Mind Map

1) database installation;

2) database configuration and management;

3) Permission setting and security management;

4) Monitoring and performance adjustment;

5) Backup and recovery;

6) solve the general problem;

the day of Mysql-dba

mysql-attention to the public number

"MySQL big line" MySQL cluster architecture