Mysql (client)

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

mysql is naast de naam van het RDBMS, tevens de naam van een command-line client. Deze wordt altijd meegeleverd met servers. Vaak is-ie onmisbaar.

Inloggen op locale server

Syntaxis voor inloggen op een locale server:

mysql -u <gebruikersnaam> -p<wachtwoord>

Merk op, dat er geen spatie is tussen -p en het wachtwoord
Doorgaans sla ik m'n credentials op in een .my.cnf-bestand, zodat ik met alleen mysql kan inloggen.

Inloggen op remote server

Remote inloggen via TCP/IP:

mysql -u <gebruikersnaam> -p<wachtwoord> -h <ip-adres> -p <poortnummer>

Inloggen via SSH Tunnel

Opzetten SSH tunnel

ssh -N -L 3336:127.0.0.1:3306 jeroen@129.210.167.215 -f

Wat dit doet:

-N: Don't execute a command right now
-L 3336:127.0.0.1:3306: Create a local port forwarding, with local port number, destination IP-adres en remote port number gescheiden door ":"
jeroen@129.210.167.215: Remote server
-f: Run command in background
SSH gebruikt hier het standaard IP-adres 22. Dit kun je desgewenst aanpassen met optie -p [port number].

Deze commando's heb ik opgeslagen in .bashrc als aliassen. Bv.:

alias tunnel_srv7="ssh -N -L 3336:127.0.0.1:3306 jeroen@129.210.167.215 -f"

Het lijkt gek genoeg niet te werken, om dit soort SSH-commando's direct door .bashrc te laten uitvoeren en misschien is dat ook wel veiliger.

Inloggen mysql-client

Dit werkt:

mysql --user dbusername --port 3337 --password -h 127.0.0.1

--port verwijst naar de getunnelde MySQL-poort. Zonder deze optie probeert mysql te verbinden met een locale MySQL-database met naam dbusername
-h 127.0.0.1 (host) is noodzakelijk, ook al lijkt me dat dit 127.0.0.1 de standaardhost is.

Dit werkt ook:

mysql --user dbusername --port 3337 -pFDLsdDSLfds -h 127.0.0.1

Hierbij is het wachtwoord geïncorporeerd in het commando. Dat werkt trouwens alleen met optie -p en niet met --password.

Alias

Deze mysql-commando's kun je onderbrengen als alias in .bashrc. Bv.:

alias mysql_be_fr="mysql --user dbusername --port 3337 -pFDLsdDSLfds -h 127.0.0.1"

SQL-scripts uitvoeren

En nu het klapstuk: Deze alias kun je gebruiken om locale SQL-scripts uit te voeren, bv. om een SQL-dump te importeren in een database. Bv.:

mysql_be_fr fsdfsdf < sku_b3.sql

waarbij fsdfsdf de naam van de database is, waarop script sku_b3.sql uitgevoerd moet worden.

In mei 2021 bleek dit een goede manier te zijn om een tabel toe te voegen aan een WordPress-database. Via MySQL Workbench wilde dit niet lukken, omdat zo'n SQL-dumpbestand al snel te groot is.

Execute SQL directly from the command line

Use the -e switch to directly execute SQL from the command line. E.g.:

mysql -e "use example_com; show tables;" > tables.txt

Execute scripts

Through the mysql client, SQL scripts can be executed that are stored and managed outside the database.

This is a critical functionality, as it allows you to transcend the limitations of SQL and embed this in a general programming environment - E.g.: Have a program run in PHP or Bash, and have relevant SQL scripts get executed. A more practical application: This allows you to use proper flow control which is often sorely missing in SQL. An even more specific example: Updating the price of a given product on 40 webshops (on the same server): Cycle through the names of the databases within a Bash script, and execute the same SQL script against all these databases.

Starter examples

file 100.sql:

# Just return the name of the current db
########################################
#
select database();

Command line:

$ mysql < 100.sql

database()
NULL

The returned value is NULL as no database has been selected.

file 100.sql:

# Select db + return its name
########################################
#
use example_com;
select database();

Command line:

$ mysql < 100.sql

database()
example_com

Execute against a specific database

Here comes the real fun: You can specify against which database the file should be executed. This allows me to manage a whole bunch of databases (webshops) on a given server with minimal tweaking:

With the original file 100.sql from above:

$ mysql example_com < 100.sql

database()
example_com

Execute against a remote database

This works! Databases on this specific server were tunneled through localhost on port 3337:

# Loop through WordPress sites
########################################
#
i=1; for ((i; i<=$site_array_rows; i++))
do
	#
	# Extract row entities
	########################################
	#
	site_cat=${site_array[$i,cat]}
	site_url=${site_array[$i,url]}
	site_db=${site_array[$i,db]}
	site_dbpass=${site_array[$i,dbpass]}
	site_country=${site_array[$i,country]}
	site_language=${site_array[$i,language]}
	site_currency=${site_array[$i,currency]}
	site_path="/var/www/${site_array[$i,url]}"


	# Select only _bal_cb_bal1_ sites
	########################################
	#
	if [[ "$site_cat" =~ "_bal_" ]] && [[ "$site_cat" =~ "_cb_" ]]  && [[ "$site_cat" =~ "_bal1_" ]]
	then
		#
		# Check
		########################################
		#
		echo ""; echo ""; echo "Match:"
		echo "	site_cat:       $site_cat"
		echo "	site_url:       $site_url"
		echo "	site_db:        $site_db"
		echo "	site_dbpass:    $site_dbpass"
		echo "	site_country:   $site_country"
		echo "	site_language:  $site_language"
		echo "	site_currency:  $site_currency"
		echo "	site_path:      $site_path"


		# Execute script
		########################################
		#
		echo ""; echo ""; echo "Executing test script against current db:"
		mysql -u "$site_db" --port 3337 -p"$site_dbpass" -h 127.0.0.1 $site_db < 130.sql


	else
		#
		echo "Not a match: $site_url"
		#
	fi
	#
done