mysql configuration file location linux ubuntu debian
/etc/mysql/mysql.cnf
mysql configuration file location in windows
(/usr/local/mysql-router/mysqlrouter.conf)
/Users/philip/.mysqlrouter.conf
mysql configuration file location mac
/etc/mysql/my. cnf.
centos fedora redhat mysql config file location
/etc/mysql/my. cnf.
validate mysql config file or syntax error check
There is a way check syntax errors in mysql.cnf file
MySQL 8.0.16, a new option called ‘validate-config’ has been introduced to help users to quickly test the server configuration without having to run the server.
mysqld –validate-config
root@-s-4vcpu-8gb-blr1-01:~# mysqld –validate-config
root@-s-4vcpu-8gb-blr1-01:~#
just like nginx and apache nginx -t ,
check mysql server config file variable in command line
mysql show variables
mysql -u root -p
mysql> SHOW VARIABLES;
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 134217728 |
+————————-+———–+
1 row in set (0.01 sec)
mysql>
after restart
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 |
+————————-+———–+
1 row in set (0.01 sec)
Start / stop / restart Reload mysql config file server
service mysql reload
systemctl reload mysqld
/etc/mysql/my.cnf error
service mysql start
service mysql stop
service mysql restart // ubuntu debian
service mysqld restart
/etc/init.d/mysqld restart
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with ‘.cnf’, otherwise they’ll be ignored.
#
!includedir /etc/mysql/conf.d/
mysql default configuration values in mysql 8
mysql sample configuration
[mysql]
default-character-set=utf8mb4
[mysqld]
default_authentication_plugin= mysql_native_password
# Skip reverse DNS lookup of clients
skip-name-resolve
#Disable all remote connections
skip-networking
#innodb_buffer_pool_size = 1073741824
#innodb_log_file_size = 256M # 25% of buffer pool size
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M # 25% of buffer pool size
open_files_limit = 30000 # would be reasonable
table_open_cache = 8000
tmp_table_size = 128M
max_heap_table_size = 128M
innodb_write_io_threads= 4
innodb_read_io_threads = 8
join_buffer_size = 512
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 1
[mysqld]