MySQL Client

If you only need to connect to a MySQL database, you can use the lighter weight MySQL client.

Table of contents
  1. MySQL Client
    1. Installation
      1. Adding to PATH
    2. Cloning a database
      1. Export query to recreate empty database

Installation

Using Homebrew:

brew install mysql-client

Adding to PATH

You will notice that the client is not immediately available in your terminal:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

==> Caveats
==> mysql-client
mysql-client is keg-only, which means it was not symlinked into /opt/homebrew,
because it conflicts with mysql (which contains client libraries).

If you need to have mysql-client first in your PATH, run:
  echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc

For compilers to find mysql-client you may need to set:
  export LDFLAGS="-L/opt/homebrew/opt/mysql-client/lib"
  export CPPFLAGS="-I/opt/homebrew/opt/mysql-client/include"

For pkg-config to find mysql-client you may need to set:
  export PKG_CONFIG_PATH="/opt/homebrew/opt/mysql-client/lib/pkgconfig"

You can follow the instruction on line number 7 to add the client binaries to PATH.

Doing so will create a conflict with the MySQL server binaries if you happen to install them later on. If you do, you will need to remove the client binaries from PATH.

Or you could just create an alias to some of the binaries you may need, for e.g.:

alias mysqldump="/opt/homebrew/opt/mysql-client/bin/mysqldump"

I personally prefer the latter.

Cloning a database

Once you’ve downloaded the client, you can use mysqldump to selectively clone a database.

Refer to mysqldump --help for a comprehensive list.

Export query to recreate empty database

To export queries to recreate schemas, views, etc. use the --no-data flag:

mysqldump \
  --host 127.0.0.1 \
  --user root \
  --port 3306 \
  --databases mydb \
  --password --no-data --routines --single-transaction \
  > schema.sql
# OR
mysqldump \
  -h 127.0.0.1 \
  -u root \
  -P 3306 \
  -B mydb \
  -p -d -R --single-transaction \
  > schema.sql

--single-transaction can be used to avoid locking the database while performing the dump or if you do not have the LOCK TABLES privilege.

Use 127.0.0.1 instead of localhost if you want to enforce connection via TCP/IP instead of sockets.

MySQL 8.0.32’s mysqldump currently has an issue with AWS RDS when using the --single-transaction flag. See this issue for more details.