Setup MariaDB like Boss

[Emad R]

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).

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.