Table of Contents

System variables

https://mariadb.com/kb/en/mariadb/server-system-variables/

Performance report

wget mysqltuner.pl

Find duplicates

SELECT evcj, udalostid FROM log
GROUP BY udalostid,evcj HAVING count(udalostid) > 1

Remove duplicates by index

ALTER IGNORE TABLE nazev ADD UNIQUE INDEX (a,b)

CVE-2016-6662

Attaker must have user access to gain access.
How to block: Configuration files must be read-only for MySQL processes. 

chown root:root /var/lib/mysql/
chown root:root /etc/mysql/my.cnf

Some configuration chaos

innodb_use_sys_malloc = 0
innodb_buffer_pool_size = 1024MB
innodb_log_file_size = 50MB
innodb_checksum = 0
innodb_doublewrite = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_check = Barracuda
innodb_file_per_table = 1

innodb_open_files = 500
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 500
interactive_timeout = 20
back_log = 75
table_cache = 300
thread_cache = 32
thread_concurrency = 8
wait_timeout = 30
connect_timeout = 10

Note that using CHAR will only speed up your access if the whole record is fixed size. That is, if you use any variable size object, you might as well make all of them variable size. You gain no speed by using a CHAR in a table that also contains a VARCHAR.

Indexes

Query Action
WHERE a = ? create index (a)
ORDER BY a create index (a)
GROUP BY a index (a)
WHERE a = ? AND b = ? create index (a, b) or (b, a)
ORDER BY a, b create index (a, b)
WHERE a = ? ORDER BY b create index (a, b)
WHERE a = ? OR b = ? create separate (a) (b)
index (a, b) exist? index (a) not neccesary
index (a) you need (a, b) drop (a) and create (a, b)
index (a, b) you need (b) create (b)

Be sure to check collate for each table, because DB can't use indexes.

Files manipulation

<?php
/** Třída pro použití funkcí stream_wrapper_register() zapisující do databáze
* @copyright Jakub Vrána, https://php.vrana.cz/
*/
class StreamDB {
    static $table = "tab", $id_col = "id", $data_col = "data";
    protected $id, $data = "", $position = 0;
    function stream_open($path, $mode, $options, $opened_path) {
        $url = parse_url($path);
        $this->id = intval($url["host"]);
        if ($mode == "wb") {
            if ($this->id) {
                mysql_query("INSERT INTO " . self::$table . " (" . self::$id_col . ") VALUES ($this->id) ON DUPLICATE KEY UPDATE " . self::$data_col . " = ''");
            } else {
                mysql_query("INSERT INTO " . self::$table . " () VALUES ()");
                $this->id = mysql_insert_id();
            }
        } elseif ($mode == "ab") {
            mysql_query("INSERT INTO " . self::$table . " (" . self::$id_col . ") VALUES ($this->id)");
        } else {
            $row = mysql_fetch_row(mysql_query("SELECT " . self::$data_col . " FROM " . self::$table . " WHERE " . self::$id_col . " = $this->id"));
            if (!$row) {
                return false;
            }
            $this->data = $row[0];
        }
        return true;
    }
    function stream_close() {
        $this->data = "";
    }
    function stream_read($count) {
        $this->position += $count;
        return substr($this->data, $this->position - $count, $count);
    }
    function stream_write($data) {
        mysql_query("UPDATE " . self::$table . " SET " . self::$data_col . " = CONCAT(" . self::$data_col . ", _binary'" . mysql_real_escape_string($data) . "') WHERE " . self::$id_col . " = $this->id");
        return strlen($data);
    }
    function stream_eof() {
        return ($this->position >= strlen($this->data));
    }
    function stream_flush() {
    }
    function stream_stat() {
        return array("id" => $this->id, "size" => strlen($this->data));
    }
    function unlink($path) {
        $url = parse_url($path);
        mysql_query("DELETE FROM " . self::$table . " WHERE " . self::$id_col . " = " . intval($url["host"]));
        return mysql_affected_rows();
    }
    function rename($path_from, $path_to) {
        $url_from = parse_url($path_from);
        $url_to = parse_url($path_to);
        return mysql_query("UPDATE " . self::$table . " SET " . self::$id_col . " = " . intval($url_to["host"]) . " WHERE " . self::$id_col . " = " . intval($url_from["host"])) && mysql_affected_rows();
    }
    function url_stat($path, $flags) {
        return array();
    }
}

stream_wrapper_register("db", "StreamDB");
?>

Usage

<?php
copy("1.jpg", "db://1");
rename("db://1", "db://2");
copy("db://2", "2.jpg");
unlink("db://2");
?>

WGS84 distance

DELIMITER $$

CREATE
    FUNCTION `gps_distance`(lat1 FLOAT,lng1 FLOAT,lat2 FLOAT,lng2 FLOAT)
    RETURNS FLOAT
    BEGIN

    SET lat1 = lat1 * pi() / 180;
    SET lng1 = lng1 * pi() / 180;
    SET lat2 = lat2 * pi() / 180;
    SET lng2 = lng2 * pi() / 180;

    RETURN acos
    (   cos(lat1)*cos(lng1)*cos(lat2)*cos(lng2)
      + cos(lat1)*sin(lng1)*cos(lat2)*sin(lng2)
      + sin(lat1)*sin(lat2)
    ) * 6372.795;

    END$$

DELIMITER ;

Bring back relax no-strict SQLmode

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Install Debian

sudo apt install mariadb-server mariadb-client
sudo mysql_secure_installation