YNOTES笔记

### 准备工作 **所有节点：** - **系统：** `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)` &emsp; ### 初始化工作 #### `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" ``` &emsp; ### 安装管理节点(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) ``` &emsp; ### 安装数据节点(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 ``` &emsp; ### 安装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; ``` &emsp; ### 监控集群 #### `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" #查看集群内存 ``` &emsp; ### 测试集群 #### `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/`