Mysql-cluster environment configuration

Configure the Mysql-cluster environment and the hardware environment

4 machines

192.168.1.10 management node

192.168.1.11 storage node SQL node

192.168.1.12 storage node and SQL node

192.168.1.13 SQL node

Software environment:

Operating system: CentOS 6.5 (64-bit)

Mysql version: MySQL-Cluster-gpl-7.3.5-1.el6.x86_64.rpm-bundle.tar

Mysql-cluster-gpl-7.3.5-linux-glibc2.5-x86_64.tar.gz (two files are needed)

Installation preparation:

1. decompress mysql-cluster-gpl-7.3.5-1.el6.x86_64.rpm-bundle.tar:

Distribute the extracted MySQL-Cluster-server-gpl-7.3.5-1.el6.x86_64.rpm to each machine;

Distribute the extracted MySQL-Cluster-client-gpl-7.3.5-1.el6.x86_64.rpm to the SQL node;

2. clear the old Mysql installation:

Yum-y remove mysql

Rpm-qa | grep mysql *

Delete the remaining mysql package in the following command format:
Rpm-e -- nodeps mysql-libs-5.1.61-4.el6.x86_64

3. install the dependency Library:

Yum-y install libaio

Install MySQL Cluster:

Create a folder (the following three classes are used to create the corresponding folder)
Storage node: mkdir/var/lib/mysql/data
Management Node: mkdir/var/lib/mysql-cluster SQL node: folder authorization is not required.
Process DIR: mkdir/var/run/mysqld

You can use the following command to change the permission to write data:
Chmod-R 1777/var/lib/mysql
Chmod-R 1777/var/run/mysqld
Chmod-R 1777/var/lib/mysql-cluster

1. install MySQL-Cluster-server-gpl-7.3.5-1.el6.x86_64.rpm on each machine

Run command: rpm-Uvh MySQL-Cluster-server-gpl-7.3.5-1.el6.x86_64.rpm

After installation, the system will prompt that the default password is stored in the/root/. mysql_secret file (and sometimes do not prompt)

2. install MySQL-Cluster-client-gpl-7.3.5-1.el6.x86_64.rpm on the SQL node

Run command: rpm-Uvh MySQL-Cluster-client-gpl-7.3.5-1.el6.x86_64.rpm

Configuration management node:

1. Decompress mysql-cluster-gpl-7.3.5-linux-glibc2.5-x86_64.tar.gz and enter its

Find ndb_mgm and ndb_mgmd in the mysql-cluster-gpl-7.3.5-linux-glibc2.5-x86_64/bin directory and copy them to/usr/local/bin on the management node,

Then make the two files executable:

Cd/usr/local/bin

Chmod + x ndb_mgm *

1. Run the following command on the management node:

Cd/var/lib/mysql-cluster

Vi config. ini

The content is as follows:

[Ndbd default]

NoOfReplicas = 2

DataMemory = 80 M

IndexMemory = 20 M

[Ndb_mgmd]

# Management process options:

Hostname = 192.168.1.10 # Hostname or IP address of MGM node

Datadir =/var/lib/mysql-cluster # Directory for MGM node log files

[Ndbd]

HostName = 192.168.1.11

DataDir =/var/lib/mysql/data

[Ndbd]

HostName = 192.168.1.12

DataDir =/var/lib/mysql/data

[Mysqld]

HostName = 192.168.1.11

[Mysqld]

HostName = 192.168.1.12

[Mysqld]

HostName = 192.168.1.13

Configure storage nodes and SQL nodes:

Create a configuration file on all storage nodes and SQL nodes:

Vi/etc/my. cnf

The content is as follows:

[Client]

Socket =/var/lib/mysql. sock

[Mysqld]

Max_connections = 100

Datadir =/var/lib/mysql

Socket =/var/lib/mysql. sock

Ndbcluster

Ndb-connectstring = 192.168.1.10

[Mysqld_safe]

Log-error =/var/log/mysqld. log

Pid-file =/var/run/mysqld. pid

[Mysql_cluster]

Ndb-connectstring = 192.168.1.10

~

Start each node:

Pay attention to the sequence.

1. Start the management node: ndb_mgmd-f/var/lib/mysql-cluster/config. ini

(You need to run the command on the management node. This will create a directory/usr/local/mysql-cluster. if not, an error may be reported and you can manually create one.

Mkdir/usr/local/mysql

Mkdir/usr/local/mysql-cluster)

2. start the storage node: ndbd -- initial

(Must be executed on the storage node.) the -- initial parameter must be added at the first startup. in the future, you only need to execute ndbd;

3. start the SQL node: nohup mysqld_safe -- explicit_defaults_for_timestamp &

(Executed on the SQL node)

Before starting the service, check whether Port 3306 is occupied. run sudo netstat-anp | grep 3306 to check whether Port 3306 is occupied.

If it is occupied, kill it and start it again.

Check whether all nodes are started properly:

Run ndb_mgm on the management node to go to the cluster management console. run the show command to list all nodes:

The SQL node is not successfully started. solution:

Cd/var/lib/mysql

Ls

Rm-f ib * (delete files starting with lb)

Run sudo netstat-anp | grep 3306 to check whether Port 3306 is occupied.

If it is occupied, kill and kill

Then restart and execute the above operation.

Common commands:

1) ndb_mgmd management command: ndb_mgm

After the command is executed, you can continue to enter the command in the console.

View the status of all nodes in the cluster: show

2) command to stop the Cluster Server: ndb_mgm-e shutdown

If the cluster configuration is updated: rm/usr/mysql-cluster/ndb_1_config.bin.1

3) command for stopping an SQL node: mysqladmin-uroot shutdown

4) connect to MySQL on SQL Node: mysql-u root-pPASSWORD

After the command is executed, it is the MySQL console. you can continue to enter the command.

Modify the root user password:

Run the following command on each SQL node:

Mysqladmin-u root-pOLDPASSWORD password 'newpassword'

The OLDPASSWORD is generated in/root/. mysql_secret:

Cat/root/. mysql_secret (view the randomly generated password)

# The random password set for the root user at Tue Apr 15 11:08:59 2014 (local time): Nnqn0u1MRcpXkMue

Test cluster: log on to 192.168.1.12

Mysql-u root-ppassword

Mysql> create database transenly;

Query OK, 1 row affected (0.06 sec)

Mysql> use transenly;

Database changed

Mysql> create table ctest2 (I INT) ENGINE = NDB; (the ENGINE = NDB must be added when creating the TABLE, otherwise data synchronization fails)

Query OK, 0 rows affected (0.38 sec)

Log on to 192.168.1.11

Mysql-u root-ppassword

You can see that both the database and the data table are available, indicating that the data synchronization is successful.

Import data using SQL files

Place the SQL file in a local place, such as/var/lib/mysql/data/

The ENGINE = InnoDB in the SQL file needs to be modified to the ENGINE-NDB to ensure data synchronization

Log on to any SQL node

Mysql-u root-ppassword

Use transenly; (the database used, provided that the database is created first)

Source/var/lib/mysql/data/transenly. SQL

If Query OK is displayed, 0 rows affected (0.00 sec) indicates that the import is successful.

Then show tables; check whether all three SQL nodes are successfully created.

Project cannot connect to database:

The permission needs to be modified, which is accessible to the server at will.

Mysql> grant all privileges on *. * to root @ '%' identified by 'mysql' with grant option;

// Grant data access permissions to any host

3. mysql> flush privileges // The modification takes effect.

4. mysql> EXIT // EXIT the MySQL server