Setting up MySQL on a (Synology) NAS Docker container

Share your Projects!
1

I just started with Docker on my Synology NAS and quickly realized the value of Docker. After lot of failed attempts, I was finally able to setup MySQL in a Docker container on my NAS (thanks to @gbboy). Here are the steps that I followed (I am sure these steps should work on other hardware, but YMMV):

  1. SSH into Synology NAS and pull the image using docker pull sameersbn/mysql:latest. After you login as admin, you may have to run sudo su if you keep getting permission errors.
  2. Make sure that the folder /volume1/docker/mysql/data exists (create one, if it does not).
  3. I am using sameersbn/mysql image. Run, the command and make sure that the container is up and running.
docker run --net=host --restart=always --name mysql -d -v /volume1/docker/mysql/data:/var/lib/mysql -e 'DB_USER=hass' -e 'DB_PASS=12345' -e 'DB_NAME=homeassistant' sameersbn/mysql:latest
  1. Add the following to the config (where 192.168.2.113 is the IP address of my NAS):
recorder:
  db_url: mysql://hass:[email protected]/homeassistant?charset=utf8
  1. Restart Home Assistant and profit :slight_smile:

I have some useful mysql commands in my HA cheatsheet that you may find useful.

6 Likes
2

Thank you for sharing.
I just setup on my Synology DS1515+ and HASS is now feeding database to it.

4

Got the example HASS analysis working on my db using MySQL, trick is to modify the connection URL:

DB_URL = "mysql+pymysql://hass:[email protected]/homeassistant?charset=utf8"

http://nbviewer.jupyter.org/github/robmarkcole/Useful-python/blob/master/SQL/SQLAlchemy%20HASS%204-11-2017.ipynb

image
image1888×1176 145 KB

5

Hello,

I have a Synology NAS, and HA is working using the home assistant container in docker.

Please can you tell me the advantages of using an external mysql for data and not the one that HA has integrated?

thanks

6

The default database is SQLite. I may be wrong, but I believe you need to be on the same machine to read from the .db. However MySQL runs its own server which is then accessible from other machines. In my case, the MySQL server is running on my NAS and I am performing data analysis on my MacBook.
Cheers

From here:

SQLite is a great database for many situations, but there are times when it’s not quite up to the job. SQLite can be used in web sites, but it’s much more common to use MySQL. This is because

  • MySQL is more scalable,
  • MySQL can be tuned more easily,
  • it supports user management and permissions,
  • MySQL is better for sites with heavy traffic,
  • it can be used in client server architectures where a database client must access a database remotely.
7

What are benefits of running the database as a container versus running it via dsm? My SQL is really rusty and I’ve been having all kinds of trouble trying to run phpmyadmin as a container because it keeps trying to bind to port 80. I don’t think I’ll have these issues if I try running MySQL/PHPAdmin via DSM.

8

Thanks for spelling this out. Was experiencing issues with the native recorder performance in Docker on Synology, so followed this guide to spin up a MySQL container. It connected immediately upon reboot and performance was significantly better.

However, it appears to only work for me for a few moments and then the graphs go static and I start seeing various errors in my logs. I’m researching what I can, but don’t know enough about MySQL yet to know how to fix. Any ideas what’s going on with my instance? It basically makes the logbook useless as it stops updating after a period of time and the graphs become static.

Error executing query: (_mysql_exceptions.DataError) (1406, “Data too long for column ‘event_data’ at row 1”) [SQL: ‘INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (%s, %s, %s, %s, %s, %s, %s)’] [parameters: (‘call_service’, ‘{“domain”: “system_log”, “service”: “write”, “service_data”: {“logger”: “frontend.js.es5.201812112”, “message”: “data:text/javascript;charset=utf-8,% … (84708 characters truncated) … com%2Flocal%2Fcustom_ui%2Fstate-card-custom-ui-es5.html.js%0A:2:31211 TypeError: Super expression must either be null or a function, not undefined”}}’, ‘LOCAL’, datetime.datetime(2018, 12, 25, 3, 26, 31, 290113, tzinfo=<UTC>), datetime.datetime(2018, 12, 25, 3, 26, 31, 309647), ‘d416fdbc98454e099c26ecf0ca12b3b5’, None)] (Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation)

It seems like this one is related to CustomUI. Looking into how to remove this as I think it’s unnecessary due to Lovelace, but haven’t figured out how to specifically remove it yet.

UPDATE: So I thought I fixed it, but it looks like it just stopped reporting, again… exact same error as above (minus date/time), so I’ll try to remove the custom UI code in the next few days to see if that helps.

Any thoughts or suggestions for what is going on and how to fix?