Unfortunately, nothing in the logs. I can access mysql using the same credentials with mysql -uhass homeassistant -p
Changing the db_url to
db_url: mysql://hass:[email protected]/homeassistant?charset=utf8
worked for me.
1 Like
Alright Christian, the influxdb/grafana walkthrough for you. This is what I did:
This may not be necessary, but just in case:
sudo apt-get install apt-transport-https curl
This adds the grafana sources and installs Grafana:
curl https://bintray.com/user/downloadSubjectPublicKey?username=bintray | sudo apt-key add -
echo "deb https://dl.bintray.com/fg2it/deb jessie main" | sudo tee -a /etc/apt/sources.list.d/grafana.list
sudo apt-get update
sudo apt-get install grafana
Now add influx sources and install Influx:
curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add -
source /etc/os-release
test $VERSION_ID = "8" && echo "deb https://repos.influxdata.com/debian jessie stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
sudo apt-get update && sudo apt-get install influxdb
sudo systemctl start influxdb
Open the command line interface of influx:
influx
The command line interface is similar to mysql, but there are major differences, one of which is you don’t need semi-colons to put commands in. Create the database and user (there are no user hosts in influx):
CREATE DATABASE homeassistant
CREATE USER "hass" WITH PASSWORD '********'
exit
Edit the configuration.yaml:
influxdb:
host: 127.0.0.1
database: homeassistant
username: hass
password: ********
Restart home assistant:
sudo systemctl restart [email protected]
Check that data is flowing into influx:
influx
Influx has series and measurements instead of tables. First enter a database with the use command. Then ask influx to show a list of all the series and measurements first, and then ask it to show the first 100 entries from the lux measurement:
use homeassistant
show series
show measurements
SELECT * FROM lux LIMIT 100
exit
Now start grafana:
sudo /bin/systemctl start grafana-server
sudo systemctl status grafana-server
Navigate to:
http://hassbian.local:3000/
Login as admin:admin (Default username and password of Grafana)
Add Data source
Name: homeassistant
Type: InfluxDB
URL: http://127.0.0.1:8086
Access: proxy
INFLUXDB DETAILS
Database: homeassistant
User: hass
Password: ********
Default Group by time: >30
Ok, then you need to add dashboards and graphs, etc.
Now to set it up so that influxdb and grafana also start at boot:
sudo systemctl enable grafana-server.service
sudo systemctl enable influxdb.service
At this point I had a serious problem. When I rebooted, Home Assistant was saying that InfluxDB config failed. InfluxDB hadn’t finished loading yet when Home Assistant tried to connect to it. I did the following to get Home Assistant to wait for InfluxDB:
sudo nano -w /etc/systemd/system/[email protected]
replace this:
After=network.target
With this:
Requisite=influxdb.service
After=influxdb.service grafana-server.service network.target
And replace:
Type=simple
With:
Type=idle
Then reload the systemctl daemon
sudo systemctl --system daemon-reload
Reboot to check. Still didn’t work. On my RPi3, it takes influx about 20 seconds to get ready for receiving data. Since Home Assistant starts much faster, it can’t find influxDB when it tries to connect, and then doesn’t try to connect to influxDB again. Therefore Home Assistant needs a 20 second delay with respect to InfluxDB to get them both starting correctly. I set a boot timer, since I couldn’t get a relative timer to work, and the total time from boot until Influx was ready was 40-45 seconds, I used 50 just in case.
create new file
cd /etc/systemd/system/
sudo nano hawait.timer
File contents:
[Unit]
Description=Runs homeassistant slightly after boot to give influxDB time to start
[Timer]
OnBootSec=50
[email protected]
[Install]
WantedBy=multi-user.target
Close and save:
ctrl+x, y, enter
Disable regular home assistant autoboot, and enable the new autoboot timer:
sudo systemctl disable [email protected]
sudo systemctl enable hawait.timer
sudo systemctl --system daemon-reload
After all this it works!
12 Likes
my database (hosted on a synology nas) keeps growing I don’t why
because in the configuration file everything looks working
recorder:
purge_days: 7
db_url: mysql://home_assistant:[email protected]/home_assistant
any idea? what is the best method to purge the database … can I do this manually how?
TIA
For me, I have the opposite effect. The size of mysql database hasn’t changed since I did the install, still has the same size and same date/time as when I created it. I am not getting any errors in my logs so I am assuming that my recorder: is setup properly. Also the strange thing is that my home-assistant_v2.db hasn’t grown either and has the same time time as mysql database. So seems like it stopped recording in the sqlite db, but I it doesn’t seem to be logging any information in mysql. Would anyone have any ideas?
Did you delete or rename the existing sqlite db file (home-assistant_v2.db) before you cut over from sqlite to mysql? I don’t know about mysql, but postgresql creates the db file and populates it on its own when you restart, and it uses the same file name.
No I haven’t done that. I wasn’t sure if it was needed or not. I’ll give it a shot this evening. Thanks for your reply!
When I moved from the embedded sqlite to postgresql, I renamed the db file before I restarted, that way I could revert back in case I was wrong. Since I am running on a pi with Rasbian, I just used the mv command (mv ./home-assistant_v2.db home-assistant_v2.old-db)
I am also seeing that the DB does not purge over time… I’m using mysql, and when it’s new it’s fast and awesome, but after a week or so…
recorder:
purge_days: 2
db_url: mysql://hassuser:*redacted*@127.0.0.1/hass_db?charset=utf8
DB is Huge in MYSQL:
+--------------------+---------------+
| hass_db | size in MB |
+--------------------+---------------+
| hass_db | 4660.00000000 |
| information_schema | 0.15625000 |
| mysql | 2.42501831 |
| performance_schema | 0.00000000 |
| sys | 0.01562500 |
+--------------------+---------------+
5 rows in set (0.04 sec)
Anyone know why the purge function is not working?
I believe it was working in postgresql, and it appears to be working on sqlite. I am set to a 3 day purge and sqlite is only 13Mb. I’ll check back and see where it is tomorrow.
Purging is not immediate but after few days is working… I don’t know why and how it works, but would be a nice feature if you could du a surge command from the configuration side!
1 Like
Purge is fixed in 0.40. Please note that purge has a timedelta of 2 days, meaning it will delete every two days (and the clock resets every time you restart HA).
Do you have instructions for Synology NAS (in a Docker container) by any chance? I am hesitating to run influxDB/Grafana on Pi to minimize the overhead.
So my db will never get purged. 
2 Likes
Well today I learned purge is useless to me lol
docker run --net=host --restart=always --name mysql -d \
-e 'DB_USER=hass' -e 'DB_PASS=pass' -e 'DB_NAME=homeassistant' \
sameersbn/mysql:latest
Thanks for the commands.
I ran these commands.
How do I run MySQL queries inside docker container? Do I have to stop HomeAssistant?
pi@rpi3:~ $ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
2e30442c73e3 sameersbn/mysql:latest "/sbin/entrypoint...." 5 hours ago Restarting (1) 2 hours ago mysql
Just curious about Docker container.
Depends if you have a MySQL client installed on your host machine.
Assuming you don’t, you can install something like PhpMyAdmin if you want a user interface for it. You can probably use
docker run --name myadmin -d -p 8080:80 phpmyadmin/phpmyadmin
I haven’t used that myself, so you might need to tinker with the command to point phpmyadmin to your MySQL server.
Otherwise, you can execute commands from the command line. First you’ll need to get access to the dockers command line
docker exec -it mysql /bin/bash
Now you’re inside the docker container, let’s access the MySQL client by command line
mysql -u hass -p
Enter your password, then switch to your home assistant database
use homeassistant
Ok. I have done a lot of read and setup mysql on my system. I have set the purge of the data base for two days but it don’t seem to be working. Noticed that my disk usage keeps climbing every day.
I have tried to find the data base on my system but cant seem to locate where it is. Is there a way to manually purge it to see if that is whats eating up my disk?
Hi there, i made a little script to manually purge my HA MySQL DB.
#!/bin/bash
HOST='mysql_server'
DB='HA_DB'
USER='username'
PW='password'
# Delete older than 7 days
BEFORE=$(date --date="7 days ago" +%Y-%m-%d)" 00:00:00"
query="DELETE FROM states WHERE created < '$BEFORE'"
echo "$query" | mysql -h$HOST -D$DB -u$USER -p$PW
query="DELETE FROM events WHERE created < '$BEFORE'"
echo "$query" | mysql -h$HOST -D$DB -u$USER -p$PW
Use at your own risk. 
3 Likes
Purges run - as @arsaboo said - every 48 hours or so. I say “or so” because in testing this I discovered it to actually happen somewhere towards the end of hour 48 and hour 49. This is going to change at some point soon according to this discussion:
https://github.com/home-assistant/home-assistant/issues/6302
But there’s no PR associated so I can’t tell you when this will happen.
If you’re using MySQL and still running low on space, even after purges run, it might be due to the size of your ibdata1 file not resizing after the purge. The file /var/lib/mysql/ibdata1 is the system tablespace for the InnoDB infrastructure. If you’re using InnoDB as your engine, this is default behavior - annoying as it is - to store everything on one file. But it can be fixed.
The only way to shrink this is to dump the tables into a backup, shutdown MySQL, delete the ibdata1 file and then restore the backups. Before doing so, I would advise reading this article as it lays out not only the steps to do this but why this is the default behavior in the first place and how to fix it.
I’ve been in tuning mode lately and this is something I plan on trying soon although I am not in a hurry as I have a lot of empty space still on my Pi. But it may be another consideration for me moving to influx depending on how it handles this type of thing.
Hope this helps!