From fdc57d7e7373414305172728966b8485e0a7b45c Mon Sep 17 00:00:00 2001
From: Pjotr Prins
Date: Sun, 12 Nov 2023 09:12:02 +0100
Subject: Running mariadb in a container with the large DB

---
 topics/setting-up-local-development-database.gmi   | 12 +++++------
 topics/systems/mariadb/mariadb.gmi                 | 12 ++++++++---
 .../mariadb/precompute-mapping-input-data.gmi      | 25 +++++++++++++++++++++-
 3 files changed, 39 insertions(+), 10 deletions(-)

(limited to 'topics')

diff --git a/topics/setting-up-local-development-database.gmi b/topics/setting-up-local-development-database.gmi
index a4c6676..58e19ad 100644
--- a/topics/setting-up-local-development-database.gmi
+++ b/topics/setting-up-local-development-database.gmi
@@ -61,6 +61,9 @@ SQL_URI="mysql://root@127.0.0.1:3306/db_webqtl_s"
 ## Method 2 (running a local container)
 
 A local container runs as a normal user. It requires some juggling of dirs to load an existing database. Make sure to not run on the same dirs as another mariadb instance(!)
+Here we run a new database, but you can use an existing DB as was done in
+
+=> precompute-mapping-input-data.gmi
 
 ```
 cd ~/tmp/mariadb
@@ -82,17 +85,17 @@ Initialize with
 mysql_install_db
 ```
 
-and run
+and run, for example
 
 ```
 export TMPDIR=/tmp
-mysqld_safe --datadir='./database' --port=3307 --user=$USER --nowatch --socket=/var/run/mysqld/mysqld.sock
+mysqld_safe --datadir='/var/lib/mysql/' --port=3307 --user=$USER --group=users --nowatch --socket=/var/run/mysqld/mysqld.sock
 ```
 
 Now from outside the container you should be able to connect with
 
 ```
-stromboli:~/tmp/mariadb$ mysql --socket=var/run/mysqld/mysqld.sock
+/export/mysql$ ~/opt/guix-pull/bin/guix shell mysql -- mysql --socket=var/run/mysqld/mysqld.sock -uwebqtlout -pwebqtlout db_webqtl
 ```
 
 and
@@ -120,9 +123,6 @@ MariaDB [(none)]> show databases;
 
 If you need to tweak the server configuration you can load the my.cnf file with the `--defaults-file=var/my.cnf` inside and outside the container.
 
-
-
-
 ## Method 3 (Manual method without Guix)
 
 /This is not recommended/
diff --git a/topics/systems/mariadb/mariadb.gmi b/topics/systems/mariadb/mariadb.gmi
index 56661b6..4000482 100644
--- a/topics/systems/mariadb/mariadb.gmi
+++ b/topics/systems/mariadb/mariadb.gmi
@@ -2,12 +2,16 @@
 
 Here we capture some common actions
 
-## Tags
+To install Mariadb (as a container) see
+
+=> setting-up-local-development-database.gmi
+
+# Tags
 
 * type: info, documentation
 * keywords: mariadb, systems
 
-## Check the transaction logs
+# Check the transaction logs
 
 Start the client and:
 
@@ -35,7 +39,7 @@ To get a log with some stuff filtered out try
 mysql -p -u webqtlout db_webqtl -e "SHOW BINLOG EVENTS IN 'gn0-binary-log.000014';" -r -s |grep -v -e "Access\|GTID\|INSERT_ID\|COMMIT\|Temp\|lastlogin\|LITERA\|flush\|ROLLBACK"
 ```
 
-## Update MariaDB on penguin2 to the latest production database
+# Update MariaDB on penguin2 to the latest production database
 
 The MariaDB instance running on penguin2 needs to be periodically updated to the latest version of the database running on production. We do this by restoring backups of the production database stored on penguin2 into the penguin2 MariaDB database directory. Here's how.
 
@@ -55,3 +59,5 @@ Stop the running mariadb-guix.service. Restore the latest backup archive and ove
 
 => https://www.borgbackup.org/ Borg
 => https://borgbackup.readthedocs.io/en/stable/ Borg documentation
+
+#
diff --git a/topics/systems/mariadb/precompute-mapping-input-data.gmi b/topics/systems/mariadb/precompute-mapping-input-data.gmi
index 091a4a3..20b23d4 100644
--- a/topics/systems/mariadb/precompute-mapping-input-data.gmi
+++ b/topics/systems/mariadb/precompute-mapping-input-data.gmi
@@ -687,10 +687,33 @@ Next we create a drop for the backups from another machine following:
 
 => ../backup_drops.gmi
 
-Now we are copying the backup which takes a while over the slow network.
+Copy the backup which takes a while over the slow network and unpack with the great tool borg
 
+```
+time borg extract --progress /export/backup/bacchus/drop/tux01/borg-tux01::borg-backup-mariadb-20231111-06:39-Sat
+```
+
+Next install a recent Mariadb from guix as a container using the instructions in
+
+=> mariadb.gmi
+=> setting-up-local-development-database.gmi
+
+move the database files into, for example, /export/mysql/var/lib/mysql. chown files to your user account. Next
 
+```
+cd /export/mysql
+mkdir tmp
+mkdir run
+tux04:/export/mysql$ ~/opt/guix-pull/bin/guix shell -C -N coreutils sed mariadb --share=/export/mysql/var=/var --share=/export/mysql/tmp=/tmp
+    export TMPDIR=/tmp
+    mysqld_safe --datadir='/var/lib/mysql/' --port=3307 --user=$USER --group=users --nowatch --socket=/var/run/mysqld/mysqld.sock
+```
+
+and a client with:
 
+```
+/export/mysql$ ~/opt/guix-pull/bin/guix shell mysql -- mysql --socket=var/run/mysqld/mysqld.sock -uwebqtlout -pwebqtlout db_webqtl
+```
 
 
 
-- 
cgit v1.2.3