• Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Search
  • Register
  • Login
Netgate Discussion Forum
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Search
  • Register
  • Login

How To install MySQL on Pfsense 2.4.5-RELEASE-p1

Scheduled Pinned Locked Moved Captive Portal
21 Posts 4 Posters 4.0k Views
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • O
    ontzuevanhussen
    last edited by Jun 17, 2020, 9:34 AM

    I know that this is a repetitive questioning " How to install Mysql on pfsense ?" I need to make Freeradius with mysql as database. Thank you

    1 Reply Last reply Reply Quote 0
    • J
      jimp Rebel Alliance Developer Netgate
      last edited by Jun 17, 2020, 8:41 PM

      pfSense is a firewall, not a database server. You don't install MySQL on pfSense, you install it on another server and then have FreeRADIUS connect to that server.

      Remember: Upvote with the 👍 button for any user/post you find to be helpful, informative, or deserving of recognition!

      Need help fast? Netgate Global Support!

      Do not Chat/PM for help!

      O 1 Reply Last reply Jun 17, 2020, 10:11 PM Reply Quote 0
      • O
        ontzuevanhussen @jimp
        last edited by ontzuevanhussen Jun 17, 2020, 11:52 PM Jun 17, 2020, 10:11 PM

        @jimp Ok, thank you. But where I can find tutorial about that? Please help

        1 Reply Last reply Reply Quote 0
        • G
          Gertjan
          last edited by Jun 18, 2020, 7:36 AM

          Hi,

          Back then, an ordinary windows PC could do the job.
          Look for WampServer or EasyPHP. These have the huge advantage that all is in one place, and you could even install the PHP project "phpmyadmin" as a CMS on that web server, so you can 'admin' your databases.
          Just keep in mind that the MySQL server used by these projects will be bound to 127.0.0.1 which means it can't accpet connection from other devices on your LAN network.
          A small "my.ini" modification will be needed. This question is in Google's top 100 most asked questions on the Internet, since 1995. Check also the Windows firewall to open the MySQL TCP port. And that's about it.

          Have a VM host on your PC : consider running your MySQL server in a VM. You could take one of the classic OS's like Debian and make it a MySQL / web server. That one is in Google's top 10 most ever asked questions / answered list. It's considered to be a no brainer these days.

          The official Microsoft Window servers editions like Windows 2008, 2016 etc all have MySQL, sorry : MSSQL - servers aviable. Some of them are free.

          If you have a "smart" NAS in your network, like, for example, a Synology diskstation, you could install a MySQL server package on your NAS. That's what I'm using. It's not a performance beast, but it does nicely what it was asked to do.

          You have a dedicated server, VPS or cloud thing running some where on the net ?, You can use that one.

          You have a smart phone ? Now it's your chance to have it doing something usefull : The MySQL app probably exists ;)

          Btw : most of the time, you won't be running MySQL (the onwer today is ... SUN , No. Orcale !) and isn't freeware any more. So, the project was forked, and now everybody shifts to "MariaDB", which is (should be) a MySQL 100 % clone.

          Note : when you install packages like FreeRadius on pfSense, you will also install the MyQL client software, not to be confounded with the server part.

          Btw : "it can be done" : installing the FreeBSD package MySQL-server on pfSense.
          The knowledge needed to set it up and correct / move the installed files is a pure waste of time.
          A pfSense update could break MySQL server- but you'll learn how to repair that.
          A MySQL server update could break pfSense - but you'll learn how to repair that.

          It is really advisable to do what everybody else does. The main advantage is : you'll find examples and support easily.

          No "help me" PM's please. Use the forum, the community will thank you.
          Edit : and where are the logs ??

          O 1 Reply Last reply Jun 18, 2020, 11:51 AM Reply Quote 0
          • O
            ontzuevanhussen @Gertjan
            last edited by Jun 18, 2020, 11:51 AM

            @Gertjan Yes, I have VPS on digitalocean with ubuntu server 18.04.4 LTS

            G 1 Reply Last reply Jun 18, 2020, 12:08 PM Reply Quote 0
            • G
              Gertjan @ontzuevanhussen
              last edited by Gertjan Jun 18, 2020, 12:09 PM Jun 18, 2020, 12:08 PM

              @ontzuevanhussen said in How To install MySQL on Pfsense 2.4.5-RELEASE-p1:

              ubuntu

              Well .....

              sudo apt install mysql-server
              

              and you enter in the world of 'maintaining a real database'.

              When everything is set up correctly : check :

              That your VPS - mysql server port (default) 3306 is protected with a firewall rule that accepts traffic from your WAN IP pfSense only. When you instal mysql server on Ubuntu, by default, it will be bound to 127.0.0.1 - or maybe even only a local socket.
              mysql server ports should never be exposed to "the Internet".
              Consider also using the SSL version of the traffic, not the "all clear standard protocol" (traffic between myslsql-client running on pfSEnse and mysql-server running on your VPS) because your using 'public roads' (the Internet) for interconnecting 'local only' services.
              Anyway : there a million or so tutos on the net about how to set it up.

              No "help me" PM's please. Use the forum, the community will thank you.
              Edit : and where are the logs ??

              O 2 Replies Last reply Jun 18, 2020, 4:07 PM Reply Quote 0
              • O
                ontzuevanhussen @Gertjan
                last edited by Jun 18, 2020, 4:07 PM

                @Gertjan Already open port on my VPS,
                Screen Shot 2020-06-18 at 22.54.04.png
                Screen Shot 2020-06-18 at 22.54.41.png

                And this my database for FreeRADIUS server:
                Screen Shot 2020-06-18 at 23.03.38.png

                This is on my pfSense:
                Screen Shot 2020-06-18 at 23.05.17.png

                All my steps is correct? What is the next step? Please help. Thank you

                1 Reply Last reply Reply Quote 0
                • O
                  ontzuevanhussen @Gertjan
                  last edited by Jun 18, 2020, 4:18 PM

                  @Gertjan This is my System Logs
                  Screen Shot 2020-06-18 at 23.16.05.png

                  1 Reply Last reply Reply Quote 0
                  • G
                    Gertjan
                    last edited by Gertjan Jun 18, 2020, 4:36 PM Jun 18, 2020, 4:34 PM

                    First image : That's a click-interface. I don't understand.
                    I use the universal language :

                    netstat -naptul | grep '3306'
                    

                    That one works for decades now.
                    => Although : check firewall is needed. Again : never ever leave a MySQL server port open on the net, or you will loose your entire VPS server "very soon".

                    Second image : phpmyadmin. Nice. More classic isn't possible.

                    Third image :
                    Nothing is filled in yet.
                    Shake your head, and the info will fall out, onto the keyboard, on the screen.
                    I could even fill it in for you, except the password for the MySQL user that accesses the database 'radius'. That one is in your head only.

                    Last image : I don't get it.
                    You do not have any authentication credentials filled in - and you are surprised the FreeRadius package can't connect to 'the server' ?
                    When you connect to your mail server, you need to have it's address like IP or host adress, login code, password, port number etc - right ? here, it's the very same thing.

                    Btw : My settings are very identical :

                    1c71d074-8d2d-4105-aef2-465532a16094-image.png

                    No "help me" PM's please. Use the forum, the community will thank you.
                    Edit : and where are the logs ??

                    O 6 Replies Last reply Jun 18, 2020, 11:01 PM Reply Quote 0
                    • O
                      ontzuevanhussen @Gertjan
                      last edited by Jun 18, 2020, 11:01 PM

                      @Gertjan said in How To install MySQL on Pfsense 2.4.5-RELEASE-p1:

                      netstat -naptul | grep '3306'

                      This is my VPS:
                      Screen Shot 2020-06-19 at 05.55.40.png

                      1 Reply Last reply Reply Quote 0
                      • O
                        ontzuevanhussen @Gertjan
                        last edited by Jun 18, 2020, 11:09 PM

                        @Gertjan I use mariadb:
                        Screen Shot 2020-06-19 at 06.07.58.png

                        1 Reply Last reply Reply Quote 0
                        • O
                          ontzuevanhussen @Gertjan
                          last edited by Jun 18, 2020, 11:17 PM

                          @Gertjan my ufw status on VPS :
                          Screen Shot 2020-06-19 at 06.14.57.png

                          1 Reply Last reply Reply Quote 0
                          • O
                            ontzuevanhussen @Gertjan
                            last edited by Jun 18, 2020, 11:25 PM

                            @Gertjan This is my pfSense SQL configuration:
                            Screen Shot 2020-06-19 at 06.22.53.png

                            Screen Shot 2020-06-19 at 06.23.47.png

                            Screen Shot 2020-06-19 at 06.24.02.png

                            1 Reply Last reply Reply Quote 0
                            • O
                              ontzuevanhussen @Gertjan
                              last edited by Jun 19, 2020, 12:04 AM

                              @Gertjan This my System logs on pfSense:
                              Screen Shot 2020-06-19 at 07.03.28.png

                              1 Reply Last reply Reply Quote 0
                              • O
                                ontzuevanhussen @Gertjan
                                last edited by Jun 19, 2020, 12:16 AM

                                @Gertjan I have create user and allow from remote access my database:
                                Screen Shot 2020-06-19 at 07.13.47.png

                                1 Reply Last reply Reply Quote 0
                                • G
                                  Gertjan
                                  last edited by Gertjan Jun 19, 2020, 7:34 AM Jun 19, 2020, 7:31 AM

                                  First image :

                                  ce62d87e-74ea-475b-9fe3-453f247cc50d-image.png

                                  Doesn't that ring a bell ?
                                  A service that is listening on address 127.0.0.1 can only be reached by the system itself. That's the defintion of localhost or 127.0.0.1.
                                  Everybody else, locally on a LAN ,and the Internet : negative.
                                  You have to listen (add) to address "0.0.0.0" which means all available IPv4 addresses.
                                  This will include the one you masked here :

                                  89177265-8af6-41e8-b36c-8f6c9ef6b0a6-image.png

                                  You should have a :
                                  tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13538/mysqld

                                  and of course, please, limit the access to this port with a firewall rule, so it accepts only traffic from your pfSense.
                                  If not, "you get hacked in no time", because the MySQL traffic is non intended to be exposed to the entire Internet.

                                  I'll leave it up to you to find the instruction to be placed into /etc/my.cnf so the mysql server daemon listens to 'everything'.

                                  No "help me" PM's please. Use the forum, the community will thank you.
                                  Edit : and where are the logs ??

                                  O 1 Reply Last reply Jun 19, 2020, 11:23 PM Reply Quote 0
                                  • O
                                    ontzuevanhussen @Gertjan
                                    last edited by ontzuevanhussen Jun 19, 2020, 11:24 PM Jun 19, 2020, 11:23 PM

                                    @Gertjan Ok now it works well.
                                    Screen Shot 2020-06-19 at 21.04.49.png

                                    but table nas, radacct, radcheck, radgroupcheck, radgroupreply, radreply, and radusergroup is no data. why?

                                    1 Reply Last reply Reply Quote 0
                                    • G
                                      Gertjan
                                      last edited by Gertjan Jun 20, 2020, 7:13 AM Jun 20, 2020, 7:12 AM

                                      When you enable all "SQL" options :

                                      58727c89-cda6-4d5e-8153-92a4ded8a815-image.png

                                      only two tables get filled in with data from the Freeradius process :
                                      radpostauth and radacct.

                                      The radcheck, radgroupcheck should by filled in by you, using phpmyadmin.
                                      Their content can not be modified from within pfSense.
                                      As you have figured out : the users and passsword could have been placed also in a database table, but pfSense uses us a file on disk for this ( PackageFreeRADIUS > Users > Users )
                                      The Freeradius package only exposes a (very) small part of all possible FreeRadius possibilities.
                                      Most option are hard coded, and can't be changed using the pfSEnse GUI.

                                      To get the idea, see this file : /usr/local/etc/raddb/sites-enabled/default and the entire directory /usr/local/etc/raddb/ (all files and folders).

                                      What these tables do, how to fill them in, etc, there is only one way to find that out : https://freeradius.org/documentation/

                                      Remember : MySQL (MariadDB) is actually doing nothing special. It's just the scratch pad area of FreeRadius.

                                      "FreeRadius" is a complex thing.

                                      No "help me" PM's please. Use the forum, the community will thank you.
                                      Edit : and where are the logs ??

                                      O 1 Reply Last reply Jun 21, 2020, 10:52 AM Reply Quote 0
                                      • O
                                        ontzuevanhussen @Gertjan
                                        last edited by Jun 21, 2020, 10:52 AM

                                        @Gertjan Ok, I try to insert table Radcheck and radgroupcheck, like this:
                                        Screen Shot 2020-06-21 at 17.50.59.png

                                        Screen Shot 2020-06-21 at 17.51.25.png

                                        Is that true? Can you help me again how to insert this table..

                                        1 Reply Last reply Reply Quote 0
                                        • G
                                          Gertjan
                                          last edited by Jun 22, 2020, 5:30 AM

                                          You can't.

                                          To have FreeRadius use all the MySQL database tables, you have to modify /usr/local/etc/raddb/sites-enabled/default - and probably other files as well - which means you have to modify pfSense itself.
                                          This means thorough FreeRadius knowledge and a good understanding about how pfSense makes the set up files.
                                          If that was the case, you wouldn't ask question, you were just doing it.

                                          No "help me" PM's please. Use the forum, the community will thank you.
                                          Edit : and where are the logs ??

                                          1 Reply Last reply Reply Quote 0
                                          3 out of 21
                                          • First post
                                            3/21
                                            Last post
                                          Copyright 2025 Rubicon Communications LLC (Netgate). All rights reserved.
                                            This community forum collects and processes your personal information.
                                            consent.not_received