Re: mysqldump from mysql 5.1.73 to mariadb 10.1.26 imports no data
- Date: Sat, 11 Nov 2017 11:23:37 +0200
- From: Tapio Lehtonen <tapio.lehtonen@xxxxxxxxxxxx>
- Subject: 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
> 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
> bzcat dump.sql.bz2 | mysql <appropriate options>
> to use the new server's mysql command to load the data into the new
> [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.
OSK Satatuuli http://satatuuli.fi/
adr;quoted-printable:;;Telj=C3=A4nkatu 8 C;Pori;;28130;Suomi
tel;work:02-641 41 74
tel;cell:040 590 13 24