Import database

[Julime]

I plan to use this as a local development setup. Therefore i need to import the production database. I couldn't find any hint how to do so at startup in this repo.

Should i do this via bin/cli?

[markshust]

Yep - just added this to the documentation so it's more apparent:
https://github.com/markshust/docker-magento#database

[Julime]

This seems to fail for me:

$ bin/cli ls -la | grep sql
1084 -rw-r--r--  1 app app 1106550 Feb 26 08:01 magento.sql

$ bin/cli mysql -h db -umagento -pmagento magento < magento.sql
-bash: magento.sql: No such file or directory

I've tried another way, got another error:

$ bin/bash

$ mysql -h db -umagento -pmagento magento < magento.sql
ERROR 1227 (42000) at line 12508: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[markshust]

put the magento.sql file on your local host machine --- after the < it looks at your local machine, not the docker container for the file

…

________________________________ From: Julian <notifications@github.com> Sent: Wednesday, February 27, 2019 3:12 AM To: markshust/docker-magento Cc: Mark Shust; State change Subject: Re: [markshust/docker-magento] Import database (#133) This seems to fail for me: $ bin/cli ls -la | grep sql 1084 -rw-r--r-- 1 app app 1106550 Feb 26 08:01 magento.sql $ bin/cli mysql -h db -umagento -pmagento magento < magento.sql -bash: magento.sql: No such file or directory I've tried another way, got another error: $ bin/bash $ mysql -h db -umagento -pmagento magento < magento.sql ERROR 1227 (42000) at line 12508: Access denied; you need (at least one of) the SUPER privilege(s) for this operation — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub<#133 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAarJWZnH6RCdBf33up4pQFtBjoA9Jidks5vRj3fgaJpZM4bRgc5>.

[Julime]

Sorry for bothering you:

$ bin/cli mysql -h db -umagento -pmagento magento < magento.sql
the input device is not a TTY

[markshust]

if you don't want a tty (automated script or otherwise), you can use bin/clinotty

…

________________________________ From: Julian <notifications@github.com> Sent: Friday, March 1, 2019 1:07 AM To: markshust/docker-magento Cc: Mark Shust; State change Subject: Re: [markshust/docker-magento] Import database (#133) Sorry for bothering you: $ bin/cli mysql -h db -umagento -pmagento magento < magento.sql the input device is not a TTY — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub<#133 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAarJbFfBIA2W13P6mDbqKCYNlM77_X2ks5vSNGVgaJpZM4bRgc5>.

[carlos-reynosa]

@markshust ,

Yep - just added this to the documentation so it's more apparent:
https://github.com/markshust/docker-magento#database

I noticed that on the section you posted you're using root:

bin/clinotty mysql -h db -u root -pmagento magento < dbdump.sql

But in the existing projects section you're using the magento user:

https://github.com/markshust/docker-magento#existing-projects

bin/clinotty mysql -hdb -umagento -pmagento magento < existing/magento.sql

Following the existing projects section commands I kept getting the following permission error when trying to import a database:

ERROR 1227 (42000) at line 2270: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

In order to bypass the error I had to change the user to root. I thought this was kind of confusing. You might want to change the user name to root in the existing projects section of the initial readme in order to reduce confusion if you're just going through the steps.

[verdde]

This ticket should be reopened.

me@192-168-0-100 ~/P/text.in (master) [1]> bin/clinotty mysql -hdb -umagento -pmagento magento < test.sql
ERROR 1227 (42000) at line 36341: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[rangerz]

@Julime @carlos-reynosa @verdde @markshust
For importing database issue for Access denied; you need (at least one of) the SUPER privilege(s) for this operation, it's because sqldump file exists DEFINER=xxx@yyyy.com clause that needs SUPER privilege.

Solution is remove the DEFINER clause from sql dump file or using n98-magerun2.phar db:dump to export your database.

# Solution 1:
mysqldump -h localhost -u user -pPassword database > dbdump.sql
cat dbdump.sql | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dbdump_fixed.sql

# Solution 2:
n98-magerun2.phar db:dump dbdump_fixed.sql

And then you can use magento user to import database.

bin/clinotty mysql -hdb -umagento -pmagento magento < dbdump_fixed.sql

Ref:
https://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps

[ikslavok]

I can confirm that this works for the latest update:

bin/CLI mysqldump -hdb -uroot -pmagento magento > backup/magento.sql

Everything else is making me wanna dump my mind :)

[blackizlu]

@mrkovalski solution works fine for the latest update!