RE: Import of a mysldump file fails

[Eramo, Mark]

Hi Kieran,
   Try the following 2 things...

1) Add this to your my.cnf / my.ini in the [mysqld] section

max_allowed_packet=32M

(you might have to set this value higher based on your existing database).


2) If the import still does not work, try it like this as well.

mysql -u user --password=password database name  file_to_import

These should help you get by this issue. I use both of these to import a MySQL 
4.0.20 dump into MySQL 5.0

Mark


-Original Message-
From: Kieran Kelleher [mailto:[EMAIL PROTECTED]
Sent: Monday, May 05, 2008 3:21 PM
To: Mysql
Subject: Import of a mysldump file fails

Hi,

I have a weird one going on here. I have done this many times before
with MySQL 4.1, but now that I am trying to accomplish same task with
a MySQL 5.0.x setup, mysql is just not succeeding in importing the
mysqldump file.

I am doing a full mysql dump from a mysql master running 5.0.51a on a
OS X 10.4.x Server PowerPC G4 XServe. The total size of all databases
is around 17GB. I compress on the fly with gzip to create a 2.4GB
compressed dump using this command the largest database is all
innodb and the 2nd largest is myisam... so this is mixed innodb and
myisam server.

$ mysqldump -u root -ppassword --all-databases --extended-insert --
add-locks --lock-all-tables --flush-logs --master-data=1 --add-drop-
table --create-options --quick --set-charset --disable-keys --quote-
names | gzip  slavesetup.sql.gz 

I am then copying this compressed dump to a MacMini Intel (aka slave)
which has a clean install of 5.0.51b and importing into the mysql
server. The import ran for a while and I then notice at some stage (by
looking at top or show processlist that importing has completed)
However checking the databases, only some of them have been imported
(about 6GB of data) and the import has mysteriously stopped in the
middle of importing the largest database. The import command is
straightforward...

$ gunzip  slavesetup.sql.gz | mysql -u root -h localhost -ppassword 

The *.err files inside the data dir have nothing to indicate what the
problem is.

The error I get is in terminal is:
ERROR 2013 (HY000) at line 570: Lost connection to MySQL server during
query

Any clues as to what might be going on here and why this might be
happening? Any alternative or workaround suggestions?

Regards, Kieran

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

RE: Starting a 2nd MySQL instance on UNIX

[Eramo, Mark]

Hi Arthur,
   Sure, no problem!

This actually proved to be rather tricky because the first instance was 
installed in /usr/local/mysql. It seems that by default, MySQL looks for things 
in this locations so if you deviate from it, you have to be explicit with mysql 
and tell it where to look for things.  My new MySQL 50 instance had to run 
alongside the 4.0.20 instance so I installed it into /usr/local/mysql-50

** Here is a summary of things I had to do to bring this up properly **

I created a my.cnf and put it in /usr/local/mysql-50

In the my.cnf, I defined the following (The first entry is for the mysql 
program and the 2nd entry is for the mysqld program).

[mysql]
socket=/tmp/mysql50/mysql.sock
port=3307

[mysqld]
user=mysql5
pid-file=/usr/local/mysql-50/mysql50.pid
log=/usr/local/mysql-50/mysql50d.log
port=3307
max_allowed_packet=32M   == I had to set this so I could import the MySQL 
4.0.20 database properly. Some users may not need this set as high.
socket=/tmp/mysql50/mysql.sock

#Path to installation directory. All paths are usually resolved relative to 
this.
basedir=/usr/local/mysql-50

#Path to the database root
datadir=/usr/local/mysql-50/data

You have to be careful to not only pick a separate port, but also a separate 
pid file for the process ID and a separate socket file, especially if you are 
running both instance at the same time.

I modified the mysql.server in the /support-files folder. I had to set the 
basedir and the datadir. I then copied this to the /bin folder.

I edited the mysql_install_db file in the /scripts folder and set the basedir 
and datadir. I then ran that to create the mysql database.

As root, I started the server  ./bin/mysql.server start (The server starts up 
:) ). Since the user mysql50 is defined in this script, it then starts mysqld 
as the mysql50 user.

Now, the tricky part, to run mysql and access the mysql database, you cannot 
just say mysql -u root -p mysql, it will try to connect to the default instance 
in /usr/local/mysql.
What you have to do is:

Mysql --defaults-file=/usr/local/mysql-50/my.cnf -u root -p mysql

This now properly connects to the new mysql50 instance. Also, if you want to 
run mysqladmin, you need to specify the --defaults-file option.  Make sure 
wherever you use the --defaults-file option that it is the FIRST command line 
option used.

It took me quite some time to get this all working but now, I understand MySQL 
much better. I hope this proves to be some help to you and others out there who 
may be going through the same thing.

Regards,
Mark




From: Arthur Fuller [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 29, 2008 9:29 AM
To: Eramo, Mark
Cc: mysql@lists.mysql.com
Subject: Re: Starting a 2nd MySQL instance on UNIX

Would you kindly supply the changes you made, for our collective education? 
Thanks.

Arthur
On Mon, Apr 28, 2008 at 11:54 AM, Mark-E [EMAIL PROTECTED]mailto:[EMAIL 
PROTECTED] wrote:

Hi Ian,
  Thanks for the reply. I was specifying the new port of 3307. I actually
got it working over the weekend. Turns out I had to add a few entries in the
mysqld section of the my.cnf file and I was able to connect.

Regards,
 Mark