Install mySQL on macOS

macOS might be the easiest platform to install MySQL on. To do so, simply download the MySQL installation package from the MySQL Download site. I like to use the third link (the DMG).

Screen Shot 2016-02-13 at 10.26.11 PM

Once downloaded, run the package. The package will ask you a few questions and you can easily just select the default choice during the installation process.

Screen Shot 2016-02-13 at 10.26.04 PM

Once installed, you’ll be prompted that a temporary password has been used for your MySQL instance.
Screen Shot 2016-02-13 at 10.26.52 PM

The password will get you in the first time, so you can change it. Once you have documented the password, open System Preferences and click on MySQL in the bottom row of System Preference Panes.

Screen Shot 2016-02-13 at 10.27.34 PM

Click Start MySQL Server and then when prompted, authenticate to the system. If you’d like to do this programmatically and don’t need the System Preference pane, you can do so with homebrew. If you have homebrew installed, simply run the brew command with the install verb and mysql as the package:

brew install mysql

Whichever way you install SQL, once installed, you’ll want to set the root password to something other than the intuitionally difficult to remember password provided at install time. To do so, first connect to the mysql instance now running on your computer. As the tools are installed in /usr/local/mysql/bin, run the following:

/usr/local/mysql/bin/mysql -u root

Then, set the password using the ALTER statement along with the USER option and then the username followed by IDENTIFIED BY and ultimately the password, as follows:

ALTER USER 'root'@'localhost' IDENTIFIED BY

Once done, you’ll then be able to connect to mysql normally.

Show MySQL Settings

MySQL usually pulls settings from a my.cnf file. However, you can end up with settings in include files, which can be defined in the my.cnf using the following directives: include /home/mydir/myopt.cnf includedir /home/mydir Because of this, and the fact that you might not have access to all locations of .cnf files on a filesystem, you can also grab them using the SHOW VARIABLES option within SQL, obtained by /usr/local/mysql/bin/mysql -uroot -p mypassword -e "SHOW VARIABLES;" > /tmp/SQLSettings.txt In the above command, -uroot defines we’ll be accessing with the root user, -p defines the password (listed as mypassword) and the -e defines that we want to execute a command and then quit. We then use > to dump the output into the defined file.

mysql command switches

Mysql command options: –auto-rehash Enable or disable automatic rehashing –auto-vertical-output Automatically display output of commands vertically rather than horizontally –batch Do not write to the history file –binary I use this when I have large results timing out due to blobs being in there –bind-address Specify a network interface when connecting to the server –character-sets-dir Indicate a custom directory where character sets are installed –column-names Output column names when running queries –column-type-info Show metadata –comments Enable or disable commenting when sending statements to the server –compress Compresses data sent to/from client and server –connect_timeout Seconds of inactivity before a timeout –database The database to connect to –debug Write debugging log; supported only if MySQL was built with debugging support –debug-check Print debugging information when program exits –debug-info Print debugging information, memory, and CPU statistics when program exits –default-auth Authentication plugin to use –default-character-set Specify default character set –defaults-extra-file Read named option file in addition to usual option files –defaults-file Read only named option file –defaults-group-suffix Option group suffix value –delimiter Set the statement delimiter –enable-cleartext-plugin Enable cleartext authentication plugin –execute Execute the statement and quit –force Continue even if an SQL error occurs –help Display help message and exit –histignore Patterns specifying which statements to ignore for logging –host Connect to MySQL server on given host –html Produce HTML output –ignore-spaces Ignore spaces after function names –init-command SQL statement to execute after connecting –line-numbers Write line numbers for errors –local-infile Enable or disable for LOCAL capability for LOAD DATA INFILE –login-path Read login path options from .mylogin.cnf –max_allowed_packet Maximum packet length to send to or receive from server –max_join_size The automatic limit for rows in a join when using –safe-updates –named-commands Enable named mysql commands –net_buffer_length Buffer size for TCP/IP and socket communication –no-auto-rehash Disable automatic rehashing –no-beep Do not beep when errors occur –no-defaults Read no option files –one-database Ignore statements except those for the default database named on the command line –pager Use the given command for paging query output –password Password to use when connecting to server –pipe On Windows, connect to server using named pipe –plugin-dir Directory where plugins are installed –port TCP/IP port number to use for connection –print-defaults Print default options –prompt Set the prompt to the specified format –protocol Connection protocol to use –quick Do not cache each query result –raw Write column values without escape conversion –reconnect If the connection to the server is lost, automatically try to reconnect –safe-updates Only process UPDATE and DELETE statements when they have values –secure-auth Do not send passwords to server in old (pre-4.1) format 5.7.5 –select_limit The automatic limit for SELECT statements when using –safe-updates –server-public-key-path Path name to file containing RSA public key –shared-memory-base-name The name of shared memory to use for shared-memory connections –show-warnings Show warnings after each statement if there are any –sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C) –silent Run silently (necessary when daemonizing) –skip-auto-rehash Disable automatic rehashing –skip-column-names Do not write column names in results –skip-line-numbers Skip line numbers for errors –skip-named-commands Disable named mysql commands –skip-pager Disable paging –skip-reconnect Disable reconnecting –socket For connections to localhost, the Unix socket file to use –ssl Enable secure connection –ssl-ca Path of file that contains list of trusted SSL CAs –ssl-capath Path of directory that contains trusted SSL CA certificates in PEM format –ssl-cert Path of file that contains X509 certificate in PEM format –ssl-cipher List of permitted ciphers to use for connection encryption –ssl-crl Path of file that contains certificate revocation lists –ssl-crlpath Path of directory that contains certificate revocation list files –ssl-key Path of file that contains X509 key in PEM format –ssl-mode Security state of connection to server 5.7.11 –ssl-verify-server-cert Verify server certificate Common Name value against host name used when connecting to server –syslog Log interactive statements to syslog 5.7.1 –table Display output in tabular format –tee Append a copy of output to named file –tls-version TLS versions Protocols permitted for secure connections –unbuffered Constantly flush buffers –user The user name to use when connecting to server (SQL users are not the same as users on an OS) –verbose As with most other commands, output verbosely –version Show the MySQL version number –vertical Show one line per column in query outputs –wait Retry when a connection fails –xml Output data into an XML format

Use the Slow Query Log in MySQL

Slow queries can cause poor database performance. MySQL has a slow query log, which lets you log queries that take too long (with too long a user-configurable setting). This allows you to quickly find craptastic queries in your logs. To enable the slow query log, use SET GLOBAL and then set the slow_query_log option to ON: SET GLOBAL slow_query_log = 'ON'; By default, the slow query log identifies any query that takes 10 seconds or longer. To change long_query_time using SET GLOBAL. Here, we’ll set it to 7 seconds: SET GLOBAL long_query_time = 7; You can also set the log file to something other than the default location of /var/lib/mysql/hostname-slow.log. Again, use SET GLOBAL and then provide a path for slow_query_log_file: SET GLOBAL slow_query_log_file = '/var/logs/slowquery.log'; To disable the slow query log: SET GLOBAL slow_query_log = 'OFF';

Index Those SQL Tables

If you have growing sets of data, one of the best ways to speed up database performance is to make sure each column in larger tables is indexed. You can easily index a column following this syntax (using the name of your table in the place of tablename and the name of your column in the place of columnname): ALTER TABLE tablename ADD INDEX (columnname); So if you have a table called Customers and the following columns (as in the case of my Customers database from the previous exercises): ID Site Contact Address City Zip Country You would index them all as follows: ALTER TABLE Customers ADD INDEX (ID); ALTER TABLE Customers ADD INDEX (Site); ALTER TABLE Customers ADD INDEX (Contact); ALTER TABLE Customers ADD INDEX (Address); ALTER TABLE Customers ADD INDEX (City); ALTER TABLE Customers ADD INDEX (Zip); ALTER TABLE Customers ADD INDEX (Country);

SQL and Date Functions

SQL has some really great features for managing dates. Given that dates are a very common thing to store in databases, that’s a pretty logical thing to get to a good, mature point. But the most challenging aspect of working with date and time in SQL is formatting. There are lots of different ways to format dates around the world, and even different structures (e.g. epoch) that are often complicated by adding time to records. But, you need to do things in a consistent fashion with how SQL expects them, if you’ll be using built in functions to manage dates. Dates can be stored in a variety of formats, which include the following:
  • DATE: Appears as YYYY-MM-DD
  • YEAR: Appears as YY or YYYY
What are those functions?
  • CONVERT: Shows the date/time data in the specified format
  • CURATE: Shows the current date
  • CURTIME: Shows the current time
  • DATE: Extracts the date from a date/time field
  • DATE_ADD: Adds the time to a date
  • DATE_SUB: Removes the time from a date
  • DATE_FORMAT: Shows the date and time data in different formats
  • DATEDIFF: Shows the days between two specified dates
  • EXTRACT: Shows part of a date and time formatted value
  • NOW: Shows the current date and time, frequently used to capture the date during signups, etc
In this article, we’ll use the same “Customers” table from our first articles, but we’ll add a column for signupdate: ID Site Contact Address City Zip Country SignupDate 1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01 2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05 3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01 Let’s show (SELECT) records with an SignupDate of 2005-01-01 from the table above. SELECT * FROM Customers WHERE OrderDate='2005-01-01' Overall, dates are pretty easy. Times are a little more challenging, and take up much more space in a database. But overall, provided you build the column with the right format you want to use, the functions make managing date tasks all the easier. I’m not going to cover all of the options, but let’s look at EXTRACT real quick. Here, we’ll serialize the year, month, and day from the previous select. To do so, we’ll use the SELECT statement, call the EXTRACT function, and then split the three parts of the date into variables we’re making up (so you can use your own strings for the names), as SignupYear, SignupMonth, and SignupDay: SELECT EXTRACT(YEAR FROM SignupDate) AS SignupYear, EXTRACT(MONTH FROM SignupDate) AS SignupMonth, EXTRACT(DAY FROM SignupDate) AS SignupDay, FROM Customers WHERE OrderDate='2005-01-01' Obviously, we knew the OrderDate, but we could have used any other column and pattern to match for that column in the WHERE to serialize date information based on other parameters. Fun stuff.

Allow Remote Connections To MySQL

By default, MySQL allows other services on the computer you’re running the daemon to connect to the database and denies any connections from hosts outside that computer. However, it’s pretty easy to provide access to the database from another host (for example, if you’re splitting up the back-end and front-end of a site, clustering, etc. To get started, you’ll edit your my.cnf file and find the [mysqld] section of the file. Then, locate the bind-address, which you will need to set as the IP of your server and comment out the line for skip-networking. Let’s say we’re going to open access for The section would look similar to the following: [mysqld] user = mysql pid-file = /var/run/mysqld/ socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/English bind-address = # skip-networking Then restart MySQL and a listener should be running on the system. You can connect using the mysql command, with the -u option to define a user (root) and then the -h to define an IP (in this case mysql -u root –h –p You can also use the telnet command to attempt a connection into a given port, which in the MySQL case would be 3306: telnet 3306 Now, just because you can connect remotely doesnt necessarily mean that another computer can actually get into any databases. Next, we’ll GRANT access to ALL resources for a user called krypted for all tables on a new database, coming from an IP of GRANT ALL ON *.* TO krypted@'' IDENTIFIED BY 'mysupersecretpassword'; Or for an existing database called mydatabase, using the same IP and account as before: UPDATE DB set Host='' where Db='mydatabase'; UPDATE USER set Host='' where user='krypted'; You’ll also need to open up port 3306 coming in, whether that’s using a firewall or opening traffic in from the internets. Once that’s done, you should be able to connect and use the database as needed.


No, SQL top doesn’t look at the status of a database the way the TOP command in bash does. It looks at the top number of records returned in a query, so a bit more like head. This makes SELECT TOP useful with larger tables where this will be millions of records getting loaded into memory during a query. In this article, we’ll use the same “Customers” table from our first articles to test out TOP: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US The following SELECT TOP statement shows the top 20% of the records from “Customers”: SELECT TOP 20 PERCENT * FROM Customers; Or just SELECT TOP the first three records: SELECT TOP 3 * FROM Customers;  

Reset A Lost MySQL Password

The first step to reset a password is to stop the MySQL daemon. This will cause mysqld to accept no new connections and terminate existing connections. But this can all be done in a matter of seconds, usually. To stop MySQL on Mac, use the System Preference pane or launchctl. To stop on Linux, use init.d: sudo /etc/init.d/mysql stop Or if it’s mysqld instead: sudo /etc/init.d/mysqld stop Then start the SQL daemon using the –skip-grant-tables option: sudo mysqld_safe --skip-grant-tables & Next, login to mysql, which won’t require a password running in this mode: mysql -u root And use the UPDATE USER statement to set a new password: UPDATE USER set password=PASSWORD("mysecretpassword") WHERE USER='root'; Then flush the privileges: flush privileges; Viola, start things back up normally and you’re off to the races.