Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 20 10 Older Entries

Displaying posts with tag: restore (reset)

How to shrink the ibdata file by transporting tables with Trite
+0 Vote Up -0Vote Down

You’ve probably had some troubles with the shared InnoDB tablespace stored in the ibdata file. Especially when it has grown for some reasons and reached a critical size.

This behavior occurs in some cases, due to excessive rollback segments growth or during a migration from a unique shared tablespace to a file-per-table configuration for example.

In this post, I would like to explain how to shrink the ibdata file after an unwanted file growth in a file-per-table configuration.
Note that the process could be done without Trite but the tool avoids to write the script used to transport tables yourself.

Initial situation

Here is a sample of the InnoDB configuration:

innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_per_table

And the status of your datafiles in the datadir directory:

drwx------ 2 mysql mysql 4,0K

  [Read more...]
Selective Restore of InnoDB Tables with MySQL Enterprise Backup 3.11
Employee +1 Vote Up -0Vote Down

Introduction

Sometimes the best way to repair data issues and problems within a MySQL database is to restore only some of the tables from a backup. For example, suppose that some data was accidentally deleted from one table due to a software error, then the easiest way to recover the lost data might be to restore only one table from a backup. Previously this kind of partial restore was not supported by MySQL Enterprise Backup (MEB). However, MEB 3.11 introduces support for selective restore from backups created with the --use-tts option (or TTS backups).

TTS backups are backups that are created with the transportable tablespaces

  [Read more...]
Faster and Space efficient Restore with MySQL Enterprise Backup
Employee +1 Vote Up -0Vote Down

A typical backup cycle with MySQL Enterprise Backup had always involved 3 steps:

1. backup (to directory)

2. apply-log (prepare the backup)

3. copy-back (restore)

In case of directory, the 'apply-log' is done on the backup directory which does not involve any additional space.

But in case of image, the image has to be extracted, before doing 'apply-log'. This involves additional space for extraction of image and the steps involved are:

1. backup-to-image

2. Extract

3. apply-log

4. copy-back

How about, combining the 3 steps – extract, apply-log and copy-back, so that the image is extracted directly on the target directory (which is the datadir of a server instance) and then apply-log is performed on the extracted contents. This way, the need for additional space

  [Read more...]
MySQL Enterprise Backup: parallel config & backup n restore results.
Employee +0 Vote Up -0Vote Down

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb –Source disk:  internal 80Gb ATA ST9808211A –Destination:  external 1Tb SAMSUNG HD103SI –MySQL Enterprise Edition 5.6.15 –MySQL Enterprise Backup 3.9.0 –Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server. And to simulate data size, I used the MySQL Utilities:
mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ...
  [Read more...]
MySQL Enterprise Backup: parallel config & backup n restore results.
Employee +0 Vote Up -0Vote Down

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb –Source disk:  internal 80Gb ATA ST9808211A –Destination:  external 1Tb SAMSUNG HD103SI –MySQL Enterprise Edition 5.6.15 –MySQL Enterprise Backup 3.9.0 –Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server. And to simulate data size, I used the MySQL Utilities:
mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ...
  [Read more...]
How to restore directly on a remote machine from the backup stream
Employee +0 Vote Up -0Vote Down

MySQL Enterprise Backup has been improved to support single step restore from the latest release 3.9.0. It enables you to restore the backup image to remote machine in single step. However, first you would have to create the backup image in local disk, copy the backup image to remote machine, and then restore in remote machine by running copy-back-and-apply-log command.

This approach has two overheads:

    Serial execution: You have to wait for each step to finish before beginning the next (e.g. You must have to wait for backup-to-image operation to finish before beginning copy).  [Read more...]
xtrabackup_51: not found & no ‘mysqld’ group in MySQL options
+0 Vote Up -0Vote Down
Recently I happen to setup a new MySQL instance with my tools – a standard MySQL 5.1+, xtrabackup setup and last-hotbackup.tar.gz. To restore from the backup we used xtrabackup binaries and ran into issues following standard commands (assuming no changes): To prepare the backup I used apply-log as follows: $] innobackupex-1.5.1 --defaults-file=/usr/local/mysql/data/backup-my.cnf --apply-log  /usr/local/mysql/data --ibbackup […]
A difficult XtraBackup restore
+1 Vote Up -0Vote Down
There was one MySQL server with a Adaptec Raid controller and 4 disks. One of the disks was having media errors and caused the whole SCSI bus to become unavailable.

This resulted in a corrupted InnoDB table.

Luckily we did have backups. A full backup and incrementals.

So to restore the backups I installed XtraBackup and MySQL 5.5 on another server.

Then the first step was to 'prepare' the backup. This worked okay for the full backup (redo only).

The second step to add the incremantals failed for the first incremental. This was easily resolved by specifying the full paths instead of relative paths.

Then the backup was fully prepared using the redo logs and undo logs.

As XtraBackup doesn't backup your my.cnf we copied the my.cnf from another server and adjusted it for this server. The my.cnf in your













  [Read more...]
Surprises in store with ndb_restore
+1 Vote Up -1Vote Down

While doing some routine fiddling regarding some topic I've now forgotten, I discovered that ndb_restore was doing something quite surprising. It's been common wisdom for some time that one can use ndb_restore -m to restore metadata into a new cluster and automatically have your data re-partitioned across the data nodes in the destination cluster. In fact, this was the recommended procedure for adding nodes to a cluster before online add node came along. Since MySQL Cluster 7.0, though, ndb_restore hasn't behaved that way, though that change in behavior doesn't seem to be documented and most don't know that the change ever took place.

I'll go through some of the methods you can use to find information about the partitioning strategy for an NDB table, talk a bit about why ndb_restore stopped working the way most everyone expected

  [Read more...]
Viewing RMAN jobs status and output
+0 Vote Up -1Vote Down

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.


  [Read more...]
Showing entries 1 to 10 of 20 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.