1. mysql

Mysql is not a mysql service, but a client tool for mysql.

Grammar:

mysql [options] [database]
Copy the code

1.1 Connection Options

Parameters: -u, --user=name Specifies the user name -p, --password[=name] specifies the password -h, --host=name Specifies the SERVER IP address or domain name -p, --port=# Specifies the connection port example: Mysql -h127.0.0.1 -p 3306 -uroot -p mysql -h127.0.0.1 -p3306 -uroot -p2143Copy the code

1.2 Execution Options

-e, --execute=name Executes the SQL statement and exitsCopy the code

This option allows you to execute SQL statements on the Mysql client without connecting to the Mysql database, which is especially convenient for some batch scripts.

The sample

mysql -uroot -p2143 db01 -e "select * from tb_book";
Copy the code

2. mysqladmin

Mysqladmin is a client program that performs administrative operations. You can use it to check the configuration and current state of the server, create and delete databases, and so on. You can view the help documentation with the mysqladmin –help directive

Example:

mysqladmin -uroot -p2143 create 'test01'; 
mysqladmin -uroot -p2143 drop 'test01';
mysqladmin -uroot -p2143 version;
Copy the code

3. mysqlbinlog

Because the binary log files generated by the server are stored in binary format, if you want to check the text format of the text, you use the mysqlBinlog log management tool.

Grammar:

mysqlbinlog [options] log-files1 log-files2 ... Options: -d, --database=name: specifies the database name and lists only the specified database-related operations. -o, --offset=# : The first n lines in the log are ignored. -r,--result-file=name: Outputs text logs to a specified file. -s, --short-form: Displays a simple format, omitting some information. --start-datatime=date1 --stop-datetime=date2: all logs within the specified date interval. --start-position=pos1 --stop-position=pos2: specifies all logs within the position interval.Copy the code

4.mysqldump

The mysqlDump client tool is used to back up databases or migrate data between different databases. The backup contains SQL statements for creating tables and inserting tables.

Grammar:

mysqldump [options] db_name [tables] mysqldump [options] --database/-B db1 [db2 db3...]  mysqldump [options] --all-databases/-ACopy the code

4.1 Connection Options

Parameters: -u, --user=name Specifies the user name -p, --password[=name] specifies the password -h, --host=name Specifies the server IP address or domain name -p, --port=# Specifies the connection portCopy the code

4.2 Output content options

Parameter: --add-drop-database Add a DROP DATABASE statement to each database create statement --add-drop-table Add a drop table statement to each table create statement, default enabled; -n, --no-create-db does not contain database create statement -t, --no-create-info does not contain table create statement -d --no-data does not contain data -t, -- TAB =name Automatically generates two files: a. SQL file, create table structure statements; A.txt file is a data file that is equivalent to select into outfileCopy the code

Example:

mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
mysqldump -uroot -p2143 -T /tmp test city
Copy the code

5.mysqlimport / source

Mysqlimport is a client data import tool, used to import the text file mysqldump with -t parameter.

Grammar:

mysqlimport [options] db_name textfile1 [textfile2...]
Copy the code

Example:

mysqlimport -uroot -p2143 test /tmp/city.txt
Copy the code

If you need to import a SQL file, you can use the source command in mysql:

source /root/tb_book.sql 
Copy the code

6. mysqlshow

Mysqlshow client object lookup tool, used to quickly find which databases, tables in the database, columns in the table, or indexes exist.

Grammar:

mysqlshow [options] [db_name [table_name [col_name]]]
Copy the code

Parameters:

--count Displays statistics about databases and tables. -i Displays status information about a specified database or tableCopy the code

Example:

#Query the number of tables and the number of records in the table for each database
mysqlshow -uroot -p2143 --count
#The querytestThe field book in each table in the library, and the number of rows
mysqlshow -uroot -p2143 test --count
#The querytestDetails of the book table in the library
mysqlshow -uroot -p2143 test book --count
Copy the code