Learn MySql at AMC Square Learning
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 ).
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.
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.
Types of table in MySql • 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.
Merging the tables • A collection of identical My example tables which are used collectively. • FOR ex. : 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) select ,insert and Operations update .are allowed
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)
How To SLAVE cont.… • 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
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
Thank you