MySQL cluster Solution Problems

1. What does "NDB" mean? It means: "Network Database". NDB (also called NDB Cluster or NDBCLUSTER), you can use it to allow MySQL to support clusters.   2. What is the difference between cluster and replication? In the replication system, a MySQL master server updates one or more slave servers. transactions are committed in sequence, so a slow transaction may lead to a lag of time from the slave server over the master server. this also means that if an error occurs on the master server, the slave server may lack the transaction log that records the last small part. if the transaction security storage engine is used, for example, InnoDB, the transaction log will be completely recorded on the slave server or not recorded at all, however, replication does not guarantee consistency between the master and slave servers. in a MySQL cluster, all data is always synchronized, and transactions committed on any data node are synchronized to all other data nodes. if one data node fails, other normal data nodes can maintain data consistency.   3. Do I need a specific network structure to use the cluster? How do computers in the cluster communicate? MySQL clusters are suitable for high-speed bandwidth environments and are connected using TCP/IP. its performance is directly related to the connection rate between hosts. the minimum rate in a cluster must be MB Ethernet or equivalent. we recommend that you use G-level networks if possible. see Section 15.10, "Using High-Speed Interconnects with MySQL Cluster ",   4. How many computers are needed in the cluster? Why? The cluster must have at least three computers. however, we recommend that you have four servers. Two are running management nodes and SQL nodes, and the other two are used as data nodes. the purpose of taking two data nodes is to improve data redundancy. The management node is placed on an independent host to ensure that the arbitration service is provided in case a data node fails.   5. What are the computers in the cluster? The MySQL cluster is physically and logically organized, and each computer serves as the basic physical element. the basic logical elements in the cluster are called nodes, and each basic logical and functional element in the cluster is also called the cluster host. there are three node types, each of which has different roles in the cluster, respectively: * MGM node: provides management services for the entire cluster, including startup, shutdown, backup, and data configuration. the management node is provided by the ndb_mgmd application, and the client of the Management node uses ndb_mgm. * Data Node: stores and copies data. The function of a data node is handled by An NDB data node process ndbd. * SQL node: This is just a simple MySQL server instance (mysqld). It uses the -- NDB-cluster option when starting to support the NDB Cluster Storage engine.   6. What kind of operating system platform can I use clusters? MySQL clusters support most UNIX-like operating systems, including Linux, Mac OS X, Solaris, BSD, HP-UX, Aix, Irix, in addition to Novell Netware. currently, MySQL clusters do not support windows. however, we are working hard to enable clusters to support other platforms, such as Windows. Our goal is to enable clusters on all platforms supported by MySQL. See http://www.mysql.com/support/supportedplatforms.html for details.   7. Are there any hardware requirements for cluster usage? MySQL clusters can run on any NDB-enabled platform. obviously, the faster the CPU, the larger the memory, the more obvious the cluster performance improvement, the 64-bit CPU may be faster than the 32-bit processor. each machine that acts as a data node must have enough memory to store the shared database (for details, see How much RAM do I Need ?). Nodes communicate with hardware through TCP/IP networks. To use SCI, you must have a specific network device.   8. How much memory does it need? Can I use a disk completely? In MySQl 5.0, clusters can only be based on memory. this means that the data (including indexes) of all tables are stored in the memory. if your data is as big as 1 GB and you want to copy a copy to the cluster, you need 2 GB of memory (1 GB for each copy ), this is the memory required by the computer running the cluster relative to other operating systems. If the memory usage on a data node exceeds the available range, the operating system uses swap memory to reach the upper limit of DataMemory. however, this will cause serious performance degradation and may lead to slow down the corresponding time. for this reason, we do not recommend using disk swap space in the production environment. under any circumstances, as long as the DataMemory ceiling is reached, all operation requests (such as insertion) will fail. We have implemented a disk-based cluster in MySQL 5.1, but we do not intend to add this feature in MySQL 5.0. You can use the following formula to estimate the memory size required for a cluster data node: (SizeofDatabase × NumberOfReplicas × 1.1)/NumberOfDataNodes To calculate the exact amount of memory required, you need to find the space occupied by each record in each table in the database, and multiply it by the number of records in each table, for how much space each record occupies, see (href = "/docs/MySQL_51_zh/mysql_51.html" target = "_ blank" title = "11.5. data Type Storage Requirements "> Section 11.5," Data Type Storage Requirements "). You must also pay attention to the following indexes for each field: * Each primary key or hash index record in the NDBCluster table needs 21-25 bytes. These indexes use IndexMemory. * Each Sorting index record requires 10 bytes. They use DataMemory. * You can also create a Sorting index when creating a primary key or unique index, unless you specify using hash when creating an index. In other words: O Each primary key or unique index record in the cluster data table usually occupies 31-35 bytes. O However, if you specify using hash when creating a primary key or a unique index, each record only needs 21-25 bytes. Note: If you specify using hash to create a primary key or unique index in a MySQL cluster, the table usually runs fast- In some cases, updating data is usually faster than a table without using hash. 20-30%. this is because it only requires a small amount of memory (because no Sorting index is created), and uses a relatively small amount of CPU resources (because it only needs to read/update less indexes ). however, this also means that the query will be slow if the range scan is not used. The memory required by the computing cluster is. You can find a tool named ndb_size.pl from MySQLForge. this Perl script connects to the current MySQL Server (not a MySQL cluster) and then reports how much space is required if the current database is converted to NDBCluster. for details, see Section 15.9.13, "ndb_size.pl ". Note: Each MySQL cluster Table requires a primary key. If no primary key is defined, the NDB storage engine automatically creates one, instead of specifying the using hash. You cannot accurately calculate the memory required for the MySQL Cluster Index at any time. However, when you use more than 80% of DataMemory or IndexMemory, it will write a piece of information into the log, then 85%, 90%, and so on.   9. During cluster configuration, the loading process is interrupted abnormally and the error message is as follows. Why?   ERROR 1114: The table 'my _ terter_table 'is full I'm trying to populate a Cluster database. The loading process terminates prematurely and I get an error message like this one: ERROR 1114: The table 'my _ terter_table 'is full Why is this happening? This is probably because the memory you set is insufficient to hold all data tables and their indexes, including the primary keys required by the NDB storage engine and the indexes automatically created if the primary keys are not defined. The memory size of all data nodes is the same, because no data node in the cluster can use more memory than the minimum memory of other data nodes. in other words, if there are four computers in the cluster, if the memory of the three computers is 3 GB, and the other one is only 1 GB, each data node can only generate 1 GB of memory for the cluster.   10. The MySQL cluster uses the TCP/IP protocol. Does this mean that I can put some nodes remotely across the Internet to use the cluster? Under such conditions, the MySQL cluster is unlikely to have stable performance, because the MySQL cluster is designed and implemented in a good high-speed connection rate environment, for example, 100 Mbps LAN or G-level Ethernet-the latter is preferred. we didn't perform tests in a slower condition, so we couldn't guarantee its performance. Similarly, you must be aware that the communication between the two nodes in the MySQL cluster is insecure. They are not encrypted or protected by any protection mechanism. A secure cluster is deployed in a private network within the firewall, and data and Management Nodes cannot be directly accessed in the external world (SQL nodes should also be protected like other MySQL servers ).   11. Do I need to learn a new programming or query language to use the MySQL cluster? No. Although some specific commands are used to manage and set the cluster, other standard (my) SQL queries and commands are as follows: * Create/modify/delete a table * Insert/update/delete data * Create/modify primary key/unique index For specific configuration parameters and files required for setting MySQL clusters, see section 15.4.4, "configuration file ". Some simple management client commands in the MySQL cluster, such as the commands used to start or close cluster nodes. For details, see section 15.7.2, "commands in the management client ".   12. How do I know some warning/error messages when I use a MySQL cluster? There are 2 methods, as follows: * In the mysql client, run the show errors or show warnings command to view the error or warning information directly. The error and warning information can also be displayed in the MySQL Query Browser. * At the system shell prompt, run perror -- ndb error_code.   13. Is the MySQL cluster transaction-safe? What transaction isolation level does it support? Yes. NDB storage engine tables support transactions. In MySQL 5.0, clusters only support read committed isolation.   14. What storage engines are supported by MySQL cluster? MySQL clusters only support the NDB storage ENGINE. That is to say, to share a table in a cluster node, you must specify ENGINE = NDB (or ENGINE = NDBCLUSTER ). MySQL clusters can also use MyISAM or InnoDB Storage engine to create data tables. Tables are not stored and shared among cluster nodes. They are independent of the created MySQL server or instance.   15. Which MySQL version supports clusters? Do you need to compile from source code? Clusters are supported in all MySQL-max binary release series of MySQL 5.0. The following lists the exceptions that you want to pay attention. you can use The show variables like 'have _ % 'or show engines statement to determine whether The NDB storage engine is supported. For details, see Section 5.3, "The mysqld-max Extended MySQL Server ". Linux users should note that the standard MySQL Server RPM package does not contain NDB. from MySQL 5.0.4, the RPM package starts to separate the NDB storage engine from the attached management tools. For details, see the ndb rpm download section on the MySQL 5.0 download page (before 5.0.4, the. tar.gz package of-max must be downloaded. this may happen in the future, but it is not necessary. You may use the Linux RPM package manager you prefer ). you can also compile and support NDB from the-max source code, but it does not need to be so troublesome. to download the latest version, please refer to the http://dev.mysql.com/downloads/mysql/5.0.html.   16. In the event of a catastrophic accident-for example, if the whole city is powered off and even the UPS is disconnected, will I lose all the data? All committed transactions have been recorded in the log. therefore, some data may be lost in an emergency, but it is also very limited. by reducing the number of operations in the transaction, you can also reduce the loss of data. (It is unwise to always execute a large number of operations in any transaction)   17. Can FULLTEXT indexes be used in clusters? In MySQL 5.0, The NDB storage engine does not support FULLTEXT indexes, and does not support other storage engines except MyISAM. We are trying to support these indexes in future releases.   18. Can I run multiple nodes on a computer? Yes, but it is not recommended. first, the cluster is used to improve data redundancy. Therefore, each node should be on a different computer. if you place multiple nodes on the same computer, if it goes down, these nodes will be lost. mySQL clusters can run on low-cost (cost-free) Operating Systems Based on commercial machines. To protect important data that cannot be lost, it is worthwhile to expand one or two additional machines. this task is completed by the ndb_mgmd and ndb_mgm processes on an operating system with a MHz Pentium CPU and enough memory, with little overhead. It is acceptable to put multiple data nodes in the MySQL cluster on one machine for learning or testing purposes. However, this cannot be used in the production environment.   19. Can I add a node to the cluster without restarting it? Not yet. To add a new MGM or SQL node to the cluster, restart the node. adding a new data node is more complicated. The following steps are required: 1. Back up all data in the cluster. 2. Completely shut down the processes related to the cluster and cluster nodes. 3. Additional option -- initial to start the cluster. 4. Restore the backup data to the cluster. In future MySQL cluster releases, we hope to achieve the "hot" (online) mode to reset the cluster function to reduce the trouble of adding new nodes. however, it is not implemented in MySQL 5.0.   20. Are there any restrictions on using a MySQL cluster? The NDB table in MySQL 5.0 has the following limitations: * TEMPORARY tables are not supported. If you specify the ENGINE = NDB or ENGINE = NDBCLUSTER parameter when executing the create temporary table statement, an error is reported. * FULLTEXT indexes and prefix indexes are not supported. You can only index the entire field. * Spatial data types are not supported. For details, see Chapter 16 and Spatial Extensions. * Only full rollback transactions are supported. Partial rollback and rollback to the storage point are not supported. * The maximum number of attributes in a table is 128, and each attribute name cannot exceed 31 characters. For each table, the Union length between the database and the table indicates a maximum of 122 characters. * The maximum size of a record in a table is 8 KB, excluding the Blob value. The size of the table depends on various constraints, especially the maximum available memory for each data node. * The NDB storage engine does not support foreign keys. They are not supported like MyISAM. For more restrictions, see section 15.11, "known limitations of MySQL cluster ".   21. How to import an existing MySQL database to a cluster? You can import Mysql Data of various versions to the cluster. except for the restrictions mentioned in this FAQ, Title = "15.11. known limitations of MySQL cluster "> section 15.11," known limitations of MySQL Cluster, the only requirement is that the table to be imported must be the NDB storage engine, that is, the table created using engine = NDB or engine = ndbcluster.   22. How do nodes in the cluster communicate with other nodes? Cluster nodes can communicate with each other in the following ways: TCP/IP, SHM (shared memory), and SCI (Scalable consistency interface ). if possible, SHM is used for communication between multiple nodes installed on the same cluster host by default. However, in MySQL 5.0, this is only for the purpose of testing. SCI is a high-speed (1 GB per second or higher) protocol for building scalable multi-processor systems; it requires special hardware and drivers. for details, see Section 15.10, "Using High-Speed Interconnects with MySQL Cluster ".   23. What is "arbitrator (Arbitration node )"? If one or more nodes are down (or fail for other reasons), you need to make other nodes see this situation. in fact, in a partitioned network, two groups of nodes may be isolated from each other, which is also called Split-brain ". this is certainly not a good situation, because each group of nodes wants to think of themselves as a part of the cluster. When a node in the cluster goes down, there are two possibilities. if more than 50% of nodes can communicate with each other, or sometimes the "majority principle", these nodes are considered as clusters. if the number of nodes in this group is exactly half, the arbitration server should play a role: In this case, the group of nodes where the arbitration server is located is considered as a cluster, the other group is closed. The preceding information is a bit simple. The following is a complex node grouping case analysis: If at least all nodes in a node group are available, the partition network is not in this range, because in this case, any part of the cluster cannot form a new cluster. this problem occurs only when all nodes in a node group are available. In this case, a partition Network ("Split-brain") may occur. in this case, arbitration is required. all cluster nodes recognize an arbitration server, that is, the Management Server. However, any server configuration in the cluster can be called an arbitration server. the arbitration Server accepts the first cluster nodes, connects them, and closes other cluster groups. the choice of arbitration Server is controlled by the ArbitrationRank parameter of the MySQL Server and the Management node Server (for details, see Section 15.4.4.4, "Defining the Management Server, the server selected as the arbitration role does not require high load, so it does not require fast speed and large memory.   24. What data types does a MySQL cluster support? MySQL clusters support all common data types, except MySQL-related Spatial extension types (for details, see Chapter 16 and Spatial Extensions). In addition, NDB Table indexes are also different. note: MySQL cluster tables (NDB or NDBCLUSTER tables) only support fixed-length records. this also means (for example) If a record contains the VARCHAR (255) field, it requires a space of 255 characters (as large as the character set used in the data table and the space required for verification), regardless of the actual number of characters stored. this problem will be solved in future releases. For details, see Section 15.11, "Known Limitations of MySQL Cluster ".   25. How do I start and close a MySQL cluster? Start each node in the cluster in the following order: 1. Run the ndb_mgmd command to start the management node. 2. Start each data node with ndbd. 3. Use mysqld_safe -- user = mysql & command to start each SQL node. The above commands must all be run in the System shell (either on the server or through the terminal ). you can run the SHOW or all status command in the ndb_mgm client tool on the Management node to check whether the cluster is running. Run the SHUTDOWN command on the MGM client to close the running cluster. In addition, you can also run the following command on the Management node host to close the cluster: Shell> ndb_mgm-e "SHUTDOWN" Note that the content in the brackets can be anything else, and shutdown can be case-insensitive. Any one of these commands can calmly close ndb_mgm, ndb_mgmd (incorrect ?), Any process in ndbd. The SQL node server running the MySQL cluster is shut down through mysqladmin shutdown. For more details, see section 15.7.2, "commands in the management client", section 15.3.6, "Safe Shutdown and restart ".   26. What should I do if the cluster is closed? Cluster data originally stored in the memory will be written to the disk, and will be reloaded to the memory at the next cluster startup.   27. Is it better to use multiple management nodes in the cluster? This helps to automatically prevent faults. you can only have one MGM node to control the cluster at any time. However, you can also configure one master MGM controller and one or more additional controllers as the slave when the master controller fails. For details about cluster management nodes, see section 15.4.4, "configuration file ",   28. Can I mix multiple different hardware and operating systems in a MySQL cluster? Yes. As long as all machines and operating systems have "endianness" You can (all the big tails or small tails are difficult to understand. Please search for them by yourself using the search engine ). different Versions of MySQL cluster releases may be used between different nodes. however, we recommend that this is only part of the rolling upgrade step. For details, see section 15.5.1, "Grouping A rolling restart of the cluster ".   29. Can I run two data nodes on a host? Or two SQL nodes? Yes, you can. if there are multiple data nodes, we recommend (not required) that different data directories have their own nodes. if you want to run multiple data nodes on the same machine The mysqld instance must use different TCP/IP ports. However, running multiple cluster nodes on each machine does not support the production environment.   30. Can I use the host name in a MySQL cluster? Yes, the cluster host can use DNS or DHCP. however, if your application requires "5 9 (99.999%)" reliability, we recommend using a fixed IP address. DNS or DHCP services can be introduced in the Automatic Fault Protection System. It is better to use them less.   31. How do I handle MySQL users in a cluster with multiple MySQL servers? Account and permission information will not be automatically synchronized between different MySQL servers accessing the MySQL cluster. Therefore, you must copy one copy between each SQL node.

 

 

 

 

 

Appendix: My ndb questions about mysql on itpub