MariaDB export to a mariaDB Docker

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

Select allOpen in new window

Then just create a tarball of the path your-site-docroot to use with Docker.

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.

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!

"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!

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

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

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.

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 ?

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.

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?

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 :


when I do this inside MariaDB via UI :

SET lower_case_table_names=2;

Select allOpen in new window

error returns by saying:

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

Select allOpen in new window

what is the problem? MariaDB seems can't see the setting in mysqlid section.

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;

Select allOpen in new window

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

Regards,
    Tomas Helgi

"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 ?

also can you help on :

https://www.experts-exchange.com/questions/29151948/Start-MariaDB-using-wsrep-recover-option.html?headerLink=workspace_open_questions

 and

https://www.experts-exchange.com/questions/29151628/MariaDB-can't-start-up-and-probably-cluster-break-down.html?headerLink=workspace_open_questions

tks.

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.

Hi,

The database is initialized every time you start the database.

Regards,
    Tomas Helgi

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.

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.

tks all