If you are need some stresstest for your MySQL server you can use mysqslap. I offen use this command
mysqlslap --auto-generate-sql --concurrency=100 --iterations=200
mysql
bash script mysql commands
I want to use this script to run with Ansible. My requirements were to read the maximum connections in mysql server. Also I want to write the output from mysql client in to a file.
You can use
mysql -e "show status like 'max_used_connections';"
Or you can use
mysql <<EOF
tee /tmp/mysql_max.txt
show variables like 'max_connections';
show status like 'max_used_connections';
EOF
output
Logging to file '/tmp/mysql_max.txt'
Variable_name Value
max_connections 100
Variable_name Value
Max_used_connections 10
Found here
https://www.shellhacks.com/mysql-run-query-bash-script-linux-command-line/
https://alvinalexander.com/mysql/how-save-output-mysql-query-file/
the ‘information_schema global_status feature is disabled
If you want to import a MySQL dump in to your MySQL server 5.7 and you got following error
The 'INFORMATION_SCHEMA.SESSION_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'Login in to your mysql root console and send this command to your mysql server
set @@global.show_compatibility_56=ON;
SHOW VARIABLES LIKE '%compatibility%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| show_compatibility_56 | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
Mysql ha alternative
MySQL Datenbank kopieren
Ich hatte oft das Problem, wie kopiere ich eine MySQL Datenbank zu einem anderen Server.
Da ich oft zwei Test Server habe wo ich die Daten hin und her kopiere sind mir die GUI Werkzeuge und PHPMyAdmin Tools etwas zu lästig.
Nach etwas Suche und probieren kam ich auf folgende Shell Eintrag
mysqldump -h 192.168.100.17 -u root –password=1234 testdb | mysql -h 192.168.100.227 -u root –password=1234 testdb
Dabei muß auf dem zu kopierenden Server mindestens eine leere DB mit dem Zielnamen der DB existieren
Konkretor Blog, IT Stuff and more 