MongoDB or NeDB

[haxmachine]

Hi Ylian,

Been following your project for some time and have been using it internally on the standard DB.
I'm going to implement a Live environment on WAN.

Which database would you suggest I use moving forward NeDB ro MongoDB and if MongoDB and what version community
or enterprise

Ill have 100-200 endpoints.
it will run on server 2019 Standard.

[VINISHVV]

@haxmachine

MongoDB is good while compare with other DB's.
I am using MongoDB community edition v5.0.1

[haxmachine]

Thanks for that.
I wanted to know is there any performance gains using MongoDB instead of NeDB.

[VINISHVV]

NeDB is suggested for small organizations having agents less than 500.
If your agent count is more then you can experience the differences.

[Ylianst]

Exactly right. If your using MeshCentral in production or for anything critical, I highly recommend using MongoDB. NeDB has not been updated in a long time and MongoDB is much more professional.

[haxmachine]

Ok, I will then deploy it with MongoDB community edition from the start, this way the platform is ready from the start.
How far off do you think MySql will be.
I would have preferred MSSQL since I've been using it for 14years for other services.

[Ylianst]

MySQL should work fine now. We just convert MongoDB calls into SQL calls and it seems to work well. One benefit of MongoDB is that MeshCentral is capable of "bulk reads" and "bulk write" into MongoDB. You need to add the following line in the settings section of the config.json:

"MongoDbBulkOperations": true

This should boots server scaling quite a lot.

For MSSQL, I am not opposed to supporting it at some point, however it's a licensed database with added costs to run and not sure how many in the community would use it.

[haxmachine]

So true Licencing is bomb.
Sounds like MongoDB is the way to go.
Im already in the middle of setting that up.
Much appreciated with the feedback.

[haxmachine]

since im new to MongoDB
when creatinf the initial Meshcentral DB
what do i fill in for it

[nzalev]

I do not believe you have to manually create the database if you are using mongodb. It has been a while since I have installed it on Windows.

However, if you prefer MySQL / MariaDB, I have worked a lot on it and it is currently at feature parity with MongoDB. You do have to first create a user+database (or use the root user to create the db automatically).

The config.json looks like

 "settings": {
    "wanonly": true,
    "mysql": { 
      "host": "localhost",
      "user": "meshcentral", 
      "port": 3306,
      "password": "meshcpassword",
      "database": "meshcentraldbname"
    },
    ...

Note that when you set up a MySQL server in particular, you have to enable Legacy Authentication. This is a limitation of the nodejs mysql connector (mysqljs/mysql#2002 , mysqljs/mysql#2233). There are ssl options though, you can find them in https://github.com/Ylianst/MeshCentral/blob/master/meshcentral-config-schema.json

PS: I can look at adding MSSQL support, but I don't think this will happen soon. As far as I'm aware, you are the first person to ask for it. I think you would be the only one to use it.

[haxmachine]

This project is awesome and has such great potential.
the community is fantastic and very helpful.
I will stick to what's proven and works well MongoDB

[nzalev]

If you are referring to Download server backup and Restore server with backup, these will only be shown when using NeDB. This is because NeDB uses a single file to store the DB (which is much easier to download and upload) whereas MongoDB/MariaDB/MySQL are all full fledged database engines which require their own tools (mongodump, mysqldump).

Regarding your server warning, meshcentral has not found the path to mongodump. You need to include the following option in your config.json:

   "AutoBackup": {
      "mongodumppath": "C:\\Program Files\\MongoDB\\Server\\4.4\\bin\\mongodump.exe"
    },

Make sure you use the actual file path to where mongodump is on your system. The double \ is also needed.

[haxmachine]

Thanks Nzalev.
I changed the path to where Mongodump.exe and i still get the same error

[nzalev]

Can you post your config.json. I'll let you know if there are any issues.

[haxmachine]

[nzalev]

I have spotted the issue. The "MongoDumpPath" option cannot be put at the root level. It must be inside "AutoBackup"

In your instance, this would look like

"_MongoDbChangeStream": true,
"AutoBackup": { "MongoDumpPath": "C:\\Program Files\\MongoDB\\Server\\5.0\\bin\\mongodump.exe" },
"WANonly": true,

[haxmachine]

Nzalev you the best awesome fix

[itpilot]

Help with Meshcentral MongoDb Ubuntu Backup Option

guys please can you help

i have installed the Latest Meshcentral version v.0.8.98 on a Ubuntu 20.4 Server

I need help with the following:

Under Server Actions I DO NOT have the Following Options:
" Download server backup "
" Restore server with backup "

when i do " whereis mongodump " i get = mongodump: /usr/bin/mongodump /usr/share/man/man1/mongodump.1.gz
when i do " whereis mongodb " i get = mongodb: /etc/mongodb.conf

my Meshcentral files are in " /opt/meshcentral "

i need to understand what i need to enter in my " config.json " file so that i get the Available Server Actions to make mongodb backups?

when i enter the following in my " config.json " file

"AutoBackup": {
"mongodumppath": "/usr/bin/mongodump",
},

it still does NOT give me the Server actions to make backups or restore

any help greatly appreciated

[nzalev]

I had partially answered that question in a few comments above:

If you are referring to Download server backup and Restore server with backup, these will only be shown when using NeDB. This is because NeDB uses a single file to store the DB (which is much easier to download and upload) whereas MongoDB/MariaDB/MySQL are all full fledged database engines which require their own tools (mongodump, mysqldump).

Meshcentral will create automatic backups which includes the db in a directory called meshcentral-backup. You mention that you have meshcentral installed to /opt/meshcentral/, then it will be /opt/meshcentral/meshcentral-backup/

To verify that it is working correctly, you can run autobackup in the server console:

If it is working correctly, you will find the backup in the meshcentral-backup directory mentioned above, along with daily backups.

[itpilot]

Thank you that worked

my question is
is there any way

Under Server Actions I DO NOT have the Following Options:
" Download server backup "
" Restore server with backup "

How do i get the two options under " Server Actions " ?

[nzalev]

Again, Download server backup and Restore server with backup will only be shown when using NeDB. These links will not be displayed if using MongoDB.

[I2700I]

Is there a possibility to transition the existing setup (which is the default one out of the box) to the MongoDB? Quite a bunch of stuff already implemented that I don't want to lose.
@Ylianst can you please point me in the right direction?

[PetieM]

Quick question for @nzalev: I just successfully migrated over to MySQL (using Mongo before but I run a central MySQL server for a bunch of other projects and wanted to consolidate if possible). Everything seems to be working great after finding your config example but I'm now getting a warning about being unable to find mysqldump every time I start the server and on the server info page. Is there a way to tell MeshCentral to ignore this if you are running the MySQL instance on a different server?

[nzalev]

@PetieM Meshcentral uses mysqldump or mongodump to include the database in its automatic backups. In either case, when the database server is remote, meshcentral still needs access to the dump commands or else it will produce this warning.

I don't know off-hand if there is a way to disable that warning but I can get back to you on that later today.

Are you creating DB dumps at the remote server instead?

[PetieM]

I am creating dumps on the remote server but went ahead and installed the MySQL client applications on my MeshCentral server as well. I figure more backups is never a bad thing. It's installed and still telling me it can't find mysqldump though. Do I need to specify its path somewhere?

[nzalev]

Generally, if you are on Windows you have to specify the path, but usually not on linux.

Continuing from my MySQL example above:

 "settings": {
    "wanonly": true,
    "mysql": { 
      "host": "localhost",
      "user": "meshcentral", 
      "port": 3306,
      "password": "meshcpassword",
      "database": "meshcentraldbname"
    },
    "AutoBackup": {
      "mysqlDumpPath": "C:\\Program Files\\...\\mysqldump.exe"
    },

    ...

If you are using windows, the double backslashes in the string are required. If you are using linux, the path is usually /usr/bin/mysqldump but can be found by running which mysqldump.

I don't know the path that it is usually installed to on Windows.

[PetieM]

I'm on CentOS 8 at the moment and it's running just fine (though I'm getting permission errors that I'll need to fix as it seems to be trying to access data outside of the meshcentral database) when I manually execute autobackup but still warns that it can't be found. Should I specify the path anyway or is there something else going on here?

[nzalev]

You should run which mysqldump. The expected output looks like

noah@L460:~$ which mysqldump
/usr/bin/mysqldump

and then fill in

    "AutoBackup": {
      "mysqlDumpPath": "/path/to/mysqldump"
    },

Quick edit:
meshcentral drops to the shell to run the mysql command directly. If specifying the path does not fix it, the form of the command is as follows:

 "/path/to/mysqldump" --user='meshcentral_user' --password='meshcentral_password' -h host -P dbport dbname > "/dev/null"

You can also try running mysqldump with these parameters (the user/pw/host/port/dbname all from your config.json) and see if it fails (and why).

This error is actually generic and is used for all error conditions. This is true for both mysql and mongodb. If adding the path manually does not fix it, you can modify line 1835 of db.js from

    func(1, "Unable to find mysqldump, MySQL/MariaDB database auto-backup will not be performed.");

to

    func(1, "Unable to find mysqldump, MySQL/MariaDB database auto-backup will not be performed." + error);

If you're not comfortable with that, I can put in a PR with an update to the error reporting, but I don't know when Ylian is going to be fully back online to accept the pull.

[PetieM]

Okay, it looks like it's finding it but there are a couple of components to the error it's throwing. See below:

Command failed: "mysqldump" --user='[username]' --password='[pasword]' -h [host] -P 3306 meshcentral > "/dev/null" mysqldump: [Warning] Using a password on the command line interface can be insecure. mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'meshcentral' AND TABLE_NAME = 'eventids';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

So it seems related to the fact that it's executing with a password on the command line? On top of that though, it seems to want to select from information_schema which shouldn't be needed if you're just dumping the one database. And --no-tablespaces should probably be added to the command to prevent needing to give the user account the global "PROCESS" privilege.

[nzalev]

I have looked at adding no-tablespaces before, I am not sure why I did not add it.

To solve your issue though, can you create the file /etc/my.cnf (if it does not exist) and add the lines

[mysqldump]
column-statistics=0

[PetieM]

Adding that to my.cnf seems to have resolved all of this in one go - I'm no longer getting an access denied message even without --no-tablespaces so it looks like that's not needed after all. Thank you for the extremely responsive help today!