confused about migrating from sqlite to mysql

[pdarcos]

Hi guys,

I've been using bitwarden_rs for some time with sqlite and everything's working fine.

I wanted to migrate to the new mysql db backend but reading through the wiki I'm even more confused.

I first started by building a new image with MySql docker build -t bitwarden_rs --build-arg DB=mysql .

Then I tried following the instructions here but that's when things got confusing for me.

I never had mysql installed on my debian server before. By building a new image with mysql using the command above I see that there's now a mysql 5.7 image.

The wiki example mentions the following example

Example using Docker:

Start a mysql container

docker run --name mysql --net \ -e MYSQL_ROOT_PASSWORD=\ -e MYSQL_DATABASE=bitwarden\ -e MYSQL_USER=\ -e MYSQL_PASSWORD= -d mysql:5.7

Start bitwarden_rs with MySQL Env Vars set.

docker run -d --name bitwarden --net \ -v $(pwd)/bw-data/:/data/ -v :/ssl/\ -p 443:80 -e ROCKET_TLS='{certs="/ssl/",key="/ssl/"}'\ -e RUST_BACKTRACE=1 -e DATABASE_URL='mysql://:@mysql/bitwarden'\ -e ADMIN_TOKEN=\ -e ENABLE_DB_WAL='false'

But how/where do I set the mysql root password as well as create the bitwarden db and the respective username and password? Is that done using the -e variables above or do I need to docker exec -it into the mysql cointainer and setup mysql from scratch?

Also, in the example you have --net . Is this the default bridge network or do we need to create a new one? If new one, what are the settings we should use?

Thanks and sorry for the long question but I'm trying to figure out the best way to migrate from sqlite without losing all my data.

Cheers

[pdarcos]

--net "some-docker-network"

[pdarcos]

I see for example here that on step 5 we need to do mysql -u bitwarden -p bitwarden < mysqldump.sql so I presume I need to docker exec -it into the mysql cointainer and then run the mysql import. Is my understanding correct or am I missing something?

[pdarcos]

So I SSHed into the mysql container and I see that the bitwarden db is there as expected. So far so good I started the bitwarden_rs container with the appropriate mysql credentials in order to initialise the database so that I can then migrate the sqlite dump into it.

But when I check the bitwarden db it seems that there are no tables there. I must be doing something wrong but can't quite figure it out yet. Shouldn't the bitwarden db contain tables?

Any help?

mysql> show databases;
+--------------------+ | Database | +--------------------+ | information_schema | | bitwarden | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)

mysql> use bitwarden; Database changed mysql> show tables; Empty set (0.00 sec)

mysql>

[pdarcos]

Hmmm, I'm beginning to wonder if the image I built with mysql enabled is broken even though I don't recall seeing any errors. I used docker build -t bitwarden_rs --build-arg DB=mysql .

I've now tried another approach. I installed a non-dockerized mysql server 5.7 and followed all the instructions in the wiki. Specifically I created the bitwarden_rs database, the user and granted all privileges according to the wiki.

I then start the bitwarden_rs container previously built using the following command: docker run -d --name bitwarden_rs -e RUST_BACKTRACE=1 -e DATABASE_URL='mysql://mydbuser:myP@ssw0rd@127.0.0.1:3306/bitwarden_rs' -e ROCKET_TLS='{certs="/ssl/live/my.domain.com/fullchain.pem",key="/ssl/live/my.domain.com/privkey.pem"}' -e ENABLE_DB_WAL='false' -v /etc/letsencrypt/:/ssl/ -v /root/web-vault:/web-vault -v /bw-data/:/data/ -p 443:80 -e DOMAIN=https://my.domain.com bitwarden_rs:latest

The container starts with no errors but I don't think that diesel is running the migrations and setting up the schema as expected. The bitwarden_rs database continues to show it has no tables: mysql> show tables; Empty set (0.00 sec)

And also, netstat shows that port 443 isn't even listening, so it seems something is wrong.

Any clues? Where can I look to help debug why the container doesn't seem to bind to port 443 or write to the mysql database?

Thanks

[pdarcos]

Any help?

Thanks

[dani-garcia]

If you are building the docker image yourself, make sure you are using the MySQL dockerfile:

docker build -t bitwarden_rs -f docker/amd64/mysql/Dockerfile --build-arg DB=mysql .

Other than that, if it's a credential or a permission issue it should give you an error.

If the container starts, can you register and login?

[BobWs]

I'm running Bitwarden_rs MySQL version on a Synology NAS and I also migrated from the sQLite version.

I don't know on which host you are running Docker but for me these are the steps I took to successfully migrate Bitwarden_rs-mysql.

• install mysql database (mariadb or mysql, I preferred mariadb)
• install phpmyadmin (to manage your databases, e.g. mariadb)
• make your sqlitedump (check the wiki how to do that)
• create your bitwarden-db and db-user through phpmyadmin
• setup your bitwarden_rs-mysql container, but don't start it yet
• import the sqlite dump into e.g. mariadb through phpmyadmin
• make sure you have also setup your ssl
• start the bitwarden container

If all goes well then you can login and create your first bitwarden user and fine tune it through the admin page.

This is what I use to update Bitwarden_rs docker pull bitwardenrs/server-mysql:latest docker stop bitwarden-mysql docker rm bitwarden-mysql docker run -d \ --name bitwarden-mysql \ -v /<<path-to-folder>>/docker/bitwarden/data/:/data/ \ -v /<<path-to-folder>>/docker/ssl:/ssl/ \ -v /etc/localtime:/etc/localtime:ro \ -v /etc/TZ:/etc/timezone:ro \ -p 44300:80 -p 3012:3012 \ --env TZ=‘<<yourtimezone>>’ \ --env DATABASE_URL='mysql://bitwarden:<<yourpassword>>@mysql/bitwarden' \ --env ROCKET_TLS='{certs="/ssl/cert.pem",key="/ssl/privkey.pem"}' \ --env ENABLE_DB_WAL='false' \ --env RUST_BACKTRACE='1' \ --link mariadb:mysql \ --restart=‘always’ bitwardenrs/server-mysql:latest

[pdarcos]

Thanks for your help guys. I'm going to start a fresh installation to make sure I'm not running into any docker issues and will keep the thread updated.

Cheers

[pdarcos]

Unfortunately still stuck at getting mysql (mariadb in my case) to play nice on my debian 10 server.

It seems to be a connection issue. For some reason the bitwarden_rs container can't seem to connect to the standalone mariadb instance running on the same host.

My DB environment is this --env DATABASE_URL='mysql://bitwarden_rs:supersecretpassword@localhost:3306/bitwarden_rs'

thread 'main' panicked at 'Can't connect to DB: BadConnection("Can\'t connect to local MySQL server through socket \'/var/run/mysqld/mysqld.sock\' (2)")', src/libcore/result.rs:1189:5

Netstat shows mariadb is listening on localhost and the right port:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1952/mysqld

When I change the Database_url from localhost to 127.0.0.1 it still fails

thread 'main' panicked at 'Can't connect to DB: BadConnection("Can\'t connect to MySQL server on \'127.0.0.1\' (115)")', src/libcore/result.rs:1189:5

I must be doing something silly but I can't figure out what's wrong.

Any idea what I'm doing wrong?

P.S. The database I created manually is called bitwarden_rs as well as the user I also created manually following the wiki. IMO I think it would be best to use different names in the wiki for the db as well as db user or else it starts getting really confusing.

[pdarcos]

Very frustrating trying to figure out what's wrong.

netstat -an | grep mysql unix 2 [ ACC ] STREAM LISTENING 103955 /var/run/mysqld/mysqld.sock

the socket is running as expected

mariadb logs show no errors: Version: '10.3.18-MariaDB-0+deb10u1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian 10

I'm tempted to go back to sqlite after spending so much time on this and still not getting it to work.

Any idea what other logs I should check?

[pdarcos]

Well it seems I stumbled onto an annoying bug with docker and linux as can be seen https://github.com/docker/libnetwork/pull/2348

I tried the suggested workaround of setting network=host and using 127.0.0.1 in the bitwarden container and now I can connect to mariadb :) Finally!

But now using that workaround I'm getting another message when I run the container:

WARNING: Published ports are discarded when using host network mode

And indeed it seems that setting -p 443:80 doesn't work. The only port open is 80 but when I try to browse to the server on port 80 I get an error message: ERR_EMPTY_RESPONSE

This is so frustrating. Anyone else successful in connecting the bitwarden container to a mariadb host in debian 10? If so, how did you configure networking?

Running out of ideas and patience...

[fbartels]

Netstat shows mariadb is listening on localhost and the right port:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1952/mysqld

You cannot connect to localhost/127.0.0.1 from inside the container as this is exclusive to your host system. If you ping 127.0.0.1 inside of the container, the container is responding, not the host.

The solution to this problem is making mariadb listen on a global interface and the Docker container using this interface instead.

Also if you decide to solve this through the host network mode you do not need to bother with port forwarding with your container as all globally listening ports are already exposed.

[pdarcos]

Thanks for your help @fbartels

I tried your suggestion and now have mariadb listening on 0.0.0.0 (also tried setting bind-address = server's public IP) and netstat shows it listening globally, but I still get errors when the container tries to connect.

I've tried DATABASE_URL='mysql://bitwarden_rs:xxxxxyyyyyzzzzzz@serverPublicIP:3306/bitwarden_rs'

and the result is thread 'main' panicked at 'Can't connect to DB: BadConnection("Host \'172.17.0.2\' is not allowed to connect to this MariaDB server")', src/libcore/result.rs:1189:5

Why can't docker simply have host.docker.internal resolving to the host? Never thought I'd spend so much time on this frustrating experience. Still no joy

[pdarcos]

Closing this since it's not a bitwarden issue but rather a network configuration problem between docker and linux. I'll just run bitwarden_rs standalone instead of in docker.

Thanks for your help guys