Learn SQL with these Online Emulators

SQLCourse has been around for sometime.   With Microsoft’s DreamSpark, students can download SQL Server to build databases and to learn how to retrieve data by performing queries  (basically asking the database for specific data).   SQLCourse gives you step by step instructions on how to run these queries.

SQLCourse

MySQL Wworkbench – manage your MySQL installation

If you are a beginner or an expert, MySQL Workbench is a must have if you have installed MySQL.  The community edition allows you to Start/Stop your MySQL instance, write queries, monitor performance and has dozens of other features.

With MySQL, the my.conf file normally found in Linux installs is the my.ini file.  This configuration file can be found under the ProgramData folder (you have to show hidden files in Windows Explorer to see it).   This file can be configured using MySQL Workbench.   If you get an error connecting to it, go to the server instance and navigate to the my.ini file.   This is a common error when reading the configuration file.

WorkBench Status

Other features -

SQL Editor – SQL Code Completion, SQL Code Formatter, SQL Syntax Highlighting, SQL Code Generation, SQL History, SQL Snippets, Server-Stop/Start, Server Status, Performance and more.

WorkBench One

 

 

MySQL Upgrade in Windows

Dawn and I faced a MySQL Upgrade for our Moodle LMS site and delayed as long as we could.   Unlike traditional upgrades of Microsoft SQL, we couldn’t simply click a setup file and the upgrade would replace files and services  as needed.

Here’s the easiest process we found to upgrade MySQL

  1. Stop the existing MySQL service in Services
  2. Make sure you have a backup  of you MySQL database
  3. Open a command prompt (as administrator)
  4. change to the MySQL directory and type mysqld –remove  (to remove the service under services) you can also remove it by using the SC Delete MySQL command.
    1. In the Service manager you should see that the service is removed.
  5. Uninstall MySQL under the Programs and Features menu
  6. Delete all folders under programdata and program files that are related to MySQL
  7. Reboot
  8. Make sure the latest .NET Framework is installed
  9. Download and run the MySQL Community package  Link
  10. Perform a new install
  11. Use the Server type:  Server  (Production deployments)
  12. Make sure your data directories are specified during the installation
  13. Use the same root password you had on the old installation
  14. Add  admin users as necessary
  15. Restore your files if necessary (backups) with MySQL Workbench Link
  16. Complete the install
  17. Make sure the service is running

Note: Always make a backup before trying to update the database.

 

SQLPanel

Optimizing MySQL on Server 2008 R2

Server 2008 R2

Optimizing IIS
(http://msdn.microsoft.com/en-us/library/ee377050(v=bts.10).aspx)

Edit the my.ini file found in the MySQL installation

http://dev.mysql.com/doc/refman/5.0/en/optimization.html

  • Set the innodb_buffer_pool_size=1G   Resource
  • Set the Max_Allowed_Packet = 100M
  • Set Key_Buffer_Size=512M
  • Set query_cache_size = 128MB
  • query_cache_limit = 4MB

  • table_cache=512
  • tmp_table_size = 64MB

Adjust your settings accordingly.

Note: Many of these settings can be made to the my.cnf file in Linux installations

Our performance increast?  50% savings on average (load times)

Chart

MySQL problem writing to database during cron backup

If you receive the error “!!!Error Writing To Database!!!” during a cron backup, this can be related to

Find the my.ini file which is normally located in the MySql folder under program files
(C:\Program Files\MySQL\MySQL Server 5.1).  Edit it by adding or modifying the line -

Max_Allowed_Packet = 100M   (adjust as necessary)

MaxAllowedPacket