[MySQL major series] Mysql cluster architecture, mysql cluster architecture

[MySQL major series] Mysql cluster architecture, mysql cluster architecture

Original article address (): [Technical Article] Mysql cluster architecture

Address: http://www.cnblogs.com/aiweixiao/p/7258444.html

Click follow public account

 

1. Main Content

 

1) mysql architecture, locks, best practices, and transactions

2) mysql command line, master-slave replication, index, data backup/recovery

3) mysql cluster, mysql Server Load balancer, monitoring, and mysql proxy

4) mysql MMM architecture, mysql source code, DBA

MySQL-from deletion to running

 

2. Details: 2.1) [architecture ]:

[Architecture diagram ]:

Architecture of MySQL-MySQL

[Overview]: Mysql is composed of SQL interface, parser, optimizer, cache, and storage engine.

2.2) [Lock ]:

[Concept]: to ensure data consistency and integrity, any database has a locking mechanism. The advantages and disadvantages of the locking mechanism should directly consider the concurrent processing capability and performance of a database system, so the implementation of the locking mechanism has become one of the core technologies of various databases.

[Category]: MySQL storage engines use three types of locking mechanisms: Row-level locking, page-level locking, and table-level locking.

1) Row-level locking: the granularity of the locked object is small and the maximum consumption is allowed. Row-level locking is also the most prone to deadlock, but the concurrency is good.
2) Table-level locking: there are few deadlocks, but the concurrency is not good,
3) page-Level Lock: between the first two.

[Code ]:

1) lock tables tbl_name [AS alias]
2) UNLOCK TABLES

 

2.3) [best practices ]:

[Optimize your query for the query cache ]:

[Mysql enforces and disables an index ]:

1) mysql enforces index: force index (index name or primary key PRI): select * from table force index (PRI, ziduan1_index) limit 2; (mandatory Index "PRI and ziduan1_index ")
2) mysql disables an index: ignore index (index name or primary key PRI): select * from table ignore index (PRI, ziduan1_index) limit 2; (index prohibited "PRI, ziduan1_index ")

 

2.4) [transaction ]:

[Concept]: A transaction refers to a logical group of operations that constitute each unit of this group of operations. If the transaction fails, the transaction fails. ACID (atomicity, consistency, isolation, durability)

[Implementation ]:

1) TRANSACTION enabling: start transaction; or BEGIN;
2) transaction COMMIT: COMMIT;
3) Transaction ROLLBACK: ROLLBACK;

 

(2.5) [mysql command line ]:

[Run mysql through the command line]: You can use the-e parameter of mysql to perform various SQL operations (create, delete, add, delete, modify, and query ).

Example: # create a database
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 replication ]:

[One master, multiple slaves]: In a one master, multiple slaves database system, multiple slave servers use an Asynchronous Method to update changes in the primary database, the Business Server performs write or related database modification operations on the master server, while the read operations are performed on the slave servers.

MySQL-MySQL master-slave Replication

[Principle]: Data replication between MySQL instances is based on binary log files ). Once binary logs are enabled for a MySQL database, it acts as the master. All operations in its database are recorded in binary logs as "events, other databases use slave to communicate with the master server through an I/O thread and monitor the changes in the binary log files of the master. If the binary log files of the master server change, the changes will be copied to your own relay logs, and an SQL thread of slave will execute the related "events" into its own database, in order to achieve consistency between the slave database and the master database, master-slave replication is achieved.

2.7) [Index ]:

[Overview]: indexes have a crucial impact on the query speed. Understanding indexes is also the starting point for optimizing database performance.

[Category]: general index, unique index, full-text index, single-column/Multi-column index, combined index (leftmost prefix)

[B + tree ]:

 

MySQL-index B + Tree Structure

1) Details B + tree: the light blue block is called a disk block. You can see that each disk block contains several data items (in dark blue) and pointers (in yellow ), for example, disk Block 1 contains data items 17 and 35, including pointers P1, P2, and P3, P1 indicates disk blocks smaller than 17, and P2 indicates disk blocks between 17 and 35, p3 indicates a disk block larger than 35. Real data exists in leaf nodes: 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, and 99. Non-leaf nodes only store real data, but only data items directing the search direction. For example, data items 17 and 35 do not actually exist in the data table.
2) Search for B + tree: If you want to search for data item 29, disk Block 1 is first loaded from disk to memory. At this time, IO occurs, in the memory, use binary search to determine that the P2 pointer of disk Block 1 is between 17 and 35. The memory time is negligible because it is very short (compared with the disk IO, the address of the P2 pointer of disk Block 1 is used to load disk block 3 from disk to memory. When the second IO occurs, the P2 pointer of disk block 3 is locked between 26 and 30, the pointer is used to load the disk block 8 to the memory, and the third IO occurs. At the same time, the binary search is performed in the memory to locate 29 and end the query, with a total of three I/O operations. The real situation is that the layer-3 B + tree can represent millions of data. If only three IO operations are required for millions of data queries, the performance improvement will be huge. If no index is available, if I/O occurs for each data item, a total of millions of I/O operations are required. Obviously, the cost is very high.

 

2.8) [data backup/recovery ]:

[Data backup ]:

1) Use the mysqldump command to back up mysqldump-u username-p -- databases dbname2 dbname2> Backup. SQL
2) directly copy the entire database Directory: the service needs to be stopped and the InnoDB engine is not supported.
3) Hot Backup: Use mysqlhotcopy to quickly back up data without stopping services. Mysqlhotcopy [option] dbname1 dbname2 backupDir/

 

[Data restoration ]:

1) restore the database backed up using the mysqldump command: mysql-u root-p <C: \ backup. SQL
2) restore the backup of the Direct Copy Directory: Make sure that the version numbers of the two MySQL databases are the same. MyISAM tables are valid and unavailable for InnoDB tables.

 

(2.9) [mysql cluster ]:

[Concept]: MySQL Cluster is a highly practical and redundant MySQL version suitable for distributed computing environments. It uses the NDB Cluster Storage engine and allows multiple MySQL servers to run in one Cluster. This storage engine is available in MySQL 5.0 and later binary versions, and in RPM compatible with the latest Linux version.

MySQL-MySQL cluster architecture

[Category]: synchronous and asynchronous clusters.

1) Synchronization cluster: (mysql cluster)
Structure: (data + SQL + mgm node)
Features:
1) memory-level, low hardware requirements, but high memory requirements. Conversion ratio: 1: 1.1;
1.2) data is stored on several servers at the same time, with good redundancy;
1.3) Average Speed;
1.4) The table creation must be declared as engine = ndbcluster.
1.5) high scalability;
1.6) high availability and load balancing can be achieved to support large-scale applications;
1.7) It must be a specific mysql version, such as the compiled max version;
1.8) easy configuration and management without data loss
 
2) asynchronous cluster (mysql replication)
Structure: (master + slave)
Features:
2.1) asynchronous data of the master-slave database;
2.2) data is stored on several servers, with common redundancy;
2.3) fast;
2.4) poor scalability;
2.5) failure to achieve high availability and load balancing (read/write splitting can only be achieved at the program level, reducing the pressure on the primary database );
2.6) Poor configuration and management, data may be lost

 

2.10) [mysql Server Load balancer ]:

[Definition]: Use director to distribute user requests to the real server and return them to the user. Flexible Server Load balancer deployment to meet various needs.

 

MySQL-LVS + Keepalived for mysql Load Balancing

[Implementation Method ]:

Hardware: BIG/IP, Cisco, IBM (expensive)
Software: LVS (free)
The LVS system encapsulates and forwards user request data packets at the data layer and network layer, in three ways to meet various requirements.
1) DR: Direct routing
2) Tuning: TCP/IP Tunnel
3) NAT: Network Address Translation

 

2.11) [mysql monitoring ]:

[Monitoring content]: monitors the daily sessions of the database. Monitoring of shards and remaining tablespaces, and timely understanding of table space expansion and remaining space distribution

[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 and MySQL server. It can monitor, analyze, or change their communication. It is flexible and has no restrictions. Its common uses include load balancing, faults, query analysis, query filtering and modification.

[Read/write splitting ]:

1) install and configure mysql proxy.
2) test: the master server mysql> grant all on *. * to 'lin3615' @ '192. 168.179.142 'identified by '123 ';

 

(2.14) [mysql MMM architecture ]:

[Overview]: it is a set of script programs supporting dual-master failover and daily management of Dual-master. Because MMM cannot completely guarantee data consistency, MMM is suitable for scenarios where data consistency is not very high, but it wants to maximize service availability. For businesses that require high data consistency, MMM is not recommended.

 

MySQL-MMM Architecture

 

2.15) [mysql source code ]:

[Content]: MySQL is written by C ++. The entry function main () directly calls mysqld_main (). Server startup code is here until the last handle_connections_sockets ().

 

MySQL-MySQL source code book 1

[Materials ]:

Download pdf: Deep MySQL source code
Official site manual: https://dev.mysql.com/doc/internals/en/
Read Source Code: https://zhuanlan.zhihu.com/p/26118810
Books: deep understanding of MySQL core technologies

 

2.16) [DBA ]:

[Responsibilities ]:

 

MySQL-DBA Mind Map

1) database installation;
2) database configuration and management;
3) permission settings and security management;
4) monitoring and performance adjustment;
5) backup and recovery;
6) solve general problems;

 

MySQL-DBA day

MySQL-follow public account