Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata


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:

cd $WD/backups
systemctl start mariadb
cat << 'EOF' >
# 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')"

mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

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
chmod +x

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  *               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