Setup MariaDB like Boss

  • Hi,

    quick guide on how to setup MariaDB like boss 😉 I use Centos 7, but you can use whatever you want really.

    yum install -y mariadb-server
    systemctl enable mariadb 
    systemctl start mariadb 

    Click no when first asked if your MariaDB installation has root password already, to create it. Then let us create rule on the firewall.

    firewall-cmd --zone=public --add-service=mysql \

    There is bug with mysql_secure_installation, even if you pressed No to allow remote connections from root, it will still disallow it, also it is better to just answer "Y" to every question in mysql_secure_installation, even if you wish to remote manage it (you can use mysql work bench to manage it and connect via ssh tunnel from different machine as seen below).

    0_1517769162164_2018-02-04 20_32_30-MySQL Workbench.png

    Lets enable SQL STRICT MODE, so the database is more strict with dealing with invalid data.

    mysql -uroot -pREPLACE_WITH_YOUR_ROOT_DB_PASS -e "SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';";

    You can double check your modes by:


    To backup your selected DB + triggers + events:

    mysqldump --add-drop-database --events --triggers --routines -uroot -pREPLACE_WITH_YOUR_ROOT_DB_PASS  --databases contacts_db > backup.sql

    To Restore:

    mysql -uroot -pREPLACE_WITH_YOUR_ROOT_DB_PASS < backup.sql;

    To Optimize your DB

    Use one of the ready tempates mysql/mariadb creates for windows, here are they:

    nano /etc/my.cnf

    and add under [mysqld] only one of those below or customize it to your hardware

    # # SMALL
     # This is for a system with little memory (<= 64M) where MySQL is only used
    # from time to time and it's important that the mysqld daemon
    # doesn't use much resources.
    key_buffer_size = 16K
    max_allowed_packet = 1M
    table_open_cache = 4
    sort_buffer_size = 64K
    read_buffer_size = 256K
    read_rnd_buffer_size = 256K
    net_buffer_length = 2K
    thread_stack = 240K
    # # MEDUIM
    # This is for a system with little memory (32M - 64M) where MariaDB plays
    # an important part, or systems up to 128M where MariaDB is used together with
    # other programs (such as a web server)
    key_buffer_size = 16M
    max_allowed_packet = 1M
    table_open_cache = 64
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    # # LARGE
    # This is for a large system with memory = 512M where the system runs mainly
    # MariaDB.
    key_buffer_size = 256M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    # # HUGE
    # This is for a large system with memory of 1G-2G where the system runs mainly
    key_buffer_size = 384M
    max_allowed_packet = 1M
    table_open_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 32M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8

    And you can go on and increase depending on your hardware, using the above as template.