MariaDB and MySQL sometimes realy strange. The last time I had offen dumps between sql server versions. you must not use my pipe for compression this is optional.
mysqldump –events –routines –triggers –single-transaction –default-character-set=utf8mb4 –opt database.dump | gzip > database.sql.gz
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/
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)
How to delete this message
show status like ‘Aborted_clients’;flush status;
So when you are exporting a MySQL Dump from RDS in AWS and Import to another RDS Instace, you are becoming an error by importing your dump file.
Access denied; you need the SUPER privilege for this operation
https://forums.aws.amazon.com/message.jspa?messageID=183618
mysqldump -h mydb.tasdasdasd.eu-central-1.rds.amazonaws.com -u my_qa -p my_q > my_qa.sql
In your exported dump file
/*!50003 CREATE*//*!50017 DEFINER=`root`@`localhost`*//*!50003TRIGGER temp_product_feature_insert
root@localhost is your enemy 😀
Import your MySQL Dump after modify with set to your target RDS instance
You must replace it with sed
sed -e's/DEFINER=`.*`@`.*`/DEFINER=CURRENT_USER /g'my_qasql > fixed_my_qa.sql
This change all entry´s to
/*!50003 CREATE*//*!50017 DEFINER=CURRENT_USER *//*!50003TRIGGER temp_product_feature_insert
mysql -h myprod.dfsfsdfe3.eu-central-1.rds.amazonaws.com -u my_prod -p myproddb_p < fixed_my_qa.sql
Slow Log im Betrieb einschalten
SHOW VARIABLES LIKE ‘slow_query_log%’;
SHOW VARIABLES LIKE ‘log_queries%’;
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;
SET GLOBAL log_queries_not_using_indexes = ‘ON’;
Slow Log im Betrieb wieder ausschalten
SET GLOBAL slow_query_log = ‘OFF’;
SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;
SET GLOBAL log_queries_not_using_indexes = ‘OFF’;
MySQL Aborted Clients anschauen und status löschen im laufenden Betrieb
show status like ‘Aborted_clients’;
flush status;
mysql> SELECT table_schema AS “Database name”, SUM(data_length + index_length) / 1024 / 1024 AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_Schema;
found here
http://makandracards.com/makandra/1447-list-sizes-of-mysql-databases
Oft steht es in der my.cnf im Klartext drin, wie es verschlüsselt geht, steht hier
Ohne restart der Datenbank MySQL durch zu führen.
Im Prinzip startet man eine zweite MySQL Instanz und schiebt diser dann das neue Passwort unter.
Wie das genau geht
Konkretor Blog, IT Stuff and more 