marrowyung
asked on
MariaDB export to a mariaDB Docker
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.
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.
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.
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.
ASKER
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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!
just that table only, it has data inside !
just when selecting, it is strange! not looking good!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or you can take backup of /var/lib/MySQL and mount to docker container
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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 ?
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.
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.
ASKER
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?
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?
ASKER
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 :
what is the problem? MariaDB seems can't see the setting in mysqlid section.
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;
error returns by saying:Lookup Error - MySQL Database Error: Variable 'lower_case_table_names' is a read only variable
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
Regards,
Tomas Helgi
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;
after the database startup and initialization is prohibited and thus cannot be done. Regards,
Tomas Helgi
ASKER
"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 ?
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 ?
ASKER
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.
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.
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
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.
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.
ASKER
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.
"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.
ASKER
tks all
Open in new window
Then just create a tarball of the path your-site-docroot to use with Docker.