Is there a way to find my.cnf in shared hosting?

Question

I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?

Answer 1

In a shared hosting environment, you cannot access my.cnf

You can see the variables see other ways:

SHOW VARIABLES

• SHOW VARIABLES; will show all variables that are set. Some can be dynamically changed within you session with the SET command. Some can be set in the server so that new DB Connections going forward can use the newly set value.

  • SHOW VARIABLES LIKE 'innodb%' shows all innodb settings
  • SHOW VARIABLES LIKE '%timeout' shows all timeout setting
  • SHOW VARIABLES LIKE '%buffer%' shows all buffers

INFORMATION_SCHEMA

You can also access the variables by means of the INFORMATION_SCHEMA database. The tables to access those values are

• INFORMATION_SCHEMA.GLOBAL_VARIABLES
• INFORMATION_SCHEMA.SESSION_VARIABLES

The same requests using these tables are

• SHOW VARIABLES; becomes SELECT * FROM information_schema.global_variables;
• SHOW VARIABLES LIKE 'innodb%' becomes SELECT * FROM information_schema .global_variables WHERE VARAIBLE_NAME LIKE 'innodb%';
• SHOW VARIABLES LIKE '%timeout' becomes SELECT * FROM information_schema .global_variables WHERE VARAIBLE_NAME LIKE '%timeout';
• SHOW VARIABLES LIKE '%buffer%' becomes SELECT * FROM information_schema .global_variables WHERE VARAIBLE_NAME LIKE '%buffer%';

The reason INFORMATION_SCHEMA.SESSION_VARIABLES exists is to see the setting your session has, in the event you change a variable during the session to something different form the global setting.

Examples

• SET GLOBAL max_connections = 1000; will now allow 1000 DB Connections
• SET sort_buffer_size = 1024 * 1024 * 8; sets your session sort_buffer_size to 8M
• SET GLOBAL sort_buffer_size = 1024 * 1024 * 8; sets session sort_buffer_size to 8M for all incoming DB Connections going forward

CAVEAT

Please keep in mind that not all settings can be dynamically changed. Some values, such as sort_buffer_size (session or global) and max_connections (global only [session is N/A]), such as innodb_buffer_pool_size and lower_case_table_names, can only be changed by setting it in my.cnf and restarting mysqld.

Click here for the List of Variables, their defaults, and their session chacateristics.