If you need to drop the contents of a MySQL database and reimport base data alot (such as a webapp demo or development site) below is a very simple script to make your life easier

-- used to refresh SimpleInvoices.org/demo each hour

#!/bin/bash

MUSER="-- Insert Db username here --"
MPASS="-- Insert Db password here --"
MDB="-- Insert Db name here --"
 FILE="/home/user/path/to/your/import_file.sql"
 
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
 
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
  
for t in $TABLES
do
    echo "Deleting $t table from $MDB database..."
    $MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

cat $FILE | mysql -u $MUSER -p$MPASS $MDB
echo "Sql imported"
 

Hi Guys,

we've got an old sql query that powers the 'Manage Customers' page thats slow as a dog to run.  if anyone has a few minutes to review and rewrite this query so it run faster it would be greatly appreciated.

theres the sql

SELECT
    c.id as CID,
    c.name as name,
    (
        SELECT (CASE  WHEN c.enabled = 0 THEN 'Disabled' ELSE 'Enabled' END )
     ) AS enabled,
    (
        SELECT
            coalesce(sum(ii.total),  0) AS total
        FROM
            si_invoice_items ii INNER JOIN
            si_invoices iv ON (iv.id = ii.invoice_id)
         WHERE
        iv.customer_id  = CID 
    ) as customer_total,
    (
        SELECT
            coalesce(sum(ap.ac_amount), 0) AS amount
        FROM
            si_payment ap INNER JOIN
            si_invoices iv ON (iv.id = ap.ac_inv_id)
         WHERE
            iv.customer_id = CID
    ) AS paid,
    ( 
        select customer_total - paid 
    ) AS owing
FROM
    si_customers c
WHERE 
    c.domain_id = '1'
ORDER BY
     name asc;

just post your improve query in the Simple Invoices forum here

Cheers

Justin

for whatever reason when i edited my.cnf on CentSO 5.3 the logging options were never picked up no matter what i tried

gave up messing with my.cnf and put them directly in the init.d file and all was well

1 - make sure that the log files are available and writeable by the mysql user

2 - edit /etc/init.d/mysqld

find the mysqld_safe section and add the --log** sections in as below

        # but we need to be sure.
        /usr/bin/mysqld_safe   --datadir="$datadir" --socket="$socketfile" \
                --log-error="$errlogfile" --pid-file="$mypidfile" \
                --log=/var/log/mysql.log \
                --log-slow-queries \
                --slow_query_log_file=/var/log/mysql-slow.log \
                --federated \
                --user=mysql >/dev/null 2>&1 &

               
3 - sudo /etc/init.d/mysql restart

and your off logging as normal

note: the general log ( --log=/var/log/mysql.log \ ) will slow this down

just setup mysql on centos and forgot to set it to auto-start on boot - there the quick fix

sudo /sbin/chkconfig --level 2345 mysqld on

/sbin/chkconfig --list

should result in the below output

mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off