Mysql (client): verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
(5 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 23: Regel 23:
 
=== Opzetten SSH tunnel ===
 
=== Opzetten SSH tunnel ===
  
−
<code>
+
<pre>
 
ssh -N -L 3336:127.0.0.1:3306 jeroen@129.210.167.215 -f
 
ssh -N -L 3336:127.0.0.1:3306 jeroen@129.210.167.215 -f
−
</code>
+
</pre>
  
 
Wat dit doet:
 
Wat dit doet:
Regel 37: Regel 37:
 
Deze commando's heb ik opgeslagen in <code>.bashrc</code> als aliassen. Bv.:
 
Deze commando's heb ik opgeslagen in <code>.bashrc</code> als aliassen. Bv.:
  
−
<code>
+
<pre>
 
alias tunnel_srv7="ssh -N -L 3336:127.0.0.1:3306 jeroen@129.210.167.215 -f"
 
alias tunnel_srv7="ssh -N -L 3336:127.0.0.1:3306 jeroen@129.210.167.215 -f"
 
</pre>
 
</pre>
Regel 81: Regel 81:
  
 
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.
 
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 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:
 +
 +
<pre>
 +
# Just return the name of the current db
 +
########################################
 +
#
 +
select database();
 +
</pre>
 +
 +
Command line:
 +
 +
<pre>
 +
$ mysql < 100.sql
 +
 +
database()
 +
NULL
 +
</pre>
 +
 +
The returned value is <code>NULL</code> as no database has been selected.
 +
 +
file 100.sql:
 +
 +
<pre>
 +
# Select db + return its name
 +
########################################
 +
#
 +
use example_com;
 +
select database();
 +
</pre>
 +
 +
Command line:
 +
 +
<pre>
 +
$ mysql < 100.sql
 +
 +
database()
 +
example_com
 +
</pre>
 +
 +
=== 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 <code>100.sql</code> from above:
 +
 +
<pre>
 +
$ mysql example_com < 100.sql
 +
 +
database()
 +
example_com
 +
</pre>
 +
 +
=== Execute against a remote database ===
 +
 +
This works! Databases on this specific server were tunneled through localhost on port 3337:
 +
 +
<pre>
 +
# 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
 +
</pre>
  
 
== Bronnen ==
 
== Bronnen ==

Versie van 4 okt 2022 19:31

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 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

Bronnen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Mysql_(client)&oldid=31371"