SQL

For clients needing to perform SQL operations on their JMC databases, here are some resources that you might find handy.

Making backups of your data ensures data integrity in the case of a system failure. Listed below are the steps required to make a backup of your JMC SQL database.

We recommend creating and restoring backups from the same computer that the MySQL server is running on. Otherwise your backups will take a long time to execute. Also you will want to keep in mind that backup sizes are typically 50-150MB and they grow larger over time. Please ensure that you have plenty of disk space.

CREATING A BACKUP
To make a backup you need MySQL Administrator. Login with your SQL username and password. You can use your root account or your standard user account, as long as the account has full privileges. (This can be checked in User Accounts/User Administration within MySQL Administrator.)

                              Windows Server
Create a backup project. Go to the Backup section in MySQL Administrator. Create a new project and give it a name, such as 'JMC Backup'. Click on the database named JMC and use the right arrow '>' to copy your database to the window on the right.

Click Save Project.

Click on the Advanced Options tab. Select InnoDB Online Backup. Select Complete Backup. Now select the following options (and ONLY these options).

  • Add DROP Statements
  • Complete INSERTS
  • Comment
  • Disable keys

Click Save Project.

You can now begin the Backup procedure. Click the Start Backup button to begin. Make sure you have sufficient privileges to the backup location or you will not be able to create a backup. When the process is finished you will have created a backup file containing all data from your database.

 

 

                              Macintosh Server
Create a backup project. Go to the Backup section in MySQL Administrator. Create a new project and give it a name, such as 'JMC Backup'. Click on the database named JMC and use the right arrow '>' to copy your database to the window on the right.

Click on the Advanced Options tab. Select InnoDB Online Backup. Select Backup Selected Schema Completely. Now select the following options (and ONLY these options).
  • Add DROP TABLE
  • Complete INSERTS
  • Include Comments
  • Disable Foreign Keys

You can now begin the Backup procedure. Click the Start Backup button to begin. Make sure you have sufficient privileges to the backup location or you will not be able to create a backup. When the process is finished you will have created a backup file containing all data from your database.

It is recommended that you schedule a daily backup of your SQL database.

To schedule a backup you need MySQL Administrator. Login with your SQL username and password. You can use your root account or your standard user account, as long as the account has full privileges. (This can be checked in User Accounts/User Administration within MySQL Administrator.)

To use the backup scheduler, we will need to create a working stored connection. First you need to enable stored passwords.

                              Windows Server
Under the Tools menu, select Options.
Go to the General Options category and click to enable Store Passwords.
Change the Password Storage Method to Obscured. Click Apply.

Now you can create and save a stored connection.

Go to the Connections category and click the New Connection button.

Give the connection a name, such as 'SQL Admin'. Enter your SQL username and password, the same you used a moment ago to log in. Your hostname should be localhost or this computer's IP address. Set your Schema name. For most clients this is going to be 'JMC'. For clients with multiple non-district buildings, you will need a new connection for each building. Save and Exit.

Now you need to check to ensure that your connection is working. Exit the MySQL Administrator program and re-run it. At the log-in prompt, select your new connection from the pop-up box. You should be able to log in using this stored connection without needing to type any information. If it is not accepting your log-in credentials, log back in and edit your connection properties until it saves all connection information. The scheduler requires this stored connection.

Now use MySQL Administrator to schedule a backup. Go to the Backup feature and select the Schedule tab.

Check the checkbox to enable scheduled backups.

Choose the Target folder in which you want to save the backup file. Make sure you have file writing permissions to this directory. You can also specify a filename, although I prefer to leave it default.

Choose from the pop-up box the interval you wish to automate backups. Edit the corresponding options as well as the Time to backup. Time should be in the form '12:59:59 PM'. Click Save Project. Type in the password for the current logged-in user to this computer, if requested.

Backups are now scheduled. Make sure to go back and ensure that the first backup gets created, and that the backup file is at least a few megabytes in size. Also make sure that you maintain write permissions to the backup folder in the future. If you lose privileges, backups will no longer be created.

 

 

 

                              Macintosh Server
Under the MySQL Administrator menu, select Preferences.
Go to the General tab and change the Password Storage option to Obscured.
Change the Password Storage Method to Obscured. Click Apply.
 
Now we can create and save a stored connection.
 
Go to the Connections tab and click the '+' to create a new connection.
 
Give the connection a name, such as 'SQL Admin'. Enter your SQL username and password, the same you used a moment ago to log in. Your hostname should be 127.0.0.1 or this computer's IP address. Set your Schema name. For most clients this is going to be 'JMC'. For clients with multiple non-district buildings, you will need a new connection for each building. Save and Exit.

Now you need to check to ensure that your connection is working. Exit the MySQL Administrator program and re-run it. At the log-in prompt, select your new connection from the pop-up box. You should be able to log in using this stored connection without needing to type any information. If it is not accepting your log-in credentials, log back in and edit your connection properties until it saves all connection information. The scheduler requires this stored connection.

Now use MySQL Administrator to schedule a backup. Go to the Backup feature and select the Schedule tab.
 
Choose the Target folder in which you want to save the backup file. Make sure you have file writing permissions to this directory. You can also specify a filename, although I prefer to leave it default.

Select the interval from the schedule pop-up as to how often you want to backup. Edit the corresponding options as well as the Time to backup. Time should be in the form '23:59'. Click the Schedule button.

Backups are now scheduled. Make sure to go back and ensure that the first backup gets created, and that the backup file is at least a few megabytes in size. Also make sure that you maintain write permissions to the backup folder in the future. If you lose privileges, backups will no longer be created.

When restoring from a backup, you will overwrite existing tables in your database with tables from your restore file. We recommend making a backup before doing a restore.

To restore a backup you need MySQL Administrator. Login with your SQL username and password. You can use your root account or your standard user account, as long as the account has full privileges. (This can be checked in User Accounts/User Administration within MySQL Administrator.

To restore from a backup, go to the Restore option. Open the backup file you want to restore data from using Choose Backup File button.

                              Windows Server
Once the file is selected you can see the contents of the backup file by clicking the Restore Content tab. Click Analyze Backup File.

You can optionally uncheck tables you don't want restored. Leave these boxes checked when restoring files sent by JMC.

Once you are comfortable with the table selection to be restored, click the Start Restore Backup button. This will overwrite the tables in your database with the tables in your backup file that are selected for backup. Once the processing is done, the backup restoration is complete.

 

                              Macintosh Server
Once the file is selected, it will be scanned for content. Go to the Selection tab to see what tables will be restored.

You can optionally uncheck tables you don't want restored. Leave these boxes checked when restoring files sent by JMC.

Once you are comfortable with the table selection to be restored, click the Start Restore Backup button. This will overwrite the tables in your database with the tables in your backup file that are selected for backup. Once the processing is done, the backup restoration is complete.

1. Call and schedule a time to have the SQL conversion done.

2. Before the conversion, you need to send us a copy of your data. At that point you will also need to stop making changes in your current database, for any new changes will be lost.

3. After you send us data, you can begin setting up for SQL.

    First you will need the following software:

4. Once the conversion is complete, we will send you your new SQL database as a backup file.

5. Install MySQL Server. Most people will use default settings. Write down your MySQL administrator password if you enter one.

6. Install MySQL Administrator. Run it and do the following:
    - Windows: Login using hostname of 'localhost', username 'root', and password from above (or just leave it blank).
    - Mac 10.6 only: Change the folder name of /Applications/MySQL Tools/MySQL Administrator to
            /Applications/MySQL_Tools/MySQL_Administrator.
           By changing the space in the names to underscores we get around a   compatibility issue with Mac 10.6.
    - Mac: Login using hostname of 127.0.0.1, username 'root', and leave the password blank.
    - Windows: Go to Variables>General Parameters.
    - Mac: Go to Options. If prompted for a config file, select mysqld and hit OK. Go to General Parameters.
    - Find Default Storage Engine and change it to InnoDB.
    - Windows: Go to Variables>InnoDB Parameters.
    - Mac: Go to Options>InnoDB Parameters
    - Find the Lock Wait Timeout value and change it to 1.
    - Windows:
        - Go to Backup and click the New Project button at the bottom. 
        - Save it. 
        - Right-click in the schemata window and select 'Create new Schema'. A schema is a database. 
    - Mac:
        - Go to Catalogs. 
        - Click the '+' button to create a new schema. A schema is a database. 
    - Name the new database "JMC". Write this down.

7. MySQL Server 5.1 and MySQL Server 5.5
MySQL Server 5.1 (If you are using MySQL Server 5.5 see below) 
    - Windows:
        - Go to User Administration. 
        - Click 'Add new user' button. 
        - Enter in a username and password and write it down. 
        - Go up to Schema Privileges. Click on JMC on the left and then click the double left arrow to give the user all privileges to           use database JMC.
        - Apply and save.
    - Mac:
        - Go to Accounts.
        - Click the '+' button on the left to create a new user.
        - Enter in a username and password and write it down.
        - Then click on the plus button to the right to add a Host from which to connect. 
            If you are uncertain what to use, add localhost and add %. 
        - Save. 
        - Click on Schema Privileges. 
        - Highlight the % sign.
        - Go under Available Privileges and select and add all privileges to the list of assigned privileges by using the '<' button.
        - Save.
        - Do the same for localhost and other Hosts from which to connect.
    - Go to Restore and click the button to choose a backup file.
    - Browse to the 'backup' database file that you receive from JMC.
    - Leave Options unchecked and click the button to restore the backup file.
    - Once the restore is complete, restart MySQL server to continue. Restarting the server computer
        will also work.

MySQL Server 5.5
  -Download and Install MySQL Workbench. Run it.
  -Click the New Server Instance button and follow the wizard to create
    a new server instance.
  -Connect to the new server instance under Server Administration.
  -Under Users and Privileges, click Add Account.
  -On the right, enter the username and password that you want for this account.
  -Click on the Schema Privileges tab at the top of your page.
  -Click on the new username and click the Add Entry button.
  -Select Any Host (%) and then down below select the schemas
   (databases) that are associated with JMC. Click OK.
  -Click the Select ALL button. Click the Save Changes button.

8. Create a new blank 'data' folder on a server. This folder needs to be accessible by anyone who runs the Office or Online software, just like the old Data folder. This folder does NOT need to be on the same computer as MySQL Server.

    If school has already started for the year, go into your previous Data folder (for the current year)
    and copy the Gradebook Grades folder and the main Teacher folder to the new SQL folder. The
    Gradebook Grades folder is named GRBK_Grades and the main Teacher folder is named similar to '10-11'.

9. The ODBC driver will need to be installed on every computer that uses JMC SQL software. This driver only needs to be installed once.

10. Install the driver. Then do the following setup:
    - Windows 32bit: Go to Start>Run>type in 'odbcad32' and hit Enter. (This can also be found in C:/Windows/System32/)
    - Windows 64bit: Browse to C:/Windows/SysWOW64/ and run odbcad32.exe
    - Mac: Open Applications>Utilities>ODBC Administrator.
    - Select User DSN, then click the 'Add' button.
    - Select the MySQL driver you just installed, then continue.
    - Type in "JMC" for your DSN. Write this down.
    - Type in the IP address for your server running SQL. If it is running on the same machine as the
        client use "localhost" as the IP address.
    - Type in the username and password of the MySQL user you created in step 6.
    - Enter the database name of "JMC" that you created in step 6.
    - Click 'test' to ensure connectivity.
    - Once you successfully connect, save and exit the manager.

11. If you are running Windows Online software, do step 9 again to add a new System (not User) DSN with the same information.

12. You are now ready to run JMC SQL software. You will be prompted for a DSN, username, and
        password (that was setup in the ODBC driver configuration) to be able to connect to the MySQL Server.

13. If you have any trouble with these steps please contact JMC. Once again, thanks for using JMC and have a great year!



MySQL Server:
    Admin Username        __________________________

    Admin Password        __________________________

    Database Name        __________________________


ODBC Setup:
    User username        __________________________

    User password        __________________________

    DSN            __________________________