Jan 232015
 
Article MySQL

A system administrator or DBA may need to connect quite often to a MySQL database from the command line, to perform administration and optimization tasks, and such. The common way to connect is executing the mysql client, with a set of arguments to specify the user and password to use, as well as the host, database and port where the instance is listening.

This post explains how to specify de defaults for all these arguments in a configuration file, to avoid having to type it again and again, every time a new connection to the database needs to be made.

Typically, an interactive connection to the database from the command line is established with a call to mysql:

or else, using the short option syntax:

Besides, if the database is hosted in other server, is listening on a TCP port other than the default (3306), or uses a different communication protocol, additional parameters need to be specified:

All these parameters can be specified in a configuration file. mysql looks for configuration files in several locations, in order:

On Linux:

  • /etc/my.cnf – Global options
  • /etc/mysql/my.cnf  – Global options
  • SYSCONFDIR/my.cnf  – Global options
  • $MYSQL_HOME/my.cnf – Server level options
  • defaults-extra-file – A configuration file specified with the option –defaults-extra-file=path
  • ~/.my.cnf – User level options

On Windows:

  • %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf  – Global options
  • %WINDIR%\my.ini, %WINDIR%\my.cnf  – Global options
  • C:\my.ini, C:\my.cnf – Global options
  • INSTALLDIR\my.ini, INSTALLDIR\my.cnf  – Server level options
  • defaults-extra-file – A configuration file specified with the option –defaults-extra-file=path
  • %APPDATA%\MySQL\.mylogin.cnf – User level options

Defaults for the connection parameters (user, password, host, port, protocol,…) can be specified inside any of these configuration files, under a generic “[client]” option group. For instance:

The “[client]” group is used not only by the mysql client, but also by other utilities (p.ej., mysqldump)

It is also possible to create a group of options specific to a given utility, adding a section below the generic group:

In this example, mysqldump will connect to the database with user “dump_user”. The defaults for the other parameters will be taken from the [client] group

References

MySQL Reference manual – 4.2.6 Using option files

 Posted by at 6:07 pm

 Leave a Reply

(required)

(required)