### 准备工作
**所有节点：**
- **系统：**
`CentOS 7.6`
- **硬件配置：**
`1核1G+2GSwap`
**管理节点：**
- IP/主机：`192.168.10.3(db1)`
**数据节点：**
- IP/主机：`192.168.10.4(db2)/192.168.10.5(db2)`
**SQL节点：**
- IP/主机：`192.168.10.6(db1)/192.168.10.7(db2)`
 
### 初始化工作
#### `db1执行`
配置hosts文件
```bash
vim /etc/hosts
```
```ini
192.168.10.3 db1
192.168.10.4 db2
192.168.10.5 db3
192.168.10.6 db4
192.168.10.7 db5
```
安装ansible
```bash
yum install -y ansible
```
配置ansible
```bash
vim /etc/ansible/hosts
```
```ini
[db_nodes]
db2
db3
[sql_nodes]
db4
db5
```
创建公私秘钥对
```bash
ssh-keygen -t rsa
```
拷贝公钥到db2-5
```bash
ssh-copy-id -i ~/.ssh/id_rsa.pub root@dbX #拷贝db1的公钥到dbX,X为2-5
```
ping所有被管理主机
```bash
ansible all -m ping
```
```py
db2 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
db5 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
db3 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
db4 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
```
关闭防火墙
```bash
systemctl stop firewalld
systemctl disable firewalld
```
ansible配置db2-5的hosts文件
```bash
ansible all -m copy -a "src=/etc/hosts dest=/etc/hosts"
```
ansible关闭db2-5的防火墙
```bash
ansible all -m shell -a "systemctl stop firewalld&&systemctl disable firewalld"
```
 
### 安装管理节点(db1)
#### 下载MySQL集群相关软件
```bash
cd ~
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
tar xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar
```
#### 安装和移除软件包
```bash
yum -y install perl-Data-Dumper libaio-devel
yum -y remove mariadb-libs
```
#### 安装MySQL集群相关软件包
```bash
cd ~
rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm
```
#### 配置管理节点
创建配置目录
```bash
mkdir -p /var/lib/mysql-cluster
```
创建配置文件
```bash
cd /var/lib/mysql-cluster
vi config.ini
```
```ini
[ndb_mgmd default]
# Directory for MGM node log files
DataDir=/var/lib/mysql-cluster
[ndb_mgmd]
#Management Node db1
HostName=db1
[ndbd default]
NoOfReplicas=2 # Number of replicas
DataMemory=256M # Memory allocate for data storage
IndexMemory=256M # Memory allocate for index storage
#Directory for Data Node
DataDir=/var/lib/mysql-cluster
[ndbd]
#Data Node db2
HostName=db2
[ndbd]
#Data Node db3
HostName=db3
[mysqld]
#SQL Node db4
HostName=db4
[mysqld]
#SQL Node db5
HostName=db5
```
#### 启动管理节点
```bash
ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini
```
```
MySQL Cluster Management Server mysql-5.6.28 ndb-7.4.10
2019-07-22 02:00:08 [MgmtSrvr] INFO -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2019-07-22 02:00:08 [MgmtSrvr] INFO -- Successfully created config directory
```
#### 查看节点信息
```bash
ndb_mgm
```
```sql
ndb_mgm> show
```
```
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from db2) #因为还未搭建，显示未连接
id=3 (not connected, accepting connect from db3)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.10.3 (mysql-5.6.28 ndb-7.4.10)
[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from db4)
id=5 (not connected, accepting connect from db5)
```
 
### 安装数据节点(db2-3)
#### `db1执行`
拷贝安装包到db2-3并解压
```bash
cd ~
ansible db_nodes -m copy -a "src=./MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar dest=/root"
ansible db_nodes -m shell -a "cd /root;tar xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar"
```
安装和移除软件包
```bash
ansible db_nodes -m shell -a 'yum -y install perl-Data-Dumper libaio-devel'
ansible db_nodes -m shell -a 'yum -y remove mariadb-libs'
```
安装MySQL集群相关软件包
```bash
ansible db_nodes -m shell -a 'cd /root;rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm&&rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm&&rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm'
```
#### 配置数据节点(db2-3)
#### `db2-3执行`
```bash
vim /etc/my.cnf
```
```ini
[mysqld]
ndbcluster
ndb-connectstring=db1 # IP address of Management Node
[mysql_cluster]
ndb-connectstring=db1 # IP address of Management Node
```
创建数据目录
```bash
mkdir -p /var/lib/mysql-cluster
```
启动数据节点
```bash
ndbd
```
```
2019-07-22 02:02:15 [ndbd] INFO -- Angel connected to 'db1:1186'
2019-07-22 02:02:15 [ndbd] INFO -- Angel allocated nodeid: 3
```
 
### 安装SQL节点(db4-5)
#### `db1执行`
拷贝安装包到db4-5并解压
```bash
cd ~
ansible sql_nodes -m copy -a "src=./MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar dest=/root"
ansible sql_nodes-m shell -a "cd /root;tar xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar"
```
安装和移除软件包
```bash
ansible sql_nodes -m shell -a 'yum -y install perl-Data-Dumper libaio-devel'
ansible sql_nodes-m shell -a 'yum -y remove mariadb-libs'
```
安装MySQL集群相关软件包
```bash
ansible sql_nodes -m shell -a 'cd /root;rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm&&rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm&&rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm'
```
#### 配置SQL节点(db4-5)
#### `db4-5执行`
```bash
vim /etc/my.cnf
```
```ini
[mysqld]
ndbcluster
ndb-connectstring=db1 # IP address for server management node
default_storage_engine=ndbcluster # Define default Storage Engine used by MySQL
[mysql_cluster]
ndb-connectstring=db1 # IP address for server management node
```
启动SQL节点
```bash
systemctl start mysql
```
查看初始密码
```bash
cd ~
cat .mysql_secret
```
```
# The random password set for the root user at Fri Jul 19 09:50:43 2019 (local time): 9uGYuWofEZpg8EzC
```
数据库安全加固
```bash
mysql_secure_installation
```
连接数据库
```bash
mysql -u root -p
```
创建远程用户
```sql
mysql> create user 'root'@'%' identified by '123456';
mysql> flush privileges;
```
 
### 监控集群
#### `db1执行`
查看集群几点
```bash
ndb_mgm
```
```sql
ndb_mgm> show
```
```
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.10.4 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=3 @192.168.10.5 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.10.3 (mysql-5.6.28 ndb-7.4.10)
[mysqld(API)] 2 node(s)
id=4 @192.168.10.6 (mysql-5.6.28 ndb-7.4.10)
id=5 @192.168.10.7 (mysql-5.6.28 ndb-7.4.10)
```
查看集群信息
```bash
ndb_mgm -e "all status" #查看集群状态
ndb_mgm -e "all report memory" #查看集群内存
```
 
### 测试集群
#### `db4执行`
```sql
创建测试数据
mysql> create database d1;
mysql> Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
```
```sql
mysql> insert into t1(name) values('ynotes.cn');
```
```sql
mysql> select * from t1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | ynotes.cn |
+----+-----------+
1 row in set (0.00 sec)
```
#### `db5执行`
查询数据
```sql
mysql> use d1
```
```sql
mysql> select * from t1;
+----+-----------+
| id | name |
+----+-----------+
| 2 | ynotes.cn |
+----+-----------+
```
`总结:通过测试发现，数据已经同步到db5节点，MySQL集群搭建成功！`
##### **参考**:`https://www.howtoforge.com/tutorial/how-to-install-and-configure-mysql-cluster-on-centos-7/`
