Common tools in MySQL

MySQL: Client Connection tool

-U,--user=name???? Specify User name

-P--password???????? Specify password

-h–host=hostname Specify the server IP or domain name

-p–port=???????????? Specify port

Of course, you can also specify the user name and password in the configuration file, so that you do not need the connection plus parameters

[Client]

User=root

Password=redhat

-E,--execute=commands;commands???????? Execute SQL statement and exit

-E,--vertical???????????????? As with the \g effect in the MySQL Shell execution command tail

-S,--silent???????????????? Remove the wireframe display from MySQL

-f,--force???????????????? Enforces SQL, usually exits when an error is encountered in the middle

-v,--verbose???????????????? Show more information, typically used to view error messages displayed

--show-warnings???????????? Displays a warning message that displays a warning message if the sql_mode is not strictly emptied, such as a string that is too long causing the insertion to be truncated, causing an error message to be inserted

When you bulk execute data that may contain syntax errors or data errors, you can ensure the correct insertion of the data through the-F,-v 、--show-warnings.

Myisampack: table Compression tool, using a high compression rate makes the compressed table much smaller than the original (40% to 70%). However, the compressed table becomes a read table .

Myisampack???? FileName???? #太小的不给压缩的

Mysqladmin:MySQL management tool

Ping???? Detects if the MySQL server is alive. Returns 0 surviving even if password access is not used. Return 1dead

Processlist???? List the Active server threads

Status???????? Displays the MySQL server simple information: Uptime,thread (the client's thread number), questions (number of queries since the child server started), slow queries (number of queries over Long_query_time), opens ( Number of tables opened by the server), open tables (number of currently open tables)

Example: uptime:205 threads:1 questions:14 Slow queries:0 opens:15 Flush tables:1 Open tables:8 queries per second avg:0 .68

More Man mysqladmin very detailed

mysqlbinlog???? Log Management Tools

Mysqlbinlog [Options] Log_file

--database=db_name,-D db_name Specify the database name, listing only the specified database-related operations

--offset=n,-o n skips the first n rows

--result-file=name,-r name outputs the output text format log to the specified file

--short-form,-S Lite mode

--set-charset=charset_name Add set names charset_name option???? To make the output result to the development file

--start-datetime=datetime (= "2015-12-25 11:25:56")--stop-datetime=datetime

Displays log information for the specified time period

--START-POSITION=N-STOP-POSITION=N displays log information in the specified location, more accurate than datetime

Mysqlcheck(Myisam Table Maintenance tool, do not stop MySQL, but Myisamchk must stop the database):

Mysqlcheck [Options] [db_name [Tbl_name ...]

-C--check (test meter)

-R--repair (repair table)???????? InnoDB not supported

-A--analyze (analysis table)???????? InnoDB not supported

-O--optimize (optimized table)???????? InnoDB not supported

Mysqldump(Data export tool)

mysqldump [Options] db_name [tbl_name ...]

--add-drop-database???????? Add drop database before creating an app for each db

--add-drop-table???????????? Add drop table before creating statement for each table

It is automatically added by default, so when you import the backup data into a new database, make sure that the data in the new data is useless.

-N,--no-create-db???????????? Create statement that does not contain a database

-T,--no-create-info???????? Create statement that does not contain a data table

-D,--no-data???????????????? does not contain data

--campact???????????????????? Does not include annotations in the default options

-C???????????????????????????? The default is field information that does not contain insert. -C, plus field information

In the export of data to a file or directory, then this directory and file must be running MySQL user can write , or will be error

Mysqldump-predhat test2-t/mnt

Mysqldump:got error:1: Can ' t create/write to file '/mnt/tm.txt ' (errcode:13) when executing ' SELECT into OUTFILE '

drwxr-xr-x. 3 root root 4096 Dec 8 11:40/mnt/

-T,--tab???? Backs up data and establishes table statements. TAB key between data

--fields-terminated-by Field Delimiter

--fields-enclosed-by???? Domain reference, parcel data segment

--fields-escaped-by???? Set character to escape special characters, such as tab, line break

--default-charater-set=name and--tab options conflict

- F – flush-logs???? The backup closes the old log first, generating a new journal. Make recovery time directly from the new log to redo, greatly facilitate the recovery process

- L – lock-tables???? Can be used during backup so that data cannot be updated, so that the backed up data remains consistent and can be used with the-f option.

Mysqlhostcopy (Hot Backup tool for MyISAM table)

Mysqlhotcopy db_name

Mysqlhotcopy is a Perl script that uses the lock Tables,flush TABLES,CP or SCP to quickly back up a database. It is the quickest way to back up a database or a single table, with the disadvantage of supporting only the backup of the MyISAM table and allowing it only on Linux.

Install package dependency: perl-dbd-mysql-4.013-3.el6.x86_64

-p password???? Specify a password with a space in between

-U???????????? Specify User name

--allowold If the backup path contains a backup with the same name, rename the old backup directory to the directory name _old

--addtodest???? If a directory with the same name exists under the backup path, only the new file is added to the directory.

--flushlog???????? Refresh log after table is locked

--noindices???????? Do not back up all index files

Mysqlimport ( Data import Tool)

Mysqlimport-predhat --local test2 emp.txt

Provides a tool for client data import to import a text file after the mysqldump plus-t option is imported. is actually a command-line interface for the load data INFILEQL statement provided by the client

Mysqlshow(Database object viewing tool)

mysqlshow [Options] [DB_NAME [table_name [column_name]]

Mysqlshow – Predhat does not specify a database name, all databases are displayed

Displays all indexes in the specified table;-K--keys

Show some states of a table – I – Status

Perror(Error code viewing tool)

perror option Error_no

Replace(text replace)

Replace from to--, < file

--, Replace and modify the source file

<, replace does not modify source file, only output terminal

[Email protected] mnt]# Replace WXL WXL--Test

Test converted

[email protected] mnt]# cat test

WXL Qiandan

[[Email protected] mnt]# Replace Qiandan Dragon < test

WXL Dragon

[email protected] mnt]# cat test

WXL Qiandan

Common tools in MySQL