MySQL Shell (mysqlsh) Basics for DBAs

2020-11-30 · mysql, mysqlsh, tools, administration

I resisted MySQL Shell for a long time. The old mysql client did everything I needed, and I didn't want to learn another prompt. Then I tried util.dumpInstance on a database that took hours with mysqldump, and I stopped resisting. mysqlsh speaks SQL, JavaScript and Python, connects over the classic protocol or the X Protocol on port 33060, and ships with utilities the old client never had: parallel dump and load, an upgrade checker, a JSON import tool, and the AdminAPI for InnoDB Cluster and ReplicaSet.

Here's what I'd want a DBA to know in the first week with MySQL Shell 8.0.22: connecting, switching modes, dump and load, and the upgrade checker. It works against MySQL 5.7 and 8.0 servers. I install it from the MySQL repository (yum install mysql-shell on CentOS 7, apt-get install mysql-shell on Ubuntu 18.04 or 20.04 with the MySQL APT repo configured). X Protocol features need port 33060 open and the mysqlx plugin, which is on by default in 8.0.

Connecting and switching modes

mysqlsh dba@db1.internal:3306

The default mode is JavaScript. The prompt shows MySQL db1.internal:3306 ssl JS >. Switch with backslash commands:

\sql
SELECT VERSION();
\js
\py
\help

Or start directly in SQL mode with mysqlsh --sql dba@db1.internal. The \status command shows the connection details, protocol and character set. Passwords can be saved in the system keyring on first connect. I say no on shared machines.

Connecting with --mysqlx or to port 33060 uses the X Protocol, which is needed for the document store features but not for the utilities in this post, so the classic port is fine.

Nicer SQL output

In SQL mode, mysqlsh renders results as tables like the old client, but can switch formats without re-running the query:

mysqlsh --sql --result-format=json dba@db1.internal -e "SELECT user, host FROM mysql.user"
mysqlsh --sql --result-format=vertical dba@db1.internal -e "SHOW SLAVE STATUS"
mysqlsh --sql --result-format=tsv dba@db1.internal -e "SELECT id, email FROM appdb.customers LIMIT 5"

Inside the shell, \option resultFormat=json/raw changes it on the fly. For scripts, mysqlsh --sql -f script.sql behaves like mysql < script.sql. Multi-line statements work as you would expect, and \G vertical output is supported at the end of a statement.

Parallel dumps

The dump utilities arrived in 8.0.21 and they're the main reason I switched. They write compressed, chunked output using several threads:

mysqlsh dba@db1.internal -- util dump-instance /backup/db1-2020-11-30 --threads=8
mysqlsh dba@db1.internal -- util dump-schemas appdb,archive --outputUrl=/backup/appdb-2020-11-30 --threads=4

Or from the JavaScript prompt:

util.dumpInstance("/backup/db1-2020-11-30", {threads: 8, consistent: true, compression: "zstd"})

consistent: true (the default) takes a global read lock briefly and dumps everything at a single point in time using a transaction per thread, so InnoDB data is consistent. The output directory holds a @.json manifest, per-schema DDL files and chunked .tsv.zst data files. On the server, the dump user needs SELECT, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT and, on 8.0, BACKUP_ADMIN for the consistent snapshot.

Loading a dump

util.loadDump("/backup/db1-2020-11-30", {threads: 8, resetProgress: true})

The loader requires local_infile = ON on the target, because it uses LOAD DATA LOCAL INFILE under the hood:

SET GLOBAL local_infile = ON;

Loading is resumable. If the connection drops, run the same command again without resetProgress and it continues from the progress file it keeps in the dump directory. Options worth knowing: skipBinlog: true avoids bloating the binary log on a restore that will not be replicated, deferTableIndexes: "all" builds secondary indexes after the data is in, and loadUsers: true restores accounts from an instance dump (off by default).

To load into MySQL 5.7, note that util.loadDump requires the target to be 5.7 or later and that dumps from 8.0 with utf8mb4_0900_ai_ci collations will fail on 5.7 unless dumped with compatibility: ["strip_...] options or the collation changed.

The upgrade checker

Before moving a 5.7 server to 8.0, I run this against it. It reports removed variables, reserved-word conflicts, orphaned .frm files, obsolete character sets and partitioned tables on non-native engines:

mysqlsh dba@db57.internal -- util check-for-server-upgrade --target-version=8.0.22 --config-path=/etc/my.cnf

Each finding is marked Notice, Warning or Error. Errors block the upgrade; fix them and re-run until the report ends with No fatal errors were found that would prevent an upgrade.

Sanity checks

Confirm the shell version and a round trip through both protocols:

mysqlsh --version
mysqlsh --sql dba@db1.internal:3306 -e "SELECT 'classic' AS proto, @@port"
mysqlsh --sql dba@db1.internal:33060 -e "SELECT 'x' AS proto, @@mysqlx_port"

After a dump and load, compare table checksums between source and target for a couple of large tables:

CHECKSUM TABLE appdb.orders, appdb.customers;

And inspect the dump metadata to confirm what was captured:

cat /backup/db1-2020-11-30/@.json | head -30
ls /backup/db1-2020-11-30/appdb@orders* | wc -l

Errors I've seen

MySQL Error 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides on loadDump. Set local_infile = ON on the target server as shown above; the shell enables it on the client side automatically.

Util.dumpInstance: Unable to lock tables: MySQL Error 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s). Grant RELOAD (and BACKUP_ADMIN on 8.0) to the dump user, or pass consistent: false and accept a non-consistent dump.

Util.loadDump: Argument #2: Invalid options: threads or similar. The option names are camelCase and case-sensitive on the command line too (--threads works, --Threads does not); check \? util.loadDump for the exact list.

ERROR: Not connected when running a utility. The -- command-line syntax needs the connection URI before --; mysqlsh -- util dump-instance ... without a URI has nothing to dump.

Shell falls back to SSL connection error against an old 5.7 server with self-signed certificates. Add --ssl-mode=REQUIRED or, for an internal network you trust, --ssl-mode=DISABLED.

For anything larger than a few gigabytes I reach for util.dumpInstance now, not mysqldump. The upgrade checker is a bonus that has saved me an afternoon per 5.7 to 8.0 migration.