Personal blog of Yzmir Ramirez

Group replication is a fault-tolerant/highly available replication topology that ensures if the primary node goes down, one of the other candidates or secondary members takes over so write and read operations can continue without any interruptions. However, there are some scenarios where, due to outages, network partitions, or database crashes, the group membership could be broken, or we end […]

MySQL Shell is an advanced client and code editor for MySQL. In addition to the provided SQL functionality, similar to MySQL, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. The X DevAPI enables you to work with both relational and document data, and MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7.

MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode. These are the utilities to take a backup; let’s see some basic commands.

• util.dumpTables – Dump one or more tables from single database
• util.dumpSchemas – Dump one or more databases
• util.dumpInstance – Dump full instance
• util.loadDump – Restore dump

1. Single table dump

The below command is to take a dump of the table sbtest1 from the sysbench database and store the backup on the destination directory sysbench_dumps. The utility will create the directory when the destination directory does not exist. By default, compression, and chunking are enabled. When chunking is enabled, the table dump will be spitted onto multiple files based on size. Dialect:”csv gives the extension of the dump file, and by default, the file will be created with the tsv (Table separated value) extension.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dialect:"csv"})
Acquiring global read lock
Global read lock acquired
Initializing - done
1 tables and 0 views will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Running data dump using 6 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
101% (1000.00K rows / ~986.40K rows), 317.96K rows/s, 63.91 MB/s
Dump duration: 00:00:03s
Total duration: 00:00:04s
Schemas dumped: 1
Tables dumped: 1
Data size: 198.89 MB
Rows written: 1000000
Bytes written: 198.89 MB
Average throughput: 60.96 MB/s
MySQL localhost JS >

These are the files created for the above dump command.

[root@centos12 sysbench_dumps]# ls -lrth
total 190M
-rw-r-----. 1 root root 869 Jun 21 13:08 @.json
-rw-r-----. 1 root root 240 Jun 21 13:08 @.sql
-rw-r-----. 1 root root 240 Jun 21 13:08 @.post.sql
-rw-r-----. 1 root root 231 Jun 21 13:08 sysbench.json
-rw-r-----. 1 root root 638 Jun 21 13:08 sysbench@sbtest1.json
-rw-r-----. 1 root root 474 Jun 21 13:08 sysbench.sql
-rw-r-----. 1 root root 789 Jun 21 13:08 sysbench@sbtest1.sql
-rw-r-----. 1 root root 1.5K Jun 21 13:08 sysbench@sbtest1.csv.idx
-rw-r-----. 1 root root 190M Jun 21 13:08 sysbench@sbtest1.csv
-rw-r-----. 1 root root 233 Jun 21 13:08 @.done.json
[root@centos12 sysbench_dumps]# pwd
/home/vagrant/sysbench_dumps

@.json	Complete information about dump options, servername, and username used for the dump and binlog file and position, etc.
@.sql, @.post.sql.	Shows server version and dump version details.
sysbench.json	Database and table details involved in the dump.
sysbench@sbtest1.json	Details about the table sbtest1, including column names, indexes, triggers, characterset, and partitions.
sysbench.sql	Create a statement for the database sysbench.
sysbench@sbtest1.sql	Create a statement for the table sbtest1.
@.done.json	End time of the dump and dump file size.
sysbench@sbtest1.csv	Table dump file.

 

2. Backup only table structure

Option ddlOnly:true is used to take only the table structures. The below command is to take the table structure of sbtest1 from the sysbench database and store it in the sysbench_dumps path.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, ddlOnly:true})

3. Dump only table data

Option dataOnly:true to take the dump of only data. The below command is to take table data of sbtest1 from the sysbench database and store it in the sysbench_dumps path.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true})

4. Dump only selected data

This “where”: {“databasename.tablename”: “condition”} option is used to take a dump of selected data. The below command is to take a dump of table sbtest1 from id 1 to 10.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6 ,chunking:false, dataOnly:true, "where" : {"sysbench.sbtest1": "id between 1 and 10"}})

It’s also possible to take a dump of multiple tables with their conditions in a single command.

Syntax:

"where" : {"databasename1.tablename1": "condition for databasename1.tablename1", "databasename2.tablename2": "condition for databasename2.tablename2"}

The below command is to take a dump of table sbtest1 from id 1 to 10 and dump of sbtest2 from id 100 to 110.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1", "sbtest2"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true, "where" : {"sysbench.sbtest1": "id between 1 and 10", "sysbench.sbtest2": "id between 100 and 110"}})

5. Dump data from partitions

The option partitions is to take a dump from selected partitions.

Syntax:

"partitions" : {"db1.table1": ["list of partitions"],"db2.table1": ["list of partitions"]}

The below command is to take a dump from only partitions p1 and p2 and dump of sbtest2 table from partitions p4 and p5.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1", "sbtest2"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true, "partitions" : {"sysbench.sbtest1": ["p1", "p2"],"sysbench.sbtest2": ["p4", "p5"]}})

6. Taking Schemas dump

When taking schemas dump, by default, events, triggers, and routines will be taken. Those are stored in the database_name.sql file. The below command is to take a dump of the percona and sakila databases.

MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {"compression":"none", "threads":6, chunking:false})

The below command is to skip the events, routines, and triggers.

MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {events:false, routines:false, triggers:false, "compression":"none", "threads":6, chunking:false})

We can also use these options to include and exclude the events, routines, and triggers

Syntax:

includeEvents   : [db1.include_event1,db2.include_event2...]
includeRoutines : [db1.include_procedure1,db2.include_function2...]
includeTriggers : [db1.include_trigger1,db2.include_trigger2...]

excludeEvents   : [db1.exclude_event1,db2.exclude_event2...]
excludeTriggers : [db1.exclude_trigger1,db2.exclude_trigger2...]
excludeRoutines : [db1.exclude_procedure1,db2.exclude_function2...]

7. Taking specified tables from different databases

Sometimes we may need to take selected tables from different schemas. We can achieve this using the option includeTables.

Syntax:

includeTables:["db1.table1", "db2.table2"....]

Below is the command to take a dump of table users from the percona database and a dump of the actor table from the sakila database.

MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {includeTables:["percona.users", "sakila.actor"], "compression":"none", "threads":6, chunking:false})

8. Instance dump

The command  util.dumpInstance takes the dump of a complete instance and stores it in /backup/instance_dump path. The system databases (mysql, sys, information_schema, performance_schema) are excluded, and by default, a dump of all the users from the instance is taken and stored in the file @.users.sql. This user dump file has the create and grant statements of all the users.

MySQL localhost JS > util.dumpInstance("/backup/instance_dump", {"compression":"none", "threads":6, chunking:false})

Some more options in the instance dump.

users: false                                - Skip users dump
excludeUsers : [‘user1’,’user2’]            - Execute particular users
includeUsers : [‘user1’,’user2’].           - Include particular users
excludeSchemas : [“db1”,”db2”]              - Exclude particular schemas
includeSchemas : [“db1”,”db2”].             - Include particular schemas
excludeTables : [“db1.table1”,”db2.table2”] - Exclude particular tables
includeTables : [“db1.table1”,”db2.table2”] - Include particular tables

9. Restore the dump into a single database

The command util.loadDump is used to restore the dumps. The variable local_infile should be enabled to load the dumps.

Syntax :

util.loadDump("/path/of/the/dump", {options})

The below command is to restore the dump into database test_restore. When we need to restore on a different schema, we have to use this option schema: “test_restore”. Otherwise, it will be restored on the source schema where it was taken.

MySQL localhost SQL > create database test_restore;
Query OK, 1 row affected (0.3658 sec)
MySQL localhost SQL > js
Switching to JavaScript mode...
MySQL localhost JS > util.loadDump("/home/vagrant/schema_dump", {schema:"test_restore", progressFile: "progress.json", threads: 8, showProgress:true, resetProgress:true})

10. Restore the full instance dump and configure replication

Here, we just loaded the full instance dump from /home/vagrant/instance_dump path with eight parallel threads.

MySQL localhost JS > util.loadDump("/home/vagrant/instance_dump", {progressFile: "progress.json", threads: 8, showProgress:true, resetProgress:true})
Loading DDL and Data from '/home/vagrant/instance_dump' using 8 threads.
Opening dump...
Target is MySQL 8.0.33-25. Dump was produced from MySQL 8.0.32-24
NOTE: Load progress file detected for the instance but 'resetProgress' option was enabled. Load progress will be discarded and the whole dump will be reloaded.
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
8 thds loading / 100% (19.18 MB / 19.18 MB), 541.36 KB/s, 6 / 23 tables and partitions done
Recreating indexes - done
Executing common postamble SQL
23 chunks (100.00K rows, 19.18 MB) for 11 tables in 2 schemas were loaded in 11 sec (avg throughput 2.53 MB/s)
0 warnings were reported during the load.
MySQL localhost JS >

I got the binlog file and position from the file @.json and configured the replication.

[root@centos12 instance_dump]# cat @.json | grep -i binlog
"binlogFile": "centos12-bin.000006",
"binlogPosition": 760871466,
[root@centos12 instance_dump]#

MySQL localhost SQL > CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.33.12", SOURCE_USER="bhuvan",SOURCE_PASSWORD="Bhuvan@123", SOURCE_LOG_FILE='centos12-bin.000006',SOURCE_LOG_POS=760871466;
Query OK, 0 rows affected, 2 warnings (0.1762 sec)

MySQL localhost SQL > START REPLICA;
Query OK, 0 rows affected (0.1156 sec)
MySQL localhost SQL > show replica statusG
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.33.12
Source_User: bhuvan
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: centos12-bin.000006
Read_Source_Log_Pos: 823234119
Relay_Log_File: centos11-relay-bin.000002
Relay_Log_Pos: 1152129
Relay_Source_Log_File: centos12-bin.000006
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 762023266
Relay_Log_Space: 62363195
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 718
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 100
Source_UUID: f46a1600-045e-11ee-809f-0800271333ce
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Waiting for dependent transaction to commit
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.1470 sec)

I hope the above examples help to understand the backup and restore using MySQL Shell. It has many advantages over native mysqldump. I personally feel that we are missing insert statements here, as we used to see the insert statements in dump files; apart from that, it looks good. Logical backup is good only when the dataset is small. When the dataset is big, the logical backup takes longer, and we have to go for physical backup using Percona XtraBackup.

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL and MySQL. It performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.

 

Download Percona XtraBackup

MySQL Shell is a pretty nice piece of software. Is not just another mysql client but it is also a tool that offers scripting capabilities for JavaScript and Python. And one of the coolest things you can do with it is to check if your MySQL 5.7 server is ready for an upgrade or not. Enter: Upgrade Checker Utility.

MySQL Shell Upgrade Checker Utility

So what is it? It is a script that will check your MySQL 5.7 instance for compatibility errors and issues with upgrading. It’s important to notice the word “check”. It doesn’t fix. Just check. Fix is on you, friendly DBA (or we can happily assist with it).

But isn’t there something that already does that? Close, but no. The mysqlchk program and the –check-upgrade parameter does something similar: Invokes the CHECK TABLE …. FOR UPGRADE command. The Upgrade Checker goes beyond that. There are also instructions to perform the same checks independently, available here: https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

Now, how can I use it? First of all, you’ll have to install the MySQL Shell package:

yum install -y mysql-shell.x86_64
….
….
Transaction test succeeded
Running transaction
  Installing : mysql-shell-8.0.17-1.el7.x86_64                                                                      1/1
  Verifying  : mysql-shell-8.0.17-1.el7.x86_64                                                                      1/1

Installed:
  mysql-shell.x86_64 0:8.0.17-1.el7

Now you are ready to perform the check. Is a simple as executing this one-liner:

mysqlsh root@localhost -e “util.checkForServerUpgrade();”

[root@mysql2 ~]# mysqlsh root@localhost -e "util.checkForServerUpgrade();"
The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.27-log - MySQL
Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.0.17...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  No issues found

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) Usage of obsolete sql_mode flags
  No issues found

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

15) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

16) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

17) Schema inconsistencies resulting from file removal or corruption
  No issues found

18) Issues reported by 'check table x for upgrade' command
  No issues found

19) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

Errors:   0
Warnings: 1
Notices:  0

No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

Cool, right?

Some things were missing due to lack of parameter. Let’s pass it.

util.checkForServerUpgrade('root@localhost:3306', {"password":"password", "targetVersion":"8.0.11", "configPath":"/etc/my.cnf"})

Now we have 22 warnings instead of one!

13) System variables with new default values
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

  back_log - default value will change
  character_set_server - default value will change from latin1 to utf8mb4
  collation_server - default value will change from latin1_swedish_ci to
    utf8mb4_0900_ai_ci
  event_scheduler - default value will change from OFF to ON
  explicit_defaults_for_timestamp - default value will change from OFF to ON
  innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to
    2 (interleaved)
  innodb_flush_method - default value will change from NULL to fsync (Unix),
    unbuffered (Windows)
  innodb_flush_neighbors - default value will change from 1 (enable) to 0
    (disable)
  innodb_max_dirty_pages_pct - default value will change from 75 (%)  90 (%)
  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10
    (%)
  innodb_undo_log_truncate - default value will change from OFF to ON
  innodb_undo_tablespaces - default value will change from 0 to 2
  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)
  max_allowed_packet - default value will change from 4194304 (4MB) to 67108864
    (64MB)
  max_error_count - default value will change from 64 to 1024
  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB
  performance_schema_consumer_events_transactions_current - default value will
    change from OFF to ON
  performance_schema_consumer_events_transactions_history - default value will
    change from OFF to ON
  slave_rows_search_algorithms - default value will change from 'INDEX_SCAN,
    TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'
  table_open_cache - default value will change from 2000 to 4000
  transaction_write_set_extraction - default value will change from OFF to
    XXHASH64

There’s some work to do there.

And now the question:

Does it work with Percona Server for MySQL? Yes, it does!

To avoid messing with repos I just got the Shell from the MySQL downloads:

wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.17-linux-glibc2.12-x86-64bit.tar.gz; tar -xvzf mysql-shell-8.0.17-linux-glibc2.12-x86-64bit.tar.gz;  cd mysql-shell-8.0.17-linux-glibc2.12-x86-64bit/bin/;./mysqlsh

In conclusion, doing the pre-checks required to go from MySQL 5.7 to MySQL 8 is easier than ever with the MySQL Shell Upgrade Checker Utility. Besides reading the release notes before doing so, the only necessary step to perform is now a command that will leave little room for mistakes. Happy upgrade!

Welcome to the third part of this series. I’m glad you’re still reading, as hopefully this means you find this subject interesting at least. Previously we presented the first two components of MySQL InnoDB Cluster: Group Replication and MySQL Router and now we will discuss the last component, MySQL Shell.

MySQL Shell

This is the last component in the cluster and I love it. Oracle have created this tool to centralize cluster management, providing a friendly, command-line based user interface.

The tool can be defined as an advanced MySQL shell, which is much more powerful than the well known MySQL client. With the capacity to work with both relational and document (JSON) data, the tool provides an extended capability to interact with the database from a single place.

MySQL Shell is also able to understand different languages:

• JavaScript (default) which includes several built-in functions to administer the cluster—create, destroy, restart, etc.—in a very easy way.
• Python it provides an easy way to write Python code to interact with the database. This is particularly useful for developers who don’t need to have SQL skills or run applications to test code.
• SQL to work in classic mode to query database as we used to do with the old MySQL client.

A very interesting feature provided with MySQL Shell is the ability to establish different connections to different servers/clusters from within the same shell. There is no need to exit to connect to a different server, just issuing the command \connect will make this happen. As DBA, I find this pretty useful when handling multiple clusters/servers.

Some of the features present in this tool:

• Capacity to use both Classic and X protocols.
• Online switch mode to change languages (JavaScript, Python and SQL)
• Auto-completion of commands using tab, a super expected feature in MySQL client.
• Colored formatting output that also supports different formats like Table, Tab-separated and Json formats.
• Batch mode that processes batches of commands allowing also an interactive mode to print output according each line is processed.

Some sample commands

Samples of new tool and execution modes:

#switch modes
\sql
\js
\py
#connect to instance
\connect user@host:[port]
#create a cluster (better to handle through variables)
var cluster=dba.createCluster('percona')
#add instances to cluster
cluster.addInstance(‘root@192.168.70.2:3306’)
#check cluster status
cluster.status()
#using another variable
var cluster2=dba.getCluster(‘percona’)
cluster.status()
#get cluster structure
cluster.describe()
#rejoin instance to cluster - needs to be executed locally to the instance
cluster.rejoinInstance()
#rejoin instance to cluster - needs to be executed locally to the instance
cluster.rejoinInstance()
#recover from lost quorum
cluster.forceQuorumUsingPartitionOf(‘root@localhost:3306’)
#recover from lost quorum
cluster.rebootClusterFromCompleteOutage()
#destroy cluster
cluster.dissolve({force:true});

Personally, I think this tool is a very good replacement for the classic MySQL client. Sadly, mysql-server installations do not include MySQL shell by default, but it is worth getting used to. I recommend you try it.

Conclusion

We finally reached the end of this series. I hope you have enjoyed this short introduction to what seems to be Oracle’s bid to have a built-in High Availability solution based on InnoDB. It may become a good competitor to Galera-based solutions. Still, there is a long way to go, as the tool was only just released as GA (April 2018). There are a bunch of things that need to be addressed before it becomes consistent enough to be production-ready. In my personal opinion, it is not—yet. Nevertheless, I think it is a great tool that will eventually be a serious player in the HA field as it’s an excellent, flexible and easy to deploy solution.

The post InnoDB Cluster in a Nutshell Part 3: MySQL Shell appeared first on Percona Database Performance Blog.

This blog post discusses how to use MySQL shell securely from Bash.

The Bourne shell is everywhere. It is part of the most basic Linux install. You will find it on the biggest SPARC machines down to a Raspberry Pi. It is nice to know it will always be there. Unlike other, more complex scripting environments such as Perl and Python, it doesn’t require any additional dependencies to be installed.

Anyone that has automated a MySQL task using a Bourne shell such as Bash will be familiar with the following message:

Warning: Using a password on the command line interface can be insecure.

This semi-ominous warning describes a security flaw in passing credentials on a process command line. Any unprivileged user on the system can use a command like ps aux to find these credentials. While the MySQL shell has added some additional protections to hide these credentials, other information such as database user names, host names, ports and sockets can still be determined by process scanning.

The recommended approach to get around this warning is to use a configuration file to store these credentials. However, in the case of a self-contained script, we may not want to require the user to create a credential file. It would need to be maintained, and might interfere with other tools that use the MySQL shell. For creating automated tools in the Percona Development Services department, I came up with a couple of methods for integrating the MySQL shell into Bash (or any other Bourne Shell) securely.

This first script demonstrates the shell function mysql_exec(). This is for use with small queries that are normally passed to the MySQL shell via the -e parameter.

#!/bin/sh
# call mysql client from shell script without
# passing credentials on command line
# This demonstrates small single queries using
# the -e parameter.   Credentials and connection
# info are sent through standard input.
# david . bennett @ percona . com - 9/24/2016
mysql_user=root
mysql_password=password
mysql_host=127.0.0.1
mysql_port=3306
mysql_database=test
mysql_exec() {
  local query="$1"
  local opts="$2"
  mysql_exec_result=$(
    printf "%sn"
      "[client]"
      "user=${mysql_user}"
      "password=${mysql_password}"
      "host=${mysql_host}"
      "port=${mysql_port}"
      "database=${mysql_database}"
      | mysql --defaults-file=/dev/stdin "${opts}" -e "${query}"
  )
}
mysql_exec "select 'Hello World' as Message"
echo "${mysql_exec_result}"

The above script allows the specification of credentials and connection information via variables in the script. As with any other shell script, these can be moved into a configuration file and secured with chown/chmod, then included with the source or . command. The mysql_exec() function creates a default my.cnf [client] on the fly and passes it to the MySQL shell via –defaults-file=/dev/stdin. The configuration is never written to disk, which makes this method a bit more secure.

Sometimes, you need to process too many queries to pass on the command line. In this case, there is another technique for passing the credentials.

mysql_exec_from_file() {
  local query_file="$1"
  local opts="$2"
  local tmpcnf="$(mktemp)"
  chmod 600 "${tmpcnf}"
  printf "%sn"
    "[client]"
    "user=${mysql_user}"
    "password=${mysql_password}"
    "host=${mysql_host}"
    "port=${mysql_port}"
    "database=${mysql_database}"
    > "${tmpcnf}"
  mysql_exec_from_file_result=$(
      mysql --defaults-file="${tmpcnf}" "$opts" < "${query_file}"
  )
  rm "${tmpcnf}"
}

This technique uses a temporary file, which allows the queries to be passed from a file or input device. Restrictive permissions are set on the file before the configuration is written. The temporary configuration is removed immediately after the shell exits.

While other languages may offer cleaner ways to access your MySQL database, you’ll always know that you’ll be able to execute your shell-based MySQL job scripts across all of the Unix machines in your enterprise.

You can download these scripts directly from my github account.

Happy scripting!

Recently Todd Farmer shared an interesting story about the mysql command line prompt in MySQL 5.7: how it was changed to provide more context and why the change was finally reverted. This made me think that after using the command line client for MongoDB for awhile, I would love seeing a much more modern mysql shell prompt. Here are a few examples of what a modern command line client can do.

Add dynamic information to the prompt

If you use replication with MongoDB, you have probably noticed a nice feature of the prompt: it is replication aware. What I mean is that for a standalone instance, the prompt is simply:

>

When you configure this instance to be the primary of a replica set named RS, the prompt automatically becomes:

RS:PRIMARY>

and for secondaries, you will see:

RS:SECONDARY>

And of course if an election is triggered and roles are switched, the prompt is updated accordingly to reflect the change.

Such a feature may not be easily transposed to MySQL as the allowed replication topologies are more flexible: for instance with master-master replication or for chained replication, some servers may be both a master and a slave.

However if you look a bit deeper how the MongoDB shell prompt can be customized, it is very flexible as you can set the prompt variable to the result of any Javascript function. So for instance if you write this:

var prompt=function() {
    return db.getName()+"/"+ISODate().toLocaleTimeString()+"> ";
}

Your prompt will be changed to something like:

test/11:37:51>

And the change can be persisted by defining the prompt variable in a ~/.mongorc.js file.

If I could do something similar with MySQL, I would for instance be able to know the replication lag of a slave or the uptime of MySQL and so on and so forth. That would be incredibly useful in some situations.

Better help system

Let’s assume I want to manually fail over to a secondary in a MongoDB replica set. I can simply instruct the master that it should no longer be the master. But what is the exact command?

I know that it is rs.xxx because all replica sets functions have this format, so let’s simply open the shell and type rs. and all the available options show up:

> rs.
rs.add(                   rs.constructor            rs.propertyIsEnumerable(  rs.syncFrom(
rs.addArb(                rs.debug                  rs.prototype              rs.toLocaleString(
rs.apply(                 rs.freeze(                rs.reconfig(              rs.toString(
rs.bind(                  rs.hasOwnProperty(        rs.remove(                rs.valueOf(
rs.call(                  rs.help(                  rs.slaveOk(
rs.conf(                  rs.initiate(              rs.status(
rs.config(                rs.isMaster(              rs.stepDown(

Here again, I’d love to see something similar with MySQL. For instance each time I need to convert a timestamp to a human readable date, I have to look at the documentation to find the exact function name.

Actually I could have used the built-in help system to find the name of the function, but it is much more convenient to look at the documentation.

Conclusion

What’s your opinion on which new features a modern mysql shell should provide? If many people agree on a set of features, it could be worth filing a feature request to get a better mysql command line client in a future version of MySQL.

The post MySQL shell prompt vs MongoDB shell prompt appeared first on MySQL Performance Blog.