发布网友 发布时间:2022-04-22 23:58
共2个回答
懂视网 时间:2022-04-08 07:38
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.[确定]
2、修改 /etc/my.cnf配置文件
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
innodb_file_per_table = 1
3、启动Mysql
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL..[确定]
4、连接到Mysql数据库
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbmonitor |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
mysql> show plugins;
+-----------------------+--------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+-----------------------+--------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-----------------------+--------+--------------------+---------+---------+
mysql> use dbmonitor;
Database changed
5、创建分区表(history_text):
mysql> create table history_text
(
id bigint(20) unsigned default null,
itemid bigint(20) unsigned default null,
clock int(11) default 0,
value text default null,
ns int(11) default 0,
index itemid_idx (itemid),
primary key(id,clock)
) engine=Innodb
partition by range (clock)
(
partition p1_201502 values less than (unix_timestamp(‘2015-03-01‘)),
partition p1_201503 values less than (unix_timestamp(‘2015-04-01‘)),
partition p1_201504 values less than (unix_timestamp(‘2015-05-01‘)),
partition p1_201505 values less than (unix_timestamp(‘2015-06-01‘)),
partition p1_201506 values less than (unix_timestamp(‘2015-07-01‘)),
partition p1_201507 values less than (unix_timestamp(‘2015-08-01‘)),
partition p1_201508 values less than (unix_timestamp(‘2015-09-01‘)),
partition p1_201509 values less than (unix_timestamp(‘2015-10-01‘)),
partition p1_201510 values less than (unix_timestamp(‘2015-11-01‘)),
partition p1_201511 values less than (unix_timestamp(‘2015-12-01‘)),
partition p1_201512 values less than (unix_timestamp(‘2016-01-01‘)),
partition p1_201601 values less than (unix_timestamp(‘2016-02-01‘)),
partition p1_201602 values less than (unix_timestamp(‘2016-03-01‘)),
partition p1_201603 values less than (unix_timestamp(‘2016-04-01‘)),
partition p1_201604 values less than (unix_timestamp(‘2016-05-01‘)),
partition p1_201605 values less than (unix_timestamp(‘2016-06-01‘)),
partition p1_201606 values less than (unix_timestamp(‘2016-07-01‘)),
partition p1_201607 values less than (unix_timestamp(‘2016-08-01‘)),
partition p1_201608 values less than (unix_timestamp(‘2016-09-01‘)),
partition p1_201609 values less than (unix_timestamp(‘2016-10-01‘)),
partition p1_201610 values less than (unix_timestamp(‘2016-11-01‘)),
partition p1_201611 values less than (unix_timestamp(‘2016-12-01‘)),
partition p1_other_all values less than maxvalue
);
mysql>alter table history_text add primary key(id,clock);
6、检查数据数据库配置参数是否生效。 [root@localhost data]# ll
总计 10900348
drwx------ 2 mysql mysql 4096 03-20 09:40 dbmonitor
-rw-rw---- 1 mysql mysql 7266631680 03-20 10:29 ibdata1
-rw-rw---- 1 mysql mysql 5242880 03-20 10:30 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 03-20 10:30 ib_logfile1
drwxr-xr-x 2 mysql root 4096 03-18 00:18 mysql
-rw-rw---- 1 mysql mysql 27338 03-18 00:18 mysql-bin.000001
[root@localhost ~]# cd /usr/local/mysql/data/dbmonitor/
[root@localhost dbmonitor]# ll
总计 5112
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201503.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:34 history_text#P#p1_201504.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201505.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 10:06 history_text#P#p1_201506.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201507.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201508.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201509.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201510.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201511.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201512.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201601.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201602.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201603.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201604.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201605.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201606.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201607.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201608.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201609.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201610.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201611.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:35 history_text#P#p1_other_all.ibd
7、查看分区表。
mysql> select table_name, partition_name from information_schema.partitions
-> where table_schema=‘dbmonitor‘ and table_name=‘history_text‘;
+--------------+----------------+
| table_name | partition_name |
+--------------+----------------+
| history_text | p1_201503 |
| history_text | p1_201504 |
| history_text | p1_201505 |
| history_text | p1_201506 |
| history_text | p1_201507 |
| history_text | p1_201508 |
| history_text | p1_201509 |
| history_text | p1_201510 |
| history_text | p1_201511 |
| history_text | p1_201512 |
| history_text | p1_201601 |
| history_text | p1_201602 |
| history_text | p1_201603 |
| history_text | p1_201604 |
| history_text | p1_201605 |
| history_text | p1_201606 |
| history_text | p1_201607 |
| history_text | p1_201608 |
| history_text | p1_201609 |
| history_text | p1_201610 |
| history_text | p1_201611 |
| history_text | p1_other_all |
+--------------+----------------+
22 rows in set (0.01 sec)
8、插入数据测试分区表。
mysql> delimiter //
mysql> create procedure insert_history()
begin
declare var int;
set var=0;
while var<100000 do
insert into history_text values(var,var,1430496000,var,var);
set var=var+1;
end while;
end;
//
mysql> delimiter ;
mysql> call insert_test();
Query OK, 1 row affected (2 min 46.97 sec)
9、查看数据文件大小的变化。
[root@localhost data]# cd dbmonitor/
[root@localhost dbmonitor]# ll
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201503.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:34 history_text#P#p1_201504.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201505.ibd
-rw-rw---- 1 mysql mysql 19922944 03-20 10:54 history_text#P#p1_201506.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201507.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201508.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201509.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201510.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201511.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201512.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201601.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201602.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201603.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201604.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201605.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201606.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201607.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201608.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201609.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201610.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201611.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:35 history_text#P#p1_other_all.ibd
[root@localhost data]# ll
总计 10919648
drwx------ 2 mysql mysql 4096 03-20 09:40 dbmonitor
-rw-rw---- 1 mysql mysql 7266631680 03-20 10:55 ibdata1
-rw-rw---- 1 mysql mysql 5242880 03-20 10:55 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 03-20 10:55 ib_logfile1
drwxr-xr-x 2 mysql root 4096 03-18 00:18 mysql
-rw-rw---- 1 mysql mysql 27338 03-18 00:18 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1031892 03-18 00:18 mysql-bin.000002
-rw-rw---- 1 mysql mysql 126 03-18 00:23 mysql-bin.000003
-rw-rw---- 1 mysql mysql 126 03-18 00:25 mysql-bin.000004
-rw-rw---- 1 mysql mysql 703646366 03-18 00:58 mysql-bin.000005
-rw-rw---- 1 mysql mysql 1074397884 03-18 02:48 mysql-bin.000006
-rw-rw---- 1 mysql mysql 1073854162 03-18 04:00 mysql-bin.000007
-rw-rw---- 1 mysql mysql 973199491 03-18 04:43 mysql-bin.000008
-rw-rw---- 1 mysql mysql 126 03-18 09:19 mysql-bin.000009
-rw-rw---- 1 mysql mysql 41826406 03-18 11:01 mysql-bin.000010
-rw-rw---- 1 mysql mysql 126 03-18 11:06 mysql-bin.000011
-rw-rw---- 1 mysql mysql 126 03-18 14:03 mysql-bin.000012
-rw-rw---- 1 mysql mysql 2175 03-18 16:48 mysql-bin.000013
-rw-rw---- 1 mysql mysql 107 03-18 23:06 mysql-bin.000014
-rw-rw---- 1 mysql mysql 107 03-19 08:49 mysql-bin.000015
-rw-rw---- 1 mysql mysql 5720758 03-20 10:29 mysql-bin.000016
-rw-rw---- 1 mysql mysql 19741312 03-20 10:55 mysql-bin.000017
-rw-rw---- 1 mysql mysql 323 03-20 10:30 mysql-bin.index
drwx------ 2 mysql mysql 4096 03-18 00:18 performance_schema
drwxr-xr-x 2 mysql root 4096 03-18 15:14 test
注释:发现只有单独的表空间数据文件在增大,mysql的元数据文件不变化。
10、删除分区表中的数据,减少数据文件的大小,清理空间。
mysql> alter table history_text truncate partition p1_201506;
Query OK, 0 rows affected (0.09 sec)
[root@localhost dbmonitor]# ll
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201503.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:34 history_text#P#p1_201504.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201505.ibd
-rw-rw---- 1 mysql mysql 131072 03-20 11:01 history_text#P#p1_201506.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201507.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201508.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201509.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201510.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201511.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201512.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201601.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201602.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201603.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201604.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201605.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201606.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201607.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201608.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201609.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201610.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:21 history_text#P#p1_201611.ibd
-rw-rw---- 1 mysql mysql 131072 03-18 09:35 history_text#P#p1_other_all.ibd
本文出自 “一念花开” 博客,请务必保留此出处http://lilingfeng.blog.51cto.com/5671021/1675560
Mysql5.6—分区表及独享表空间
标签:copyright password version welcome oracle
热心网友 时间:2022-04-08 04:46
frm表结构文件,myd表数据文件,myi表索引文件。