{"id":444,"date":"2019-05-13T18:00:20","date_gmt":"2019-05-13T18:00:20","guid":{"rendered":"https:\/\/unsrewiki.1sys1.com\/?p=444"},"modified":"2019-05-16T17:47:12","modified_gmt":"2019-05-16T17:47:12","slug":"building-and-scaling-a-website-part-3","status":"publish","type":"post","link":"https:\/\/server-help.org\/index.php\/2019\/05\/13\/building-and-scaling-a-website-part-3\/","title":{"rendered":"Building and Scaling a Website \u2013 Part 3"},"content":{"rendered":"\n

Further Scaling and Redundancy<\/h2>\n\n\n\n

In part 3 we will add additional scaling for MySQL as well as add in some redundancy into our setup.<\/p>\n\n\n\n

For this setup we will require at least 5 servers.<\/p>\n\n\n\n

1 – Management Node (Where we will manage and control the cluster)<\/p>\n\n\n\n

2 – Data Nodes (Where the MySQL Data will be stored)<\/p>\n\n\n\n

2 – SQL Nodes (End points where web services connect to MySQL)<\/p>\n\n\n\n

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).<\/p>\n\n\n\n

Management Node<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

2). vi \/etc\/sysconfig\/network-scripts\/ens224 <\/p>\n\n\n\n

DEVICE=ens224\nBOOTPROTO=none\nONBOOT=yes\nIPADDR=192.168.0.1\nNETMASK=255.255.255.0\nUSERCTL=no\nVLAN=yes<\/code><\/pre>\n\n\n\n
3). ifup ens224<\/p>\n\n\n\n
4). systemctl stop NetworkManager.service<\/p>\n\n\n\n
5). systemctl disable NetworkManager.service<\/p>\n\n\n\n
6). systemctl enable network.service<\/p>\n\n\n\n
7). systemctl start network.service<\/p>\n\n\n\n
8). vi \/etc\/rc.local<\/p>\n\n\n\n
ifup ens224<\/code><\/pre>\n\n\n\n
9). chmod +x \/etc\/rc.local<\/p>\n\n\n\n
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<\/p>\n\n\n\n
11). tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar<\/p>\n\n\n\n
12). yum -y install perl-Data-Dumper<\/p>\n\n\n\n
13). yum -y remove mariadb-libs<\/p>\n\n\n\n
14). rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
15). rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
16). rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
17). mkdir -p \/var\/lib\/mysql-cluster<\/p>\n\n\n\n
18). vi \/var\/lib\/mysql-cluster\/config.ini<\/p>\n\n\n\n
[ndb_mgmd default]\n# Directory for MGM node log files\nDataDir=\/var\/lib\/mysql-cluster\n \n[ndb_mgmd]\n#Management Node db1\nHostName=192.168.0.1\n \n[ndbd default]\nNoOfReplicas=2      # Number of replicas\nDataMemory=256M     # Memory allocate for data storage\nIndexMemory=128M    # Memory allocate for index storage\n#Directory for Data Node\nDataDir=\/var\/lib\/mysql-cluster\n \n[ndbd]\n#Data Node db2\nHostName=192.168.0.4\n \n[ndbd]\n#Data Node db3\nHostName=192.168.0.5\n \n[mysqld]\n#SQL Node db4\nHostName=192.168.0.2\n \n[mysqld]\n#SQL Node db5\nHostName=192.168.0.3<\/code><\/pre>\n\n\n\n
19). ndb_mgmd –config-file=\/var\/lib\/mysql-cluster\/config.ini<\/p>\n\n\n\n
20). vi \/etc\/rc.local<\/p>\n\n\n\n
ndb_mgmd --config-file=\/var\/lib\/mysql-cluster\/config.ini<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
[root@localhost ~]# ndb_mgm\n-- NDB Cluster -- Management Client --\nndb_mgm> show\nConnected to Management Server at: localhost:1186\nCluster Configuration\n---------------------\n[ndbd(NDB)]     2 node(s)\nid=2 (not connected, accepting connect from 192.168.0.4)\nid=3 (not connected, accepting connect from 192.168.0.5)\n\n[ndb_mgmd(MGM)] 1 node(s)\nid=1    @192.168.0.1  (mysql-5.6.28 ndb-7.4.10)\n\n[mysqld(API)]   2 node(s)\nid=4 (not connected, accepting connect from 192.168.0.2)\nid=5 (not connected, accepting connect from 192.168.0.3)\n<\/code><\/pre>\n\n\n\n
\nData Nodes\n\n<\/h2>\n\n\n\n
1). Login to server #4<\/p>\n\n\n\n
2).  vi \/etc\/sysconfig\/network-scripts\/ens224 <\/p>\n\n\n\n
DEVICE=ens224\nBOOTPROTO=none\nONBOOT=yes\nIPADDR=192.168.0.4\nNETMASK=255.255.255.0\nUSERCTL=no\nVLAN=yes<\/code><\/pre>\n\n\n\n
3).  ifup ens224 <\/p>\n\n\n\n
4). systemctl stop NetworkManager.service<\/p>\n\n\n\n
5). systemctl disable NetworkManager.service<\/p>\n\n\n\n
6). systemctl enable network.service<\/p>\n\n\n\n
7). systemctl start network.service<\/p>\n\n\n\n
8). vi \/etc\/rc.local<\/p>\n\n\n\n
ifup ens224<\/code><\/pre>\n\n\n\n
9). chmod +x \/etc\/rc.local<\/p>\n\n\n\n
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<\/p>\n\n\n\n
11). tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar<\/p>\n\n\n\n
12). yum -y install perl-Data-Dumper<\/p>\n\n\n\n
13). yum -y remove mariadb-libs<\/p>\n\n\n\n
14). rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
15). rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
16). rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
17). vi \/etc\/my.cnf<\/p>\n\n\n\n
[mysqld]\nndbcluster\nndb-connectstring=192.168.0.1     # IP address of Management Node\n \n[mysql_cluster]\nndb-connectstring=192.168.0.1     # IP address of Management Node<\/code><\/pre>\n\n\n\n
18). mkdir -p \/var\/lib\/mysql-cluster<\/p>\n\n\n\n
19). ndbd<\/p>\n\n\n\n
20). vi \/etc\/rc.local<\/p>\n\n\n\n
ndbd<\/code><\/pre>\n\n\n\n
21). Login to server #5 and repeat steps 2-19. Be sure to use IP 192.168.0.5 on step 2.<\/p>\n\n\n\n
SQL Nodes<\/h2>\n\n\n\n
1). Login to Server 2 (Your first Web Server)<\/p>\n\n\n\n
2). yum -y remove mariadb<\/p>\n\n\n\n
3). rm -rf \/var\/lib\/mysql<\/p>\n\n\n\n
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<\/p>\n\n\n\n
5). tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar<\/p>\n\n\n\n
6). yum -y install perl-Data-Dumper<\/p>\n\n\n\n
7). yum -y remove mariadb-libs<\/p>\n\n\n\n
8). rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
9). rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
10). rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm<\/p>\n\n\n\n
11). vi \/etc\/my.cnf<\/p>\n\n\n\n
[mysqld]\nndbcluster\nndb-connectstring=192.168.0.1       # IP address for server management node\ndefault_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL\n \n[mysql_cluster]\nndb-connectstring=192.168.0.1       # IP address for server management node<\/code><\/pre>\n\n\n\n
12). \/etc\/init.d\/mysql start<\/p>\n\n\n\n
13). vi \/etc\/rc.local<\/p>\n\n\n\n
\/etc\/init.d\/mysql start<\/code><\/pre>\n\n\n\n
14). To see your current MySQL password run the following: cat \/root\/.mysql_secret<\/p>\n\n\n\n
15). mysql_secure_installation<\/p>\n\n\n\n
16). mysql -uroot -p<\/p>\n\n\n\n
Create DATABASE wp;\nCREATE USER 'wordpress'@'%' IDENTIFIED BY 'Xsfd2V^dg1';\nGRANT ALL PRIVILEGES ON *.* TO wordpress@'%' IDENTIFIED BY 'Xsfd2V^dg1' WITH GRANT OPTION;<\/code><\/pre>\n\n\n\n
17).  mysql -uwordpress -p wp < backup.sql <\/p>\n\n\n\n
18). Login to Server 2 and repeat steps 2-15.<\/p>\n\n\n\n
19).  mysql -uroot -p <\/p>\n\n\n\n
CREATE USER 'wordpress'@'%' IDENTIFIED BY 'Xsfd2V^dg1';\nGRANT ALL PRIVILEGES ON *.* TO wordpress@'%' IDENTIFIED BY 'Xsfd2V^dg1' WITH GRANT OPTION;<\/code><\/pre>\n\n\n\n
Finishing up<\/h2>\n\n\n\n
1). mysql -uroot -p<\/p>\n\n\n\n
2). use wp;<\/p>\n\n\n\n
3). show tables;<\/p>\n\n\n\n
+-----------------------+\n| Tables_in_wp          |\n+-----------------------+\n| wp_commentmeta        |\n| wp_comments           |\n| wp_links              |\n| wp_options            |\n| wp_postmeta           |\n| wp_posts              |\n| wp_term_relationships |\n| wp_term_taxonomy      |\n| wp_termmeta           |\n| wp_terms              |\n| wp_usermeta           |\n| wp_users              |\n+-----------------------+\n12 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
mysql> ALTER TABLE wp_commentmeta ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_comments ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_links ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_options ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_postmeta ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_term_relationships ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_term_taxonomy ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_termmeta ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_terms ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_usermeta ENGINE=NDBCLUSTER;\nmysql> ALTER TABLE wp_users ENGINE=NDBCLUSTER;<\/code><\/pre>\n\n\n\n
5). You can then check to confirm that your default engine is NBDCluster and that your changes to the wordpress tables also took effect:<\/p>\n\n\n\n
mysql> show engines;\n+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+\n| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |\n+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+\n| ndbcluster         | DEFAULT | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |\n| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |\n| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |\n| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |\n| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |\n| BLACKHOLE          | YES     | \/dev\/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |\n| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |\n| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |\n| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |\n| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |\n| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |\n+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+\n11 rows in set (0.00 sec)\n\nmysql> use wp;\nReading table information for completion of table and column names\nYou can turn off this feature to get a quicker startup with -A\n\nDatabase changed\nmysql> SHOW TABLE STATUS\\G\n*************************** 1. row ***************************\n           Name: wp_commentmeta\n         Engine: ndbcluster\n        Version: 10\n     Row_format: Dynamic\n           Rows: 0\n Avg_row_length: 0\n    Data_length: 0\nMax_data_length: 0\n   Index_length: 0\n      Data_free: 0\n Auto_increment: 1\n    Create_time: NULL\n    Update_time: NULL\n     Check_time: NULL\n      Collation: utf8mb4_unicode_520_ci\n       Checksum: NULL\n Create_options:\n        Comment:\n<\/code><\/pre>\n\n\n\n
6). Login to one of your WebServers<\/p>\n\n\n\n
7). vi \/shared\/mywebsite.com\/wp-config.php<\/p>\n\n\n\n
\/** MySQL hostname *\/\ndefine( 'DB_HOST', 'localhost' );<\/code><\/pre>\n\n\n\n
8). Login to your Management Node Server to confirm all 5 nodes are connected.<\/p>\n\n\n\n
9). ndb_mgm<\/p>\n\n\n\n
-- NDB Cluster -- Management Client --\nndb_mgm> show\nConnected to Management Server at: localhost:1186\nCluster Configuration\n---------------------\n[ndbd(NDB)]     2 node(s)\nid=2    @192.168.0.4  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)\nid=3    @192.168.0.5  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)\n\n[ndb_mgmd(MGM)] 1 node(s)\nid=1    @192.168.0.1  (mysql-5.6.28 ndb-7.4.10)\n\n[mysqld(API)]   2 node(s)\nid=4    @192.168.0.2  (mysql-5.6.28 ndb-7.4.10)\nid=5    @192.168.0.3  (mysql-5.6.28 ndb-7.4.10)<\/code><\/pre>\n\n\n\n
Conclusion of Part 3<\/h2>\n\n\n\n
You have now created a robust and fast MySQL Cluster.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
You can now test your site using webpagetest.org to confirm everything is working and loading quickly.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
I would also recommend that you perform additional testing using loadimpact.com<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,4,1],"tags":[],"_links":{"self":[{"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/posts\/444"}],"collection":[{"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/comments?post=444"}],"version-history":[{"count":26,"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/posts\/444\/revisions"}],"predecessor-version":[{"id":505,"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/posts\/444\/revisions\/505"}],"wp:attachment":[{"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/media?parent=444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/categories?post=444"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/server-help.org\/index.php\/wp-json\/wp\/v2\/tags?post=444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}