ECA 236

1. ECA 236 Open Source Server Side Scripting Intro to MySQL Open Source Server Side Scripting

2. test MySQL • to test from the command line on Windows platform, change the directory: c:\mysql\bin • or change the PATH • start the MySQL server mysqld • or use the Windows utility, winmysqladmin, to start the MySQL server Open Source Server Side Scripting

3. test MySQL cont … • to test the installation, at the command line type: mysqlshow # returns a list of databases mysqlshow mysql # returns the tables in the database named mysql mysqladmin version status proc # returns current status / information re: MySQL Open Source Server Side Scripting

4. MySQL server • through administration of the MySQL server we can: • create databases • drop databases • create users • set permissions • etc Open Source Server Side Scripting

5. MySQL privileges • MySQL uses a system of privileges to allow users access to do certain things • we do not want all users to have the same level of privileges • regular users should not have the power to create, delete, or change the structure of a database, or even use any other database on the server Open Source Server Side Scripting

6. MySQL configuration • the default configuration of MySQL is not ideal • to make MySQL more secure we will • set a password for the root user • delete the anonymous user Open Source Server Side Scripting

7. root user • MySQL comes with a superuser account named root, which has all the possible privileges to administer the server • by default, root does not have an associated password • without a password, virtually anyone could log on to the server as root and use administrative privileges Open Source Server Side Scripting

8. root user cont … • when signing in at the command line as a particular user we use a flag ( -u ) followed by the name of the user • without a password, anyone can log on as root • remember, the mysql monitor is a way to interact with the MySQL server through a command line interface mysql -u root Open Source Server Side Scripting

9. setting the root password • at the mysql prompt type: • remember, all commands must end with a semicolon • SQL commands, which we will look at shortly, are generally written in upper case to differentiate them from database, table, and column names use mysql;UPDATE user SET password=PASSWORD(‘welcome’) WHERE user=‘root’;FLUSH PRIVILEGES; Open Source Server Side Scripting

10. setting the root password cont … • new versions of MySQL (4.1 and up) have changed the passwords from 16 to 32 bits • incompatible with older versions of PHP • older versions of PHP use 16 bit passwords • use MySQL OLD_PASSWORD( ) function use mysql;UPDATE user SET password=OLD_PASSWORD(‘welcome’) WHERE user=‘root’;FLUSH PRIVILEGES; Open Source Server Side Scripting

11. setting the root password cont … • new versions of MySQL (4.1 and up) have changed the passwords from 16 to 32 bits • use OLD_PASSWORD if you get an error similar to: Client does not support authentication protocol requested by server; consider upgrading MySQL client … Open Source Server Side Scripting

12. setting the root password cont … • the use keyword tells MySQL which database we will be working with • at the time of installation, two databases are created • mysql • contains grant tables, used to store information about privileges • test • a simple test database use mysql; Open Source Server Side Scripting

13. setting the root password cont … • passwords should not be saved as unencrypted text • UPDATE is a SQL command which allows us to modify data in a table • user is a table in the mysql database which stores information about users, including associated passwords UPDATE user SET password=PASSWORD(‘welcome’) WHERE user=‘root’; Open Source Server Side Scripting

14. setting the root password cont … UPDATE user SET password=PASSWORD(‘welcome’) WHERE user=‘root’; • PASSWORD( ) is a function to encrypt the actual string password • encrypted string is always 16 characters long • PASSWORD( ) uses a one-way encryption algorithm, meaning there is no way to unencrypt it • output of PASSWORD( ) is not arbitrary – the same word will result in the same encrypted string each time • EG, “welcome” will be encrypted as 4326ef3a2d0d0116 Open Source Server Side Scripting

15. setting the root password cont … UPDATE user SET password=PASSWORD(‘welcome’) WHERE user=‘root’; • WHERE limits which records to return • conditional operators such as the equal sign are used to create a condition • here we are testing to make sure the user’s name is root • other conditional operators include > < BETWEEN NOT BETWEEN etc Open Source Server Side Scripting

16. setting the root password cont … • after you make certain changes to the grant tables using UPDATE, the changes do not automatically take effect • FLUSH PRIVILEGES lets the server know that changes have taken place • it resets the list of acceptable users and their privileges FLUSH PRIVILEGES; Open Source Server Side Scripting

17. setting the root password cont … • from this point on, tasks that previously did not require the use of a password will not work without one • administrative tasks with mysqladmin • signing on to mysql monitor • include the -u flag followed by the username • include the -p flag to indicate a password is being used mysql -u root -p Open Source Server Side Scripting

18. setting the root password cont … • do not type the actual password after the -p flag, although doing so is possible • MySQL will prompt you for a password, which will be overwritten by asterisks bin>mysql -u root -pEnter password: ******* Open Source Server Side Scripting

19. deleting anonymous user • when MySQL is installed, an anonymous user is created • the anonymous user can access the system without supplying a username or password two single quotes DELETE command mysql>DELETE FROM user WHERE user=‘’; FROM user table user is equal to anonymous Open Source Server Side Scripting

20. planning your database • plan your database • what tables will the database contain? • what field will each table contain? • what type of information will each field contain? • we will create a database named sitename which contains: • user ID • last name • first name • email address • password • registration date Open Source Server Side Scripting

21. MySQL naming rules • when naming fields the following rules apply: • you must use names containing • alphanumeric characters • underscore • no spaces • correct: • bob_inventory • incorrect • bob’s inventory Open Source Server Side Scripting

22. data types • when creating the database, MySQL requires that you define a data type for each field • MySQL’s three main categories of data: • Text • Numbers • Date & Times • choosing data type determines what kind of data can be stored, and overall performance of database Open Source Server Side Scripting

23. text data types CHAR and VARCHAR can take a (length) attribute, limiting their total size – length is optional for CHAR, required for VARCHAR Open Source Server Side Scripting

24. CHAR vs VARCHAR • both store can be set to a maximum length • CHAR will always be stored as a string the length of your maximum length. Spaces are used to pad any string that is less than the max • VARCHAR will be stored as a string which is only as long as the string itself Open Source Server Side Scripting

25. number data types Open Source Server Side Scripting

26. number data types cont … • many number data types take optional [length] • SIGNED • able to take negative values • UNSIGNED • only positive values and zero • ZEROFILL • extra spaces padded with zeros, automatically unsigned Open Source Server Side Scripting

27. date data types for more information about date data types, visit the MySQL manual, www.mysql.com Open Source Server Side Scripting

28. two special types Open Source Server Side Scripting

29. NULL and NOT NULL • NULL means the field has no value, not even an empty string • NULL • no value is required, field can be empty • NOT NULL • value is required, field cannot be empty • default values • use default followed by the default value Open Source Server Side Scripting

30. designing users for sitename • the database sitename will contain one table named users, which contains the following fields Open Source Server Side Scripting

31. designing users for sitenamecont… • identify whether those fields should be a text, number, or date data type Open Source Server Side Scripting

32. designing users for sitenamecont… • choose a subtype for each field Open Source Server Side Scripting

33. designing users for sitenamecont… • set maximum length and other attributes for each column Open Source Server Side Scripting

34. primary key • to be a PRIMARY KEY a column must meet three conditions: • it must always contain a value ( NOT NULL ) • the value must never change • the value must be unique for each record • the PRIMARY KEY for the users table will be the field user_id Open Source Server Side Scripting

35. auto increment • to insure that each record in user_id is unique, use the AUTO_INCREMENT statement • every time a record is added, the value in user_id will automatically be incremented Open Source Server Side Scripting

36. create sitename • start MySQL • sign onto the mysql monitor as the root user • create the sitename database • use the sitename database CREATE DATABASE sitename; USE sitename; Open Source Server Side Scripting

37. creating a table • the CREATE statement is also used to create a table, once we indicate which database to use • syntax to create a table: • after naming the table, name and define each column in order, within parentheses • separate columns/descriptions with a comma CREATE TABLE tablename (column1name description, column2name description, …); Open Source Server Side Scripting

38. create table users • syntax: CREATE TABLE users (user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,first_name VARCHAR(15) NOT NULL,last_name VARCHAR(30) NOT NULL,email VARCHAR(40),password CHAR(16) NOT NULL,registration_date DATETIME NOT NULL,PRIMARY KEY (user_id)); Open Source Server Side Scripting

39. confirm table creation • confirm the existence of the table: • confirm the creation of the columns: SHOW TABLES; SHOW COLUMNS FROM users; ~~~ or ~~~ DESCRIBE users; Open Source Server Side Scripting

40. backing up a database • mysqldump • will back up tables and their structure • run directly from command line • dump file to the screen: mysqldump -u root -p sitename Open Source Server Side Scripting

41. backing up a database cont … • mysqldump • create an output file • contains SQL commands to create the table • contains data to populate the table mysqldump -u root -p sitename > c:/data/mydump.sql Open Source Server Side Scripting

42. backing up a database cont … • mysqldump • read the file back into MySQL using the syntax: • review MySQL Manual for a long list of options to use with mysqldump mysql -u root -p sitename < c:/data/mydump.sql Open Source Server Side Scripting