During the MySQL Users Conference, Edward Screven did a keynote presentation that made many of us feel
warm and fuzzy about Oracle's future plans for MySQL. If you
advance 16m 25s into the presentation, it even gives something to
rejoice the MySQL Enterprise customers: "Backup is now included".
He didn't say much more after that. Asking around at the
conference the days following this announcement, I couldn't get a
straight answer about when and how would it be available for
existing customers.
Now, 6 months later (give or take a couple of weeks), the
MySQL Enterprise Features page has no signs of the
now included MySQL Enterprise Backup (the utility
previously known as InnoDB Hot Backup) and there has been no
other news supporting Edward's announcement …
The latest from the MySQL community
Ideas, releases, practical guides, and perspectives from the people building with MySQL.
Any corruption in MyISAM table is a terrible situation for
Database Administrator. These MyISAM tables are the most
important components in the MySQL Database to store personal as
well as professional data, and the corruption in these tables may
inaccessibility to the database. In this situation, a database
backup allows you to restore the database and access the records
in the table. Duplicate file (Backup) of the database is mostly
stored on a different storage media, the changes of database
corruption still exist. In that situation, you will need to
perform MySQL database repair by the using of third party
software.
Some error messages that enable you to identify that your MyISAM
table (student) is corrupt:
“Student.frm is locked against change”
Or
“Got error message student from table handler”
Or
“Can't find file student.MYI (Errcode: nnn)”
The record …
Let’s suppose that your backup process looks like this: you stop a replication slave, shut down MySQL, and copy away the data directory. Assume that the slave is perfect and has the same data as the master. Nothing is broken, nothing is wrong, everything is working fine. In most cases, this should work, right?
Under what kinds of circumstances will you not get all your data back if you restore the file copy and start MySQL?
Related posts:
- Pop quiz: how can one slave break another slave
- Progress on High Performance MySQL Backup and Recovery chapter
- …
The primary responsibility of MySQL professionals is to establish and run proper backup and recovery plans. The most used method to backup a MySQL database is the mysqldump utility. This mysqldump utility creates a backup file for one or more MySQL databases that consists of DDL/DML statements needed to recreate the databases with their data. To [...]
You might be fortunate enough to allow yourself some downtime, it
is dependent on your application and business model. During this
window it's possible for you to stop your MySQL daemon or lock
your tables to give yourself a consistent backup of your data.
Quite often this is a luxury that you cannot afford. If you are
tied to a strict uptime that doesn't permit any interruption to
your data availability then MySQL Replication could be the answer
you need to grab that essential backup file. Once you've enabled
Replication to a slave then you have the chance to backup by
stopping the replication thread and mitigate the risk of
corruption whilst securing your latest dataset. Using the slave
will also negate any overhead a backup like mysqldump would have
on your active Master server.
Although in this case we are deploying Replication to take
consistent backups of our data, there are many uses for the
mechanism such as scaling out …
Moving your data and tables around comes in many different
flavours. The use of mysqldump is common practice to dump your
data and schema out to a file. It is also possible to pipe your
mysqldump into a 2nd server. Try the code below (adapting the
users and passwords!) in a test environment;
$ mysqldump -u UserA -p p455w0rd --single-transaction
--all-databases --host=Server1 | mysql -u UserA -p p455w0rd
--host=Server2
As you can see from the command we are taking all the databases
in a single transaction into Server2 from Server1. If you're not
using transactional tables substitute the --single-transaction
for --lock-all-tables to ensure you get a consistent copy.
Remember; You must be able to see the 'other' server over the
network and there must be permissions set for remote access from
your feeding Server. For large databases this technique may not
be suitable because of the performance restrictions surrounding …
Few years ago I was looking at crash recovery code, and realized that InnoDB has removed all the comments from the code [this assumption is debunked by Heikki in comments section], related to replay of transaction log. Judging by high quality of comments in the remaining codebase, I realized that it was all done to obscure any efforts to build another InnoDB hot backup solution – competitor to first Innobase standalone offering.
I was enjoying the moment when Percona launched their own implementation of the tool. Since the inception, it became more and more robust and feature rich.
We have used xtrabackup in our environment a lot – just… not for backup – the major use case right now is for cloning server instances – either for building new replicas, shadow servers, or …
[Read more]
I don't usually post these simple tricks, but it came to my
attention today and it's very simple and have seen issues when
trying to get around it. This one tries to solve the question:
How do I restore my production backup to a different
schema? It looks obvious, but I haven't seen many people
thinking about it.
Most of the time backups using mysqldump will include the
following line:
USE `schema`;
This is OK when you're trying to either (re)build a slave or
restore a production database. But what about restoring it to a
test server in a different schema?
The actual trick
Using vi (or similar) editors to edit the line will most
likely result in the editor trying to load the whole backup file
into memory, which might cause paging or even crash the server if
the backup is big enough (I've seen it happen). Using sed
(or similar) might take some time with a big …
MySQL Backup Script has been around for a long time. I have
used it on and off for years but now I’ve needed to make
some improvements. This script is based on VER.
2.6 – http://sourceforge.net/projects/automysqlbackup/
Copyright (c) 2002-2003 wipe_out@lycos.co.uk.
I have added my own Copyright (c) 2010 mark@grennan.com –
http://www.mysqlfanboy.com/Files/automysqlbackup.sh.
But as the code says: This program is distributed in the
hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS
FOR A PARTICULAR PURPOSE. See the GNU General Public
License for more details.
My improvements include:
# VER 2.6 Beta 5 – MTG – (2010-04-18)
# …
I’ve been wanting to write a backup script for a while now that does the following: reads the partition information for the directory that you are backing up into and computes the used/available/percentage-available space. Then it reads the total data size from the MySQL tables and ensures that you have enough space on disk (for [...]