Best way to start a thread when looking for MySQL Help and more

I’m active on multiple platforms (mail, slack, forums, …) and often, when people are looking for help, the first 5 or 10 questions are always the same:

  • which version of MySQL are you running ?
  • which OS ?
  • is it in the cloud ?
  • which provider ?
  • are you using replication ?
  • GTIDs ?
  • ….

I’ve added to MySQL Shell Plugin repository and plugin called support which provides an output that user can share when looking for MySQL help.

Usually, I’m focusing only in MySQL 8.0, but this plugin works with older versions too (don’t try MySQL 3.23…. it should be compatible from 5.6).

Let’s see an output if I run it locally:

[fred@fedora ~] $ mysqlsh root@localhost -e "support.fetchInfo()"
========================================
Report for fedora - Fri 2021-10-15 10:57
========================================
                 MySQL Version: MySQL Community Server - GPL (8.0.26) - x86_64 
                       Dataset: 
                              |--             Data: 177.73 MiB
                              |--          Indexes: 32.19 MiB
                              |--       Total Size: 209.92 MiB

                  Engines Used: 
                              |--           InnoDB: 43 table(s) (0.20G)
                              |--           MyISAM: 2 table(s) (0.00G)


             Tables without PK: 12

            InnoDB Buffer Pool: 
                              |--          BP Size: 128.00 MiB
                              |--   BP Instance(s): 1
                              |--    BP filled at : 16.0%
                              |--  Disk Read Ratio: 1.12%

                   InnoDB Logs: 
                              |--        File Size: 48.00 MiB
                              |--      Nb of Files: 2
                              |--  Checkpoint Info:    0 bytes / 96.00 MiB
                              |--    CheckPointAge: 0.0%
                              |--  Flush at Commit: 1
                              |--         Redo Log: ON

           Adaptive Hash Index: 
                              |--      AHI Enabled: ON
                              |--        AHI Parts: 8
                              |--        AHI Ratio: 50.06

   Global Variables Configured: 
                              |--           GLOBAL: 4
                              |--        PERSISTED: 4
                              |--          DYNAMIC: 2


       Replication Information: 
                              |--  Binlog Checksum: CRC32
                              |-- Binlog Encryption: 0
                              |--    Binlog Format: ROW
                              |-- Binlog Row Image: FULL
                              |-- Binlog Row Metadata: MINIMAL
                              |-- Binlog Trx Dependency Tracking: COMMIT_ORDER
                              |--        GTID Mode: ON
                              |--          Log Bin: 1
                              |-- Log Bin Basename: /var/lib/mysql/binlog
                              |--        Server Id: 1
                              |--      Sync Binlog: 1
                              |-- Trx Write Set Extraction: XXHASH64

                Flush Commands: 1

         User Defined Routines: 
                              |--              sys: 1
                              |--              mds: 7
                              |--    query_rewrite: 1

        Authentication Plugins: 
                              |-- caching_sha2_password: 13 user(s)
                              |--  sha256_password: 1 user(s)
                              |--   mysql_no_login: 1 user(s)

   MDS Incompatible Privileges: 6 user(s)

As you can see, this is very easy and provides already valuable information.

Operating System

As the server is running on my local machine, I can also, provide some information regarding the system. By default this is disabled. Let’s try it (I use the Python mode this time):

[fred@fedora ~] $ mysqlsh root@localhost --py -e "support.fetch_info(os=True)"
========================================
Report for fedora - Fri 2021-10-15 11:02
========================================
              Operating System: Linux (MySQL built on Linux)
                       Version: 5.14.11-200.fc34.x86_64
                  Architecture: x86_64
                     Processor: Intel(R) Core(TM) i5-4570 CPU @ 3.20GHz
                   CPU Core(s): 4
                  Total Memory: 31Gi
                   Memory Used: 10Gi
                   Memory Free: 10Gi
              Filesystem Cache: 10Gi
                    Total Swap: 8.0Gi
                     Swap Used: 0B
                     Swap Free: 8.0Gi
                    Swappiness: 60
                       Datadir: /var/lib/mysql/
                              |--   Physical drive: /dev/mapper/luks-500325a0-4b9e-4ad2-83a9-fecadbec02e1
                              |--             Size: 1.9T
                              |--       Used (10%): 171G
                              |--             Free: 1.7T
                              |--      Mount point: /
                              |--       Filesystem: btrfs
                              |-- Mount attributes: rw,relatime,compress=zstd:1,ssd,space_cache,subvolid=256,subvol=/home
          innodb_data_home_dir: None
     innodb_log_group_home_dir: ./
   innodb_temp_tablespaces_dir: ./#innodb_temp/
                 innodb_tmpdir: None
                        tmpdir: /var/tmp

                 MySQL Version: MySQL Community Server - GPL (8.0.26) - x86_64 
                       Dataset: 
                              |--             Data: 177.73 MiB
                              |--          Indexes: 32.19 MiB
                              |--       Total Size: 209.92 MiB

                  Engines Used: 
                              |--           InnoDB: 43 table(s) (0.20G)
                              |--           MyISAM: 2 table(s) (0.00G)


             Tables without PK: 12

            InnoDB Buffer Pool: 
                              |--          BP Size: 128.00 MiB
                              |--   BP Instance(s): 1
                              |--    BP filled at : 16.0%
                              |--  Disk Read Ratio: 1.07%

                   InnoDB Logs: 
                              |--        File Size: 48.00 MiB
                              |--      Nb of Files: 2
                              |--  Checkpoint Info:    0 bytes / 96.00 MiB
                              |--    CheckPointAge: 0.0%
                              |--  Flush at Commit: 1
                              |--         Redo Log: ON

           Adaptive Hash Index: 
                              |--      AHI Enabled: ON
                              |--        AHI Parts: 8
                              |--        AHI Ratio: 50.37

   Global Variables Configured: 
                              |--           GLOBAL: 4
                              |--        PERSISTED: 4
                              |--          DYNAMIC: 2


       Replication Information: 
                              |--  Binlog Checksum: CRC32
                              |-- Binlog Encryption: 0
                              |--    Binlog Format: ROW
                              |-- Binlog Row Image: FULL
                              |-- Binlog Row Metadata: MINIMAL
                              |-- Binlog Trx Dependency Tracking: COMMIT_ORDER
                              |--        GTID Mode: ON
                              |--          Log Bin: 1
                              |-- Log Bin Basename: /var/lib/mysql/binlog
                              |--        Server Id: 1
                              |--      Sync Binlog: 1
                              |-- Trx Write Set Extraction: XXHASH64

                Flush Commands: 1

         User Defined Routines: 
                              |--              sys: 1
                              |--              mds: 7
                              |--    query_rewrite: 1

        Authentication Plugins: 
                              |-- caching_sha2_password: 13 user(s)
                              |--  sha256_password: 1 user(s)
                              |--   mysql_no_login: 1 user(s)

   MDS Incompatible Privileges: 6 user(s)

More ?

The plugin can also provide more information when possible. Like details and advice:

 JS > \h support
NAME
      support - Getting Information useful for requesting help.

DESCRIPTION
      A collection of methods useful when requesting help such as support or
      Community Slack and Forums

FUNCTIONS
      fetchInfo([mysql][, os][, advices][, details][, session])
            Fetch info from the system.

      help([member])
            Provides help about this object and it's members

Advices can be very useful when for example planing to migrate to MySQL Database Service.

Let’s have a look when using the plugin with AWS Aurora:

[fred@fedora ~] $ mysqlsh admin@aurora57.cluster-cy6iecpgwnkz.eu-west-1.rds.amazonaws.com --py -e "support.fetch_info(advices=True)"
==============================================
Report for ip-10-4-1-81 - Fri 2021-10-15 12:05
==============================================
                 MySQL Version: MySQL Community Server (GPL) (5.7.12) - x86_64 (AWS RDS)
                        Aurora: 2.07.2
Your MySQL version might be old or not fully supported by this tool!
                       Dataset: 
                              |--             Data: 9.02 MiB
                              |--          Indexes: 112.00 KiB
                              |--       Total Size: 9.12 MiB

                  Engines Used: 


             Tables without PK: 4
It's not recommended to have tables without Primary Key

            InnoDB Buffer Pool: 
                              |--          BP Size: 7.79 GiB
                              |--   BP Instance(s): 2
                              |--    BP filled at : 1.0%
                              |--  Disk Read Ratio: 0.08%

                   InnoDB Logs: 
                              |--        File Size: 48.00 MiB
                              |--      Nb of Files: 2
                              |--       Total Size: 96.00 MiB
                              |--  Flush at Commit: 1

           Adaptive Hash Index: 
                              |--      AHI Enabled: OFF
                              |--        AHI Parts: 8
                              |--        AHI Ratio: 0.0


       Replication Information: 
                              |--  Binlog Checksum: CRC32
                              |--    Binlog Format: ROW
                              |-- Binlog Row Image: FULL
                              |--        GTID Mode: OFF_PERMISSIVE
You should enable GTIDs !
                              |--          Log Bin: 0
You should enable binary logs !
                              |-- Log Bin Basename: None
                              |--        Server Id: 340517295
                              |--      Sync Binlog: 1
                              |-- Trx Write Set Extraction: OFF

                Flush Commands: 0

  Extra tables in mysql schema: 19
Extra tables in mysql schema are not supported in MDS
         User Defined Routines: 
                              |--            mysql: 32
Custom routines in mysql schema are not supported in MDS

        Authentication Plugins: 
                              |-- mysql_native_password: 3 user(s)

   MDS Incompatible Privileges: 2 user(s)
admin@% has a privilege not supported in MDS: RELOAD
admin@% has a privilege not supported in MDS: LOAD FROM S3
admin@% has a privilege not supported in MDS: SELECT INTO S3
admin@% has a privilege not supported in MDS: INVOKE LAMBDA
admin@% has a privilege not supported in MDS: INVOKE SAGEMAKER
admin@% has a privilege not supported in MDS: INVOKE COMPREHEND
rdsadmin@localhost has a privilege not supported in MDS: ALL PRIVILEGES

Conclusion

From now when you have a question related to MySQL and some more info is required, you know an easy way to provide such info.

If you plan to migrate to OCI MySQL Database Service and use MDS Inbound Replication, the plugin provides you some information regarding the basics limitations but I encourage you to check those in the manual.

If you like the tool, please contribute, I’m sure there are plenty nice and useful stuff that can be added to it.

Enjoy MySQL and MySQL Shell !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.