MySQL Configuration Tutorial

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.

MySQL

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

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.

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"
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.

Read More: http://dev.mysql.com/doc/refman/5.5/en/option-files.html

Happy Leaning !!

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

3 thoughts on “MySQL Configuration Tutorial”

  1. Hi Lokesh,

    Can we have a new section for Database, SQL and transaction as these are one important area of interview for Java developers.
    We can collect the most frequently asked questions related to database and SQL queries for senior developers and can discuss the same through your forum.

    Thanks a lot for your blogs as it help a lot of people to crack interviews at senior level.

    Reply
  2. How to work on created users. Say I have created user called siddu using below command.
    creat user siddu identified by ‘password’ then gave grant and all. then i want to create database, how to switch between default users to created user. Cloud you please explain

    Reply

Leave a Comment

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.