{"id":414,"date":"2021-07-04T19:24:37","date_gmt":"2021-07-04T19:24:37","guid":{"rendered":"https:\/\/andrejacobs.org\/?p=414"},"modified":"2022-04-11T20:22:59","modified_gmt":"2022-04-11T20:22:59","slug":"convert-home-assistant-to-use-mariadb-as-the-database","status":"publish","type":"post","link":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/","title":{"rendered":"Convert Home Assistant to use MariaDB as the database"},"content":{"rendered":"\n<p class=\"has-small-font-size\">Photo by <a href=\"https:\/\/unsplash.com\/@markuswinkler?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText\">Markus Winkler<\/a> on <a href=\"https:\/\/unsplash.com\/s\/photos\/graph?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText\">Unsplash<\/a><\/p>\n\n\n\n<div class=\"wp-block-jetpack-markdown\"><h2>Overview<\/h2>\n<p>Currently my Home Assistant is using the default SQLite database for recording sensor data but I would like to use MariaDB instead. This is part of my <a href=\"https:\/\/github.com\/andrejacobs\/100DayChallenges\">Day 14 of 100 Days of STEM<\/a>.<\/p>\n<p><strong>NOTE:<\/strong> My Home Assistant setup is running on a Raspberry Pi 3 using a SSD drive, so using MariaDB will be fine in my case.<\/p>\n<p><strong>VERY IMPORTANT: You will lose all the previous recorded sensor data if you switch databases.<\/strong><\/p>\n<h2>Objectives<\/h2>\n<p>My overall objectives to achieve over the next couple of days are:<\/p>\n<ul>\n<li>[&#x2705;] Convert Home Assistant to be using MariaDB as the database<\/li>\n<li>[ ] Backup database to Amazon S3<\/li>\n<li>[ ] Backup database to local Linux server<\/li>\n<li>[ ] Monitor and notify of errors<\/li>\n<\/ul>\n<p>By default Home Assistant uses a SQLite database for storing the recorded sensor data. The database is located at <code>\/config\/home-assistant_v2.db<\/code><\/p>\n<h2>Initial steps<\/h2>\n<ul>\n<li>First backup your Home Assistant installation.<\/li>\n<li>Navigate to <a href=\"http:\/\/homeassistant.local:8123\/hassio\/snapshots\">Snapshots<\/a>. Supervisor \u2192 Snapshots.<\/li>\n<li>Create a Full Snapshot and make sure you download a copy locally. You can do this by selecting the snapshot and choosing to download the snapshot.<\/li>\n<li>You can view a copy of the SQLite database by extracting the snapshot file and then extracting the <code>homeassistant.tar.gz<\/code> file. The database file is named <code>home-assistant_v2.db<\/code>.<\/li>\n<li>Apply available updates from the <a href=\"http:\/\/homeassistant.local:8123\/hassio\/dashboard\">Dashboard<\/a>. Supervisor \u2192 Dashboard. The order I chose was Home Assistant Core, Supervisor and then OS. (I did try starting with OS but it did some weird things).<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"https:\/\/andrejacobs.org\/wp-content\/uploads\/2021\/07\/Untitled.png\" alt=\"\"><\/p>\n<ul>\n<li><strong>NOTE:<\/strong> If you are having issues, then there is also the <code>Observer<\/code> that can give clues as to what is happening on Home Assistant. The port is <code>4357<\/code>. <a href=\"http:\/\/homeassistant.local:4357\/\">Local link<\/a>.\nThe UI doesn&#8217;t always indicate what is going on behind the scenes and it is worth always waiting and doing a page refresh. Go grab that tea.<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"https:\/\/andrejacobs.org\/wp-content\/uploads\/2021\/07\/Untitled-1.png\" alt=\"\"><\/p>\n<ul>\n<li>Now is a great time to rename any sensors. In my case my sensors were setup like T1, T2 .. TN (for Temperature etc.) but I want to include the location of the sensor in the name. The reason I chose this initial naming convention comes from the fact that I wanted to label the sensors and don&#8217;t have enough space to stick a big ol label on them. The approach I took was to open the Phoscon portal and rename the sensors there and then rebooted the Host system (i.e. Raspberry Pi).<\/li>\n<li>After applying updates and making changes, it is a good idea to take another full snapshot.<\/li>\n<\/ul>\n<h2>Setting up MariaDB<\/h2>\n<ul>\n<li>Install the official MariaDB add-on. Navigate to Supervisor \u2192 <a href=\"http:\/\/homeassistant.local:8123\/hassio\/store\">Add-on Store<\/a> and search for MariaDB.<\/li>\n<li>Click on Install. Once it has finished then enable the Watch dog setting.<\/li>\n<li>Change the password before starting the add-on. Navigate to the Configuration tab and set a generated password (you are using a password manager right?). Click save.<\/li>\n<li>Start the MariaDB database. Navigate back to the Info tab and click start.<\/li>\n<li>Verify everything is running and that the database has been installed. You can view the information on the Log tab.<\/li>\n<\/ul>\n<h2>Configure Home Assistant to use MariaDB for recording<\/h2>\n<ul>\n<li>You need to specify the database connection string for the <code>recorder<\/code> module.<\/li>\n<li>Edit the file <code>\/config\/configuration.yaml<\/code>. For this I am using the File editor add-on, but you can also use SSH.<\/li>\n<\/ul>\n<pre><code class=\"language-yaml\"># Change the username,password and database name as required\nrecorder:\n  db_url: mysql:\/\/homeassistant:password@core-mariadb\/homeassistant?charset=utf8mb4\n<\/code><\/pre>\n<ul>\n<li>Save and check the configuration. Navigate to Configuration \u2192 Server Controls and click Check Configuration.<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"https:\/\/andrejacobs.org\/wp-content\/uploads\/2021\/07\/Untitled-2.png\" alt=\"\"><\/p>\n<ul>\n<li>On the same page, click the Restart button under Server management.<\/li>\n<li>Once everything is back up and running again you can verify by looking at the History section and will notice that all the sensor data has been reset and is only showing values from a few minutes ago.<\/li>\n<\/ul>\n<h3>Accessing MariaDB remotely<\/h3>\n<ul>\n<li>I want to be able to access the MariaDB from other computers on my network for the time being and have exposed the container port. <strong>NOTE:<\/strong> I recommend that you do not do this since you are exposing the database to anything and anyone on your network.<\/li>\n<li>First I added a new read-only user to MariaDB.<\/li>\n<\/ul>\n<pre><code class=\"language-yaml\">databases:\n  - homeassistant\nlogins:\n  - username: homeassistant\n    password: &lt;&lt;generated&gt;&gt;\n  - username: ha-readonly\n    password: &lt;&lt;generated&gt;&gt;\nrights:\n  - username: homeassistant\n    database: homeassistant\n  - username: ha-readonly\n    database: homeassistant\n    privileges:\n      - SELECT\n<\/code><\/pre>\n<ul>\n<li>Then set the exposed port to be used.<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"https:\/\/andrejacobs.org\/wp-content\/uploads\/2021\/07\/Untitled-3.png\" alt=\"\"><\/p>\n<ul>\n<li>I then used MySQLWorkbench to connect to the MariaDB database and verify that the sensor data is being stored as expected.<\/li>\n<\/ul>\n<h2>Wrapping up for today<\/h2>\n<p>My Home Assistant is now using MariaDB for storing all the sensor data and the next task would be to setup remote backing up to Amazon S3 and my local Linux server.<\/p>\n<h2>References<\/h2>\n<ul>\n<li><a href=\"https:\/\/www.youtube.com\/watch?v=FbFyqQ3He7M\">Home Assistant MariaDB Install and System Monitoring<\/a><\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Photo by Markus Winkler on Unsplash [&#8230;]<\/p>\n<p><a class=\"understrap-read-more-link\" href=\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":417,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[29],"tags":[44,39],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.13 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Convert Home Assistant to use MariaDB as the database - Andr\u00e9 Jacobs<\/title>\n<meta name=\"description\" content=\"Today I will show you how you can convert your Home Assistant setup to be using MariaDB as the database for better performance.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Convert Home Assistant to use MariaDB as the database - Andr\u00e9 Jacobs\" \/>\n<meta property=\"og:description\" content=\"Today I will show you how you can convert your Home Assistant setup to be using MariaDB as the database for better performance.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Andr\u00e9 Jacobs\" \/>\n<meta property=\"article:published_time\" content=\"2021-07-04T19:24:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-11T20:22:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/andrejacobs.org\/wp-content\/uploads\/2021\/07\/markus-winkler-IrRbSND5EUc-unsplash-scaled.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"2560\" \/>\n\t<meta property=\"og:image:height\" content=\"1707\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Andr\u00e9 Jacobs\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@andrejacobs81\" \/>\n<meta name=\"twitter:site\" content=\"@andrejacobs81\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Andr\u00e9 Jacobs\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\"},\"author\":{\"name\":\"Andr\u00e9 Jacobs\",\"@id\":\"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68\"},\"headline\":\"Convert Home Assistant to use MariaDB as the database\",\"datePublished\":\"2021-07-04T19:24:37+00:00\",\"dateModified\":\"2022-04-11T20:22:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\"},\"wordCount\":728,\"publisher\":{\"@id\":\"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68\"},\"keywords\":[\"100DaysOfSTEM\",\"MariaDB\"],\"articleSection\":[\"Home Automation\"],\"inLanguage\":\"en-GB\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\",\"url\":\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\",\"name\":\"Convert Home Assistant to use MariaDB as the database - Andr\u00e9 Jacobs\",\"isPartOf\":{\"@id\":\"https:\/\/andrejacobs.org\/#website\"},\"datePublished\":\"2021-07-04T19:24:37+00:00\",\"dateModified\":\"2022-04-11T20:22:59+00:00\",\"description\":\"Today I will show you how you can convert your Home Assistant setup to be using MariaDB as the database for better performance.\",\"breadcrumb\":{\"@id\":\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/andrejacobs.org\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Convert Home Assistant to use MariaDB as the database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/andrejacobs.org\/#website\",\"url\":\"https:\/\/andrejacobs.org\/\",\"name\":\"Andr\u00e9 Jacobs\",\"description\":\"iOS Development, Electronics, Robotics, Artificial Intelligence, Business and Health\",\"publisher\":{\"@id\":\"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/andrejacobs.org\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68\",\"name\":\"Andr\u00e9 Jacobs\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/andrejacobs.org\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/andrejacobs.org\/wp-content\/uploads\/2019\/03\/andre_jacobs.jpg\",\"contentUrl\":\"https:\/\/andrejacobs.org\/wp-content\/uploads\/2019\/03\/andre_jacobs.jpg\",\"width\":450,\"height\":450,\"caption\":\"Andr\u00e9 Jacobs\"},\"logo\":{\"@id\":\"https:\/\/andrejacobs.org\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/andrejacobs.org\",\"https:\/\/www.youtube.com\/channel\/UCzqXvnd_UJ3sAzQkQBD-IVw\"],\"url\":\"https:\/\/andrejacobs.org\/author\/andre\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Convert Home Assistant to use MariaDB as the database - Andr\u00e9 Jacobs","description":"Today I will show you how you can convert your Home Assistant setup to be using MariaDB as the database for better performance.","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:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/","og_locale":"en_GB","og_type":"article","og_title":"Convert Home Assistant to use MariaDB as the database - Andr\u00e9 Jacobs","og_description":"Today I will show you how you can convert your Home Assistant setup to be using MariaDB as the database for better performance.","og_url":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/","og_site_name":"Andr\u00e9 Jacobs","article_published_time":"2021-07-04T19:24:37+00:00","article_modified_time":"2022-04-11T20:22:59+00:00","og_image":[{"width":2560,"height":1707,"url":"https:\/\/andrejacobs.org\/wp-content\/uploads\/2021\/07\/markus-winkler-IrRbSND5EUc-unsplash-scaled.jpg","type":"image\/jpeg"}],"author":"Andr\u00e9 Jacobs","twitter_card":"summary_large_image","twitter_creator":"@andrejacobs81","twitter_site":"@andrejacobs81","twitter_misc":{"Written by":"Andr\u00e9 Jacobs","Estimated reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/#article","isPartOf":{"@id":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/"},"author":{"name":"Andr\u00e9 Jacobs","@id":"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68"},"headline":"Convert Home Assistant to use MariaDB as the database","datePublished":"2021-07-04T19:24:37+00:00","dateModified":"2022-04-11T20:22:59+00:00","mainEntityOfPage":{"@id":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/"},"wordCount":728,"publisher":{"@id":"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68"},"keywords":["100DaysOfSTEM","MariaDB"],"articleSection":["Home Automation"],"inLanguage":"en-GB"},{"@type":"WebPage","@id":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/","url":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/","name":"Convert Home Assistant to use MariaDB as the database - Andr\u00e9 Jacobs","isPartOf":{"@id":"https:\/\/andrejacobs.org\/#website"},"datePublished":"2021-07-04T19:24:37+00:00","dateModified":"2022-04-11T20:22:59+00:00","description":"Today I will show you how you can convert your Home Assistant setup to be using MariaDB as the database for better performance.","breadcrumb":{"@id":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/andrejacobs.org\/home-automation\/convert-home-assistant-to-use-mariadb-as-the-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/andrejacobs.org\/"},{"@type":"ListItem","position":2,"name":"Convert Home Assistant to use MariaDB as the database"}]},{"@type":"WebSite","@id":"https:\/\/andrejacobs.org\/#website","url":"https:\/\/andrejacobs.org\/","name":"Andr\u00e9 Jacobs","description":"iOS Development, Electronics, Robotics, Artificial Intelligence, Business and Health","publisher":{"@id":"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/andrejacobs.org\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/andrejacobs.org\/#\/schema\/person\/3d38360883015e883c80c2fb875c5a68","name":"Andr\u00e9 Jacobs","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/andrejacobs.org\/#\/schema\/person\/image\/","url":"https:\/\/andrejacobs.org\/wp-content\/uploads\/2019\/03\/andre_jacobs.jpg","contentUrl":"https:\/\/andrejacobs.org\/wp-content\/uploads\/2019\/03\/andre_jacobs.jpg","width":450,"height":450,"caption":"Andr\u00e9 Jacobs"},"logo":{"@id":"https:\/\/andrejacobs.org\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/andrejacobs.org","https:\/\/www.youtube.com\/channel\/UCzqXvnd_UJ3sAzQkQBD-IVw"],"url":"https:\/\/andrejacobs.org\/author\/andre\/"}]}},"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/andrejacobs.org\/wp-content\/uploads\/2021\/07\/markus-winkler-IrRbSND5EUc-unsplash-scaled.jpg","_links":{"self":[{"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/posts\/414"}],"collection":[{"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/comments?post=414"}],"version-history":[{"count":3,"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/posts\/414\/revisions"}],"predecessor-version":[{"id":418,"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/posts\/414\/revisions\/418"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/media\/417"}],"wp:attachment":[{"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/media?parent=414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/categories?post=414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andrejacobs.org\/wp-json\/wp\/v2\/tags?post=414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}