mySQL
The database that gets installed in a typical LAMP build.
Hints and tips.
Flightairmap was/is using huge amounts of CPU.
mysqladmin -u adsb -p -i 1 processlist
sudo apt-get install mysql-server
If you are running PHP you will also need to install the php module for mysql 5
sudo apt-get install php5-mysql
sudo apt-get install phpmyadmin
Remote connections
Attempting to create an ODBC connection to the MySQL server from a remote PC was giving errors.
Use phpmyadmin to create a new username and password that could connect from any host (“%”) but connection attempts still failed.
netstat -an showed MySQL server was configured only to listen to TCP port 3306 connection requests from the localhost.
Edit /etc/mysql/my.cnf commenting out the “bind-address=127.0.0.1” line & restarted the mysql service.
You can then create an ODBC connection from your remote host.
If you have trouble with the ODBC client not connecting, but the Windows Test button working.
Look at adding skip_name_resolve to the my.conf file (may not even be in there, just add it anywhere).
Large MySQL DB’s tip If you need to upload largish db’s via phpmyadmin, you will need to bump up the script size limit in the php.ini file. Do a search for the text ‘post_max_size’ and ‘upload_max_filesize’, change them to something like 30m (but check the size of the file), also note that if you are working over a slow network, you might have to bump up the ‘max_input_time as well, then restart apache.
Backing up
Sooner or latter you need to back up your databases.
http://www.swordsky.com/
http://www.ahsay.com/jsp/en/home/index.jsp?rf=www.google.com&kw=ahsay is what Gary uses, its supposed to have a free for home use version, but a bit of look reveals a fair bit of confusion. Im interested in sticking with this one as it is supposed to do live mySQL backups which is what I need for the photo gallery.
http://sypex.net/en/
http://www.databasejournal.com/features/mysql/article.php/3879616/MySQL-Open-Source-Backup-and-Recovery-Alternative-Xtrabackup.htm
http://mysqlbackupftp.com/
http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html
http://www.mysqldumper.net/
http://code.openark.org/blog/mysql/tips-for-taking-mysql-backups-using-lvm
One of the main things I have taken away from my reading is that most methods use the ‘mysqldump’ command, the potential problem with this command is that it locks the db.
Will this be an issue with the photo gallery? I am not sure. How often does it run? I wonder if the dump command would run quick enough that it would dump the db between inserts?
Even if a user is uploading a photo and the app finds a locked db, will it keep retrying?
I’m not sure now what I need……Blah.