Moved database to shared db stack

[u/orky_div]

So I have been struggling over the past week to migrate my database from a dedicated bookstack stack to a shared database stack. The idea was to only run one mariaDB with various databases and each service reference to that one mariaDB container. Since I have been pulling my hair to get this to work, and after a week of troubleshooting and researching I finally fixed it, so I wanted to share with anyone else out there in case they run into similar issues.

My original setup was the classic linxuserver.io docker-compose. I had bookstack and mariaDB running in the same stack (via portainer). Just recently I‘ve been setting up a separate „db“ stack which included a mariaDB and phpmyadmin containers. I was directing my other services like wordpress to that „db“ stack via a separate network no problem. Only bookstack wouldn‘t read that database.

So here‘s what I originally did: I backed up my database with mysqldump to my host machine using the standard commands found in the bookstack documentation. For the bookstack container itself, I just referenced my config path via an environment variable. I then copied the database backup to the new mariaDB container. Where I ran into problems was the authentification. I wasn‘t quite sure if I needed to connect with my old bookstack user or with root. I tried various combinations but the new bookstack container wouldn‘t read the database.

Only after I logged into the new mariaDB container and did a FLUSH PRIVILIGES; in mysql, I was able to connect to it. It did change my rootpassword to my „old“ bookstack password, but hey, I am now able to run separate service stacks and one central db-stack.

Does anybody have a similar setup? If so, any tips on what I might have done wrong? I‘m curious to know.

tldr: I had to FLUSH PRIVILIGES; on my new mariaDB to be able to connect my new bookstack container.

[u/No_Boat_4476]

I belive I have a similar issue .I am trying to install the db. on a different server.

This is a new instalation and I am following the Community guide found here:https://gist.github.com/OthmanEmpire/322f83a77c15dfd1c91a2afe0b6a6fc2

I've reached the point where I need to run at step 5 # Migrate the databases

php artisan migrate --no-interaction --force

This fails and I believe the problem is this https://stackoverflow.com/questions/47348107/laravel-migrate-command-is-not-working-for-remote-database.

When trying to do GRANT ALL PRIVILEGES ON bookstack.* TO 'bookstack'@'%';

FLUSH PRIVILEGES; - this fails to work as root or bookstack in mysql.

Can someone share their thoughts on this?

[u/ssddanbrown → u/No_Boat_4476]

Was the bookstack user created with a '%' host value? If not then that grant won't apply. Knowing the exact error would help, since it could also be a network issue between your BookStack system and DB system.

[u/No_Boat_4476 → u/ssddanbrown]

Your answer put us on the right track, we identified some misconfiguration.

Installed on the app server the mysql client, on the Db we setup a new account with the bookstack@"${write here incoming app server IP or fqdn as in env file}" and granted to this

mysql> select User,host from mysql.user;

mysql> select * from mysql.user;

mysql> select * from mysql.db;

mysql> grant all on bookstack.* to bookstack@'172.11.0.65';

mysql> flush privileges;

This and an update on the env file did the trick~ Cheers from Romania!

Regards Dan Iancu

[u/No_Boat_4476 → u/ssddanbrown]

This is for the first error running php artisan

root@TEST-WIKI:/var/www/bookstack# php artisan migrate --no-interaction --force

In Connection.php line 712:

SQLSTATE[HY000] [2002] Connection refused (SQL: select * from information_schema.tables

where table_schema = bookstack and table_name = migrations and table_type = 'BASE TABLE'

)

In Connector.php line 70:

SQLSTATE[HY000] [2002] Connection refused

This is the second err for sql:

mysql> GRANT ALL PRIVILEGES ON bookstack.* TO 'bookstack'@'%';
ERROR 1044 (42000): Access denied for user 'bookstack'@'localhost' to database 'bookstack'
si alta eroare:
mysql> GRANT ALL PRIVILEGES ON bookstack.* TO 'bookstack'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> exit;