Hi all!
I was asked to make a conversion from T-SQL (MSSQL) Procedure to PL/PGSQL. Regarding how boring is this task, the follow link helped me:
http://www.sqlines.com/online
I highly recommend it. The site has a commercial solution to convert all database, but some code can be converted online for free. 🙂
The conversion not fixed at all, but make a good part of the work… And all help is helpful…
More“Converting Between SQLServer, Oracle, PostgreSQL, MySQL, Sybase and others…”
Quick one!
# Warnings
cat /var/log/mysqld.log |grep `date +%y%m%d` | grep "\[Warning\]"
# Errors
cat /var/log/mysqld.log |grep `date +%y%m%d` | grep "\[ERROR\]"
And a Bonus!
To get entries from X days ago:
cat /var/log/mysqld.log |grep `date --date="46 days ago" +%y%m%d`
Matheus.
Quick tip:
# Problem:
MySQL: Unable to connect to database 'xxx' on server 'xxx' on port xx with user 'root' - Access denied for user 'root'@'xxxxx'
Solution:
GRANT ALL PRIVILEGES ON *.* TO root@'xxxxx' IDENTIFIED BY '$PASSWORD' WITH GRANT OPTION; FLUSH PRIVILEGES;
Have a nice week!
Matheus.
Hi all!
As you know, we have some commercial solutions to monitoring/alerting MySQL, like MySQL Enterprise Monitor or Oracle Grid/Cloud Control.
But, regarding we are using MySQL instead of Oracle Database, we can assume it’s probably a decision taken based on cost. So, considering Open Source solutions, we basically have Nagios, Zabbix, OpenNMS…
Thinking on Nagios, in my opinion the “supra sumo” is mysql_health_check.pl.
Below whitepaper and presentation:
White Paper
Presentation
Code
Good one by Sheeri Cabral and posted here!
Any way, with theese two we can make lots of magic:
1. check_mysql.pl
– Check status of MySql server (slow queries, etc)
– Queries per second graph
2. check_db_query.pl
– Allowes to run SQL Queries and setting thresholds for warning e critical. Ex:
check_db_query.pl -d database -q query [-w warn] [-c crit] [-C conn_file] [-p placeholder]
Ex for Nagios call:
define command{
command_name check_db_entries
command_line /usr/local/bin/perl $USER1$/check_db_query.pl -d "$ARG1$" -q "$ARG2$" $ARG3$
}
So, now it’s just make your queries and implement your free monitoring on MySQL! 🙂
Matheus.
It happens on MySQL, as you know. Run an Optimize Table solve the question.
BUT, be careful! During the optimize the table stay locked (writing is not possible).
(Fragmented Table)
So what?
To not cause a lock in every table, the script below shows and runs (if you want to list but not run, comment the line) only for tables that have fragmentation.
It was very useful to me! 🙂
#!/bin/sh echo -n "MySQL username: " ; read username echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do if [ "$datafree" -gt 0 ] ; then fragmentation=$(($datafree * 100 / $datalength)) echo "$database.$name is $fragmentation% fragmented." mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database" fi done done
The resul will be like:
MySQL username: root MySQL password: ... mysql.db is 12% fragmented. mysql.db optimize status OK mysql.user is 9% fragmented. mysql.db optimize status OK ...
This script is a full copy from this post by Robert de Bock.
Thanks, Robert!
Matheus.
Do you know the difference?
current_date(): Only give you the date.
now(): Datetime when the statement,procedure etc… started.
sysdate(): Current datetime.
Take a look between the functions now() and sysdate() after executing sleep of 5 seconds…:
SQL> select current_date(),now(),sysdate(),SLEEP(5),now(),sysdate(); "2016-03-24";"2016-03-24 16:00:43";"2016-03-24 16:00:43";"2016-03-24 16:00:43";"2016-03-24 16:00:48"
Matheus.