wiki.janforman.com

User Tools

Site Tools

You are here: start » mariadb
mariadb
no way to compare when less than two revisions

Differences

This shows you the differences between two versions of the page.


—mariadb [2019/10/21 18:25] (current) – created - external edit 127.0.0.1
Line 1: Line 1:
 +====== System variables ======
 +[[https://mariadb.com/kb/en/mariadb/server-system-variables/]]
 +
 +====== Performance report ======
 +<code>wget mysqltuner.pl</code>
 +
 +====== Find duplicates ======
 +<code>SELECT evcj, udalostid FROM log
 +GROUP BY udalostid,evcj HAVING count(udalostid) > 1</code>
 +
 +====== Remove duplicates by index ======
 +<code>ALTER IGNORE TABLE nazev ADD UNIQUE INDEX (a,b)</code>
 +
 +====== CVE-2016-6662 ======
 +Attaker must have user access to gain access.\\
 +How to block: Configuration files must be read-only for MySQL processes.
 +<code>
 +chown root:root /var/lib/mysql/
 +chown root:root /etc/mysql/my.cnf
 +</code>
 +
 +====== Some configuration chaos ======
 +<code>
 +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
 +</code>
 +
 +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 ======
 +<code>
 +<?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");
 +?>
 +</code>
 +
 +====== Usage ======
 +<code><?php
 +copy("1.jpg", "db://1");
 +rename("db://1", "db://2");
 +copy("db://2", "2.jpg");
 +unlink("db://2");
 +?></code>
 +
 +====== WGS84 distance ======
 +<code>
 +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 ;
 +</code>
 +
 +====== Bring back relax no-strict SQLmode ======
 +<code>
 +SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
 +</code>
 +
 +====== Install Debian ======
 +
 +<code>sudo apt install mariadb-server mariadb-client</code>
 +<code>sudo mysql_secure_installation</code>
  
mariadb.txt · Last modified: 2019/10/21 18:25 by Jan Forman

Page Tools