mysql> SELECT * FROM performance_ssion_variables WHERE variable_name LIKE 'sort_buffer_size' +-+-+ġ row in set (0.00 sec) Persisting Configuration Parameters:Ĭheck the sort_buffer_size from the performance schema, the session variables and the global variables table. So we can use the Select to query the global value mysql> SELECT | We were using the same client connection all the way so with the same global we changed the default for new sessions but our session is not new, our session started before the change of the default. It didn’t change because the sort_buffer size has both global and session-level scope and the global is just a default for new sessions. mysql> SET GLOBAL sort_buffer_size = 512 * 1024 I will use sort_buffer_size for this example because this has global and session-level scope, so this will explain the scope very well. Session: Applies only to the current connection (session) Global: Applies to all the new connections There are two types of variables based on the scope of the variable: The server executes the SQL statements in that file before starting Using global and session variables: You can see that the –init-file parameter is passed to the server. Rather than changing it in the config file # /usr/local/mysql/bin/mysqld -basedir=/usr/local/mysql -datadir=/usr/local/mysql/data -plugin-dir=/usr/local/mysql/lib/plugin -user=mysql -log-error=/usr/local/mysql/data/centos7.err -pidfile=/usr/local/mysql/data/centos7.pid -init-file=/tmp/mysql-init & Suppose you wish to start MySQL using a startup script and not through systemd,Įspecially for testing or for some temporary change. | 2| Using parameters with the startup script: Server-id=2 (edit this parameter in config file) ~]# mysql -e 'select |Ĭhange the server-id parameter to 2 and restart the mysql server #vim /etc/my.cnf : Read by the mysqld_safeprocess (MySQL Server Startup Script).: The section is read by the backup utility called mysqldump.: Section is read by all connecting clients (including mysql cli).

: Section is read by the mysql command-line client.

The configuration file has the below sections, the related parameter should be kept under them. The config file can be found in /etc/my.cnf (RHL and CENTOS) and /etc/mysql/my.cnf(Debian), you can edit this file in your choice of editor. Variables can be set through the following:

Static, which takes effect after restarting MySQL server Dynamic, which can be changed online without restarting MySQL server works in version 5.7 and later.

Toad for sql server mysql schema