Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the command arrangement of daily use of MYSQL". In the daily operation, I believe that many people have doubts about the command arrangement of daily use of MYSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "command arrangement of daily use of MYSQL". Next, please follow the editor to study!

one。 There are three ways to view the structure of the mysql table:

1 、 desc tablename

2 、 show create table tablename

3. Use information_schema;select * from columns where table_name='tablename'

The second method is best if you want to see the command on how to create a data table.

two。 View size, index size:

> use information_schema

Database changed

Mysql > select concat (round (sum (index_LENGTH) / (1024 / 1024), 2), 'MB') as' Index Size' from tables where table_schema='DBname'

Explanation:

Concat and "MB" are units

Round and 2 indicate exact 2 decimal places

Sum indicates that all indexes add up

Mysql > select concat (round (sum (data_LENGTH) / (1024 / 1024), 2), 'MB') as' Data Size' from tables where table_schema='DBname'

III. BLOB data and backup

BLOB in MySQL is a binary large object and a container that can store a large amount of data, which can hold different sizes of data. A BLOB type is actually a family of types (TinyBlob, Blob, MediumBlob, LongBlob) that are equivalent except for the maximum amount of information stored.

Four BLOB types of MySQL

Type size (in bytes)

TinyBlob maximum 255i

Maximum of 65K for Blob

MediumBlob up to 16m

LongBlob maximum 4G

In practical use, different BLOB types are defined according to the amount of data that needs to be stored. If the file you store is too large, the performance of the database will degrade a lot.

Related backup command: / usr/local/mysql/bin/mysqldump-- hex-blob mydata > / opt/bak.sql

four。 Backup MySQL

There are two options for backing up MySQL-mysqldump or mysqlhotcopy.

Mysqldump can back up various types of data tables.

Mysqlhotcopy is only suitable for backing up data tables for MyISAM and ISAM. So before using mysqlhotcopy, you must make sure that your datasheet has another storage engine (storage engines).

Mysql > show engines

Shows the full list of available database engines and whether they are supported in the current database server. (Default is the default)

A more flexible approach is to specify the storage engine to use when publishing the MySQL client that is provided with the MySQL server.

The most direct way is to specify the type of storage engine when creating the table:

CREATE TABLE mytable (id int, title char (20)) ENGINE = INNODB

You can also change the storage engine used by the existing table with the following statement:

ALTER TABLE mytable ENGINE = MyISAM

How To:

Mysqldump-uroot-paired w'.dump.gz * DBNAME | gzip-f > / backup/dbname.'date +% w'.dump.gz

Mysqlhotcopy DBNAME-u root-p * * / backup

Both speeds: because mysqlhotcopy directly copies files that store data, its speed depends on the speed of disk operations, which is faster than mysqldump.

At this point, the study of "order arrangement for daily use of MYSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.