Skip to content

Instantly share code, notes, and snippets.

@Foadsf
Forked from nrollr/MySQL_macOS_Sierra.md
Last active January 15, 2024 22:15
Show Gist options
  • Save Foadsf/b351fe7686de19a4c91d3e0b4c91080a to your computer and use it in GitHub Desktop.
Save Foadsf/b351fe7686de19a4c91d3e0b4c91080a to your computer and use it in GitHub Desktop.
Installing MySQL and MySQL Workbench on macOS using Homebrew

Preface

This procedure explains how to install MySQL and MySQL Workbench using Homebrew on macOS. This is a fork of these instructions, to make them more concise and solve the issues reported here.

MySQL

To install MySQL enter :

  • brew install mysql
  • brew services start mysql
  • then run mysql_secure_installation and follow the instructions

MySQL Workbench

install via HomeBrew Cask: brew cask install mysqlworkbench

phpMyAdmin

  • install brew install phpmyadmin
  • edit /private/etc/apache2/httpd.conf file as sudo
  • add below code block at the end:
Alias /phpmyadmin /usr/local/share/phpmyadmin
    <Directory /usr/local/share/phpmyadmin/>
        Options Indexes FollowSymLinks MultiViews
        AllowOverride All
        <IfModule mod_authz_core.c>
            Require all granted
        </IfModule>
        <IfModule !mod_authz_core.c>
            Order allow,deny
            Allow from all
        </IfModule>
    </Directory>


LoadModule php7_module /usr/local/opt/php/lib/httpd/modules/libphp7.so

    <FilesMatch \.php$>
        SetHandler application/x-httpd-php
    </FilesMatch>

and make sure you have the line:

DirectoryIndex index.html index.php home.pl index.cgi
  • then sudo apachectl restart

Visual Studio Code connection:

  • Install MySQL plugin
  • run mysql - u <user> -p to enter mysql
  • run ALTER USER '<user>'@'<server>' IDENTIFIED WITH mysql_native_password BY '<password>';
  • if your password is not accepted:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

  • run SHOW VARIABLES LIKE 'validate_password%'; to see the password policies
  • run SET GLOBAL <Variable_name> = <New_Value>; to change

Python:

  • install pip3 install mysql-connector
  • run the blow code to test your Python connection:
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="<user>",
    passwd="<password>",
    auth_plugin='mysql_native_password',
)

print(mydb)

and using Pandas to run and fetch the results of MySQL queries:

df = pd.read_sql_query("SELECT * FROM <table>", mydb)
df.head()

Jupyter:

  • Install pip3 install ipython-sql
  • pip3 install mysqlclient
  • now you should be able to run these cells and get pretty-printed HTML output:
# %%
%load_ext sql

# %%
%sql mysql+mysqldb://<user>:<password>@localhost/<dataBase>

# %%
%%sql

SELECT *
FROM <table>;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment