Install MySQL using Homebrew - rajivkanaujia/alphaworks GitHub Wiki
Install MySQL using Homebrew
If you have previous MySQL installation on your Mac, please uninstall it. See Uninstalling MySQL on MacOS
Install base MySQL using Homebrew
Run following command on Terminal to update Homebrew and install MySQL
$ brew update
$ brew doctor
$ brew upgrade
$ brew install mysql
You will see message like following that indicates that the installation is not secure. We will secure the installation later as we go through the process.
==> Caveats
We\'ve installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
Find where to place my.cnf file
Open two Terminals
Terminal 1: use following command
$ mysql.server stop
$ sudo fs_usage | grep my.cnf
Terminal 2: use command
$ mysql.server restart
Find the potential location where my.cnf needs to be added. Three potential areas will be
/usr/local/etc/my.cnf --> Global Access
/usr/local/Cellar/mysql/5.7.18/my.cnf --> Sever Level Access
~/.my.cnf --> User Level Access
In my case the cnf was found at /usr/local/etc/my.cnf Now you have the location of my.cnf. Restart MySQL
$ mysql.server restart
Secure your installation
Secure your installation. Select good password and select Y for all the options. Keep the password safe. You will need it soon in the following steps. Execute the command below and select Y|y to all.
$ mysql_secure_installation
Locate MySQL Install Directory
Run command below to find out where the key MySQL directories are
$ ps auxww|grep [m]ysqld
Information like following can be obtained
demon location - /usr/local/Cellar/mysql/5.7.18_1/bin/mysqld
basedir=/usr/local/Cellar/mysql/5.7.18_1
datadir=/usr/local/var/mysql
plugin-dir=/usr/local/Cellar/mysql/5.7.18_1/lib/plugin
log-error=/usr/local/var/mysql/Rajivs-MacBook-Air.local.err
datadir=/usr/local/var/mysql
For information on various log files, you can use the MySQL demon command like following
$ /usr/local/Cellar/mysql/5.7.18_1/bin/mysqld --verbose --help | grep '^log'
log-bin No default value)
log-bin-index (No default value)
log-bin-trust-function-creators FALSE
log-bin-use-v1-row-events FALSE
log-builtin-as-identified-by-password FALSE
log-error stderr
log-error-verbosity 1
log-isam myisam.log
log-output FILE
log-queries-not-using-indexes FALSE
log-raw FALSE
log-short-format FALSE
log-slave-updates FALSE
log-slow-admin-statements FALSE
log-slow-slave-statements FALSE
log-statements-unsafe-for-binlog TRUE
log-syslog FALSE
log-syslog-facility daemon
log-syslog-include-pid TRUE
log-syslog-tag
log-tc tc.log
log-tc-size 24576
log-throttle-queries-not-using-indexes 0
log-timestamps UTC
log-warnings 0
Running MySQL as local user
$ mysql.server stop
Unset the temporary directory
$ echo $TMPDIR
$ unset TMPDIR
$ echo $TMPDIR
After you have run the command below, make sure that the folders in the command and whoami resolves into the right name
$ whoami
Execute command mentioned below. Validate the output in "echo" command before executing the actual command.
$ echo mysqld -initialize --verbose --user=$(whoami) --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
$ mysqld -initialize --verbose --user=$(whoami) --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
FYI - One of the older variant of the command is
$ echo mysql_install_db --verbose --user=$(whoami) --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
$ mysql_install_db --verbose --user=$(whoami) --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
Running MySQL as local user (Continued)
Restart MySQL and run following commands to further secure the database
$ mysql.server restart
$ mysql -u root -p
Supply the password that was used to secure MySQL when "mysql_secure_installation" command was run. No users without 'a password or a host association' should exist in MySQL
mysql> SELECT User, Host, authentication_string FROM mysql.user;
+-----------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | *7DCA42D47EF99B968C2A25EBB4216AB12D5E8D2C |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.01 sec)
To update password, use command like
mysql> update user set authentication_string=password('yournewpassword') where user='root';
mysql> flush privileges;
Running MySQL as local user (Continued)
Change the 'root' user
mysql> rename user 'root'@'localhost' to 'newAdminUser'@'localhost';
mysql> rename user 'root'@'localhost' to 'rajiv'@'localhost';
Run the command below to see the change
mysql> SELECT User, Host, authentication_string FROM mysql.user;
mysql> flush privileges;
mysql> exit
Reconfirm access.
$ mysql -u rajiv -p
Make changes to my.cnf
Stop the server
$ mysql.server stop
Make the changes to my.cnf as mentioned below -
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
local-infile=0
Install a sample database
Sakila is one of the sample database Visit https://dev.mysql.com/doc/sakila/en/sakila-installation.html Download the zip file Create Schema
mysql> source ~/Downloads/sakila-db/sakila-schema.sql;
Populate the database
mysql> source ~/Downloads/sakila-db/sakila-data.sql;
Select the database and see the tables
mysql> use sakila;
mysql> show tables;
mysql> select count(*) from film;
Some interesting commands in MySQL command line
mysql> show database;
mysql> use databasename;
mysql> show tables;
Add right permissions to the PIDs
Check the permission at the var/myaql folder
$ ls -laF /usr/local/var/mysql/
If it is owner by root you should change it mysql or your_user
$ sudo chown -R mysql /usr/local/var/mysql/
Creating a read-only user
Assuming you have setup the Sakila database. This section will let you create a guest users and grant it privileges to Sakila db.
mysql> SELECT User, Host, authentication_string FROM mysql.user;
+-----------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+-----------+-----------+-------------------------------------------+
| rajiv | localhost | *7DCA42D47EF99B968C2A25EBB4216AB12D5E8D2C |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
Create a guest user
mysql> create user guest identified by '******';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User, Host, authentication_string FROM mysql.user;
+-----------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+-----------+-----------+-------------------------------------------+
| rajiv | localhost | *7DCA42D47EF99B968C2A25EBB4216AB12D5E8D2C |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| guest | localhost | *E2C75AE6AE73E1A6F55017B962183AFAD2CFD248 |
+-----------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> grant select on sakila.* to 'guest'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> quit;
Login back as guest
$ mysql -u guest -p
Test access
Try to do an operation that should fail due to read only privilege
mysql> desc rental;
mysql> INSERT INTO rental(rental_date, inventory_id, customer_id, staff_id)
-> VALUES(NOW(), 10, 3, 1);
ERROR 1142 (42000): INSERT command denied to user 'guest'@'localhost' for table 'rental'
Install MySQL WorkBench
$ brew cask install mysqlworkbench
==> Downloading https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-6.3.9-osx-x86_64.dmg
######################################################################## 10.0%
==> Verifying checksum for Cask mysqlworkbench
==> Installing Cask mysqlworkbench
==> Moving App 'MySQLWorkbench.app' to '/Applications/MySQLWorkbench.app'.
🍺 mysqlworkbench was successfully installed!
Sample my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with
# ticks/quotes escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing
# the socket location.
[client]
port = 3306
#socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions
# are currently parsed.
[mysqld_safe]
#socket = /var/run/mysqld/mysqld.sock
#nice = 0
[mysqld]
#
# * Basic Settings
#
#
# * IMPORTANT
# If you make changes to these settings and your system uses
# apparmor, you may also need to also adjust
# /etc/apparmor.d/usr.sbin.mysqld.
#
#user = mysql
#socket = /var/run/mysqld/mysqld.sock
port = 3306
#basedir = /usr
#datadir = /var/lib/mysql
#tmpdir = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
log_error = /var/log/mysql/error.log
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or
# for replication.
# note: if you are setting up a replication slave, see
# README.Debian about other settings you may need
# to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
# Query Caching
query-cache-type = 1
# Default to InnoDB
default-storage-engine=innodb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
Some Troubleshooting Hints
Finding a table under sys database
Login to MySQL
$ mysql -u rajiv -p
Run following commands
mysql> show databases;
mysql> use sys;
mysql> show tables;
mysql> show tables like "%user%";
MySQL PID issues
ERROR! The server quit without updating PID file
ERROR! MySQL server PID file could not be found!
Find the basedir of MySQL (see above installation sections). Run command
$ /usr/local/Cellar/mysql/5.7.18_1/support-files/mysql.server restart
If the above fails, then Remove my.cnf or just back it up for now and restart
$ sudo mv /usr/local/etc/my.cnf /usr/local/etc/my.cnf.bak
Remove err files
$ rm /usr/local/var/mysql/*.err
How to tail error file
Run command like one mentioned below. If you see error message, then use the -p option.
$ echo sudo tail -f $(mysql -Nse "SELECT @@log_error")
ERROR 1045 (28000): Access denied for user 'rajiv'@'localhost' (using password: NO)
$ echo sudo tail -f $(mysql -p -Nse "SELECT @@log_error")
Enter password:
The actual command that worked on my installation was:
$ sudo tail -f $(mysql -p -Nse "SELECT @@log_error")
or
$ sudo tail -f /usr/local/var/mysql/Rajivs-MacBook-Air.local.err
Check the databases
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
Check Version
mysql> show variables like "%version%";
+-------------------------+-----------------------+
| Variable_name | Value |
+-------------------------+-----------------------+
| innodb_version | 5.7.18 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.18 |
| version_comment | Homebrew |
| version_compile_machine | x86_64 |
| version_compile_os | osx10.12 |
+-------------------------+-----------------------+
Note: If you like the instructions here, please refer it on your posts/documentation. Contact me if there are corrections needed.