I have a working MySQL (MariaDB) Server running on my raspberry pi. It works fine when I want to connect to it from my local network.
My specs are as followed:
MariaDB [mysql]> SHOW VARIABLES LIKE "%version%";
+-----------------------------------+------------------------------------------+
| Variable_name | Value |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000 |
| innodb_version | 10.3.22 |
| protocol_version | 10 |
| slave_type_conversions | |
| system_versioning_alter_history | ERROR |
| system_versioning_asof | DEFAULT |
| version | 10.3.22-MariaDB-0+deb10u1 |
| version_comment | Raspbian 10 |
| version_compile_machine | armv8l |
| version_compile_os | debian-linux-gnueabihf |
| version_malloc_library | system |
| version_source_revision | 0152704ae3f857668dbc05803950adcf131b8685 |
| version_ssl_library | YaSSL 2.4.4 |
| wsrep_patch_version | wsrep_25.24 |
+-----------------------------------+------------------------------------------+
14 rows in set (0.013 sec)
But I want to be able to access it not only from my local network I want to be able to access it from everywhere in the world. How do I do that ?
I wouldn't recommend you to expose a database to the world. Usually a database will seat behind of an app server that will serve web pages, web services (or rest calls). This app server will read or write to the database as needed.
Having said that, it's technically possible to expose the database. Again, don't do it. ...but if you must:
Configure the engine to serve remote hosts, and not just the local apps:
sudo vi /etc/mysql/my.cnf
and set the bind address to:
bind-address = 0.0.0.0
then, restart the engine:
sudo service mysql restart
Create a MariaDB user with access from everywhere (using @'%'), as in:
create user 'myuser'@'%' identified by 'mypass';
Grant this user access to a database (assuming you already created a database):
grant all on my_database.* to 'myuser'@'%';
Finally, open your home firewall. Enter the admin page of your router and find the "Port Forwarding" section. There, add a rule to listen to the world to port 3306 (TCP) and redirect it to your local raspberry pi IP address. Save the rule. You may need to restart the router.
That's it. Your raspberri pi database is now listening to the world. I would suggest configuring SSL on the connection at least, so passwords (and data) are not sent in plain text over the wire.
Extra, for the same price: Listening on which address, you may ask? Your home address as seen by your ISP. Now, can I use a fake domain name in case the IP changes, you may ask? You can use a free DNS service such as duckdns.org. It's free and works like a charm in a raspoberry pi (I use it since 2015).