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...

…

Thanks……Good stuff…..

So I realized I had not made a post about this after this situation that recently came up.

Here is the scenario: A backup was taken at midnight, they used MySQL dumps per database. Then at ten am the next day the database crashed. A series of events happened before I was called in, but they got it up to a version of the database with MyISAM tables and the IBD files missing from the tablespace.

So option 1, restoring from backup would get us to midnight and we would lose hours of data. Option 2, we reimport the 1000's of ibd files and keep everything. Then we had option 3, restore from backup, then apply the binlogs for recent changes.

To make it more interesting, they didn't have all of the ibd files I was told, and I did see some missing. So not sure how that was possible but option 2 became an invalid option. They, of course, wanted the least data loss possible, so we went with option 3.

…

(Image credit: Pixabay).

In my earlier post, I showed how MySQL, since version 8.0.14, has support for LATERAL derived tables. With LATERAL, a JOIN can have a second table – a subquery-based derived table – be defined based on values from columns of the first table, and thus be re-calculated for each row of the first table.…

Facebook Twitter LinkedIn

What is new with MySQL 8.0 Delayed Replication ?

Delayed Replication – You can deliberately execute transactions later than the master by a specific duration of time , Why you do that and for what ? Consider this, Accidentally someone did a wrong UPDATE / DELETE in the master and the transaction is committed, Now how can DBA rollback the database system to the last known good condition ? This is when we benefit from MySQL delayed slave replication investment. The default replication delay in MySQL is “0” seconds, To delay the slave by N seconds use the CHANGE MASTER TO MASTER_DELAY = N, The transactions received from the master is not executed until N seconds later than it’s commit on the immediate master. We have blogged here how to setup …

In 5.6 and earlier, the format is 190711 18:44:52 (YYmmdd HH:MM:SS) and the time zone is always the system time zone. Hope that helps.

MySQL Roles are becoming more and more popular. Therefor, we receive more and more questions related to them.

First I encourage you to read this previous 2 posts:

• MySQL 8.0 Roles and GraphML
• MySQL 8.0: Listing Roles

In this post, I will share you some queries I find useful when using MySQL Roles.

Listing the Roles

The first query allows you to list the Roles created on your MySQL Server and if they are assigned to users, and how many:

SELECT any_value(User) 'Role Name', 
       IF(any_value(from_user) is NULL,'No', 'Yes') Active,
       count(to_user) 'Assigned  …

Like I stated in my previous article - MySQL InnoDB Cluster - Recovering and provisioning with mysqldump :
"As the administrator of a cluster, among others tasks, you should be able to restore failed nodes and to add (or remove) new nodes".
Well, I still agree with myself :)
MySQL customers using a Commercial Edition have access to MySQL Enterprise Backup (MEB) which provide enterprise-grade physical backup and recovery for MySQL.
MEB delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.

The post MySQL InnoDB Cluster - Recovering and provisioning with MySQL Enterprise Backup first appeared on dasini.net - Diary of a MySQL expert.

Percona is glad to announce the release of Percona XtraBackup 2.4.15 on July 10, 2019. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it …

This tutorial shows how to install an Apache web server on a CentOS 7 server with PHP (mod_php with PHP 5.4, 7.0, 7.1, 7.2 or 7.3) and MySQL support. This setup is often referred to as LAMP which stands for Linux - Apache - MySQL - PHP.