MySQL Development advanced Article series 31 Tools (MySQL connection tool and MyISAM table compression tool)

A. mysql Connection tool

Among the tools provided by MYSQ, the DBA uses MySQL most frequently. MySQL here refers to the client tool that connects the database.

1.1 Connection options

-U,--user=name specified user name
-P,--password[=name] Specify password
-H,--host=name Specify the server IP or domain name
-P,--port=# specify the connection port

In the actual connection, the command is as follows:mysql-u User name-~]#  mysql-u root-p

--~]#  172.168.  18.2013306Enter password:welcome to the MySQL Monitor.  Commands End With; or \g.

1.2 Execution Options
Execute SQL statements and exit with "-e,--execute=name" This option does not need to be connected to the MySQL database, and for some batch scripts it is convenient to query the user and host fields of the user table in the MySQL database directly from the client:

[[email protected] ~]#  mysql-u root-p  "select User,host from User"

　　　　
Concatenate multiple SQL statements separated by semicolons such as select User,host from User; Select XXX.

Two. Myisampack (MyISAM table compression tool)

Myisampack is a table compression tool. Before compressing, you need to confirm that the mysqld is closed or that the table you want to compress does not have additional SQL operations, and that the compression process consumes CPU resources, which is recommended in the idle state of the server.

You can compress the data file to 40%-70%, and when you use the table later, the information that is required to extract the columns is read into memory. Performance is better when accessing specific records because you only need to decompress one record. The compressed table will also become a read-only table and cannot be DML-operated.

-- before compressing, look at the data file size of the table in the test library, where a table is MyISAM type, and the MyD file will be compressed

[Email protected] ~]# Cd/usr/local/mysql/data

[ Email protected] data]# CD test [[email protected] test]#ls-Total ltr Dosage --rw-r-----1MySQL MySQL AAugust -  -: +db.opt-rw-r-----1MySQL MySQL8566August -  -: ina.frm-rw-r-----1MySQL MySQL1024x768August -  -: +a.myi-rw-r-----1MySQL MySQL456August -  -: +A.myd

--~]# Cd/usr/local/mysql/bin

-- compress a table, the hint is too small to compress [[email protected] bin]#. /myisampack/usr/local/mysql/data/test/a/usr/local/mysql/data/test/A is too small to compress if the compression succeeds, The table is read-only and cannot be written. You can view the size of the MyD file. 

MySQL Development advanced Article series 31 Tools (MySQL connection tool and MyISAM table compression tool)