Monday, 31 January 2022

How to connect to MySQL database from Ubuntu command line

We first need to install MySQL shell.
 
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



1 comment:

Taylor said...

I just have to introduce this hacker that I have been working with him on getting my credit score been boosted across the Equifax, TransUnion and Experian report. He made a lot of good changes on my credit report by erasing all the past eviction, bad collections and DUI off my credit report history and also increased my FICO score above 876 across my three credit bureaus report you can contact him for all kind of hacks . Email him here support@wavedrive.tech go on their website wavedrive.tech for more details,Whatsapp No:+14106350697 if you want to chat them up,One thing i can assure you would not regret this at all he is 100% legit