Invoking MySQL Client Programs

Invoking MySQL Client Programs

The MySQL Client programs are used for connecting the MySQL Server for accessing and managing the activities of MySQL Databases there are different client programs

  • mysql
  • mysqladmin
  • mysqlimport
  • mysqldump

 

mysql

mysql is a client program which is used to connect the MySQL Server and access the data from the databases present in the MySQL Server and it used for connecting the local server where the mysqld server program is running and also can be used for connecting from the remote severs to access the MySQL Server by using various switches with the mysql Program it can only be used for accessing the data by connecting to the MySQL databases but we cannot perform any administrative tasks.

To find the available switches that can be used in the mysql program the command is

The most commonly used switches are

mysql –u (which is to specify username as which user we are trying to connect)

mysql –p (this option is to specify the password but don’t specify in the command specify it after prompting to specify password as unix commands are stored in the history)

mysql –P (this is to specify to connect mysqld which is running on a different port)

mysql –h (It is used for connecting the MySQL Server which is running on different host)

The above command will list all the options available for mysql program that can be used with two types of formats

1 Short Format

2 Long Format

Connecting to mysql program using short format

Connecting to mysql program using long format

The above both short and long format works same but which one to use is depends on the user flexibility

How to find the no of databases

To switch a particular database

To find version of mysql

To find the detailed description about mysql program

Types of modes used in mysql:

  • Interactive mode
  • Direct mode

In Interactive mode the mysql commands are executed in the mysql prompt which is used for day to day operations here we can execute single command at a single point time.

In Direct mode we can execute the mysql commands from the operating system prompt directly and also we can execute multiple commands at a single point of time.

The above command will first connect to the mysql prompt and then executes show databases command.

The above command will first connect to mysql prompt then it switches to the database name that you have specified after the use and selects the value from t1 table and t2 table this is the direct mode of accessing the data from the operating system prompt.

Load SQL Statements / SQL backup files into MySQL:

There is script file which contains bulk of SQL Statements which need to load into the database and it can be performed using two ways one is loading each statement into the database and loading all the statements at once by using a script file

We can load the script files into database using two ways

  • Source
  • Import from linux prompt

First connect to MySQL Sever

Now execute the script using source command by specifying the location of the script file along with the file name assume my script file is available at /var/log/mysql with file name world.sql

now if we want to see what statements are executing during the loading operation we cannot see it in the foreground as the script is running but it only prints the result set but not the query to monitor what statements are executing while we are loading the script we need to enable general log .

To find out general log is enabled or disabled and the location of general logfile  execute the below command

open one more terminal and open the general logfile using tail command for displaying the new inserted statements

Now from one more terminal try to load data using source command and observe the file which you opened with tail command in another session there you can see the Statements executing during the loading operation of the script file.

tee

The tee is a command used in mysql to redirect all the statements and output of the statements into a logfile it is a kind of logging all the statements and result set into logfile and this tee works at the session level once the session is closed we need to enable it again.

To enable tee execute the below command

To disable tee execute the below command

Query Terminator

Every SQL Statement ends with semicolan (;) and it is also known as query terminator and in mysql we can also use \g at the end of the statement which is same as semicolan (;)

The above both commands terminated with semicolon (;) and \g will give the output in the same format like tabular format

The above command which is used with the terminator \G will print the output in vertical format and it is used when there are too many columns.

mysqladmin

mysqladmin is a client program which can be used for performing Administrative tasks by Database Administrators like ping the server, shutdown the server, create and drop the database, setting root password, changing root password, monitoring mysql processes, reloading grant tables

To get complete options from mysqladmin

How to check MySQL server is up and running

How to Find out which version of MySQL we are running

How to find current status of MySQL Server

How to find all the MySQL Server system variables and values

How to find running process and queries in MySQL Database

How to create a MYSQL Database

How to drop a MySQL Database

How to reload privilege or grant table

Thank you ……….

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Add Comment