0

I'm in the beginning stages of hacking together code that syncs two completely different pieces of software/webware. I'm looking for a way to run a SQL query and output the results to a text file. I'd like this information to be updated daily so I'm leaning towards a cron job.

My current line of thought is to just write a script that logs into the database(phpBB) run the query (All users that have X number of posts) and then export that list of users to a text file.

My question/s:

  1. Do I have to worry about putting the database username/password into this cron script?

  2. Is there another more efficient way for me to get this information from the database without logging into MySQL?

  3. Is Cron jobs appropriate for retrieving information from a database?

  4. Is there any MySQL specific cron/scripting issues I need to be aware of while programing this.

Please keep in mind that I will be using linux to code and execute this script and the MySQL server is hosted on a unix machine.

Improve this question
3
  • What version of MySQL? There are ways of securely connecting in 5.6+ without needing passwords in scripts. Questions 2-4 are matters of opinion. Commented Apr 8, 2015 at 13:03
  • I'm pretty sure that I'm using the newest version. Whats the feature called @PhilSumner whats the feature called where can I get more information? Commented Apr 8, 2015 at 16:04
  • It's an "Option File": dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html Commented Apr 8, 2015 at 16:07

3 Answers 3

Reset to default
0

After talking to someone more knowledgeable then me I have came up with the following answers to my original questions.

TLDR; Cron should be fine for what your doing but don't write a bash script, write a PHP script instead, it's better.

Q1. Do I have to worry about putting the database username/password into this >cron script?

A1. As long as the script is in a safe spot on the server that is running the >>cron it should be fine. I can also create a new database user with very >>limited access specifically for this script.

Q2. Is there another more efficient way for me to get this information from >the database without logging into MySQL?

A2. There is a more efficient way to get the information, MySQLi or PDO via a >>PHP script, but there is no way to get around logging in.

Q3. Is Cron jobs appropriate for retrieving information from a database?

A3. Cron jobs should work fine for running the script on a regular basis.

Q4. Is there any MySQL specific cron/scripting issues I need to be aware of >while programing this.

A4. There isn't any cron specific issues but Bash or any other *nix based >>scripting language shouldn't be used because of limited access to MySQL. >>Instead a php script should be created utilizing either (PDO or MySQLi).

If anyone can answer the "Why" PHP is better then Bash Scripting in this scenario feel free to leave a post or comment.

Improve this answer
0

To check this out, I did the following - I created a file called my_script.sql

[pol@localhost inst]$ more my_script.sql 
use test;
select * from example;
[pol@localhost inst]$

Then ran the following

[pol@localhost inst]$ ./bin/mysql -S ./mysql.sock -u root -pdba < my_script.sql > xfile 
Warning: Using a password on the command line interface can be insecure.
[pol@localhost inst]$

Please note that all the connection parameters won't be necessary if you have a standard MySQL install. Note the password warning - this won't appear if you cron the job, but there is a risk, but see here how you can put your password into your my.cnf file, then the message dissapears.

[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -u root < my_script.sql > xfile
[pol@localhost inst]$

Note - no message.

Then, I ran 

[pol@localhost inst]$ more xfile
id  cname   posted_date posted_time
1   abc 2015-03-26  04:25 PM
1   def 2015-03-27  04:30 PM
2   ghi 2015-03-11  02:25 AM
2   jkl 2015-03-15  12:25 PM
[pol@localhost inst]$

Compare to the result from the MySQL client

mysql> 
mysql> select * from example;
+------+-------+-------------+-------------+
| id   | cname | posted_date | posted_time |
+------+-------+-------------+-------------+
|    1 | abc   | 2015-03-26  | 04:25 PM    |
|    1 | def   | 2015-03-27  | 04:30 PM    |
|    2 | ghi   | 2015-03-11  | 02:25 AM    |
|    2 | jkl   | 2015-03-15  | 12:25 PM    |
+------+-------+-------------+-------------+
4 rows in set (0.00 sec)
mysql>

They are identical.

Just as a final note, if you want to suppress the column headers, just simply run the command with the -N option as shown here

[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -N -u root < my_script.sql > xfile
[pol@localhost inst]$ more xfile
1   abc 2015-03-26  04:25 PM
1   def 2015-03-27  04:30 PM
2   ghi 2015-03-11  02:25 AM
2   jkl 2015-03-15  12:25 PM
[pol@localhost inst]$
Improve this answer
0

You could also use MySQL events (like internal cron) and select into outfile. Advantages of this method? Your backups will contain this logic. Both approaches are valid IMO just depends on your preference.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.