MySQL is the world’s most popular open source database, sporting a barrier of entry low enough to attract novice developers yet powerful enough to power some of the world’s most popular websites, among them Yahoo!, BBC News, the U.S. Census Bureau, and Craigslist. It is the world’s second most widely used open-source relational database management system (RDBMS). On first place, SQLlite, which is deployed with every iPhone and Android device along with the Chrome and Firefox browsers.
For next some posts, I will be covering all major topics you should learn to work better with MySQL. To keep informed about next updates, please subscribe to email subscription option.
In this post, I am starting with some configuration overview you will help you in debugging/setting MySQL server runtime configuration.
MySQL configuration for single server instance
MySQL server uses some default as well as custom options, when it is started. These options are also called configuration parameters. By default, MySQL server uses options specified in default location which is “/etc/my.cnf” in Linux systems and “C:Program FilesMySQLMySQL Server X.Ymy.ini” in Windows system. If you need to change any start-up configuration parameter, locate this file and add/update required option.
MySQL supports many configuration parameters, capable of controlling behavior regarding memory, logging, error reporting, and much more. While it’s possible to tweak these parameters by passing them as command line options when starting the MySQL server, typically you’ll want to ensure they’re always set at server start-up, done by adding them to the my.cnf/my.ini file.
prompt> mysqld --defaults-file="C:Program FilesMySQLMySQL Server 5.1my.ini"
MySQL Server command line options: http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html
[notice type=”info”]Please note that options specified in default location (“/etc/my.cnf” or “C:Program FilesMySQLMySQL Server X.Ymy.ini”) are in global scope i.e. even if you specify another configuration file in command line, this file will still be read.[/notice]
MySQL configuration for multiple server instances
If you have multiple instances of MySQL server and want to use different configuration for each one, then you can supply additional configuration parameters to each instance using another extra configuration file. This is done using command line parameter “-defaults-extra-file=name”.
prompt> mysqld --defaults-extra-file="C:myConf1.ini" prompt> mysqld --defaults-extra-file="C:myConf2.ini"
[notice type=”alert”]Each server starts in the foreground ( i.e. no prompt will appear until the server exits), so you will need to issue those two commands in separate console windows.[/notice]
Happy Leaning !!