MySQL Getting Started

1. MySQL Getting Started BCIS 3680 Enterprise Programming

2. Prepare to Use MySQL • To ensure that (a) the changes you make to MySQL databases will be saved if you use lab computers, and (b) you can share the same database files between lab and home computers (or between multiple computers you own), you need to do the following: • Copy the default data folders to your USB drive. • You only need to do this once, whether you do it on lab or home computer. • Change MySQL Server’s default database directory to the one on your USB drive. • You only need to do this once if you use your own computer. • You must do this every time you use the lab computer.

3. Duplicating DB Folder • Browse to the default database folder of MySQL. • Right-click the data subfolder and select “Copy” (do not open the folder). • You may need to change your Windows Explorer setting to show hidden files and folders. Right-click and select “Copy” We will come back and edit this file later.

4. Default DB Folder • Windows 8 & Windows 7 C:\ProgramData\MySQL\MySQL Server 5.6\data • C:\programData is a hidden folder. Change the Windows Explorer setting to show hidden folders and files. • Windows Vista C:\Users\All Users\MySQL\MySQL Server 5.6\data • Windows XP C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6\data • C:\Documents and Settings\All Users\Application Data is a hidden folder. Change the Windows Explorer setting to show hidden folders and files.

5. Duplicating DB Folder • On your USB drive, create a folder called BCIS3680 (if you are currently using a folder of this name for you JSPs, then use a different folder, such as BCIS3680DB). • Open BCIS3680. • Right-click and select “Paste”. • Now you have copied the data directory from the default location to your USB drive.

6. Start Console with Admin Rights As with Tomcat, start a command console with administrator rights if you use Windows Vista or higher

7. Start Console with Admin Rights

8. Changing MySQL Default Data Folder • Before you use MySQL on the lab computer, you must do the following so that changes to databases will be made to your USB drive, not the lab computer. • Run net stop mysql • Wait until you see the message saying that the service was stopped successfully.

9. Editing my.ini • At the same level of the \data subfolder, find the file by the name my.ini. • Start Notepad with admin rights to edit it. Do not double-click. Instead, open Notepad with administrator rights. Then, inside Notepad, click File | Open. Browse to and open this file.

10. Start NotePad with Admin Rights Again, open Notepad with administrator rights if you use Windows Vista or higher

11. Start NotePad with Admin Rights

12. Editing my.ini • Open the my.ini file, scroll down to find the [mysqld] section. • Find the line starting with datadir="C:/Documents… • Add a # sign at the very beginning of that line to “comment it out”. • Below that line, add a new line that says: datadir="E:/BCIS3680/data/" • This should be the path to the folder you used in Slide #5, so make changes if your folder is different from the above example. • Use forward slashes instead of backward slashes. • Don’t modify anything else in that file! If you think you have done so by mistake, close it without saving and start over again.

13. Edited my.ini • Once you’re done, save the file and close the window.

14. Restarting MySQL Server • Go back to the command console or open a new one. • Run net start mysql • Wait until you see the message saying that the service was started successfully.

15. Verifying Change of datadir • Run mysql –u root –p • If you work on a lab computer, when prompted, enter bcis3680(or whatever password you set) as your password. • You should be able to log in as the root user.

16. Verifying Change of datadir • Run show variables like 'datadir'; • If you see E:\BCIS3680\data\ in the value column (note that in the example it’s C:\data\), then you have successfully changed the database directory for the current session to the folder on your USB drive.

17. Log in to MySQL • Run mysql –u root –pto log in • -u option for username • -p will enter password • Run use <databaseName>; to select the database you want to work with. • To log out, type either • \q • exit • Quit or • press Ctrl + C

18. MySQL Commands • A MySQL command ends with a semicolon, except: quit, exit • In comparison, the net stop, net start, and mysql commands are Windows commands and appending semicolon to them would generate an error. • You may split a complex, long command over multiple lines. It will not run until you hit the ; key and then Enter. • For even more complex input of commands, you may group the commands into a script file. Then run source <ScriptFileName>;to execute the script.

19. The Forta Database • Download the forta scripts from the course site. • Use these scripts from my website instead of those from the author/publisher. • I modified the scripts so that the database so created can be accessed from both the lab and your home computers without problems. • Create a database called forta in MySQL. • Unzip and run the two scripts (create.sql and populate.sql) to create and populate tables in that database.

20. Create the Forta Database • Run create database forta; to create the forta DB. • Run show databases; to verify.

21. Run Script to Create Tables • Run use forta; to actually work on that DB. • Run source <create_script>to create tables.

22. Run Populate Script to Add Data • Run source <populate_script>to populate the tables. • Now you will be able to follow along while reading the Forta book.

23. Display Names of Tables in DB • Run show tables; to show tables in the forta DB.