Based on video from lullabot, the following settings in my.cnf file in mysql for drupal performance and handling:
Before beginning to start using MySQL it is recommended to run the script provided by MySQL to provide some security features.
The link is: /usr/bin/mysql_secure_installation . The location can vary. It basically requests for root password, disabling remote root login, remove anonymous users and other security setting.
Character Set: utf8. Drupal mostly expects the default character set to be utf8.
default_character_set=utf8
character_set_server = utf8
collation_server = utf8_general_ci
MySQL to cache read only queries:
query_cache_size = 64M
query_cache_limit = 2M
InnoDB uses row level locking as compared to MyISAM which uses table level locking. This is great for drupal as it involves greater no of writes to DB
default_table_type=InnoDB
The space allocated to InnoDB rto
innodb_buffer_pool_size = 4096M
Write InnoDB log file every second instead of at each write:
innodb_flush_log_at_trx_commit=0
--
Memory Allocation for InnoDB: http://www.rathishkumar.in/2017/01/how-to-allocate-innodb-buffer-pool-si...
Building a Drupal 7 Optimized my.conf File: https://groups.drupal.org/node/289613