15 Aug
2012
15 Aug
'12
11:58 p.m.
Hi All,
Just looking for guidance, I have had a server crash, HDD failure,
luckily I have good backups through Backup Exec 2012. What I need to
know is what files do I need to restore to a basic install of the same
distro (CentOS 5.8) to get our MySQL databases running again with the
same security permissions?
TIA
Chris
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
16 Aug
16 Aug
12:05 a.m.
Hi Chris
Just looking for guidance, I have had a server crash,
HDD failure,
luckily I have good backups through Backup Exec 2012. What I need to
know is what files do I need to restore to a basic install of the same
distro (CentOS 5.8) to get our MySQL databases running again with the
same security permissions?
Install MySQL server+client (RPMs).
Grab your /etc/my.cnf from the backup, and /var/lib/mysql and copy those over the fresh
install. /var/lib/mysql and below should be owned by mysql:mysql.
This is the *very short* version - reality can be much more complex depending on the many
possible factors involved. Also make sure the newly installed MySQL server is at last the
same version or higher - best to be just the same.
Cheers,
Arjen.
--
Exec.Director @ Open Query (http://openquery.com) MySQL services
Sane business strategy explorations at http://Upstarta.biz
Personal blog at http://lentz.com.au/blog/
2:12 a.m.
Arjen Lentz wrote:
Unless he did the backups while the server was off, isn't it likely
that his backups of /var/lib/mysql will be incoherent? AIUI he should
have backed up a dump of the database.
Just looking
for guidance, I have had a server crash, HDD failure,
luckily I have good backups through Backup Exec 2012. What I need
to know is what files do I need to restore to a basic install of
the same distro (CentOS 5.8) to get our MySQL databases running
again with the same security permissions?
Install MySQL server+client (RPMs). Grab your /etc/my.cnf from the
backup, and /var/lib/mysql and copy those over the fresh
install. /var/lib/mysql and below should be owned by mysql:mysql.
This is the *very short* version [...]
2:23 a.m.
Hi Trent
Arjen Lentz wrote:
Unless he did the backups while the server was off, isn't it likely
that his backups of /var/lib/mysql will be incoherent? AIUI he should
have backed up a dump of the database.
Yes indeed he should have, and while SQL dumps may exist that's not what he wrote.
SQL dumps are not by definition good either, the appropriate command line options depend
on things like which storage engine(s) are used and what character sets. We regularly
encounter dumps that cannot be sanely loaded, primarily due to character set issues.
People are exceedingly slack in this respect, and only "start caring" when
there's a drama which is obviously rather late.
Cheers,
Arjen.
--
Exec.Director @ Open Query (http://openquery.com) MySQL services
Sane business strategy explorations at http://Upstarta.biz
Personal blog at http://lentz.com.au/blog/
Just
looking for guidance, I have had a server crash, HDD failure,
luckily I have good backups through Backup Exec 2012. What I need
to know is what files do I need to restore to a basic install of
the same distro (CentOS 5.8) to get our MySQL databases running
again with the same security permissions?
Install MySQL server+client (RPMs). Grab your /etc/my.cnf from the
backup, and /var/lib/mysql and copy those over the fresh
install. /var/lib/mysql and below should be owned by mysql:mysql.
This is the *very short* version [...] 
3:08 a.m.
SQL dumps are not by definition good either, the appropriate command
line options depend on things like which storage engine(s) are used and
what character sets. We regularly encounter dumps that cannot be sanely
loaded, primarily due to character set issues. People are exceedingly
slack in this respect, and only "start caring" when there's a drama
which is obviously rather late.
Cheers, Arjen.
Is there a generic set of commands that should be applied to a mysqldump
to guard against such things?
Roger
3:20 a.m.
Hi Roger
> SQL dumps are not by definition good either, the
appropriate
> command line options depend on things like which storage
> engine(s) are used and what character sets. We regularly
> encounter dumps that cannot be sanely loaded, primarily due
> to character set issues. People are exceedingly slack in
> this regard and only "start caring" when there's a drama
> which is obviously rather late.
Is there a generic set of commands that should be
applied to a
mysqldump to guard against such things?
If you have any utf8 data, settings on server/database/table/column/connection level
should be appropriate. For connection this means doing SET NAMES=utf8 after creating a
connection. mysqldump does this when you specify --default-character-set=utf8
But I mention the whole range because people sometimes stuff utf8 into fields that are
not, or where the connection is not. Then data can still get mangled, and MySQL has no way
of knowing what you meant. So it's the developer's responsibility.
If you have an InnoDB-only environment, passing the --single-transaction option to
mysqldump will get you a consistent snapshot (without any locking during the dump!)
But as I said, there are many things to consider and these are only a few examples. Also
mind the I/O and CPU time mysqldump eats when you do a dump, you may have to nice it to
not have it interfere with production work - obviously InnoDB's lock-free consistent
reads come in very nicely there, regardless of how long the dump takes it won't have
your app wait...
Regards,
Arjen.
--
Exec.Director @ Open Query (http://openquery.com) MySQL services
Sane business strategy explorations at http://Upstarta.biz
Personal blog at http://lentz.com.au/blog/
17 Aug
17 Aug
2:14 a.m.
On 16/08/12 13:20, Arjen Lentz wrote:
Hi Roger
Also:
* Test that you can recover from a backup onto a different machine. It
won't catch all the things which go wrong only occasionally, but
probably will catch things like character set issues in a mysql dump
file, and will make sure that if things go wrong you know your recovery
procedure before you are under time pressure.
* Monitor your backups in some fashion. Periodic testing is good, but
time consuming. At least have some sort of automated check to see that
new backups
* are coming through on the expected schedule.
* have a size within an expected range.
* contain some bit of data which you write each day (or appropriate
period) to the system that will be backed up.
* verify completion. In the case of mysqlbackup, verify the last line
of the dump file, which should say something like "-- Dump completed on
2012-08-16 01:08:08".
Andrew
> SQL dumps are not by definition good either,
the appropriate
> command line options depend on things like which storage
> engine(s) are used and what character sets. We regularly
> encounter dumps that cannot be sanely loaded, primarily due
> to character set issues. People are exceedingly slack in
> this regard and only "start caring" when there's a drama
> which is obviously rather late.
Is there a generic set of commands that should be
applied to a
mysqldump to guard against such things?
If you have any utf8 data, settings on
server/database/table/column/connection level should be appropriate. For connection this
means doing SET NAMES=utf8 after creating a connection. mysqldump does this when you
specify --default-character-set=utf8
But I mention the whole range because people sometimes stuff utf8 into fields that are
not, or where the connection is not. Then data can still get mangled, and MySQL has no way
of knowing what you meant. So it's the developer's responsibility.
If you have an InnoDB-only environment, passing the --single-transaction option to
mysqldump will get you a consistent snapshot (without any locking during the dump!)
But as I said, there are many things to consider and these are only a few examples. Also
mind the I/O and CPU time mysqldump eats when you do a dump, you may have to nice it to
not have it interfere with production work - obviously InnoDB's lock-free consistent
reads come in very nicely there, regardless of how long the dump takes it won't have
your app wait...
3604
days inactive
3606
days old
6 comments
5 participants
participants (5)
-
Andrew McNaughton -
Arjen Lentz -
Christopher M. Bailey -
Roger -
Trent W. Buck