Programs such as ‘mysql’ and ‘mysqldump’, that use MySQL client library have the support of MySQL connection to server with the help of many transport protocols, such as TCP/IP, Unix socket file, named pipe, shared memory, and so on. Let us understand the connection transport protocols used with MySQL −The below table shows the values permitted for --protocol and also tells the platforms where each of these values are applicable. It is to be noted that the values are not case-sensitive.--protocol valueTransport protocol usedApplicable platformsTCPTCP/IPAllSOCKETUnix socket fileUnix and Unix-like systemsPIPENamed pipeWindowsMEMORYShared memoryWindowsTCP/IPTCP/IP transport supports connections to local or remote MySQL ... Read More

Let us see how command line options can be used to establish connection with the MySQL server for clients like mysql or mysqldump.For a client program to be able to connect to the MySQL server, it must use proper connection parameters, like the name of the host where the server is running, the user name and password of the MySQL account. Every connection parameter has a default value, but it can be overridden when necessary using program options specified on the command line or in an option file.Invoke mysqlCommand to invoke mysql without specifying any explicit connection parameters is −mysqlSince ... Read More

Let us see the options that are supported by MySQL client programs which control how client programs establish connections to the server, whether connections are encrypted, compressed or not.These options can also be given on the command line or in an option file. The below mentioned command options can be used for connection establishment −--default-auth: It is the authentication plugin that needs to be used.--host: It is the host on which MySQL server is located.--password: It is the password that needs to be used when connecting to server.--pipe: It is used to connect to the server using named pipe. This ... Read More

Let us understand the default options, the options that expects values, and the ‘=’ sign in MySQL −By convention, the long forms of options which assign a value are written using an equals (=) sign. It has been shown below −mysql --host=tonfisk --user=jonFor options which require a value, i.e which doesn’t have a default value, the equal sign isn’t required. This means that the below command would be valid in such cases −mysql --host tonfisk --user jonIn both the above cases, the mysql client tries to connect to a MySQL server that is running on the host named “tonfisk” with ... Read More

Many of the MySQL programs have internal variables that are set during runtime using the SET statement. Most of these program variables can also be set at server startup with the help of the same syntax that applies to specifying the program options.Example 1The mysql has a max_allowed_packet variable which controls the maximum size of its communication buffer.To set this max_allowed_packet variable for mysql to a value of 16MB, either of the below mentioned commands can be used −mysql --max_allowed_packet=16777216 (or) mysql --max_allowed_packet=16MThe first command specifies the value in terms of bytes. On the other hand, the second command specifies ... Read More

Some options are “boolean” and control the behavior which can be turned on or off.ExampleThe mysql client supports a --column-names option which tells whether or not to display a row of column names at the beginning of the query results.By default, this option is enabled. But sometimes, it may be required to disable this. This could be while sending the output of mysql into another program which is expecting to see only data and not the initial header line.To disable column names, the options can be specified in any of the below mentioned forms −Query--disable-column-names (or) --skip-column-names (or) --column-names=0The --disable ... Read More

Let us understand how MySQL command line options affect option file handling −Many of the MySQL programs which support option files handle the below options. Since these options affect option-file handling, they must be provided on the command line and not in an option ile. For it to work properly, each of these options must be provided before other options, with the below mentioned exceptions −−−print−defaults should be used immediately after −−defaults−file, −−defaults−extra−file, or −−loginpath.On Windows, if the server startup is done with the --defaults-file and --install options, --install must be first.--defaults-extra-file=file_nameOn Unix, read the above line in option file ... Read More

Let us understand how option files can be used with MySQL programs −Most MySQL programs can read the startup options from option files, which is also known as the configuration files.Option files provide an easy way to specify commonly used options so that they need not be entered on the command line every time the user runs a program.To know whether a program reads option files or not, it can be invoked with the help of the −−help option.For mysqld, the −−verbose and –help can be used.If the program reads option files, the help message indicates the files that it ... Read More

Let us understand how to use options on command line for MySQL programs −The program options which are specified on the command line follow the below rules −The options are given after the command name.An option argument begins with one dash or two dashes, and this depends on whether it is a short form or long form of the option name.Many options have both short and long forms. Let us take an example to understand this − −? and −−help are the short and long forms of the option which instructs a MySQL program to display the help message.The option ... Read More

Let us understand how to specify options in MySQL programs. There are many ways in which options can be specified for MySQL programs −Command LineThe options on the command line after entering the program name have to be listed. This is a common step for options which applies to a specific invocation of the program.Option fileThe options in an option file which the program reads when it starts also needs to be listed. This is a common step for options that the user needs the program to use every time it runs.Environment VariablesThe options in environment variables need to be ... Read More