====== 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/

===== JDBC =====

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

===== Instalace a nastavení MariaDB =====

**Root heslo k MariaDB:** --tajne heslo--

**Instalace balíků**

<code>
shell# yum install mariadb-server.x86_64
</code>

**Start a automatické spouštění**

<code>
shell# systemctl start mariadb.service
shell# systemctl status mariadb.service
shell# systemctl enable mariadb.service
</code>

**Nastavení hesla pro uživatele root**

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

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

<code>
shell# mysql -p -u root
</code>

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

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



===== Zálohovací script =====

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

<file bash 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
</file>
===== Vytvoření databáze =====

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

<code>
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;
</code>

===== Export DB schéma =====

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

===== Import dump databáze =====

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