One place for hosting & domains

      June 2019

      How To Use PostgreSQL with Your Ruby on Rails Application on Ubuntu 18.04


      Introduction

      When using the Ruby on Rails web framework, your application is set up by default to use SQLite as a database. SQLite is a lightweight, portable, and user-friendly relational database that performs especially well in low-memory environments, and will work well in many cases. However, for highly complex applications that need more reliable data integrity and programmatic extensibility, a PostgreSQL database will be a more robust and flexible choice. In order to configure your Ruby on Rails setup to use PostgreSQL, you will need to perform a few additional steps to get it up and running.

      In this tutorial, you will set up a Ruby on Rails development environment connected to a PostgreSQL database on an Ubuntu 18.04 server. You will install and configure PostgreSQL, and then test your setup by creating a Rails application that uses PostgreSQL as its database server.

      Prerequisites

      This tutorial requires the following:

      Step 1 – Installing PostgreSQL

      In order to configure Ruby on Rails to create your web application with PostgreSQL as a database, you will first install the database onto your server.

      Using sudo privileges, update your APT package index to make sure that your repositories are up to date:

      Next, install PostgreSQL and its development libraries:

      • sudo apt install postgresql postgresql-contrib libpq-dev

      In the previous command, the postgresql package holds the main PostgreSQL program, while postgresql-contrib adds several PostgreSQL features that extend its capabilities. libpq-dev is a PostgreSQL library that allows clients to send queries and receive responses from the back-end server, which will allow your application to communicate with its database.

      Once PostgreSQL and its dependencies are installed, the next step is to create a role that your Rails application will use later to create your database.

      Step 2 – Creating a New Database Role

      In PostgreSQL, roles can be used in the same way as users in Linux to organize permissions and authorization. This step will show you how to create a new super user role for your Linux username that will allow you to operate within the PostgreSQL system to create and configure databases.

      To create a PostgreSQL super user role, use the following command, substituting the highlighted word with your Ubuntu 18.04 username:

      • sudo -u postgres createuser -s sammy -P

      Since you specified the -P flag, you will be prompted to enter a password for your new role. Enter your desired password, making sure to record it so that you can use it in a configuration file in a future step.

      In this command, you used createuser to create a role named sammy. The -s gave this user super user privileges, and sudo -u allowed you to run the command from the postgres account that is automatically created upon installing PostgreSQL.

      Note: Since the authentication mode for PostgreSQL on Ubuntu 18.04 starts out as ident, by default an Ubuntu user can only operate in PostgreSQL with a role of the same name. For more information, check out the PostgreSQL official documentation on authentication.

      If you did not use the -P flag and want to set a password for the role after you create it, enter the PostgreSQL console with the following command:

      You will receive the following output, along with the prompt for the PostgreSQL console:

      Output

      psql (10.9 (Ubuntu 10.9-0ubuntu0.18.04.1)) Type "help" for help. postgres=#

      The PostgreSQL console is indicated by the postgres=# prompt. At the PostgreSQL prompt, enter this command to set the password for the new database role, replacing the highlighted name with the one you created:

      PostgreSQL will prompt you for a password. Enter your desired password at the prompt, then confirm it.

      Now, exit the PostgreSQL console by entering this command:

      Your usual prompt will now reappear.

      In this step, you created a new PostgreSQL role with super user privileges. Now you are ready to create a new Rails app that uses this role to create a database.

      Step 3 – Creating a New Rails Application

      With a role configured for PostgreSQL, you can now create a new Rails application that is set up to use PostgreSQL as a database.

      First, navigate to your home directory:

      Create a new Rails application in this directory, replacing appname with whatever you would like to call your app:

      • rails new appname -d=postgresql

      The -d=postgresql option sets PostgreSQL as the database.

      Once you've run this command, a new folder named appname will appear in your home directory, containing all the elements of a basic Rails application.

      Next, move into the application's directory:

      Now that you have created a new Rails application and have moved into the root directory for your project, you can configure and create your PostgreSQL database from within your Rails app.

      Step 4 – Configuring and Creating Your Database

      When creating the development and test databases for your application, Rails will use the PostgreSQL role that you created for your Ubuntu username. To make sure that Rails creates these databases, you will alter the database configuration file of your project. You will then create your databases.

      One of the configuration changes to make in your Rails application is to add the password for the PostgreSQL role you created in the last step. To keep sensitive information like passwords safe, it is a good idea to store this in an environment variable rather than to write it directly in your configuration file.

      To store your password in an environment variable at login, run the following command, replacing APPNAME with the name of your app and PostgreSQL_Role_Password with the password you created in the last step:

      • echo 'export APPNAME_DATABASE_PASSWORD="PostgreSQL_Role_Password"' >> ~/.bashrc

      This command writes the export command to your ~/.bashrc file so that the environment variable will be set at login.

      To export the variable for your current session, use the source command:

      Now that you have stored your password in your environment, it's time to alter the configuration file.

      Open your application's database configuration file in your preferred text editor. This tutorial will use nano:

      Under the default section, find the line that says pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> and add the following highlighted lines, filling in your credentials and the environment variable you created. It should look something like this:

      config/database.yml

      ...
      default: &default
        adapter: postgresql
        encoding: unicode
        # For details on connection pooling, see Rails configuration guide
        # http://guides.rubyonrails.org/configuring.html#database-pooling
        pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
        username: sammy
        password: <%= ENV['APPNAME_DATABASE_PASSWORD'] %>
      
      development:
        <<: *default
        database: appname_development
      ...
      

      This will make the Rails application run the database with the correct role and password. Save and exit by pressing CTRL + x, Y, then ENTER.

      For more information on configuring databases in Rails, see the Rails documentation.

      Now that you have made changes to config/database.yml, create your application's databases by using the rails command:

      Once Rails creates the database, you will receive the following output:

      Output

      Created database 'appname_development' Created database 'appname_test'

      As the output suggests, this command created a development and test database in your PostgreSQL server.

      You now have a PostgreSQL database connected to your Rails app. To ensure that your application is working, the next step is to test your configuration.

      Step 5 – Testing Your Configuration

      To test that your application is able to use the PostgreSQL database, try to run your web application so that it will show up in a browser.

      Using the rails server command, run your web application on the built-in webserver in your Rails app, Puma:

      • rails server --binding=127.0.0.1

      --binding binds your application to a specified IP. By default, this flag will bind Rails to 0.0.0.0, but since this means that Rails will listen to all interfaces, it is more secure to use 127.0.0.1 to specify the localhost. By default, the application listens on port 3000.

      Once your Rails app is running, your command prompt will disappear, replaced by this output:

      Output

      => Booting Puma => Rails 5.2.3 application starting in development => Run `rails server -h` for more startup options Puma starting in single mode... * Version 3.12.1 (ruby 2.6.3-p62), codename: Llamas in Pajamas * Min threads: 5, max threads: 5 * Environment: development * Listening on tcp://127.0.0.1:3000 Use Ctrl-C to stop

      To test if your application is running, open up a new terminal window on your server and use the curl command to send a request to 127.0.0.1:3000:

      • curl http://127.0.0.1:3000

      You will receive a lot of output in HTML, ending in something like:

      Output

      ... <strong>Rails version:</strong> 5.2.3<br /> <strong>Ruby version:</strong> 2.6.3 (x86_64-linux) </p> </section> </div> </body> </html>

      If your Rails application is on a remote server and you want to access it through a web browser, an easy way is to bind it to the public IP address of your server. First, open port 3000 in your firewall:

      Next, look up the public IP address of your server. You can do this by running the following curl command:

      • curl http://icanhazip.com

      This will return your public IP address. Use it with the rails server command, substituting server_public_IP with your server's public IP:

      • rails server --binding=server_public_IP

      Now you will be able to access your Rails application in a local web browser via the server's public IP address on port 3000 by visiting:

      http://server_public_IP:3000
      

      At this URL, you will find a Ruby on Rails welcome page:

      Ruby on Rails Welcome Page

      This means that your application is properly configured and connected to the PostgreSQL database.

      After testing the configuration, if you would like to close port 3000, use the following command.

      • sudo ufw delete allow 3000

      Conclusion

      In this tutorial, you created a Ruby on Rails web application that was configured to use PostgreSQL as a database on an Ubuntu 18.04 server. If you would like to learn more about the Ruby programming language, check out our How To Code in Ruby series.

      For more information on choosing a database for your application, check out our tutorial on the differences between and use cases of SQLite, PostgreSQL, and MySQL. If you want to read more about how to use databases, see our An Introduction to Queries in PostgreSQL article, or explore DigitalOcean's Managed Databases product.



      Source link

      How To Set Up a Help Desk System with OTRS on Ubuntu 18.04


      The author selected the Free Software Foundation to receive a donation as part of the Write for DOnations program.

      Introduction

      OTRS, also known as Open source Ticket Request System, is a help desk and IT service management system. It provides a single point of contact for users, customers, IT personnel, IT services, and any external organizations. The program is written in Perl, supports a variety of databases (MySQL, PostgreSQL, etc.), and can integrate with LDAP directories.

      In this tutorial, you will install OTRS Community Edition on an Ubuntu 18.04 server and set up a simple help desk system, which will allow you to receive and process requests from your customers using both the web interface and email.

      Prerequisites

      To complete this tutorial, you will need the following:

      • An Ubuntu 18.04 server set up by following our Initial Server Setup Guide for Ubuntu 18.04, including a non-root user with sudo privileges and a firewall configured with ufw.

      • Apache and MySQL installed on your Ubuntu server. Follow step 1 and 2 of this guide to configure these.

      • A fully registered domain name. This tutorial will use example.com throughout. You can purchase a domain name on Namecheap, get one for free on Freenom, or use the domain registrar of your choice.

      • Both of the following DNS records set up for your server. You can follow this introduction to DigitalOcean DNS for details on how to add them.

        • An A record with example.com pointing to your server’s public IP address.
        • An A record with www.example.com pointing to your server’s public IP address.
      • A TLS/SSL certificate installed on your Ubuntu 18.04 server for your domain. You can follow the Let’s Encrypt on Ubuntu 18.04 guide to obtain a free TLS/SSL certificate.

      • Postfix mail transfer agent set up by following our tutorial How To Install and Configure Postfix on Ubuntu 18.04.

      • (Optional) A dedicated Gmail account with IMAP access enabled, 2-step verification, and an App password generated with the Other (Custom name) option. When you generate the App password, write it down so that you can use it in Step 5. You will use Gmail to configure inbound mail ticket creation in OTRS, with Gmail as your IMAPS mailbox. This is just one method of configuring inbound mail for OTRS; if you would like to explore other options, check out the OTRS documentation.

      Warning: Do not use any of your own active Gmail accounts to configure inbound mail for OTRS. When imap.gmail.com forwards emails to OTRS, all emails in the Gmail account are deleted. Because of this, it is a better option to create a new Gmail account to use specifically for OTRS.

      Step 1 — Installing the OTRS Package and Perl Modules

      In this step, you will install OTRS and a set of Perl modules that will increase the system’s functionality.

      OTRS is available in Ubuntu’s package manager, but the official documentation suggests installing OTRS from source.

      To do this, first log into your Ubuntu server as your non-root user:

      • ssh sammy@Ubuntu_Server_IP

      Then download the source archive with the wget command. For this tutorial, you will download version 6.0.19; you can find the latest available version on the OTRS download page.

      • wget http://ftp.otrs.org/pub/otrs/otrs-6.0.19.tar.gz

      Next, unpack the compressed file with tar:

      • tar xzf otrs-6.0.19.tar.gz

      Move the contents of the archive into the /opt/otrs directory:

      • sudo mv otrs-6.0.19 /opt/otrs

      Because OTRS is written in Perl, it uses a number of Perl modules. Check for missing modules by using the CheckModules.pl script included with OTRS:

      • sudo /opt/otrs/bin/otrs.CheckModules.pl

      You’ll see output like this, listing which modules you already have downloaded and which you are missing:

      Output

      o Apache::DBI......................FAILED! Not all prerequisites for this module correctly installed. o Apache2::Reload..................ok (v0.13) . . . o XML::LibXML......................Not installed! Use: 'apt-get install -y libxml-libxml-perl' (required - Required for XML processing.) o XML::LibXSLT.....................Not installed! Use: 'apt-get install -y libxml-libxslt-perl' (optional - Required for Generic Interface XSLT mapping module.) o XML::Parser......................Not installed! Use: 'apt-get install -y libxml-parser-perl' (optional - Recommended for XML processing.) o YAML::XS.........................Not installed! Use: 'apt-get install -y libyaml-libyaml-perl' (required - Required for fast YAML processing.)

      Some modules are only needed for optional functionality, such as communication with other databases or handling mail with specific character sets; others are necessary for the program to work.

      Although the suggested commands to download these modules use apt-get, this tutorial will install the missing modules with the apt command, which is the suggested best practice for Ubuntu 18.04. Feel free to go through these modules manually, or use the following command:

      $ sudo apt install libapache2-mod-perl2 libdbd-mysql-perl libtimedate-perl libnet-dns-perl libnet-ldap-perl 
          libio-socket-ssl-perl libpdf-api2-perl libsoap-lite-perl libtext-csv-xs-perl 
          libjson-xs-perl libapache-dbi-perl libxml-libxml-perl libxml-libxslt-perl libyaml-perl 
          libarchive-zip-perl libcrypt-eksblowfish-perl libencode-hanextra-perl libmail-imapclient-perl 
          libtemplate-perl libdatetime-perl
      

      Whenever you’re done installing these modules, rerun the script to make sure that all the required modules have been installed:

      • sudo /opt/otrs/bin/otrs.CheckModules.pl

      Your output will now show all the installed modules:

      Output

      ... o Text::CSV_XS.....................ok (v1.34) o Time::HiRes......................ok (v1.9741) o XML::LibXML......................ok (v2.0128) o XML::LibXSLT.....................ok (v1.95) o XML::Parser......................ok (v2.44) o YAML::XS.........................ok (v0.69)

      Now that you have OTRS and its dependencies installed on your server, you can configure OTRS to use Apache and MySQL.

      Step 2 — Configuring OTRS, Apache, and MySQL server

      In this step, you will create a system user for OTRS, and then configure Apache and MySQL server to work with OTRS.

      Create a user named otrs to run OTRS functions with the useradd command:

      • sudo useradd -d /opt/otrs -c 'OTRS user' otrs

      -d sets the user’s home directory as /opt/otrs, and -c sets the 'OTRS user' comment to describe the user.

      Next, add otrs to the webserver group:

      • sudo usermod -G www-data otrs

      OTRS comes with a default config file /opt/otrs/Kernel/Config.pm.dist. Activate this by copying it without the .dist filename extension:

      • sudo cp /opt/otrs/Kernel/Config.pm.dist /opt/otrs/Kernel/Config.pm

      Now, navigate to the /opt/otrs directory:

      From here, run the otrs.SetPermissions.pl script. It will detect the correct user and group settings and set the file and directory permissions for OTRS.

      • sudo bin/otrs.SetPermissions.pl

      This will yield the following output:

      Output

      Setting permissions on /opt/otrs

      The correct permissions are now set.

      Next, activate the apache2 configuration file and make sure it is loaded after all other configurations. To do this, make a symbolic link with the zzz_ prefix:

      • sudo ln -s /opt/otrs/scripts/apache2-httpd.include.conf /etc/apache2/sites-enabled/zzz_otrs.conf

      OTRS requires a few Apache modules to be active for optimal operation. You can activate them via the tool a2enmod. Although some of these have already been enabled, it is a good idea to check them all:

      • sudo a2enmod perl
      • sudo a2enmod headers
      • sudo a2enmod deflate
      • sudo a2enmod filter

      These modules enable Apache to work with Perl, control HTTP headers, compress server output, and configure output content filters.

      Restart your web server to apply new configurations:

      • sudo systemctl restart apache2

      Before you go to the next step and run the web installer, change some of the MySQL configuration settings. Open the MySQL configuration file in your preferred text editor. This tutorial uses nano:

      • sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

      Look for the following options under the [mysqld] section. For max_allowed_packet and query_cache_size, change the values to 64M and 32M respectively, as highlighted in the following code block:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      ...
      max_allowed_packet      = 64M
      thread_stack            = 192K
      thread_cache_size       = 8
      # This replaces the startup script and checks MyISAM tables if needed
      # the first time they are touched
      myisam-recover-options  = BACKUP
      #max_connections        = 100
      #table_open_cache       = 64
      #thread_concurrency     = 10
      #
      # * Query Cache Configuration
      #
      query_cache_limit       = 1M
      query_cache_size        = 32M
      ...
      

      This adjusts the maximum allowed packet size and the query cache size so that MySQL can interface with OTRS.

      Then add the following highlighted additional options under the [mysqld] section, at the end of the file:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      ...
      # ssl-cert=/etc/mysql/server-cert.pem
      # ssl-key=/etc/mysql/server-ikey.pem
      innodb_log_file_size = 256M
      collation-server = utf8_unicode_ci
      init-connect='SET NAMES utf8'
      character-set-server = utf8
      

      This sets the database logfile size, determines the character set and collation, and creates an init_connect string to set the character set upon starting the MySQL server.

      Save and close mysqld.cnf by pressing CTRL + X, followed by Y and then ENTER. Then, restart your MySQL server to apply the new parameters:

      • sudo systemctl restart mysql.service

      Now that you have created the otrs user and configured Apache and MySQL to work with OTRS, you are ready to use the web installer.

      Step 3 — Using the Web Installer

      In this step, you will configure OTRS's database settings in a web browser and start the OTRS daemon process on the command line.

      Open https://example.com/otrs/installer.pl in your favorite web browser, replacing example.com with your domain name. You will find a welcome screen with the message Welcome to OTRS 6 and information about the OTRS offices.

      OTRS Welcome Screen

      Click Next. The next screen will have the license for OTRS, which is the GNU General Public License common to open source programs. Accept by clicking Accept license and continue after reading.

      On the next screen, you will be prompted to select a database type. The defaults (MySQL and Create a new database for OTRS) are fine for your setup, so click Next to proceed.

      Database Selection

      On the next screen, enter the MySQL credentials that you set up during the MySQL server installation. Use root for the User field, then enter the password you created. Leave the default host value.

      Click Check database settings to make sure it works. The installer will generate credentials for the new database. There is no need to remember this generated password.

      Result of database check

      Click Next to proceed.

      The database will be created and you will see the successful result:

      Database setup successful

      Click Next.

      Next, provide the following required system settings:

      • System FQDN: A fully qualified domain name. Replace example.com with your own domain name.
      • AdminEmail: The email address of your system administrator. Emails about errors with OTRS will go here.
      • Organization: Your organization's name.

      Leave all other options at their default values:

      System Settings

      Click Next.

      Now you will land on the Mail Configuration page. In order to be able to send and receive emails, you have to configure a mail account. This tutorial will take care of this later in Step 5, so click Skip this step.

      The OTRS installation is now complete; you will see a Finished page with a link to the admin panel after Start page, and the credentials of the OTRS super user after that. Make sure you write down the generated password for the root@localhost user and the URL for the Start page.

      The only thing left after a successful installation is to start the OTRS daemon and activate its cronjob.

      Bring up the terminal you are using to access your Ubuntu 18.04 server. The OTRS daemon is responsible for handling any asynchronous and recurring tasks in OTRS. Start it with the otrs user:

      • sudo su - otrs -c "/opt/otrs/bin/otrs.Daemon.pl start"

      You will see the following output:

      Output

      Manage the OTRS daemon process. Daemon started

      There are two default cron files in the /opt/otrs/var/cron/ directory. Move into this directory.

      These cron files are used to make sure that the OTRS daemon is running. Activate them by copying them without the .dist filename extension.

      • sudo cp aaa_base.dist aaa_base
      • sudo cp otrs_daemon.dist otrs_daemon

      To schedule these cron jobs, use the script Cron.sh with the otrs user:

      • sudo su - otrs -c "/opt/otrs/bin/Cron.sh start"

      You have now installed OTRS with the web installer and set up its connection to the MySQL database. You also started the OTRS daemon on your server. Next, you will log in to the administrator web interface and secure OTRS.

      Step 4 — Securing OTRS

      At the moment, you have a fully functional application, but it's not secure to use the super user account with OTRS. Instead, you'll create a new agent. In OTRS, agents are users who have rights to the various functions of the system. In this example, you will use a single agent who has access to all functions of the system.

      To get started, log in as root@localhost. Open the Start page link which you received in the previous step. Enter root@localhost for the username and the password you copied from Step 3, then click Login.

      You will see the main dashboard. It contains several widgets which show different information about tickets, statistics, news, etc. You can freely rearrange them by dragging or switch their visibility in settings.

      Main screen

      First, create a new agent. To do this, follow the link by clicking on the red message in the top of the screen that reads Don't use the Superuser account to work with OTRS 6! Create new Agents and work with these accounts instead. This will bring you to the Agent Management screen.

      Agent Management

      Click the Add agent button. This will bring you to the Add Agent screen. Most of the default options are fine. Fill in the first name, last name, username, password, and email fields. Record the username and password for future login. Submit the form by clicking the Save button.

      Next, change the group relations for the new agent. Because your agent will also be the administrator, you can give it full read and write access to all groups. To do this, click the checkbox next to RW all the way on the right, under Change Group Relations for Agent.

      Change Group Relations

      Finally, click Save and finish.

      Now, log out and log back in again using the newly created account. You can find the Logout link by clicking on the avatar picture in the top left corner.

      Logout Location

      Once you have logged back in, you can customize your agent's preferences by clicking on Personal preferences in the avatar menu. There you can change your password, choose the interface language, configure setup notifications and favorite queues, change interface skins, etc.

      Once you have logged in as your new agent and configured the account to your liking, the next step is to configure the inbound mail options to generate tickets from incoming emails.

      Step 5 — Configuring Inbound Mail

      Customers have two ways to forward new tickets to OTRS: via the customer front-end or by sending an email. In order to receive customer's messages you need to set up a POP or IMAP account. In this tutorial, you will use your dedicated OTRS Gmail account that you created as a prerequisite.

      Navigate to the Admin tab by clicking on Admin in the top menu. Then find the PostMaster Mail Accounts option and click on it. Press the Add Mail Account button to set up a new mailbox.

      Add Mail Account

      On the Add Mail Account screen, select IMAPS for Type. For Username, type in your Gmail address, and for Password, enter the App password that you generated for your Gmail account in the prerequisites. Leave all other options as default. Click Save.

      Note: You can use Gmail for IMAPS without 2-step verification by enabling Less secure app access for your Gmail account. You will find instructions on how to do this in the Google Help Center. However, this method is less secure, and it can take up to 24 hours for Less secure app access to take effect. It is recommended that you use the App password method.

      Next, send a test email from an external email account to your dedicated OTRS Gmail account. The mail will be fetched every 10 minutes by the OTRS daemon, but you can force receipt by clicking the Fetch mail link.

      As a result, you will see the new ticket.

      Email ticket

      Now you are ready to accept tickets from customers via email. Next, you will go through the process of creating a ticket through the customer front-end.

      Step 6 — Working with the Customer Interface

      The second way for a customer to create a ticket is through the OTRS front-end. In this step, you will walk through this process to make sure this ticket creation method is set up.

      The customer front-end is located at https://example.com/otrs/customer.pl. Navigate to it in a web browser. You can create a customer account there and submit a ticket using the GUI.

      Use the Sign up now link to open the registration form.

      Create Account

      Fill out the form and press the Create button.

      You will see a message like this:

      New account created. Sent login information to [email protected]. Please check your email.
      

      Check your inbox for the message from the OTRS. You will see a message with the new account credentials:

      Hi sammy,
      
      You or someone impersonating you has created a new OTRS account for
      you.
      
      Full name: sammy
      User name: [email protected]
      Password : Sammy_Password
      
      You can log in via the following URL. We encourage you to change your password
      via the Preferences button after logging in.
      
      http://example.com/otrs/customer.pl
      

      Now, use the provided credentials to access the customer front-end and create another ticket. All new tickets created using the customer front-end will immediately appear on the agent's dashboard:

      Customer ticket

      On the agent dashboard, you can see the information on all current tickets: their status (new, opened, escalated, etc.), their age (the time elapsed from the moment when the ticket was received), and subject.

      You can click on the ticket number (in the TICKET# column) to view its details. The agent can also take actions on the ticket here, like changing its priority or state, moving it to another queue, closing it, or adding a note.

      You have now successfully set up your OTRS account.

      Conclusion

      In this tutorial, you set up OTRS and created test help desk tickets. Now you can accept and process requests from your users using both the web interface and email.

      You can learn more about OTRS by reading the OTRS Admin Manual. If you want to read more about how to use MySQL, see our An Introduction to Queries in MySQL article, or explore DigitalOcean's Managed Databases product.



      Source link

      How To Install an Apache, MySQL, and PHP (FAMP) Stack on FreeBSD 12.0


      The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      A FAMP stack, which is similar to a LAMP stack on Linux, is a group of open source software that is typically installed together to enable a FreeBSD server to host dynamic websites and web apps. FAMP is an acronym that stands for FreeBSD (operating system), Apache (web server), MySQL (database server), and PHP (to process dynamic PHP content).

      In this guide, we’ll get a FAMP stack installed on a FreeBSD 12.0 cloud server using pkg, the FreeBSD package manager.

      Prerequisites

      Before you begin this guide you’ll need the following:

      • A FreeBSD 12.0 Droplet.
      • Access to a user with root privileges (or allowed by using sudo) in order to make configuration changes.
      • A firewall configured using this tutorial on Recommended Steps For New FreeBSD 12.0 Servers. Ensure you open ports 80 and 443 as part of your setup.
      • Familiarity with the CLI (Command Line Interface) is recommended. FreeBSD’s vi editor has almost identical syntax as vim.

      Step 1 — Installing Apache

      The Apache web server is currently the most popular web server in the world, which makes it a great choice for hosting a website.

      You can install Apache using FreeBSD’s package manager, pkg. A package manager allows you to install most software pain-free from a repository maintained by FreeBSD. You can learn more about how to use pkg here.

      To install Apache 2.4 using pkg, use this command:

      • sudo pkg install apache24

      Enter y at the confirmation prompt to install Apache and its dependencies.

      To enable Apache as a service, add apache24_enable="YES" to the /etc/rc.conf file. You’ll use the sysrc command to do just that:

      • sudo sysrc apache24_enable="YES"

      Now start Apache:

      • sudo service apache24 start

      To check that Apache has started you can run the following command:

      • sudo service apache24 status

      As a result you’ll see something similar to:

      Output

      apache24 is running as pid 20815.

      You can do a spot check right away to verify that everything went as planned by visiting your server’s public IP address in your web browser. See the note under the next heading to find out what your public IP address is, if you do not have this information already:

      http://your_server_IP_address/
      

      You will see the default FreeBSD Apache web page, which is there for testing purposes. You’ll see: It Works!, which indicates that your web server is correctly installed.

      How To find Your Server’s Public IP Address

      If you do not know what your server’s public IP address is, there are a number of ways that you can find it. Usually, this is the address you use to connect to your server through SSH.

      If you are using DigitalOcean, you may look in the Control Panel for your server’s IP address. You may also use the DigitalOcean Metadata service, from the server itself, with this command: curl -w "n" http://169.254.169.254/metadata/v1/interfaces/public/0/ipv4/address.

      A more universal way to look up the IP address is to use the ifconfig command, on the server itself. The ifconfig command will print out information about your network interfaces. In order to narrow down the output to only the server’s public IP address, use this command (note that the highlighted part is the name of the network interface, and may vary):

      • ifconfig vtnet0 | grep "inet " | awk '{ print $2; exit }'

      You could also use curl to contact an outside party, like icanhazip, to tell you how it sees your server. This is done by asking a specific server what your IP address is:

      • curl http://icanhazip.com

      Now that you have the public IP address, you may use it in your web browser’s address bar to access your web server.

      Step 2 — Installing MySQL

      Now that you have your web server up and running, it is time to install MySQL, the relational database management system. The MySQL server will organize and provide access to databases where your server can store information.

      Again, you can use pkg to acquire and install your software.

      To install MySQL 8.0 using pkg, use this command:

      • sudo pkg install mysql80-server

      Enter y at the confirmation prompt to install the MySQL server and client packages.

      To enable MySQL server as a service, add mysql_enable="YES" to the /etc/rc.conf file. You can us the sysrc command to do just that:

      • sudo sysrc mysql_enable="YES"

      Now start the MySQL server with the following command:

      • sudo service mysql-server start

      You can verify the service is up and running:

      • sudo service mysql-server status

      You’ll read something similar to the following:

      Output

      mysql is running as pid 21587.

      Now that your MySQL database is running, you will want to run a simple security script that will remove some dangerous defaults and slightly restrict access to your database system. Start the interactive script by running this command:

      • sudo mysql_secure_installation

      The prompt will ask you if you want to set a password. Since you just installed MySQL, you most likely won’t have one, so type Y and follow the instructions:

       Would you like to setup VALIDATE PASSWORD component?
      
      Press y|Y for Yes, any other key for No: y
      
      There are three levels of password validation policy:
      
      LOW    Length >= 8
      MEDIUM Length >= 8, numeric, mixed case, and special characters
      STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
      
      Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
      Please set the password for root here.
      
      New password:  password
      
      Re-enter new password:  password
      
      Estimated strength of the password: 50
      Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
      

      For the rest of the questions, you should hit the y key at each prompt to accept the recommended safe values. This will remove some sample users and databases, disable remote root logins, and load these new rules so that MySQL immediately respects the changes you’ve made.

      At this point, your database system is now set up and you can move on to installing PHP.

      Step 3 — Installing PHP

      PHP is the component of your setup that will process code to display dynamic content. It can run scripts, connect to MySQL databases to get information, and hand the processed content over to the web server to display.

      You can once again leverage the pkg system to install your components. You’re going to include the mod_php, php-mysql, and php-mysqli package as well.

      To install PHP 7.3 with pkg, run this command:

      • sudo pkg install php73 php73-mysqli mod_php73

      Enter y at the confirmation prompt. This installs the php73, mod_php73, and php73-mysqli packages.

      Now copy the sample PHP configuration file into place with this command:

      • sudo cp /usr/local/etc/php.ini-production /usr/local/etc/php.ini

      Now run the rehash command to regenerate the system’s cached information about your installed executable files:

      Before using PHP, you must configure it to work with Apache.

      Installing PHP Modules (Optional)

      To enhance the functionality of PHP, you can optionally install some additional modules.

      To see the available options for PHP 7.3 modules and libraries, you can type this:

      pkg search php73
      

      The results will be mostly PHP 7.3 modules that you can install:

      Output

      php73-7.3.5 PHP Scripting Language php73-aphpbreakdown-2.2.2 Code-Analyzer for PHP for Compatibility Check-UP php73-aphpunit-1.8 Testing framework for unit tests php73-bcmath-7.3.5 The bcmath shared extension for php php73-brotli-0.6.2 Brotli extension for PHP php73-bsdconv-11.5.0 PHP wrapper for bsdconv php73-bz2-7.3.5 The bz2 shared extension for php php73-calendar-7.3.5 The calendar shared extension for php php73-composer-1.8.4 Dependency Manager for PHP php73-ctype-7.3.5 The ctype shared extension for php php73-curl-7.3.5 The curl shared extension for php php73-dba-7.3.5 The dba shared extension for php php73-deployer-6.4.3 Deployment tool for PHP php73-dom-7.3.5 The dom shared extension for php ...

      To get more information about what each module does, you can either search the internet or you can look at the long description of the package by typing:

      • pkg search -f package_name

      There will be a lot of output, with one field called Comment which will have an explanation of the functionality that the module provides.

      For example, to find out what the php73-calendar package does, you could type this:

      • pkg search -f php73-calendar

      Along with a large amount of other information, you'll find something that looks like this:

      Output

      php73-calendar-7.3.5 Name : php73-calendar Version : 7.3.5 ... Comment : The calendar shared extension for php ...

      If, after researching, you decide that you would like to install a package, you can do so by using the pkg install command.

      For example, if you decide that php73-calendar is something that you need, you could type:

      • sudo pkg install php73-calendar

      If you want to install more than one module at a time, you can do that by listing each one, separated by a space, following the pkg install command, like this:

      • sudo pkg install package1 package2 ...

      Step 4 — Configuring Apache to Use PHP Module

      Apache HTTP has a dedicated directory to write configuration files into it for specific modules. You will write one of those configuration files for Apache HTTP to "speak" PHP.

      • sudo vi /usr/local/etc/apache24/modules.d/001_mod-php.conf

      Add the following lines to that file:

      /usr/local/etc/apache24/modules.d/001_mod-php.conf

      <IfModule dir_module>
          DirectoryIndex index.php index.html
          <FilesMatch ".php$">
              SetHandler application/x-httpd-php
          </FilesMatch>
          <FilesMatch ".phps$">
              SetHandler application/x-httpd-php-source
          </FilesMatch>
      </IfModule>
      

      Now check Apache's HTTP configuration is in good condition:

      • sudo apachectl configtest

      You'll see the following output:

      Output

      Performing sanity check on apache24 configuration: Syntax OK

      Because you've made configuration changes in Apache you have to restart the service for those to be applied. Otherwise Apache will still work with the prior configuration.

      Now you can move on to testing PHP on your system.

      Step 5 — Testing PHP Processing

      In order to test that your system is configured properly for PHP, you can create a very basic PHP script.

      You'll call this script info.php. In order for Apache to find the file and serve it correctly, it must be saved under a specific directory—DocumentRoot—which is where Apache will look for files when a user accesses the web server. The location of DocumentRoot is specified in the Apache configuration file that you modified earlier (/usr/local/etc/apache24/httpd.conf).

      By default, the DocumentRoot is set to /usr/local/www/apache24/data. You can create the info.php file under that location by typing:

      • sudo vi /usr/local/www/apache24/data/info.php

      This will open a blank file. Insert this PHP code into the file:

      /usr/local/www/apache24/data/info.php

      <?php phpinfo(); ?>
      

      Save and exit.

      Now you can test whether your web server can correctly display content generated by a PHP script. To try this out, you can visit this page in your web browser:

      http://your_server_IP_address/info.php
      

      You'll see a PHP FreeBSD testing page.

      FreeBSD info.php

      This page gives you information about your server from the perspective of PHP. It is useful for debugging and to ensure that your settings are being applied correctly.

      If this was successful, then your PHP is working as expected.

      You should remove this file after this test because it could actually give information about your server to unauthorized users. To do this, you can type this:

      • sudo rm /usr/local/www/apache24/data/info.php

      You can always recreate this page if you need to access the information again later.

      Conclusion

      Now that you have a FAMP stack installed, you have many choices for what to do next. You've installed a platform that will allow you to install most kinds of websites and web software on your server.



      Source link