Computing: Website and Database Programming

Web/Database environment on macOS.

3. MySQL database server setup.
  In my tutorial Web development environment setup on MS Windows: MySQL database server, I showed how to install and configure MySQL on Microsoft Windows. In this tutorial, you will learn, how to run a local database on macOS. The tutorial is about the installation of MySQL 8 on macOS 11 Big Sur, but I suppose that it applies to other (newer) versions, too.
  3.1. Installing MySQL.
The version of MySQL server used in this tutorial is MySQL Community Server 8.0.31, 64bit, downloaded as DMG file (mysql-8.0.31-macos12-x86_64.dmg) from the MySQL website. The archive contains a single file, with the same name, but with a .pkg extension. Double-click this file to install the package.
A warning pops up, telling you that you only should install software from trusted sources. Push the Allow button to confirm that you want to do the installation. The MySQL setup wizard is started; it will not only copy the new files, but also asks you for all information that is needed to configure the database server. After the Welcome and License agreement windows, you have to define the installation type. To use all default values, accept the Standard install on macOS type by simply pushing the Install button (If you want to make changes to the default setup, use the Customize button instead).
MySQL installation on macOS: Standard installation type
Files are copied, then, the wizard continues with the server configuration. The first decision, you have to make, is if you want to use strong or legacy password encryption. The advantage of using the latter one is that it will allow the connection of older clients (as for example MySQL 5 clients), too. And on a local server, there is not really the need to use enforced security settings (I think).
MySQL installation on macOS: Legacy password encryption
In the next window, you are asked to enter the root password and if you want to start the server automatically (screenshot). The configuration settings, selected by the user, are saved, and the wizard ends with a Thank you window.
MySQL installation on macOS: Root password and sever auto-start selection
  3.2. Installing MySQL Workbench.
MySQL Workbench is a GUI application that may be used for SQL development, as well as for server configuration, user administration, backup, and other administration tasks. It is not mandatory to install it. You can use phpMyAdmin instead, and you could even work with the only MySQL command line tools, if you wanted so.
You can download the application from the MySQL Workbench website. The download is a DMG archive (in my case named mysql-workbench-community-8.0.31-macos-x86_64.dmg). Double-clicking it, opens one of these installation windows, that I would call "typical for macOS". Beside information concerning the software, it contains 2 icons: On the left the application, on the right the macOS Applications folder. Really easy to start setup: Just drag the application icon onto the Applications folder (the one within this window). That's it!
MySQL installation on macOS: Installing MySQL Workbench
When I launched Workbench, a window popped up, telling me that my mac is an unsupported operating system and that this could lead to problems when working with it. I don't know what the reason for this could be. Version 8.0.31 is said requiring macOS 11.1 or higher, what with Big Sur 11.3 being installed on my computer should be ok. Anyway, I did not have any problems, when installing the sample database, configuring the users, and trying some SQL queries... MySQL Workbench starts with a Welcome screen, showing the available MySQL connections. With default settings, one local instance, running on localhost, port 3306. Click the instance to establish the connection.
MySQL installation on macOS: MySQL Workbench Welcome page
As a difference with phpMyAdmin, MySQL Workbench does not include the possibility to choose a user to log in. It's always the MySQL root user who is used, and after having clicked onto the MySQL instance, you'll be asked to enter the password that you set for root during the installation of the server. The default window displayed at startup is the server status page, as shown on the screenshot below.
MySQL installation on macOS: MySQL Workbench Server Status page
  3.3. Installing the "world" sample database.
I did not check if the sample database "world" is included with MySQL 8 on macOS, as it is on Windows (and could perhaps be installed, using a customized installation type). In fact, the tables of the Windows database use some Latin character set, and accentuated characters appear as "garbage" in the query output. So I think that the best and easiest to do is to create the database manually, using the SQL of the "world" database download file (I actually used the file that I had downloaded when recreating the database on my Windows 10). I set up the database step by step (creating the database, creating the tables, filling the tables) by copying the corresponding lines of the downloaded SQL code into a Query tab in MySQL Workbench and executing the statements. The screenshot shows the successful creation of the "country" table.
MySQL installation on macOS: Setting up the 'world' sample database in MySQL Workbench
If you have a closer look at the screenshot, you can see that there is an error message in relationship with the creation of the table "country_language". The reason for this is that "country_language" contains a foreign key that references a field in the "country" table. Thus, the "country_language" table has to be created after the "country" table.
When all tables have been created and filled, we can run SELECT queries on it. Here the SQL to display the number of records in the "city" table:
    select count(*) as Cities from city;
The screenshot shows the result returned by this query.
MySQL installation on macOS: 'world' sample database - Simple SELECT query in MySQL Workbench
  3.4. MySQL schemas, users and privileges.
Even on a local server it's highly recommended not to use root (but a user with limited permissions) to access MySQL from your applications. The root user has full read-write access to all tables in all databases, thus, not difficult to imagine what could happen if your program has a bug...
The concepts of MySQL schemas and privileges are explained in part 5 of my Web development environment setup on MS Windows - MySQL database server tutorial. It also describes my two standard MySQL users "allu" and "nemo", the first one having read (select) access to all databases and write (insert, update, and delete records) and execute access to individual databases (as, for example, "world"); the second one having no global privileges at all and only select access to individual databases (limited read-only user). With these permissions, root will be the only one who has administration privileges (such as creating, altering or dropping databases or tables). The screenshot below shows the global and database-specific grants for the "world" database for all MySQL users on my macOS Big Sur.
MySQL installation on macOS: 'world' database user privileges in MySQL Workbench
  3.5. Accessing MySQL from PHP and Perl.
I described setup of PHP and Perl in my Web/Database environment on macOS: Dynamic webpages with PHP and Perl tutorial. Using MySQL with PHP is even simpler than on Windows; using it with Perl ... I failed so far!
Concerning PHP (I'm actually using PHP 7.3, that came with the original installation of macOS Big Sur): On macOS, the MySQL related extensions are enabled by default in php.ini, so if you succeed to run my hello.php script, you should also succeed to run mysql.php (without having to do any further configuration steps). Here the code of my sample script, placed in the /Library/WebServer/Documents directory:
            <title>PHP-MySQL test</title>
                $host= ''; $database='world'; $user = 'nemo'; $passwd = 'nemo';
                $mysqli = new mysqli($host, $user, $passwd, $database);
                $sql = "SELECT count(*) AS _count FROM city";
                $uresult = $mysqli->query($sql, MYSQLI_USE_RESULT);
                $count = 0;
                if ($uresult) {
                    $row = $uresult->fetch_assoc(); $count = $row['_count'];
                echo "Number of cities in database 'world' = $count";
To run the script, type localhost/mysql.php in your web browser address field. Here the result in Safari:
MySQL installation on macOS: Running a PHP script with database access in Safari
Concerning Perl: As a difference with Windows, the Perl modules for database access (cf. part 5 of my Web development environment setup on MS Windows: Perl tutorial) are not (or at least not all) included with the macOS default Perl installation (Perl 5.30 on macOS Big Sur). Thus, you'll need to install them, using CPAN (cf. part 6 of the Web development environment setup on MS Windows: Perl tutorial).
To launch the CPAN client, open a terminal and enter the command:
    sudo perl -MCPAN -e shell
The CPAN commands to install the required modules are the following:
    install Data::Dumper
    install DBI
    force install DBD::mysql
where the "force" is used because some of the tests will fail due to an invalid MySQL root password; using "force", these failures will be simply ignored.
I failed to make Perl access to MySQL work on my Mac. The installation of Data::Dumper took a long time, but finally succeeded. DBI was already installed. But, during the installation of DBD::mysql, lots of errors and warnings were displayed, and the installation finally failed with return status 512. I hope that I'll find a solution to this issue in order to update this document...
  3.6. Accessing MySQL from Lazarus/Free Pascal.
To connect to a MySQL database from a Lazarus/Free Pascal application using a TMySQL80Connection, please have a look at the Accessing MySQL from Lazarus/Free Pascal on macOS tutorial in the Lazarus/Free Pascal Programming section of my site.

If you find this text helpful, please, support me and this website by signing my guestbook.