mysql 运维手册_MySQL数据库的维护手册

查看MySQL 库中所有表的大小和记录数

SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length, \

TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size \

FROM information_schema.TABLES WHERE TABLE_SCHEMA='database_name' order by length desc;

说明：

TABLE_NAME ：表名字；

DATA_LENGTH ： 数据大小；

INDEX_LENGTH ：索引大小；

TABLE_ROWS ： 记录数量；

TABLE_SCHEMA ： 数据库名字；

ENGINE：所使用的存储引擎；

information_schema ：是mysql自带的，它提供了访问数据库元数据的方式，元数据是关于数据的数据，

如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

一个表占用空间的大小，相当于是 数据大小 + 索引大小；

实例：

mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,\

-> TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size \

-> FROM information_schema.TABLES WHERE TABLE_SCHEMA='zhcwsystem' order by length desc;

+---------------------------------+-------------+--------------+------------+------------+------------+

| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | length | TABLE_ROWS | total_size |

+---------------------------------+-------------+--------------+------------+------------+------------+

| tab_lottery_spinfo_bd | 7332730928 | 489879552 | 7822610480 | 31176968 | 7460.223MB |

| tab_kaijiang_info | 130711552 | 16269312 | 146980864 | 853084 | 140.172MB |

| tab_lottery_match_bd | 55002516 | 3718144 | 58720660 | 271763 | 56.000MB |

| tab_lottery_result_bd | 16977364 | 10750976 | 27728340 | 276547 | 26.444MB |

| tab_cz_zj_info | 18198416 | 607232 | 18805648 | 26841 | 17.934MB |

| tab_lottery_result | 6447924 | 3440640 | 9888564 | 120396 | 9.430MB |

| tab_sport_lottery_info | 5374524 | 1154048 | 6528572 | 39045 | 6.226MB |

| tab_cz_dq_info | 5115396 | 424960 | 5540356 | 8684 | 5.284MB |

| tab_nb_detail | 56 | 2048 | 2104 | 1 | 0.002MB |

| tab_cz_type | 40 | 2048 | 2088 | 2 | 0.002MB |

| tab_lottery_result_lancai_copy1 | 0 | 2048 | 2048 | 0 | 0.002MB |

| tab_lottery_result_lancai_copy | 0 | 2048 | 2048 | 0 | 0.002MB |

+---------------------------------+-------------+--------------+------------+------------+------------+

12 rows in set (0.19 sec)

MySQL 复制表结构

MySQL复制表结构主要有以下几种方法。

方法1：   create table new_tab like ori_tab

mysql> desc tab_lottery_spinfo_bd;

+--------------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| lotteryid | varchar(32) | NO | MUL | NULL | |

| lotteryissue | varchar(12) | NO | | NULL | |

| ballid | varchar(8) | NO | | NULL | |

| spinfo | text | YES | | NULL | |

| single | int(1) | YES | | 0 | |

| p_status | varchar(30) | YES | | NULL | |

| lasttime | datetime | YES | | NULL | |

| addtime | datetime | YES | | NULL | |

+--------------+-------------+------+-----+---------+----------------+

9 rows in set (0.00 sec)

mysql> create table tab_lottery_spinfo_bd_new like tab_lottery_spinfo_bd;

Query OK, 0 rows affected (0.08 sec)

mysql> desc tab_lottery_spinfo_bd_new;

+--------------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| lotteryid | varchar(32) | NO | MUL | NULL | |

| lotteryissue | varchar(12) | NO | | NULL | |

| ballid | varchar(8) | NO | | NULL | |

| spinfo | text | YES | | NULL | |

| single | int(1) | YES | | 0 | |

| p_status | varchar(30) | YES | | NULL | |

| lasttime | datetime | YES | | NULL | |

| addtime | datetime | YES | | NULL | |

+--------------+-------------+------+-----+---------+----------------+

9 rows in set (0.01 sec)

mysql> select count(*) from tab_lottery_spinfo_bd_new;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

方法2：   create table new_tab1 select * from ori_tab limit 0;

mysql> create table tab_lottery_spinfo_bd_new1 select * from tab_lottery_spinfo_bd limit 0;

Query OK, 0 rows affected (0.08 sec)

方法3：执行SQL语句来创建新表的表结构

# 显示需创建表的SQL语句：

mysql> show create table tab_lottery_spinfo_bd\G;

*************************** 1. row ***************************

Table: tab_lottery_spinfo_bd

Create Table: CREATE TABLE `tab_lottery_spinfo_bd` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`lotteryid` varchar(32) NOT NULL,

`lotteryissue` varchar(12) NOT NULL,

`ballid` varchar(8) NOT NULL,

`spinfo` text,

`single` int(1) DEFAULT '0',

`p_status` varchar(30) DEFAULT NULL,

`lasttime` datetime DEFAULT NULL,

`addtime` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `lotteryissue` (`lotteryid`,`lotteryissue`,`ballid`)

) ENGINE=MyISAM AUTO_INCREMENT=312 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

# Copy上面的SQL语句，修改 表名 和 AUTO_INCREMENT 的值，然后执行一下。

CREATE TABLE `tab_lottery_spinfo_bd_new2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`lotteryid` varchar(32) NOT NULL,

`lotteryissue` varchar(12) NOT NULL,

`ballid` varchar(8) NOT NULL,

`spinfo` text,

`single` int(1) DEFAULT '0',

`p_status` varchar(30) DEFAULT NULL,

`lasttime` datetime DEFAULT NULL,

`addtime` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `lotteryissue` (`lotteryid`,`lotteryissue`,`ballid`)

) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

MySQL复制表结构及其表数据

方法1： create table new_tab select * from ori_tab;

mysql> create table tab_lottery_spinfo_bd_new1 select * from tab_lottery_spinfo_bd;

Query OK, 1004000 rows affected (0.58 sec)

Records: 1004000 Duplicates: 0 Warning: 0

方法2： create table new_tab2 select filed1,filed2 from ori_tab [ where id=1];

mysql> create table tab_lottery_spinfo_bd_new2 select id,lotteryid,lotteryissue,ballid,spinfo from tab_lottery_spinfo_bd where lotteryissue=90602;

Query OK, 311 rows affected (0.28 sec)

Records: 311 Duplicates: 0 Warning: 0

以上两种方法，方便快捷、灵活性强。

方法3： 先创建一个空表，然后在进行 INSERT INTO new_tab3

先创建一个新表，然后执行如下两个语句：

INSERT INTO new_tab3 SELECT * FROM ori_tab;

或者：

INSERT INTO  new_tab3(field1, field2, field3) SELECT fied1, field2, field3 FROM ori_tab;

这种方法不是很方便，但是也有很多人经常用。