Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

WIP:

WD=/opt/mariadb
mkdir -p $WD/{data,conf,backups}
cd $WD/backups
#Backup via File-Level-Archive
systemctl stop mariadb
tar -zcvf mariadb-data-dir_"$(date '+%F')".tgz '/var/lib/mysql/'
tar -zcvf mariadb-conf-dir_"$(date '+%F')".tgz '/etc/mysql/'
#Copy Conf via RSync
rsync -avzP /etc/mysql/ $WD/conf/


Dump All-DB's (except schema/mysql) & Users:

https://dba.stackexchange.com/a/69667/113202

cd $WD/backups
systemctl start mariadb
cat << 'EOF' >mysql_all_db_dump.sh
#!/bin/bash
#https://dba.stackexchange.com/a/69667/113202
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > mariadb_alldb_"$(date '+%F')".sql
EOF
chmod +x mysql_all_db_dump.sh
./mysql_all_db_dump.sh

https://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server/399875#399875

cd $WD/backups
systemctl stop mariadb
MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > mariadb_users_"$(date '+%F')".sql

Install Fresh Container & Start MariaDB-Docker

Import Backups:

cd $WD/backups
mysql -u root -p mysql < mariadb_users_*.sql
mysql -u root -p < mariadb_alldb_*.sql
docker restart mariadb

Note: If your previous installation only allowed for localhost connections you may have to adjust some things such as the "bind-address" and the hostname params of MySQL Users.

UPDATE IGNORE mysql.user SET host = '%' WHERE host = 'localhost';
netstat -tulpn | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      157544/mysqld
HOSTIP="$(ip -4 addr show eth0 | grep -oP '(?<=inet\s)\d+(\.\d+){3}')"
mysql -h $HOSTIP -u SuperUserName -pSuperUserPass
mysql -h localhost -u SuperUserName -pSuperUserPass


Remove Old MariDB, then re-install for tools:

apt purge mariadb
rm /etc/mysql/ -R
rm /var/lib/mysql/ -R
apt update
apt install mariadb
  • No labels