Create Local Mysql Database Mac

  1. Create Local Mysql Database Macros
  2. Create Mysql Database On Local Machine
  3. Create Local Mysql Database Mac Os

A database in MySQL is implemented as a directory containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement creates only a directory under the MySQL data directory. Before we get started we need to create a phpapidb database, well this can be done through two methods either you can use the PhpMyAdmin, or you can do it by accessing the MySQL root user via the terminal or command-line tool to create the Database. We have written a detailed article on Installing and Setting up MySQL in Mac’s Terminal app.

Create Local Mysql Database Macros

The package is located inside a disk image (.dmg) file that you first need to mount by double-clicking its icon in the Finder. It should then mount the image and display its contents.

Before proceeding with the installation, be sure to stop all running MySQL server instances by using either the MySQL Manager Application (on macOS Server), the preference pane, or mysqladmin shutdown on the command line.

To install MySQL using the package installer:

  1. Download the disk image (.dmg) file (the community version is available here) that contains the MySQL package installer. Double-click the file to mount the disk image and see its contents.

    Figure 2.1 MySQL Package Installer: DMG Contents


  2. Double-click the MySQL installer package from the disk. It is named according to the version of MySQL you have downloaded. For example, for MySQL server 5.7.35 it might be named mysql-5.7.35-osx-10.13-x86_64.pkg.

  3. The initial wizard introduction screen references the MySQL server version to install. Click to begin the installation.

    Figure 2.2 MySQL Package Installer Wizard: Introduction


  4. The MySQL community edition shows a copy of the relevant GNU General Public License. Click and then to continue.

  5. From the Installation Type page you can either click to execute the installation wizard using all defaults, click to alter which components to install (MySQL server, Preference Pane, Launchd Support -- all enabled by default).

    Although the option is visible, the installation location cannot be changed.

    Figure 2.3 MySQL Package Installer Wizard: Installation Type


    Figure 2.4 MySQL Package Installer Wizard: Customize


  6. Click to begin the installation process.

  7. After a successful installation, the installer displays a window with your temporary root password. This cannot be recovered so you must save this password for the initial login to MySQL. For example:

    Figure 2.5 MySQL Package Installer Wizard: Temporary Root Password


    Mac snow leopard update. MySQL expires this temporary root password after the initial login and requires you to create a new password.

  8. Summary is the final step and references a successful and complete MySQL Server installation. the wizard.

    Figure 2.6 MySQL Package Installer Wizard: Summary


MySQL server is now installed, but it is not loaded (or started) by default. Use either launchctl from the command line, or start MySQL by clicking 'Start' using the MySQL preference pane. For additional information, see Chapter 3, Installing a MySQL Launch Daemon, and Chapter 4, Installing and Using the MySQL Preference Pane. Use the MySQL Preference Pane or launchd to configure MySQL to automatically start at bootup.

When installing using the package installer, the files are installed into a directory within /usr/local matching the name of the installation version and platform. For example, the installer file mysql-5.7.35-osx10.13-x86_64.dmg installs MySQL into /usr/local/mysql-5.7.35-osx10.13-x86_64/ . The following table shows the layout of the installation directory.

Create Local Mysql Database Mac

Table 2.1 MySQL Installation Layout on macOS

DirectoryContents of Directory
binmysqld server, client and utility programs
dataLog files, databases
docsHelper documents, like the Release Notes and build information
includeInclude (header) files
libLibraries
manUnix manual pages
mysql-testMySQL test suite
shareMiscellaneous support files, including error messages, sample configuration files, SQL for database installation
support-filesScripts and sample configuration files
/tmp/mysql.sockLocation of the MySQL Unix socket

Create Mysql Database On Local Machine


During the package installer process, a symbolic link from /usr/local/mysql to the version/platform specific directory created during installation is created automatically.

Whether you are a seasoned PHP / MySQL developer, or a beginner, it really helps to have a local development environment. This allows you to test your code as you develop your web application before publishing it 'live' to the Internet.

The following power tip will guide you through setting up a PHP, Apache, and MySQL development environment on your local Windows system. We'll also take a look at the UEStudio features that can make your PHP / MySQL development easier.

Please keep in mind that PHP, Apache, and MySQL are software provided by third party entities. Therefore we (IDM) cannot provide technical support for the installation of these on your system.

Step 1. Download the installation files

In days past, you would have had to download Apache server, PHP, and MySQL all separately. However, there are now solutions which include these components bundled together and pre-configured to run 'out of the box' on a Windows system. We recommend using WampServer, which provides the basics for setting up your local Apache / PHP / MySQL environment on Windows.

If you do not wish to use an 'all-in-one' solution, you will need to download and install the Windows installers for each of the below components:

  • Apache, the web server software.
  • PHP, the general purpose scripting language that runs on Apache.
  • MySQL, the database server software that works hand-in-hand with PHP and Apache.

Step 2: Install everything and make sure it works

Create Local Mysql Database Mac Os

For the purposes of this power tip, we are assuming that you've chosen to install WampServer. To install it, simply double-click the setup file and follow the prompts.

You can confirm that everything has installed properly by pointing your browser to http://localhost/ while Wamp is running. If you see the WampServer 'welcome' page, you know everything is installed and running properly. You can also verify this by checking the Wamp icon in the system tray – if the icon is green, then everything is up and running.

Note: You may need to download and install the dependencies described in this community forum post in order for WampServer to run properly on your system.

Step 3: Set up MySQL

Since Wamp installs MySQL, all you need to do is set up a user and a database. Click the Wamp icon in the system tray, then go to MySQL » MySQL console. If the console prompts you for a password, just hit Enter.

Create a MySQL database

Now you need to create a new MySQL database. You can do this by issuing the following command in the console:

Make sure to include the semi-colon at the end of the command! The console should give a 'query ok' response.

You can verify that the databases have been created by using the 'Show databases' command. If you type, 'Show databases;' and hit enter, you should see something similar to:

Create a Table

For the purposes of this power tip, we will create a table called 'names' that includes the following fields: key, first, last. Create this table in our 'uestudio_test' database by using the following commands:

USE uestudio_test;

..then:

CREATE TABLE names (id INT NOT NULL AUTO_INCREMENT, first VARCHAR(48), last VARCHAR(48), PRIMARY KEY(id));

We won't cover the syntax of these commands in the scope of this power tip.

You can see the structure of the table and confirm it was created correctly by typing:

You should see something similar to:

Now, we need to insert some sample data into our table. For example, if we want to create an entry to insert the name 'John' (first) 'Smith' (last) into the table 'names', we would do so using the following command:

INSERT INTO names (first, last) VALUES ('John', 'Smith');
Create Local Mysql Database Mac

You can insert additional data by modifying the VALUES. Because you created the column id as an auto increment, you don't need to specify a value for this field.

To display all the data in the table, simply type:

Because we inserted a few other names into our table, this query produced the following results:

Create a user

It's never a good idea to use the 'root' user in MySQL to interact with your databases. Create a new user and assign it to your database in the MySQL console by issuing the following command:

GRANT ALL PRIVILEGES on uestudio_test.* to 'SomeUser'@'localhost' IDENTIFIED BY 'SomePassword';

Obviously you will want to modify the user name and password in the above to suit your preferences. You should see the console spit out:

Step 5. UEStudio and PHP/MySQL

PHP Support

UEStudio has built in PHP support which allows you to run scripts, check syntax, and more. To configure PHP support, click the Coding tab then open the PHP drop down. Click the last item here to set the location of the PHP executable.

Click the Browse button to browse to and select the folder containing the PHP executable, which in a default Wamp installation would be:

C:wamp64binphpphp7.1.9

Of course this folder path may vary based upon what version of PHP you have installed and running.

After you've set the PHP executable's parent folder, you can use the PHP options in the Coding tab. Simply open a PHP file then click on any of the options from the PHP icon.

UEStudio offers additional options that make developing PHP scripts easier, but before we demonstrate those options we will create a sample PHP script that we can work with.

Learning to code PHP is not within the scope of this power tip, so we won't go into great detail about the script below. This script is simply provided as an example.

The script below will connect to the 'uestudio_test' database running on localhost. This is the database we created earlier. The script connects, then retrieves all the data from the 'names' table and output the results in an HTML table.

If you used different values for the variables, you would need to change them for the script to work according to your environment.

Create and save this script with any file name and a .php extension, like 'mysql_test.php,' in the www directory, which is the same location as the phpinfo.php script from above. If you click on Run script in the PHP drop down, you'll see the output of the script in the output window.

You can try the other commands available in the PHP drop down.

Database

To see the contents of the script in the web browser, type http://localhost/mysql_test.php in your browser. You should see something similar to:

1JohnSmith
2BobWhite
3MikeJones

Create a Project

You don't have to create a project to develop using PHP/MySQL in UEStudio, but there are added benefits to creating a project. If the files you are editing are part of a project, then UEStudio's IntelliTips will parse the PHP script, which provides you a visual representation of classes, functions, etc. in the Classviewer, and provides some context-aware auto-complete when typing source code.

To create a project, click on New project in the Project tab. Name and save the project file. The 'Project settings' dialog will open, and you can add the 'mysql_test.php' to the project by clicking + Active file.

To view the parsed representation of the file, click on the Tags tab at the bottom of the Workspace manager. If the Workspace manager is not open, you can open it by checking Workspace manager in the Layout tab. Using the mysql_test.php file you created in this tutorial, you should see something similar to the below parsed representation:

CreateCreate

By adding your source files to a project, you can also access IntelliTips functionality in the Project tab, like Find symbol definition, Display functions, etc.

For a single file like in this example, there really isn't much advantage for this. However, if you are using an object-oriented style of PHP programming, and your project includes many files, you would see something that looks more like:

As you can see, with a large project, this feature can be very helpful.

Link local to remote

If your local development environment is intended to 'mirror' your live site, you can link a local folder to a remote folder. This will allow you to sync or upload / download files between the local and remote folder in a single click. Learn more about linking local folders to remote folders.