Cloud computing platform (data)-mysql High Availability platform for building master-slave

Installation environment and MySQL software

The installation environment of the company's MySQL cluster is Red Hat 5 or client os6.8

Msql cluster software version 7.2.8 (http://www.mysql.com/downloads/cluster)

MySQL cluster Installation

A) node configuration

1. wget MySQL package to/software

2. decompress the MySQL software package

Tar-zxvf mysql-cluster-gpl-7.2.8-linux2.6-x86_64.tar.gz

3. Move the directory to/usr/local and rename it to MySQL.

Music mysql-cluster-gpl-7.2.8-linux2.6-x86_64.tar.gz MySQL

CD/usr/local/MySQL

4. Create a MySQL user group and user

Groupadd MySQL

Useradd-G MySQL

5. Initial

Sciprts/mysql_install_db-user = MySQL

6. Modify permissions for mysql users

Chown-r root.

Chown-r Mysql Data

Chgrp-r MySQL.

7. Put mysql. server in init. d.

CP support-files/MySQL. Server/etc/rc. d/init. d/mysqld

8. Permission

Chmod + x/etc/rc. d/init. d/mysqld

Chkconfig --- add mysqld

(* Do not start MySQL at this time)

B) Management node configuration

1. decompress the MySQL package wget

2. Move related management tools to the bin directory

Tar-zxvf mysql-cluster-gpl-7.2.8-linux2.6-x86_64.tar.gz

Rm mysql-cluster-gpl-7.2.8-linux2.6-x86_64.tar.gz

Mkdir/usr/src/MySQL-MGM

CD/usr/src/MySQL-MGM

Music mysql-cluster-gpl-7.2.8-linux2.6-x86_64/bin/ndb_mgm.

Music mysql-cluster-gpl-7.2.8-linux2.6-x86_64/bin/ndb_mgmd.

Chmod + x ndb_mg *

MV ndb_mg */usr/bin

CD

Rm-RF/usr/src/MySQL-MGM

C) configuration file

1. Configure on the Management NodeConfig. iniFile

Mkdir/var/lib/MySQL-Cluster

CD/var/lib/MySQL-Cluster

VI config. ini

Add the following content to config. ini:

[Ndbd default]

Noofreplicas = 2

[Mysqld default]

[Ndb_mgmd default]

[TCP default]

# Managment Server

[Ndb_mgmd]

Hostname = 192.168.0.3 # manage the IP address of node server server3

# Storage engines

[Ndbd]

Hostname = 192.168.0.1 # IP address of MySQL cluster server1

Datadir =/var/lib/MySQL-Cluster

[Ndbd]

Hostname = 192.168.0.2 # IP address of MySQL cluster server2

Datadir =/var/lib/MySQL-Cluster

[Mysqld]

2.Configure on the node

# Vi/etc/My. CNF

[Mysqld]

Ndbcluster

NDB-connectstring = 192.168.0.3 # server3 IP Address

[Mysql_cluster]

NDB-connectstring = 192.168.0.3 # server3 IP Address

 

Mkdir/var/lib/MySQL-Cluster

 

3. MySQL cluster management

1. Start a MySQL Cluster

MySQL cluster startup sequence:

Start a management Node

Bin/ndb_mgmd-F/var/lib/MySQL-cluster/config. ini-initial

Bin/ndb_mgm-e show (view status)

-NDB Cluster -- management client --

Ndb_mgm> show

Connected to management server at: localhost: 1186

Cluster configuration

---------------------

[Ndbd (NDB)] 2 node (s)

Id = 2 @ 192.168.0.1 (Version: 4.1.9, nodegroup: 0, Master)

Id = 3 @ 192.168.0.2 (Version: 4.1.9, nodegroup: 0)

[Ndb_mgmd (MGM)] 1 node (s)

Id = 1 @ 192.168.0.3 (Version: 4.1.9)

[Mysqld (API)] 2 node (s)

Id = 4 (Version: 4.1.9)

Id = 5 (Version: 4.1.9)

Ndb_mgm>

Connect a node to a management Node

#/Usr/local/MySQL/bin/ndbd-initial (start with-initial for the first start)

Start a MySQL Node

#/Etc/rc. d/init. d/mysqld start

(* Initial is required for the first startup)

MySQL cluster troubleshooting

During the installation and use of the MySQL cluster, I encountered many problems. Now I want to list the problems and solve them again. I hope you will spend less time on detours in future use.

 

1. the following error is reported during data import:

Error 1005 (hy000) at line 25: Can't create table 'tuge. pangolin_fnc_accountverification '(errno: 140)

Error | 1296 | got error 738 'record too Day' from NDB

Cause: the table's row data is too large.

Solution: You need to change the table structure so that the data size of the maximum single row is smaller than 8 KB!

 

2. When importing large amounts of data, the following error occurs:

Error 1297: Got temporary error 'redo 'log overloaded.

Error: Got temporary error 1204 'temporary failure, distribution changed 'from ndbcluster

Error: Got temporary error 1234 'redo log files overloaded (increase disk hardware) 'from ndbcluster

Cause Analysis: this error indicates that the redo log is used up and needs to be added.

Solution: Modify the config. ini file and add the following parameters:

Fragmentlogfilesize = 256 m

Nooffragmentlogfiles = 16

Nooffragmentlogfiles can be changed to a greater value, but it will be slower during initialization.

 

3. When importing a large amount of data, the following error occurs:

Error 1114 (hy000) at line 54: The table 'GPS _ led_sendadverthistory 'is full

Cause analysis: the memory or hard disk space you allocated has been used up (if you use a disk table), you need to use the NDB management node client and log on to MySQL to view the specific cause, log on through ndb_mgm, run the following command:

All report memory: Check whether the allocated memory is used up. If the disk table is used, log on to the mysqld node and run the following query to check whether the disk is insufficient:

Select tablespace_name, file_name, extent_size * total_extents/1024/1024 as total_mb, extent_size * free_extents/1024/1024 as free_mb, extra from information_schema.files where file_type = "datafile ";

Solution: if the data memory is insufficient, directly change

Datamemory = 11480 m

Indexmemory = 1024 m

Increase according to actual situation

If a disk table is used and the disk file space is insufficient, you need to add the disk file in the following format:

Alter tablespace ts_1

Add datafile 'data _ 4. dat'

Initial_size 10240 m

Engine ndbcluster;

4. the following error is reported during restoration:

Configuration error: Error: cocould not alloc node ID at 192.168.100.223 port 1186: Id 12 configured as ndbd (NDB), connect attempted as mysqld (API ).

Unable to connect to management server.

Cause: this error is reported because mysqld is connected during restoration and no empty mysqld node is added during NDB configuration.

Solution: add several empty mysqld nodes in config. ini. The syntax is as follows:

[Mysqld]

[Mysqld]

[Mysqld]

[Mysqld]

 

5. When importing a large amount of data or executing a large number of insert or update operations, the following errors often occur:

Error 1297 (hy000) at line 1: Got temporary error 1218 'send buffers overloaded in NDB kernel 'from ndbcluster

There are several reasons for this error. First, the sendbuffermemory parameter is set too small. Second, it is related to the binary log of mysqld. Third, it is related to the network environment.

Solution: Check whether the network environment is OK, whether the duplex mode troops are deployed, whether the network is a gigabit network or a hundred megabytes, and increase the sendbuffermemory settings. Generally, 2 Mb is enough. Set totalsendbuffermemory to 256 MB, if this error persists, we recommend that you disable MySQL binary logs. comment out the relevant parameters in CNF, for example:

# Log-bin = mysql-bin

 

6. error logs

Status: temporary error, restart Node

Message: system error, node killed during node restart by other node (internal error, programming error or missing error message, please report a bug)

Error: 2303

Error Data: node 12 killed this node because GCP stop was detected

Error object: ndbcntr (line: 273) 0x00000006

Cause Analysis: this problem occurs because the UNDO log file space is used up!

Solution: add the UNDO log space file. The syntax is as follows:

Alter logfile group lg_1

Add undofile 'undo _ 2. Log'

Initial_size 2048 m

Engine ndbcluster;

 

7. the following error occurs during restoration:

Temporary error: 266: time-out in NDB, probably caused by deadlock

Cause Analysis: this problem occurs when the lock exceeds 1.2 s.

Solution: Add transactiondeadlockdetectiontimeout = 10000 to config. ini. The default value is 1200.

 

8. When creating a log file group, the following error occurs:

Error code: 1528 failed to create logfile Group

Use show warnings; to view the detailed error:

Got error 1504 'out of logbuffer memory 'from NDB

Failed to create logfile Group

Cause: the error occurs because the sharedglobalmemory value is smaller than the undo_buffer_size value.

Solution: Increase the value of sharedglobalmemory. In the past, my undo_buffer_size was 128, but my sharedglobalmemory was 80. Set sharedglobalmemory to MB to solve the problem.