Server Fault Stack Exchange
Server Fault Stack Exchange

Questions tagged [database-backup]

247 questions
15
votes
4 answers

Should I backup and restore the `mysql` database?

In the process of creating an automated solution for backing up and restoring an entire MySQL server, I've come across the mysql database which seems to contain user accounts, permissions, metadata, that kind of thing. Should this database be…
Daniel Beardsley
  • 527
  • 6
  • 12
15
votes
4 answers

Can't backup the mysql table with mysqldump. SELECT,LOCK TABL command denied for 'cond_instances'

I am having trouble running mysqldump as the mysql root user. When I try to backup the mysql table I get this error: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK…
bryan kennedy
  • 1,641
  • 3
  • 16
  • 31
15
votes
3 answers

How do I create a read only MySQL user for backup purposes with mysqldump?

I'm using the automysqlbackup script to dump my mysql databases, but I want to have a read-only user to do this with so that I'm not storing my root database password in a plaintext file. I've created a user like so: grant select, lock tables on *.*…
stickmangumby
  • 526
  • 2
  • 5
  • 11
14
votes
6 answers

Downtimeless MySQL backups on a budget

My current MySQL backup scenario is to replicate our db to a second server and run mysqldump on that server to remove any downtime from table or row locking. This is working well but costs $150 per month for the second server (Australian hosting is…
Christian
  • 779
  • 1
  • 13
  • 31
11
votes
3 answers

Is this a valid backup strategy for MongoDB?

I've got a single dedicated server with a MongoDB database of around 10GB. I need to do daily backups, but I can't have downtime with the database. Is it possible to use a replica set on a single disk (with 2 instances of mongod running on different…
James Simpson
  • 1,601
  • 2
  • 14
  • 30
11
votes
4 answers

Incremental/Differential MySQL Backup using mysqldump

Are there any techniques to use mysqldump (or similar command) to make incremental or differential backups for MySQL?
Brian Boatright
  • 480
  • 2
  • 9
  • 17
11
votes
2 answers

pg_restore taking much longer than pg_dump

I am regularly saving and later restoring a smallish PostgreSQL database, which is used for testing. Its data is updated regularly as a result of tests, then a new dump must be made, and the dumps are regularly used to recreate the database in a…
sleske
  • 9,851
  • 4
  • 33
  • 44
9
votes
1 answer

Why can NT AUTHORITY\SYSTEM create SQL Server database backups?

I have a scheduled task (in Windows Task Scheduler) that connects to SQL Server using SMO (Windows Authentication) and creates a database backup. So far, this task was running under the Administrator account and I wanted to change it to use the…
Heinzi
  • 2,138
  • 5
  • 30
  • 51
9
votes
3 answers

Do InnoDB tables got locked during mysqldump when mixed with MyISAM?

I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following: MySQL servers they are not replicated each server stands for its own This number can grow, so setting up a master/slave…
tounano
  • 211
  • 2
  • 7
9
votes
2 answers

How to make pg_dump less resource greedy

I have configured cron to invoke pg_dump on a daily basis using following rule: # xyz database backups: 00 01 * * * root umask 077 && pg_dump --user=xyz_system xyz | gzip > /var/xyz/backup/db/xyz/`date -u +\%Y\%m\%dT\%H\%M\%S`.gz Basically, it…
Dariusz Walczak
  • 195
  • 1
  • 1
  • 5
9
votes
1 answer

Does a failed full backup invalidate future transaction log backups?

I have a server running SQL Server 2005. I have nightly full backups, with transaction log backups every 15 minutes. This is a relatively small server so I am not doing any differential backups. If one of the nightly full backups fails, is the…
Warrior Bob
  • 205
  • 1
  • 4
8
votes
1 answer

RDS snapshot: how long does I/O suspension occur?

As we're relying on RDS Postgresql manual backup for our backup strategy, we encountered the issue with the possible downtime of the RDS instance (single AZ) during snapshot creation. According to AWS: Creating this DB snapshot on a Single-AZ DB…
Arcobaleno
  • 191
  • 1
  • 3
8
votes
3 answers

SQL Server transaction log BACKUPS are very large

In SQL Server 2008 R2 I have a database that has full backups nightly and transaction log backups every ten minutes. The database is in full recovery model. The problem is that the backup files for the transaction logs are hundreds of megabytes in…
Dustin Martin
  • 181
  • 1
  • 3
8
votes
6 answers

Optimal way to make MySQL backups for fairly large databases (MyISAM / InnoDB)

Currently we have one beefy MySQL database that runs a couple of high traffic Django based websites as well as some e-commerce websites of decent size. As a result we have a fair amount of large databases using both InnoDB and MyISAM…
WinkyWolly
  • 598
  • 6
  • 19
7
votes
1 answer

pg_dump: [archiver (db)] connection to database failed: FATAL: Peer authentication failed for user "postgres"

I'm trying to backup my Postgres DB to another server but I keep getting denied access. my pg_hba.conf file looks like this: # DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database superuser can access the…
Drace
  • 175
  • 1
  • 1
  • 4
1
2 3
…
16 17