As a Server Owner

MySQL can have advantages as well as drawbacks. While configuring a plugin, you'll often notice the ability to store data in MySQL instead of in files. The main point of an external database such as MySQL is to offload data fetching, allowing high-speed access to small sections of a large amount of data.

A database can be an excellent idea for plugins that only need small amounts of data at a time, such as economy or block logging. For other plugins that need all data loaded at startup, using MySQL is generally a bad idea. Some plugins that need to load all data are region or town or region protection plugins. A good example is WorldGuard, in which you should almost never use the SQL region adapter.

This is a bit of a grey area in plugins that load data for a single player when they log in. In general, you should not use a database in this case unless the data persists across multiple servers. Some examples of plugins like this would be permission plugins. If the data fetch takes too long, everything that needs the data is stuck waiting for it, which can potentially cause lag spikes when a player joins.

In general, if a plugin supports both MySQL and files, it's likely not written in a way that will benefit from MySQL. While this isn't always the case, a good rule of thumb is only to use an external database if it's the default option or needed for other functionality (such as sharing data between servers). Do note that most plugins that use a database are not designed for sharing the data between multiple servers. Just because it's possible doesn't mean it's safe.

As a Developer

When deciding whether your plugin should use a database such as MySQL or not, you should first identify how your plugin needs to access data. If your plugin requires a large portion of your data readily available or does widespread lookups of the information on the main thread, it may be best to load the data from a file.

Databases are best suited for accessing small amounts of data asynchronously. You should never interact with a database from the main thread in a Minecraft plugin.

The biggest differentiator in loading data from a file or database is that generally, files load the entire contents, while a database loads small amounts of the data. If you only need a player's data while connected, storing a file per player and loading on player join is likely a good option. Using a database makes more sense if you have a considerable amount of data with unpredictable access patterns.

A note about MongoDB or NoSQL databases

The most common NoSQL database used in Minecraft plugins is a document database, such as MongoDB. These are very different from MySQL and other SQL databases and should never be used in the same way. Document databases act more like remote files rather than a way to query data. These can be the right choice for plugins that store a file per player and load the data on player join but need the data available on multiple servers.

Conclusion

There's no definitive rule of whether you should or should not use a database, but there are situations that better suit one or the other. Using a file is probably the right choice in most cases, but there are definitely use cases for databases. The most important thing to know is that no format is better than others; they suit different situations.