How to migrate databases with mongodump and mongorestore

For small deployments , the MongoDB utilities mongodump and mongorestore is an efficient and useful method. The steps below outline how to take a backup of the MongoDB databases on server1 and migrate\restore onto server2 The mongodump utility can create a backup for a server, individual databases or collection(s), or a customised query for a collection subset. In this example - I'm taking a mongodump of all the user databases including admin on MongoDB - server1. I'm using a logon account with sufficient privileges , that has at least backupadmin privileges Step 1 - This command backups all the databases... Read more →

How to edit a user password with MongoDB Ops Manager

Question: I have a list of MongoDB Users refreshed and appearing in the Deployment | MongoDB Users Screen. These were all users created directly on the MongoDB server outside of the MongoDB Ops Manager. When the users were refreshed the Synched status is set at No. What I'm trying to do is change the password for the user via the MongoDB Ops Manager - but without having to do it at the MongoDB server. The user is created in 3 replica sets - and I'd like to maintain the same password in all the 3 replicasets. Is it possible to... Read more →

MariaDB is number function

Question: I am creating a View in MariaDB to to streamline a recordset requested by an application. As part of the View - there is a requirement to create some CASE statements and check to assess whether the data is numeric. How can I create a function to check if the data is numeric? Answer: MariaDB has plenty of Numeric functions - which process Functions dealing with numerals, including ABS, CEIL, DIV, EXP, PI, SIN, etc - but you are looking for a function - which checks to see if some data is a number. MariaDB has good support for... Read more →

How to make MySQL tables case insensitive

Question: I'm working with vendor supplied ddl scripts, which are causing case-sensitive issues. Early in the scripts - there is a CREATE TABLE statement . But further down the script there is an INSERT statement - but the case of the table is lower - causing an error . How can I fix this issue? Answer:To fix this issue - change the "lower_case_table_names" variable . To see the current value of lower_case_table_names use : show variables like '%lower_case_table_names%' To solve this problem - you will need to change the value to "1" . Find the location of the "my.cnf" file... Read more →

MariaDB EXPLAIN same query - different results on different servers

Same data , same queries , different response time. Server 1 is MariaDB version 10.2.6 using the innodb version 5.7.14 Server 2 is MariaDB version 10.3.17 a using the innodb version 10.3.17 There is a VIEW and within the VIEW there is a UNION covering two SELECT statements . The underlying dataset is greater than 100 million rows.Note: Dataset is refreshed twice a year - Since the migration to Server 2 - the index from the table 1 is not used , causing a significant drop is response time for certain queries. The Execution Plan is now forcing a Table... Read more →

MongoDB Performance Tuning

Find a list of "out of the box" command line tools , used to gather MongoDB database and server data. This information can be used to identify bottlenecks for troubleshooting performance issues.This list only begins to touch upon sources of MongoDB data. MongoDB database sizes are growing ,requiring new strategies for maintaining MongoDB performance. Benchmarking query response times and other indicators serves as a basis to database performance. Predictability is the key to scalability A performance strategy should include a) best practises for deploying MongoDB servers & b)a SYSTEMATIC tuning process & c) Asking the right questions My general steps... Read more →

Transparent Huge Pages - Detected startup warnings on a database backing the system

Question: I'm getting these messages as system alerts through the MongoDB Ops Manager. 2020-02-07T21:34:21.241+0000 I CONTROL [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'. 2020-02-07T21:34:21.241+0000 I CONTROL [initandlisten] ** We suggest setting it to 'never' What do they mean? and how can I stop these warning messages occuring? Answer: The Transparent Hugepages Linux kernel feature aims to boost performance by by "optimising" the processor's memory mapping. By using larger memory page sizes , the aim is to decrease the Translation Lookaside Buffer(TLB) lookups . By default this feature is enabled in most Linux distributions. MongoDB recommends to Disable Transparent Huge Pages Follow... Read more →

MariaDB RLIKE condition

Question: I'm having a problem using RLIKE and searching for a hyphen '-' , within a string.Also some other problems related to identifying strings with both character and numeric , while excluding numeric only or letters only strings. These are some examples of the strings: Example 1 : Digit followed by letter – 12B, 110A , 78B, 2150A, 287H, 941C Example 2: Numbers or numbers/letter with hyphen separator eg 61-63, 98-99, 1-33,2-14,1A-2A,169-173 Example 3: Numbers with forward slash separator e.g 1B/32,142/8 ,336C/2 Could you give me some RLIKE examples for each of the above scenarios Answer: The following are simple... Read more →

MongoDB Replica Set High Availability Notes

MongoDB offers a strong solution for High Availability via the Replica Set High Availability setup. DBAs work with HA and become familiar with the behaviour during setup and management, there is also a requirement to communicate with application teams some of the HA behaviour. Developers can use this information to design their applications In the examples below - we are using a 3 node cluster set. A 3 node cluster replica set has a fault tolerance supporting unavailability of 1 node while still having a Primary Node and the ability to still write to the Primary. If 2 nodes become... Read more →

mongo : command not found

Question: I've installed the mongo as part of a replicaset . The server is part of a 3 - node replicaset and I can manage through MongoDB Operations Manager. But when i get onto the command line and type "mongo" , I'm getting the -bash: mongo: command not found. How can I fix this issue - as I'd like to be able to have the command line option Answer: Assuming everything is running as expected - the most likely reason is that the relevant mongo binaries are not in the /usr/bin folder. I've had this problem a few times and... Read more →