Building and Scaling a Website – Part 3

Further Scaling and Redundancy

In part 3 we will add additional scaling for MySQL as well as add in some redundancy into our setup.

For this setup we will require at least 5 servers.

1 – Management Node (Where we will manage and control the cluster)

2 – Data Nodes (Where the MySQL Data will be stored)

2 – SQL Nodes (End points where web services connect to MySQL)

For purposes of this guide our 2 previously setup WebServers will run the SQL Nodes. The 3 additional servers used for MySQL will be new. If you followed the guide in part 2 the MySQL Server used here can be removed (After you have safely stored a backup of your database).

Management Node

1). In your IONOS Cloud Panel first shutdown your 3 new servers and then add them to your private network. Once added startup all 3 servers and login to new server #3.

2). vi /etc/sysconfig/network-scripts/ens224

DEVICE=ens224
BOOTPROTO=none
ONBOOT=yes
IPADDR=192.168.0.1
NETMASK=255.255.255.0
USERCTL=no
VLAN=yes

3). ifup ens224

4). systemctl stop NetworkManager.service

5). systemctl disable NetworkManager.service

6). systemctl enable network.service

7). systemctl start network.service

8). vi /etc/rc.local

ifup ens224

9). chmod +x /etc/rc.local

10). wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

11). tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

12). yum -y install perl-Data-Dumper

13). yum -y remove mariadb-libs

14). rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm

15). rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm

16). rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

17). mkdir -p /var/lib/mysql-cluster

18). vi /var/lib/mysql-cluster/config.ini

[ndb_mgmd default]
# Directory for MGM node log files
DataDir=/var/lib/mysql-cluster
 
[ndb_mgmd]
#Management Node db1
HostName=192.168.0.1
 
[ndbd default]
NoOfReplicas=2      # Number of replicas
DataMemory=256M     # Memory allocate for data storage
IndexMemory=128M    # Memory allocate for index storage
#Directory for Data Node
DataDir=/var/lib/mysql-cluster
 
[ndbd]
#Data Node db2
HostName=192.168.0.4
 
[ndbd]
#Data Node db3
HostName=192.168.0.5
 
[mysqld]
#SQL Node db4
HostName=192.168.0.2
 
[mysqld]
#SQL Node db5
HostName=192.168.0.3

19). ndb_mgmd –config-file=/var/lib/mysql-cluster/config.ini

20). vi /etc/rc.local

ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini

Your Management node has been setup and is now running. You can use the commands below to check on the status of your MySQL Cluster. Currently you will see that the other nodes are not connected as they have not been configured yet.

[root@localhost ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2 (not connected, accepting connect from 192.168.0.4)
id=3 (not connected, accepting connect from 192.168.0.5)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.1  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   2 node(s)
id=4 (not connected, accepting connect from 192.168.0.2)
id=5 (not connected, accepting connect from 192.168.0.3)

Data Nodes

1). Login to server #4

2). vi /etc/sysconfig/network-scripts/ens224

DEVICE=ens224
BOOTPROTO=none
ONBOOT=yes
IPADDR=192.168.0.4
NETMASK=255.255.255.0
USERCTL=no
VLAN=yes

3). ifup ens224

4). systemctl stop NetworkManager.service

5). systemctl disable NetworkManager.service

6). systemctl enable network.service

7). systemctl start network.service

8). vi /etc/rc.local

ifup ens224

9). chmod +x /etc/rc.local

10). wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

11). tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

12). yum -y install perl-Data-Dumper

13). yum -y remove mariadb-libs

14). rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm

15). rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm

16). rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

17). vi /etc/my.cnf

[mysqld]
ndbcluster
ndb-connectstring=192.168.0.1     # IP address of Management Node
 
[mysql_cluster]
ndb-connectstring=192.168.0.1     # IP address of Management Node

18). mkdir -p /var/lib/mysql-cluster

19). ndbd

20). vi /etc/rc.local

ndbd

21). Login to server #5 and repeat steps 2-19. Be sure to use IP 192.168.0.5 on step 2.

SQL Nodes

1). Login to Server 2 (Your first Web Server)

2). yum -y remove mariadb

3). rm -rf /var/lib/mysql

4). wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

5). tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

6). yum -y install perl-Data-Dumper

7). yum -y remove mariadb-libs

8). rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm

9). rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm

10). rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

11). vi /etc/my.cnf

[mysqld]
ndbcluster
ndb-connectstring=192.168.0.1       # IP address for server management node
default_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL
 
[mysql_cluster]
ndb-connectstring=192.168.0.1       # IP address for server management node

12). /etc/init.d/mysql start

13). vi /etc/rc.local

/etc/init.d/mysql start

14). To see your current MySQL password run the following: cat /root/.mysql_secret

15). mysql_secure_installation

16). mysql -uroot -p

Create DATABASE wp;
CREATE USER 'wordpress'@'%' IDENTIFIED BY 'Xsfd2V^dg1';
GRANT ALL PRIVILEGES ON *.* TO wordpress@'%' IDENTIFIED BY 'Xsfd2V^dg1' WITH GRANT OPTION;

17). mysql -uwordpress -p wp < backup.sql

18). Login to Server 2 and repeat steps 2-15.

19). mysql -uroot -p

CREATE USER 'wordpress'@'%' IDENTIFIED BY 'Xsfd2V^dg1';
GRANT ALL PRIVILEGES ON *.* TO wordpress@'%' IDENTIFIED BY 'Xsfd2V^dg1' WITH GRANT OPTION;

Finishing up

1). mysql -uroot -p

2). use wp;

3). show tables;

+-----------------------+
| Tables_in_wp          |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.00 sec)

4). You will now need to run the command: ALTER TABLE table ENGINE=NDBCLUSTER; on all of your tables. If the ENGINE is not changed on your tables data replication will not work properly.

mysql> ALTER TABLE wp_commentmeta ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_comments ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_links ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_options ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_postmeta ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_term_relationships ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_term_taxonomy ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_termmeta ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_terms ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_usermeta ENGINE=NDBCLUSTER;
mysql> ALTER TABLE wp_users ENGINE=NDBCLUSTER;

5). You can then check to confirm that your default engine is NBDCluster and that your changes to the wordpress tables also took effect:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | DEFAULT | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

mysql> use wp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: wp_commentmeta
         Engine: ndbcluster
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_unicode_520_ci
       Checksum: NULL
 Create_options:
        Comment:

6). Login to one of your WebServers

7). vi /shared/mywebsite.com/wp-config.php

/** MySQL hostname */
define( 'DB_HOST', 'localhost' );

8). Login to your Management Node Server to confirm all 5 nodes are connected.

9). ndb_mgm

-- 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.4  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=3    @192.168.0.5  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.1  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   2 node(s)
id=4    @192.168.0.2  (mysql-5.6.28 ndb-7.4.10)
id=5    @192.168.0.3  (mysql-5.6.28 ndb-7.4.10)

Conclusion of Part 3

You have now created a robust and fast MySQL Cluster.

Going forward you can continue to add additional Data Nodes to further expand website capacity and redundancy. When adding additional WebServers be sure to configure it as a new SQL Node as well.

You can now test your site using webpagetest.org to confirm everything is working and loading quickly.

I would also recommend that you perform additional testing using loadimpact.com

This site tests and generates load on your server by building up a large number of requests to your server to see how your site reacts. If the response time stays steady overtime then that means your setup can handle a decent amount of load without negatively effecting the speed of your site.

11 thoughts on “Building and Scaling a Website – Part 3”

virtual private server says:

I am really thankful to the owner of this web
page who has shared this wonderful piece of writing at at
this time.


May 19, 2019 at 11:53 am
GuQin says:

I’m extremely impressed with your writing skills as well as with the layout on your weblog. Is this a paid theme or did you modify it yourself? Either way keep up the excellent quality writing, it is rare to see a nice blog like this one nowadays..


August 8, 2019 at 10:28 am
gnomeitsolutions says:

You need to be a part of a contest for one of the highest quality blogs on the internet.

I will highly recommend this blog!


August 11, 2019 at 7:02 am
Lexus Ben Thanh says:

Marvelous, what a website it is! This website presents
helpful facts to us, keep it up.


August 13, 2019 at 5:47 pm
https://hyundaisgc.com says:

I’m amazed, I must say. Rarely do I encounter a blog that’s equally educative and entertaining, and
let me tell you, you’ve hit the nail on the head. The issue is something too few people
are speaking intelligently about. I am very happy I stumbled
across this in my hunt for something relating to this.


August 18, 2019 at 10:45 pm
sirglio frei says:

Nice blog right here! Additionally your site so much up very fast! What web host are you using? Can I am getting your affiliate hyperlink on your host? I wish my web site loaded up as fast as yours lol


October 3, 2019 at 7:59 am
eebest8 back says:

“I really enjoy the post.Thanks Again. Much obliged.”


October 5, 2019 at 8:01 pm
Google says:

Sites of interest we’ve a link to.


October 8, 2019 at 4:18 am
sex says:

I love what you guys are usually up too. This type of clever work and coverage!
Keep up the awesome works guys I’ve added you guys to our blogroll.


October 9, 2019 at 3:20 am
best CBD oil for dogs says:

very handful of web-sites that take place to be comprehensive below, from our point of view are undoubtedly very well really worth checking out


May 3, 2020 at 11:30 pm
Google says:

Sites of interest we have a link to.


May 18, 2020 at 1:48 am