{"id":217,"date":"2018-03-22T22:56:08","date_gmt":"2018-03-22T22:56:08","guid":{"rendered":"https:\/\/www.bigwisedata.com\/?p=217"},"modified":"2024-03-11T00:27:59","modified_gmt":"2024-03-11T00:27:59","slug":"setting-up-wordpress-4-9-4-on-openbsd-6-2-part3","status":"publish","type":"post","link":"https:\/\/www.bigwisedata.com\/?p=217","title":{"rendered":"Setting up MariaDB for WordPress 4.9.4 on OpenBSD 6.2 part3"},"content":{"rendered":"

The Bitter<\/a> DB …<\/h3>\n

Welcome back to installing WordPress 4.9.4 on OpenBSD 6.2.  <\/span>In this installment we are going to install MariaDB for WordPress to use as a data store.  <\/span>To be successful we will need to configure our Mariadb web access user to run as a socket in our chrooted environment, and secure the database against malicious users. To that end our goals are too:<\/p>\n

    \n
  1. Install the MariaDB -10.0.32v1<\/span> required by WordPress<\/li>\n
  2. Configure PHP to use the MariaDB libraries <\/span><\/li>\n
  3. Configure MariaDB to run correctly in a Chroot\u2019d environment that httpd uses.<\/li>\n<\/ol>\n

    To test our MariaDB and httpds ability to talk to it through the php socket we will need to set up our testing environment.  <\/span>We will be using two terminal sessions in two separate windows today:<\/span><\/p>\n

      \n
    1. A “VPS” in which we will be starting httpd sessions in debug mode <\/span><\/span><\/li>\n
    2. A \u201cMaintenance\u201d widow to be used to make any changes to your configuration files etc.<\/span><\/li>\n<\/ol>\n

      Open a ssh connection using you Client window to your VPS as our beloved \u201dbob” and su to root.  <\/span>We will need root privileges to make our changes.<\/span><\/p>\n

      Now we will need to make some test files.  <\/span>The first test file will make sure curl is functioning through the php socket by making a connection to WordPress.org.  <\/span>In your Maintenance window using your favorite editor create  <\/span>file in \/var\/www\/htdocs called test_curl.php.<\/span><\/p>\n

      Vi \/var\/www\/htdocs\/test_curl.php<\/span><\/p>\n

      Paste the following into the file.<\/span><\/p>\n

      <?php\n$_h = curl_init();\ncurl_setopt($_h, CURLOPT_HEADER, 1);\ncurl_setopt($_h, CURLOPT_RETURNTRANSFER, 1);\ncurl_setopt($_h, CURLOPT_HTTPGET, 1);\n\/\/curl_setopt($_h, CURLOPT_URL,'https:\/\/jetpack.wordpress.com' );\ncurl_setopt($_h, CURLOPT_URL,'https:\/\/planet.wordpress.org' );\ncurl_setopt($_h, CURLOPT_DNS_USE_GLOBAL_CACHE, false );\ncurl_setopt($_h, CURLOPT_DNS_CACHE_TIMEOUT, 2 );\nvar_dump(curl_exec($_h));\nvar_dump(curl_getinfo($_h));\nvar_dump(curl_error($_h)); \n?><\/pre>\n
      And save the file.  <\/span>As you can see, it instantiates a curl object, loads some basic properties, executes the connection and dumps the results of the connection, the configuration details and any errors.<\/span><\/p>\n
      The second test file we will create test the MariaDB connection through the PHP socket.   <\/span>In your Maintenance window using your favorite editor create  <\/span>file in \/var\/www\/htdocs called test_mysql.php.<\/span><\/p>\n
      Vi \/var\/www\/htdocs\/test_mysql.php<\/span><\/p>\n
      Paste the following into the file.<\/span><\/p>\n
      <?php\n$user = \u201c\u2019your mysql user\u2019\u201d;\n$pwd = \u201cyour password\u201d;\n$host = \"127.0.0.1\";\n$db = \u201cyour database name\u201c;\n$link = mysqli_connect(\"$host\", \"$user\", \"$pwd\" , \"$db\");\nif (!$link) {\n   echo \"Error: Unable to connect to MySQL using user as $user and password as $pwd .\" . PHP_EOL;\n   echo \"Debugging errno: \" . mysqli_connect_errno() . PHP_EOL;\n   echo \"Debugging error: \" . mysqli_connect_error() . PHP_EOL;\n   $time = time();\n   $hash = md5(\u201cyour email\u201d  . time() . mt_rand() );\n   echo \"hash = $hash\" . PHP_EOL;\n   echo \"time = $time\" . PHP_EOL;\n   exit;\n}\n\necho \"Success: A proper connection to MySQL was made using user as $user and password as $pwd! The my_db database is great.\" . PHP_EOL;\necho \"Host information: \" . mysqli_get_host_info($link) . PHP_EOL;\n$time = time();\n$hash = md5( $value . time() . mt_rand() );\necho \"hash = $hash\" . PHP_EOL;\necho \"time = $time\" . PHP_EOL;\nmysqli_close($link);\n?><\/pre>\n
      You will need to update the \u201cyour mysql user\u201d, \u201cyour password\u201d, \u201cyour database name\u201d, and \u201cyour email\u201d with valid values once you have that information before we can test with this file.<\/span><\/p>\n
      Lets install the MariaDB.  <\/span>Now that we have the PKG system setup, we just need to query our Mirror to see if they have the MariaDB we want to install.  <\/span>But first lets refresh our memory on what we have installed from Setting up WordPress on OpenBSD 6.2 part2<\/span> by using pkg_info<\/span><\/p>\n
      pkg_info<\/span><\/p>\n
      Returns:<\/span><\/p>\n
      colorls-6.0         <\/span>ls(1) that can use color to display file attributes<\/span><\/p>\n
      curl-7.55.1         <\/span>get files from FTP, Gopher, HTTP or HTTPS servers<\/span><\/p>\n
      femail-1.0p1        <\/span>simple SMTP client<\/span><\/p>\n
      femail-chroot-1.0p2 simple SMTP client for chrooted web servers<\/span><\/p>\n
      gettext-0.19.8.1p1  <\/span>GNU gettext runtime libraries and programs<\/span><\/p>\n
      jpeg-1.5.1p0v0      <\/span>SIMD-accelerated JPEG codec replacement of libjpeg<\/span><\/p>\n
      libiconv-1.14p3     <\/span>character set conversion library<\/span><\/p>\n
      libxml-2.9.5        <\/span>XML parsing library<\/span><\/p>\n
      mariadb-client-10.0.32v1 multithreaded SQL database (client)<\/span><\/p>\n
      nghttp2-1.26.0      <\/span>library for HTTP\/2<\/span><\/p>\n
      php-7.0.23          <\/span>server-side HTML-embedded scripting language<\/span><\/p>\n
      php-curl-7.0.23     <\/span>curl URL library extensions for php5<\/span><\/p>\n
      php-gd-7.0.23       <\/span>image manipulation extensions for php5<\/span><\/p>\n
      php-mysqli-7.0.23   <\/span>mysql database access extensions for php5<\/span><\/p>\n
      png-1.6.31          <\/span>library for manipulating PNG images<\/span><\/p>\n
      quirks-2.367        <\/span>exceptions to pkg_add rules<\/span><\/p>\n
      xz-5.2.3p0          <\/span>LZMA compression and decompression tools<\/span><\/p>\n
      But what\u2019s this?  <\/span>The MariaDB is already installed?  <\/span>Well, not the part we need.  <\/span>What your seeing is the <\/span>mariadb-client-10.0.32v1 multithreaded SQL database (client) <\/span>which was an install requirement for \u201c<\/span>php-mysqli-7.0.23   <\/span>mysql database access extensions for php5.<\/span>\u201c(See pkg_info section in the Setting up WordPress on OpenBSD 6.2 Part 2 on install requirements)  <\/span>We need the \u201cserver\u201d component for the mariadb as well. Lets check for that on the mirror:<\/span><\/p>\n
      pkg_info -Q mariadb<\/span><\/p>\n
      Returns:<\/span><\/p>\n
      mariadb-client-10.0.32v1 (installed)<\/span><\/p>\n
      mariadb-server-10.0.32v1<\/span><\/p>\n
      mariadb-tests-10.0.32v1<\/span><\/p>\n
      Lets install the matching server component<\/span><\/p>\n
      pkg_add mariadb-server-10.0.32v1<\/span><\/p>\n
      As you saw, there are a lot of components to the mariadb install.  <\/span>You are also encouraged to read the <\/span>\/<\/span>usr\/local\/share\/doc\/pkg-readmes\/ for the mariadb-server-10.0.32v1 install.  <\/span>Do so.  <\/span>One of the first things you are asked to do in the <\/span>\/usr\/local\/share\/doc\/pkg-readmes\/mariadb-server-10.0.32v1 <\/span>is to run the \/usr\/local\/bin\/mysql_install_db in order to create the default database.<\/span><\/p>\n
      \/usr\/local\/bin\/mysql_install_db<\/span><\/p>\n
      Unfortunately, \u201c<\/span>\/usr\/local\/share\/doc\/pkg-readmes\/mariadb-server-10.0.32v1\u201d<\/span> makes no mention of <\/span>\/usr\/local\/bin\/mysql_secure_installation<\/span> which runs you through some basic security setup needed for mariadb to run on a production server.  <\/span>Good thing the <\/span>\/usr\/local\/bin\/mysql_install_db<\/span> does.  <\/span>But before we can do that process we need to enable the mysql daemon.<\/span><\/p>\n
      rcctl enable mysqld<\/span><\/p>\n
      Now start the mysqld daemon<\/span><\/p>\n
      rcctl start mysqld<\/span><\/p>\n
      Now execute the mysql_secure_installation <\/span><\/span><\/p>\n
      \/usr\/local\/bin\/mysql_secure_installation<\/span><\/p>\n
      You are now presented with a series of questions:<\/span><\/p>\n
        \n
      1. \u201cEnter the current password for root (enter for none):\u201d Press Enter.<\/li>\n
      2. \u201cSet root password?\u201d [Y\/n]\u201d Use your password vault to generate and store a password for the mysql root login, then enter it here and hit enter.<\/li>\n
      3. \u201cRemove anonymous users? [Y\/n]\u201d  <\/span>Enter \u201cY\u201d, we do not want anonymous users on our system.<\/li>\n
      4. \u201cDisallow root login remotely? [Y,n]\u201d Enter \u201cY\u201d, never let root have direct access to anything. Always login in with a low level user and then elevate your privileges.<\/li>\n
      5. \u201cRemove test database and access to it? [Y\/n]\u201d Enter \u201cY\u201d, test databases are for a test environment.<\/li>\n
      6. \u201cReload privilege tables now? [Y,n]\u201d Enter \u201cY\u201d, this activates all of our changes immediately.<\/li>\n<\/ol>\n
        You should now be at the command prompt.  <\/span>Lets test your \u201croot\u201d login by using the mysql client<\/span><\/p>\n
        mysql -u root -p <your root password><\/span><\/p>\n
        You should have a mysql prompt if everything went as planned.  <\/span>Now lets exit back out to the terminal by typing quit.<\/span><\/p>\n
        quit<\/span><\/p>\n
        Now let tell php all about mysql by editing the \/etc\/php-7.0.ini\u2026<\/span><\/p>\n
        vi \/etc\/php-7.0.ini<\/span><\/p>\n
        \u2026and scroll down to the extension section, it looks a lot like this:<\/span><\/p>\n
        ;;;;;;;;;;;;;;;;;;;;;;<\/span><\/p>\n
        ; Dynamic Extensions ;<\/span><\/p>\n
        ;;;;;;;;;;;;;;;;;;;;;;<\/span><\/p>\n
        ; If you wish to have an extension loaded automatically, use the following<\/span><\/p>\n
        ; syntax:<\/span><\/p>\n
        ;<\/span><\/p>\n
        ;   <\/span>extension=modulename.extension<\/span><\/p>\n
        ;<\/span><\/p>\n
        ; For example, on Windows:<\/span><\/p>\n
        ;<\/span><\/p>\n
        ;   <\/span>extension=msql.dll<\/span><\/p>\n
        ;<\/span><\/p>\n
        ; … or under UNIX:<\/span><\/p>\n
        ;<\/span><\/p>\n
        ;   <\/span>extension=msql.so<\/span><\/p>\n
        ;<\/span><\/p>\n
        ; … or with a path:<\/span><\/p>\n
        ;<\/span><\/p>\n
        ;   <\/span>extension=\/path\/to\/extension\/msql.so<\/span><\/p>\n
        ;<\/span><\/p>\n
        ; If you only provide the name of the extension, PHP will look for it in its<\/span><\/p>\n
        ; default extension directory.<\/span><\/p>\n
        ;<\/span><\/p>\n
        ; Windows Extensions<\/span><\/p>\n
        ; Note that ODBC support is built in, so no dll is needed for it.<\/span><\/p>\n
        ; Note that many DLL files are located in the extensions\/ (PHP 4) ext\/ (PHP 5+)<\/span><\/p>\n
        ; extension folders as well as the separate PECL DLL download (PHP 5+).<\/span><\/p>\n
        ; Be sure to appropriately set the extension_dir directive.<\/span><\/p>\n
        ;<\/span><\/p>\n
        ;extension=php_bz2.dll<\/span><\/p>\n
        ;extension=php_curl.dll<\/span><\/p>\n
        ;extension=php_fileinfo.dll<\/span><\/p>\n
        ;extension=php_ftp.dll<\/span><\/p>\n
        ;extension=php_gd2.dll<\/span><\/p>\n
        \u2026<\/span><\/p>\n
        Verify the path to the mysqli.so using find.<\/span><\/p>\n
        find \/ -name mysqli.so<\/span><\/p>\n
        Add the path returned by Find at the end of the extension section as in this example:<\/span><\/p>\n
        extension=<\/span>\/usr\/local\/lib\/php-7.0\/modules\/mysqli.so<\/span><\/p>\n
        Save the file.  <\/span>Now lets do a little testing.  <\/span><\/span>In your VPS window make sure your running as root.  <\/span>Now lets start or httpd in debug (-d) mode with verbosity turned to medium (-vv) using our httpd_config.test (-f \/etc\/httd_config.test)<\/span><\/p>\n
        httpd -d -vv -f \/etc\/httpd_config.test<\/span><\/p>\n
        Open up your web browser and point it to http:\/\/<IP address of your server>.   <\/span>You should see the out put of the phpinfo() function.  <\/span>Notice that there is now a section called mysqli curtesy of mysqli.so.<\/span><\/p>\n
        Type ctrl+c to shutdown the httpd daemon.<\/span><\/p>\n
        Lets add a database and a user to mysql for WordPress to use.  <\/span>At you terminal command prompt login as root to mysql<\/span><\/p>\n
        mysql -u root -p <your root password><\/span><\/p>\n
        Lets create a database called wp_website1.  <\/span>Type the following:<\/span><\/p>\n
        Create database wp_website1;<\/span><\/p>\n
        Now lets create a user that can access wp_website1 for WordPress to use.  <\/span>First generate a pass word in your password vault and save it then use it in the following query.  <\/span>Type the following:<\/span><\/p>\n
        Create user \u2018wp_user\u2019@\u2018127.0.0.1\u2019 identified by \u2018your_password_from_vault\u2019;<\/span><\/p>\n
        Do not use \u2018localhost\u2019, use \u2018127.0.0.1\u2019 to force mysql to use the database socket for this user.  <\/span>The \/var\/run\/mysql\/mysql.sock is the only way for our chroot\u2019d environment to talk to mysql.<\/span><\/p>\n
        Lets grant wp_user rights to do things on wp_website1<\/span><\/p>\n
        Grant all on wp_website1.* to \u2018wp_user\u2019@\u2018127.0.0.1\u2019;<\/span><\/p>\n
        Now exit mysql by typing quit.<\/span><\/p>\n
        Lets edit the \/var\/www\/htdocs\/test_mysql.php file and enter in your wp_user, password, database name and email information; being mindful that the user has both double and single quotes around the name and that some meta-characters like \u201c$\u201d need to be escaped with a leading \u201c\\\u201d if they are used in the password.<\/span><\/p>\n
        Great, lets test our database connection.  <\/span>In your VPS window start up you httpd daemon.  <\/span><\/span><\/p>\n
        httpd -d -vv -f \/etc\/httpd_config.test<\/span><\/p>\n
        In Your Maintenance window restart php and mysql<\/span><\/p>\n
        rcctl restart mysqld\nrcctl restart php70_fpm<\/pre>\n
        Now in you web browser enter the following URL <\/span>http:\/\/\u201cyour<\/span><\/a> IP address\u201d\/test_mysql.php.<\/span>  You should see something like the following:<\/span><\/p>\n
        Success: A proper connection to MySQL was made using user as ‘wp_user’ and password as <your password> The my_db database is great. Host information: 127.0.0.1 via TCP\/IP hash = f133bb3bf8278ec4eb48ae1fdff56382 time = 1521508293<\/span><\/p>\n
        While we are at it lets finish up the curl component.  <\/span>Curl is installed, but since it operates in a chroot it needs a few pieces moved into \/var\/www in order to be able to grab external url\u2019s. Those pieces are the following:<\/span><\/p>\n
        \/etc\/resolv.conf<\/span><\/p>\n
        \/etc\/services<\/span><\/p>\n
        \/etc\/ssl<\/span><\/p>\n
        Remember we are in a chroot environment and the root for that environment is \/var\/www.  <\/span>We need to keep the same directory relationship here so we need to add \/var\/www to each destination. We will do that using mkdir and cp:<\/span><\/p>\n
        mkdir -p \/var\/www\/etc\nmkdir -p \/var\/www\/etc\/ssl\/lib\nmkdir -p \/var\/www\/etc\/ssl\/private\ncp \/etc\/resolv.conf \/var\/www\/etc\/resolv.conf\ncp \/etc\/services \/var\/www\/etc\/services\ncp -R \/etc\/ssl \/var\/www\/etc\/<\/pre>\n
        Now we can test curl.  <\/span>In your VPS window start up you httpd daemon.  <\/span><\/span><\/p>\n
        httpd -d -vv -f \/etc\/httpd_config.test<\/span><\/p>\n
        In Your Maintenance window restart php.<\/span><\/p>\n
        rcctl restart php70_fpm<\/span><\/p>\n
        Now in you web browser enter the following URL http:\/\/\u201cyour<\/span><\/a> IP address\u201d\/test_curl.php.  <\/span>You should see something like the following:<\/span><\/p>\n
        string(130999) “HTTP\/1.1 200 OK Server: nginx Date: Tue, 20 Mar 2018 18:32:01 GMT Content-Type: text\/html Content-Length: 130714 Connection: keep-alive Vary: Accept-Encoding Last-Modified: Tue, 20 Mar 2018 18:30:27 GMT X-Frame-Options: SAMEORIGIN X-nc: EXPIRED ord 1 Accept-Ranges: bytes<\/span><\/p>\n
        And the web page for WordPress.org<\/span><\/a> should display blow that.<\/span><\/p>\n
        Ok lets shut down our services to secure our server until next time:<\/p>\n
        ctrl+c in your VPS window to stop our debug httpd.\nrcctl stop php70_fpm\nrcctl stop mysql<\/pre>\n
        Not bad! <\/span><\/span> Lets summarize our <\/span>achievements today…<\/span><\/span><\/p>\n
          \n
        1. We created test scripts to verify php-curl and MariaDB functionality through php and httpd<\/li>\n
        2. We installed MariaDB, and setup a user for WordPress to use with the appropriate permissions and socket access through php<\/li>\n
        3. Finished setting up the curl component for php<\/li>\n
        4. And verified base MariaDB connectivity and curl functionality with our scripts.<\/li>\n<\/ol>\n
          Another good days work.  <\/span>Stay tuned, much more to come.<\/p>\n
          \n","protected":false},"excerpt":{"rendered":"
          The Bitter DB … Welcome back to installing WordPress 4.9.4 on OpenBSD 6.2.  In this installment we are going to install MariaDB for WordPress to use as a data store.  To be successful we will need to configure our Mariadb web access user to run as a socket in our chrooted environment, and secure the … <\/p>\n
          Continue reading “Setting up MariaDB for WordPress 4.9.4 on OpenBSD 6.2 part3”<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[2,3],"tags":[],"class_list":["post-217","post","type-post","status-publish","format-standard","hentry","category-openbsd","category-wordpress"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9IcIr-3v","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=\/wp\/v2\/posts\/217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=217"}],"version-history":[{"count":10,"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions"}],"predecessor-version":[{"id":262,"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions\/262"}],"wp:attachment":[{"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bigwisedata.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}