Recently I decided to mess around with my Home Assistant setup and accidentally borked my MySQL installation on my Raspberry Pi 3. Getting it up and running again was a bit of a pain, so I decided to write a post on the exact steps I took in the hopes that someone will find it useful.
By default, the database created by Home Assistant is an SQLite database. For most people this is probably ok, but my Home Assistant configuration began to grow and I found that the SQLite database just wasn’t cutting it (too slow, for starters). So I opted for MySQL.
Deleting an old MySQL installation
If you’re starting fresh on a new Pi, you can skip these steps. However, if you’re like me and messed up your MySQL installation, here’s how you can fix it (note, this will delete any old databases, so use this method with caution).
First, let’s make sure Home Assistant isn’t running and stop any mysql processes:
$ sudo systemctl stop home-assistant.service $ sudo service mysql stop $ sudo killall -9 mysql $ sudo killall -9 mysqld
Now, we need to completely remove mysql via the following steps. First, run
sudo dpkg --purge mysql and hit tab; there should be multiple entries. Run that command for each
mysql-* entry (I ran into a few that errored and was able to safely skip them).
$ sudo apt-get remove --purge mysql-server mysql-client mysql-common $ sudo apt-get autoremove $ sudo apt-get autoclean $ sudo deluser mysql
I also found that I needed to get rid of the following directories which contain the database, so use with caution:
$ sudo rm -rf /var/lib/mysql $ sudo rm -rf /etc/mysql
Now, let’s make sure it’s really dead by running
which mysql. It should return nothing if the removal was successful. The last command we need to run is:
$ sudo dpkg --configure -a
Which will regenerate the dpkg info.
Installing MySQL on Raspberry Pi
These instructions were originally found here. I’m reproducing them for the sake of completeness.
First, let’s make sure everything is up to date by running:
$ sudo apt-get update
Now we can install:
$ sudo apt-get install libmysqlclient-dev $ sudo apt-get install python-dev python3-dev
Create MySQL Database for Home Assistant
Now we can create our MySQL database for Home Assistant. Open mysql via the
mysql -u root -p command. Then run the following commands within mysql:
mysql> CREATE DATABASE hass_db; mysql> CREATE USER 'hassuser'@'localhost' IDENTIFIED BY '<YOURPASSWORD>'; mysql> GRANT ALL PRIVILEGES ON hass_db.* TO 'hassuser'@'localhost'; mysql> FLUSH PRIVILEGES; mysql> quit
Next, assuming you’re using a virtual-env, we need to switch to our venv and make sure mysqlclient is up-to-date:
$ sudo su -s /bin/bash hass $ source /srv/hass/hass_venv/bin/activate $ pip3 install --upgrade mysqlclient $ deactivate
Hit Ctrl+d to return to your default user.
Change Home Assistant Recorder to use MySQL
The last step is to add the
recorder component into our
configuration.yaml file so that Home Assistant knows it should be using the MySQL database instead of SQLite:
recorder: db_url: mysql://hassuser:<YOURPASSWORD>@localhost/hass_db
And now it’s safe to restart Home Assistant via
sudo systemctl restart home-assistant.service
And that’s it! That’s how you can use MySQL with Home Assistant and fix any mishaps that might happen with your installation if you start wildly screwing around with it.