{"id":557,"date":"2020-02-10T01:56:11","date_gmt":"2020-02-10T00:56:11","guid":{"rendered":"https:\/\/codedoneright.eu\/?page_id=557"},"modified":"2020-03-10T17:52:11","modified_gmt":"2020-03-10T16:52:11","slug":"php-sql","status":"publish","type":"page","link":"https:\/\/codedoneright.eu\/?page_id=557","title":{"rendered":"MySQL"},"content":{"rendered":"\n
\"MySQL
MariaDB logo<\/a><\/figcaption><\/figure><\/div>\n\n\n\n

PHP and SQL are vital to any modern website. They are not services themselves, but many services rely on them.<\/p>\n\n\n\n

SQL is a programming language that relies on databases. Think of databases as a gigantic Excel sheet. All posts, pages and databases on this page make use of SQL. MariaDB is how we are going to implement that on our server.<\/p>\n\n\n\n

You also need to know that there is a necessity for a parser. A package that will allow PHP and SQL to talk to each other. Worry not, I will tell you exactly what you need to do in order for all this to work.<\/p>\n\n\n\n

<\/div>\n\n\n\n

SQL with MariaDB<\/h2>\n\n\n\n

SQL is an enormous topic, too big to dive into in such a small how-to guide. Right now the only thing you need to know is how to install SQL server, how to create database user and how to create a database.<\/p>\n\n\n\n

Rule of thumb is \u2013 create a separate user and database for every service that you use. If you have WordPress and Nextcloud on your server, make sure that they are using separate users and databases. In short, one service \u2013 one user, one database.<\/p>\n\n\n\n

Installing SQL server<\/h4>\n\n\n\n

As usual, installation is not that hard. We will utilize MariaDB server, an open source successor of MySQL. <\/p>\n\n\n\n

Run the following code<\/p>\n\n\n\n

sudo apt install mariadb-server mariadb-common php-mysql<\/code><\/pre>\n\n\n\n
Initial MySQL secure installation<\/h4>\n\n\n\n
Before we start using MySQL we need to secure it properly. Set up a root <\/em>password for MySQL. Note that this is MySQL root<\/em>, and not root system user<\/em>.<\/p>\n\n\n\n
Run the following code<\/p>\n\n\n\n
sudo mysql_secure_installation<\/code><\/pre>\n\n\n\n
Initially MySQL root user<\/em> has no password. Just press Enter and when asked, set up a new password. Answer with y<\/strong><\/em> to all other prompts. Basically remove everything, disallow remote root login and reload privileges. You will be left with a clean install secured with a new root <\/em>password.<\/p>\n\n\n\n
Logging into MySQL<\/h4>\n\n\n\n
In order to create users and databases you need to log in to MySQL with MySQL root user<\/em>. To do so, run the following code<\/p>\n\n\n\n
sudo mysql -uroot -p<\/code><\/pre>\n\n\n\n
Now, type in the password that you have set for the root <\/em>user.  Note that your terminal changed. It is no longer the default Bash shell. You are logged into MariaDB server.<\/p>\n\n\n\n
In order to log out of MySQL and go back to your terminal just press<\/p>\n\n\n\n
CTRL+D<\/code><\/pre>\n\n\n\n
Creating SQL data for our services<\/h4>\n\n\n\n
As mentioned before, each service should have the following, separate, set of data to use<\/p>\n\n\n\n
  • User<\/li>
  • Database<\/li><\/ul>\n\n\n\n
    This is fairly simple and after logging in just put the following in MySQL terminal<\/p>\n\n\n\n
    CREATE DATABASE $DATABASE_NAME;\nGRANT ALL PRIVILEGES ON $DATABASE_NAME.* TO '$USER'@'localhost' IDENTIFIED BY '$USER_PASSWORD';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n
    CAUTION <\/strong>do not remove semicolons (;<\/em><\/strong>) from the end of lines. They are required syntax elements that tell SQL that an instruction has ended.<\/p>\n\n\n\n
    Substitute <\/p>\n\n\n\n
    • $DATABASE_NAME<\/em> \u2013 with database name for your service (e.g. wordpress_database<\/em>)<\/li>
    • $USER<\/em> \u2013 with a new username that will be used for handling this database<\/li>
    • $USER_PASSWORD<\/em> \u2013 with a password for our new user<\/li><\/ul>\n\n\n\n
      CAUTION<\/strong> GRANT ALL PRIVILEGES… <\/em>line will create a new user if you are specifying a non-existent user.<\/p>\n\n\n\n
      Now you are basically done. You have set up a database and a user that can be utilized with one of our services like WordPress or Nextcloud. Just remember about setting separate sets for different services!<\/p>\n\n\n\n
      Remember about naming users and databases in a way that will allow for easy identification. You will not remember what service uses a database named db_1<\/em> but wordpress_mywebsite<\/em> will be easily identifiable.<\/p>\n\n\n\n
      Show MySQL data<\/h4>\n\n\n\n
      NOTE<\/strong> you have to be logged to MariaDB in order to use the following commands.<\/p>\n\n\n\n
      To list all users registred in your MySQL use the following command<\/p>\n\n\n\n
      SELECT User FROM mysql.user;<\/code><\/pre>\n\n\n\n
      To show all databases in your system<\/p>\n\n\n\n
      show databases;<\/code><\/pre>\n\n\n\n
      If you want to show grants just for a particular user, use the following pattern<\/p>\n\n\n\n
      show grants for '$USER'@'localhost';<\/code><\/pre>\n\n\n\n
      To show users and databases they are allowed to use<\/p>\n\n\n\n
      SELECT User, Db, Host from mysql.db;<\/code><\/pre>\n\n\n\n
      Deleting a user and a database<\/h4>\n\n\n\n
      If you decide that you want to remove a user or a database log into MySQL server as root user<\/em> and run one of the following commands<\/p>\n\n\n\n
      DROP DATABASE $DATABASE_NAME;\nDROP USER '$USER_NAME'@'localhost';<\/code><\/pre>\n\n\n\n
      Before logging out reload privileges with the following<\/p>\n\n\n\n
      FLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n
      And you are done<\/p>\n\n\n\n
      <\/div>\n\n\n\n
      Conclusion<\/h3>\n\n\n\n
      PHP and SQL are a necessity, but you do not really have to know that much about them. If you know how to create users and databases in SQL, and search for missing PHP modules you will do just fine.<\/p>\n","protected":false},"excerpt":{"rendered":"
      PHP and SQL are vital to any modern website. They are not services themselves, but many services rely on them. SQL is a programming language that relies on databases. Think of databases as a gigantic Excel sheet. All posts, pages… <\/p>\n","protected":false},"author":1,"featured_media":0,"parent":483,"menu_order":15,"comment_status":"open","ping_status":"closed","template":"","meta":{"footnotes":""},"yoast_head":"\nMySQL the database your server needs — Code Done Right!<\/title>\n<meta name=\"description\" content=\"Learn how to quickly implement SQL on your server, create databases and users with this short and newbie friendly tutorial!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/codedoneright.eu\/?page_id=557\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"MySQL the database your server needs — Code Done Right!\" \/>\n<meta name=\"twitter:description\" content=\"Learn how to quickly implement SQL on your server, create databases and users with this short and newbie friendly tutorial!\" \/>\n<meta name=\"twitter:image\" content=\"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/codedoneright.eu\/?page_id=557\",\"url\":\"https:\/\/codedoneright.eu\/?page_id=557\",\"name\":\"MySQL the database your server needs — Code Done Right!\",\"isPartOf\":{\"@id\":\"https:\/\/codedoneright.eu\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/codedoneright.eu\/?page_id=557#primaryimage\"},\"image\":{\"@id\":\"https:\/\/codedoneright.eu\/?page_id=557#primaryimage\"},\"thumbnailUrl\":\"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg\",\"datePublished\":\"2020-02-10T00:56:11+00:00\",\"dateModified\":\"2020-03-10T16:52:11+00:00\",\"description\":\"Learn how to quickly implement SQL on your server, create databases and users with this short and newbie friendly tutorial!\",\"breadcrumb\":{\"@id\":\"https:\/\/codedoneright.eu\/?page_id=557#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/codedoneright.eu\/?page_id=557\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/codedoneright.eu\/?page_id=557#primaryimage\",\"url\":\"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg\",\"contentUrl\":\"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/codedoneright.eu\/?page_id=557#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/codedoneright.eu\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Server features\",\"item\":\"https:\/\/codedoneright.eu\/?page_id=483\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/codedoneright.eu\/#website\",\"url\":\"https:\/\/codedoneright.eu\/\",\"name\":\"Code Done Right!\",\"description\":\"Raspberry Pi server guides\",\"publisher\":{\"@id\":\"https:\/\/codedoneright.eu\/#\/schema\/person\/50378701e349dbd5d40888bc5b532568\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/codedoneright.eu\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/codedoneright.eu\/#\/schema\/person\/50378701e349dbd5d40888bc5b532568\",\"name\":\"CodeDoneRight\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/codedoneright.eu\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/codedoneright.eu\/wp-content\/uploads\/www_icon.png\",\"contentUrl\":\"https:\/\/codedoneright.eu\/wp-content\/uploads\/www_icon.png\",\"width\":120,\"height\":120,\"caption\":\"CodeDoneRight\"},\"logo\":{\"@id\":\"https:\/\/codedoneright.eu\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/codedoneright.eu\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL the database your server needs — Code Done Right!","description":"Learn how to quickly implement SQL on your server, create databases and users with this short and newbie friendly tutorial!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/codedoneright.eu\/?page_id=557","twitter_card":"summary_large_image","twitter_title":"MySQL the database your server needs — Code Done Right!","twitter_description":"Learn how to quickly implement SQL on your server, create databases and users with this short and newbie friendly tutorial!","twitter_image":"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/codedoneright.eu\/?page_id=557","url":"https:\/\/codedoneright.eu\/?page_id=557","name":"MySQL the database your server needs — Code Done Right!","isPartOf":{"@id":"https:\/\/codedoneright.eu\/#website"},"primaryImageOfPage":{"@id":"https:\/\/codedoneright.eu\/?page_id=557#primaryimage"},"image":{"@id":"https:\/\/codedoneright.eu\/?page_id=557#primaryimage"},"thumbnailUrl":"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg","datePublished":"2020-02-10T00:56:11+00:00","dateModified":"2020-03-10T16:52:11+00:00","description":"Learn how to quickly implement SQL on your server, create databases and users with this short and newbie friendly tutorial!","breadcrumb":{"@id":"https:\/\/codedoneright.eu\/?page_id=557#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/codedoneright.eu\/?page_id=557"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/codedoneright.eu\/?page_id=557#primaryimage","url":"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg","contentUrl":"https:\/\/mariadb.org\/wp-content\/themes\/twentynineteen-child\/icons\/mariadb_org_rgb_h.svg"},{"@type":"BreadcrumbList","@id":"https:\/\/codedoneright.eu\/?page_id=557#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/codedoneright.eu\/"},{"@type":"ListItem","position":2,"name":"Server features","item":"https:\/\/codedoneright.eu\/?page_id=483"},{"@type":"ListItem","position":3,"name":"MySQL"}]},{"@type":"WebSite","@id":"https:\/\/codedoneright.eu\/#website","url":"https:\/\/codedoneright.eu\/","name":"Code Done Right!","description":"Raspberry Pi server guides","publisher":{"@id":"https:\/\/codedoneright.eu\/#\/schema\/person\/50378701e349dbd5d40888bc5b532568"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/codedoneright.eu\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/codedoneright.eu\/#\/schema\/person\/50378701e349dbd5d40888bc5b532568","name":"CodeDoneRight","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/codedoneright.eu\/#\/schema\/person\/image\/","url":"https:\/\/codedoneright.eu\/wp-content\/uploads\/www_icon.png","contentUrl":"https:\/\/codedoneright.eu\/wp-content\/uploads\/www_icon.png","width":120,"height":120,"caption":"CodeDoneRight"},"logo":{"@id":"https:\/\/codedoneright.eu\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/codedoneright.eu"]}]}},"_links":{"self":[{"href":"https:\/\/codedoneright.eu\/index.php?rest_route=\/wp\/v2\/pages\/557"}],"collection":[{"href":"https:\/\/codedoneright.eu\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/codedoneright.eu\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/codedoneright.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codedoneright.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=557"}],"version-history":[{"count":25,"href":"https:\/\/codedoneright.eu\/index.php?rest_route=\/wp\/v2\/pages\/557\/revisions"}],"predecessor-version":[{"id":947,"href":"https:\/\/codedoneright.eu\/index.php?rest_route=\/wp\/v2\/pages\/557\/revisions\/947"}],"up":[{"embeddable":true,"href":"https:\/\/codedoneright.eu\/index.php?rest_route=\/wp\/v2\/pages\/483"}],"wp:attachment":[{"href":"https:\/\/codedoneright.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=557"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}