MySQL/MariaDB

MariaDB je fork MySQL.

Home page MySQL: http://www.mysql.com/
Home page MariaDB: https://mariadb.org/

MySQL Workbench: http://www.mysql.com/products/workbench/

MySQL Driver: com.mysql.jdbc.Driver
MariaDB Driver: org.mariadb.jdbc.Driver

Root heslo k MariaDB: –tajne heslo–

Instalace balíků

shell# yum install mariadb-server.x86_64

Start a automatické spouštění

shell# systemctl start mariadb.service
shell# systemctl status mariadb.service
shell# systemctl enable mariadb.service

Nastavení hesla pro uživatele root

shell# /usr/bin/mysqladmin -u root password
shell# /usr/bin/mysqladmin -u root -h isn password

Příkaz pro připojení k db jako uživatel root

shell# mysql -p -u root

Smazat databázi test a anonymního uživatele

mysql> DROP DATABASE test;
mysql> DELETE FROM mysql.user WHERE User='';

Zálohuje a komprimuje všechny databáze MySQL/MariaDB.

mysql-backup.sh

#!/bin/bash
#
# Author: Jiří Blažek (blazek@isn.cz)
# App: MySQL/MariaDB backup script FOR ALL DATABASES
#
 
# absolute path home
APH=$(dirname $(readlink -f $0))
 
# app conf
MYSQL_USER="root"
MYSQL_PASS="secret-password"
MYSQL_BIN="/usr/bin/mysql"
MYSQL_DUMP="/usr/bin/mysqldump"
BZIP_BIN="/usr/bin/bzip2 -9"
BACKUP_DIR="/backup/mysql"
 
# log
function log() {
 echo $(/bin/date +"%Y-%m-%d %k:%M:%S") $@
}
LOG_PATH="$APH/backup-mysql.log"
 
# log info
log "[INFO] START backup MySQL databases" >> $LOG_PATH
 
# get all db names of mysql
GET_DB_NAMES=$($MYSQL_BIN --user=$MYSQL_USER --password=$MYSQL_PASS -s -N -e "SHOW DATABASES;" | grep -Ev 'information_schema|performance_schema|test|mysql')
 
# backup all databases
for DB_NAME in $GET_DB_NAMES; do
 
  $MYSQL_DUMP --user=$MYSQL_USER --password=$MYSQL_PASS --default-character-set=utf8 $DB_NAME | $BZIP_BIN > $BACKUP_DIR/$DB_NAME-$(date '+%Y-%m-%d-%H-%M-%S').bz2
  if [ $? -eq 0 ]; then
    log "[INFO] Database backup OK = "$DB_NAME >> $LOG_PATH
  else
    log "[ERROR] Database backup failed = "$DB_NAME >> $LOG_PATH
    exit 1
  fi
 
done
 
# log info
log "[INFO] END backup MySQL databases" >> $LOG_PATH

Vytvoření databáze example s nastavením práv na databázi example pro uživatele example.

Vytvoření databáze v DBMS MariaDB pro example.com.

<code>
shell# mysql -p -u root
...
MariaDB [(none)]> CREATE DATABASE example CHARACTER SET utf8;
MariaDB [(none)]> GRANT ALL privileges ON example.* TO example@localhost IDENTIFIED BY '--tajne heslo--';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> QUIT;

shell> mysqldump -p -u root --no-data dbname > schema.sql

shell> mysql -p -u root --default-character-set=utf8 dbname < /pathdump/dump.sql