Configuring the parameters

After installation, the basic things you need to configure are covered in this section. The rest all can be left as default or tuned later according to the load.

data directory

Data managed by the MySQL server is stored under a directory known as the data directory. Each sub-directory of the data directory is a database directory and corresponds to a database managed by the server. By default, the data directory has three sub directories:

• mysql: MySQL system database
• performance_schema: Provides information used to inspect the internal execution of the server at runtime
• sys: Provides a set of objects to help interpret performance schema information more easily

Apart from these, the data directory contains the log files, InnoDB tablespace and InnoDB log files, SSL and RSA key files, pid of mysqld, and mysqld-auto.cnf, which stores persisted global system variable settings.

To set the data directory change/add the value of datadir to the config file. The default is /var/lib/mysql

You can set it to wherever you want to store the data, but you should change the ownership of the data directory to mysql.

innodb_buffer_pool_size

This is the most important tuning parameter that decides how much memory the InnoDB storage engine can use to cache data and indexes in memory. Setting it too low can degrade the performance of the MySQL server, and setting it too high can increase the memory consumption of MySQL process. The best thing about MySQL 8 is that innodb_buffer_pool_size is dynamic, meaning you can vary innodb_buffer_pool_size without restarting the server.

innodb_buffer_pool_instances

You can divided the InnoDB buffer pool into separate regions to improve concurrency, by reducing contention as different threads read and write to cached pages. For example, if the buffer pool size is 64 GB and innodb_buffer_pool_instances are 32, the buffer is split into 32 regions with 2 GB each.

innodb_log_file_size

This is the size of the redo log space used to replay committed transactions in case of a database crash. The default is 48 MB, which may not be sufficient for production workloads. To start with, you can set 1 GB or 2 GB. This change requires a restart. Stop the MySQL server and make sure that it shuts down without errors. Make the changes in my.cnf and start the server. In earlier versions, you need to stop the server, remove the log files, and then start the server. In MySQL 8, it is automatic.