mysqldump

mysqldump | mysql yields 'too many open files' error. Why?

I have a RHEL 5 system with a fresh new hard drive I just dedicated to the MySQL server. To get things started, I used "mysqldump --host otherhost -A | mysql", even though I noticed the manpage never explicitly recommends trying this (mysqldump into a file is a no-go. We're talking 500G of database). This process fails at random inter...

Migrating from MySQL to arbitrary standards-compliant SQL2003 server

Is there an incantation of mysqldump or a similar tool that will produce a piece of SQL2003 code to create and fill the same databases in an arbitrary SQL2003 compliant RDBMS? (The one I'm trying right now is MonetDB) ...

Run MySQLDump without Locking Tables

I want to copy a live production database into my local development database. Is there a way to do this without locking the production database? I'm currently using: mysqldump -u root --password=xxx -h xxx my_db1 | mysql -u root --password=xxx -h localhost my_db1 But it's locking each table as it runs. ...

How do I restore a MySQL .dump file?

I was given a .dump MySQL database file that I need to restore as a database on my Windows Server 2008 machine. I tried using MySQL Administrator, but I got the following error: The selected file was generated by mysqldump and cannot be restored by this application. How do I get this working? ...

Limiting the number of records from mysqldump?

I am trying to load a small sample of records from a large database into a test database. How do you tell mysqldump to only give you n records out of 8 million? Thanks ...

Does mysqldump --password really do what it says?

I'm trying to use mysqldump to dump a schema, and it mostly works but I ran into one curiosity: the -p or --password option seems like it is doing something other than setting the password (as the man page and --help output say it should). Specifically, it looks like it's doing what is indicated here: http://snippets.dzone.com/posts/sho...

How can I use mysqldump to replicate views between accounts?

I'm using mysqldump to replicate a database between accounts on a particular machine. Everything works just great, except when we get to our defined views. Because the dump includes a line like the following ... /*!50013 DEFINER=`user_a`@`localhost` SQL SECURITY DEFINER */ ... when loading the dump into mysql on user_b we receive an...

MySQL permissions issue - should be non-issue

This is making me kind of crazy: I did a mysqldump of a partitioned table on one server, moved the resulting SQL dump to another server, and attempted to run the insert. It fails, but I'm having difficulty figuring out why. Google and the MySQL forums and docs have not been much help. The failing query looks like this (truncated for ...

MYSQL - How would I Export tables specifying only certain fields?

How would I Export tables specifying only certain fields? I am using mysql 5.0 - using either a sql command or mysqldump. My table is X, and the fields I want to export are A,B,C ...

Skip certain tables with mysqldump

Is there a way to restrict certain tables from the mysqldump command? For example, I'd use the following syntax to dump only table1 and table2: mysqldump -u username -p database table1 table2 > database.sql But is there a similar way to dump all the tables except table1 and table2? I haven't found anything in the mysqldump documentat...

How to obtain a correct dump using mysqldump and single-transaction when DDL is used at the same time?

I'm new to MySQL and I'm figuring out the best way to perform an on-line hot logical backup using mysqldump. This page suggests this command line: mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > backup_sunday_1_PM.sql but... if you read the documentation carefully you find that: While a --s...

How to get the exact same output from mysqldump as mysqladmin/backup

On a very important Windows server we use a scheduled task set up by the mysqladmin utility to back up our critical data. I'd like to use the simpler mysqldump command, but I want to ensure that the output is the same. Presumably mysqladmin and mysqldump both share a common core dumping component, so for any given setting in mysqladmin ...

script to convert mysql dump sql file into format that can be imported into sqlite3 db

hi, i have an export sql file containing tables and data from mysql and i want to import it into a sqlite 3 db. please can you tell me the best way? i get error reading file in via sqlite3 binary. ...

Too many open files error on Ubuntu 8.04

mysqldump: Couldn't execute 'show fields from `tablename`': Out of resources when opening file './databasename/tablename#P#p125.MYD' (Errcode: 24) (23) on checking the error 24 on the shell it says >>perror 24 OS error code 24: Too many open files how do I solve this? ...

Problem importing mysql procedures generated from mysqldump --routines

I use the mysqldump tool to make copies of my database. The problem is, when I use the --routines parameter to output my stored procedures along with my data, the generated output causes an error when I try to import it. It goes something like this: % mysqldump --routines MyDB | mysql MyDB2 (where MyDB2 already exists but is empty) ...

How do I suppress the row count for tables added (AUTO_INCREMENT=N) to mysqldump output?

I use the following command to output the schema for a MySQL db: mysqldump --no-data --skip-add-drop-table I will do this for two databases I want to compare, and then diff the two output files. This is my crude way of tracking database changes between environments. However, a minor inconvenience to me is that the row count for each...

Dump mysql view as a table with data

Say I have a VIEW on my database, and I want to send a file to someone to create that views output as a real TABLE on their database. mysqldump of course only exports the 'create view...' statement (well ok it includes the create table, but no data) What I have done is simply duplicate the view as a real table and dump that. But for a...

What is the best way/tool to synchronize two MySql servers (not in the same cluster).

I find myself develop apps in my home, which then, on the commercial server are filled with data. I need a simple and easy way to dump a MySql DB from the commercial server into my private one. Is there a tool/command to do this? (I thought about making my DB a slave, but then, I don't always have the permissions to touch the commercial ...

Complex Database Backup

i am required to back up certain rows from certain tables from a database. often the criteria requires joins and such. what's a good way to accomplish this? (i don't have to use mysqldump). ...

mysql dump outside the command line

Do any of the modern programming languages support mysqldump as a method, Or is it still the domain of command line environments. ...