Re: mysqldump with single-transaction option.

[Andrew Moore]

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

[yoku ts.]

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 :

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

[geetanjali mehra]

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

[yoku ts.]

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 :

> 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 
> 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
> >> *). 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
> >>  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.

[geetanjali mehra]

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 
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
>> *). 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
>>  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.

[shawn l.green]

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
*). 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
 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.

[Rick James]

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

[Inigo Medina]

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

[Ananda Kumar]

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

[Rick James]

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

[Ananda Kumar]

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

> 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: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES

[Shafi AHMED]

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

[Suresh Kuna]

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

[Prabhat Kumar]

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  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: Got error: 1017: Can't find file: './ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES

[Dan Nelson]

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

[Johan De Meersman]

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" 
> 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 --ignore-table

[Claudio Nanni]

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 

> 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

[jayabharath]

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

> 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 except one table

[a . smith]

Check the manual? Its here under ignore-table

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Quoting Adarsh Sharma :


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 has a lot of variation for time to complete

[Dan Nelson]

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

[Tanmay Pradhan]

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

[Tanmay Pradhan]

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: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES

[Shawn Green (MySQL)]

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 Pradhan  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'@'' (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

[Michael Dykman]

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

[Krishna Chandra Prajapati]

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  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'@' 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 error 1064 for database Use command

[Nitin Mehta]

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 
To: win.a 
Cc: "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
>  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
> >  >  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

[Manasi Save]

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

[Manasi Save]

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

[Jangita]

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

[Manasi Save]

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
To: "mysql@lists.mysql.com"
Sent: 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.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 error 1064 for database Use command

[Nitin Mehta]

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 
To: "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

[Jerry Schwartz]

>-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  wrote:
>
>> [1  ]
>> 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 backup

[Raj Shekhar]

In infinite wisdom Angelina Paul  wrote:

> [1  ]
> 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

[Angelina Paul]

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

[Anand.S]

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  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 "hex-blob" option

[Barry Leslie]

On 3/4/10 7:21 PM, "peng yao"  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]

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

> 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 vs phpmyadmin dump

[Mark Goodge]

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 Unknown command '\''.

[Michael Dykman]

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

[Mikhail Berman]

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 warning or actual error?

[Michael Dykman]

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 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
>
> --
> 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 and access rights

[walter harms]

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 
> 
>>
>> muhammad subair schrieb:
>>> On Thu, Jul 23, 2009 at 10:10 PM, walter harms  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

[peng yao]

you also can do this:#sudo -u mysql "mysqldump command"
or
#su - mysql -c "mysqldump command"



2009/7/24 walter harms 

>
>
> muhammad subair schrieb:
> > On Thu, Jul 23, 2009 at 10:10 PM, walter harms  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

[walter harms]

muhammad subair schrieb:
> On Thu, Jul 23, 2009 at 10:10 PM, walter harms  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 and access rights

[peng yao]

# chown mysql.mysql  datafile

2009/7/23 walter harms 

> 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

[peng yao]

#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 

> we need more information,
> such as your client and server version, the command that exactly your input
> etc.
>
> 2009/7/14 JingTian 
>
> > 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 output

[zhu dingze]

we need more information,
such as your client and server version, the command that exactly your input
etc.

2009/7/14 JingTian 

> 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

[Ian Simpson]

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

[Jim Lyons]

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 -e"show 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 

> try something like:
>
> mysqldump -u(user) -p (db-name) `ls z*` > (filename)
>
>
> 2009/4/19 ChoiSaehoon 
>
>
>> 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 syntax - dumping only specific tables starting with a certain character. (e.g. z*)

[Uwe Kiewel]

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

[ChoiSaehoon]

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

[Uwe Kiewel]

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

[ChoiSaehoon]

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

[Jim Lyons]

try something like:

mysqldump -u(user) -p (db-name) `ls z*` > (filename)


2009/4/19 ChoiSaehoon 

>
> 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: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt'

[Ray Anderson]

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

[ewen fortune]

Hi,

On Thu, Apr 2, 2009 at 1:18 PM, Virgilio Quilario
 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

Re: mysqldump failing to load data

[Virgilio Quilario]

> 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:
> |  | >mysql_select_db
> | | enter: db: 'spl2'
> | | >net_clear
> | | | >vio_blocking
> | | | | enter: set_blocking_mode: 0  old_mode: 1
> | | | | exit: 0
> | | |  | | | >vio_read
> | | | | enter: sd=4, buf=0xb7c97008, size=1047551
> | | | | vio_error: Got error 11 during read
> | | | | exit: -1
> | | |  | | | >vio_blocking
> | | | | enter: set_blocking_mode: 1  old_mode: 0
> | | | | exit: 0
> | | |  | |  
>
> 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: < ? Solved ? > Re: mysqldump: Error 2013: Lost connection to MySQL server

[Andrew Garner]

On Tue, Jan 13, 2009 at 6:06 PM, Dan  wrote:
> On Tue, 13 Jan 2009 18:34:44 -0600, Andrew Garner
>  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: < ? Solved ? > Re: mysqldump: Error 2013: Lost connection to MySQL server

[Dan]

On Tue, 13 Jan 2009 18:34:44 -0600, Andrew Garner
 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

[Andrew Garner]

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  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 2>>bkp.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 2>>bkp.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

< ? Solved ? > Re: mysqldump: Error 2 013: Lost connection to MySQL server

[Dan]

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 2>>bkp.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 2>>bkp.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: mysqldump: Error 2013: Lost connection to MySQL server

[Aaron Blew]

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: Error 2013: Lost connection to MySQL server

[Dan]

On Mon, 12 Jan 2009 16:25:12 +0530, Chandru  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 2>>bkp.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

[Chandru]

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 2>>bkp.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 wrote:

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

[Amit Sharma]

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

> 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

[Moon's Father]

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

[Moon's Father]

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

[Moon's Father]

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

[Krishna Chandra Prajapati]

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

[Nanu Kalmanovitz]

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 questions

[chaim . rieger]

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

[Mary Bahrami]

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

[Martin Gainty]

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

[John Nietzsche]

[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

[Dan Nelson]

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 turn off quotes

[Dan Nelson]

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 [SOLVED]

[Moon's Father]

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 giving errors [SOLVED]

[cr.vegelin]

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

[Moon's Father]

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

[Moon's Father]

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

[Dan Lipsitt]

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

[Moon's Father]

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

[Ananda Kumar]

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

[Norbert Tretkowski]

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

[Daniel Brown]

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

-- 

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 on specific columns only?

[Rob Wultsch]

>  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 on specific columns only?

[Waynn Lue]

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?

[Tim McDaniel]

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?

[Rob Wultsch]

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: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

[Jed Reynolds]

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: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

[Mike Spreitzer]

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

[Jed Reynolds]

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]

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

[Jed Reynolds]

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 (in crontab) failing with 'errno 32' even though it worked last week

[FMGreen]

"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

[Baron Schwartz]

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 (in crontab) failing with 'errno 32' even though it worked last week

[Rolando Edwards]

'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 of huge innodb database

[Dan Buettner]

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 -

Re: mysqldump of huge innodb database

[Benjamin Schmidt]

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

[Hartleigh Burton]

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



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.485 / Virus Database: 269.13.3/986 - Release Date: 3/09/2007 9:31 
AM
 

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.485 / Virus Database: 269.13.3/986 - Release Date: 3/09/2007 9:31 
AM
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]