MySQL View the size of the database and table footprint

MySQL View the size of the database and table footprint

?

1. Part I-Tasks

After you export the online DB, import to Office db

One.

Two scenarios:

1, the data directly from the Online-->office, through the mysqldump

Tunnels are built via local putty and ECS, and then data is sent locally. (local win can be compressed and uploaded to Office Server by gzip)

2, first on the ECS mysqldump compression, and then download it down

Download Solution:

1,rsync Daemon

2,rsync SSH

3,scp

4,sz <----This is faster, the first three are similar, remember md5sum

?

?

1. Part II

View database size

1. View the size of the database

?

Use database name

SELECT sum (data_length) +sum (index_length) from INFORMATION_SCHEMA. TABLES where table_schema= ' database name ';

The resulting result is in bytes, except 1024 for K, except 1048576 for M.

?

2. View the last MySQL modification time of the table

?

Select Table_name,update_time from information_schema.tables where table_schema= ' database name ';

You can determine whether the MySQL database has been unused for long periods by looking at the MySQL modification time of the tables in the database.

?

3. View the amount of space occupied by each table in the database

?

?

You can open the MySQL information_schema database if you want to know the amount of space that each table in the MySQL database occupies and the number of rows that the table records. In this library there is a TABLES table, the main fields of this table are:

?

Table_schema: Database name

TABLE_NAME: Table name

Engine: the storage engine used

Tables_rows: Number of records

Data_length: Data size

Index_length: Index size

?

For other fields please refer to the MySQL manual, we only need to know these few is enough.

?

So to know the size of a table occupies space, it is equivalent to the size of the data + index size.

?

Sql:

?

SELECT table_name,data_length+index_length,table_rows from TABLES WHERE table_schema= ' database name ' and table_name= ' table name '

?

4. View the last MySQL modification time of the table

?

Select Table_name,update_time from information_schema.tables where table_schema= ' database name ';

?

You can determine whether the MySQL database has been unused for long periods by looking at the MySQL modification time of the tables in the database.

MySQL View the size of the database and table footprint