1

6

17

WebManiacs Conference 2008 Washington D.C. Oğuz Demirkapı CTO NicheClick Media http://NicheClick.com

About Me CTO at NicheClick Media Coding since ‘85, CGI Programming since ’94 ColdFusion Developer since ’97 Founder and Manager (prev) of CFUG for Turkey Team Fusebox Member Interested in ColdFusion, Flex, AIR, Ajax, Frameworks, i18N, L10N, G11N Have big interest in Epistemology Living in Potomac, MD, moving to Irvine, CA Personal blog: http://blog.demirkapi.net 2 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Agenda What you may expect? What is MySQL? Why MySQL? MySQL Basics MySQL Tips MySQL & ColdFusion Questions & Answers 3 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

What You May Expect? Open Your Eyes, if; Attend another session, if; You are a MySQL newbie You heard that MySQL is a good solution but not involved yet You need an alternative solution for your database needs You want to get some tips You are already using MySQL in production You are a *nix guy who selected MySQL as database solution You are expecting an “Advance MySQL” session You think I know everything about MySQL The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 4

What is MySQL? Relational Database Management System (RDBMS) Dual Licensed Platform Runs in multi platform Linux, MacOS, Windows, Solaris, BSD … Multi-threaded Good multi-processor performance Flexible security model MySQL AB develops and markets a family of high performance, affordable database servers and tools. MySQL is officially part of Sun Microsystems Since February 26, 2008 5 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Dual Licensed Platform Users may choose to use MySQL products under the free software/opensource GNU general Public License (commonly known as the "GPL") or under a commercial license. MySQL software is 100% GPL (General Public License); if yours is 100% GPL compliant, then you have no obligation to pay us for the licenses. This is a great opportunity for the open source community and those of you who are developing open source software. MySQL Commercial License The Commercial License is an agreement with MySQL AB for organizations that do not want to release their application source code. Commercially licensed customers get a commercially supported product with assurances from MySQL. Commercially licensed users are also free from the requirement of making their own application open source. When your application is not licensed under either the GPL-compatible Free software License as defined by the Free Software Foundation or approved by OSI, and you intend to or you may distribute MySQL software, you must first obtain a commercial license to the MySQL product. 6 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Who Use MySQL? Wikipedia Yahoo! YouTube Facebook Flickr Digg Technorati del.icio.us NASA Google Nokia Vodafone Alcatel-Lucent Cox Communications NicheClick Media  and much more ... The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 7

Why MySQL? The MySQL® database has become the world's most popular open source database because of its consistent fast performance, high reliability and ease of use. 8 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Why MySQL? – 2 Scalability and Flexibility Capacity from1 Mb to TeraBytes data Platform flexibility High Performance Configure database server specifically for particular applications High-speed load utilities, distinctive memory caches, full text indexes, and other performance-enhancing mechanisms High Availability Robust Transactional Support Include complete ACID (atomic, consistent, isolated, durable) transaction support, unlimited row-level locking, distributed transaction capability, and multi-version transaction support 9 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Why MySQL? – 3 Web and Data Warehouse Strengths Fast data insert capability Fast full text searches Scales up into the terabyte range Strong Data Protection Powerful mechanisms for ensuring only authorized users have entry to the database server SSH and SSL support A granular object privilege framework Embedded backup and recovery utilities 10 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Why MySQL? – 4 Comprehensive Application Development Stored procedures, triggers, functions, views, cursors, ANSI-standard SQL, and more Management Ease Quick-start capability Open Source Freedom and 24 x 7 Support Lowest Total Cost of Ownership 11 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Why MySQL? – Why I prefer it Handles large files (e.g. 200GB) efficiently Easy to manage MySQL is so lightweight compared to other platforms. ANSI SQL92 (almost!) except: Sub-select SELECT * FROM myTable WHERE id IN (SELECT id FROM mtTable2); SELECT INTO myTable… LIMIT Functionality and efficiency on paging 12 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL 5 Stored Procedures Views Triggers Data Dictionary (INFORMATION_SCHEMA) A new database called INFORMATION_SCHEMA which contains a fair number of tables, each providing details of specific aspects of the MySQL server, table definitions, and so on. Lots of metadata, which you can now access through queries 13 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

What's New in MySQL 6.0 The new Falcon transactional storage engine Support for additional Unicode character sets: utf16, utf32, and 4-byte utf8. BACKUP DATABASE and RESTORE statements for backup and restore operations. Improvements in the INFORMATION_SCHEMA database. 14 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL Storage Engines MyISAM Manages non-transactional tables. It provides high-speed storage and retrieval, as well as full text searching capabilities. InnoDB Provides transaction-safe tables. Memory Provides in-memory tables. NBDCluster The storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. There are some others … 15 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MyISAM vs. InnoDB MyISAM InnoDB Table level locking Best for selects and limited inserts and updates Recovery from crash faster/easier No support for transactions Low overhead on server High performance Select count(*) is fast - keeps up-to-date row count Row level locking Best for concurrent selects, inserts, updates High performance with concurrent connections Has transaction support Uses approx. 3x disk space of MyISAM Select count(*) is slow – must count all rows Creates primary key - whether part of the table or not The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 16

MySQL Database Engine Feature Comparison 17 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

Falcon Is Is Not Transactional MySQL storage engine Based on Netfrastructure database engine Extended and integrated into MySQL The main goals of Falcon are to exploit large memory for more than just a bigger cache, to use threads and processors for data migration. Scaling gracefully to very heavy loads an InnoDB clone Firebird a Firebird clone a standalone database management management system Netfrastructure The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 18

Falcon Engine Falcon is a next-generation transactional management engine designed to run on all popular hardware/architecture platforms, but it is especially designed to take advantage of the large memory caches available in 64-bit environments. Falcon is a natural fit for modern businesses and always online applications that need lightening-fast transaction performance and rock-solid data reliability. Benchmarks http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 19

MySQL Data Types – String Types CHAR( ) A fixed section from 0 to 255 characters long. VARCHAR( ) A variable section from 0 to 255 characters long. TINYTEXT A string with a maximum length of 255 characters. TEXT A string with a maximum length of 65535 characters. BLOB A string with a maximum length of 65535 characters. MEDIUMTEXT A string with a maximum length of 16777215 characters. MEDIUMBLOB A string with a maximum length of 16777215 characters. LONGTEXT A string with a maximum length of 4294967295 characters. LONGBLOB A string with a maximum length of 4294967295 characters. ENUM ( ) Short for ENUMERATION which means that each column may have one of a specified possible values. SET Similar to ENUM except each column may have more than one of the specified possible values. 20 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL Data Types – Number Types TINYINT( ) -128 to 127 normal 0 to 255 UNSIGNED. SMALLINT( ) -32768 to 32767 normal 0 to 65535 UNSIGNED. MEDIUMINT( ) -8388608 to 8388607 normal 0 to 16777215 UNSIGNED. INT( ) -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED. BIGINT( ) -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED. FLOAT A small number with a floating decimal point. DOUBLE( , ) A large number with a floating decimal point. DECIMAL( , ) A DOUBLE stored as a string , allowing for a fixed decimal point. 21 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL Data Types – DateTypes DATE YYYY-MM-DD. DATETIME YYYY-MM-DD HH:MM:SS. TIMESTAMP YYYYMMDDHHMMSS. TIME HH:MM:SS. 22 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL Data Types – Unicode Storage The number of bytes required per character varies according to the character set used. Each character requires 1 bytes storage in latin1 Each character requires 3 bytes storage in utf8 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 23

MySQL Installation Easy on *nix platforms Mostly one command debian:~# apt-get install mysql-client-5.0 mysql-common-5.0 mysql-server-5.0 Installer on Windows Platforms http://dev.mysql.com/downloads/ The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 24

MySQL Basics – Command Line 1 Connecting Server mysql -h hostname -u root –p Listing all databases on the server show databases; Listing database's field formats describe myTable; Creating a New Database CREATE DATABASE myDatabase CHARACTER SET utf8; The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 25

MySQL Basics – Command Line 2 Switch to a database use myDatabase; Listing all the tables in the db show tables; Creating a table CREATE TABLE myTable (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Adding a New Field into a Table ALTER TABLE myTable ADD myField VARCHAR(50); The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 26

MySQL Basics – Command Line 3 Listing Current Processes SHOW PROCESSLIST; Removing Process KILL process_number; Repairing Tables mysqlcheck --all-databases mysqlcheck --all-databases --fast The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 27

MySQL Basics – Command Line 4 Loading tab-delimited data into a table LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE myTable; Performing Search-and-Replace Actions on a Table update myDatabase set myTable = REPLACE(myTable ,"[String to Search For]","[String Replacement]"); The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 28

MySQL Basics - Users and Privileges GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password‘; GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password'; REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions SET PASSWORD = PASSWORD('new_pass') SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass') SET PASSWORD = OLD_PASSWORD('new_pass') DROP USER 'user'@'host‘ host ‘%’ indicates any host. The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 29

MySQL Basics – BackUp & Restore BackUp mysqldump -u Username -p myDatabase > myDatabase_backup.sql mysqldump --all-databases > all_backup.sql Do not forget; functions, triggers, and views will not export with with ‘mysqldump’ Restore mysql - u username -p myDatabase < myDatabase_backup.sql The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 30

MySQL Basics – Reset Root Stop MySQL $ /etc/init.d/mysql stop Deactivate Rights $ mysqld_safe --skip-grant-tables Update Password mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root'; Restart MySQL $ /etc/init.d/mysql start The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 31

MySQL Basics – Some More ... Alter Table ALTER TABLE T MODIFY C Definition Delete Column ALTER TABLE T DROP COLUMN C Get Identity after insert SELECT @@identity FROM Table Change Field Order ALTER TABLE myTable MODIFY field1 type1 FIRST ALTER TABLE myTable MODIFY field1 type1 AFTER anotherField Create Index ALTER TABLE myTable ADD INDEX (field1(4)) 32 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL Tips First TIMESTAMP field is automatically set to current date/time whenever record is updated Last change time can be a very useful per-record property. Format is ‘YYYYmmddHHMMSS’, e.g. 20010621142532 Access is allow/deny at a global, database, table or column level Username / password (and optionally client hostname) checked before any commands are accepted; Use EXPLAIN SELECT to discover slow-downs maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE) The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 33

MySQL GUI Tools MySQL GUI Tools http://dev.mysql.com/downloads/gui-tools/ EMS SQL Manager for MySQL Freeware http://www.sqlmanager.net/en/products/mysql/manager phpMyAdmin http://www.phpmyadmin.net SQLyog http://www.webyog.com Navicat http://navicat.com 34 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL GUI Tools - Management MySQL GUI Tools 35 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net

MySQL & ColdFusion ColdFusion 5 can use ODBC to connect MySQL ColdFusion MX (6-7) has native MySQL 3.x support MySQL 4.x & 5.x can be used via MySQL JDBC drivers http://www.adobe.com/go/6ef0253 http://www.howtoforge.com/coldfusion7_mysql4.1_connection ColdFusion 8 has native MySQL (3/4/5) drivers The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 36

MySQL & ColdFusion – Settings ColdFusion 7 & MySQL 4 & 5 Install JDBC driver first and use it The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 37

MySQL & ColdFusion – Settings ColdFusion 8 The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 38

Resources MySQL Website – http://mysql.com MySQL Forge - http://forge.mysql.com Mailing Lists - http://lists.mysql.com Planet MySQL - http://www.planetmysql.org http://www.mysqlperformanceblog.com http://www.howtoforge.com/howtos/mysql ColdFusion & Databases http://www.adobe.com/devnet/coldfusion/databases.html My blog - http://blog.demirkapi.net The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 39

Questions & Answers ? Oğuz Demirkapı http://blog.demirkapi.net webmaniacs@demirkapi.net The Power of MySQL | Oğuz Demirkapı | http://blog.demirkapi.net 40