Sort out routine MYSQL commands

I. There are three ways to view the mysql table structure:
1. desc tablename;
2. show create table tablename;
3. use information_schema; select * from columns where table_name = 'tablename ';
The second method is the best if you want to view how to create a data table.
 
2. view the database size and index size:
Mysql> 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 the sum of all indexes.
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
In MySQL, BLOB is a binary large object and a container that can store large amounts of data. It can hold data of different sizes. The BLOB type is actually a type series (TinyBlob, Blob, MediumBlob, and LongBlob). They are equivalent except for the maximum amount of information stored.
Four BLOB types in MySQL
Type size (unit: bytes)
TinyBlob Max. 255
Blob Max 65 K
MediumBlob up to 16 MB
Up to 4 GB LongBlob
In actual use, different BLOB types are defined based on the data size to be stored. If the file you store is too large, the database performance will decrease a lot.
Related Backup commands:/usr/local/mysql/bin/mysqldump -- hex-blob mydata>/opt/bak. SQL
4. Back up MySQL
There are two ways to back up MySQL: mysqldump or mysqlhotcopy.
Mysqldump can back up various data tables.
Mysqlhotcopy is only suitable for backing up data tables of MyISAM and ISAM. Before using mysqlhotcopy, you must check whether your data table has other storage engines.
Mysql> show engines;
Displays the list of available database engines and whether these engines are supported on the current database server. (Default is the Default value)
A more flexible way is to specify the storage engine used when the MySQL client is released along with the MySQL server.
The most direct method is to specify the storage engine type when creating a table:
Create table mytable (id int, title char (20) ENGINE = INNODB
You can also change the storage engine used by the existing table by using the following statement:
Alter table mytable ENGINE = MyISAM
How:
Mysqldump-uroot-p *** DBNAME | gzip-f>/backup/dbname. 'date used before w'.dump.gz
Mysqlhotcopy DBNAME-u root-p ***/backup
Speed: Because mysqlhotcopy directly copies the files that store data, the speed depends on the disk operation speed, which is faster than mysqldump.



Author deams