My SQL 5 Part 2

DDL Statements : DDL Statements CREATE ALTER DROP 25/10/2007 1

DDL Statements – Create Database : DDL Statements – Create Database #mysqladmin –u root –pnic123 create Mysqladmin –uroot –pnic123 create cipa or Mysql>create database ; Mysql>create database cipa; Or Go to /var/lib/mysql folder #mkdir cipa #chown –R mysql.mysql /var/lib/mysql/cipa 25/10/2007 2

DDL Statements – DROP Database : DDL Statements – DROP Database #mysqladmin –u root –pnic123 drop Mysqladmin –uroot –pnic123 create cipa or Mysql>DROP database ; Mysql>DROP database cipa; Or Go to /var/lib/mysql folder #rm –rf cipa 25/10/2007 3

DDL Statements – Create Table : DDL Statements – Create Table Create Table Syntax Mysql> CREATE TABLE Mysql>CREATE DATABASE hr Mysql>USE hr; Mysql>CREATE TABLE IF NOT EXISTS employee (fname varchar(20),lname varchar(20) ,phone varchar(15) , email varchar(30))ENGINE=MYISAM; 25/10/2007 4

DDL Statements – Create Table : DDL Statements – Create Table Duplicating a TABLE Mysql> CREATE TABLE IF NOT EXISTS LIKE Mysql>CREATE TABLE IF NOT EXISTS employee_new LIKE employee; Moving Data from Old Table to New Table Mysql> insert into employee_new select * from employee; 25/10/2007 5

DDL Statements – Create Table : DDL Statements – Create Table Creating Temporary Table Mysql> CREATE TEMPORARY TABLE IF NOT EXISTS (fname varchar(20),lname varchar(20),email varchar(30)); Renaming a Table Mysql>RENAME TABLE employee TO employee_new; 25/10/2007 6

DDL Statements – DROP Table : DDL Statements – DROP Table Mysql> DROP TABLE Mysql>USE HR; //use Mysql>DROP TABLE employee; 25/10/2007 7

DDL Statements – ALTER : DDL Statements – ALTER mysql> describe employee; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | fname | varchar(20) | YES | | NULL | | | lname | varchar(20) | YES | | NULL | | | email | varchar(30) | YES | | NULL | | ------------------------------------------------------- Mysql> ALTER TABLE ADD|DROP|MODIFY| CHANGE|RENAME column_name mysql> alter table employee add phone varchar(20); Mysql>alter table employee drop phone varchar (20) Mysql>alter table employee CHANGE email email_new varchar(35); Mysql > alter table employee modify email varchar(40); 25/10/2007 8

Views : Views CREATE VIEW Syntax CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] 25/10/2007 9

Views : Views example :- mysql> CREATE TABLE t (qty INT, price INT); mysql> INSERT INTO t VALUES(3, 50); mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; mysql> SELECT * FROM v; +------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | +---+--+------+ 25/10/2007 10

Views : Views ALTER VIEW Syntax ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] DROP VIEW v; DROP VIEW Syntax DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE] 25/10/2007 11

Triggers : Triggers Triggers are associated with Table updation. Triggers fired when some events happened on a table Trigger statements are single or multiple line SQL statements. Syntax Trigger_name =name of the trigger defined by you Trigger_time=BEFORE | AFTER Trigger_event = INSERT|UPDATE|DELETE Trigger_statement = SQL Statements –Single|multiple lines CREATE TRIGGER trigger_name trigger_time trigger_event ON Table_name FOR EACH ROW trigger_statement; 25/10/2007 12

Triggers Cont.. Single Line : Triggers Cont.. Single Line Example CREATE TABLE employee(fname varchar(20),lname varchar(20)); CREATE TABLE employee_inserted (last_inserted TIMESTAMP); CREATE TRIGGER insert_attempt AFTER INSERT ON employee FOR EACH ROW INSERT INTO employee_inserted VALUES (DEFAULT) CREATE TRIGGER trigger_name trigger_time trigger_event ON Table_name FOR EACH ROW trigger_statement; 25/10/2007 13

Triggers Cont..-Multi Line : Triggers Cont..-Multi Line Declaration Multiline Triggers – Important is Delimiter parameter setting Before and after Declaration DELIMETER | CREATE TRIGGER trigger_name trigger_time trigger_event ON Table_name FOR EACH ROW BEGIN SQL statement 1 ; SQL Statement 2 ; SQL Statement 3 ; END DELIMETER ; 25/10/2007 14

Triggers Cont..-Multi Line : Triggers Cont..-Multi Line Example: CREATE TABLE employee(fname varchar(20),lname varchar(20)); CREATE TABLE employee_inserted (last_inserted TIMESTAMP); Mysql>DELIMITER | Mysql>CREATE TRIGGER insert_attempt AFTER INSERT ON employee FOR EACH ROW BEGIN INSERT INTO employee_inserted VALUES (DEFAULT); INSERT INTO employee_inserted VALUES (DEFAULT); INSERT INTO employee_inserted VALUES (DEFAULT); END| Mysql>DELIMETER ; 25/10/2007 15

Triggers Management : Triggers Management SHOW TRIGGERS CREATE TRIGGER DROP TRIGGER trigger_name REPLACE TRIGGER 25/10/2007 16

Stored Procedures : Stored Procedures Stored procedures are associated with DB not with entire DBMS In Order To CREATE/ALTER routines the user must have CREATE/ALTER routine privilege Procedure GRANT Tables – db and proc_priv table in the mysql database Db table – create_routine_priv alter_routine_priv User table – create_routine_priv alter_routine_priv Proc table – keeps information of -Procedures and Functions 25/10/2007 17

Stored Procedures -Declartaion : Stored Procedures -Declartaion Syntax of Declaring Stored Procedures : Mysql> use dbname; Mysql>CREATE PROCEDURE procedure_name([proc_parameter,……,…..]) SQL Statement….; PROC_Parameter : [IN,OUT,INOUT] IN – Deafult 25/10/2007 18

Stored Procedures -Simple Procedure : Stored Procedures -Simple Procedure Task To Create Simple Procedure to return results of a query and call the query procedure again and again. The Procedure name is sp_get_employee() Mysql> use hr; Mysql> CREATE PROCEDURE sp_get_employee() select * from employee; No IN/OUT parameter send output to STDOUT To reveal stored procedures Mysql> show procedure status; Calling The Stored Procedures Mysql> CALL sp_get_employee(); 25/10/2007 19

Stored Procedures –Procedure With Parameter : Stored Procedures –Procedure With Parameter Task To Create Procedure to return results of a query to a variable sp_count_employee() Mysql> use hr; Mysql> CREATE PROCEDURE sp_count_employee (OUT total_employees INT) select count(*) INTO total_employes from employee ; Calling The Stored Procedures Mysql> CALL sp_count_employee(@total_employees); Mysql> SELECT @total_employees; 25/10/2007 20

Stored Procedures –Multi Statement Procedure : Stored Procedures –Multi Statement Procedure Task to create a Stored Procedure with multi line statements sp_count_employee() Mysql> use hr; Mysql>DELIMETER | Mysql> CREATE PROCEDURE sp_total_employee () BEGIN select count(*) INTO total_employes from employee ; select * from employee ; END;| Mysql>DELIMETER ; Calling The Stored Procedures Mysql> CALL sp_total_employee(); 25/10/2007 21

Stored Functions : Stored Functions Stored Functions returns value It can be used by stored procedures The table stores all user defined functions. By default the mysql.func table is empty. Syntax: 25/10/2007 22 CREATE FUNCTION func_name (func_parameter_1 type,….) RETURNS type RETURN function_operations

Stored Functions : Stored Functions The Function accepts at least on parameter. The type of argument is [int,char,varchar…]. The function_operations is any SQL statements. Example: 25/10/2007 23 CREATE FUNCTION func_name (func_parameter_1 type,….) RETURNS type RETURN function_operations e.g: CREATE FUNCTION hello (arg1 char(20)) RETURNS char(40) RETURN CONCAT(‘HELLO ’ ,arg1);

Stored Functions : Stored Functions Calling The functions : Showing The functions : Delete the function 25/10/2007 24 mysql> SELECT hello('world'); DROP Function func_name Select * from mysql.func

Replication : Replication 25/10/2007 25

Replication : Replication 25/10/2007 26

Replication : Replication Binary Logging must be enabled on the master server before replication starts 2 processes will execute on each slave server to handle operation 1 process will execute on the master server per slave server basis Replication is asynchronous updates are propagated immediately. Updates must be done at master server Ideal for Non Updating Databases 25/10/2007 27

Replication –Configuring Master MySQL Server : Replication –Configuring Master MySQL Server Configure Binary Logging Enabled on Master Server. To do so enable log_bin and put server_id = 1 parameter in /etc/my.cnf file . Restart MySQL server and check /var/lib/mysql folder for binary log file and index file creation # vi /etc/my.cnf [mysqld] log_bin=mysqlmaster-bin server_id=1 # service mysqld start i.e # ll /var/lib/mysql mysqlmaster-bin.000001 mysqlmaster-bin.index 25/10/2007 28

Replication –Configuring Master MySQL Server : Replication –Configuring Master MySQL Server Check master server status Create a super user account for replication with proper grant options # mysql>SHOW MASTER STATUS +------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ | mysqlmaster-bin.000001 | 98 | | | +------------------------+----------+--------------+------------------+ Binary Log File : mysqlmaster-bin.000001 index offset : 664 These two parameters are requires during slave server configuration mysql>GRANT REPLICATION SLAVE,SELECT,SUPER,RELOAD ON *.* TO ‘nic’@’%’ identified by ‘nic123’; 25/10/2007 29

Replication –Configuring Master MySQL Server : Replication –Configuring Master MySQL Server SHOW GRANTS FOR nic; mysql> show grants for nic; GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'nic'@'%' IDENTIFIED BY PASSWORD '53adf496046adce3' Master Server Is Ready 25/10/2007 30

Replication –Configuring Slave MySQL Server : Replication –Configuring Slave MySQL Server Configure server_id = 100 parameter in /etc/my.cnf file . Restart MySQL server # vi /etc/my.cnf [mysqld] server_id=100 # service mysqld start 25/10/2007 31

Replication –Configuring Slave MySQL Server : Replication –Configuring Slave MySQL Server Configure Slave server Mysql> CHANGE MASTER TO MASTER_HOST=‘mysqlmaster’, MASTER_USER=‘nic’, MASTER_PASSWORD=‘nic123’, MASTER_LOG_FILE=‘mysqlmaster-bin.000001’, MASTER_LOG_POS=98; MYSQL>START SLAVE; MYSQL> SHOW SLAVE STATUS; Host should be resolved, put host entry in /etc/hosts Slave Server Is Ready 25/10/2007 32

Replication –Complete : Replication –Complete Verify the replication process Create a new database at master mysql server Create new table and insert some data Check in the slave server whether replication works or not 25/10/2007 33

Replication –Complete : Replication –Complete Verify the replication process Create a new database at master mysql server Create new table and insert some data Check in the slave server whether replication works or not 25/10/2007 34

Replication V Clustering : Replication V Clustering Replication Model Clustering Model Common Storage 25/10/2007 35

Logging : Logging MySQL utilizes 5 log files by default. Error Log – Error During Start/Run/Stop MySQL Query Log – Logs Client connections and their executed Statements Binary Log –Data Changing Statements during Replication Process Slow Log – File that identifies Slow Running Queries Relay Log –Data Changing Statements in the Slave Server during replication. 25/10/2007 36

Logging –Error Log : Logging –Error Log Error Log file keeps log of mysqld START|STOP|RUN error Default file name e.g mysqlmaster.err if the hostname is mysqlmaster. By default it is not on in Redhat Linux . To On the error log file configure the parameter To view all log error parameter use the command #mysqld - -verbose - -help|grep –I log |less 25/10/2007 37

Logging –Error Log-Config : Logging –Error Log-Config Open /etc/my.cnf file under [mysqld] put the parameter #vi /etc/my.cnf [mysqld] log-error or log-error= e.g log-error=mysqlmaster-error 25/10/2007 38

Logging –Error Log- Flush : Logging –Error Log- Flush By default the error log file will be created in the default folder /var/lib/mysql The default error file name is .err or otherwise configured in the /etc/my.cnf. To Flush the Error log file use the command #mysqladmin flush-logs Flushing the error log file will rename the old log file as ‘hostname.err.old’ 25/10/2007 39

Logging –Query Log : Logging –Query Log By default the query log is off Use it to trouble shoot performance Issues. The query log file reflects all SQL statements. The default file name is Can be set using parameter ‘log=hostname.log’ in the /etc/my.cnf file. 25/10/2007 40

Logging –Query Log-Cont.. : Logging –Query Log-Cont.. Open /etc/my.cnf file [mysqld] Log or Log=mysqlserver-query.log Restart the server after configuration. The log file will be created with the name of mysqlserver-query.log Use ‘mysqladmin flush-logs ‘ to flush the log file. 25/10/2007 41

Logging –Binary Log : Logging –Binary Log Binary log stores in the form of binary files It is faster than ascii log files Binary Log files used during replication. It stores database changed logs (Insert/update/delete) By Default the log file created as hostname-bin.000001 e.g mysqlserver-bin.000001 hostname-bin.index e.g mysqlserver-bin.index The hostname-bin.000001 keeps the actual log and The hostname-bin.index keeps the track of current log file information. 25/10/2007 42

Logging –Binary Log Cont.. : Logging –Binary Log Cont.. The log files by default created in /var/lib/mysql folder. The binary log is enabled in /etc/my.cnf file. The Binary log file only created in the master replication server. Use mysqlbinlog to view the binary log file. # vi /etc/my.cnf [mysqld] log_bin=mysqlmaster-bin server_id=1 25/10/2007 43

Logging –Intermediately Binary Log : Logging –Intermediately Binary Log The Intermediately binary log file creates in slave server only. The default format is ‘hostname-relay.bin.000001 . The intermediately log file keeps the data changes made to slave server . Use mysqlbinlog to view the intermediately relay log file. 25/10/2007 44

Logging –Slow Log : Logging –Slow Log Only used for debugging of mysql slow queries. Default time out of slow queries is 10 seconds. It can be fixed to any seconds. The parameter is long_query_time=10 Log_slow_queries=hostname-slow.log [mysqld] Long_query_time=10 Log_slow_queries=mysqlserver-slow.log 25/10/2007 45

MySQL Stored Engines : MySQL Stored Engines 25/10/2007 46

Stored Engines -Types : Stored Engines -Types DATABASE MysqlD 25/10/2007 47

Stored Engines -MyISAM : Stored Engines -MyISAM MyISAM is the default database of Mysql. MyISAM is a non-transaction al database engine ,means it does not support commit / rollback feature like Oracle. No Log file maintained so transaction being done directly to the database. Creating a table without any engine parameter creates MyISAM table. 25/10/2007 48

Stored Engines -MyISAM : Stored Engines -MyISAM Creating a table with MyISAM engine Mysql>create database hrm; Mysql>use hrm; Mysql>CREATE TABLE employee(fname varchar(20),lname varchar(20),email varchar(30)) ENGINE=MYISAM; Mysql>SHOW CREATE TABLE EMPLOYEE; Viewing Database file structure [root@mysqlmaster ~]# ll /var/lib/mysql/hrm/ -rw-rw---- 1 mysql mysql 65 Oct 23 00:28 db.opt -rw-rw---- 1 mysql mysql 8626 Oct 23 00:31 employee.frm -rw-rw---- 1 mysql mysql 0 Oct 23 00:31 employee.MYD -rw-rw---- 1 mysql mysql 1024 Oct 23 00:31 employee.MYI 25/10/2007 49

Stored Engines -MyISAM : Stored Engines -MyISAM Viewing Database file structure db.opt – Database optional Configuration File employee.frm – MYSQL MYISAM Table Schema Definition File employee.MYD –MYSQL MYISAM Table Data File employee.MYI –MYSQL MYISAM Table Index file 25/10/2007 50

Stored Engines -MEMORY : Stored Engines -MEMORY Memory Storage engine Keeps data in memory. Data retrieval is first . Memory Storage Engine creates only table schema file[table_name.frm] in the database. The data in memory based tables are available as long as the server [mysqld] is up and running Security –Risk :The memory based tables are available to all user . BLOG and TEXT column type are not supported by memory based engines. 25/10/2007 51

Stored Engines -MEMORY : Stored Engines -MEMORY Creating a table with MEMORY engine Mysql>CREATE TABLE employee_mem(fname varchar(20),lname varchar(20),email varchar(30)) ENGINE=MEMORY; Mysql>SHOW CREATE TABLE employee_mem; Viewing Database file structure [root@mysqlmaster ~]# ll /var/lib/mysql/hrm/ -rw-rw---- 1 mysql mysql 65 Oct 23 00:28 db.opt -rw-rw---- 1 mysql mysql 8626 Oct 23 00:31 employee_mem.frm 25/10/2007 52

Stored Engines -MEMORY : Stored Engines -MEMORY The SELECT ,INSERT,UPDATE,DELETE statement works on MEMORY Table . Insert Data into employee _mem Table Mysql>INSERT INTO employee_mem (fname,lname,email) SELECT fname , lname , email FROM employee; To enable mysqld to reinitiate the data-set upon startup do the following Modify /etc/my.cnf to include Init-file=file-name[put the above sql statement in the file] 25/10/2007 53

Stored Engines -CSV : Stored Engines -CSV CSV – Comma Separated Value CSV format suitable for exporting data to other database format e.g Microsoft Excel By default CSV engine is not supported by MySQL MySQL-MAX version supports CSV engine For Use of CSV engine download mysql-max from mysql.org web site CSV Engine creates the table as file. The file can be improrted directly to Microsoft Excel. 25/10/2007 54

Stored Engines -CSV : Stored Engines -CSV Creating a table with CSV engine Mysql>CREATE TABLE employee_csv(fname varchar(20),lname varchar(20),email varchar(30)) ENGINE=CSV; Mysql>SHOW CREATE TABLE employee_csv; Viewing Database file structure [root@mysqlmaster ~]# ll /var/lib/mysql/hrm/ -rw-rw---- 1 mysql mysql 65 Oct 23 00:28 db.opt -rw-rw---- 1 mysql mysql 8626 Oct 23 00:31 employee_csv.csv 25/10/2007 55

Stored Engines -Federated : Stored Engines -Federated Federated Engine provides access to remote tables To implement Federated Engine the network infrastructure should be strong enough to handle the network traffic. Federated Table is link table structure to a remote table data. Supports DML Statement 25/10/2007 56

Stored Engines -Federated : Stored Engines -Federated RemoteDB LocalDB 25/10/2007 57

Stored Engines -Archived : Stored Engines -Archived Archived engines stores data in a compressed GZ format Use to findout mysql is supporting archived engine Does not support Indices Archive table stores in the format and Retrive Data from archived engine is slow Create archived table Mysql>CREATE TABLE employee_arc (fname varchar(20), lname varchar(20),email varchar(30)) ENGINE=ARCHIVE; File Structure Employee_arc.arz Employee_arc.frm 25/10/2007 58

Stored Engines -INNODB : Stored Engines -INNODB High End Storage Engine. Supports large TRANSACTIONAL Data Set. Typically deploy in critical environment. MYISAM and Other storage engine does not support TRANSACTION (COMMIT ,ROLLBACK). By Default INNODB stores all table data and indices in one file , like other high end RDBMS (Oracle , MS SQL). Default storage file is ibdata1. INNODB creates two transactional log files (ib_logfile0 and ib_logfile1) . 25/10/2007 59

Stored Engines -INNODB : Stored Engines -INNODB INNODB -All the transactions been recorded into log files then to database. MYISAM –All the transactions been recorded directly into the database. By default INNODB stores all table data in ibdata1 file but multiple files spread across different hard disks can be configured or multiple different table innodb files can be created by simply changing configuration file . 25/10/2007 60

Stored Engines -INNODB : Stored Engines -INNODB INNODB Configuration file is /etc/my.cnf. INNODB default database files are (ib_logfile1,ib_logfile0,ibdata). INNODB automatically extends the default 10Mb file to accommodate additional data. So before using INNODB engine try to forecast data storage as much as possible to avoid superfluous I/O . Table INDEX file stores in the same ibdata but in MYISAM index file is a separate file. Using INNODB the size of database allocation occurs on the fly, auto extent also occurs on the fly but deallocation of space needs administrator help. 25/10/2007 61

Stored Engines –INNODB-customization : Stored Engines –INNODB-customization By default INNODB customization files are stored in /usr/share/doc /mysql-server/ folder (my-innodb-small.cnf ,my-innodb-medium.cnf ,my-innodb-heavy-4G.cnf). Copy the any one of sample conf ig file to /etc and rename it to my.cnf file for innodb use as for your requirement . But some customization is required in the /etc/my.cnf file to enhance the performance of INNODB . All the INNODB parameter starts with innodb - . 25/10/2007 62

Stored Engines –INNODB-customization-Important Parameters. : Stored Engines –INNODB-customization-Important Parameters. Sample INNODB Configuration file /etc/my.cnf Innodb_data_home_dir=/var/lib/mysql Innodb_data_file_path=ibdata1:4G:autoextend Innodb_buffer_pool_size=256M #50 – 80% of physical memory Innodb_log_file_size=64M #25 % of buffer pool size After changing the configuration file restart the mysql server process ,it will auto create the ibdata1 database file of size 4GB in the folder /var/lib/mysql folder. 25/10/2007 63

Stored Engines –INNODB-Table-Creation : Stored Engines –INNODB-Table-Creation Creating INNODB Table Mysql>CREATE TABLE employee_csv(fname varchar(20),lname varchar(20),email varchar(30)) ENGINE=INNODB; By default all the tables and indices will be stored in the default ibdata1 file. 25/10/2007 64

Stored Engines –INNODB-Custom Innodb File : Stored Engines –INNODB-Custom Innodb File Copy the sample innodb config file from /usr/share/doc/mysql-server/ folder. INNODB buffer pool size preferably 50% of the physical memory of the database server to enhance performance ,be care not to give more than 80% . INNODB log file size may be 25% of the buffer pool size. Innodb_data_home_dir=/var/lib/mysql Innodb_data_file_path=ibdata1:4G:autoextend Innodb_buffer_pool_size=256M Innodb_log_file_size=64M Innodb_autoextend_increment=16M 25/10/2007 65

Stored Engines –INNODB-Custom Innodb File : Stored Engines –INNODB-Custom Innodb File Creating Custom INNODB file – File Syntax INNODB Max and Autoextend Innodb_data_file_path=file1:file_size(M/G);file2:file_size(M/G);file3:file_size(M/G) e.G :Innodb_data_file_path=ibdata1:1G;ibdata2:4G;ibdata3:10G Innodb_data_file_path=file1:file_size(M/G):max:max_size(M/G) :autoextend e.G :Innodb_data_file_path=ibdata1:500M:max:2G :autoextend 25/10/2007 66

Stored Engines –INNODB-Multiple Data file-Multiple Disk : Stored Engines –INNODB-Multiple Data file-Multiple Disk Hard Disk Space for Database /data1 /dev/sdb1 10G /data2 /dev/sdc1 20G /data3 /dev/sdd1 20G Innodb_data_home_dir= # should be blank Innodb_data_file_path=/data1/ibdata1:10G;/data2/ibdata2:20G; /data3/ibdata3:20G Mysql need read and write permission on the file system where it will create innodb data file . 25/10/2007 67

Stored Engines –INNODB-Multiple Table File in Multiple INNODB Files : Stored Engines –INNODB-Multiple Table File in Multiple INNODB Files By default INNODB stores all tables in one disk file . But multiple tables can be created in multiple INNODB files. By configuring the parameter innodb_file_per_table [mysqld] innodb_file_per_table =1 By setting the above parameter INNODB splits tables in multiple files. 25/10/2007 68

Stored Engines –INNODB-RAW Partition : Stored Engines –INNODB-RAW Partition Raw Partitions improves the performance in a great deal. It is useful during the clustering of Mysql Database. High-end databases like oracle supports raw partitions. Implementing INNODB raw partitions ,identify the disk partitions to use e.g /dev/sdb1 ,/dev/sdc1 Make the partitions using fdisk and make it type linux. 25/10/2007 69

Stored Engines –INNODB-Creating -RAW Partition : Stored Engines –INNODB-Creating -RAW Partition Example – use the partition /dev/sdb1 to use as INNODB database .Change the /etc/my.cnf file --------------------------------------------------------------- [mysqld] Innodb_data_file_path=/dev/sdb1:10G newraw -------------------------------------------------------------- Restart the mysql server and change again the newraw to raw. Innodb_data_file_path=/dev/sdb1:10G raw 25/10/2007 70