A Brief Introduction to MySQL

1. A Brief Introduction to MySQL By Nishkam, Neeraj,Saurabh, Hrishikesh and Somesh

2. Pointers • What is MySQL ? Its good features. • Installation guide. • Security and Privilege System • Compatibility with standards • Locks – an interesting feature • Table types • Some more salient features • Replication

3. What Is MySQL? • A fast, reliable, easy-to-use, multi-user multi-threaded relational database system. • It is freely available and released under GPL (GNU General Public License ).

4. Why Use MySQL ? • MySQL server can handle very large databases. • Offers rich and very useful set of functions. • Connectivity, speed and security make MySQL very suited for accessing database on a network. • A lot of contributed software available.

5. What Does MySQL Offer ? • A privilege and password system that is very flexible and secure and allows host-based verification. • Multi-threaded request-handling using kernel thread. • Replication features. • Very actively developed. • Memory leak proof.

6. Quick Installation Guide • tar –zxvf Mysql.x.xx.xx.tar.gz • cd Mysql-x.xx.xx.xx • ./configure –prefix=/usr/local/mysql • make • make install • scripts/mysql_install_db • safe_mysqld • Mysql –u root password ‘mypassword’

7. Configuring MySql • --bindir=Dir specifies dir for binaries • --localstatedir=Dir data dir • --with-tcp-port=portno • --with-unix-sock-path=absolute-path • --with-mysqld-ldflag=-all-static • --with-charset=charset (default Latin1) • --with-low-memory

8. Support • Works on many different platforms like FreeBSD , NetBSD , Linux 2.0+, Windows ’95, ’98, 2000 & NT, HP-UX etc • There are client tools and APIs available in c, c++, java, perl, python, php, Tcl

9. Security in mysql • Issues that bother: eavesdropping,altering • Uses ACL’s (Access Control Lists) , also there’s some support for SSL connections • Has inbuilt methods for storing confidential data like passwords in encrypted form. • Access is restricted thru grant of privileges to users,hosts

10. Privileges provided by mysql • The five tables: user,db,host,tables_priv,columns_priv • The privileges come into play only if there is a retrieval of data from the database, or updation of data in the database,e.g: select 1+1; calculator  • File privilege : load data infile, select …. Into outfile

11. How the privilege system works • First reference to user followed by db and host , takes place during connection verification • Reference to tables_priv and columns_priv at data access stage • Ordering of user table on the basis of more specific host values first, followed by more specific user values. • For security purposes , mysql disallows host addresses of the form :192.168.7.yahoo.com

12. Some tips on security • Beneficial to invest in a firewall ; check using “telnet server_port 3306” • Do not rely on the data entered by the user, he can trick the code by using special character sequences • Make use of tcpdump, to check the whether or not mysql data streams r unencrypted: tcpdump –l –i eth0 –w – src port 3306|strings • Dont run mysqld as root. • Don’t give process and file privileges to users as far as possible.

13. Continued….. • Following mysqld options affect security: 1. –- secure 2. – skip-grant-privileges 3. – skip-name-resolve 4. –skip-networking • Passwords by default are stored in hashed form in database.But if the scrambled password is known the hacker can still log in as the user. • Passwords can be stored in my.cnf file when non-interactive access has to be done • Inbuilt functions for hashing: password, encrypt,encode,decode

14. Compatibility with Standards What doesn’t MySQL have ? • Transactions – no provision for commit and rollback • No nested sub-queries • Views are not supported • Foreign keys not used for referential integrity checks

15. Compatibility (contd.) What extra does MySQL have ? • Locking/Unlocking of tables • Atomicity of operations • Directory organization • Access of tables across tables • Several other features, discussed later…

16. More about Locks ! • Locks help in maintaining integrity, atomicity • Read locks – enable you to only read from the locked tables. • Write locks – enable you to read and write exclusively. Other threads can’t access/update currently locked tables • Write locks have higher priority than read locks

17. MySql Table Types • Creates a .frm file that holds the table and column definition. • Syntax for defining table type is .. : CREATE TABLE test ( ) TYPE=HEAP .. • Categorization of types : Transaction safe type and Non transaction safe type. • Transaction Safe table types allow rollbacks,restore changes if update fails ,etc . • Non Transaction Safe table types are much faster ,use less disk space as well as memory for updates.

18. MyISAM tables • Use B-Tree indexing to store the keys, string indexes are compressed . • MyISAM itself supports three different table formats: a) static (fixed length) b) Dynamic c) Compressed • Static : fastest ,secure and simplest format , used when there are no varchar, blob or text. • Dynamic : each record stores a header that contain its length • Compressed : created using myisampack tool , they are read only ,use very little space as each record is compressed separately • ISAM is a deprecated version of MyISAM

19. Merge tables • A collection of identical MyISAM tables which are used collectively. • FOR eg : CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ; CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2) • allowed : select ,insert and Operations update .

20. Heap tables and Berkley DB • Use hashed index and are stored in memory • Generally used for temporary tables . • Berkley DB is used for making the tables transaction safe

21. MySQL Language Reference OPTIMIZE • Syntax :: … OPTIMIZE TABLE tbl_name[,tbl_name].. • Used only for MyISAM tables • It performs the following functions :repairs the table if the table has deleted rows,sorts the index,and the statistics are also made to date. CHECK • Syntax :: … CHECK TABLE tbl_name[,tbl_name...] [TYPE = [QUICK | FAST | EXTEND | CHANGED]]

22. MySQL Language Reference • Checks a table for errors and updates the key statistics of the table BACKUP • BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory‘ • This again works only for MyISAM ANALYZE • During analysis the table is locked with a read lock

23. MySQL Language Reference REPAIR • Syntax: REPAIR TABLE tbl_name[,tbl_name...] [TYPE = QUICK] FLUSH • Syntax : FLUSH flush_option [,flush_option] • Used to clear the internal cache of Mysql • It has various options like HOSTS,LOGS,PRIVELEDGES, TABLES tbl_names,etc

24. Replication in MySQL • What?? • One server is designated as the master, while the other ( or others) as slave(s) • Updates done only on master and binary logs made • The slave connects to the master, catches up on the missed updates, and then starts receiving updates immediately as they come to the master. • Why?? • For robustness you have two systems and switch to the backup if you have problems with the master. • The extra speed is achieved by sending a part of the non-updating queries to the replica server.

25. How To (SLAVE) • Upgrade both slave and master to 3.23.15 or higher. • PUT THESE IN my.cnf of SLAVE • master-host,user,passwd • server-id=< unique no>= 1 & <=2^32-1 • master-connect-retry !! (keep trying) • master-info-file • replicate-rewrite-db • skip-slave-start • Restart the slave(s)

26. How To (SLAVE) (contd) • Take a snapshot of all the tables/databases on the master • Use command LOAD TABLE <tblname> FROM MASTER (3.23.23)+ • SLAVE START/STOP • FLUSH SLAVE

27. HOW TO (MASTER) • log-bin , FLUSH MASTER , FLUSH SLAVE • When you start the slave thread will be created • Tables are not Locked • SET SQL_LOG_BIN=0/1 • CHANGE MASTER TO master_def_list