Tag Info

Hot answers tagged

19

I believe the user just needs select permissions on the tables to be backed up. Edit: This guy says to assign the "lock tables" permission too, which makes sense.


16

According to here - "OS error code 24: Too many open files" which lines up with the more general error 23 "Out of resources". So it seems as though you are running out of file handles. This is usually server-end setting/problem, either in MySQL, or in the OS itself. Perhaps check/adjust the --open-files-limit setting in MySQL itself and see if that ...


14

One thing that may be slowing the process is the key_buffer_size, which is the size of the buffer used for index blocks. Tune this to at least 30% of your RAM or the re-indexing process will probably be too slow. For reference, if you were using InnoDB and foreing keys, you could also disable foreing key checks and re-enable it at the end (using SET ...


13

--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


12

You could set an ssh tunnel and use mysqldump on your local machine. ssh -f -L 3306:localhost:3306 user@remoteserver -N mysqldump -P 3306 -h localhost -u dbuser dbname


9

Also you need SHOW_VIEW, if there are views in your DB.


9

mysqldump is the officially supported application for dumping MySQL databases. If you are getting faulty SQL code, then you should bring this to the attention of the developers by raising tickets. I have used mysqldump over many years and never had any issues with it. Every single time it produced an output that differed from my expectations it was down to ...


9

If you are considering migrating to another DB Server with the exact same version of MySQL, you may want to rsync the datadir from the old server to the new server. This will work regardless of InnoDB file layout or even the presence of MyISAM tables. install the same version of mysql on ServerB that ServerA has On ServerA, run RESET MASTER; to erase all ...


8

You want to run the dump through the mysql client. Example: mysql -uroot -p testimport < database.dump


8

You can use the --ignore command line switch ยท --ignore-table=db_name.tbl_name Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option also can be used to ignore views. someting like shell> mysqldump -u ...


7

mysqldump has a --where option. Assuming you have some sort of toehold to figure out what the last 1000 inserted records are (for instance, an auto-increment field called id), you should be able to tack that onto the mysqldump command, like so: mysqldump --where "1=1 ORDER BY id DESC LIMIT 1000" DB_NAME TBL_NAME The 1=1 is necessary because the "WHERE" ...


7

Here's a backup/maintenance script I use: #!/usr/bin/sh #backup all mysql databases # list MySQL databases and dump each DIR= DATESTAMP=$(date +%Y%m%d) DB_USER= DB_PASS= # remove old backups find ${DIR} -type f -mtime +5 -exec rm -rf {} \; DB_LIST=`mysql -u $DB_USER -p"$DB_PASS" -e'show databases;'` DB_LIST=${DB_LIST##Database} for DB in $DB_LIST; do ...


7

man mysqldump -f, --force Continue even if we get an sql-error.


7

You don't say how you're importing the dump into your local MySQL server, but anything over a few MB should be done on the command line as opposed to a tool like phpMyAdmin. Things like phpMyAdmin are great for quick queries and edits, but since it's running on a web server there will be some limitations on the size of uploads which can be impractical to ...


7

This appears to be a feature for which there is a workaround mysqldump -uroot -p --events --ignore-table=mysql.event mysql > /tmp/mysql.sql" This says dump the event (--events) table and then ignore it.


6

Easiest thing to do would be to do a mysqldump on your linux box, and then import said dump into your local windows database. First, the mysqldump (docs: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html) mysqldump -u [username] -p [any other options desired] [database name] > dump.sql The above creates a file called "dump.sql" in the directory ...


6

It entirely depends on the state of the mysql service at the time of the backup. But the simple answer is no, it is not a safe or good way to make a mysql backup. You should use the included mysqlbackup to make periodic backups of the database. You can make backups to the local machine and then backup the backups with ntbackup.


6

mysqldump dbname | ssh root@remoteserver.com "mysql -D dbname" that should work :-) Set up keys between the systems as well so you can do so w/o needing a login/pass :-)


6

(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 ...


6

A mysqldump output file is valid SQL, so you can mess around in the file and pick and choose what you want. So, yes, if data theoretically disappeared you could selectively replace it from the dump.


6

What to try to fix your problem: You should remove master.info on slave first and restart mysql issue CHANGE MASTER TO MASTER_HOST='XX.XX.XX.XX', MASTER_USER='repl', MASTER_PASSWORD='slavepass'; do mysqldump with '--flush-logs' option on master 'mysql -u user -p < dump.sql' on slave 'show slave status\G' on slave to ensure that it is properly ...


6

My guess would be someone was editing the file with vi, tried to quit (:q) while in writing mode, didn't notice and then saved the file. If this is the case edit the file and remove the extra ":q" characters. Edit: Is there an alias for mysqldump or another script with the same name in the current path (which mysqldump)?


6

First, you need to use full path like: 0 0 * * * /path/to/mysql_backup.sh Second, you need to make sure your script has execute permission or you can invoke it using: 0 0 * * * /bin/bash /path/to/mysql_backup.sh Third, you need to make sure you are executing your cron job with enough privilege.


5

If you must dump the information_schema database then add --skip-lock-tables to it. mysqldump does not dump the INFORMATION_SCHEMA database by default. As of MySQL 5.1.38, mysqldump dumps INFORMATION_SCHEMA if you name it explicitly on the command line, although currently you must also use the --skip-lock-tables option. Before 5.1.38, ...


5

mysqldump is the export tool with which you would create the file to import, e.g. mysqldump -u root -p db > db-export.sql The resulting file will consist largely of SQL CREATE statements, followed by INSERT statements. mysql is the interactive MySQL client, which will also happily take a file full of SQL on stdin and carry out those queries -- in this ...


5

You should not. Given the password at the command line is evil as it is visible to everybody issuing a ps or top. The recommended way is to specify the password in a separate file and then add a command line switch --defaults-extra-file=/etc/mysql/mysqlpassword.cnf The mysqlpassword.cnf then contains (only): [mysqldump] # The following password will be ...


5

Use ssh-keygen, copy the .ssh/ file that's generated to the other server, append the contents to the ./.ssh/authorized_keys file, then you can ssh into the other server without any password.


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: ...


5

Some possibilities: MyISAM tables only support table-level locking, so while your mysqldump is reading the table, any writes will be queued up until it's finished. If a write gets queued, any other reads will then be queued up behind the write, effectively locking the application. There's nothing special about mysqldump in this sense, it's just a single ...



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