Hot answers tagged

20

--skip-add-locks doesn't work : # mysqldump -u root -p`cat mysqlRoot.txt` --databases performance_schema --routines --quote-names --skip-add-locks > mysql_performance_schema mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES you want --skip-lock-tables instead


14

Here is something interesting to consider: Backing up the mysql database limits you greatly in that you can only restore such a database to the same version of mysql you ran the backup from. Here is why: Here is mysql.user from MySQL 5.0.45 mysql> desc mysql.user; +-----------------------+-----------------------------------+------+-----+---------+-------...


12

(I realize this comes 8 months late) This is not a problem of locks, and the offered solutions merely bypass the real problem: A 5.5 mysqldump application should not export the performance_schema database in the first place. Based on my previous experience, I suggest that the mysqldump program you have used is a 5.1 version. How to tell? Issue: mysqldump ...


10

If you use innodb tables, you can use http://www.percona.com/docs/wiki/percona-xtrabackup:start That will take a dump of your database that can be imported by their tools also without locking. I believe if you have myisam tables it locks those.


10

Yes, you definitely want to backup the mysql database -- it's an integral part of your service. While you can reconstruct it's contents from other information, the difficulty of doing so is prohibitive if you're trying to get back to service quickly.


9

The minimum permissions to back up a database are PUBLIC at the server level and DB_BACKUPOPERATOR at a database level. In SQL Server 2005, Microsoft recommended against removing NT AUTHORITY\SYSTEM from the sysadmin role: The NT AUTHORITY\SYSTEM account is also granted a SQL Server login. The NT AUTHORITY\SYSTEM account is provisioned in the ...


8

Ok, first - Tape is more reliable than (disc) drives. Mostly / also because with a drive the drive is on the medium, with a tape you can use another drive to read a tape. Tapes also store huge amounts of data for a relatively low price - they are the most efficient form for offline backup (no real time single file restore) and often used in combination with ...


7

Full backups, successful or failed, do not truncate the log. Only log backups do. So a failed full backup has no impact on the log backup chain, is like no operation was done at all. If that would not be true, you could not possibly create a safe maintenance plan: any failed full backup would put your database at danger if it would interrupt the log backup ...


6

The content of an index is not part of the backup, only the definition of the index. And that will only take a few bytes. When the index is created during restore and all data is indexed, it will be much bigger. This will take time, but it depends on your situation how much time. pg_restore does have an option for concurrent restore (as of version 8.4), use ...


6

ReplicaSet will work in this scenario. However, I cannot tell if having two MongoDB instances at the same server is a good idea -- this depends on the server hardware/software and load. To make sure your backup MongoDB node does not become master, set its priority parameter to 0, e.g. rs.add({_id: 1, host: "localhost:<port>", priority: 0}) NOTE: if ...


6

There is a way to get the result you're after but it's not a backup and should not be considered as such (see gbn's answer to understand why). This is a handy tool for generating scripts that you might use in an application installer and to prime your application's database with objects such as stored procedures, views reference tables and data. But it's ...


6

Wow. Amazing number of questions. I will try to address some, but this answer is not complete yet. how to determine where the bottleneck is. Use top first to see what's going on during the dump. Inspect process CPU usage, process status. D means "waiting for I/O". Is the poor performance caused by heavy I/O operations? Yes, most probably. ...


6

It all depends on the database. If you don't have a way to tell the database engine "get all your files in a consistent state", then you can't do a snapshot, because the snapshot has no guarantee that the files within it are consistent between themselves -- you could take the snapshot at a moment when one file in the database has been changed, and another ...


6

mysqldumps with InnoDB and MyISAM together are treated as mutually exclusive. Here is why: If you can login to mysql while a mysqldump is in progress, you will see something like this: SELECT /* SQL_NO_CACHE */ * FROM tblname By default, mysqldump will do the following: Every database is dumped in alphabetical order Every table dumped per database is ...


6

RESTORE HEADERONLY FROM DISK = 'pathTobackupFile' The database version can be found in the column: DatabaseVersion From Jonathan Kehayias blog post here the number in this column: SQL Server 7.0 databases have version number 515 SQL Server 2000 databases have version number 539 SQL Server 2005 databases have version number 611/612 SQL Server 2008 ...


6

If your PHP Apache web site works with peer, keep this, and let's deal with it. 1. First Method : As authentication is peer based, you have to specify the hostname (usually localhost) using -h option : ssh server "pg_dump -h localhost -U postgres | gzip -c" >/backup/sqlbackup.sql.gz The main issue here is that you will be prompted for the postgres ...


5

Use mysqldump --no-data to dump the database schema but not the data and then restore that dump on your new database server. If you are using triggers or stored procedures you should include the respective parameters in your mysqldump call. See 4.5.4. mysqldump — A Database Backup Program for details.


5

I've had good success with MySQL replication and nightly tarballs. For smaller db, the mysql database, and schema I use a combination of scripts designed to use mysqlhotcopy and mysqldump. InnoDB hot backup is a great commercial product but I'm not sure how it handles mixed tables in the same database. pQd's recommendation for XtraBackup may be good to ...


5

If you are using innodb or another backend that's fully transactional, you can use mysqldump --single-transaction .... I have used this on fairly large (~100GB) databases with good results; if the database is under heavy load it can take hours but it does work without locking your tables. Replication is generally better but sometimes you want a nice solid ...


5

Backups of MySQL are normally done via the command line but if you really want a GUI interface you could use something like HeidiSQL, which allows you to export and import your data as eithe CSV or SQL files, which you can place wherever you like. I do recommend that you learn to use the command line tools, such as mysqldump as that will allow you to ...


5

1) Setup a second server, replicate your DB to it, backup from there This might not be a good solution if you have lots of changes on your master server, since replication on the slave is single threaded. 2) Use http://www.percona.com/doc/percona-xtrabackup/ Or as always, consult the official documentation which outlines other alternatives: http://dev....


5

mysqldump --single-transaction does not lock tables but MyISAM tables are not guaranteed to have consistent dumps with this option. It's better to use mydumper, mydumper locks MyISAM tables and does not lock InnoDB so the dump is consistent.


5

When you run a command from cron, it doesn't have access to your environment information the same way that it would if it were run from the shell. This means that it doesn't find your ssh key, or whatever environment variables you are using to set your SSH agent. If you're using a passwordless key, change your rsync line to this: rsync -chavzP -e "ssh -i /...


5

Use gpg (GnuPG). You'll be able to generate multiple keys and encrypt a file for decryption with any key from chosen list. Also GPG keys are not symmetric — your server only need public keys for encryption. Private keys, used for decryption, might be securely stored somewhere else. You can for example print one private key as QRcode for your director and ...


4

xtrabackup - at least for innodb.


4

I would do like this: Backup database Create new log file Run shrinkfile on the old database Set the Initial Size of old Log File to 1MB and Authogrowth to None. You will have two log files, but one will have only 1 MB. Hope it would help. I hope there is some better way to do that


4

Ok, you really need to head over to the documentation and read it - become a small dba. Expecting people here to copy / paste the EXTREMELY detailed information from BOOKS ONLINE - the SQL Server documentation - is not good behavior. That said, you also areon the wrong site - this is so absolutely NOT a programming issue. There is a sister site (serverfault....


4

A differential backup contains all the changes made to the database since the last full backup. You only need to restore the full backup and the last differential one.


4

My strategy would include: Implementing a Active-Passive Cluster Bare Metal Recovery Backups A warm-site spare if downtime costs justified it


4

Those permissions should be all that's needed for the mysqldump. Since you've granted LOCK TABLES, and it's erroring on LOCK TABLES, seems like the permissions are inconsistent. Have you run a FLUSH PRIVILEGES?



Only top voted, non community-wiki answers of a minimum length are eligible