MariaDB export to a mariaDB Docker

marrowyung
marrowyung used Ask the Experts™
on
hi,

what is the full command to do SQL dump from MariaDB v 10.4.x to a SQL files which include all index, schema, trigger, function. ?

I want to import it to a MariaDb on a docker.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
What I use...

cd your-site-docroot
nice -19 mysqldump --defaults-file=/etc/mysql/debian.cnf --force --opt --single-transaction --default-character-set=utf8 --routines --triggers --events $dbname > $dbname.sql

Open in new window


Then just create a tarball of the path your-site-docroot to use with Docker.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Be aware, if this is your first time using Docker, then you'll find Docker doesn't work as you might expect, as Docker has no concept of persistent data, so all data dies across container bounces (stop/restart), unless you setup a bunch of directories for your Docker instance to own + access.

1) You must arrange for all your config files (for Apache, PHP, MariaDB) to live in some directory only your Docker instance will use.

2) Same for your /var/lib/mysql raw files.

3) Same for all your log files to allow debugging... including any CMS files, like WordPress debug files.

4) First time your run your Docker container, you must do an init phase to setup your database, pointing to #1-#3 data volumes (directories).

5) Subsequent Docker container runs must then skip the init phase, to do a standard service startup.

6) You must ensure no other code ever touches an of the #1-#3 data, else all your data will be destroyed.

Tip: When persistent data is required, LXD will likely be a better choice, as LXD allows a machine type runtime environment, so in essence you get a container that works just like a normal physical machine... so all config files, data files, log files are private to the container + persist across reboots.

With Docker you must arrange all this + if you make a mistake, your data is corrupt.

With LXD, you simply install packages + run your code exactly like you would using a physical machine.
marrowyungSenior Technical architecture (Data)

Author

Commented:
now a stranght problem is , once imported, one of the table named 'CASE', when quering it like, select * from CASE; it say can't find CASE table.

but when we do select * from `CASE`; , then it works ! any reason why and how to fix it?

we even need to add schema in front of the CASE to get ride of it, can't see why!
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

David FavorFractional CTO
Distinguished Expert 2018
Commented:
Keep this in mind.

1) When you start a Docker container, there is no data. This means you must reload your database each start.

2) When you stop a Docker container, all data is lost.

3) To fix this requires you arrange for a docker volume containing your /var/lib/mysql/* files + LAMP Stack config files.

4) You must also arrange for some way to init your /var/log/mysql/* files once, then on subsequent runs skip the init phase.

This means your missing table likely means you're missing all tables, because your database wasn't properly managed outside the Docker container as a Docker data volume.

LXD is far easier to use, to me, where a container requires persistent data.

Docker can be used for microservices, which have no persistent data.

If you try using Docker for persistent data, then you have to develop your own handrolled LXD-ish data system, which is complex + error prone.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"This means your missing table likely means you're missing all tables, because your database wasn't properly managed outside the Docker container as a Docker data volume"

just that table only, it has data inside !

just when selecting, it is strange! not looking good!
Prabhin MPDevOps Engineer
Distinguished Expert 2018
Commented:
do mysql dump for all the databases. and import to the docker container MySQL
Prabhin MPDevOps Engineer
Distinguished Expert 2018

Commented:
or you can take backup of /var/lib/MySQL and mount to docker container
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

I think this one can't create a logical backup that can  solve all dependency, right?

mysqldump -u root -h <target MariDB IP> -p <Database> > <files name>.sql

it seems it is not !

"or you can take backup of /var/lib/MySQL and mount to docker container"

copy all files over there? just a copy ? and how to mount ?

please help on https://www.experts-exchange.com/questions/29151628/MariaDB-can't-start-up-and-probably-cluster-break-down.html?headerLink=workspace_open_questions
Database Administrator / Software Engineer
Commented:
Hi,

Check your exported file (dbname.sql as in David's example) and if the file contains "CREATE  DATABASE dbname" or similar command then
you can simply import the file using
mysql -u root -p < dbname.sql 

Open in new window

in Docker otherwise it should be
mysql -u root -p dbname < dbname.sql

Open in new window


Also to skip the quoted text in tablenames you should use the mysqldump option --skip-quote-names when you create the dump file.

Regards,
    Tomas Helgi
David FavorFractional CTO
Distinguished Expert 2018

Commented:
You must work through this step by step with Docker, as Docker != LXD + Docker != machine.

With Docker you must run your containers in 2x modes, as mentioned above.

1) Init mode is when databases are built + loaded.

2) Runtime mode is when databases are accessed.

This tends to be very complex in Docker. You just have to work through how best to do this in your situation.

Most people have 2x Docker configs, one for each mode.

You'll may also have a different tool set (Docker volumes) available in each mode.

You asked, "copy all files over there? just a copy ? and how to mount ?"

Answers all depend on your specific setup.

Tip: For LAMP Stacks with databases, LXD is the correct tool. For microservices with no data, Docker can be used.
marrowyungSenior Technical architecture (Data)

Author

Commented:
right now once imported to the docker we found a problem, if the table name is using one of the reserved keyword, when we selected from that table imported.

we need to add ' before and after the table name in order to select from it,

e.g, select * from 'CASE';  

we can't do select * from CASE;

anyway to not using that  character ?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Tip: Don't use reserved keywords as table names.

If you do use reserved keywords, expect this may fail at some point in the future.

And you will have to enclose your table names in quotes if you used reserved keywords.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

so nothing can be done to avoid this ?

and MariaDB can allow lower case and upper case characters as diff table name, anyway to force MariaDB to use only upper case as the table name ? so any other same name with diff lower and upper case still see as the same table so that duplicated table is not allowed?
marrowyungSenior Technical architecture (Data)

Author

Commented:
I found this:

https://stackoverflow.com/questions/44648343/mysql-upper-case-table-schema-name

and i try that setting in server.cnf for mariaDB and it doesn't work, lower_case_table_names is STILL 0 if I double check from UI when MariaDB is up !:

SHOW VARIABLES LIKE 'lower_case_table_names';

this is the content of server.cnf :

MariaDB upper case table
when I do this inside MariaDB via UI :

SET lower_case_table_names=2;

Open in new window

error returns by saying:

Lookup Error - MySQL Database Error: Variable 'lower_case_table_names' is a read only variable

Open in new window


what is the problem? MariaDB seems can't see the setting in mysqlid section.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

Note that on both MySQL and MariaDB then
"lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited. "

See here and here for further info.

Running the command
SET lower_case_table_names=2;

Open in new window

after the database startup and initialization is prohibited and thus cannot be done.

Regards,
    Tomas Helgi
marrowyungSenior Technical architecture (Data)

Author

Commented:
"after the database startup and initialization is prohibited and thus cannot be done. "

once install the MariaDB it will be start anyway, so it can't be done anyway ?

whta do you mean initialization ?  what is the commnad you mena ? service start is startup but not initializatoin ?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
This question seems to have morphed into a completely different question.

The original question related how to import a database in Docker.

The answer is, the same way you always import databases.

And with Docker, you have to arrange this using a Docker volume holding /etc/mysql + /var/lib/mysql along with initialization of the Docker volume.

Best to open a new question related to lower_case_table_names settings.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

The database is initialized every time you start the database.

Regards,
    Tomas Helgi
David FavorFractional CTO
Distinguished Expert 2018

Commented:
About, "The database is initialized every time you start the database."

Docker works differently... in 2x phases/stages...

1) Init the database + load data.

2) Normal runtime mode where data persists between container stop/restart (which is the equivalent of a container reboot).

Both these phases/stages are very different + can't be mixed.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Thomas,

"The database is initialized every time you start the database.
"

I run systemctl start mariadb, so this is it?

David.

"Docker works differently... in 2x phases/stages..."

any detail doc on how it works ?

I am now referring to MariaDB docker.

I open this ticket :

https://www.experts-exchange.com/questions/29152502/lower-case-table-names-variable-for-MariaDB-how-to-enable-it.html#questionAdd

also please help me on the post on how to boottrap my failure MariaDB cluster.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial