-
Notifications
You must be signed in to change notification settings - Fork 0
MySQL & MariaDB cheat sheet
matveynator edited this page Mar 13, 2024
·
8 revisions
mysqladmin shutdown
SET PASSWORD FOR 'user'@'IP' = PASSWORD('PASSSS');
FLUSH PRIVILEGES;
ALTER USER 'user'@'IP' IDENTIFIED BY 'PASSSS';
FLUSH PRIVILEGES;
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;
curl -L 'https://git.io/J4POb' | sudo bash
docker-create-mysql
docker-create-mariadb
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;
apt-get -y install mariadb-backup
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;
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/ ;
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/ ;
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;
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;
#!/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
docker run -d \
--name mariadb-3306 \
-v /var/lib/mariadb-3306/conf.d:/etc/mysql/conf.d \
-v /var/lib/mariadb-3306/data:/var/lib/mysql \
-v /etc/timezone:/etc/timezone:ro \
-v /etc/localtime:/etc/localtime:ro \
-v /backup/mariadb-3306/binlogs:/var/log/mysql \
-e MYSQL_ROOT_PASSWORD=ПАРОЛЬСКРЫТ \
-e MYSQL_USER=root \
-e MYSQL_PASSWORD=ПАРОЛЬСКРЫТ \
-p 3306:3306 \
--restart=unless-stopped \
mariadb:10.1.48
[mysql]
max_allowed_packet = 512M
default-character-set = utf8mb4
[mysqld]
#skip-grant-tables
skip-name-resolve
server-id = 6
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/slave/mysqld-relay-bin
expire_logs_days = 20
max_allowed_packet = 512M
slave-skip-errors = 1062
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
# InnoDB settings
innodb_buffer_pool_size = 180G
innodb_log_buffer_size = 1G
innodb_file_per_table = 1
innodb_print_all_deadlocks = 1
innodb_flush_method = O_DIRECT
innodb_doublewrite = 0
innodb_flush_neighbors = 0
innodb_io_capacity = 20000
innodb_io_capacity_max = 40000
innodb_change_buffer_max_size = 80
innodb_thread_concurrency = 48
innodb_read_io_threads = 24
innodb_write_io_threads = 24
#FULL ACID compliance (fault tolerant innodb_flush_log_at_trx_commit = 1,3 (0,2 speed but crashing))
innodb_flush_log_at_trx_commit = 2
#sync_binlog = 0
# Increase number of threads for handling connections
max_connections = 4000
thread_handling = pool-of-threads
[mysqldump]
max_allowed_packet = 512M
default-character-set = utf8mb4
```
# BENCHMARK:
## 700 клиентов по 50 запросов:
```
mysqlslap --port=3306 --user=USER --password=ПАРОЛЬ --auto-generate-sql --concurrency=700 --iterations=10 --number-int-cols=4 --number-char-cols=10 --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --auto-generate-sql-execute-number=50 --host=IP
```
## 700 клиентов по 10 запросов:
```
mysqlslap --port=3306 --user=USER --password=ПАРОЛЬ --auto-generate-sql --concurrency=700 --iterations=10 --number-int-cols=4 --number-char-cols=10 --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --auto-generate-sql-execute-number=10 --host=IP
```