Mysql Cluster overview and deployment

The most significant advantage of Mysql Cluster overview and deployment is high availability, high real-time performance, high redundancy, and high scalability. It allows you to deploy the Cluster of the "in memory" database in a non-shared system. without a shared architecture, the system can use cheap hardware. in addition, since each component has its own memory and disk, there is no single point of failure. it consists of a group of computers, each of which runs a variety of processes, including mysql servers, data nodes of NDB clusters, and management services, and all these nodes of the dedicated Data Access Program constitute a complete mysql cluster system. the data is stored in the storage engine of "NDB storage server", and the table (structure) is saved in "mysql Server. the application accesses these data tables through the "mysql Server", and the cluster management server manages the "NDB storage server" through the management tool (ndb_mgmd ". the basic concept "NDB" is a "in-memory" storage engine with high availability and good data consistency. the following describes the process of a mysql cluster node. A single computer can have any number of nodes. management node (MGM): This type of node is used to manage mysql clu. Other nodes in the ster, such as configuration files and cluster logs, start and stop nodes, and run backups. each node in the cluster retrieves configuration data from the Management Server and requests the location of the management server. when a new event occurs in the data node, the node transmits the information about the event to the management server, and then writes the information to the cluster log. Because these nodes are responsible for managing the configurations of other nodes, you should first start these nodes before starting other nodes. MGM nodes use the "ndb_mgmd" command to start data nodes (NDB): these nodes are used to save cluster data. the number of data nodes is related to the number of copies, which is a multiple of fragments. suppose there are two copies, each of which has two fragments, then there are four data nodes. however, there is no need to set multiple copies. the data node is started with the "ndbd" command. SQL node: This is the node used to access cluster data. for MYSQL cluster, the client node is a traditional Mysql server that uses the NDB cluster Storage engine. generally, SQL nodes Add "ndbcluster" to "my. after cnf is started with "mysqld", there can be any number of cluster client processes or programs. they are divided into two types: Standard mysql client and management client. standard mysql client: access the mysql cluster management client from existing mysql applications written in php, perl, c, c ++, java, python, and ruby: these clients are connected to the Management Server and provide commands such as starting and stopping nodes, starting and stopping message tracing, displaying node versions and statuses, and starting and stopping backup. the following is the mysql cluster architecture: because Mysql Cluster uses TCP/IP connection, and data transmission between nodes is not encrypted, it uses a separate subnet. in order to facilitate the deployment, I will put the management nodes, data nodes, and SQL nodes on one machine. management Node 1 10.1.6.205 data node 1 10.1.6.203 data node 2 10.1.6.205 SQL Node 1 10.1.6.203 SQL Node 2 10.1.6.205 1. install (7.2.6 is installed here)

Download mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz Binary Package (which contains ndb, mysql) 1root@10.1.6.205 :~ # Tar-C/usr/local-xzvf mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz2root@10.1.6.205:/usr/local # ln-s/usr/local/mysql-cluster-gpl-7.2.8-linux2.6-i686/usr/local/mysql3root@10.1.6.205: /usr/local # cd mysql4root@10.1.6.205:/usr/local/mysql # scripts/mysql_install_db -- user = mysql5root@10.1.6.205:/usr/local/mysql # chown-R mysql: mysql/usr/local/mysql is the same as 10.1.6.2032. configure SQL nodes and store NDB node 01root@10.1.6.205:/usr/local/mysql # vim/etc/my. cnf02 [mysqld] 03 basedir =/usr/local/mysql/04 datadir =/usr/local/mysql/data/05 user = mysql06port = 330607 socket =/tmp/mysql. sock0809ndbcluster10max_connect_errors = 201711ndb-connectstring = 10.1.6.20512connect _ timeout = 3001314 [mysql_cluster] 15ndb-connectstring = 10.1.6.205 similarly 10.1.6.2033. configuration Management node 01root@10.1.6.205:/usr/local/mysql # vim/opt/cluster/config. ini02 [ndbd default] 03 NoOfReplicas = 2 04 DataMemory = 80 M # allocate the memory used by data storage. Each ndb occupies 05 IndexMemory = 18 M # allocate the memory used by index storage. Each ndb occupies 0607 [tcp default] 08 portnumber = 2205 # ndb listening port 0910 # Set Management node 11 [ndb_mgmd] 12 NodeId = 113 hostname = 10.1.6.20514datadir =/opt/cluster # Save log on MGM directory 1516 # Set storage node NDB117 [ndbd] 18 NodeId = 219 hostname = 10.1.6.20320datadir =/usr/local/mysql/data2122 # Set storage node NDB223 [ndbd] 24 NodeId = 325 hostname = 10.1.6.20526datadir =/usr/local/mysql/data2728 # Set SQL node 129 [mysqld] 30 NodeId = 431 hostname = 10.1.6.2033233 # Set SQL node 234 [mysqld] 35 NodeId = 536 hostname = 10.1.6.20537 [mysqld] # Run any ip connection 38 [mysqld] 4. start mysql terter1) first start the management node server. 2) Start the NDB storage node server. 3) Start the SQL node server. 1) execute the startup MGM node process 1root@10.1.6.205:/usr/local/mysql/bin #/usr/local/mysql/bin/ndb_mgmd-f/opt/cluster/config. the ini2MySQL Cluster Management Server mysql-5.5.22 ndb-7.2.6 must use the parameter-f or -- config-file to tell the ndb_mgm configuration file config. the location of the INI file. 2) on two storage node servers, if it is the first time to start the NDB process, you must first execute the following command: 1root@10.1.6.205: /usr/local/mysql/bin #/usr/local/mysql/bin/ndbd -- initial22013-08-28 23:40:36 [ndbd] INFO -- Angel connected to '10. 1.6.205: 1186 '2017-08-28 23:40:36 [ndbd] INFO -- Angel allocated nodeid: 2

 

Note: The-initial parameter is used only when NDB is started for the first time, or when the backup/recovery or configuration file changes and NDB is restarted. this parameter causes the node to delete any files created by an earlier NDB instance for restoration, including log files for restoration. if it is not the first time, use the following command

1root@10.1.6.205:/usr/local/mysql/bin #/usr/local/mysql/bin/ndbd3) Start the SQL node server 1root@10.1.6.203: /usr/local/mysql/bin #/usr/local/mysql/bin/mysqld_safe/etc/my. cnf & 5. view each node condition 01root@10.1.6.205: /usr/local/mysql #/usr/local/mysql/bin/ndb_mgm02 -- NDB Cluster -- Management Client -- 03ndb_mgm> show04Cluster Configuration05 ------------------- 06 [ndbd (NDB)] 2 node (s) 07id = 2 @ 10.1.6.203 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0, Master) 08id = 3 @ 10.1.6.205 (mysql-5.5.22 ndb-7.2.6, Nodegroup: 0) 0910 [ndb_mgmd (MGM)] 1 node (s) 11id = 1 @ 10.1.6.205 (mysql-5.5.22 ndb-7.2.6) 1213 [mysqld (API)] 4 node (s) 14id = 4 @ 10.1.6.203 (mysql-5.5.22 ndb-7.2.6) 15id = 5 @ 10.1.6.205 (mysql-5.5.22 ndb-7.2.6) 16id = 6 (not connected, accepting connect from any host) 17id = 7 (not connected, accepting connect from any host) 1819ndb_mgm>

 

6. test Note: Compared with Mysql that does not use the Cluster, the data operation method in the mysql cluster is not much different. note 1) the table must be created using the engine = NDB or engine = NDBCLUSTER option. 2) Each NDB table must have a primary key. if you do not define a primary key when creating a table, the NDB Cluster Storage engine automatically generates an implicit primary key. this implicit key also occupies space, just like any other table index. because there is not enough memory to accommodate these automatically created keys, it is easy to see problems. create a table on 203 SQL Node 1

01root@10.1.6.203:/usr/local/mysql/bin #/usr/local/mysql/bin/mysql-uroot-p02mysql> use test; 03 mysql> create table dave (num int (10) engine = ndb; 04 mysql> show create table dave \ G; 05 **************************** 1. row *************************** 06 Table: dave07Create Table: create table 'Dave '(08 'num' int (10) DEFAULT NULL09) ENGINE = ndbcluster default charset = latin1101 row in set (0.00 sec) 1112 mysql> insert into dave13-> values14-> (100); 15 Query OK, 1 row affected (0.01 sec) 16 mysql> select * from dave; 17 + ------ + 18 | num | 19 + ------ + 20 | 100 | 21 + ------ + then view the table 1root@10.1.6.205 on 205 SQL Node 2: /usr/local/mysql #/usr/local/mysql/bin/mysql-uroot-p2mysql> use test3mysql> select * from dave; 4 + ------ + 5 | num | 6 + ------ + 7 | 100 | 8 + ------ + test OK follow table 1 mysql> select * from ndbinfo. memoryusage; 2 + --------- + -------------- + -------- + ------------ + ---------- + nodes + 3 | node_id | memory_type | used | used_pages | total | total_pages | 4 + --------- + hour + -------- + ------------ + ---------- ------------- + 5 | 2 | Data memory | 851968 | 26 | 83886080 | 2560 | 6 | 2 | Index memory | 212992 | 26 | 19136512 | 2336 | 7 | 3 | Data memory | 851968 | 26 | 83886080 | 2560 | 8 | 3 | Index memory | 212992 | 26 | 19136512 | 2336 | note: if the usage is full, the access will fail. You need to adjust the DataMemory and IndexMemory parameters. all configuration files must be adjusted and restarted to take effect. 7. close cluster1root@10.1.6.205:/usr/local/mysql/bin #/usr/local/mysql/bin/ndb_mgm-e shutdown2Connected to Management Server at: 10.1.6.205: 118633 NDB Cluster node (s) have shutdown.4Disconnecting to allow management server to shutdown. close the SQL node mysqld service 1root@10.1.6.203:/usr/local/mysql/bin #/usr/local/mysql/bin/mysqladmin-uroot-p shutdown2Enter password: 3130829 02:19:57 mysqld_safe mysqld from pid file/usr/local/mysql/data // debian. pid ended4 [1] + Done/usr/local/mysql/bin/mysqld_safe/etc/my. cnf

 

The above is the initial deployment of the mysql cluster, and then the haproxy + keepalive dual-host high availability will be written.