Mysql (client): verschil tussen versies
(3 tussenliggende versies door dezelfde gebruiker niet weergegeven) | |||
Regel 88: | Regel 88: | ||
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. | 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: | file 100.sql: | ||
<pre> | <pre> | ||
− | # Just return the name of the current db | + | # Just return the name of the current db |
− | + | ######################################## | |
# | # | ||
select database(); | select database(); | ||
Regel 102: | Regel 102: | ||
<pre> | <pre> | ||
− | mysql < 100.sql | + | $ mysql < 100.sql |
database() | database() | ||
NULL | 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> | </pre> | ||
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 alleenmysql
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 naamdbusername
-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