Welcome to this detailed tutorial, where I'll guide you through the process of installing and configuring MySQL on your Mac with Apple Silicon, whether it's an M1, M2, or the latest M3 chip.
The official MySQL documentation points you towards using the Native Package, which can be a bit complex, especially if you're new to Mac.
Based on my experience, I suggest we bypass that route and go with a smoother, more user-friendly approach.
We'll utilize the wonderful Homebrew package manager, which simplifies everything from installing MySQL to future upgrades and database migrations.
Ready to dive in? Let's get started.
Step 1: Install Homebrew
Homebrew is a great package manager that makes it easy to install and uninstall software (especially for open-source tools)
Visit https://brew.sh/ to follow the step-by-step instructions, or copy and paste the following command into your terminal (iTerm 2 you just installed).
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
Step 2: Install the latest MySQL server
brew install mysqlHomebrew will always search for the latest version of a formula; as of Nov 7, 2023, the latest MySQL version is 8.1.0
After a few seconds, you should see a success and instructions message.
leon $ brew install mysql
==> Downloading https://ghcr.io/v2/homebrew/core/mysql/manifests/8.0.31
Already downloaded: /Users/leon/Library/Caches/Homebrew/downloads/eeb7708d75e293e371d52ef35b6688a9ccb547d1c84c5bfd48d285550250c638--mysql-8.0.31.bottle_manifest.json
==> Downloading https://ghcr.io/v2/homebrew/core/mysql/manifests/8.0.31
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/mysql/blobs/sha256:73a817585b4aa253b0984698b6e2d2fde197332d3de144855d4489969f94fb9f
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:73a817585b4aa253b0984698b6e2d2fde197332d3de144855d4489969f94fb9f?se=2022-
######################################################################## 100.0%
==> Pouring mysql--8.0.31.arm64_ventura.bottle.tar.gz
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -u root
To restart mysql after an upgrade:
brew services restart mysql
Or, if you don't want/need a background service you can just run:
/opt/homebrew/opt/mysql/bin/mysqld_safe --datadir=/opt/homebrew/var/mysql
Step 3: Turn on the MySQL server automatically.
brew services start mysqlThe MySQL server will automatically run in the background when you restart your Mac.
Step 4: sign in and play with the newly installed MySQL server
On your command line, type
mysql -u rootmysql is a command-line utility that was installed together with the MySQL server, -u specifies which user to login. By default, you, as the admin of your machine, can log in as a 'root' user.
You should see something like the following:
leon $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.31 Homebrew Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
You can run some basic commands to find existing default databases and tables
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
37 rows in set (0.00 sec)
And Type \q to exit the database console.
To switch to a different database, type use <other_db_name>
Step 5: update my.cnf to improve MySQL performance
By default, your newly installed MySQL server is using default settings, but if you prefer a fast database, you can fine-tune it and change the parameters.
First, we need to locate the file in your command line, and type the following in your terminal.
mysql --verbose --help | grep my.cnf
You will see something like the following:
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnfYou can search MySQL fine tuning on google and read more details:
For example:
- https://haydenjames.io/mysql-8-sample-config-tuning/
- https://github.com/Releem/awesome-mysql-performance
- https://severalnines.com/blog/mysql-performance-cheat-sheet/
After that, you can update the config file.
Open the config file (it may require sudo privelege):
sudo vim /etc/my.cnf
Paste the following into the empty file:
[mysqld]
disable-log-bin = 1
skip-name-resolve = 1
performance-schema = 0
local-infile = 0
mysqlx = 0
bind-address = [IPs removed]
default-storage-engine = InnoDB
open_files_limit = 200000
max_allowed_packet = 256M
sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
innodb_dedicated_server = 1
innodb_buffer_pool_instances = 48
innodb_log_buffer_size = 64M
innodb_read_io_threads = 12
innodb_write_io_threads = 12
innodb_stats_on_metadata = 0
innodb_file_per_table = 1
max_connections = 500
thread_cache_size = 128
table_definition_cache = 65536
table_open_cache = 65536
wait_timeout = 10
connect_timeout = 5
interactive_timeout = 30
tmp_table_size = 128M
max_heap_table_size = 128M
read_buffer_size = 256K
join_buffer_size = 1M
sort_buffer_size = 512K
read_rnd_buffer_size = 512K
slow-query-log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql_slow_query.log
log-error = /var/log/mysql/db.[removed].com.errSource: https://haydenjames.io/mysql-8-sample-config-tuning/
Finally, we need to save the file and restart the MySQL server:
brew services restart mysql
And the following success message:
Stopping `mysql`... (might take a while) ==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql) ==> Successfully started `mysql` (label: homebrew.mxcl.mysql)
Since many variables impact the performance of your MySQL server and explaining those factors is beyond the scope of this article, if you are interested to know more, please feel free to read the following articles:
https://severalnines.com/blog/mysql-performance-cheat-sheet/
Congratulations, now you have a well-performed MySQL server on your Mac.
Eager to experiment with MySQL without installing on your machine? Dip your toes into SQLPad's MySQL Playground, where you can play with real data, completely free of charge.
