Re: mysqldump question
Randy Paries wrote: > Hello, > I have just created a new fedora 4 box with the latest mysqldump > mysqldump Ver 10.9 > > something has changed. > > Before all my tables entries had their own insert statements for each > row. Now each table has one insert with all the values appended to the > end. > > is there switch that puts it back to the old way of separate inserts > for each row? > > Thanks > Randy Hi Randy, since 4.something extended-inserts are used by default. Use mysqldump --extended-insert=false to get the old behaviour. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump question
Is there a reason you can't run mysqldump on the server? You could then gzip it and use any transport method with throttling you want (like wget) -Eric Aaron Wohl wrote: http://www.birdsoft.demon.co.uk/proglib/slowpipe.htm would seem to do what you want... I havent tried it yet, but noted the URL for the next time I needed that functionality. - Original message - From: "Amit M Bhosle" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Date: Fri, 13 May 2005 09:18:00 +0530 Subject: mysqldump question Hi: i was wondering if there's any way to limit the bandwidth used by mysqldump to dump data from remote hosts. since i couldn't find any documentation on this, i assume that mysqldump will use all the available bandwidth of the network. the issue is that i'm looking to fetch data to the tune of 100s of MBs, and i don't want the mysqldump to hog all the bandwidth, thus adversely affecting other communication. thx in advance for ur time. AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump question
http://www.birdsoft.demon.co.uk/proglib/slowpipe.htm would seem to do what you want... I havent tried it yet, but noted the URL for the next time I needed that functionality. - Original message - From: "Amit M Bhosle" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Date: Fri, 13 May 2005 09:18:00 +0530 Subject: mysqldump question Hi: i was wondering if there's any way to limit the bandwidth used by mysqldump to dump data from remote hosts. since i couldn't find any documentation on this, i assume that mysqldump will use all the available bandwidth of the network. the issue is that i'm looking to fetch data to the tune of 100s of MBs, and i don't want the mysqldump to hog all the bandwidth, thus adversely affecting other communication. thx in advance for ur time. AB -- A great idea need not be complicated. http://www.cs.ucsb.edu/~bhosle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump question
At 20:58 -0600 7/21/04, Jim McAtee wrote: (Apologies in advance for the crossposting, but I asked the same questions on the MySQL Windows list and didn't get any replies) I need a simple backup mechanism for MySQL (3.2x) that will backup all databases on a server. Something that can be run from a scheduled Windows batch file. From what I've read, it looks like mysqldump.exe is the simplest method. The plan is to backup the server to a directory (file?) on the same machine and then use my standard backup software to backup that directory to my backup media of choice. Questions: Can I easily restore individual databases and/or individual tables from a backup made with mysqldump? I imagine read locks are placed on the data during a dump. What degree of granularity is employed in the locking when doing an all databases backup? That is, can I expect the entire server to be non-writable during the entire backup, or only one database or one table at a time? I see the mysqlhotcopy script mentioned almost any time mysqldump is recommended. What are the advantages (if any) to using mysqlhotcopy? On Windows, none. mysqlhotcopy runs on Unix and NetWare, but not Windows. mysqlhotcopy tells the the server to lock the table files, then it makes copies of those files while they are locked. Windows file-locking semantics prevent this from working, because you cannot copy a file while the server has it locked. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump question
"Jim McAtee" <[EMAIL PROTECTED]> wrote: > Can I easily restore individual databases and/or individual tables from a > backup made with mysqldump? Yes if you will individually dump the tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump question
On Tue, 2002-07-23 at 21:04, Bill Bernat wrote: > Hey, thanks *so* much for checking into this. > > Your message did help. In case you're curious. First, I had the admin > create a 777 dump directory from root. Still couldn't write out. Then, > though, I just tried going to /tmp as you had done, and that was fine. > /tmp -- I feel like an idiot, but at least I'm an idiot who can dump the > database now:) > > Also, do you happen to know how old is 3.23.21-beta-log? Our MYSQL is > old, but I can't find out how old, is there a version history online > anywhere? Check this: http://www.mysql.com/documentation/mysql/bychapter/manual_News.html#News-3.23.x It shows some release dates. > -Original Message- > From: Diana Soares [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 16, 2002 3:22 AM > To: Bill Bernat > Cc: [EMAIL PROTECTED] > Subject: Re: mysqldump question > > > On Tue, 2002-07-16 at 02:31, Bill Bernat wrote: > > Question: is there anything I need to be aware of when writing dump > > files to a local directory for my user, I'm having the following > > problem. > > > > 1. I create a directory in my own home directory (linux, red hat 7.2) > > ~/dumps and give it 777 permissions > > > > 2. I run " /usr/local/mysql/bin/mysqldump -uroot -p<> --tab > > ~/dumps dbname table1 table2" > > > > 3. The .sql file for table1 gets created, correctly, in the dumps > > directory fine. > > > > 4. mysqldump pukes when trying to write the .txt file: > > > > /usr/local/mysql/bin/mysqldump: Got error: 1: Can't create/write to > > file '/home/bbernat/dumps/customer_relations_database.txt' (Errcode: > > 13) when executing 'SELECT INTO OUTFILE' > > > > QUESTION: > > What have I done wrong and how can I fix it? > > Hi, > > I was curious about your problem and decided to try it. > As user root (in the filesystem, redhat7.2), i created the dir ~/dumps > with 777 permissions and have run > root@cartman:~# mysqldump -uroot -p --tab ~/dumps/ test > (root mysqluser has FILE permissions). It gave me the same error. > > Then i tried with a common filesystem user (dsoares): dsoares@cartman:~$ > mysqldump -uroot -p --tab ~/dumps/ test > > And it worked! > I went to see my /root permissions and it was: dsoares@cartman:~$ ll -d > /root/ > drwxr-x--- 31 root root 4096 Jul 16 11:09 /root/ > > So the problem was there! mysql user doesn't have permissions to access > /root directory. Maybe this is your problem. > > (also as root i tried with /tmp/dumps directory, 777 permissions, and it > worked.) > -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: mysqldump question
Bill, Tuesday, July 23, 2002, 11:04:27 PM, you wrote: BB> Also, do you happen to know how old is 3.23.21-beta-log? Our MYSQL is BB> old, but I can't find out how old, is there a version history online BB> anywhere? Check MySQL manual: http://www.mysql.com/doc/index.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqldump question
Hey, thanks *so* much for checking into this. Your message did help. In case you're curious. First, I had the admin create a 777 dump directory from root. Still couldn't write out. Then, though, I just tried going to /tmp as you had done, and that was fine. /tmp -- I feel like an idiot, but at least I'm an idiot who can dump the database now:) Also, do you happen to know how old is 3.23.21-beta-log? Our MYSQL is old, but I can't find out how old, is there a version history online anywhere? -billb -Original Message- From: Diana Soares [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 3:22 AM To: Bill Bernat Cc: [EMAIL PROTECTED] Subject: Re: mysqldump question On Tue, 2002-07-16 at 02:31, Bill Bernat wrote: > Question: is there anything I need to be aware of when writing dump > files to a local directory for my user, I'm having the following > problem. > > 1. I create a directory in my own home directory (linux, red hat 7.2) > ~/dumps and give it 777 permissions > > 2. I run " /usr/local/mysql/bin/mysqldump -uroot -p<> --tab > ~/dumps dbname table1 table2" > > 3. The .sql file for table1 gets created, correctly, in the dumps > directory fine. > > 4. mysqldump pukes when trying to write the .txt file: > > /usr/local/mysql/bin/mysqldump: Got error: 1: Can't create/write to > file '/home/bbernat/dumps/customer_relations_database.txt' (Errcode: > 13) when executing 'SELECT INTO OUTFILE' > > QUESTION: > What have I done wrong and how can I fix it? Hi, I was curious about your problem and decided to try it. As user root (in the filesystem, redhat7.2), i created the dir ~/dumps with 777 permissions and have run root@cartman:~# mysqldump -uroot -p --tab ~/dumps/ test (root mysqluser has FILE permissions). It gave me the same error. Then i tried with a common filesystem user (dsoares): dsoares@cartman:~$ mysqldump -uroot -p --tab ~/dumps/ test And it worked! I went to see my /root permissions and it was: dsoares@cartman:~$ ll -d /root/ drwxr-x--- 31 root root 4096 Jul 16 11:09 /root/ So the problem was there! mysql user doesn't have permissions to access /root directory. Maybe this is your problem. (also as root i tried with /tmp/dumps directory, 777 permissions, and it worked.) -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question
On Tue, 2002-07-16 at 02:31, Bill Bernat wrote: > Question: is there anything I need to be aware of when writing dump > files to a local directory for my user, I'm having the following > problem. > > 1. I create a directory in my own home directory (linux, red hat 7.2) > ~/dumps and give it 777 permissions > > 2. I run " /usr/local/mysql/bin/mysqldump -uroot -p<> --tab ~/dumps > dbname table1 table2" > > 3. The .sql file for table1 gets created, correctly, in the dumps directory > fine. > > 4. mysqldump pukes when trying to write the .txt file: > > /usr/local/mysql/bin/mysqldump: Got error: 1: Can't create/write to file > '/home/bbernat/dumps/customer_relations_database.txt' (Errcode: 13) when > executing 'SELECT INTO OUTFILE' > > QUESTION: > What have I done wrong and how can I fix it? Hi, I was curious about your problem and decided to try it. As user root (in the filesystem, redhat7.2), i created the dir ~/dumps with 777 permissions and have run root@cartman:~# mysqldump -uroot -p --tab ~/dumps/ test (root mysqluser has FILE permissions). It gave me the same error. Then i tried with a common filesystem user (dsoares): dsoares@cartman:~$ mysqldump -uroot -p --tab ~/dumps/ test And it worked! I went to see my /root permissions and it was: dsoares@cartman:~$ ll -d /root/ drwxr-x--- 31 root root 4096 Jul 16 11:09 /root/ So the problem was there! mysql user doesn't have permissions to access /root directory. Maybe this is your problem. (also as root i tried with /tmp/dumps directory, 777 permissions, and it worked.) -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question
Hi. On Mon, Nov 26, 2001 at 05:39:57PM +0300, [EMAIL PROTECTED] wrote: [...] > diff database_dump/Mtable.txt `mysqldump > --databases MYdatabase --tables MYtable` > ... > and make a conclusions depending of diff output. > > But according to well known SQL standarts the order of stored records is undefined. >However, using MySQL I note that the order of records output is the same as order of >records input in the table and it isn't violates while queries like "SELECT * FROM >table" are performed. > > The question is: may I be sure I'll get absolutely identical dump result > for unchanged table with mysqldump used at different times ? No. For MyISAM tables, it will change on DELETE/UPDATES. For other table types I don't know. Aside from that, you should not rely it anyhow, because this is a implementation detail and it may change without notice according to the SQL specification. Regarding mysqldump, have a look at an earlier post from me: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:msn:10037:lcmmfflncinfjoipdpmc Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqldump question (very basic)
> > Hi. > > I'm trying to run a mysqldump. I enter mysqldump > -databases in order to backup all the databases. For > some reason all I'm getting back is a help on the > mysqldump. What am I doing wrong? > > thanks > > = > It returns help whenever it doesn't understand what you're asking it to do (or if your command is incomplete.) The following line works to backup a remote database to a local PC - Running the same version of MySQL both places, and a user is setup on the MySQL server that allows remote read (IP-restricted in my case, but I have a fixed IP): c:/mysql/bin/mysqldump -h www.mydomain.com -u UserID -pPassword --add-drop-table MyDBName > c:/ServerDumps/MyDBName.sql I run a batch job with one of these for every database every day. Hope that gets you started! Jay Fesco - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question (very basic)
At 12:11 PM -0700 9/10/01, Gene Gurevich wrote: >Hi. > >I'm trying to run a mysqldump. I enter mysqldump >-databases in order to backup all the databases. For >some reason all I'm getting back is a help on the >mysqldump. What am I doing wrong? If you're really entering the option as -databases, try --databases. Otherwise, check whether your version of MySQL is older than 3.23.12, which is when this option was added. -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question
I am on 3.27.37. My mysqldump looks something like this: mysqldump -T /data/home/pmehta/mysql --no-create-info --fields-enclosed-by='"' --fields-t erminated-by=',' --opt mydb MyTable - Original Message - From: "Ed Carp" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, June 10, 2001 5:27 PM Subject: Re: mysqldump question > [EMAIL PROTECTED] ([EMAIL PROTECTED]) writes: > > > Is there a way that I can make mysqldump output a row in a single line ? > > The line is fairly long, and wraps to something like 20 lines or so, > > due to one column that is a clob. Total there are 67K records. > > I thought the default was to not wrap lines - are you running an old version of mysqldump? I couldn't reproduce your problem with mysql 3.23.38. > > > -- > Ed Carp, N7EKG [EMAIL PROTECTED] 214/986-5870 cell phone > http://www.pobox.com/~erc > > I sometimes wonder if the American people deserve to be free - they seem > so unwilling to fight to preserve the few freedoms they have left. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question
[EMAIL PROTECTED] ([EMAIL PROTECTED]) writes: > Is there a way that I can make mysqldump output a row in a single line ? > The line is fairly long, and wraps to something like 20 lines or so, > due to one column that is a clob. Total there are 67K records. I thought the default was to not wrap lines - are you running an old version of mysqldump? I couldn't reproduce your problem with mysql 3.23.38. -- Ed Carp, N7EKG [EMAIL PROTECTED] 214/986-5870 cell phone http://www.pobox.com/~erc I sometimes wonder if the American people deserve to be free - they seem so unwilling to fight to preserve the few freedoms they have left. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question
on 3/7/01 6:39 PM, Cindy at [EMAIL PROTECTED] wrote: > How would I copy ONE table from a database over to another? > > I used this last time: > > mysqldump -h mysql.io.com -u DB1 -p --opt DB1 > ! backup-file.sql > mysql -h mysql.io.com -u DB2 -p DB2 < backup-file.sql > > But it copies the whole shebang (all tables in DB1). The situation I > have now is that I use DB2 (after previously using DB1 up to two weeks > ago), but this afternoon, I dropped one of the tables by accident. > Call it Table1. Table1 is now filled with current entries, but is > missing all its old entries, which can be found in the copy of Table1 > in DB1. I would like to *add* (not replace) all the entries in Table1 > of DB1 to Table1 of DB1. > > I'm guessing this would involve using mysql dump to get the copy of > Table1 out of DB1 and then into DB2 under a new name, and then > use some command within mysql's command line interface to add the rows > from the old table to the current one? Some kind of Merge function? If they're on the same server, try: insert into DB2.table1 select * from DB1.table1 Otherwise, consider selecting INTO OUTFILE on DB1 and then using mysqlimport or LOAD INFILE to bring it in to DB2. Geoff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqldump question
Hi, Just add your table name to the mysqldump command Quentin -Original Message- From: Cindy [mailto:[EMAIL PROTECTED]] Sent: Thursday, 8 March 2001 2:40 p.m. To: [EMAIL PROTECTED] Subject: mysqldump question How would I copy ONE table from a database over to another? I used this last time: mysqldump -h mysql.io.com -u DB1 -p --opt DB1 > ! backup-file.sql mysql -h mysql.io.com -u DB2 -p DB2 < backup-file.sql But it copies the whole shebang (all tables in DB1). The situation I have now is that I use DB2 (after previously using DB1 up to two weeks ago), but this afternoon, I dropped one of the tables by accident. Call it Table1. Table1 is now filled with current entries, but is missing all its old entries, which can be found in the copy of Table1 in DB1. I would like to *add* (not replace) all the entries in Table1 of DB1 to Table1 of DB1. I'm guessing this would involve using mysql dump to get the copy of Table1 out of DB1 and then into DB2 under a new name, and then use some command within mysql's command line interface to add the rows from the old table to the current one? Some kind of Merge function? Help! Thanks, --Cindy -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump question!
try adding a -u (--user=) and -p, this will tell the mysql server what user you are connecting as and it will prompt you for a password. It could be many other things though, depending on how you have set up your access tables. It's usually a bad idea to not restrict connections by either host or password (preferrably both!) On Wed, 10 Jan 2001, Web Master wrote: > Hello, > > can any one help me with my following mysqldump question. > > I want to dump a complete database from one server to another server. I am > trying the following comment > > mysqldump --opt mydatabase | mysql --host=remotehost -C mydatabase > > when I tried, the remote host is failing with invalid log in. > Can any one help me, how I can enter the remote uid and pwd in the same dump? > > Thanks in advance > > > -- > - > Please check "http://www.mysql.com/documentation/manual.php" before > posting. To request this thread, e-mail [EMAIL PROTECTED] > > To unsubscribe, send a message to: > <[EMAIL PROTECTED]> > > If you have a broken mail client that cannot send a message to > the above address (Microsoft Outlook), you can use: > http://lists.mysql.com/php/unsubscribe.php > -- - Please check "http://www.mysql.com/documentation/manual.php" before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to: <[EMAIL PROTECTED]> If you have a broken mail client that cannot send a message to the above address (Microsoft Outlook), you can use: http://lists.mysql.com/php/unsubscribe.php