Web lists-archives.com

Re: mysqldump from mysql 5.1.73 to mariadb 10.1.26 imports no data




On Fri, Nov 10, 2017 at 05:20:15PM +0200, Tapio Lehtonen wrote:
> I am importing databases from old host to new. Checking stuff I noticed all
> databases were created but they had no tables and no records.
> 
> > Examining this I tried to run the mysqldump that tries to copy the
> > database from SOURCE to TARGET. Seems it does not dump the contents of
> > the database, just some SET lines. Example below with usernames and
> > passwords edited out. This command was run on the TARGET. I did not see
> > error messages in logs.
> > 
> > root@ispc6:~# mysqldump -cCQ --triggers --routines --quote-names
> > --hex-blob -h <ip-number -u <username> -p'password' alfamat_db
> > -- MySQL dump 10.16  Distrib 10.1.26-MariaDB, for debian-linux-gnu
> > (x86_64)
> > -- 

I'm not sure I'd expect that much newer a mysqldump client to work on 
that much older a server. And the mysql - mariadb divide won't be 
helping either (although it also may not be hindering much)

Isn't it an option to use the old version's mysqldump command to dump to 
a compressed file, by doing on the server something like:

mysqldump <appropriate options> | bzip2 > dump.sql.bz2

Then honk that compressed dump over the network to the new server, and 
do:

bzcat dump.sql.bz2 | mysql <appropriate options>

to use the new server's mysql command to load the data into the new 
server?

[I recommend bzip2 for this job over xz as it's been my experience that 
with mysqldump output bzip2 achieves better compression and doesn't take 
noticably longer]

If storage space on the old server is a problem, an option would be 
creative use of mounts to get around that.

HTH

Mark