Mysql Administration

MySQL Root Password

For Linux

To restore mysql database dumps on Debian/Ubuntu based Linux distributions, you need credentials for a highly priviledged mysql user which is different from the mDIS user. By default, on mysql, this user is called root. This database user is different from the root superuser of the operating system.

Any mysql user who can restore databases from backups must have permission to drop and re-create database objects such as tables or indexes.

By default, the predefined Mysql user root can do anything inside mysql. You can create another user if you have access to the mysql root account. You can even create other database users which are as powerful and unrestricted as the root user. To do so, you should login with the mysql database system.

From the command line, try
mysql -u root -p # use mysql-root user to access mysql .

Add the -h HOST flag if your db server is on a different host.

This shell command will ask for the mysql-root password. If you don't have it, you are in trouble.
Also, if the mysql root user has fewer permissions than it should have (some administrator or script might have taken away some rights from that root account) then you are also in trouble.

Optional: Reset mysql root user

In case you have lost the credentials for such a mysql user, or cannot get to know them, or if root lacks certain rights, do this:
Check file /etc/mysql/debian.cnf on the database host. This file might contain a username and a password which you can use. You will need to access this file as a "sudoer", or as the operating-system root account (of the host where mysql runs).

If you are stuck, read this

• "MYSQL Permission: Access denied for user 'debian-sys-maint'@'localhost" (opens new window)
• "mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists" (opens new window)

and start googling.

Workaround, for mySQL 5.7

Here is another way to reset the credentials of the mysql 5 root user.

However, the following procedure might help only if you have access to another mysql useraccount that has grant privileges equivalent to the root user. This might hold for your own development server, for example.

These SQL statements reset the mysql user root password for connection from localhost, and from any other host:

-- be careful. Might vary for different MySQL/MariaDB releases!
use mysql;
update user set authentication_string=PASSWORD("?????") where User='root' and host = 'localhost';
update user set authentication_string=PASSWORD("?????") where User='root' and host = '%';
flush privileges;
quit
-- For mySQL 8 this is NOT the right syntax. PASSWORD() does no longer exist.

For a more thorough discussion regarding mySQL 8, see this StackOverflow Post (opens new window).

This was only a first step. Extra work is necessary, before and after issuing these SQL statements! In particular new SQL SET PASSWORD (opens new window)-, CREATE USER- and GRANT- commands are needed to restore previous users and their privileges.

Now you should have sufficient mysql-root credentials. You can continue restoring the mysql database(s) from backups, or you can create a special-purpose mysql account for creating and restoring backups.

More Credentials

Suggestions

You need valid credentials to login to mysql to see any mDIS data in tabular format; and (ideally) you need a mysql-root password, or a sufficiently enabled mysql-user (see above) to make full backups of the mysql server.

Maybe store these credentials elsewhere!

• mysql User Account used by the Webserver:
  • Username dis (or mdis, or ...), Password
  • ICDP internal: (wb33: similar to pw of the mdis or administrator ssh account)
• mysql Root Account on localhost:
  • Username root, Password ??????

perhaps in file /home/mdis/.my.dis_backup.cnf or equivalent.

# example for  ~/.my.dis_backup.cnf credentials-file
# usable from command line
[client]
user="dis"
password=
host=localhost

This setting would work on VirtualBox installations where the mysql DB resides on the same host as the mDIS codebase.

This config file is used in the mysqldump script below.

Dump script

This is a minimal example. It writes compressed dump files with a certain naming convention,
and stores them in dir /var/www/dis/backend/data/upload/backup/mysql/. Adapt to your needs.

#!/bin/sh
# create a mysql dump , put that .sql file into a zipfile
# also save json templates
# knb 202004
#
host=$(hostname -I | sed -r "s/ .+$//")
host="$(uname -n)--${host}"
if [  $# -eq 1 ]
then
  host=$1
fi
host="$(echo "${host}" | tr -d '[:space:]')"


date=$(date -I)
db=dis

mkdir -p /var/tmp/upload/backup/mysql

outd=/var/tmp/upload/backup/mysql
outd_base=/var/www/dis/
outd_json=backend/dis_templates/
myd=/usr/bin
outf=mysqldb_backup_vbox_${host}--${date}--${db}.sql
outzip=${outf}.zip

if [ -d /tmp ]
then
  cd /tmp
  ${myd}/mysqldump --defaults-extra-file=$HOME/.my.dis_backup.cnf \
  --add-drop-table  --add-locks --no-create-db --extended-insert \
  --quick --lock-tables --databases $db > "${outf}"

  # quiet, move sql-dump into zip, line separator is cr-lf
  zip -q -m -l  "${outd}/${outzip}" "${outf}"

  chmod g+w ${outd}/${outzip}
fi

# also save the all-important *.json files
# (containing forms- and models-metadata) into the same zip file
cd ${outd_base}
zip -q -r  "${outd}/${outzip}" "${outd_json}"

A better dumpscript would also save database-views, save all table definitions into 1 .sql file per table, and also save files uploaded by the users.

Dumping creates compressed outfiles named like this: mysqldb_backup_vbox_mdis-jet--10.0.2.15--2020-04-10--dis.sql.zip.

Uncompress with zip, 7z, or use the built-in "Extract" feature of the graphical filemanagement tools of your Windows-PC or Mac laptop.

Here is a description what the zipfile contents could look like: Zipfile Listing

Cronjob

It is easy to automatically make backups with the above-mentioned scripts.

Check if a there is a Cron job running periodically. Run crontab -l | grep -v "#", it should show output similar to the following:

57 14 * * * $HOME/bin/dump_dis_mysql.sh

If this is missing, add such an entry with crontab -e running as unixuser administrator (or mdis, or any user who is a regular operating system user, not root).

TIP

Making mysql dumps can be automated, but loading a database is a manual task and cannot be automated. Therefore there is no cronjob for restoring databases from backups, and veryfing integrity of backus.

Load script

It loads files from dir /var/www/dis/backend/data/upload/backup/mysql/. Adapt to your needs.

Script content:

#!/bin/sh
#load databases dumped at a foreign server into localhost
#
# knb 2019

host=`uname -n`
pw=
myd=/usr/bin
db=dis

if [ $# -ne 1 ]
then
echo $0: load mysql databases dumped at a foreign server into host ${host}
echo "$0: expects input file extension .sql or .bz2"
echo ""
echo Invalid format: $# argv parameters provided, 1 required
echo Required format: $0 '<DUMPFILENAME> '
echo If the filename extension is .bz2, this script will bunzip2 the dumpfile first.

exit 1
fi



fullfile="$1"
shift

filename=$(basename -- "$fullfile")
extension="${filename##*.}"
infile_no_ext="${filename%.*}"

dumpdir=/var/www/dis/backend/data/upload/backup/mysql/
infile="${dumpdir}/${filename}"
echo "dir: '$dumpdir'"
echo "looking for infile '$infile' "

if [ -z "$1" -a  "$extension" = "bz2" ]
then
  ext=".$2"
  cmd="bunzip2 --keep --force $infile"
  $($cmd)
  cmd="echo '${cmd}'"
  infile="$dumpdir/$infile_no_ext"
echo ""

elif [ -z "$1" -a "$extension" = "sql" ]
then
  echo "Trying to load dumpfile '$filename' into mysql"
  echo "$infile"

else
  echo "Nothing to import. Filename extension must bei either .bz2 or .sql "
  echo ""
fi


echo ""
ls -l "$infile"
echo ""
echo ""
echo "head -25 "${dumpdir}/$infile_no_ext" | grep -C 3 $db"
head -25 "${dumpdir}/$infile_no_ext" | grep -C 3 $db

echo "Execute this:"
echo ""
echo " ${myd}/mysql -hmyhost.gfz-potsdam.de -udis  -p$pw --database=$db -f  < $infile"

Example Call on commandline:

./load_some_mysqldbs.sh /var/www/dis/backend/data/upload/backup/mysql/mysqldb_backup--dis.2.sql.bz2

Output

dir: '/var/www/dis/backend/data/upload/backup/mysql/'
looking for infile '/var/www/dis/backend/data/upload/backup/mysql//mysqldb_backup--dis.2.sql.bz2'


-rwxrwxrwx 1 administrator administrator 1039342 Jul 25 17:10 /var/www/dis/backend/data/upload/backup/mysql//mysqldb_backup--dis.2.sql


head -25 /var/www/dis/backend/data/upload/backup/mysql//mysqldb_backup--dis.2.sql | grep -C 3 dis
-- MySQL dump 10.13  Distrib 5.7.27, for Linux (x86_64)
--
-- Host: localhost    Database: dis
-- ------------------------------------------------------
-- Server version 5.7.27-0ubuntu0.18.04.1

--
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dis`
--

USE `dis`;

--
-- Table structure for table `archive_file`
Execute this:

 /usr/bin/mysql -hwb33.gfz-potsdam.de -udis  -pXXXXX --database=dis -f  < /var/www/dis/backend/data/upload/backup/mysql//mysqldb_backup--dis.2.sql

WARNING

The last line of the output above is the actual load command which you can copy and paste, adapt to your needs, and the reload the dis database.

More about restore

Sys-Admin page - higher-level documentation of backup/restore.

Template manager - understanding the interplay of json-templates and SQL database is essential. Read this if you prefer to restore individual forms or mDIS tables. You can do this from within the mDIS webpage 'Templates-Manager' itself. You do not need to use the command line.

More about mysql

These commands are not that important, noted here for completeness and reproducibility. Mysql commands are case-insensitive, and both double quotes and single quotes are allowed for quoting strings. TBC

mySQL Versions and sql_mode used in 2020

The actual SQL commands are commented out with -- prefixes below:

    select @@version
--    5.7.30-0ubuntu0.18.04.1
-- when mDIS runs mySQL version 5.7

-- SELECT @@GLOBAL.sql_mode

STRICT_TRANS_TABLES,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

-- older settings (2019):
--STRICT_TRANS_TABLES,
--ERROR_FOR_DIVISION_BY_ZERO,
--NO_AUTO_CREATE_USER,
--NO_ENGINE_SUBSTITUTION
--ONLY_FULL_GROUP_BY,
--NO_ZERO_IN_DATE,
--NO_ZERO_DATE,

-- when mDIS runs mySQL version 8
 8.0.20-0ubuntu0.20.04.1 |
SELECT @@GLOBAL.sql_mode;

ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION

References

Comparison of relational database management systems (opens new window)