I'm running my test db from within a Docker container as:
$ docker run \
--name my-mysql \
--rm \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=root \
-v mysql-volume:/var/lib/mysql \
-d mysql:latest \
--sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
To test that DB is up and running we can use telnet:
$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
J
8.0.27NwogIs�,
2)IqzZ%caching_sha2_password2#08S01Got timeout reading communication packetsConnection closed by foreign host.
To connect MySQL shell running directly on my local host:
$ mysqlsh --mysql -u root -proot -h127.0.0.1
MySQL Shell 8.0.28
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@127.0.0.1'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 64
Server version: 8.0.27 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL 127.0.0.1:3306 ssl JS >
As we can see above, the default execution mode is set to JS (JavaScript). If we want to execute SQL commands, we need to change execution mode to SQL which can be done with \sql command (see all mysqlsh commands
here):
MySQL 127.0.0.1:3306 ssl JS > \sql
Switching to SQL mode... Commands end with ;
MySQL 127.0.0.1:3306 ssl SQL >
Alternatively, we can pass
--sql argument when running mysqlsh in order to start it in SQL mode from the very beginning (connection protocol, classic or X protocol is autodetected):
$ mysqlsh --sql ...
We can now execute SQL queries:
MySQL 127.0.0.1:3306 ssl SQL > show databases;
+--------------------+
| Database |
+--------------------+
| my_custom_schema |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.0019 sec)
MySQL 127.0.0.1:3306 ssl SQL >
MySQL 127.0.0.1:3306 ssl SQL > use mysql
Default schema set to `mysql`.
Fetching table and column names from `mysql` for auto-completion... Press ^C to stop.
MySQL 127.0.0.1:3306 ssl mysql SQL > 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.0040 sec)
MySQL 127.0.0.1:3306 ssl mysql SQL > describe user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.0046 sec)
MySQL 127.0.0.1:3306 ssl mysql SQL > select User, Host from user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.0035 sec)
If we know in advance the schema (database) that we want to use, we can pass its name to --schema or --database argument. Also, if we want to save the output of all commands from the interactive session into a file, we can specify --pager command (e.g. tee):
$ mysqlsh --host=127.0.0.1 --user=root --password=root --sql --schema=sys --pager="tee mysqlsh.out"
MySQL Shell 8.0.28
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'root@127.0.0.1/sys'
Fetching schema names for autocompletion... Press ^C to stop.
Fetching table and column names from `sys` for auto-completion... Press ^C to stop.
Your MySQL connection id is 66
Server version: 8.0.27 MySQL Community Server - GPL
Default schema set to `sys`.
MySQL 127.0.0.1:3306 ssl sys SQL > show tables;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
...
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
+-----------------------------------------------+
101 rows in set (0.0060 sec)
MySQL 127.0.0.1:3306 ssl sys SQL > \quit
Bye!
Let's check the output file now:
$ cat mysqlsh.out
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
...
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
+-----------------------------------------------+
101 rows in set (0.0060 sec)
To execute multiple commands in a single session and store their outputs in order of execution into a (text) file we need first to write a .sql file which lists all commands.
mysql-queries.sql:
select now() as '';
select 'This is a mysqlsh session output' as'';
show tables;
Then we can set it as a source file and redirect output into a file:
$ mysqlsh --host=127.0.0.1 --user=root --password=root --sql --schema=sys --file=mysql-queries.sql > mysqlsh.out
WARNING: Using a password on the command line interface can be insecure.
Let's check the output file:
$ cat mysqlsh.out
2022-01-31 16:36:49
This is a mysqlsh session output
Tables_in_sys
host_summary
host_summary_by_file_io
host_summary_by_file_io_type
...
x$waits_by_user_by_latency
x$waits_global_by_latency
Another way to execute multiple commands and pipe output into a file is by listing SQL commands one by another, separated by semicolon.
# mysqlsh \
--host=$MYSQL_DB_HOST \
--port=$MYSQL_DB_PORT \
--user=root \
--password=root \
--sql \
--execute='use mysql; select User from user;' > ./out/$SQL_FILE.out
The queries above are equal to:
select User from mysql.user;
MySQL DB ports and connection protocols
It is possible to check on which port DB listens to:
$ docker container inspect mysql-demo | jq .[].NetworkSettings
{
"Bridge": "",
"SandboxID": "ad84f18f89bb51dd6c694357346633aae3350e7333f5eadd4296aa8a81fba96a",
"HairpinMode": false,
"LinkLocalIPv6Address": "",
"LinkLocalIPv6PrefixLen": 0,
"Ports": {
"3306/tcp": [
{
"HostIp": "0.0.0.0",
"HostPort": "3307"
}
],
"33060/tcp": null
},
"SandboxKey": "/var/run/docker/netns/ad84f18f89bb",
"SecondaryIPAddresses": null,
"SecondaryIPv6Addresses": null,
"EndpointID": "246dcd5e7ab59333868ebdeda32f8580309a9b5daeac5648f369f53a62c5c461",
"Gateway": "172.17.0.1",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"IPAddress": "172.17.0.3",
"IPPrefixLen": 16,
"IPv6Gateway": "",
"MacAddress": "02:42:ac:11:00:03",
"Networks": {
"bridge": {
"IPAMConfig": null,
"Links": null,
"Aliases": null,
"NetworkID": "7a20c87648fe59ba77848c398b3c69e0073a294f018c9c563242302cddcba16c",
"EndpointID": "246dcd5e7ab59333868ebdeda32f8580309a9b5daeac5648f369f53a62c5c461",
"Gateway": "172.17.0.1",
"IPAddress": "172.17.0.3",
"IPPrefixLen": 16,
"IPv6Gateway": "",
"GlobalIPv6Address": "",
"GlobalIPv6PrefixLen": 0,
"MacAddress": "02:42:ac:11:00:03",
"DriverOpts": null
}
}
}
Default classic MySQL protocol port is usually 3306.
Default X Protocol port is usually 33060.
From the output above, we can see that our DB server supports only classic MySQL protocol.
Exporting Tables
How to export tables from MySQL Database | My Public Notepad
Importing Tables
