Introduction to using MySQL's own tools

Keywords: Operation & Maintenance MySQL mysqladmin Database SQL

In MySQL, there are many powerful tools, such as mysql, mysqladmin, mysqldump, etc.
1. mysql command
The Mysql command is the most common command tool used to provide a command line interface for users to operate and manage MySQL servers.You can see how it's used in more detail through mysql --help.

1) Grammar used for -e, -u, -p, -h, -P, and so on

[root@mysql ~]# mysql -uroot -p123 -h -P3306 mysql -e "show tables;";
# The meaning of the above command is as follows:
# -uroot: Use root user
# Password
# -h: The host address for login is
# -P: The port to log on is 3306
# mysql: Log in to a library named mysql
# -e: followed by the SQL statement
# Summary: Using root user with password, connect mysql library through port 3306 to query which tables are in MySQL library.

The results are as follows:

2) --prompt usage

[root@mysql ~]# mysql -uroot --prompt="\\u@\\h: \\d \\r:\\m:\\s> "
#The above parameters are as follows: \u for user name, \h for host name, and \d for current database (none for no library);
# \r Hour 24-hour systemr Hour (12-hour system), \m minutes, \s seconds, \r Hour 24-hour system

The results are as follows:

It is cumbersome to write those characters to customize each connection in the way described above, which can be written to the clinet field in the configuration file and then omitted by login, as follows:

[client]      #Note that it is written in the client field
prompt="\\u@\\h: \\d \\R:\\m:\\s> "         #Write to this line
# Note: There is no need to restart the MySQL service, the configuration will be re-read every time the client connects
             ..........#Omit some content
#Remember to restart the service yo

Connect again without specifying any more, as follows:

3) --tee usage

[root@mysql ~]# mysql -uroot -p123 --tee=/tmp/opt.log
root@localhost: (none) 22:48:18> show database;
root@localhost: (none) 22:48:24> show databases;

As follows (note the prompts and errors for screen printing):

Now look at the log file specified by --tee as follows:

You should see from the above file that all the commands and output information the user performs after connecting to the database are recorded.

Similarly, the configuration item "--tee" can be writtenMy.cnfIn this main configuration file, under the client field, are the following:

[root@mysql ~]# vim /etc/my.cnf 
[client]                    ...........Omit some content
prompt="\\u@\\h: \\d \\R:\\m:\\s> "

4)-H option usage

[root@mysql ~]# mysql -H -uroot -p123 -e "select * from mysql.user" > a.html
#Redirect the results of the query to an output a.html file
[root@mysql ~]# sz a.html 

Open the downloaded file with your browser as follows:

Similarly, the -X option is used in the same way.
2. mysqladmin command tool
mysqadmin, as its name implies, provides a variety of functions related to mysql administration.Examples include MySQL Server status checks, flush of various statistics, creating/deleting databases, closing MySQL Server, and so on.What mysqladmin can do, although most of it can be done after you log in to MySQL Server via a mysql connection, most of it is easier and easier to do through mysqladmin
mysqladmin can be followed by options or commands, not to mention options, but mainly commands

1) Use of status command word

[root@mysql ~]# mysqladmin -uroot -p123 status

The results are as follows:

2) Use of the ping command word
Execute the ping command when the service is started:

[root@mysql ~]# mysqladmin -uroot -p123 -h ping

Stop the service before executing the ping command:

[root@mysql ~]# mysqladmin -uroot -p123 -h ping

3) Use of processlist, kill id,id..Command word

[root@mysql ~]# mysqladmin -uroot -p123 processlist

Use the kill command word to kill its connection ID:

[root@mysql ~]# mysqladmin -uroot -p123 kill 5

The function of this tool is to dump data from MySQL Server into a text file in the form of a SQL statement from the database.mysqldump is a logical backup tool for MySQL that I used in my previous blog: MySQL Backup and Recovery Details
The main function of the mysqlbinlog program is to analyze the binary logs (that is, binlogs) generated by MySQL Server.
With mysqlbinlog, we can parse the contents of the binlog for a specified time period or the starting and ending locations of the log into SQL statements.

[root@mysql data]# mysqlbinlog binary_log.000012   #Just specify the binary log file

Posted by Spudgun on Tue, 26 May 2020 10:54:08 -0700