Ravindra BagaleCourses & study guides मराठी Track your progress

How to Install LAMP and LEMP Stack on Ubuntu (EC2)

In the previous chapter we built LAMP on Amazon Linux. Now we do the same job on Ubuntu, where the package names, the PHP service name and the PHP set-up are different. We write an Ubuntu lamp.sh, fix Apache downloading .php files, switch the same website from Apache to Nginx (LEMP), and connect Nginx to PHP. On the way we learn to read error messages, handle the apt lock error, and take our first steps inside the MySQL database shell.

What you'll learn in this class

  • Why a dynamic website needs a database, and how Ubuntu differs from Amazon Linux for LAMP
  • An Ubuntu lamp.sh: apt-get, apache2 mysql-server php php-fpm, the version-named php8.x-fpm service, removing Ubuntu's default index.html
  • Fix Apache downloading .php: a2enmod proxy_fcgi setenvif and a2enconf php8.x-fpm
  • Switch from Apache to Nginx and keep /var/www/html; clear the browser cache with Disable cache
  • The Nginx PHP location block, the right socket version, nginx -t and reload
  • Read four common errors, and the "status bottom lines" habit
  • The apt lock error, and sudo chmod on a file that root owns
  • First steps in MySQL: sudo mysql, ALTER USER, mysql -u root -p, hidden password typing, and why real setups keep the database on its own server

1. Why: dynamic sites need a database

Classroom line

Yesterday we did LAMP/LEMP on Amazon Linux; today we do the same on Ubuntu.

A static site only shows files. A dynamic site takes data from you and keeps it:

  1. A sign-up form saves your name, email and password.
  2. A login form checks them against what was saved.
  3. An upload form stores your photo or document details.

All of that is saved in a database, such as MySQL. The PHP code talks to the database in a language called SQL, which we learn in coming chapters. In this chapter we install the whole stack, so it's ready for that.

Classroom line

Until we learn SQL, PHP and MySQL won't talk.

2. What changes on Ubuntu

Classroom line

Now we do the same work in Ubuntu.

The stack is the same: Linux, a web server, a database, PHP. The names around it change:

  1. Package manager: apt-get instead of yum.
  2. Apache is the package apache2 (not httpd), and its service is apache2.
  3. Database: Ubuntu offers mysql-server (MySQL itself). Its service is mysql.
  4. PHP: install php and php-fpm. Ubuntu installs its own default PHP version; you don't type a version.
  5. The php-fpm service has the version in its name, for example php8.3-fpm. Find your version with php -v (it prints PHP 8.3.x ...) or ls /run/php/ (it shows php8.3-fpm.sock).
  6. Ubuntu's Apache puts a default index.html in /var/www/html.
  7. Apache doesn't hand PHP to php-fpm until you switch it on (section 5).
The same LAMP stack, two distributions Task Amazon Linux → Ubuntu Package manager yum → apt-get Web server package httpd → apache2 Database package mariadb105-server →mysql-server PHP packages php → php php-fpm PHP service name php-fpm → php8.x-fpm Default page in web root none → index.html Apache hands PHP to php-fpm automatically → a2enmod +a2enconf Ubuntu names the PHP service with its version: find it with php -vor ls /run/php/.

Figure 1. The same LAMP stack on Amazon Linux and Ubuntu: different package manager, package names, PHP service name and PHP set-up.

3. How: the Ubuntu lamp.sh script

Launch an Ubuntu EC2 server with SSH (22) from your IP and HTTP (80) allowed, and connect as ubuntu.

Step 1. In your home folder, open a new file. Don't use sudo here; the script should belong to you:

cd ~
nano lamp.sh

Step 2. Type the script:

#!/bin/bash
sudo apt-get update
sudo apt-get install apache2 mysql-server php php-fpm -y
PHPV=$(php -r 'echo PHP_MAJOR_VERSION.".".PHP_MINOR_VERSION;')
sudo service apache2 start
sudo service mysql start
sudo service php$PHPV-fpm start
cd /var/www/html
sudo rm -f index.html
sudo touch index.html index.php
echo "html page" | sudo tee index.html
echo "<?php phpinfo(); ?>" | sudo tee index.php

What each part does:

  1. sudo apt-get update refreshes Ubuntu's package list first.
  2. One line installs Apache, MySQL, PHP and php-fpm, with -y so the script never stops at a question.
  3. PHPV=... asks PHP for its own version (for example 8.3) and keeps it in a variable. So php$PHPV-fpm becomes php8.3-fpm on your server, and the script works whatever version Ubuntu installs. (If you type a version yourself, such as php8.5-fpm, and your Ubuntu doesn't have it, you get Unable to locate package or Unit php8.5-fpm.service not found.)
  4. The three service ... start lines start Apache, MySQL and php-fpm.
  5. sudo rm -f index.html removes Ubuntu's default Apache page, so you see your own page.
  6. The tee lines write the two test pages as root (exactly as in the previous chapter).

Writing a file with several lines. For a longer page, use a heredoc: everything up to the EOF line goes into the file.

cat <<'EOF' | sudo tee index.php
<?php
phpinfo();
?>
EOF

Step 3. Save, make it executable and run it:

chmod +x lamp.sh
./lamp.sh

4. Two errors you may meet while running it

4.1 chmod says "Operation not permitted"

If you created the file with sudo nano lamp.sh, it belongs to root. Then chmod +x lamp.sh (or chmod u+x lamp.sh) as ubuntu fails with Operation not permitted: only the owner or root can change permissions.

  1. Check the owner: ls -l lamp.sh shows root root.
  2. Use sudo: sudo chmod +x lamp.sh.
  3. Better next time: create your scripts in ~ without sudo.

4.2 apt says "Could not get lock"

Symptom. E: Could not get lock /var/lib/dpkg/lock-frontend. It is held by process 1234 (apt-get).

Cause. Another install is still running. On a new Ubuntu server, the automatic updates often run in the first minutes after boot, or an earlier apt-get of yours hasn't finished.

Classroom line

Lock error means the earlier install is still running.

Fix:

  1. Wait a few minutes and run the command again.
  2. Check what is running: ps aux | grep -i apt.
  3. If it's still stuck after a long wait, reboot the server: sudo reboot.
  4. Never delete the lock files while apt is running. Two installs writing at once can break the package database.

5. Ubuntu Apache downloads index.php: switch on the PHP hand-off

Step 1. Open http://PUBLIC_IP/index.html. You see html page.

Step 2. Open http://PUBLIC_IP/index.php. On Ubuntu, the browser downloads the file instead of showing the PHP page.

Why? php-fpm is installed and running, but Ubuntu's Apache has no rule yet that sends .php files to it. So Apache sends the file as it is.

Step 3. Switch the hand-off on (use your version from php -v):

sudo a2enmod proxy_fcgi setenvif
sudo a2enconf php8.3-fpm
sudo service apache2 restart
  1. a2enmod switches on Apache modules: proxy_fcgi lets Apache talk to php-fpm, setenvif is needed by its config.
  2. a2enconf switches on the config file php8.3-fpm.conf that sends .php to the php-fpm socket.
  3. The restart loads both.

Step 4. Refresh http://PUBLIC_IP/index.php. The PHP information page appears, with Server API: FPM/FastCGI and Build Provider: Ubuntu.

Browser showing the PHP information page: a blue header PHP Version 8.5.4 with the PHP logo, then a table with System (host name hidden), Build Date (hidden), Build System Linux, Build Provider Ubuntu and Server API FPM/FastCGI.

Whiteboard: PHP on Ubuntu: Build Provider Ubuntu and Server API FPM/FastCGI, so Apache now hands PHP to php-fpm.

Ubuntu Apache downloads index.php: switch on thePHP hand-off Before Apache has no rule for .php, so it sends the file as it is. The browserdownloads it. Fix (Ubuntu only) sudo a2enmod proxy_fcgi setenvif sudo a2enconf php8.x-fpm sudo service apache2 restart Use your version: php -v After .php goes to php-fpm, which runs it and returns HTML. a2enmod switches on Apache modules; a2enconf switches on thephp-fpm config file.

Figure 2. Ubuntu Apache downloads .php until you run a2enmod proxy_fcgi setenvif and a2enconf php8.x-fpm, then restart apache2.

The final lamp.sh. Add the fix to the script, after the start lines, so a new server works first time:

sudo a2enmod proxy_fcgi setenvif
sudo a2enconf php$PHPV-fpm
sudo service apache2 restart

On Amazon Linux

These a2en... commands exist only on Ubuntu. On Amazon Linux the PHP config is already in /etc/httpd/conf.d/, so you only restart php-fpm and then httpd (previous chapter).

6. Read the error: four messages, and the bottom lines

Errors look scary, but each one tells you what went wrong. Read the message before you try anything.

  1. command not found: a typo in the command, or the program isn't installed. Example: ngnix -t. Check the spelling, or install the package.
  2. Operation not permitted (or Permission denied): you need sudo, or you aren't the owner of the file. Example: chmod +x lamp.sh on a root-owned file (section 4.1).
  3. Unit ... not found: that service isn't installed, or its name is wrong. Example: sudo service php-fpm start on Ubuntu gives Unit php-fpm.service not found, because the name is php8.3-fpm.
  4. Job for ... failed: the service is installed but crashed while starting. The reason is in its status.
Read the error: four messages you will meet command not found A typo, or the program is notinstalled. Check spelling; install it. Operation not permitted You need root, or you are not theowner. Add sudo; check ls -l. Unit ... not found That service is not installed, orthe name is wrong. ls /run/php/, check the name. Job for ... failed The service crashed whilestarting. Read the status bottom lines. Message · Usually means · Do this

Figure 3. Four common messages: command not found, operation not permitted, unit not found, job failed; what each usually means and what to do.

The status habit. When a service fails, look at the status of that service:

sudo service apache2 status

Classroom line

Check the status of whichever software stopped.

  1. The top says what happened: Active: failed.
  2. The bottom lines say why. Two common ones:
    • Address already in use ... could not bind to address [::]:80: another web server already has port 80. Stop it.
    • unknown directive or Invalid command: a typo in a config file. The line number is in the message.
  3. Fix the reason, then start the service again.

Classroom line

Someone else is using the port.

Status: the top says what, the bottom says why $ sudo service apache2 status x apache2.service - The Apache HTTP Server Active: failed (Result: exit-code) ... apachectl: (98)Address already in use: AH00072: make_sock: could not bind to address[::]:80 apachectl: no listening sockets available, shuttingdown Top failed: something went wrong. Bottom lines the real reason: port 80 is taken. Fix the reason (stop the other server or fix the typo), then startthe service again.

Figure 4. The status of a failed service: the top says it failed, the bottom lines give the real reason, here port 80 already in use.

7. LEMP: switch the same website to Nginx

Why. Many companies run Nginx instead of Apache. You can switch a server to Nginx and keep the same website files.

Step 1. Free port 80. Only one web server can own it:

sudo service apache2 stop
sudo systemctl disable apache2

(disable stops Apache from taking the port again at the next boot.)

Step 2. Install and start Nginx:

sudo apt-get install nginx -y
sudo service nginx start

Step 3. Check the web root. Open /etc/nginx/sites-available/default and find the root line. On Ubuntu it is already root /var/www/html;, the same folder Apache used. (If yours points elsewhere, change it to /var/www/html.)

Step 4. Test and reload:

sudo nginx -t
sudo service nginx reload

Step 5. Open http://PUBLIC_IP/index.html. The same html page, now served by Nginx.

Switching from Apache to Nginx, same files Browser port 80 Apache: running owns port 80 1. Apache owns port 80 and serves /var/www/html. Apache: stopped service apache2 stop 2. sudo service apache2 stop: port 80 is free. Nginx: running owns port 80 3. Install and start Nginx; its root is /var/www/html too. 4. sudo nginx -t, sudo service nginx reload: same files, newserver. /var/www/html index.html, index.php

Figure 5. Animation: stop Apache to free port 80, start Nginx with the same /var/www/html, test and reload: same files, new server.

A lemp.sh for new servers. Copy the script and swap the web server:

cp lamp.sh lemp.sh
nano lemp.sh

Change the install line to sudo apt-get install nginx mysql-server php-fpm -y, change apache2 to nginx in the start line, and delete the three a2en.../apache2 restart lines (they are Apache-only). Then add the Nginx PHP block from section 9.

Still seeing the old page? The browser cache

After switching servers or editing a page, the browser may still show the old copy it saved earlier. Turn the cache off while you work:

  1. Right-click the page, Inspect.
  2. Open the Network tab.
  3. Tick Disable cache.
  4. Keep the developer tools open and refresh.

Classroom line

Right-click, Inspect, Network, tick Disable cache.

Firefox developer tools with the Network tab selected; the toolbar shows filter buttons All, HTML, CSS, JS, XHR, Fonts, Images, Media, WS, Other and a ticked Disable Cache checkbox next to No Throttling; below, Perform a request or Reload the page to see detailed information about network activity.

Whiteboard: Developer tools, Network tab: tick Disable cache, keep the tools open, and refresh.

8. Why Nginx also downloads .php

Open http://PUBLIC_IP/index.php on Nginx: the browser downloads it again. Nginx, like Ubuntu's Apache, needs a rule that sends .php files to php-fpm.

Classroom line

If the URL ends in .php, hand that page to php-fpm.

9. How: the Nginx PHP location block

Step 1. Find your php-fpm socket:

ls /run/php/

It shows something like php8.3-fpm.sock.

Step 2. Edit the site file (the real file is in sites-available; sites-enabled/default is only a link to it):

sudo nano /etc/nginx/sites-available/default

Step 3. In the index line, add index.php:

index index.php index.html index.htm index.nginx-debian.html;

Step 4. Further down, find the commented block under # pass PHP scripts to FastCGI server. Remove the # from the location line, the include line, the php-fpm fastcgi_pass line and the closing }. Leave the 127.0.0.1:9000 line commented. Put your socket in fastcgi_pass:

location ~ \.php$ {
    include snippets/fastcgi-php.conf;
    fastcgi_pass unix:/run/php/php8.3-fpm.sock;
}
  1. location ~ \.php$ matches every URL that ends in .php. (~ means a regular expression, \. is a real dot, $ is the end.)
  2. include snippets/fastcgi-php.conf; adds Ubuntu's standard PHP settings.
  3. fastcgi_pass is where php-fpm listens. A wrong version here (for example php8.5-fpm.sock when you have 8.3) gives a 502 Bad Gateway.

Step 5. Test the config, then reload:

sudo nginx -t
sudo service nginx reload

nginx -t must say syntax is ok and test is successful. If it shows an error, it names the file and line; fix it before reloading. A reload keeps the site up while Nginx reads the new config.

Step 6. Refresh http://PUBLIC_IP/index.php (with Disable cache ticked). The PHP information page appears, now through Nginx: your LEMP stack works.

Nginx: hand .php to php-fpm server { root /var/www/html; Same web root as Apache. index index.php index.html; Add index.php to the index line. location ~ \.php$ { Matches URLs that end in .php. include snippets/fastcgi-php.conf; Standard PHP settings.     fastcgi_pass       unix:/run/php/php8.x-fpm.sock; Socket of YOUR version: ls /run/php/   } } Edit /etc/nginx/sites-available/default, then: sudo nginx -t andsudo service nginx reload.

Figure 6. The Nginx server block for PHP: same web root, index.php in the index line, location ~ \.php$ with the socket of your PHP version, then nginx -t and reload.

10. First steps in MySQL

What is SQL? SQL (Structured Query Language) is the language for working with relational databases: creating tables, saving rows, reading them back. MySQL and MariaDB both use it. Common SQL databases:

  1. Open source: MySQL, MariaDB, PostgreSQL.
  2. Commercial: Oracle Database (common in large enterprises such as banks), Microsoft SQL Server, IBM Db2.
  3. Managed on AWS: Amazon Aurora (compatible with MySQL and PostgreSQL) and Amazon RDS.

MySQL is one of the most widely used of all.

Step 1. Open the database shell. On Ubuntu, the MySQL root user logs in through the Linux root account at first, so use sudo:

sudo mysql

The prompt changes to mysql>. You are now inside the database, not in Linux.

Classroom line

Linux commands like ls, cd don't work in the DB shell.

Step 2. Give the database admin a password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YOUR_STRONG_PASSWORD';
  1. 'root' is the database administrator (a MySQL user, not the Linux root).
  2. 'localhost' means this login is allowed only from the same server.
  3. IDENTIFIED WITH caching_sha2_password BY switches root to normal password login with this password.
  4. Use a long, unique password that you don't use anywhere else, and keep the quotes and the ; at the end.

Step 3. Leave the shell:

exit;

Step 4. Log in again, now with the password:

mysql -u root -p

-u root is the user, and -p makes MySQL ask for the password. Never type the password after -p on the command line: it would be saved in your shell history.

Step 5. At Enter password:, type the password. Nothing appears while you type, not even stars. That's normal; it isn't stuck. Press Enter, and you are back at mysql>.

Classroom line

The cursor doesn't move while typing the password.

First steps in the MySQL shell $ sudo mysql mysql> 1. sudo mysql opens the database shell. ls and cd do not workhere. mysql> ALTER USER 'root'@'localhost' IDENTIFIEDWITH caching_sha2_password BY '....'; 2. Give the database admin (root) a strong password. mysql> exit; $ mysql -u root -p 3. Leave with exit; and log in again with the password. Enter password: 4. The password does not show while you type. Type it andpress Enter.

Figure 7. Animation: sudo mysql opens the database shell, ALTER USER sets the root password, exit; leaves, mysql -u root -p logs in again, and the password doesn't show while you type.

Ravindra Bagale's Tip

On Amazon Linux with MariaDB, the same steps are sudo mariadb, then ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOUR_STRONG_PASSWORD';, exit; and mysql -u root -p.

11. Web server and database on separate servers

In practice we run everything on one server. Real websites usually keep the database on its own server (or on Amazon RDS), which accepts connections only from the web server.

Why? If an attacker breaks into the web server, the data isn't sitting on the same machine. The database server also gets its own size, backups and security rules. We build this later with Amazon RDS.

Practice vs real setups Practice: one server Apache or Nginx + PHP MySQL on the same server Break into the web server, reach the data. Real: separate servers Web server Apache/Nginx + PHP DB server MySQL, or Amazon RDS The database only accepts the web server.

Figure 8. Practice: web server, PHP and MySQL on one server. Real setups: the database on a separate server that only accepts the web server.

12. Clean up: stop the instance

When you finish practising, Instance state > Stop instance in the EC2 console (check the region first). A stopped instance doesn't charge for running time; terminate test servers you don't need any more.

13. The whole flow on one page

  1. Ubuntu LAMP: sudo apt-get update, then sudo apt-get install apache2 mysql-server php php-fpm -y.
  2. Start apache2, mysql and php8.x-fpm (find the version with php -v or ls /run/php/); remove Ubuntu's default index.html.
  3. .php downloads on Apache: a2enmod proxy_fcgi setenvif, a2enconf php8.x-fpm, restart apache2.
  4. Root-owned script: sudo chmod +x. apt lock: wait, never delete lock files.
  5. Read the message; for a failed service, read the status bottom lines.
  6. LEMP: stop and disable apache2, install and start nginx, root stays /var/www/html.
  7. Nginx PHP: index.php in the index line, location ~ \.php$ with your socket, nginx -t, reload.
  8. Old page? DevTools, Network, Disable cache.
  9. MySQL: sudo mysql, ALTER USER ... ;, exit;, mysql -u root -p (the password stays hidden).
  10. Real setups put the database on its own server. Stop instances after practice.

14. Try at home

Try at home

Task 1: LAMP on Ubuntu

  1. Launch an Ubuntu server with SSH and HTTP allowed.
  2. Write lamp.sh from section 3 in your home folder, chmod +x, and run it.
  3. Open index.php. If it downloads, add the a2enmod/a2enconf lines and restart apache2.
  4. Run sudo service php-fpm start on purpose. Read the Unit ... not found message and start the right service.

Task 2: switch to LEMP

  1. Stop and disable apache2, then install and start Nginx.
  2. Add the PHP location block with your socket version, run sudo nginx -t, and reload.
  3. Tick Disable cache and open index.php through Nginx.
  4. Start apache2 again while Nginx runs, and read the status bottom lines.

Task 3: MySQL

  1. sudo mysql, set a strong root password with ALTER USER, and exit;.
  2. Log in with mysql -u root -p.
  3. Stop the instance when you're done.

Recap

In short

  1. Dynamic sites save sign-ups, logins and uploads in a database; PHP talks to it in SQL.
  2. On Ubuntu: apt-get, apache2, mysql-server, php php-fpm; the PHP service is version-named (php8.x-fpm), found with php -v or ls /run/php/.
  3. Ubuntu's Apache downloads .php until a2enmod proxy_fcgi setenvif + a2enconf php8.x-fpm + restart.
  4. Errors: command not found, operation not permitted, unit not found, job failed. For a failed service, the status bottom lines give the reason.
  5. A root-owned file needs sudo chmod; an apt lock means another install is running, so wait.
  6. Switch to Nginx: stop apache2, install nginx, same /var/www/html; add the location ~ \.php$ block with your socket, nginx -t, reload.
  7. Disable cache in DevTools when the browser shows an old page.
  8. MySQL: sudo mysql, ALTER USER 'root'@'localhost' ..., mysql -u root -p; the password doesn't show. Real setups keep the database on a separate server.

Practice Tasks

About this scenario

Konkan Cashews is a food export business in Ratnagiri. Prakash Sawant looks after its IT and cloud work. They count money in INR and run their AWS resources in the ap-south-1 Region. In the tasks below you do real jobs for them using what this lesson teaches. All names, account IDs, IP addresses and numbers are made up for practice.

0 / 12

Easy Install a package on Ubuntu

Why: Ubuntu uses apt. Package names differ: on Ubuntu Apache is apache2.

What to do: Install the apache2 package on Ubuntu without being asked to confirm.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

Easy Install a package on Amazon Linux

Why: dnf is the package manager on Amazon Linux 2023. -y answers yes for you.

What to do: Install the httpd package on Amazon Linux 2023 without being asked to confirm.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

Easy Restart a service after a change

Why: Config changes only apply after the service restarts or reloads.

What to do: Restart the nginx service.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

Easy Check if a service is running

Why: When a site is down, first check whether the web server process is running.

What to do: Show the status of the httpd service.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

Easy Log in to MariaDB

Why: The mysql client lets you run SQL on the database server.

What to do: Open the MariaDB client as user root and ask for the password.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

Easy Biggest orders first

Why: Querying the database is daily work after you set up RDS.

What to do: Show order id and amount, biggest amount first.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Easy Filter customers by city

Why: Querying the database is daily work after you set up RDS.

What to do: List the names of customers in Pune.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Medium Start a service now and at boot

Why: enable makes it start after reboot; --now also starts it right away.

What to do: Enable the nginx service and start it immediately, in one command.

How:
  1. Find the Linux command that does this job.
  2. Add the options (like -r or -h) the task needs.
  3. Add the file names, folders or users in the right order.
  4. Use sudo if the job changes system files or users. Click Check answer.

Medium Remove small orders

Why: Querying the database is daily work after you set up RDS.

What to do: Delete orders with amount below 200.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Medium Orders with customer names

Why: Querying the database is daily work after you set up RDS.

What to do: Show each customer name with each order amount.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Hard Sales per customer

Why: Querying the database is daily work after you set up RDS.

What to do: Show customer_id and total amount per customer, only where total is above 1000.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Hard Customers with no orders

Why: Querying the database is daily work after you set up RDS.

What to do: List names of customers who have never ordered.

Tables: customers(id, name, city) and orders(id, customer_id, amount, odate).

How:
  1. Find the table the task is about (see the table list above the box).
  2. Write the SQL statement step by step: what, from where, which rows.
  3. End the statement with ;
  4. Click Check answer. Your query runs on a practice copy of the table.

Quick revision cards

Revise this lesson in two minutes. Save the cards on your phone or download them as PNG files. The QR code on each card opens its lesson.

Comparison card: Amazon Linux vs Ubuntu. Amazon Linux: AWS's own Linux for EC2. Ubuntu: A popular Linux from Canonical.
Comparison: Amazon Linux vs Ubuntu · Download PNG
Revision card: Fix Apache downloading .php on Ubuntu. Opening index.php downloads the file instead of running it. Apache must hand .php files to php-fpm. Two modules and one config do that. Enable them, then restart Apache and clear the browser cache. Commands (PHP 8.3):
Revision card: Fix Apache downloading .php on Ubuntu · Download PNG
Revision card: Nginx PHP location block. Nginx does not run PHP by itself; .php shows as text or downloads. A location block sends .php to the php-fpm socket. The socket name has the PHP version. Uncomment the block, fix the version, nginx -t, reload. Socket line:
Revision card: Nginx PHP location block · Download PNG
Revision card: The apt lock error. Right after boot, apt install fails with "Could not get lock". Ubuntu runs automatic updates first. Only one apt can run at a time. Wait a few minutes and retry. Do not delete lock files. Error:
Revision card: The apt lock error · Download PNG
Revision card: First steps in MySQL on Ubuntu. The app needs a password to log in to MySQL. On Ubuntu, root logs in with sudo mysql (no password). ALTER USER sets one. Set it, exit, then log in with -p. Typing is hidden; that is normal. SQL:
Revision card: First steps in MySQL on Ubuntu · Download PNG
Interview card: A .php page downloads instead of running. What is wrong?. The web server is not passing .php to php-fpm. Without the handler, Apache or Nginx sends the file as-is. Apache: a2enmod proxy_fcgi + a2enconf php8.3-fpm. Nginx: php location block.
Interview card: A .php page downloads instead of running. What is wrong? · Download PNG
Interview card: What changes when you set up LAMP on Ubuntu instead of Amazon Linux?. apt-get instead of yum, apache2 instead of httpd, and a versioned php8.x-fpm service. Package and service names differ by distribution. Ubuntu:
Interview card: What changes when you set up LAMP on Ubuntu instead of Amazon Linux? · Download PNG
Interview card: A service fails to start. What is your first habit?. Read the last lines of the status output, then the journal. The real error is usually in the bottom lines. Commands:
Interview card: A service fails to start. What is your first habit? · Download PNG
Interview card: Why do real setups keep MySQL on its own server?. Security, separate scaling and safety of data if the web server breaks. A hacked web server should not expose the database directly. Web on EC2, data on RDS in a private subnet.
Interview card: Why do real setups keep MySQL on its own server? · Download PNG

More cards: Ravi Sir's Cards.