Hi,

I have a medium sized site/db that does UPDATEs and SELECTs at almost
every single page load, (if you are a registered user).

When I run a nightly backup, (cron job at 3am EST), this causes a lock
on all the tables and effectively locks my database for sometime up to 3
minutes.

As you can imagine, this is unacceptable for my users, (and it causes
slow load time for anonymous users as well).

During backup, the server CPU is not doing too bad, it is a fairly good
server and I don't have that much load on the server.
So I am reluctant to get a new server simply to enable replication.

So, my questions are...

Is it possible to create a 'Slave' database on the same server that I
could use for my backup?

Would that make any real difference on the 'Master' database?

If it is possible, can you provide the link to set this up?

My logic is, if the site gets any bigger then I will setup replication
on a slaver server and do backups from that server, but in the meantime
the current database will not be locked.

Many thanks in advance

Simon

On Tue, 08 Feb 2011 09:41:49 +0200, Simon wrote:
Ungrateful whiners.

You shouldn't need to.

Yes.

No. Well, not in the long run anyway. There'll be a little more disk
used, and a little more CPU, but if you were that tight on either that
it would make a difference, you'd probably already be looking to add a
server anyway.

You should read the MySQL manual Chapters 6 and 16, paying particular
attention to section 6.2. 6.2 contains a non-exclusive list of some
backup strategies and how and when each works better than the other,
based on things like "Are you using MyIASM or InnoDB?" "Are you running
on a filesystem that supports snapshot copies?" etc. You're not wrong,
but you might be seeing only one possible solution...

--
93. If I decide to hold a double execution of the hero and an underling
who failed or betrayed me, I will see to it that the hero is
scheduled to go first.
--Peter Anspach's list of things to do as an Evil Overlord

On Feb 8, 6:07*pm, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:

Enterprise backup or mysqldump?

and who needs to be online at 3AM any way...

"onedbguru" <onedbguru (AT) yahoo (DOT) com> wrote

<snip>

Europe is wide awake when its 3AM in the US.
When its 3AM in London, Singapore is wide awake.

If the site is only used by locals then I'd agree, but if its used by people
from all over the world then 3AM might even turn out to be the busiest time
of day ...
--
Brian Cryer
http://www.cryer.co.uk/brian

onedbguru wrote:
People whose working day may not coincide with YOUR timezone.

In my case, most of the western USA and Australasia..

Here in GB the Australian working day starts at about 2 am. Asian
Working day starts at about 3 a.m..and then the day rolls around through
India and Europe, and onwards to the east coast 5 hours behind, the west
coast 8 hours behind, and finally Hawaii, which I don't know cos don't
deal with Hawaiians much. :-)