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:
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…
Recently I caught a bunch of connections in ‘TIME_WAIT’ on a MySQL Server through ‘netstat – antp 3306’…
After some time, we identified this was caused by the environment not using DNS, only fixed IPS (uuugh!)…
As you know, for security measures MySQL maintains a host cache for connections established. From MySQL docs:
“For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.”
184.108.40.206 DNS Lookup Optimization and the Host Cache
For this reason, there is a DNS ‘reverse’ lookup for each login was hanging this connections.
Right way: Add an A type registry in DNS for the hosts. Use DNS!
Quick way: Add on /etc/hosts from database server the mapping for the connected hosts, avoiding the DNS Lookup.
Quicker way: Setting the skip-name-resolve variable at /etc/my.cnf. This variable avoids this behavior in database layer for new connections and solve the problem.
This is a good (portuguese) post about it: http://wagnerbianchi.com/blog/?p=831
cat /var/log/mysqld.log |grep `date +%y%m%d` | grep "\[Warning\]"
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`
MySQL: Unable to connect to database 'xxx' on server 'xxx' on port xx with user 'root' - Access denied for user 'root'@'xxxxx'
GRANT ALL PRIVILEGES ON *.* TO root@'xxxxx' IDENTIFIED BY '$PASSWORD' WITH GRANT OPTION;
Have a nice week!
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:
Good one by Sheeri Cabral and posted here!
Any way, with theese two we can make lots of magic:
– Check status of MySql server (slow queries, etc)
– Queries per second graph
– 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:
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! 🙂
We all faced that situation when we have to make a logical backup/export and haven’t so much area to do that, right?
We know the export usually compress a lot on zip/gzip… It wouldn’t be great if we can export directly to compressed file?
This situation become much more common because of Datapump, that requires a directory accessible by database server. If you have not possibility to make a mounting point or any other area, this can help…
## BKP with MKNOD
mknod bkp_$DATE.dmp p
gzip bkp_$DATE.dmp.gz &
### Uncomment and Ajust one of:
#mysqldump -u $user -p$password $database > bkp_$DATE.dmp
## Oracle (Datapump or EXP)
expdp \"/ as sysdba\" dumpfile=bkp_$DATE.dmp full=y directory=DIRECTORY_EXAMPLE logfile=log_bkpzipped.log compress=y
#expdp $user/$password dumpfile=bkp_$DATE.dmp full=y directory=DIRECTORY_EXAMPLE logfile=log_bkpzipped.log
#exp \"/ as sysdba\" file=bkp_$DATE.dmp log=log_bkpzipped.log compress=y [tables=owner.table,..] [owner=schema1,..] [...]
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).
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! 🙂
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"
The resul will be like:
MySQL username: root
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.