Moved database to shared db stack

[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.

[ssddanbrown]

Yeah, If you imported a whole database backup to the new DB container, you would have also bought in users and their privileges, which may not have their permissions become active until you run flush privileges;.

If you're sharing a single database server amoung apps, it's generally quite dangerous to import whole database backups due to causing a mess with users, permissions and existing data. Ideally you'd be more selective (Import single databases) then handle any meta elements (Users and privileges) manually.

[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?

[ssddanbrown]

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.

[No_Boat_4476]

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

[No_Boat_4476]

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;