I ran into a situation where I had SSH access to a MySQL server but needed to export a table to a spreadsheet. There are too many options, so this Techbit documents my frequent methods.
MySQL Quickstart
The How to Redirect a Referrer Parameter in Apache Techbit links a guide for getting an Ubuntu Server 22.04 LTS virtual machine running in VirtualBox. By contrast, installing MySQL isn’t so straightforward:
Warning
The following steps are for a test environment. My clashcallerbot-reddit project includes instructions for installing MySQL or MariaDB on Ubuntu for use in a production environment.
Install mysql:
sudo apt install mysql-server
Verify mysql service status:
sudo service mysql status
In my case, it outputs something like this:
● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:> Active: active (running) since Thu 2024-03-07 05:11:08 UTC; 57s ago Process: 3012 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=e> Main PID: 3036 (mysqld) Status: "Server is operational" Tasks: 38 (limit: 2220) Memory: 365.3M CPU: 1.905s CGroup: /system.slice/mysql.service └─3036 /usr/sbin/mysqld Mar 07 05:11:06 ubuntuserver systemd[1]: Starting MySQL Community Server... Mar 07 05:11:08 ubuntuserver systemd[1]: Started MySQL Community Server.Note
The following steps vary depending on the mysql version, so this is the version at the time of this writing:
sudo mysqladmin version mysqladmin Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu)) Copyright (c) 2000, 2024, 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. Server version 8.0.36-0ubuntu0.22.04.1 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 54 min 21 sec Threads: 2 Questions: 86 Slow queries: 0 Opens: 254 Flush tables: 3 Open tables: 173 Queries per second avg: 0.026
Update the bind-address to use VirtualBox’s default:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Then add/modify these lines:
#bind-address = 127.0.0.1 #mysqlx-bind-address = 127.0.0.1 bind-address = 10.0.2.15 mysqlx-bind-address = 10.0.2.15
Tip
The How to Redirect a Referrer Parameter in Apache Techbit also includes details using VirtualBox’s Port Forwarding feature to forward ports from the Host to the Guest. That’ll provide us mysql access without authenticating into the Guest first.
Restart mysql service:
sudo systemctl restart mysql.service
Verify mysql‘s listening address and ports:
sudo ss -tapn | grep mysql
Mine looked like this:
LISTEN 0 70 10.0.2.15:33060 0.0.0.0:* users:(("mysqld",pid=1090,fd=21)) LISTEN 0 151 10.0.2.15:3306 0.0.0.0:* users:(("mysqld",pid=1090,fd=23))Log into mysql as the root user:
sudo mysql -u root
By default, the root user doesn’t have a password, but we can set one (optional since it’s running in VirtualBox):
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';
Use the -p switch to authenticate as root with a password (optional, since sudo bypasses the requirement):
sudo mysql -u root -p
MySQL 8 introduced some user creation changes, so use the following to create a test user with root permissions that can log in from anywhere:
mysql> CREATE USER 'debug'@'%' IDENTIFIED BY 'PASSWORD'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'debug'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;
My clashcallerbot-reddit project has an example database schema we can create:
mysql> CREATE DATABASE onboarding; mysql> USE onboarding; mysql> CREATE TABLE message_table (id INT UNSIGNED NOT NULL AUTO_INCREMENT, permalink VARCHAR(100), message VARCHAR(100), new_date DATETIME, username VARCHAR(20), PRIMARY KEY(id)); mysql> ALTER TABLE message_table AUTO_INCREMENT=1;
It creates a database called onboarding with an empty table called message_table consisting of 5 columns:
- id is configured to start at the number 1 and automatically increments when data is inserted, which ensures no two values will be the same. Since it contains unique values, it’ll be the primary key, which we’ll use to identify records in the table.
- permalink contains 100-character strings. Intended for URLs.
- message also contains strings of 100 characters. Intended for message text.
- new_date is a MySQL DateTime, intended to be a date and time in the future.
- username contains strings of 20 characters. Intended to identify users in a system.
Insert data into message_table:
mysql> INSERT INTO message_table (permalink,message,new_date,username) VALUES ("link1", "message1","20240307203701","user1"); mysql> INSERT INTO message_table (permalink,message,new_date,username) VALUES ("link2", "message2","20240307203702","user2"); mysql> INSERT INTO message_table (permalink,message,new_date,username) VALUES ("link3", "message3","20240307203703","user3"); mysql> INSERT INTO message_table (permalink,message,new_date,username) VALUES ("link4", "message4","20240307203704","user4"); mysql> INSERT INTO message_table (permalink,message,new_date,username) VALUES ("link5", "message5","20240307203705","user5");
Finally, verify the contents of the message_table:
mysql> SELECT * FROM message_table; +----+-----------+----------+---------------------+----------+ | id | permalink | message | new_date | username | +----+-----------+----------+---------------------+----------+ | 1 | link1 | message1 | 2024-03-07 20:37:01 | user1 | | 2 | link2 | message2 | 2024-03-07 20:37:02 | user2 | | 3 | link3 | message3 | 2024-03-07 20:37:03 | user3 | | 4 | link4 | message4 | 2024-03-07 20:37:04 | user4 | | 5 | link5 | message5 | 2024-03-07 20:37:05 | user5 | +----+-----------+----------+---------------------+----------+ 5 rows in set (0.00 sec)
Now that we have a database and table with data, we can review typical methods of exporting it.
Export with MySQL Workbench
MySQL Workbench is a Graphical User Interface (GUI) for MySQL made by Oracle Corporation (the same creators of MySQL).
As is often the case with GUIs, it provides many tools in a convenient, integrated environment. We can install it on the Host and connect to the Guest with 127.0.0.1:3306 using VirtualBox Port Forwarding.
The steps for exporting will vary based on the Workbench version. At the time of this writing, the latest MySQL Workbench Community release is Version 8.0.36.
Exporting with Workbench is straightforward with the Export Resultset feature. First, use the SQL Query tab to input and execute SQL statements:
Then use Export Resultset to export it as CSV, XML, or JSON:
Well, that was exponentially easier than configuring the database, but what if we can’t install MySQL Workbench or it isn’t compatible with the currently installed version of MySQL?
There are archived versions of Workbench available for older MySQL versions. However, a more direct way to export data is from the database server!
Export in Terminal
Whether it’s the database server or a Host machine with MySQL installed, exporting with the terminal doesn’t require a GUI or additional software.
The format of the terminal commands will vary depending on the method used to connect to MySQL running on the Ubuntu Server Guest.
If the Host machine already has MySQL installed, we can connect as the debug user with the following:
mysql -h 127.0.0.1 -P 3306 -u debug -p
If we’re connected to the Ubuntu Server Guest directly via SSH, then we can authenticate as the debug user directly:
mysql -u debug -p
I use two methods with a direct database server terminal connection to export data as a CSV.
Tip
With either method, I’ve mentioned in my How to Make a Private Microsoft Azure VM in any Region with a VPN Gateway project about using SFTP to transfer files, which we can use to download the CSV from the Guest.
Using Translate
The translate command method is the most straightforward:
mysql -u debug -p -e "SELECT * FROM message_table" onboarding | tr '\t' ',' > output1.csv
cat output1.csv
id,permalink,message,new_date,username
1,link1,message1,2024-03-07 20:37:01,user1
2,link2,message2,2024-03-07 20:37:02,user2
3,link3,message3,2024-03-07 20:37:03,user3
4,link4,message4,2024-03-07 20:37:04,user4
5,link5,message5,2024-03-07 20:37:05,user5
The mysql command outputs data as tab-separated values by default, so we can pipe that output to substitute the tabs \t as commas using tr and then save it to the CSV file.
Warning
This only works if the data doesn’t contain tabs or commas!
The tr command replaces all tabs and can’t differentiate between MySQL-output tabs and tabs in data. Similarly, if there are commas in the data, tr will leave them, so the software reading the CSV might not separate the data correctly.
If supported by the software reading the CSV, using a different delimiter, like a semicolon or other uncommon character, might work better.
tr is a simple substitution method, i.e., replacing one character with another, but what if we want more flexibility?
Using Sed
The stream editor method has more options but works similarly:
mysql -u debug -p -e "SELECT * FROM message_table" onboarding | sed 's/\t/,/g' > output2.csv
cat output2.csv
id,permalink,message,new_date,username
1,link1,message1,2024-03-07 20:37:01,user1
2,link2,message2,2024-03-07 20:37:02,user2
3,link3,message3,2024-03-07 20:37:03,user3
4,link4,message4,2024-03-07 20:37:04,user4
5,link5,message5,2024-03-07 20:37:05,user5
In this case, we’re using sed‘s substitute command, which consists of 3 parts: s/regexp/replacement/flags. The first part is a regular expression (regex) that specifies the text to replace. Next, we provide the string that will replace each regex match. Finally, the g flag ensures case conversion (which we aren’t using) isn’t applied between occurrences of the regex.
Like tr, this method won’t work on data with tabs or commas, so consider substituting with a different delimiter if the software reading the CSV supports it.
Note
MacOS uses a BSD version of sed instead of the GNU version, which doesn’t recognize tabs identically. As a result, OSX needs a different workaround using sed. For example, using printf to make a tab character that BSD-sed recognizes:
mysql -u debug -p -e "SELECT * FROM message_table" onboarding | sed 's/$(printf '\t')/,/g' > output2.csv
None of the Above
These methods might not work for everyone (like datasets with tabs and/or commas), but they aren’t the only ones. As hinted at the beginning, there are too many options, like exporting to a file directly from MySQL or various scripts in various languages. Those methods require more configuration or third-party software, so I don’t use them, but they’re out there for those with no other options.
Storing data is important, but so is exporting it. This was a fantastic opportunity to learn more about common (and less common) MySQL exporting methods.