Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
SHOW VARIABLES can imply some of it: may be useful to know... –  gbn Jan 2 '12 at 13:07
    
You mean I can use SHOW VARIABLES to see some of mySQL variables that I can configure. Correct me if I'm wrong @gbn. –  john.locke Jan 2 '12 at 13:14
    
correct: my.cnf sets variables that can be read via SHOW. Some can be changed via SET too for testing without changing my.cnf. Note: you can't tell the difference between defaults and what is explicitly set in my.cnf –  gbn Jan 2 '12 at 13:17

1 Answer 1

up vote 3 down vote accepted

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

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.

share|improve this answer
    
Really informative, thanks. I've checked my Cpanel, it seems that some hosting providers remove those two tables (Global & Session) from their INFORMATION_SCHEMA DB! –  john.locke Jan 3 '12 at 3:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.