Re: mysqldump with single-transaction option.
We will tend to use binary backups (Xtrabackup) for full consistent dataset restore (think slave provisioning and disaster recovery) and logical backups to perform single table restores in the event that a rollback may need to occur if someone drops a table or carries out an insane update. We will also use mydumper instead of mysqldump due to the features of compression and encryption. Mysqldump stops being useful on full|large datasets due to it's single-threaded-ness. On Tue, Oct 7, 2014 at 8:35 AM, yoku ts. yoku0...@gmail.com wrote: Maybe no, as you knew. It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. Not only binary logs, each tables in your dump is based the time when mysqldump began to dump *each* tables. It means, for example, table1 in your dump is based 2014-10-07 00:00:00, and next table2 is based 2014-10-07 00:00:01, and next table3 is .. I don't have a motivation for restoring its consistency.. Regards, 2014-10-07 15:44 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com: So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump be of any useful? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 11:55 AM, yoku ts. yoku0...@gmail.com wrote: Hello, If you use any *NOT InnoDB* storage engine, you're right. mysqldump with --single-transaction doesn't have any consistent as you say. If you use InnoDB all databases and tables, your dumping process is protected by transaction isolation level REPEATABLE-READ. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction Regards, 2014-10-07 12:52 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com : It seems to me that once the read lock is acquired, only the binary log coordinates are read. Soon after binary log coordinates are read, lock is released. Is there anything else that happens here? It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. So, to make this dump a consistent one when restoring it, binary log will be applied starting from the binary log coordinates that has been read earlier. This is what I understand. Please correct me if my understanding is wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump with single-transaction option.
Hello, If you use any *NOT InnoDB* storage engine, you're right. mysqldump with --single-transaction doesn't have any consistent as you say. If you use InnoDB all databases and tables, your dumping process is protected by transaction isolation level REPEATABLE-READ. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction Regards, 2014-10-07 12:52 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com: It seems to me that once the read lock is acquired, only the binary log coordinates are read. Soon after binary log coordinates are read, lock is released. Is there anything else that happens here? It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. So, to make this dump a consistent one when restoring it, binary log will be applied starting from the binary log coordinates that has been read earlier. This is what I understand. Please correct me if my understanding is wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump with single-transaction option.
So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump be of any useful? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 11:55 AM, yoku ts. yoku0...@gmail.com wrote: Hello, If you use any *NOT InnoDB* storage engine, you're right. mysqldump with --single-transaction doesn't have any consistent as you say. If you use InnoDB all databases and tables, your dumping process is protected by transaction isolation level REPEATABLE-READ. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction Regards, 2014-10-07 12:52 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com: It seems to me that once the read lock is acquired, only the binary log coordinates are read. Soon after binary log coordinates are read, lock is released. Is there anything else that happens here? It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. So, to make this dump a consistent one when restoring it, binary log will be applied starting from the binary log coordinates that has been read earlier. This is what I understand. Please correct me if my understanding is wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump with single-transaction option.
Maybe no, as you knew. It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. Not only binary logs, each tables in your dump is based the time when mysqldump began to dump *each* tables. It means, for example, table1 in your dump is based 2014-10-07 00:00:00, and next table2 is based 2014-10-07 00:00:01, and next table3 is .. I don't have a motivation for restoring its consistency.. Regards, 2014-10-07 15:44 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com: So, in case of * NOT Innodb * storage engine, say MyISAM, will this dump be of any useful? Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 11:55 AM, yoku ts. yoku0...@gmail.com wrote: Hello, If you use any *NOT InnoDB* storage engine, you're right. mysqldump with --single-transaction doesn't have any consistent as you say. If you use InnoDB all databases and tables, your dumping process is protected by transaction isolation level REPEATABLE-READ. http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction Regards, 2014-10-07 12:52 GMT+09:00 geetanjali mehra mailtogeetanj...@gmail.com : It seems to me that once the read lock is acquired, only the binary log coordinates are read. Soon after binary log coordinates are read, lock is released. Is there anything else that happens here? It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. So, to make this dump a consistent one when restoring it, binary log will be applied starting from the binary log coordinates that has been read earlier. This is what I understand. Please correct me if my understanding is wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump with single-transaction option.
Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump with single-transaction option.
It seems to me that once the read lock is acquired, only the binary log coordinates are read. Soon after binary log coordinates are read, lock is released. Is there anything else that happens here? It means that after lock is released, dump is made while the read and write activity is going on. This dump then, would be inconsistent. So, to make this dump a consistent one when restoring it, binary log will be applied starting from the binary log coordinates that has been read earlier. This is what I understand. Please correct me if my understanding is wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Consultant and Database Security Specialist On Tue, Oct 7, 2014 at 6:22 AM, shawn l.green shawn.l.gr...@oracle.com wrote: Hello Geetanjali, On 9/23/2014 7:14 AM, geetanjali mehra wrote: Can anybody please mention the internals that works when we use mysqldump as follows: *mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql* MySQL manual says: This backup operation acquires a global read lock on all tables at the beginning of the dump (using *FLUSH TABLES WITH READ LOCK http://dev.mysql.com/doc/refman/5.6/en/flush.html*). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH http://dev.mysql.com/doc/refman/5.6/en/flush.html statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. Can anyone explain it more? Please. Which part would you like to address first? I have a feeling it's more about how FLUSH TABLES WITH READ LOCK works but I want to be certain before answering. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Mysqldump routines dump, problem with lock tables.
Do not try to dump or reload information_schema. It is derived meta information, not real tables. -Original Message- From: Rafał Radecki [mailto:radecki.ra...@gmail.com] Sent: Monday, February 04, 2013 12:17 AM To: mysql@lists.mysql.com Subject: Mysqldump routines dump, problem with lock tables. Hi All. I use: # rpm -qa | grep -i percona-server-server Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 My system: # uname -a;cat /etc/redhat-release Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP Wed Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Red Hat Enterprise Linux Server release 6.3 (Santiago) I have a backup script which at some point calls: mysqldump --default-character-set=utf8 --routines --no-data --no- create-info --skip-triggers -S /mysql/database.sock -u backup -pxxx database and I have error: mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using password: YES) when using LOCK TABLES So I thinke that mysqldump locks the table (--add-locks) by default. But for this user: mysql show grants for yyy@'zzz'; +-- --- ---+ | Grants for backup@localhost | +-- --- ---+ | GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz' IDENTIFIED BY PASSWORD ... | | +-- --- ---+ 2 rows in set (0.00 sec) So why is this error showing? When I add --single-transaction to mysqldump everything is ok. But I would like to have this table locked because: mysql SELECT ENGINE - FROM information_schema.TABLES - WHERE TABLE_SCHEMA = 'information_schema' - AND TABLE_NAME = 'routines'; ++ | ENGINE | ++ | MyISAM | ++ so information_schema.tables is myisam. So why do I get the error about LOCK TABLES? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump Got error 1034 Incorrect key file for table
On Tue, 20 Nov 2012, Ricardo Barbosa wrote: Hi all. I'm trying to do a recover on a table for a client, with the following message root@falcon:~# mysqldump -u root -pXXX database -- MySQL dump 10.13 Distrib 5.1.30, for pc-linux-gnu (i686) -- -- Host: localhost Database: database -- -- -- Server version 5.1.30 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; mysqldump: Got error: 1034: Incorrect key file for table 'table1'; try to repair it when using LOCK TABLES root@falcon:~# I'm trying recover with mysql check table table1; +-+---+--+---+ | Table | Op | Msg_type | Msg_text | +-+---+--+---+ | database.table1 | check | Error | Incorrect key file for table 'table1'; try to repair it | | database.table1 | check | error | Corrupt | +-+---+--+---+ 2 rows in set (0.00 sec) mysql repair table table1; +-++--+---+ | Table | Op | Msg_type | Msg_text | +-++--+---+ | database.table1 | repair | Error | Incorrect key file for table 'table1'; try to repair it | | database.table1 | repair | error | Corrupt | +-++--+---+ 2 rows in set (0.00 sec) mysql lock table table1 write; ERROR 1034 (HY000): Incorrect key file for table 'table1'; try to repair it mysql Trying repair with myisamchk and mysqlcheck root@Falcon:~# mysqlcheck -r database table1 -u root -p database.table1 Error : Incorrect key file for table 'table1'; try to repair it error : Corrupt root@falcon:~# root@Falcon:~# cd /data/mysql/database root@Falcon:/data/mysql/database# myisamchk -r *.MYI - recovering (with sort) MyISAM-table 'table1.MYI' Data records: 0 - Fixing index 1 - Any idea. Try to start with MySQL advices for such cases: http://dev.mysql.com/doc/refman/5.6/en/myisam-repair.html iñ Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump not escaping single quotes in field data
mysqldump --databases test --tables ananda test.dmp mysql show create table ananda\G; *** 1. row *** Table: ananda Create Table: CREATE TABLE `ananda` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) On Sat, Jun 16, 2012 at 3:36 AM, Rick James rja...@yahoo-inc.com wrote: Are you using an abnormal CHARACTER SET or COLLATION? SHOW CREATE TABLE Show us the args to mysqldump. -Original Message- From: James W. McNeely [mailto:j...@newcenturydata.com] Sent: Friday, June 15, 2012 10:19 AM To: mysql@lists.mysql.com Subject: mysqldump not escaping single quotes in field data My backups from a mysqldump process are useless, because the dump files are not escaping single quotes in the data in the fields. So, O'Brien kills it - instead of spitting out 'O\'Brien' it spits out 'O'Brien' I don't see anywhere in the documentation about mysqldump where you can tweak this kind of thing. We're using MySQL 5.0.70 enterprise on Ubuntu 8.04.1. I tried to enter this into the Oracle support center but all of the navigational and SR tabs are gone. Maybe our accounting dept. forgot to pay the bill or something. Thanks, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysqldump not escaping single quotes in field data
I have mysql 5.5. I am able to use mysqldump to export data with quotes and the dump had escape character as seen below LOCK TABLES `ananda` WRITE; /*!4 ALTER TABLE `ananda` DISABLE KEYS */; INSERT INTO `ananda` VALUES (1,'ananda'),(2,'aditi'),(3,'thims'),(2,'aditi'),(3,'thims'),(2,'aditi'),(3,'thims'),(2,'aditi'),(3,'thims'),(2,'aditi'),(3,'thims'),(5,'O\'Brien'); /*!4 ALTER TABLE `ananda` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; Import it back to database Database changed mysql drop table ananda; Query OK, 0 rows affected (0.00 sec) mysql --database test test.dmp mysql select * from ananda; +--+-+ | id | name| +--+-+ |1 | ananda | |2 | aditi | |3 | thims | |2 | aditi | |3 | thims | |2 | aditi | |3 | thims | |2 | aditi | |3 | thims | |2 | aditi | |3 | thims | |5 | O'Brien | +--+-+ May be u want to upgrade you database On Fri, Jun 15, 2012 at 10:48 PM, James W. McNeely j...@newcenturydata.comwrote: My backups from a mysqldump process are useless, because the dump files are not escaping single quotes in the data in the fields. So, O'Brien kills it - instead of spitting out 'O\'Brien' it spits out 'O'Brien' I don't see anywhere in the documentation about mysqldump where you can tweak this kind of thing. We're using MySQL 5.0.70 enterprise on Ubuntu 8.04.1. I tried to enter this into the Oracle support center but all of the navigational and SR tabs are gone. Maybe our accounting dept. forgot to pay the bill or something. Thanks, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: mysqldump not escaping single quotes in field data
Are you using an abnormal CHARACTER SET or COLLATION? SHOW CREATE TABLE Show us the args to mysqldump. -Original Message- From: James W. McNeely [mailto:j...@newcenturydata.com] Sent: Friday, June 15, 2012 10:19 AM To: mysql@lists.mysql.com Subject: mysqldump not escaping single quotes in field data My backups from a mysqldump process are useless, because the dump files are not escaping single quotes in the data in the fields. So, O'Brien kills it - instead of spitting out 'O\'Brien' it spits out 'O'Brien' I don't see anywhere in the documentation about mysqldump where you can tweak this kind of thing. We're using MySQL 5.0.70 enterprise on Ubuntu 8.04.1. I tried to enter this into the Oracle support center but all of the navigational and SR tabs are gone. Maybe our accounting dept. forgot to pay the bill or something. Thanks, Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Thanks dear everyone for sharing your views! Let me try the workarounds and keep you posted. Best Rgs, Shafi AHMED _ From: Suresh Kuna [mailto:sureshkumar...@gmail.com] Sent: Saturday, September 24, 2011 8:56 PM To: Prabhat Kumar Cc: Dan Nelson; Shafi AHMED; mysql@lists.mysql.com Subject: Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Hello Shafi, Adding to Prabhat alternatives, you can use --force to the mysqldump command to ignore the errors and continue taking backup. Regarding the error, we need to check whether the table is present or not and the engine type specifically. Thanks Suresh Kuna On Sat, Sep 24, 2011 at 3:31 AM, Prabhat Kumar aim.prab...@gmail.com wrote: correct. mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened. Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used. or try with add --single_transaction to your mysqldump command On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Thanks Suresh Kuna MySQL DBA Get your world in your inbox! Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id! Log on to http://www.sify.com ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Technologies Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at ad...@sifycorp.com
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Hello Shafi, Adding to Prabhat alternatives, you can use --force to the mysqldump command to ignore the errors and continue taking backup. Regarding the error, we need to check whether the table is present or not and the engine type specifically. Thanks Suresh Kuna On Sat, Sep 24, 2011 at 3:31 AM, Prabhat Kumar aim.prab...@gmail.comwrote: correct. mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened. Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used. or try with add --single_transaction to your mysqldump command On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Thanks Suresh Kuna MySQL DBA
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Someone seems to have deleted that file, which contains the description of the corresponding table. Recreate the exact same table (EXACT, including keys, indices, datatypes, encoding, the lot) and copy that tables's .frm file to replace the lost one. Then pray to the elder gods and restart your mysqld to see if it works. - Original Message - From: Shafi AHMED shafi.ah...@sifycorp.com To: mysql@lists.mysql.com Sent: Friday, 23 September, 2011 1:42:26 PM Subject: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Folks I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? Best Rgs, Shafi AHMED -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
correct. mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened. Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used. or try with add --single_transaction to your mysqldump command On Fri, Sep 23, 2011 at 9:49 AM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Sep 23), Shafi AHMED said: I have a mysql database of 200G size and the backup fails due to the foll. Issue. mysqldump: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES Can someone assist pls.? $ perror 24 OS error code 24: Too many open files You need to bump up the max files limit in your OS. It may be defaulting to a small number like 1024. If you can't change that limit, edit your my.cnf and lower the table_open_cache number. You'll lose performance though, since mysql will have to stop accessing some tables to open others. http://dev.mysql.com/doc/refman/5.5/en/not-enough-file-handles.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: mysqldump --ignore-table
Hi Daniel,
you can use a workaround from the shell,
cd /path/to/your/database (e.g.: cd /var/lib/mysql/mydb)
ls -al *table** | awk '{print $8}' | awk -F. '{print --ignore-table=*mydb
*.$1}' | xargs mysqldump -u*root* -p*toor* *--your-flags **mydb*
It's not that beautiful but it should work.
Claudio
2011/6/8 zia mohaddes zia.si...@gmail.com
Dear all,
I am currently trying to figure-out how I could ignore multiple tables in
mysql using a simple a regex. For example I have multiple tables which have
the following structure: mytable1, mytable2, ..,mytable100. And I
would like these tables to be ignore when doing mysqldump by doing
something
like this:
mysqldump --ignore-table = mydb.table*
I am wondering if there is any way do something like this in mysql!
Thank you kindly for the help,
regards,
daniel
--
Claudio
Re: mysqldump except one table
Check the manual? Its here under ignore-table http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html Quoting Adarsh Sharma adarsh.sha...@orkash.com: Dear all, I am researching about different parameters provided by the *mysqldump* utility. It provides a method to dump databases with all tables. My problem is I want to dump a database having 30 tables except one table i.e i have to dump only 29 tables. Is this possible ? Please guide me how to do this. Thanks Best Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump except one table
you can use --ignore-table option for this, mysqldump -u -p dbname --ignore-table=dbname.tablename xyz.sql you can use this option multiple times to ignore multiple tables. Rgds, Jay On Tue, Jan 18, 2011 at 6:18 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, I am researching about different parameters provided by the *mysqldump* utility. It provides a method to dump databases with all tables. My problem is I want to dump a database having 30 tables except one table i.e i have to dump only 29 tables. Is this possible ? Please guide me how to do this. Thanks Best Regards Adarsh Sharma
Re: mySQLdump has a lot of variation for time to complete
In the last episode (Jan 12), Feighen Oosterbroek said: I know that this is a bit of a vague question, but over a period of days mysqldump will take on one day 2min to complete and on the following day 25min to complete, with the resulting sql file being maybe 200M bigger. The dataset isn't really all that large 14Gb on disk with the dump files being around 7G gzipped down to ~700M. What I'd like to know is how can I check to see what is causing the slowness on any given day, or, if indeed, there is something I can check. If you are dumping myisam tables, mysqldump's SELECT statements will queue up behind INSERTs just like any other query, and if you happen to have kicked off an UPDATE that takes 20 minutes to run, your dump will have an elapsed time of 25 minutes just because it had to wait to the UPDATE to finish. If you are on the system on a day that it's taking a long time to run, run some show processlist commands and see if there are any INSERT or UPDATEs running. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Appreciate any help on this. Any alternative of mysqldump is also fine with me to take backup while restricting root login access from localhost only. Thanks in advance. Regards, Tanmay On Wed, Oct 20, 2010 at 10:14 AM, Tanmay Pradhan tanma...@gmail.com wrote: Yes, r...@localhost entry is still present in user table. Only root@'%' is deleted. So it's not obvious to fail. Hi yu.zou, The r...@localhost entry already had all privileges, except this entry had empty password column. u...@localhost entry before GRANT ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | However, I still gave the following cmd. mysql GRANT select, lock tables ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password'; mysql flush privileges; u...@localhost entry after GRANT == ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Of course you know you did not delete r...@localhost, just root @ '%'
which generally should not matter to mysqldump.
What I suspect is the issue here is that the database you are trying
to dump contains procedures/methods that were defined by a user while
logged in as root@'%'. If you recreate those procs as your database
user, this should correct.
- michael dykman
On Tue, Oct 19, 2010 at 8:40 AM, Krishna Chandra Prajapati
prajapat...@gmail.com wrote:
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan tanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Hellpo Krishna,
On 10/19/2010 8:40 AM, Krishna Chandra Prajapati wrote:
Hi Pradhan,
Obviously, it should fail. Since you have deleted the root user which is
used by mysqldump for making connection to mysql server for taking backup
Not true. The utility mysqldump is just a client like any other program
and can authenticate with the MySQL instance (the database daemon) as
any valid user.
Krishna
CGI.COM
On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhantanma...@gmail.com wrote:
Hi,
I am using the following version of MySQL on my Mac OS X Server 10.5.8:
*** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
readline 5.1 ***
In order to restrict root account login from localhost only, I did the
following:
mysql DELETE FROM user WHERE user = 'root' AND host = '%';
mysql FLUSH PRIVILEGES;
After this,
mysqldump failed with the following error:
$ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE
abc.dump
mysqldump: Got error: 1449: The user specified as a definer
('root'@'%') does not exist when using LOCK TABLES
To fix this, you need to reset the DEFINER for a TRIGGER defined within
the database so that it is defined as a valid user account.
http://dev.mysql.com/doc/refman/5.1/en/triggers.html
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'IP
Address' (using password: YES) when using LOCK TABLES
In order to issue the LOCK TABLES command, a user needs certain privileges:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Securing MySQL is fairly easy. Check out this guide in the manual for
details:
http://dev.mysql.com/doc/refman/5.1/en/security.html
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Yes, r...@localhost entry is still present in user table. Only root@'%' is deleted. So it's not obvious to fail. Hi yu.zou, The r...@localhost entry already had all privileges, except this entry had empty password column. u...@localhost entry before GRANT ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost | root| | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y | Y | Y | Y | Y| || | | 0 | 0 | 0 | 0 | However, I still gave the following cmd. mysql GRANT select, lock tables ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password'; mysql flush privileges; u...@localhost entry after GRANT == ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | Host | User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ | localhost
Re: mysqldump error 1064 for database Use command
Hi win.a, I tried with another user the problem still persists. I am getting same error for another user as well. -- Regards, Manasi Save On Tue, 7 Sep 2010 11:41:35 0800, win.a wrote: use another user and dump the data ,eg the root . mysqldump -uroot -p --al-databases AllNew_Databases_20100904.sql All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 8:33 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi win.a, How am I suppose put that command. Could you help me with the syntax: If I am giving it this way:- mysqldump --all-databases AllNew_Databases_20100904.sql then it says it will not work with my system user. nor with this it is working :- mysqldump -u --all-databases AllNew_Databases_20100904.sql -- Regards, Manasi Save On Mon, 6 Sep 2010 20:12:26 0800, win.a wrote: try it without username and password in command line,type it it prompts. All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 3:28 PM, Manasi Save manasi.s...@artificialmachines.com wrote: No it does not. But when I dump database name mydb it does. but not the database with name 1. -- Regards, Manasi Save On Mon, 06 Sep 2010 09:23:21 0200, Jangita wrote: On 06/09/2010 6:54 a, Manasi Save wrote: Dear Nitin, I have newly installed mysql on this server. mysql Select version(); - | version() | - | 5.1.22-rc-Debian_2~ppa5-log | - Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. -- Regards, Manasi Save Does it work when you dump only the database `1`? -- Jangita | 256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=manasi.s...@artificialmachines.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=win@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump error 1064 for database Use command
I'm not sure but you might try with sql_mode set to ANSI. Otherwise try the dump remotely from a Linux box. Regards, Nitin From: Manasi Save manasi.s...@artificialmachines.com To: win.a win@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Wed, September 8, 2010 3:57:40 PM Subject: Re: mysqldump error 1064 for database Use command Hi win.a, I tried with another user the problem still persists. I am getting same error for another user as well. -- Regards, Manasi Save On Tue, 7 Sep 2010 11:41:35 0800, win.a wrote: use another user and dump the data ,eg the root . mysqldump -uroot -p --al-databases AllNew_Databases_20100904.sql All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 8:33 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi win.a, How am I suppose put that command. Could you help me with the syntax: If I am giving it this way:- mysqldump --all-databases AllNew_Databases_20100904.sql then it says it will not work with my system user. nor with this it is working :- mysqldump -u --all-databases AllNew_Databases_20100904.sql -- Regards, Manasi Save On Mon, 6 Sep 2010 20:12:26 0800, win.a wrote: try it without username and password in command line,type it it prompts. All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 3:28 PM, Manasi Save manasi.s...@artificialmachines.com wrote: No it does not. But when I dump database name mydb it does. but not the database with name 1. -- Regards, Manasi Save On Mon, 06 Sep 2010 09:23:21 0200, Jangita wrote: On 06/09/2010 6:54 a, Manasi Save wrote: Dear Nitin, I have newly installed mysql on this server. mysql Select version(); - | version() | - | 5.1.22-rc-Debian_2~ppa5-log | - Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. -- Regards, Manasi Save Does it work when you dump only the database `1`? -- Jangita | 256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=manasi.s...@artificialmachines.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=win@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ntn...@yahoo.com
Re: mysqldump error 1064 for database Use command
On 06/09/2010 6:54 a, Manasi Save wrote: Dear Nitin, I have newly installed mysql on this server. mysql Select version(); +-+ | version() | +-+ | 5.1.22-rc-Debian_2~ppa5-log | +-+ Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. -- Regards, Manasi Save Does it work when you dump only the database `1`? -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump error 1064 for database Use command
No it does not. But when I dump database name mydb it does. but not the database with name 1. -- Regards, Manasi Save On Mon, 06 Sep 2010 09:23:21 0200, Jangita wrote: On 06/09/2010 6:54 a, Manasi Save wrote: Dear Nitin, I have newly installed mysql on this server. mysql Select version(); - | version() | - | 5.1.22-rc-Debian_2~ppa5-log | - Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. -- Regards, Manasi Save Does it work when you dump only the database `1`? -- Jangita | 256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=manasi.s...@artificialmachines.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump error 1064 for database Use command
Dear Nitin, I have newly installed mysql on this server. mysql Select version(); +-+ | version() | +-+ | 5.1.22-rc-Debian_2~ppa5-log | +-+ Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. --Regards, Manasi Save On Sat, 4 Sep 2010 21:00:22 -0700 (PDT), Nitin Mehta wrote: Hi, Have you recently upgraded your MySQL installation? 1064 in earlier days used to mean use of reserved word. Few clients have reported this error in last couple of months after they upgraded from 5.1.41 to higher versions. As of now, I don't have a solution other than changing the name (of database in your case). Hope that helps. Regards, Nitin From: Manasi Save manasi.s...@artificialmachines.comTo: "mysql@lists.mysql.com" mysql@lists.mysql.comSent: Sat, September 4, 2010 12:06:27 PMSubject: mysqldump error 1064 for database Use commandHi All,I have 10 mysql databases all the tables use MyIsAm mysql storage engine.Database names are 1,2,3,4,...10.When I use mysqldump command with --all-databases option. This gives me following error:mysqldump -u myuser -p --all-databases AllNew_Databases_20100904.sqlERROR MESSAGE :-mysqldump: Couldn't execute 'use 1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (1064)Can anyone provide any input on this. I have never got this error before. The backups uptill now was happening properly. Please let me know if I am missing out any information which should be provided to get more clear idea about this error.Any input will be a great help.Thanks in advance.--Regards,Manasi Save
Re: mysqldump error 1064 for database Use command
Hi, Have you recently upgraded your MySQL installation? 1064 in earlier days used to mean use of reserved word. Few clients have reported this error in last couple of months after they upgraded from 5.1.41 to higher versions. As of now, I don't have a solution other than changing the name (of database in your case). Hope that helps. Regards, Nitin From: Manasi Save manasi.s...@artificialmachines.com To: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Sat, September 4, 2010 12:06:27 PM Subject: mysqldump error 1064 for database Use command Hi All, I have 10 mysql databases all the tables use MyIsAm mysql storage engine. Database names are 1,2,3,4,...10. When I use mysqldump command with --all-databases option. This gives me following error: mysqldump -u myuser -p --all-databases AllNew_Databases_20100904.sql ERROR MESSAGE :- mysqldump: Couldn't execute 'use 1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (1064) Can anyone provide any input on this. I have never got this error before. The backups uptill now was happening properly. Please let me know if I am missing out any information which should be provided to get more clear idea about this error. Any input will be a great help. Thanks in advance. -- Regards, Manasi Save
Re: mysqldump backup
redirect your standard errors to some log file.. mysqldump --all-databases --flush-logs --master-data=2 /bk01/all_databases_`date +%a`.sql 2 /tmp/test.log i follow the above syntax for logging the errors in my script. Thanks Anand On Fri, May 28, 2010 at 5:44 PM, Angelina Paul arshup...@gmail.com wrote: Could you please inform me how to test the mysql databases backup failure by using the mysqldump utility. I written a unix script for sending status notification against ten mysql databases. I need to test the nine databases backups have completed successfully and one failed due some reason. Thanks for your help.
Re: mysqldump backup
I looking for a way to corrupt a mysql database forcefully for testing purpose and then the mysqldump utility will fail for taking backup against it. On Fri, May 28, 2010 at 8:06 AM, Anand.S anand@gmail.com wrote: redirect your standard errors to some log file.. mysqldump --all-databases --flush-logs --master-data=2 /bk01/all_databases_`date +%a`.sql 2 /tmp/test.log i follow the above syntax for logging the errors in my script. Thanks Anand On Fri, May 28, 2010 at 5:44 PM, Angelina Paul arshup...@gmail.comwrote: Could you please inform me how to test the mysql databases backup failure by using the mysqldump utility. I written a unix script for sending status notification against ten mysql databases. I need to test the nine databases backups have completed successfully and one failed due some reason. Thanks for your help.
Re: mysqldump backup
In infinite wisdom Angelina Paul arshup...@gmail.com wrote: [1 text/plain; ISO-8859-1 (7bit)] I looking for a way to corrupt a mysql database forcefully for testing purpose and then the mysqldump utility will fail for taking backup against it. kill -9 MYSQLD_PID while the backup is running should give you an indication. -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: mysqldump backup
-Original Message- From: Raj Shekhar [mailto:rajl...@rajshekhar.net] Sent: Friday, May 28, 2010 2:40 PM To: mysql@lists.mysql.com Cc: mysql@lists.mysql.com Subject: Re: mysqldump backup In infinite wisdom Angelina Paul arshup...@gmail.com wrote: [1 text/plain; ISO-8859-1 (7bit)] I looking for a way to corrupt a mysql database forcefully for testing purpose and then the mysqldump utility will fail for taking backup against it. kill -9 MYSQLD_PID while the backup is running should give you an indication. [JS] If you know the file name of one of your databases, how about just using the cat (*nix) or copy (Windows) command to trash a little bit of it. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- Raj Shekhar - If there's anything more important than my ego around, I want it caught and shot now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump hex-blob option
On 3/4/10 7:21 PM, peng yao xwei...@gmail.com wrote: hello erveryone, I have a question about mysqldump.I have some blob data, someone tell me mysqldump the data must use hex-blob options, why? mysqldump just creates a file containing insert statements that when executed rebuild your tables. As a result, this file cannot contain binary data and so BLOBs must be converted to hex. If you know your BLOBs are just text then you can get away with out the hex-blob option. Barry Leslie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump vs phpmyadmin dump
Wang Zi Feng wrote: Hi everyone, Here is a rookie question. The problem what I found is that mysqldump and phpmyadmin generate different size of backup file against same database. I try to dump same database with the 2 different methods, the original database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only 941kb file. mysqldump has a number of different settings, and the file size will vary according to which you use. For example, using extended insert syntax will significantly increase the size of the output, and that could easily account for the difference between your two files. What's probably happening is that the settings you're using when running mysqldump from the command line are different to those used by phpMyAdmin, so you end up with differently formatted files. I know there must be some difference between the two export method, but after I import the 941kb file which mysqldump created into a new database, it just works fine. So I'm not sure if I can use mysqldump as the best option to do mysql backup, can someone can help me to figure out why phpmyadmin would generate twice big file? And I see some post that address it is not recommend to import mysqldump file by using phpmyadmin, because it will cause problem. Importing any large file via phpMyAdmin is likely to have problems, as you'll find yourself limited by the maximum upload file size of the web server where phpMyAdmin is running (that's typically 2Mb for PHP on Apache, although the administrators can change that). But the source of the file is irrelevant; so long as it's within the file upload limit then it doesn't matter whether it was exported by phpMyAdmin itself or created using mysqldump from the command line. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump vs phpmyadmin dump
hi there, thanks for your reply. the mysql server is running on windows xp sp3, every time I use root user to log in. the command line i use for mysqldump is mysqldump --user=root --password=pass testtest.sql phpmyadmin is with following checked Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT Add IF NOT EXISTS Add AUTO_INCREMENT value Enclose table and field names with backquotes Complete inserts Extended inserts I read about the manual saying that mysqldump is default enabled with -opt, which is --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. So I wonder what is the most secure way to backup mysql database to keep data consistency? Thanks and best regards Wang 2009/11/20 Mark Goodge m...@good-stuff.co.uk Wang Zi Feng wrote: Hi everyone, Here is a rookie question. The problem what I found is that mysqldump and phpmyadmin generate different size of backup file against same database. I try to dump same database with the 2 different methods, the original database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only 941kb file. mysqldump has a number of different settings, and the file size will vary according to which you use. For example, using extended insert syntax will significantly increase the size of the output, and that could easily account for the difference between your two files. What's probably happening is that the settings you're using when running mysqldump from the command line are different to those used by phpMyAdmin, so you end up with differently formatted files. I know there must be some difference between the two export method, but after I import the 941kb file which mysqldump created into a new database, it just works fine. So I'm not sure if I can use mysqldump as the best option to do mysql backup, can someone can help me to figure out why phpmyadmin would generate twice big file? And I see some post that address it is not recommend to import mysqldump file by using phpmyadmin, because it will cause problem. Importing any large file via phpMyAdmin is likely to have problems, as you'll find yourself limited by the maximum upload file size of the web server where phpMyAdmin is running (that's typically 2Mb for PHP on Apache, although the administrators can change that). But the source of the file is irrelevant; so long as it's within the file upload limit then it doesn't matter whether it was exported by phpMyAdmin itself or created using mysqldump from the command line. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=frank.zif...@gmail.com
Re: mysqldump Unknown command '\''.
mysqldump is by no means the fastest way to move data between systems. For a bulk job of this magnitude, try this: http://dev.mysql.com/doc/refman/5.1/en/load-data.html - michael dykman On Wed, Oct 14, 2009 at 3:59 AM, Claudio Nanni claudio.na...@gmail.com wrote: We dumped a mysql 4.1.22 database using the mysqldump binaries from its version, We tried to import this in 5.0.82 and we have the error. mysqldump Unknown command '\''. It seems the the extended import (multiple inserts) is somewhere too big since the single insert dump works but takes ages and we need to import this 300gb database in a couple of days. Any suggestions? Thanks -- Claudio -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump warning or actual error?
If you look at the options for mysqldump more closely, you will see that you can specify the version of the server which will be importing the result file. These cause MySQL to taylor the SQL syntax according to the target platform. - michael dykman On Wed, Sep 2, 2009 at 11:03 AM, Matt Neimeyerm...@neimeyer.org wrote: My local windows machine has mysql 5.1.33 installed on it. One of my Mac OSX dev servers has some 4.1 flavor of MySQL on it. When I try to do something like the following: mysqldump -h devserver -u me -p somedb dump.sql I get the following: mysqldump: Error: 'Table 'information_schema.files' doesn't exist' when trying to dump tablespaces It looks like it creates the export fine but I've been ssh-ing into the dev box and doing it locally there just in case Should I be worried? Is there some option that would supress that (that i didn't see in mysqldump --help)? Is it truely harmless? Thanks Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump warning or actual error?
Hi Matt, The error you are getting is very particular to information_schema database. Information_schema does NOT actually have tables, they are views: |INFORMATION_SCHEMA| is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside |INFORMATION_SCHEMA| there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. http://dev.mysql.com/doc/refman/5.1/en/information-schema.html Therefore mysqldump generates error trying to dump tables that does not exist. Regards, Mikhail Berman Matt Neimeyer wrote: My local windows machine has mysql 5.1.33 installed on it. One of my Mac OSX dev servers has some 4.1 flavor of MySQL on it. When I try to do something like the following: mysqldump -h devserver -u me -p somedb dump.sql I get the following: mysqldump: Error: 'Table 'information_schema.files' doesn't exist' when trying to dump tablespaces It looks like it creates the export fine but I've been ssh-ing into the dev box and doing it locally there just in case Should I be worried? Is there some option that would supress that (that i didn't see in mysqldump --help)? Is it truely harmless? Thanks Matt
Re: mysqldump and access rights
ok, i see now the resultings files are owned by mysql. every file has the same user granted, but the wrong one since i can not chown user.group * as normal user. So far i see it is the same problem as with select into outfile Is there a fancy trick for mysqldump so i will create the corresponding select statements ? re, wh peng yao schrieb: you also can do this:#sudo -u mysql mysqldump command or #su - mysql -c mysqldump command 2009/7/24 walter harms wha...@bfs.de muhammad subair schrieb: On Thu, Jul 23, 2009 at 10:10 PM, walter harms wha...@bfs.de wrote: Hi list, i use mysqldump --tab to create database dumps. this will produce txt and sql files. the resulting sql files is owned by the user but the resulting datafile is owned by mysql.mysql is there any way to change that ? re. wh - Hi, you can use this in Linux *# chown user:user /path/to/file.txt* hi, yes i am aware of that but it would be more helpful for me if mysqldump uses the right ownership in the first place. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=xwei...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump and access rights
you also can do this:#sudo -u mysql mysqldump command or #su - mysql -c mysqldump command 2009/7/24 walter harms wha...@bfs.de muhammad subair schrieb: On Thu, Jul 23, 2009 at 10:10 PM, walter harms wha...@bfs.de wrote: Hi list, i use mysqldump --tab to create database dumps. this will produce txt and sql files. the resulting sql files is owned by the user but the resulting datafile is owned by mysql.mysql is there any way to change that ? re. wh - Hi, you can use this in Linux *# chown user:user /path/to/file.txt* hi, yes i am aware of that but it would be more helpful for me if mysqldump uses the right ownership in the first place. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=xwei...@gmail.com
Re: mysqldump and access rights
muhammad subair schrieb: On Thu, Jul 23, 2009 at 10:10 PM, walter harms wha...@bfs.de wrote: Hi list, i use mysqldump --tab to create database dumps. this will produce txt and sql files. the resulting sql files is owned by the user but the resulting datafile is owned by mysql.mysql is there any way to change that ? re. wh - Hi, you can use this in Linux *# chown user:user /path/to/file.txt* hi, yes i am aware of that but it would be more helpful for me if mysqldump uses the right ownership in the first place. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump output
#mysqldump -u username -p -h mysqld_host databasename database_name.sql username is the mysqld server login name mysqld_host is the mysqld server address or hostname databasename is the database which you should dump You can use man mysqldump or mysqldump --help to get more infomation 2009/7/21 zhu dingze mysql.li...@gmail.com we need more information, such as your client and server version, the command that exactly your input etc. 2009/7/14 JingTian jingtian.seu...@gmail.com hi all, i use mysqldump to backup my database, the command line is; mysqldump -p -u -h database_name database_name.sql i find in the database_name.sql, there is a line: Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server can anyone tell me what does it mean? thanks very much, -- Tianjing -- Dingze Zhu We are running the best Chinese MySQL and Solaris Community in China. Welcome to visit http://www.mysqlsystems.com
Re: mysqldump and access rights
# chown mysql.mysql datafile 2009/7/23 walter harms wha...@bfs.de Hi list, i use mysqldump --tab to create database dumps. this will produce txt and sql files. the resulting sql files is owned by the user but the resulting datafile is owned by mysql.mysql is there any way to change that ? re. wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=xwei...@gmail.com
Re: mysqldump output
we need more information, such as your client and server version, the command that exactly your input etc. 2009/7/14 JingTian jingtian.seu...@gmail.com hi all, i use mysqldump to backup my database, the command line is; mysqldump -p -u -h database_name database_name.sql i find in the database_name.sql, there is a line: Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server can anyone tell me what does it mean? thanks very much, -- Tianjing -- Dingze Zhu We are running the best Chinese MySQL and Solaris Community in China. Welcome to visit http://www.mysqlsystems.com
Re: MYSQLDUMP ERROR
MySQL dump calls LOCK TABLES before dumping the data (presumably to prevent data modification halfway through the process). LOCK TABLES has its own privilege (conveniently given the same name) which your user account will have to be given in order to run a mysqldump. On Fri, 2009-05-01 at 15:49 +0530, Krishna Chandra Prajapati wrote: Hi lists, I have given select privileges to database tables. when i am taking mysqldump remotely it's giving error. [prajap...@beta2 prajapati]$ mysqldump --verbose -h 152.20.1.115 -u dip dip states -pdip state.sql -- Connecting to 152.20.1.115... mysqldump: Got error: 1044: Access denied for user 'dip'@'152.20.1.%' to database 'dip' when doing LOCK TABLES Thanks, Krishna Chandra Prajapati -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) you have to use the ` sign, not the ' Uwe :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSes1W0JXG7BUuynnAQJWExAAuzKonpnWPeynnFyvodfGYSP1p9A5ybC6 Azi0j4iGk237HlJ8I8OKh+aBsQGnykzGEQ5eE4xAAaL7bocFlYjHkvaIAhqxzg7c /ZaS9j8k+sGrteAsiA1y8fTj4L3uzLJP+0+crm1yKGKO8Jl9BKxqWl+x3Di6XMYF 1R8OdgBDY1mClUZEqbbBtm0pOs6N/SGcLGyiZNH5xEyRrnXbrh24WY0OQov5pDPj T2G0vC2GpZOZEN1qQeJp5G2ZZ+8gPvZhW+cMElvLNVF3aWkWyd/hEFIxG0cBZ/cR afcreK3AcnAP80ng8150cFN+y5nMR5GExqPDx1j91s9zdW7poMjZiRZKjAOMSqJv Aj+m/MitxGECTOIrCiYo9kP5CyzT6LIng9jyqiNCNR3L2T4sx/LgpLvap+ynwsmz kbqBaLcuPwzCW2f5eIYsfrh5lJMoF86GjTshbFnXXk/EN4etcjTTuFJMiNqPjqvy jonw0b1S17qHRJJPSfLvgk0NEWOB6tYQQuVcWyZyEb0TamfHP04Vdbe9NIKexU04 gafSM5pOBed4DfCsy6qaO9jstrJGMVQlZxxbDzgvsDazMmyh9lTwtup+5TGmxk+I e7HziEFBun8sf1BhDfwm1CJfAQs28lkuD4KctV3DuKyXYqDBftEr0fguxCtqNCtJ 0dTcSkPFzKU= =jim1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
Thanks Uwe, I used ` now. It says ls: z*: no such file or directory exists then, when I enter the password it dumps all the tables. (instead of tables starting with 'z') Yes, I'm a newbie. (I didn't know to use ` instead of '...) But please help me. Plz tell me what I've done wrongly this time. :) Date: Sun, 19 Apr 2009 16:29:48 +0200 From: m...@kiewel-online.ch To: saeho...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) you have to use the ` sign, not the ' Uwe :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSes1W0JXG7BUuynnAQJWExAAuzKonpnWPeynnFyvodfGYSP1p9A5ybC6 Azi0j4iGk237HlJ8I8OKh+aBsQGnykzGEQ5eE4xAAaL7bocFlYjHkvaIAhqxzg7c /ZaS9j8k+sGrteAsiA1y8fTj4L3uzLJP+0+crm1yKGKO8Jl9BKxqWl+x3Di6XMYF 1R8OdgBDY1mClUZEqbbBtm0pOs6N/SGcLGyiZNH5xEyRrnXbrh24WY0OQov5pDPj T2G0vC2GpZOZEN1qQeJp5G2ZZ+8gPvZhW+cMElvLNVF3aWkWyd/hEFIxG0cBZ/cR afcreK3AcnAP80ng8150cFN+y5nMR5GExqPDx1j91s9zdW7poMjZiRZKjAOMSqJv Aj+m/MitxGECTOIrCiYo9kP5CyzT6LIng9jyqiNCNR3L2T4sx/LgpLvap+ynwsmz kbqBaLcuPwzCW2f5eIYsfrh5lJMoF86GjTshbFnXXk/EN4etcjTTuFJMiNqPjqvy jonw0b1S17qHRJJPSfLvgk0NEWOB6tYQQuVcWyZyEb0TamfHP04Vdbe9NIKexU04 gafSM5pOBed4DfCsy6qaO9jstrJGMVQlZxxbDzgvsDazMmyh9lTwtup+5TGmxk+I e7HziEFBun8sf1BhDfwm1CJfAQs28lkuD4KctV3DuKyXYqDBftEr0fguxCtqNCtJ 0dTcSkPFzKU= =jim1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=saeho...@hotmail.com _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: Thanks Uwe, I used ` now. It says ls: z*: no such file or directory exists You need to use the path full qualified, e.g. ls /var/lib/mysql/db/z*, or wherever your mysql installation live. then, when I enter the password it dumps all the tables. (instead of tables starting with 'z') Yes, I'm a newbie. (I didn't know to use ` instead of '...) But please help me. Plz tell me what I've done wrongly this time. :) Date: Sun, 19 Apr 2009 16:29:48 +0200 From: m...@kiewel-online.ch To: saeho...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) you have to use the ` sign, not the ' Uwe :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSes1W0JXG7BUuynnAQJWExAAuzKonpnWPeynnFyvodfGYSP1p9A5ybC6 Azi0j4iGk237HlJ8I8OKh+aBsQGnykzGEQ5eE4xAAaL7bocFlYjHkvaIAhqxzg7c /ZaS9j8k+sGrteAsiA1y8fTj4L3uzLJP+0+crm1yKGKO8Jl9BKxqWl+x3Di6XMYF 1R8OdgBDY1mClUZEqbbBtm0pOs6N/SGcLGyiZNH5xEyRrnXbrh24WY0OQov5pDPj T2G0vC2GpZOZEN1qQeJp5G2ZZ+8gPvZhW+cMElvLNVF3aWkWyd/hEFIxG0cBZ/cR afcreK3AcnAP80ng8150cFN+y5nMR5GExqPDx1j91s9zdW7poMjZiRZKjAOMSqJv Aj+m/MitxGECTOIrCiYo9kP5CyzT6LIng9jyqiNCNR3L2T4sx/LgpLvap+ynwsmz kbqBaLcuPwzCW2f5eIYsfrh5lJMoF86GjTshbFnXXk/EN4etcjTTuFJMiNqPjqvy jonw0b1S17qHRJJPSfLvgk0NEWOB6tYQQuVcWyZyEb0TamfHP04Vdbe9NIKexU04 gafSM5pOBed4DfCsy6qaO9jstrJGMVQlZxxbDzgvsDazMmyh9lTwtup+5TGmxk+I e7HziEFBun8sf1BhDfwm1CJfAQs28lkuD4KctV3DuKyXYqDBftEr0fguxCtqNCtJ 0dTcSkPFzKU= =jim1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=saeho...@hotmail.com 강력한 폴더 공유 기능과 무료 문자 메시지, 오프라인 쪽지 보내기 기능까지! MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSetE9UJXG7BUuynnAQLWXQ/6AhduBZHFSrfsyhmaK0yRjjy6H6+fWZ0s cUfCANJlGbkP1RW6VpMVeF6U2o8xDBcs7m4OLOfLckT5/Lf+RX7AFj9T9T++3oPd DMGZzHEAStApcD0yvYqDPi5Mc88aPUdBaJyNbhc1Ufs+8M42T5sGkqfPWjB5r4Co REdKFt+6JC7VlIBGNn0EdVYA554IQ+93WJus5p9IGk+k5YS5NNBzDiF38SNRszco 2qH9b7I3FP8nxYnlWbpbNdVb0WC5RRk8HojpOE1X+jSJKcWqiebjC+ayVkAytgKL zZUxcmBmQjf2lRpbMatpR2YV1TZKLkWu6nMCfdYFtK/ggxrA23riIbvehjibXRIJ JdLSUp49EWUSx9Fk3DrsuDHiXyZy0mhcEanmBNU5jQSspq6pseYWXDoQUBW1TXY1 i9fs0nItaI+dXZcyvcMbYDRXsttuPUrfzB9lEQORPK2d7htxnsCRZtL0vcMmV8b7 yGVkirLyL4+6RlSnEfGk0lxb+Hi6MgVvOJ2V1J46A0pF6Pab+Mwi0+RoQ3YcLdPI OaWsVGelx+gKsY1szB7kYq2mfHcN+L0Hcdh+6U06+Y2SvJgavVn57sBTislBPfds fl3DfDdHayBFDI2IyrpLPuvq7Zug1Raj4pc8SyMswVeN0MWI4akxl77+hVTeKfrS UYxuDNNhrSc= =OFKv -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
actually, that was stupid of me - you need a list of tables not files. I think the only to do this, and the way we do it, is to run some command like: mysql -eshow tables in db-name like 'z%' tabnames Note the use of double-quotes and single-quotes. then use a loop to read the file tabnames and build a string to tack on the mysqldump command, or issue multiple mysqldump commands. A shell interpreter like bash or a program like perl or php can do this easily. You might try putting the above command in back-tics (`) and then inserting directly into the mysqldump command. 2009/4/19 Jim Lyons jlyons4...@gmail.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt'
I wanted to respond with what I found: APPARMOR blocks mysql from writing to any other directory than the data dir and /tmp. I had to edit the /etc/apparmor.d/usr.sbin.mysqld file and add the following: /backups/mysql** rwk, to the bottom. Hope this helps someone. Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump failing to load data
Hi, MySQL v4.1.22 on Linux 2.6.18-6-686 I have a dump file generate with mysqldump created by a version 4.1.10 server. I want to import the dump file into a different server. When I run mysqldump --database mydb --debug mydumpfile.sql I get the following: -- MySQL dump 10.9 -- -- Host: localhost Database: mydb -- -- -- Server version 4.1.22-debug-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40101 SET sql_mo...@old_sql_mode */; /*!40014 SET foreign_key_chec...@old_foreign_key_checks */; /*!40014 SET unique_chec...@old_unique_checks */; /*!40101 SET character_set_clie...@old_character_set_client */; /*!40101 SET character_set_resul...@old_character_set_results */; /*!40101 SET collation_connecti...@old_collation_connection */; /*!40111 SET sql_not...@old_sql_notes */; The database remains empty. Is there some incompatibility between the data I am trying to import and the installed server? There is the following from the trace log: | cli_read_query_result | mysql_select_db | | enter: db: 'spl2' | | net_clear | | | vio_blocking | | | | enter: set_blocking_mode: 0 old_mode: 1 | | | | exit: 0 | | | vio_blocking | | | vio_read | | | | enter: sd=4, buf=0xb7c97008, size=1047551 | | | | vio_error: Got error 11 during read | | | | exit: -1 | | | vio_read | | | vio_blocking | | | | enter: set_blocking_mode: 1 old_mode: 0 | | | | exit: 0 | | | vio_blocking | | net_clear User time 0.01, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 679, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 4, Involuntary context switches 3 Does anyone have any ideas what the probem might be? Thanx, Dp. hi, mysql error 11 indicates that the system cannot create new thread or resource is temporarily unavailable. two reasons: 1. server is out of memory 2. used up all file descriptors -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump failing to load data
Hi, On Thu, Apr 2, 2009 at 1:18 PM, Virgilio Quilario virgilio.quila...@gmail.com wrote: Hi, MySQL v4.1.22 on Linux 2.6.18-6-686 I have a dump file generate with mysqldump created by a version 4.1.10 server. I want to import the dump file into a different server. When I run mysqldump --database mydb --debug mydumpfile.sql If you are running that command to import then you are sure to have a problem. Use: mysql dumpfile http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html Or in your case mysql mydb mydumpfile.sql Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
? Solved ? Re: mysqldump: Error 2 013: Lost connection to MySQL server
On Tue, 2009-01-13 at 12:19 +0530, Chandru wrote: Hi, Did u try using this command mysqldump --opt db_name db_name.sql -p 2bkp.err Not quite. Firstly, I had to alter the normal backup cron job, and that doesn't happen until late at night. Secondly, yes I added the redirection to capture errors. There were none ( empty file this time ). Thirdly, I didn't use '--opt'. I had no other suggestions yesterday ( before I went to bed anyway - there's 1 in my inbox this morning ), so I did some experimenting of my own and changed the dump command to: mysqldump --skip-opt --add-drop-table --add-locks --create-options --quick --lock-tables --set-charset --disable-keys dbmail dbmail.sql -pSOME_PASSWORD 2bkp.err This made mysql do 1 insert per record. The backup *appears* to have completed successfully. At least the end of the dump file looks valid. It ends dumping the last table, then a view, then I get: -- Dump completed on 2009-01-13 17:23:13 Previously it just finished part-way through dumping a blob. I have yet to do extensive testing on it. I suppose I should try importing the dump file into another server and see if I get the correct number of rows in each table ... The only issue now is that the dump file is much smaller than I would have expected. When using --opt, I was getting 30GB dump files. I would have expected the current format ( 1 insert statement per record ) to be much bigger, but it's 23GB. Now having said that, I did email the current DB administrator and ask him to get people to archive all emails with huge attachments somewhere on a network share ( people have some pretty big attachments ). Also I asked him to get people to clean out their Trash ( which happens only when we tell them to ). So I suppose it's not completely infeasible that this alone is responsible for the difference. Anyway, it's been a very disconcerting experience. It goes without saying that people would expect that anything that gets into a MySQL database should be able to be backed up by mysqldump. And it's worrying that the default --opt can't do that. When I get some time I'll enter a bug ... Thanks for you help Chandru. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ? Solved ? Re: mysqldump: Error 2013: Lost connection to MySQL server
This sounds like you need to raise max_allowed_packet for mysqldump (and possibly mysqld) - these are separate settings for both the client and the server. You can do this via the my.cnf (or ~/.my.cnf) or specify it as an option on the command line mysqldump --opt ... --max_allowed_packet=1G dbname backup-file. On Tue, Jan 13, 2009 at 2:58 PM, Dan d...@entropy.homelinux.org wrote: On Tue, 2009-01-13 at 12:19 +0530, Chandru wrote: Hi, Did u try using this command mysqldump --opt db_name db_name.sql -p 2bkp.err Not quite. Firstly, I had to alter the normal backup cron job, and that doesn't happen until late at night. Secondly, yes I added the redirection to capture errors. There were none ( empty file this time ). Thirdly, I didn't use '--opt'. I had no other suggestions yesterday ( before I went to bed anyway - there's 1 in my inbox this morning ), so I did some experimenting of my own and changed the dump command to: mysqldump --skip-opt --add-drop-table --add-locks --create-options --quick --lock-tables --set-charset --disable-keys dbmail dbmail.sql -pSOME_PASSWORD 2bkp.err This made mysql do 1 insert per record. The backup *appears* to have completed successfully. At least the end of the dump file looks valid. It ends dumping the last table, then a view, then I get: -- Dump completed on 2009-01-13 17:23:13 Previously it just finished part-way through dumping a blob. I have yet to do extensive testing on it. I suppose I should try importing the dump file into another server and see if I get the correct number of rows in each table ... The only issue now is that the dump file is much smaller than I would have expected. When using --opt, I was getting 30GB dump files. I would have expected the current format ( 1 insert statement per record ) to be much bigger, but it's 23GB. Now having said that, I did email the current DB administrator and ask him to get people to archive all emails with huge attachments somewhere on a network share ( people have some pretty big attachments ). Also I asked him to get people to clean out their Trash ( which happens only when we tell them to ). So I suppose it's not completely infeasible that this alone is responsible for the difference. Anyway, it's been a very disconcerting experience. It goes without saying that people would expect that anything that gets into a MySQL database should be able to be backed up by mysqldump. And it's worrying that the default --opt can't do that. When I get some time I'll enter a bug ... Thanks for you help Chandru. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.b.gar...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ? Solved ? Re: mysqldump: Error 2013: Lost connection to MySQL server
On Tue, 13 Jan 2009 18:34:44 -0600, Andrew Garner andrew.b.gar...@gmail.com wrote: This sounds like you need to raise max_allowed_packet for mysqldump (and possibly mysqld) - these are separate settings for both the client and the server. You can do this via the my.cnf (or ~/.my.cnf) or specify it as an option on the command line mysqldump --opt ... --max_allowed_packet=1G dbname backup-file. This is certainly the most common advice for this error, yes. I increased the max_allowed_packet size from 1M to 128M when the problem initially occured. This didn't fix anything. Since dbmail splits up all email body / attachments into small chunks and inserts these chunks in separate records, I really don't see how a max_allowed_packet size of 128M would fail ... especially since the data got in there with a max_allowed_packet size of 1M to begin with. The biggest email in the database is 50M. So even if dbmail *hadn't* split the email into separate records, a max_allowed_packet size of 128M should be *easily* big enough, shouldn't it? As for a max_allowed_packet size of 1G, that just sounds dangerous. The server has 900MB or so of chip RAM and 512MB of swap. It's also running a LOT of other services. I don't want something stupid happening like Linux's out-of-memory-killer coming along and killing MySQL, causing database corruption. Can someone please comment on this? If it's not dangerous, I will try it. As noted in a prior post, I 'successfully' completed a backup last night, and I'm testing it now, but it took 10 hours to complete, and was still running when people came in this morning, which is obviously not desirable, so if I can somehow still use the --opt option of mysqldump by making max_allowed_packet to some absolutely astronomical level without endangering things, maybe that's the way to go. Maybe ... Anyway, thanks for the comments Andrew. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ? Solved ? Re: mysqldump: Error 2013: Lost connection to MySQL server
On Tue, Jan 13, 2009 at 6:06 PM, Dan d...@entropy.homelinux.org wrote: On Tue, 13 Jan 2009 18:34:44 -0600, Andrew Garner andrew.b.gar...@gmail.com wrote: This sounds like you need to raise max_allowed_packet for mysqldump (and possibly mysqld) - these are separate settings for both the client and the server. You can do this via the my.cnf (or ~/.my.cnf) or specify it as an option on the command line mysqldump --opt ... --max_allowed_packet=1G dbname backup-file. This is certainly the most common advice for this error, yes. I increased the max_allowed_packet size from 1M to 128M when the problem initially occured. This didn't fix anything. My apologies. I hadn't read up-thread where this was discussed, and given that, max_allowed_packet is almost certainly not the problem. Sorry for the noise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump: Error 2013: Lost connection to MySQL server
Hi, please increase your interactive_timeout variable to some big number and also try to log the erros if any thing by using the command: mysqldump --opt db_name db_name.sql -p 2bkp.err check if you get some thing in the bkp.err file. Regards, Chandru, www.mafiree.com On Mon, Jan 12, 2009 at 9:07 AM, Daniel Kasak d...@entropy.homelinux.orgwrote: Hi all. I have a 30GB innodb-only database in mysql-5.0.54. I have always done nightly backups with: mysqldump --opt db_name db_name.sql -p Recently this started failing with: Error 2013: Lost connection to MySQL server I have checked all tables for corruption - nothing found. Also as far as I can tell there are no issues with clients using the database. There have been no crashes since I did a full restore. So I assume we can rule out corruption. I have searched around for the error message, and found people discussing the max_allowed_packet option. I've tried increasing the server's max_allowed_packet to many different values. Currently it's at 128M, which is *way* over the default. I have also used the --max_allowed_packet option simultaneously with mysqldump. And lastly, I have been restarting the server after each my.cnf change. The data was inserted via the 'dbmail' application ( http://www.dbmail.org ), while the server was set up with the default max_allowed_packet size. DBMail breaks up message into chunks, and stores these chunks in individual records. I'm not sure what the default size of these chunks is, but I belive it's a reasonable value anyway. What next? I *must* get regular backups working again ... Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=chandru@gmail.com
Re: mysqldump: Error 2013: Lost connection to MySQL server
On Mon, 12 Jan 2009 16:25:12 +0530, Chandru chandru@gmail.com wrote: Hi, please increase your interactive_timeout variable to some big number and also try to log the erros if any thing by using the command: mysqldump --opt db_name db_name.sql -p 2bkp.err check if you get some thing in the bkp.err file. Thanks for responding :) Unfortunately I don't think this is the problem for us. This value is already at 28800 seconds ( equals 8 hours ). The backup certainly never used to take that long. The mysql portion of the backup used to take about 90 minutes. I will retry with your suggestion anyway tonight and post back if something new happens. Here are our server variables which I should have posted the 1st time ( minus version_bdb as it will cause horrible text wrapping ): mysql show variables - where Variable_name != 'version_bdb'; +-+-+ | Variable_name | Value | +-+-+ | auto_increment_increment| 1 | | auto_increment_offset | 1 | | automatic_sp_privileges | ON | | back_log| 50 | | basedir | /usr/ | | bdb_cache_size | 8384512 | | bdb_home| | | bdb_log_buffer_size | 262144 | | bdb_logdir | | | bdb_max_lock| 1 | | bdb_shared_data | OFF | | bdb_tmpdir | | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_filesystem| binary | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8| | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 10 | | datadir | /mnt/stuff/mysql/ | | date_format | %Y-%m-%d| | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | div_precision_increment | 4 | | keep_files_on_create| OFF | | engine_condition_pushdown | OFF | | expire_logs_days| 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024| | have_archive| NO | | have_bdb| DISABLED| | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv| NO | | have_dynamic_loading| YES | | have_example_engine | NO | | have_federated_engine | NO | | have_geometry | YES | | have_innodb | YES
Re: mysqldump: Error 2013: Lost connection to MySQL server
I'm also having a similar issue with some tables I've been trying to dump (total data set is around 3TB). I'm dumping directly from one host to another (mysqldump -hSOURCE DATABASE | mysql -hLOCALHOST DATABASE) using mysql 4.1.22. One system is Solaris 10 SPARC, while the other is Solaris 10 x64 (64bit MySQL as well). I wrote a script that starts a mysqldump process for each table within a database, which shouldn't be a problem since the host currently has around 12G unused memory. Midway through the dump I seem to lose the connection as Dan described. After attempting to drop/re-import (using a single process), the larger tables continue to fail (though at different points) while some of the small-medium sized tables made it across. Anyone else run into this before? Ideas? Thanks, -Aaron
Re: MysqlDump destination file
Hi Nanu, According to me You could use 1. --result-file=file, -r file 2. file (complete path or present working directory would be the place where your file would be saved) Since you are not mentioning the output to be in any different format apart from SQL, it would be a sql file. You can name it as an extension of .sql, however while you import it back it doesnt matter whether your backed up file is on .sql extension or not. Though the idea of keeping extensions is to always help you identify the type of files. Best regards, Amit Sharma On Fri, Dec 19, 2008 at 1:11 PM, Nanu Kalmanovitz n...@kalmanovitz.co.ilwrote: Hi! I'm a newbie trying to use the backup\dump command. My questions are: 1. When using the command mysqldump --single-transaction --quick -uroot -p --all-databases, what is the default destination path (server\volume\folder) and the default file name? 2. If using a redirection destination_file_name, is it possible to add a path before the filename (server\volume\folder) and what is regular\correct file name? TIA Nanu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=amitsha...@affle.co.uk
Re: mysqldump: Got error: 1030
What is your storage engine used? On Fri, Nov 21, 2008 at 8:59 AM, Marten Lehmann [EMAIL PROTECTED] wrote: Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Got error: 1030
What is your storage engine used? On Fri, Nov 21, 2008 at 8:59 AM, Marten Lehmann [EMAIL PROTECTED] wrote: Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Error 5: Out of memory
Show the details of your hardware us. On Thu, Oct 2, 2008 at 3:02 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, Just try the below command on console. It will give that the error is exactly related to what. $perror 5 What is total ram in your box. On Thu, Oct 2, 2008 at 12:26 PM, Uma Bhat [EMAIL PROTECTED] wrote: Hey Guys! I have been googling a lot on this error and read various suggestions. But havnt found an appropriate solution yet. I get this error while taking mysqldump of an InnoDB table (say mytable) mysqldump: Error 5: *Out of memory (Needed 632894352 bytes) when dumping table `mytable` at row: 484911* *current my.cnf settings:* innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 32M max_allowed_packet = 1024M -q with mysqldump option did not help. it resulted in same error. Appreciate your quick response ! Much Thanks, Uma -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Error 5: Out of memory
Hi, Just try the below command on console. It will give that the error is exactly related to what. $perror 5 What is total ram in your box. On Thu, Oct 2, 2008 at 12:26 PM, Uma Bhat [EMAIL PROTECTED] wrote: Hey Guys! I have been googling a lot on this error and read various suggestions. But havnt found an appropriate solution yet. I get this error while taking mysqldump of an InnoDB table (say mytable) mysqldump: Error 5: *Out of memory (Needed 632894352 bytes) when dumping table `mytable` at row: 484911* *current my.cnf settings:* innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 32M max_allowed_packet = 1024M -q with mysqldump option did not help. it resulted in same error. Appreciate your quick response ! Much Thanks, Uma -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Re: mysqldump questions
Whatever you name it Wherever you place it Mysqldump (options) filename --Original Message-- From: Nanu Kalmanovitz Sender: To: mysql@lists.mysql.com Sent: Aug 22, 2008 23:48 Subject: mysqldump questions Hi! What is the name dumped file and where it is created? System is :Novell 6.5 sp6 - Apache 2, MySQL ver. 4.0.26, PHP 5.2.3. TIA Nanu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Sent via BlackBerry from T-Mobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
בעניין: Re: mysqldump questions
Thanks! Nanu [EMAIL PROTECTED] 23/08/2008 09:50:13 Whatever you name it Wherever you place it Mysqldump (options) filename -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump
John, Cut out the sql that creates and loads the problem table and run it by itself to capture the error messages as it loads; I don't know how you're loading, but in mysql, you could do : tee myload.out; source tableload.sql; notee; Then check myload.out for the errorsI had problems with loading 4.1 to 5.1 due to the change in defaults for timestamps between the versions, just corrected the table create sql and it loaded fine. mary -Original Message- From: John Nietzsche [mailto:[EMAIL PROTECTED] Sent: Saturday, July 26, 2008 12:31 PM To: Dan Nelson Cc: mysql@lists.mysql.com Subject: Re: mysqldump [EMAIL PROTECTED] mysqldump -u jpaulo -paivalf -h 200.18.142.98 genweb -- MySQL dump 10.11 -- -- Host: 200.18.142.98Database: genweb -- -- -- Server version 4.0.18-log /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-07-26 19:29:11 [EMAIL PROTECTED] As you can see, that's what happens. Any suggestion? On Sat, Jul 26, 2008 at 3:56 PM, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Jul 26), John Nietzsche said: i am in need to migrate a database between two mysql server. The server source is running mysql 4.0.18-log, the destination server is running mysql 5.0.51a-log. I dumped the database (in the server source) using mysqldump utility. The command i issued in de source sever was: robigo$ mysqldump -u root -probigoroot genweb s.sql It worked alright, but when i try to restore (s.sql) in the server i, mysql (client utility) complains on error and exit, here is the output. robigo$ mysql -u jpaulo -paivALF genweb genweb ERROR 1005 (HY000) at line 11: Can't create table './genweb/contigs.frm' (errno: 150) robigo$ $ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed I wonder if a 4.0 mysqldump isn't directly processable by a 5.0 server? Try running the mysqldump command from the 5.0 server, and connect to the 4.0 server with the -h option. -- Dan Nelson [EMAIL PROTECTED] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
In the last episode (Jul 26), John Nietzsche said: i am in need to migrate a database between two mysql server. The server source is running mysql 4.0.18-log, the destination server is running mysql 5.0.51a-log. I dumped the database (in the server source) using mysqldump utility. The command i issued in de source sever was: robigo$ mysqldump -u root -probigoroot genweb s.sql It worked alright, but when i try to restore (s.sql) in the server i, mysql (client utility) complains on error and exit, here is the output. robigo$ mysql -u jpaulo -paivALF genweb genweb ERROR 1005 (HY000) at line 11: Can't create table './genweb/contigs.frm' (errno: 150) robigo$ $ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed I wonder if a 4.0 mysqldump isn't directly processable by a 5.0 server? Try running the mysqldump command from the 5.0 server, and connect to the 4.0 server with the -h option. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
[EMAIL PROTECTED] mysqldump -u jpaulo -paivalf -h 200.18.142.98 genweb -- MySQL dump 10.11 -- -- Host: 200.18.142.98Database: genweb -- -- -- Server version 4.0.18-log /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-07-26 19:29:11 [EMAIL PROTECTED] As you can see, that's what happens. Any suggestion? On Sat, Jul 26, 2008 at 3:56 PM, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Jul 26), John Nietzsche said: i am in need to migrate a database between two mysql server. The server source is running mysql 4.0.18-log, the destination server is running mysql 5.0.51a-log. I dumped the database (in the server source) using mysqldump utility. The command i issued in de source sever was: robigo$ mysqldump -u root -probigoroot genweb s.sql It worked alright, but when i try to restore (s.sql) in the server i, mysql (client utility) complains on error and exit, here is the output. robigo$ mysql -u jpaulo -paivALF genweb genweb ERROR 1005 (HY000) at line 11: Can't create table './genweb/contigs.frm' (errno: 150) robigo$ $ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed I wonder if a 4.0 mysqldump isn't directly processable by a 5.0 server? Try running the mysqldump command from the 5.0 server, and connect to the 4.0 server with the -h option. -- Dan Nelson [EMAIL PROTECTED] -- 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
John and Dan- would have to see your constraint statement preferably the CREATE TABLE statement to make any kind of intelligent assessment here.. Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Sat, 26 Jul 2008 16:30:55 -0300 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: mysqldump CC: mysql@lists.mysql.com [EMAIL PROTECTED] mysqldump -u jpaulo -paivalf -h 200.18.142.98 genweb -- MySQL dump 10.11 -- -- Host: 200.18.142.98Database: genweb -- -- -- Server version 4.0.18-log /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-07-26 19:29:11 [EMAIL PROTECTED] As you can see, that's what happens. Any suggestion? On Sat, Jul 26, 2008 at 3:56 PM, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Jul 26), John Nietzsche said: i am in need to migrate a database between two mysql server. The server source is running mysql 4.0.18-log, the destination server is running mysql 5.0.51a-log. I dumped the database (in the server source) using mysqldump utility. The command i issued in de source sever was: robigo$ mysqldump -u root -probigoroot genweb s.sql It worked alright, but when i try to restore (s.sql) in the server i, mysql (client utility) complains on error and exit, here is the output. robigo$ mysql -u jpaulo -paivALF genweb genweb ERROR 1005 (HY000) at line 11: Can't create table './genweb/contigs.frm' (errno: 150) robigo$ $ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed I wonder if a 4.0 mysqldump isn't directly processable by a 5.0 server? Try running the mysqldump command from the 5.0 server, and connect to the 4.0 server with the -h option. -- Dan Nelson [EMAIL PROTECTED] -- 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] _ Time for vacation? WIN what you need- enter now! http://www.gowindowslive.com/summergiveaway/?ocid=tag_jlyhm
Re: Mysqldump turn off quotes
In the last episode (Jul 02), Martin Gainty said: How do I get mysqldump to turn off quotes e.g from INSERT INTO table to INSERT INTO table http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_quote-names -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump giving errors
Maybe you have to adjust your file limit by user named mysql. On Sat, Jun 21, 2008 at 7:50 PM, [EMAIL PROTECTED] wrote: Hi All, I am running mysqldump with --no-data option, but it gives error: can't exec 'show fields from ...' out of resources when opening file ... (errorcode 24) (23) perror 24 says: too many open files perror 23 says: too many open files in system mySQL show variables says: open_files_limit: 622 and the error occurs at the 74th table. I have enough GB free space. Is this error caused by my Windows or by MySQL ? Any suggestions how to solve this ? TIA, Cor -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump giving errors [SOLVED]
I found the bug report at http://bugs.mysql.com/bug.php?id=26026 Adding --lock-all-tables as in mysqldump --lock-all-tables --no-data etc. solved the problem. - Original Message - From: Moon's Father To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Saturday, June 21, 2008 12:20 PM Subject: Re: mysqldump giving errors Maybe you have to adjust your file limit by user named mysql. On Sat, Jun 21, 2008 at 7:50 PM, [EMAIL PROTECTED] wrote: Hi All, I am running mysqldump with --no-data option, but it gives error: can't exec 'show fields from ...' out of resources when opening file ... (errorcode 24) (23) perror 24 says: too many open files perror 23 says: too many open files in system mySQL show variables says: open_files_limit: 622 and the error occurs at the 74th table. I have enough GB free space. Is this error caused by my Windows or by MySQL ? Any suggestions how to solve this ? TIA, Cor -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump giving errors [SOLVED]
Leant from this post.Thanks. On Sat, Jun 21, 2008 at 9:35 PM, [EMAIL PROTECTED] wrote: I found the bug report at http://bugs.mysql.com/bug.php?id=26026 Adding --lock-all-tables as in mysqldump --lock-all-tables --no-data etc. solved the problem. - Original Message - *From:* Moon's Father [EMAIL PROTECTED] *To:* [EMAIL PROTECTED] *Cc:* mysql@lists.mysql.com *Sent:* Saturday, June 21, 2008 12:20 PM *Subject:* Re: mysqldump giving errors Maybe you have to adjust your file limit by user named mysql. On Sat, Jun 21, 2008 at 7:50 PM, [EMAIL PROTECTED] wrote: Hi All, I am running mysqldump with --no-data option, but it gives error: can't exec 'show fields from ...' out of resources when opening file ... (errorcode 24) (23) perror 24 says: too many open files perror 23 says: too many open files in system mySQL show variables says: open_files_limit: 622 and the error occurs at the 74th table. I have enough GB free space. Is this error caused by my Windows or by MySQL ? Any suggestions how to solve this ? TIA, Cor -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump character set
If you create all your databases and tables with utf8,then every thing is fine. On Mon, Apr 28, 2008 at 1:43 AM, Velen [EMAIL PROTECTED] wrote: Hi, I'm storing some ascii codes in a table. When I do a dump using mysqldump from the server and then restoring it on another station, the ascii codes in the table has changed. But if i'm accessing the table from another station the code is good. Even if I insert it from a station, it goes fine in the server. Anyone knows why it changes when using mysqldump? How can I prevent this problem to happen in the future? Regards, Velen -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump and auto_increment
Thanks. That sounds pretty labor intensive, since I'd have to do the copy for each table. That makes the process much harder to automate. And I'm not sure the end result would be what I wanted anyway. I would like to be able to do this without copying data around. Does anybody have another suggestion? On Mon, May 19, 2008 at 11:00 PM, Moon's Father [EMAIL PROTECTED] wrote: You can import your data into a test database,then export the data using statement select ... into ... then You can complare the two. On Tue, May 20, 2008 at 9:14 AM, Dan Lipsitt [EMAIL PROTECTED] wrote: Is there a set of flags for mysqldump that will include the auto_increment specifier for columns, but leave out the AUTO_INCREMENT=x saved values? I want to compare the schema of two versions of a database, without considering the data. In my opinion, the saved auto increment counter is part of the data, not part of the schema, but the --no-data flag doesn't seem to share my opinion. If I run mysqldump a -d a.sql mysqldump b -d b.sql diff a.sql b.sql I want to see no output if the schemas are the same, regardless of how many rows of data each database has. Is there a way to do this with just mysqldump or mysql? Thanks, Dan -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump and auto_increment
You can import your data into a test database,then export the data using statement select ... into ... then You can complare the two. On Tue, May 20, 2008 at 9:14 AM, Dan Lipsitt [EMAIL PROTECTED] wrote: Is there a set of flags for mysqldump that will include the auto_increment specifier for columns, but leave out the AUTO_INCREMENT=x saved values? I want to compare the schema of two versions of a database, without considering the data. In my opinion, the saved auto increment counter is part of the data, not part of the schema, but the --no-data flag doesn't seem to share my opinion. If I run mysqldump a -d a.sql mysqldump b -d b.sql diff a.sql b.sql I want to see no output if the schemas are the same, regardless of how many rows of data each database has. Is there a way to do this with just mysqldump or mysql? Thanks, Dan -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Mysqldump
On Tue, Apr 15, 2008 at 3:11 PM, minky arora [EMAIL PROTECTED] wrote: Hi Gurus, I am a newbie.Please bear with me. Could someone pls guide me as to the best way of adding excel files as tables to Mysql ? mysqldump is for exporting data. What you're looking for is the LOAD DATA [LOCAL] INFILE command. Dump out your Excel data to a CSV and import it by using the manual entry as a guide: http://dev.mysql.com/doc/refman/5.0/en/load-data.html -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump
Am Dienstag, den 15.04.2008, 15:11 -0400 schrieb minky arora: Could someone pls guide me as to the best way of adding excel files as tables to Mysql ? You can import CSV files into MySQL: http://dev.mysql.com/doc/refman/5.1/en/load-data.html Norbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
use can say mysqldump --help, it would give you all the options. To speedup the dump, you can use -e -q and --single-transaction, regards anandkl On 4/16/08, minky arora [EMAIL PROTECTED] wrote: Thanks. I will successful in executing LOAD DATA INFILE Now i need to do a SqlDump as well to get a backup of the DB..I looked at the syntax but am not sure if /what all options to use. I only have one DB. Could someone tell me if I really need to specify any options ?
Re: mysqldump on specific columns only?
On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] wrote:
I'm trying to dump all the columns of a table but one in a
restructuring of my schema. I found this post:
http://www.jsw4.net/info/listserv_archives/mysql/05-wk49/msg00131.html
which seems to indicate that this isn't possible, but I was wondering
if anyone had any suggestions. My current solution is to do a simple
search and replace, but that won't work for everything. I could
always do regex search/replace, as well, but I'm hoping for something
native to mysqldump, since the files are going to be rather big.
Thanks,
Waynn
I would probably use INTO OUTFILE. Any particular reason it does not work?
CREATE TABLE `t` (
`c1` varchar(10) NOT NULL,
`c2` varchar(10) NOT NULL,
`c3` varchar(10) NOT NULL
);
INSERT INTO `t` (`c1`, `c2`, `c3`) VALUES
('1', '2', '3'),
('4', '5', '6'),
('7', '8', '9'),
('10', '11', '12');
mysql select c1,c2 from t INTO OUTFILE 'outfile';
Query OK, 4 rows affected (0.00 sec)
outfile contents:
1 2
4 5
7 8
10 11
--
Rob Wultsch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] wrote: Subject: Re: mysqldump on specific columns only? I'm trying to dump all the columns of a table but one in a restructuring of my schema. By dump, I assume you mean output, not delete. I suspect I'm too inexperienced in MySQL or I don't understand the question, because I don't see why you haven't mentioned ALTER TABLE DROP COLUMN. Or, if you don't want to touch the original database but rather a copy database: mysqldump, load it into another database, ALTER TABLE DROP COLUMN. It's true that you wrote since the files are going to be rather big. The second notion has the disadvantage that the data will be copied three times (mysqldump, load, ALTER TABLE). On the other hand, you have the full reliability of mysqldump, with its care in copying CREATE TABLEs and data correctly. Am I perhaps misunderstanding? On Sun, 9 Mar 2008, Rob Wultsch [EMAIL PROTECTED] wrote: I would probably use [SELECT] INTO OUTFILE. Any particular reason it does not work? It's not well-suited for restructuring of my schema. mysqldump emits SQL that can be used immediately to create the table with exactly the same data types and column names and keys and other properties (like NOT NULL), and to insert data into columns with quoting and escaping as needed, and nice little flourishes like DROP TABLE IF EXISTS, LOCK TABLES, UNLOCK TABLES. mysql select c1,c2 from t INTO OUTFILE 'outfile'; Query OK, 4 rows affected (0.00 sec) outfile contents: 1 2 4 5 7 8 10 11 That provides only the data to insert. Further, it would require considerable massaging to get it into the form of INSERT statements, though for all I know MySQL provides other mechanisms to load that sort of data. But those are tab-separated columns: what if one of the columns were a text field with a literal tab? -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
Thanks for the responses, Inline: On Sun, Mar 9, 2008 at 7:49 PM, Tim McDaniel [EMAIL PROTECTED] wrote: On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] wrote: Subject: Re: mysqldump on specific columns only? I'm trying to dump all the columns of a table but one in a restructuring of my schema. By dump, I assume you mean output, not delete. Indeed, I meant output. I suspect I'm too inexperienced in MySQL or I don't understand the question, because I don't see why you haven't mentioned ALTER TABLE DROP COLUMN. Or, if you don't want to touch the original database but rather a copy database: mysqldump, load it into another database, ALTER TABLE DROP COLUMN. It's true that you wrote since the files are going to be rather big. The second notion has the disadvantage that the data will be copied three times (mysqldump, load, ALTER TABLE). On the other hand, you have the full reliability of mysqldump, with its care in copying CREATE TABLEs and data correctly. Am I perhaps misunderstanding? ALTER TABLE unfortunately takes longer than dumping and recreating the tables. I started a previous thread on this, and the consensus was recreating everything was faster, and I'm taking advantage of this downtime to restructure our schema, and add a few other columns that will be needed. The initial impetus was expanding an INT to a BIGINT, but in the presence of a foreign key. So that would require dropping the FK, expanding the INT to BIGINT in two tables, then recreating the FK. I would also want to add three more columns while I'm at it. And in this case, the cost of all those operations is significantly higher than just dumping all the tables, truncating/dropping the tables, recreating the tables, then importing. On Sun, 9 Mar 2008, Rob Wultsch [EMAIL PROTECTED] wrote: I would probably use [SELECT] INTO OUTFILE. Any particular reason it does not work? It's not well-suited for restructuring of my schema. mysqldump emits SQL that can be used immediately to create the table with exactly the same data types and column names and keys and other properties (like NOT NULL), and to insert data into columns with quoting and escaping as needed, and nice little flourishes like DROP TABLE IF EXISTS, LOCK TABLES, UNLOCK TABLES. I'm actually not going to be using the exact same schema anyway, so SELECT INTO does work, but ... let me continue this thought below. mysql select c1,c2 from t INTO OUTFILE 'outfile'; Query OK, 4 rows affected (0.00 sec) outfile contents: 1 2 4 5 7 8 1011 That provides only the data to insert. Further, it would require considerable massaging to get it into the form of INSERT statements, though for all I know MySQL provides other mechanisms to load that sort of data. But those are tab-separated columns: what if one of the columns were a text field with a literal tab? MySQL does provide LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.0/en/load-data.html), but I was hoping to do it through mysqldump instead of SELECT INTO because I assumed the performance was faster. If that's not the case, though, I'll go ahead and use SELECT INTO OUTFILE and LOAD DATA INFILE. I guess most any character is fine as a delimiter, since my fields should all just be IDs... Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
MySQL does provide LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.0/en/load-data.html), but I was hoping to do it through mysqldump instead of SELECT INTO because I assumed the performance was faster. If that's not the case, though, I'll go ahead and use SELECT INTO OUTFILE and LOAD DATA INFILE. I guess most any character is fine as a delimiter, since my fields should all just be IDs... Thanks, Waynn OK. Why not just create the new table and SELECT... INSERT . I would think that would be the fastest solution of all. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Mike Spreitzer wrote: So I am trying to use mysqldump --tab for the first time. I am running into the same problem everybody does on their first try --- Errcode: 13. I have set the permissions on the target directory to be completely liberal --- anybody can do anything with it --- and I still get Errcode: 13. I can even write into that directory when logged in as mysql (UID 100, GID 100, and yes that is what the mysqld process is running as). What's going wrong here? SELinux enabled? What's the syntax of the command you're using? Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
I am running MySQL 5.0.51a-community on RedHat Enterprise Linux 4. Here is a further typescript showing the failure: [EMAIL PROTECTED] /]# mysqldump -u root -p --skip-opt --quick --tab=/dump1 wyky red1_p2 Enter password: mysqldump: Got error: 1: Can't create/write to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' [EMAIL PROTECTED] /]# ls -ld /dump1 drwxrwxrwx 2 mysql mysql 4096 Feb 22 19:11 /dump1 [EMAIL PROTECTED] /]# ls -l /dump1 total 16 -rw-r--r-- 1 mysql mysql 29 Feb 22 18:38 foo.bar -rw-r--r-- 1 root root 742 Feb 22 19:11 red1_p2.sql [EMAIL PROTECTED] /]# Thanks, Mike Jed Reynolds [EMAIL PROTECTED] 02/22/08 07:04 PM Please respond to [EMAIL PROTECTED] To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql list mysql@lists.mysql.com Subject Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' Mike Spreitzer wrote: So I am trying to use mysqldump --tab for the first time. I am running into the same problem everybody does on their first try --- Errcode: 13. I have set the permissions on the target directory to be completely liberal --- anybody can do anything with it --- and I still get Errcode: 13. I can even write into that directory when logged in as mysql (UID 100, GID 100, and yes that is what the mysqld process is running as). What's going wrong here? SELinux enabled? What's the syntax of the command you're using? Jed
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
what happens when you delete the files that are already in there? Looks like you're dumping to a file owned by root. Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Sorry if I have confused people by presenting evidence in the wrong order. Also, it seems that the .sql file is written by the client while the .txt file is (attempted to be) written by the server. Here is a single typescript with all the evidence: [EMAIL PROTECTED] ~]# cd / [EMAIL PROTECTED] /]# rm -f dump1/* [EMAIL PROTECTED] /]# ls -ld dump1 drwxrwxrwx 2 mysql mysql 4096 Feb 22 20:45 dump1 [EMAIL PROTECTED] /]# ls -l dump1 total 0 [EMAIL PROTECTED] /]# mysqldump -u root -p --skip-opt --quick --tab=/dump1 wyky red1_p2 Enter password: mysqldump: Got error: 1: Can't create/write to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' [EMAIL PROTECTED] /]# ls -l dump1 total 8 -rw-r--r-- 1 root root 742 Feb 22 20:46 red1_p2.sql [EMAIL PROTECTED] /]# ps axlw | grep mysqld 0 0 8494 1 25 0 3408 944 wait Spts/5 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/xd026.watson.ibm.com.pid 4 100 8517 8494 16 0 251828 154772 - Sl ? 4:05 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/xd026.watson.ibm.com.pid --skip-external-locking 0 0 26228 26177 16 0 4704 672 pipe_w S+ pts/1 0:00 grep mysqld [EMAIL PROTECTED] /]# su - mysql -bash-3.00$ id uid=100(mysql) gid=101(mysql) groups=101(mysql) context=user_u:system_r:unconfined_t -bash-3.00$ cd /dump1 -bash-3.00$ date foo.bar -bash-3.00$ ls -l total 16 -rw-r--r-- 1 mysql mysql 29 Feb 22 20:46 foo.bar -rw-r--r-- 1 root root 742 Feb 22 20:46 red1_p2.sql -bash-3.00$ Thanks, Mike Jed Reynolds [EMAIL PROTECTED] 02/22/08 07:24 PM Please respond to [EMAIL PROTECTED] To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql list mysql@lists.mysql.com Subject Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' what happens when you delete the files that are already in there? Looks like you're dumping to a file owned by root. Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Mike Spreitzer wrote: Sorry if I have confused people by presenting evidence in the wrong order. Also, it seems that the .sql file is written by the client while the .txt file is (attempted to be) written by the server. Here is a single typescript with all the evidence: [EMAIL PROTECTED] ~]# cd / [EMAIL PROTECTED] /]# rm -f dump1/* [EMAIL PROTECTED] /]# ls -ld dump1 drwxrwxrwx 2 mysql mysql 4096 Feb 22 20:45 dump1 [EMAIL PROTECTED] /]# ls -l dump1 total 0 [EMAIL PROTECTED] /]# mysqldump -u root -p --skip-opt --quick --tab=/dump1 wyky red1_p2 Enter password: mysqldump: Got error: 1: Can't create/write to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' I'm reading thru http://dev.mysql.com/doc/refman/5.0/en/select.html and it says that it will not write to an already existing file (so you deleted the old files) and you need the FILE privilege, and that it writes the file mode 777 as the user running the client (and suggests not using root). I would try $ rm -f /dump1/* $ echo SELECT * FROM red1_p2 INTO OUTFILE '/dump1/red1_p2.txt'; \ | mysql -u root -p to see if this is a mysql permissions issue. The above will create a file owned by the mysql process. You might try changing the directory to /tmp or /var/tmp to see if that makes some kind of magical difference. Might also check your grants for [EMAIL PROTECTED] Unlikely, but possible. Also, check /var/log/messages and if there's any SELinux warnings. You might have the option 'secure_file_priv' set? http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_secure-file-priv Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump (in crontab) failing with 'errno 32' even though it worked last week
Maybe too much data is being pumped in through the pipe This sounds spot on. I redid the command to take the pipe out of the equation and it seems to be working now. Thanks for the feedback. Rolando Edwards-3 wrote: 'errno 32 on write' That's a broken pipe error (Run 'perror 32' in Linux) Maybe too much data is being pumped in through the pipe. Instead of this : /usr/bin/mysqldump --all-databases -u root | /usr/bin/gzip -c /backups/mysql_backup.gz Try it this way : /usr/bin/mysqldump --all-databases -u root AllData.txt /usr/bin/gzip -c /backups/mysql_backup.gz AllData.txt -Original Message- From: FMGreen [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 04, 2007 11:52 AM To: mysql@lists.mysql.com Subject: mysqldump (in crontab) failing with 'errno 32' even though it worked last week I use the mysqldump command (as below) in crontab to do a dump of my database each night. Up until last week this was working fine. Last week (when I was out of the office) there was a problem with the db and a colleague of mine had to do a manual dump. When he tried to run mysqldump (using the same command as I do) he got 'errno 32 on write'. I just tried to run the command myself and also got 'errno 32 on write'. My cronjob has also started failing even though crontab has not been changed. I am very confused given that this exact command was working up until last week. Can anyone shed light on what is going on? This is the command I use, run as super user: /usr/bin/mysqldump --all-databases -u root | /usr/bin/gzip -c /backups/mysql_backup.gz -- View this message in context: http://www.nabble.com/mysqldump-%28in-crontab%29-failing-with-%27errno-32%27-even-though-it-worked-last-week-tf4944271.html#a14154528 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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] -- View this message in context: http://www.nabble.com/mysqldump-%28in-crontab%29-failing-with-%27errno-32%27-even-though-it-worked-last-week-tf4944271.html#a14171020 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump (in crontab) failing with 'errno 32' even though it worked last week
'errno 32 on write' That's a broken pipe error (Run 'perror 32' in Linux) Maybe too much data is being pumped in through the pipe. Instead of this : /usr/bin/mysqldump --all-databases -u root | /usr/bin/gzip -c /backups/mysql_backup.gz Try it this way : /usr/bin/mysqldump --all-databases -u root AllData.txt /usr/bin/gzip -c /backups/mysql_backup.gz AllData.txt -Original Message- From: FMGreen [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 04, 2007 11:52 AM To: mysql@lists.mysql.com Subject: mysqldump (in crontab) failing with 'errno 32' even though it worked last week I use the mysqldump command (as below) in crontab to do a dump of my database each night. Up until last week this was working fine. Last week (when I was out of the office) there was a problem with the db and a colleague of mine had to do a manual dump. When he tried to run mysqldump (using the same command as I do) he got 'errno 32 on write'. I just tried to run the command myself and also got 'errno 32 on write'. My cronjob has also started failing even though crontab has not been changed. I am very confused given that this exact command was working up until last week. Can anyone shed light on what is going on? This is the command I use, run as super user: /usr/bin/mysqldump --all-databases -u root | /usr/bin/gzip -c /backups/mysql_backup.gz -- View this message in context: http://www.nabble.com/mysqldump-%28in-crontab%29-failing-with-%27errno-32%27-even-though-it-worked-last-week-tf4944271.html#a14154528 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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 (in crontab) failing with 'errno 32' even though it worked last week
Hi, On Dec 4, 2007 2:58 PM, Rolando Edwards [EMAIL PROTECTED] wrote: 'errno 32 on write' That's a broken pipe error (Run 'perror 32' in Linux) Maybe too much data is being pumped in through the pipe. Instead of this : /usr/bin/mysqldump --all-databases -u root | /usr/bin/gzip -c /backups/mysql_backup.gz Try it this way : /usr/bin/mysqldump --all-databases -u root AllData.txt /usr/bin/gzip -c /backups/mysql_backup.gz AllData.txt -Original Message- From: FMGreen [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 04, 2007 11:52 AM To: mysql@lists.mysql.com Subject: mysqldump (in crontab) failing with 'errno 32' even though it worked last week I use the mysqldump command (as below) in crontab to do a dump of my database each night. Up until last week this was working fine. Last week (when I was out of the office) there was a problem with the db and a colleague of mine had to do a manual dump. When he tried to run mysqldump (using the same command as I do) he got 'errno 32 on write'. I just tried to run the command myself and also got 'errno 32 on write'. My cronjob has also started failing even though crontab has not been changed. I am very confused given that this exact command was working up until last week. Can anyone shed light on what is going on? This is the command I use, run as super user: /usr/bin/mysqldump --all-databases -u root | /usr/bin/gzip -c /backups/mysql_backup.gz $ perror 32 OS error code 32: Broken pipe Next question is, why is the pipe broken? My guess is lack of disk space or privileges. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump of huge innodb database
Unfortunately the additional parameters didn't solve my problem. But thanks for your response! ssh [EMAIL PROTECTED] \ mysqldump -u XYZ --verbose --password=XYZ --quick --single-transaction --net_buffer_length=1G --max_allowed_packet=1G dbmail | /bin/gzip \ /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp I don't thinks the problem and also following command didn't work mysqldump -h XYZ -u XYZ --verbose --password=XYZ --quick --single-transaction --net_buffer_length=1G --max_allowed_packet=1G dbmail | gzip /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp Always get the result: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 177912 Script ended at: Sat Sep 22 06:32:16 CEST 2007 (1190435536) Execution Time: Hours: 4 Minutes: 269 Seconds: 16155 OR mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 189738 Script ended at: Sun Sep 23 06:30:30 CEST 2007 (1190521830) Execution Time: Hours: 4 Minutes: 267 Seconds: 16048 OR mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 137554 Script ended at: Mon Sep 24 06:30:01 CEST 2007 (1190608201) Execution Time: Hours: 4 Minutes: 267 Seconds: 16020 I know these two other solutions: - Setting up a replication service - Stopping mysql, copying db-files, and restart mysql Doing replication is not possible cause of the huge size of the database. Hard-core copy of db-files causes a downtime of up to 8 hours so it would be possible. Or does somebody has another (hope better) solution? With best regards, Benjamin Schmidt Hartleigh Burton wrote: Hiya, I was backing up a 95GB InnoDB database and forever had problems. It ended up working and I never really worked out exactly what the cause was... but try using the following: --opt (does --quick + extended-insert + others) --net_buffer_length=1G (set this to whatever you want, 1G is the largest it will support. I was backing up uncompressed audio so had it at 1G. When --opt is set it also uses --extended-insert, the net_buffer_length tells mysqldump when to break the extended insert and create a new insert. Useful when dealing with large packets) --max_allowed_packet=1G (or whatever you expect your largest packet to be, in my case was up to 1G) Example: mysqldump -u mysqldump --password= --opt --verbose --net_buffer_length=1G --max_allowed_packet=1G --single-transaction dbname dbname.sql If this still fails... try running the backup from a remote computer either by using MySQL Administrator or mysqldump. Occasionally I would get the same error you received when running mysqldump on localhost, however it would complete when run from either my workstation or on another server. I can't really explain why this would happen, but now I just run all of my backups straight to a mirrored server. Example: mysqldump -h 192.168.x.x -u mysqldump --password= --opt --verbose --net_buffer_length=1G --max_allowed_packet=1G --single-transaction dbname dbname.sql Good luck, hope this helps. Hartz. -Original Message- From: Benjamin Schmidt [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 September 2007 7:05 PM To: mysql@lists.mysql.com Subject: mysqldump of huge innodb database Hello list members Since a few days I get this error message when making a backup of my database: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 174955 Script ended at: Tue Sep 4 06:45:37 CEST 2007 (111137) Execution Time: Hours: 4 Minutes: 282 Seconds: 16956 The ibdata1 file now has a size of 42GB (I use the innodb engine). The command to backup is following: ssh [EMAIL PROTECTED] \ mysqldump -u mysqldump --password= --quick --single-transaction dbmail | /bin/gzip /Backup/mysqldump.tar.gz And this is my config-file (default values from the debian package): ... innodb_data_file_path=ibdata1:10M:autoextend:max:183G key_buffer = 16MB max_allowed_packet = 64M thread_stack = 128K query_cache_limit = 1048576 query_cache_size = 16777216 query_cache_type = 1 set-variable = max_connections=1000 max_allowed_packet = 64M ... As I wrote above, it worked this way a very long time. And it should work again ;) Does anyone know this problem or has an idea? Many thanks in advance, Benjamin Schmidt smime.p7s Description: S/MIME Cryptographic Signature
Re: mysqldump of huge innodb database
I see one conflict that could be causing your lost connection message - you are specifying a 1 GB max_allowed_packet for the client, but the server is configured to only support 64 MB. You should adjust the max_allowed_packet = 64M setting on the server to match or exceed what you specify on the mysql or mysqldump command line client, then try again. HTH, Dan On 9/24/07, Benjamin Schmidt [EMAIL PROTECTED] wrote: Unfortunately the additional parameters didn't solve my problem. But thanks for your response! ssh [EMAIL PROTECTED] \ mysqldump -u XYZ --verbose --password=XYZ --quick --single-transaction --net_buffer_length=1G --max_allowed_packet=1G dbmail | /bin/gzip \ /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp I don't thinks the problem and also following command didn't work mysqldump -h XYZ -u XYZ --verbose --password=XYZ --quick --single-transaction --net_buffer_length=1G --max_allowed_packet=1G dbmail | gzip /Backup/HD-Save/XYZ/data_mysql/XYZ_mysqldump.tar.gz_tmp Always get the result: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 177912 Script ended at: Sat Sep 22 06:32:16 CEST 2007 (1190435536) Execution Time: Hours: 4 Minutes: 269 Seconds: 16155 OR mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 189738 Script ended at: Sun Sep 23 06:30:30 CEST 2007 (1190521830) Execution Time: Hours: 4 Minutes: 267 Seconds: 16048 OR mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 137554 Script ended at: Mon Sep 24 06:30:01 CEST 2007 (1190608201) Execution Time: Hours: 4 Minutes: 267 Seconds: 16020 I know these two other solutions: - Setting up a replication service - Stopping mysql, copying db-files, and restart mysql Doing replication is not possible cause of the huge size of the database. Hard-core copy of db-files causes a downtime of up to 8 hours so it would be possible. Or does somebody has another (hope better) solution? With best regards, Benjamin Schmidt Hartleigh Burton wrote: Hiya, I was backing up a 95GB InnoDB database and forever had problems. It ended up working and I never really worked out exactly what the cause was... but try using the following: --opt (does --quick + extended-insert + others) --net_buffer_length=1G (set this to whatever you want, 1G is the largest it will support. I was backing up uncompressed audio so had it at 1G. When --opt is set it also uses --extended-insert, the net_buffer_length tells mysqldump when to break the extended insert and create a new insert. Useful when dealing with large packets) --max_allowed_packet=1G (or whatever you expect your largest packet to be, in my case was up to 1G) Example: mysqldump -u mysqldump --password= --opt --verbose --net_buffer_length=1G --max_allowed_packet=1G --single-transaction dbname dbname.sql If this still fails... try running the backup from a remote computer either by using MySQL Administrator or mysqldump. Occasionally I would get the same error you received when running mysqldump on localhost, however it would complete when run from either my workstation or on another server. I can't really explain why this would happen, but now I just run all of my backups straight to a mirrored server. Example: mysqldump -h 192.168.x.x -u mysqldump --password= --opt --verbose --net_buffer_length=1G --max_allowed_packet=1G --single-transaction dbname dbname.sql Good luck, hope this helps. Hartz. -Original Message- From: Benjamin Schmidt [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 September 2007 7:05 PM To: mysql@lists.mysql.com Subject: mysqldump of huge innodb database Hello list members Since a few days I get this error message when making a backup of my database: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dbmail_messageblks` at row: 174955 Script ended at: Tue Sep 4 06:45:37 CEST 2007 (111137) Execution Time: Hours: 4 Minutes: 282 Seconds: 16956 The ibdata1 file now has a size of 42GB (I use the innodb engine). The command to backup is following: ssh [EMAIL PROTECTED] \ mysqldump -u mysqldump --password= --quick --single-transaction dbmail | /bin/gzip /Backup/mysqldump.tar.gz And this is my config-file (default values from the debian package): ...
Re: mysqldump with single rows per dataset
Hello, thanks. I just wonder why this isn't actually documented in the --help output of mysqldump. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
