Skip to content

MySQL & MariaDB cheat sheet

Jump to bottom
Matvey Gladkikh edited this page Jan 18, 2023 · 8 revisions

Gracefull shutdown:

mysqladmin shutdown

Change password version <= 5.7.5

SET PASSWORD FOR 'user'@'IP' = PASSWORD('PASSSS');
FLUSH PRIVILEGES;

Change password version > 5.7.5

ALTER USER 'user'@'IP' IDENTIFIED BY 'PASSSS';
FLUSH PRIVILEGES;

Some useful commands:

CREATE USER 'user'@'10.27.53.%/255.255.255.0' IDENTIFIED BY 'XXXXXXXX';
SET PASSWORD FOR 'user'@'10.27.53.%/255.255.255.0' = PASSWORD('XXXXXXXX');
GRANT ALL on *.* to 'user'@'10.27.53.%/255.255.255.0';

CREATE USER 'user'@'1.2.3.4' IDENTIFIED BY 'XXXXXXXX';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'1.2.3.4';

CREATE USER 'slave'@'1.2.3.4' IDENTIFIED BY 'XXXXXXXX';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'1.2.3.4';

CHANGE MASTER TO MASTER_HOST='1.2.3.4', MASTER_USER='slave', MASTER_PASSWORD='XXXXXXXX', MASTER_LOG_FILE='mysql-bin.078304', MASTER_LOG_POS=33848958;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=000;

utilites (docker-create-mariadb, docker-create-mysql)

curl -L 'https://git.io/J4POb' | sudo bash

install MySQL

docker-create-mysql

install MariaDB

docker-create-mariadb

fast file dumps

MySQL - innobackupex

wget http://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb; dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb; apt-get update; apt-get -y install percona-xtrabackup-24 qpress; which innobackupex;

MariaDB - mariabackup

apt-get -y install mariadb-backup

create dump (MySQL)

mkdir -p /backup/xtrabackup
echo "show master status;" |mysql > /backup/xtrabackup/master-status.txt
innobackupex /backup/xtrabackup/; 
innobackupex --apply-log /backup/xtrabackup/2021-06-25_16-41-30;

create mariabackup copy (docker)

apt-get update;
apt-get -y install mariadb-backup;
rm -rf /backup/mariabackup;
mkdir -p /backup/mariabackup;
echo "show master status;" |mysql > /backup/mariabackup-master-status.txt
mariabackup --backup --target-dir=/backup/mariabackup/ --datadir=/var/lib/mariadb-3306/data --logdir=/backup/mariadb-3306/binlogs/
mariabackup --prepare --target-dir=/backup/mariabackup/ --datadir=/var/lib/mariadb-3306/data --logdir=/backup/mariadb-3306/binlogs/

#restore:
#rsync -avP --delete /backup/mariabackup/* root@IP:/var/lib/mariadb-3306/data/ ;

create mariabackup copy (normal)

apt-get update;
apt-get -y install mariadb-backup;
rm -rf /backup/mariabackup;
mkdir -p /backup/mariabackup;
echo "show master status;" |mysql > /backup/mariabackup-master-status.txt
mariabackup --backup --target-dir=/backup/mariabackup/ --datadir=/var/lib/mysql --logdir=/var/log/mysql/
mariabackup --prepare --target-dir=/backup/mariabackup/ --datadir=/var/lib/mysql --logdir=/var/log/mysql/

#restore:
#rsync -avP --delete /backup/mariabackup/* root@IP:/var/lib/mysql/ ;

restore dumps:

mariadb

docker stop mariadb-3306;
rm /var/lib/mariadb-3306/data/* -rf;
rm /backup/mariadb-3306/binlogs/* -rf;
innobackupex --datadir=/var/lib/mariadb-3306/data/ --logdir=/backup/mariadb-3306/binlogs/ --copy-back /backup/xtrabackup/2021-06-25_16-41-30/
chown systemd-coredump:systemd-coredump /var/lib/mariadb-3306 -R
chown systemd-coredump:systemd-coredump /backup/mariadb-3306 -R
docker start mariadb-3306;

mysql

docker stop mysql-3306;
rm /var/lib/mysql-3306/data/* -rf;
rm /backup/mysql-3306/binlogs/* -rf;
innobackupex --datadir=/var/lib/mysql-3306/data/ --logdir=/backup/mysql-3306/binlogs/ --copy-back /backup/xtrabackup/2021-06-25_16-41-30/
chown systemd-coredump:systemd-coredump /var/lib/mysql-3306 -R
chown systemd-coredump:systemd-coredump /backup/mysql-3306 -R
docker start mysql-3306;

mysqldump script

#!/bin/bash

#local settings:
pass_file="/root/.my.cnf"
days_to_keep="15"
backup_path="/backup/`hostname`/sql"

#ftp:
ftp_server=''
ftp_user_name=''
ftp_user_pass=''

LANG=C
cmddir=`dirname $0`
cmdname=`basename $0`
newtmpdir="/tmp/${cmdname}"
mkdir -p "${newtmpdir}" 
if [ -e ${newtmpdir}/lock ]
then
echo "allready running with lockfile ${newtmpdir}/lock";
exit;
else
touch ${newtmpdir}/lock
fi

function cleanup () {
  mysql -u ${user} -p$pass -e "start slave;" &> /dev/null
  rm -rf "$newtmpdir"
}

trap 'cleanup' EXIT
trap 'cleanup' SIGTERM

user=`cat ${pass_file} |grep user | awk '{print$3}' |head -1`
pass=`cat ${pass_file} |grep password | awk '{print$3}' |head -1`
hostname=`hostname`
day=`date +%y%m%d-%H-%M`;


mkdir -p ${backup_path} &> /dev/null
mysql -u ${user} -p$pass -e "stop slave;" &> /dev/null
mysql -u ${user} -p$pass -e "show master status\G;" > ${backup_path}/${day}-master-status.txt
mysql -u ${user} -p$pass -e "show slave status\G;" > ${backup_path}/${day}-slave-status.txt
for dbname in `mysql -u ${user} -p$pass -e "show databases" | grep -Ev 'Database|information_schema|mysql|performance_schema'`;
do 
echo "Dumping $dbname"; 
nice -n19 ionice -c 3 mysqldump --max_allowed_packet=512M --lock-tables --skip-extended-insert --single-transaction --flush-privileges --routines --triggers -u ${user} -p${pass} $dbname | nice -n19 ionice -c 3 gzip > ${backup_path}/${day}-${dbname}.sql.gz;
if [ $? -ne 0 ]; then
  echo "ERROR: $dbname database backup FAILED at ${hostname} : ${day} : ${backup_path}/${day}-${dbname}.sql.gz"
fi
done
mysql -u ${user} -p$pass -e "start slave;" &> /dev/null

nice -n19 ionice -c 3  find ${backup_path}/* -type f -ctime +${days_to_keep} | xargs nice -n19 ionice -c 3 rm -f

# then transfer all to ftp:
if [ "${ftp_server}" != "" ] 
 then
  trickle -u 20000 -d 20000 nice -n19 ionice -c 3 lftp -c "set ftp:list-options -a; set ssl:verify-certificate no; open ftp://$ftpuser:$ftppass@$ftphost; lcd ${backup_path}; mkdir -p ${backup_path}; cd ${backup_path}; mirror --reverse --delete  --verbose" &> /dev/null
fi
Clone this wiki locally