MySQL How do you restore tablespace?
This is not new information but I have not covered it much so addressing it now for those that need it.
If you lose your ibd files... you lose your data. So if you have a copy of one available.. or even if you are syncing from another database you can still import it. What/how do you lose tablespace?
Here is a simple example to recover tablespace.
mysql> Create database demo;
mysql> use demo;
mysql> CREATE TABLE `demotable` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Now we store some data...
MySQL How do you restore tablespace?
This week I've got a question that sounded basically like
"Is it possible to just copy the entire partition from the replicated server?"Let me share some background story. As it happens sometimes, user had a huge table with many partitions, let's say hundreds of gigabytes in size each, and one of them got unfortunately corrupted. It happened in a replication setup on master, but lucky they were, they had used innodb_file_per_table=1 and they had a slave that was more or less in sync with master. This allowed to reconfigure replication and continue to work, but the task remained to eventually put master back in use and get correct data in the corrupted partition. Let's assume that dumping and reloading data from one of instances in replication setup is not a desired option, as it will take too much time comparing to just copying the partition tablespace file. Hence the question above...
Side note: Let's assume …
MySQL 8.0 (the DMR version is available now) has two great features (among others):
- The new data dictionary completely removed *.frm files, which is great
- The ability to create a tablespace and assign a group of tables to it (originally introduced in 5.7).
With those two options, we can use MySQL for creating multi-tenant environments with a “schema per customer” approach.
Schema per Customer with MySQL 8.0
Using schema per customer with older MySQL versions presents issues … namely the number of files. (I’ve described …[Read more]
This article will explain how the data is organized in InnoDB storage engine. First we will look at the various files that are created by InnoDB, then we look at the logical data organization like tablespaces, pages, segments and extents. We will explore each of them in some detail and discuss about their relationship with each other. At the end of this article, the reader will have a high level view of the data layout within the InnoDB storage engine.
MySQL will store all data within the data directory. The data directory can be specified using the command line option –data-dir or in the configuration file as datadir. Refer to the Server Command Options for complete details.
By default, when InnoDB is initialized, it creates 3 important files in the data directory – ibdata1, ib_logfile0 and …[Read more]
I found Dennis the Menace, he now has a job as system administrator for a hosting company. Scenario: client has a problem with a server becoming unavailable (cause unknown) and has it restarted. MySQL had some page corruption in the InnoDB tablespace.
The hosting provider, being really helpful, goes in as root and first deletes ib_logfile* then ib* in /var/lib/mysql. He later says “I am sorry if I deleted it. I thought I deleted the log only. Sorry again.” Now this may appear nice, but people who know what they’re doing with MySQL will realise that deleting the iblogfiles actually destroys data also. MySQL of course screams loudly that while it has FRM files it can’t find the tables. No kidding!
Then, while he’s been told to not touch anything any more, and I’m trying to see if I can recover the deleted files on ext3 filesystem (yes there are tools for that), he goes in again and puts an ibdata1 file back. No, …[Read more]
There are times when what you have is a partially running
database and a bunch of backup innodb tablespace files (the .ibd
files). If you're using
you have a separate .ibd file for each InnoDB table.
Now, you have your running database with a bunch of tables, and you want to replace some of them with the backup .ibd files. According to the MySQL docs, you'd do this:
ALTER TABLE foo DISCARD TABLESPACE;(this deletes the current .ibd file)
- copy the old .ibd file into your database directory
ALTER TABLE foo IMPORT TABLESPACE;
Assuming your .ibd file was from the same database and you did not drop the table and recreate it sometime between when you made the backup .ibd and now, this should work. Except... if …[Read more]
|Server Startup Option||--innodb-file-per-table|
General InnoDB Engine: No
InnoDB plug-in: Yes
|Possible Values||Enable|Disable Flag|
This variable if enabled, makes InnoDB to create a separate
.ibd file for storing indexes and data. Setting this
variable makes it easy to manage disks with huge tables. Having …
If you are not familiar with tablespaces you may be wondering
what the big deal about them is. Tablespaces are a logical
addition to a database that helps maintenance, and potentially,
can improve performance.
In Oracle and MySQL, a tablespace is a logical unit meant to store segments (i.e. tables and indexes). In Postgres, a tablespace is a physical unit. It is a symbolic link to a directory. Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows).
The data file is the actual physical storage mechanism in Oracle and MySQL. Postgres stores tables in individual files. Postgres support of tablespaces is minimal. In MySQL and Oracle, performance can be improved by a more granular spread of data across disks. Ease of maintenance is maintained due to the logical grouping of tablespaces.
Oracle syntax for creating a tablespace is much the same as MySQL …