How do I transfer a mariadb database to another server?

Florian35 · 2022-11-15 10:13:38

I currently have a glpi 9.5.2 server in debian 10.

I tried to upgrade the server to debian 11, I succeeded but then upgrading glpi to version 10. It doesn't work.

So I created a new Debian 11 server, with GLPI 10.0.3 (To get FusionInventory).

But now I want to transfer all my database from my old server to my new one.

Because all the tickets, groups, computers, end I would like to recover all to avoid starting from scratch.

With you an idea thank you

feuleudcrider · 2022-11-15 12:09:51

Hello,

First, you need to do a Dump of all your database if you have multiples on the Debian 10 server:

mysqldump -u username -p password databse > database.sql

Then use SCP to transfer your .sql file to the Debian 11:

scp [option] [user_name@source_host:path/to/source/file] [user_name@target_host:target/path]

And Finaly, upload the new database in Debian 11 like so:

mysql -u root -p -h localhost glpi <database.sql

It Would be done.

Don't Forget to save the Configuration and Files folders in GLPI and to copy them on the new Server to keep all tickets users and entities.

Last edited by feuleudcrider (2022-11-15 12:12:13)

Florian35 · 2022-11-15 12:18:49

My database is called "glpidb" and my username is "glpiuser".

So I have to put : mysqldump -u glpiuser -p "MY PASSWORD" glpidb > database.sql ??

feuleudcrider · 2022-11-15 12:41:49

Sure, and you can change the name of the created file "database".sql if you want it would work as well.

Florian35 · 2022-11-15 14:08:12

I have a mistake:

mysqldump : Got error: 1044: " Access denied for user 'glpiuser'@'localhost' to database "password" when selecting the database

Do you know the error?

Yann1ck · 2022-11-17 15:50:28

Hello,

Use your root mariadb account for dump the glpi database.

like this if your root mariadb is named by default "root":

mysqldump -u root -p password database > database.sql

Last edited by Yann1ck (2022-11-17 15:57:16)

LaDenrée · 2022-11-17 16:21:46

you can find user & PW in .../glpi/config/config_db.php file

mysqldump -u glpiuser -p glpidb > database.sql (no password) then server will prompt for glpiuser password

Florian35 · 2022-11-17 16:45:56

I succeeded with the command "mysqldump -u root -p glpidb > database.sql"
And then I entered my root password.
Then it crashed for 2 minutes and came back so I guess my database registration worked.

But then where did my database go? Searching on WinSCP I find a database file in the file "/var/lib/systemd/catalog" is that right?

Florian35 · 2022-11-17 16:48:51

I don't understand what to do exactly? Because I can retrieve the database with WinSCP otherwise and copy and paste it on my new server but how to do that?

LaDenrée · 2022-11-17 16:49:14

type

cd /tmp
mysqldump -u root -p glpidb > database.sql

dump can take several minutes (depending on the size)

then your dump will be in /tmp

Florian35 · 2022-11-17 16:56:28

And how do you export it afterwards?

LaDenrée · 2022-11-17 16:58:18

Florian35 wrote:

I don't understand what to do exactly? Because I can retrieve the database with WinSCP otherwise and copy and paste it on my new server but how to do that?

use win scp to copy database.sql to your newserver /tmp

then using mysql, create a new EMPTY database (ie glpidb)

then cd /tmp (assuming you have copied you dump in this folder)

mysql glpidb<database.sql

(over 500 tables can take a while ! be patient)

Florian35 · 2022-11-17 17:03:57

I have the error message the : ERROR 1005 (HY000) at line 12580: Can't create table `glpidb`.`glpi_plugin_mydashboard_userwidgets` (errno: 150 "Foreign key constraint is incorrectly formed")

Florian35 · 2022-11-17 17:09:46

Then when I try to go to my GLPI interface, it tells me "Your database version is not compatible with the version of the installed files. An update is required."

So I click on "Update" and the error it tells me "The requested URL cannot be found on this server."

Plugin accounting problem?
Or because I put plugins on my new server?

Florian35 · 2022-11-17 17:27:28

Removing all plugins except FusionInventory and starting a new database export
I have a new problem in the line : "ERROR 1005 (HY000) at line 11796: Can't create table `glpidb`.`glpi_plugin_mydashboard_userwidgets` (errno: 150 "Foreign key constraint is incorrectly formed")"

With NotePad I look at
This is the line "CREATE TABLE `glpi_plugin_mydashboard_userwidgets` ("

CREATE TABLE `glpi_plugin_mydashboard_userwidgets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`users_id` int(11) NOT NULL COMMENT 'RELATION to glpi_users(id)',
`profiles_id` int(11) NOT NULL DEFAULT 0,
`widgets_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `users_id` (`users_id`),
CONSTRAINT `glpi_plugin_mydashboard_userwidgets_ibfk_1` FOREIGN KEY (`users_id`) REFERENCES `glpi_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

Florian35 · 2022-11-17 18:05:56

So I succeeded.

From my old GLPI server

server, I did

mysqldump -u root -p glpidb > database.sql

Then I created a new debian 11 server with the GLPI version, so I created the database etc.
Then when I go on the web with my address
I go to the /tmp folder of my old server, I retrieve the database.sql file (which is placed in "/tmp/database.sql" which I copy to my new server in the same place.

Then I copy the configuration in "/var/www/html/config/" I copy everything in it and put it in the new server.

Then I start the configuration on the web and indicate that I am updating and it will update and put your configuration back.

Forum GLPI-Project

Announcement

#1 2022-11-15 10:13:38

How do I transfer a mariadb database to another server?

#2 2022-11-15 12:09:51

Re: How do I transfer a mariadb database to another server?

#3 2022-11-15 12:18:49

Re: How do I transfer a mariadb database to another server?

#4 2022-11-15 12:41:49

Re: How do I transfer a mariadb database to another server?

#5 2022-11-15 14:08:12

Re: How do I transfer a mariadb database to another server?

#6 2022-11-17 15:50:28

Re: How do I transfer a mariadb database to another server?

#7 2022-11-17 16:21:46

Re: How do I transfer a mariadb database to another server?

#8 2022-11-17 16:45:56

Re: How do I transfer a mariadb database to another server?

#9 2022-11-17 16:48:51

Re: How do I transfer a mariadb database to another server?

#10 2022-11-17 16:49:14

Re: How do I transfer a mariadb database to another server?

#11 2022-11-17 16:56:28

Re: How do I transfer a mariadb database to another server?

#12 2022-11-17 16:58:18

Re: How do I transfer a mariadb database to another server?

#13 2022-11-17 17:03:57

Re: How do I transfer a mariadb database to another server?

#14 2022-11-17 17:09:46

Re: How do I transfer a mariadb database to another server?

#15 2022-11-17 17:27:28

Re: How do I transfer a mariadb database to another server?

#16 2022-11-17 18:05:56

Re: How do I transfer a mariadb database to another server?

Board footer