Web lists-archives.com

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

Mark Fletcher kirjoitti 11.11.2017 klo 08:44:
> 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)

I did expect that to work.

I have now examined more. The command returns exit code 2, which
indicates Not Found. But the error text does not show in Standard Error.
When I changed the password I do get

mysqldump: Got error: 1045: "Access denied for user ...

It does seem to me there is some bug here. If I fail to check the exit
code there is no indication someting is wrong.

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

There are other ways to transfer the database from host to host. If the
above command would work, that is the easiest because I have a script
that generates the dump commands. Now I have to change the script to do
this some other way.

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

Tapio Lehtonen
OSK Satatuuli http://satatuuli.fi/
fn:Tapio  Lehtonen
org:Osk Satatuuli
adr;quoted-printable:;;Telj=C3=A4nkatu 8 C;Pori;;28130;Suomi
tel;work:02-641 41 74
tel;cell:040 590 13 24