Shulou(Shulou.com)06/01 Report--

Preface

In MySQL, there are many powerful tools, such as mysql, mysqladmin, mysqldump and so on. This blog post will write down the use of some command tools.

1. Mysql command

The Mysql command is the most frequently used command tool, providing users with a command line interface to operate and manage the MySQL server. You can see how to use it in detail through mysql-- help.

Mysql command option function description-u specifies the user used when connecting to the database-p specifies the user's password can be written directly after-p, or can not be written, enter the password interactively, recommend the latter-h to specify the host to log in optional, if empty, log in to the local machine-P to specify the port to connect optional, the default is 3306mure you can directly execute the SQL statement through the-e command Instead of entering the database and executing SQL statements without interactive login to the database, you usually use-D in the script to specify which library you want to log in to by default. You can omit this option and directly write the result of the query from the library name-E in a row to display something similar to each SQL statement followed by "\ G"-f even if there is a SQL error. It is also forced to continue, for example, to delete the library without logging in to the database, there will be an interactive confirmation operation. You can use this option to avoid interactive-X files exported from queried data bits xml files can be opened using the excel table in windows systems-H exported files from queried html files can be opened in windows systems using browsers-prompt customizes the content displayed at its own MySQL prompt and the prompt after logging in to MySQL is "mysql >" by default. You can use this option to customize the prompt-- tee records all inputs and outputs of the operation database into a file in case of some major maintenance changes, in order to be checked The output information during the entire operation can be saved to a file 1)-e,-u,-p,-h,-P, etc. Syntax [root@mysql] # mysql-uroot-p123.com-h 192.168.20.2-P3306 mysql-e "show tables " # the meaning of the above command is as follows: #-uroot: use root user #-p123.com: password is 123.com#-h: login host address is 192.168.20.login-P: login port is 330Secretmysql: log in to the library named mysql #-e: followed by SQL statement # Summary: connect to the 192.168.20.2 mysql library through port 3306 using the root user with password 123.com Query which tables are in the mysql library.

The above command returns the following result:

2)-- prompt usage [root@mysql ~] # mysql-uroot-p123.com-- prompt= "\ u @\ h:\\ d\ r:\\ m:\\ s >" # the above parameter solution:\ u indicates the user name,\ h indicates the host name, and\ d indicates the current database (none indicates that it is not in any library) #\ r 24-hour system\ r (12-hour system),\ m minutes,\ s seconds,\ R 24-hour system

The returned result is as follows:

In the above method, you have to write those characters to customize each connection, which is very troublesome. You can write them under the clinet field in the configuration file, and then log in and then omit them, as shown below:

[root@mysql ~] # vim / etc/my.cnf # Edit the main configuration file [mysqld]. # omit part of the content [client] # Note: write to the client field prompt= "\\ u@\\ h:\\ d\ R:\\ m:\\ s >" # Note: there is no need to restart the MySQL service, each client connection will re-read the configuration

If you connect again, you no longer need to specify it, as follows:

3)-- tee method # use the "--tee" option when connecting to the database, and log in to the database to execute some SQL statements [root@mysql ~] # mysql-uroot-p123.com-- tee=/tmp/opt.logroot@localhost: (none) 14:46:45 > show database;root@localhost: (none) 14:46:54 > show databases

As follows (pay attention to the prompt and error message printed on the screen):

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

You can see from the above file that all the operation commands and output information made by the user after connecting to the database are recorded.

Similarly, the configuration item "--tee" can also be written under the client field in the main configuration file my.cnf, as follows:

4) how to use the-H option [root@mysql ~] # mysql-H-uroot-p123.com-e "select * from mysql.user" > a.html# redirects the output of the query to the a.html file [root@mysql ~] # sz a.html# downloads this file to the local windows system

Open the downloaded file with a browser and display it 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 management. Such as MySQL Server status check, flush of various statistics, create / delete database, close MySQL Server and so on. Although most of the things that mysqladmin can do can be done after logging in to MySQL Server through a mysql connection, most of the operations can be done through mysqladmin.

It's convenient.

Mysqladmin can be followed by options or commands. Let's not talk about options here, but mainly about commands.

The command word functions as create databasename to create a library drop databasename to delete a library status query MySQL basic status (limited information displayed) extended-status query server detailed status information (similar to executing show status in a database ) flush-hosts refresh server cache flush-logs refresh binary log files (if binary logging is enabled So performing this operation will generate a new binary log file) flush-status refresh status variable flush-tables refresh all tables flush-threads refresh all thread cache flush-privileges reload authorization table processlist view all ID details of the current connection database kill id kills one or more connection ID (generally you need to use processlist to see the ID list first Then kill it according to ID) ping detects whether a MySQL service is in startup state password modifies user password shutdown closes MySQL service start-slave turns on master-slave replication stop-slave turns off master-slave replication variables query MySQL service version query version details of MySQL 1) use of the status command word [root@mysql data] # mysqladmin-uroot-p123.com status

The returned result is as follows:

2) the use of ping command words

Execute the ping command in the service startup state:

[root@mysql data] # mysqladmin-uroot-p123.com-h 192.168.20.2 ping

The returned result is as follows:

Stop the service before executing the ping command:

[root@mysql data] # mysqladmin-uroot-p123.com-h 192.168.20.2 ping

The returned result is as follows:

3) processlist, kill id,id.. Use of command words [root@mysql data] # mysqladmin-uroot-p123.com processlist

The returned result is as follows:

Use the kill command word to kill its connection to ID:

3 、 mysqldump

The function of this tool is to dump the data in MySQL Server into text files from the database in the form of SQL statements. Mysqldump is a logical backup tool for MySQL. In my previous blog post, there is how to use this tool: backup and recovery of MySQL.

4 、 mysqlbinlog

The main function of the mysqlbinlog program is to analyze the binary logs (that is, binlog) generated by MySQL Server.

With mysqlbinlog, we can parse the contents of the specified time period or the start and end of the log in binlog into SQL statements.

The method of use is as follows:

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

The returned result is as follows:

-this is the end of this article. Thank you for reading-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.