Create the virtual machine:
The virtual machine is an emulated computer system with its own CPU, memory, storage, and network interface that exists on the physical hardware of our cloud service provider. This allows us to install our web server, host our database, and upgrade our hardware as needed for a small monthly cost. In this section, we will create the virtual machine that will contain our database using the cheapest monthly plan available.
# create virtual machine1. create an account on linode2. click the “build” button 3. click the “linode” menu item # select the Linux distribution1. click on the image dropdown in the “choose a distribution” section 2. click on the “ubuntu 20.04 LTS” menu item # select the data center to store the virtual machine1 . click the region dropdown under the “region” section 2. select the same region as your VMs from before# select the monthly plan1. click the “shared cpu” tab in the “plan” section 2. click the “nanode 1gb” radio button # end virtual machine1. scroll down to the “linode tag” section 2. enter “database-1” in the “linode tag” text field 3. enter the password in the “root password” text field4. click the “create linode” button5. wait until linoid6 finish. click the “turn on” link 7. click the “turn on Linode” button # create a private IP address1. click the “network” tab 2. click the “add IP address” button 3. click the “private” radio button 4. click the “assign” button # type the IP address private database1. scroll down to the “IP Addresses” section 2. type the IP address “ipv4 – private”# restart “database-1” linode1. click the “restart” button 2. click the “restart linode” button
Install MySQL database:
MySQL database is a database management system relational that organizes data in tables that are protected by access control. This allows us to control which users can create, request, update or delete the data stored in our database. In this section, we’ll install MySQL, configure our basic security settings, and enable remote connections.
# open the console as root on Linode1. open the “linodes” page in linode2. click on linode3 “database”. click the “start console lish” link 4. press the “return” key 5. enter “root” at the “login” prompt 6. enter your password at the “password” prompt 7. press the key “return”8. paste the commands into the console # update package informationsudo apt-get update# install mysqlsudo apt-get install -yes mysql-server# enable firewall sudo ufw enable# allow mysql to bypass the firewallsudo ufw allow mysql# open mysql directorycd /etc/mysql/mysql.conf.d # allow mysql to receive connections from any IP addresssudo sed “s|127.0.0.1|0.0.0.0|g” -i mysqld.cnf# configure mysql to run at startup sudo systemctl enable mysql # restart mysqlsudo systemctl restart mysql
Prepare the MySQL database:
The MySQL client is a command line program that provides the interface to user to manage our database. This allows us to manipulate the data in our database by executing SQL statements interactively or by executing them from a text file. In this section, we will download and run our text file to create our database, table, and data that we will upload to our website.
- The password must contain upper and lower case letters, a number, and a symbol.
# download the mysql filecurl -o /etc/mysql/database.sql https://gist.githubusercontent.com/david-littlefield/ 74266b53347d2605fac6f53a27277f4c/ raw# store database namedatabase=”website”# store table nametable=”records”# change “placeholder ” to a unique username user=”placeholder”# change “placeholder” to the desired passwordpassword=”placeholder”# add database to mysql filesudo sed “s|#database_placeholder#|$database| g” -i /etc/mysql/database.sql# add table to mysql filesudo sed “s|#table_placeholder#|$table |g” -i /etc/mysql/database.sq l# add user to mysql filesudo sed “s|#user_placeholder#|$user|g” -i /etc/mysql/database.sql# add word pass to mysql file strong>sudo sed “s|#password_placeholder#|$password|g” -i /etc/mysql/database.sql# open mysql as root usersudo mysql -u root -p# run mysqlsource file /etc/mysql/database.sql# exit mysqlexit
Replace static website:
The static website The Website is a website that displays the same content to all of our users. It serves the web browser with HTML files that are pre-compiled with encoded text and CSS and JavaScript files that are stored on our web server. This type of website is best for sites that feature content, update infrequently, and provide non-interactive or non-personalized content.
Dynamic website is a website that displays unique content for all of our users .It serves the web browser with HTML files that are generated in real time using server-side languages and CSS and JavaScript files that are stored on our web server. This type of website is best for sites that manage content, update frequently, and provide interactive or personalized content.
The website we host, protect, and optimize is a static website that helped us learn each of these topics individually, incrementally, and with minimal complexity. It also allows us to do a performance comparison between static and dynamic websites. In this section, we will change our static website into a dynamic website simply by uploading a different php file.
# open console as non-root user1. open the “linodes” page in linode2. click on “website-1” linode3. click the “start lish console” link 4. press the “return” key 5. enter your unique username at the “login” prompt6. Enter your password at the “password” prompt 7. Press the “return” key8. paste commands in console # switch from static website to dynamic websitesudo sed “s|index static.php|index dynamic.php|g” -i /etc/nginx/nginx.conf# restart nginxsudo systemctl restart nginx
Connect the website to the database:
The configuration file is a php file that contains our MySQL credentials that our site web needs to connect to our database and run queries It uses our credentials to make the connection, store the connection, and specify how to handle errors. In this section, we’ll store our credentials by adding our database name, IP address, username, and password to our configuration file.
- Use a server-side language that doesn’t is visible from the web browser
# change “placeholder” to the old database namedatabase=”placeholder”# change “placeholder” placeholder” to the database IP address host=”placeholder”# change “placeholder” to the database username aboveuser=”placeholder”# change “placeholder” to the old database passwordpassword=”placeholder”# add database to config filesudo sed “s|#database_placeholder#| $database|g” -i /var/www/html/includes/configuration.php# add host to configuration filesudo sed “s|#host_placeholder#|$host|g” -i / var/www/ html/includes/configuration.php# add username to configuration filesudo sed “s|# user_placeholder#|$username|g” -i /var/www/html/includes/configuration. ph p# add password to configuration filesudo sed “s|#password_placeholder#|$password|g” -i /var/www/html/includes/configuration.php
Select Records in the Database:
The SELECT statement is an SQL statement that retrieves the records from one or more of our database tables. This allows us to request one, some or all of the records by combining them with other SQL statements. In this section, we will request all the records, generate the HTML with the data from those records, and display the HTML as content on our website.
# load website without cache1. open chrome web browser 2. click on “…” menu3. click on menu item 4 “more tools”. click the “developer tools” menu item 5. click the “network” tab 6. check the “disable cache” checkbox 7. enter your domain name in the address bar8. press the “return” key
Insert a record into the database:
The INSERT statement is an SQL statement that inserts one or more records into a table in our database. This allows us to add records by providing data for one, some, or all of the columns in a table. In this section, we will parse the data from the text fields on our website, create more data with the parsed data, download the related image, and add the data to the database.
# add image to ” “recent” page1. click the “upload” tab # enter the display url in the “display url” text fieldhttps://unsplash.com/ photos/UoqAR2pOxMo# enter the location in the text field “location” Da Nang, Vietnam# enter the description in the text field “description”Non Nuoc Beach is located at the foot of the Marble Mountains and stretches for 5 km.This beach has calm waves and crystal blue water all year long.You can also eat fresh locally caught fish in one of the restaurants.It is also a place ideal for practicing sports such as surfing, windsurfing, volleyball, etc.# create record1. c on the “load” button
Update a record in the database:
The UPDATE statement is an SQL statement that updates one or more records in a table in our database. This allows us to change one, some or all of our records by combining them with other SQL statements. In this section, we’ll analyze the data in the text fields on our website, analyze more data from the current record object, and change the data in our database.
# open “edit” page1. click on the image “Da Nang, Vietnam”# remove description1. select the description from the text field “description”2.press the “delete” key # enter the description in the “description” text fieldSon Tra peninsula is located about 8 km from the city center and has many beautiful beaches, such as the But, Tien Sa beach, Nam beach, Rang beach, Bac beach and Con beach. These beaches are all very beautiful at the foot of mountains with jungle and clear blue sea. In addition to relaxing on the beach and swimming, you can also venture into the jungle, visit pagodas, ride a scooter around the peninsula, and go snorkeling.# update record1. click the “Save” button
Delete a record from the database:
The DELETE statement is an SQL statement that deletes one or more records from a table in our database. This allows us to delete one, some or all of our records by combining them with other SQL statements. In this section, we will analyze the record id from the query parameter in the URL, remove the related image, and delete the data from our database with the record id.
# open “edit” page 1. click on the image “Da Nang, Vietnam”# delete record1. click the “delete” button # measure performance with gtmetrix1. open gtmetrix2. enter your domain name in the “enter url to parse” text field. click the “test your site” button4. scroll to the “page details” section 5. review the “full load time” metric
Review the database process:
Our website manipulates the database with a chain of different classes that are stored in various php files. This process can be traced in the php files that are stored on our web server. In this section, we’ll customize and review the php file that shows a consolidated version of this process.
# add database to demo filesudo sed “s|#database_placeholder#|$database| g” -i /var/www/html/demo.php# add host to demo filesudo sed “s|#host_placeholder#|$host|g” -i /var/www/html/ demo .php# add username to demo filesudo sed “s|#username_placeholder#|$username|g” -i /var/www/html/demo.php# add password to the demo filesudo sed “s|#password_placeholder#|$password|g” -i /var/www/html/demo.php# change “placeholder.com” to your domain namedomain_name=”placeholder.com”# view url of demo php fileecho $domain_name/demo.php# open demo php file in web browser1. copy the url to demo2 php file. paste the url in the address bar3. press the “enter” key.