Backup MySQL

$ mysqldump -u username -p password --all-databases | gzip > /home/bigbang/Desktop/`date +%Y-%m-%d`_all_database_backup.sql.gz

crontab:

* * * * * /opt/lampp/bin/mysqldump -u root --all-databases | gzip > /opt/lampp/database_backup/`date +\%Y-\%m-\%d`_all_database_backup.sql.gz

for the date input to work in the crontab you must escape each param with a ‘\’ ==>`date +\%Y-\%m-\%d`

More on backing up MySQL and an explanation, however the latter link is not 100% correct information on the date outputs mentioned above.

To import backup:

$mysql -pPASSWORD -uUSERNAME DATABASENAME < /file/location/file.sql

tag:
backing up mysql, export, import

Run python script with crontab

1. Create Python script, and make sure it works
script.py:

import os
# it has to be the full path not ./location or else the crontab wont work
f = open('/home/user/test.txt','a') #open file to append, create it if doesn't exist
f.write("balls\n") #write

2. Edit crontab
$ crontab -e

3. Then it will ask what editor you wish to use. I selected nano.

4. Insert into file
# every minute
* * * * * python /home/user/script.py

# a random cron example
* * * * * uptime > /opt/lampp/database_backup/text.txt

ubuntu cronhowto link

For the log, cron ran:
$ tail -f /var/log/syslog | grep CRON

Create saved query in MySQL, nesting

Microsoft Access has a nice feature which lets you create queries and also link them together. This can also be done with other platforms like MySQL however the difference is individual queries cannot be saved and later linked, it must be all done in one script. Its basically multiple “SELECT * FROM” in one query.

Examples
Microsoft Access:
SELECT *
FROM table
INNER JOIN saved_query
ON table.field = saved_query.field;

MySQL:
SELECT *
FROM table as T
INNER JOIN
(SELECT * FROM another_table) as P
ON T.field = P.field

The difference between the two examples is MySQL needs to nest any additional query and also name each (eg: as T).

It’s a lot easier for me to write a query in Access. What I did was take the Access SQL code and replace (copy and paste) the saved queries with the full code.  See the complex example below.

Access code:
SELECT year.Year, [how many dividends were paid a year].*, [Price average per year].ticker, [Price average per year].AvgOfhigh, [how many dividends were paid a year].[sumofdividend]/[Price average per year].[AvgOfhigh]*100 AS yield
FROM ([year]
LEFT JOIN [how many dividends were paid a year]
ON year.Year = [how many dividends were paid a year].year)
LEFT JOIN [Price average per year]
ON year.Year = [Price average per year].year
WHERE ((([Price average per year].ticker)=[how many dividends were paid a year].[ticker]))
ORDER BY [Price average per year].ticker;

MySQL code:
SELECT t.Year, p.*, k.ticker, k.AvgOfhigh, p.`sumofdividend`/k.`AvgOfhigh`*100 AS yield
FROM (year as t
LEFT JOIN (SELECT dividend.ticker, Count(dividend.ticker) AS CountOfticker, Left(dividend.Date,4) AS year, Sum(dividend.dividend) AS SumOfdividend
FROM dividend
GROUP BY dividend.ticker, Left(dividend.Date,4)
ORDER BY dividend.ticker ) as p
ON t.Year = p.year)
LEFT JOIN (SELECT price.ticker, Left(price.Date,4) AS year, Avg(price.high) AS AvgOfhigh
FROM price
GROUP BY price.ticker, Left(price.Date,4)) as K
ON t.Year = k.year
WHERE (((k.ticker)=p.ticker))
ORDER BY k.ticker

the most common error I encounter during the conversion was that semicolon(;) and MySQL doesn’t use brackets([])

Tags:
inner join another query,  create saved query in mysql, link queries, relationship multiple queries, connect query to one another, save query as table
nesting

Connect Access 2010 to MySQL

If you want to be able to use the tools found in Microsoft Access 2010 with your MySQL data here is how to do it.

1. You need download and install Connector/ODBC
2. Once installed open Access and click on OBDC Database under External Data, and select “Link to the data source by creating a linked table”.

3. Under “Machine Data Source” click “New”

4. Click Next

5. Select MySQL OBDC 3.51 Driver and click Next

6. Click Finish

7. Input your database data

 

For additional information go to http://www.washington.edu/itconnect/web/publishing/mysql-access-3.5.html

Creator not the Teacher

“Don’t who can’t do, teach”. All the teachers in the world whether its your college professor, manager at your job, your friend, bloggers and so on cannot do because if they could they wouldn’t teach. Many may disagree because you need to know first the how before you can teach. But why would a successful person in the area of business waste their time teaching others to be their competitors. One may be fooled by the fact they enjoy helping people but the reality is they got off the elevator and decided to settle. If you were given the option to run a business were it paid a huge sum of money, which will you do? Teach or create? Would you be a Steve Jobs or Steve Wozniak? For me it would be to run a business because teaching a bunch of 5th graders is the easy way out.

Sometimes I get caught up with the teaching stuff too much and lose track of what I’m really trying to accomplish. Yes, teaching will build you an audience but that’s where it needs to be left. I don’t want to be a teacher but an experience sharer and feed off the insight of others. I want to be the creator not the teacher.