Showing entries 1 to 5
Displaying posts with tag: tablespaces (reset)
Using Transportable Tablespaces with Partitioned Tables

Overview

In this post I will explain how to transfer a partitioned table using transportable tablespaces on MySQL 5.6. Note that the operation is not officially supported until MySQL 5.7, as the discard tablespace operation will fail if it is run against a partitioned table on MySQL 5.6.

The workaround is transferring each individual partition as if it was a standalone table. Continue reading to learn more about the detailed procedure.

Steps

1. Create the same table structure on the destination server:

[root@mysqlsandbox ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35-80.0 Percona Server (GPL), Release 80.0, Revision f113994f31

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. …
[Read more]
Recover orphaned InnoDB partition tablespaces in MySQL

A few months back, Michael wrote about reconnecting orphaned *.ibd files using MySQL 5.6. I will show you the same procedure, this time for partitioned tables. An InnoDB partition is also a self-contained tablespace in itself so you can use the same method described in the previous post.

To begin with, I have an example table with a few orphaned partitions and we will reconnect each partition one by one to the original table.

mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t1 G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
[...]
KEY `h_date` (`h_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(h_date))
(PARTITION p0 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION …
[Read more]
InnoDB General Tablespaces – Preview

The new InnoDB Labs release contains the ability to create and use independent multi-table general tablespaces.

This feature will provide a way to group tables together into tablespaces at a location and filename of your choosing.  Tables using row formats of Redundant, Compact, and Dynamic can be combined together into the same general tablespace. Compressed tables with the same key_block_size can also be combined together.

The SQL syntax for creating an empty general tablespaces is:

CREATE TABLESPACE `tblspace_name` ADD DATAFILE 'tablespace.ibd' [FILE_BLOCK_SIZE=n];

The filename can contain an absolute path or a path relative to the …

[Read more]
Online Truncate of InnoDB UNDO Tablespaces

We have received a lot of requests from our user community regarding the ability to  truncate UNDO tablespaces (‘truncate’ here means that the size of the tablespace is reset back to what it was when it was first created). We are happy to say that we’ve now been able to implement this anticipated feature.

Introduction

The InnoDB UNDO tablespace(s) host rollback segments that hold rollback information related to database changes. This information is used to rollback a transaction and to retrieve the previous version of a record that has been updated or deleted for multi-version concurrency control (MVCC). Once a transaction is committed, InnoDB will discard the related UNDO log records. UNDO log records for updates or deletes will be kept around as long as there exists an open transaction that may access older versions of the records. When all such open transactions are committed then the associated UNDO log records can …

[Read more]
Using Cluster Disk Data in MySQL™ 5.1

[HoneyMonitor is a GUI for MySQL™. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com/] Contents

  • Introduction
  • Log File Group & UndoFiles
  • TableSpaces & DataFiles
  • Cluster Disk Objects Metadata
  • Creating Cluster Disk Tables
  • Dropping Cluster Disk Data Objects
  • The Cluster Monitor
  • Cluster Disk Data Limitations
  • Cluster Disk Data Quiz
  • Other Resources

1. Introduction

Cluster Disk Data is a new …

[Read more]
Showing entries 1 to 5