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 
    mysql_secure_installation
    

    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 \
      --permanent
    

    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';";
    mysql -uroot -pREPLACE_WITH_YOUR_ROOT_DB_PASS -e "SET SESSION sql_mode = 'STRICT_ALL_TABLES';";
    

    You can double check your modes by:

    mysql -uroot -pREPLACE_WITH_YOUR_ROOT_DB_PASS -e "SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;";
    

    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
    #MariaDB.
    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.