Showing entries 1 to 10 of 64
10 Older Entries »
Displaying posts with tag: recovery (reset)
How to create a rock-solid MySQL database backup & recovery strategy

Have you ever wondered what could happen if your MySQL database goes down?

Although it’s evident such a crash will cause downtime – and surely some business impact in terms of revenue – can you do something to reduce this impact?

The simple answer is “yes” by doing regular backups (of course) but are you 100% sure that your current backup strategy will really come through when an outage occurs? And how much precious time will pass (and how much revenue will be lost) before you get your business back online?

I usually think of backups as the step after HA fails. Let’s say we’re in M<>M replication and something occurs that kills the db but the HA can’t save the day. Let’s pretend that the UPS fails and those servers are completely out. You can’t failover; you have to restore data. Backups are a key piece of “Business Continuity.” Also factor in the frequent need to restore data that’s been …

[Read more]
Restore / Recover dropped MySQL database from binary logs

In this post I will share a recovery scenario of a MySQL database restore from the binary logs. Recently someone accidentally dropped an important MySQL database and the backup was not present! As we know the the binary log contains DMLs to table data and that’s where our hope lies. Luckily the binary log retention […]

How to Bootstrap MySQL/MariaDB Galera Cluster

January 27, 2015 By Severalnines

Unlike standard MySQL server and MySQL Cluster, the way to start a MySQL/MariaDB Galera Cluster is a bit different. Galera requires you to start a node in a cluster as a reference point, before the remaining nodes are able to join and form the cluster. This process is known as cluster bootstrap. Bootstrapping is an initial step to introduce a database node as primary component, before others see it as a reference point to sync up data.

 

How does it work?

 

When Galera starts with the bootstrap command on a node, that particular node will reach Primary state (check the value of wsrep_cluster_status). The remaining nodes will just require a normal start command and they will automatically look for existing Primary Component (PC) in the cluster and join to form a cluster. Data synchronization then happens through either incremental state transfer (IST) or …

[Read more]
Recover Corrupt MySQL Database

The unDROP for InnoDB tool can be used to recover corrupt MySQL database. In this post we will show how to repair MySQL database if its files became corrupted and even innodb_force_recovery=6 doesn’t help.

The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write garbage, thus page checksum will be wrong. InnoDB then reports to the error log:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

MySQL is well know for poor start-up script. A simple upgrade procedure may end up with two mysqld processes writing to the same tablespace. That leads to the corruption too. Sometimes power reset corrupts not only InnoDB files, but file system becomes unusable for the operating system.

InnoDB …

[Read more]
MySQL Enterprise Backup 3.10: Teasing compression.

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

 

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp …
[Read more]
MySQL Enterprise Backup 3.10: Teasing compression.

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

 

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp …
[Read more]
–use-tts backup & restore

In addition to my recent post, I just had to go into using the –use-tts for specific tables and selective backup sets.

As all my schemas were employeesn, I thought it would be a good idea to run:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees* backup

as I want all the tables. If I only wanted a specific table, say ‘salaries’ I could have done:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees*\.*salaries backup

and backup just the ‘salaries’ tables but from all the employeesn databases.

then.. be a mean little dba:

drop database employees;
drop database employees10;
drop database employees11;
..
..
drop database employees2;
drop database employees20;
drop database …
[Read more]
MySQL Enterprise Backup: parallel config & backup n restore results.

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 ... employees:employees18 \
employees:employees19 employees:employees20

[Read more]
MySQL Enterprise Backup: parallel config & backup n restore results.

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 ... employees:employees18 \
employees:employees19 employees:employees20

[Read more]
New Webinar: Repair and Recovery for your MySQL, MariaDB and MongoDB/TokuMX Clusters

December 19, 2013 By Severalnines


Database clusters are pretty sophisticated distributed systems with complex dependencies between nodes. The failure of a node will generally impact the overall cluster, as the remaining nodes need to reconfigure themselves to continue to operate without the failed node. Since re-introducing a node will also affect the existing cluster, the timing could therefore be dependent on the state of the other nodes in the cluster. Repair and restarts often needs to be performed in a particular order in compliance with the redundancy model of the cluster so as not to jeopardize the normal functioning of existing nodes.

 

Webinar: Repair and Recovery for your MySQL, MariaDB and MongoDB/TokuMX clusters

 

[Read more]
Showing entries 1 to 10 of 64
10 Older Entries »