MySQL is a open source database which means it’s freely available with free redistribution, this means you have full access to the source code.
MySQL comes in a number of flavors, commercial customers have a number of different choices depending on your needs.
Community Server (free edition)
Standard Edition
Enterprise Edition
Cluster Carrier Grade edition
MySQL Storage Engines
MySQL is very different from other databases, in that it’s storage engine is pluggable, what I mean by this is that the MySQL server core code is separate from the storage engine, which means that you can have a pluggable storage engine that fits your application.
MyISAM :
MyISAM has three files associated with it, because the three files represent a table they can be simply copied to another server and used, however to avoid corruption you should take down mysql server before copying. The other server must have the same endian format as the source server, so you cannot copy from linux X86 server to a Sparc server for instance.
.frm the table format file
.MYD the data file
.MYI the index file
MyISAM has the following features
Non-transactional
No foreign key support
FULLTEXT indexes for text matching
No data cache
Index caches can be specified by name
Implements both HASH and BTREE indexes
Table level locking
Very fast read activity, suitable for data warehouses
Compressed data (with myisampack)
Online backup with mysqlhotcopy
Maximum of 64 indexes per table
InnoDB:
InnoDB is now the default storage engine in MySQL, it has the following key features
Transactional support provided by MVCC (Multi Version Concurrency Control)
Row-level locking
Foreign key support
Indexing using clustered B-tree indexes
Configurable buffer caching of both data and indexes
Online non-blocking backup through separate commercial backup program
ith the Innodb storage engine you have control of the format and the location of the tablespace, a tablespace is a logical group of one or more data files in a database (yes the same as in Oracle), using parameters you can control the path the home directory and if you want to use separate files or a shared tablespace.
innodb_data_file_path Determine both the path to individual centralized data files (shared tablespace) and the size of the files
innodb_data_home_dir The common part of the directory path for all InnoDB data files
innodb_file_per_table If enable then any InnoDB tables will be using their own .idb file for both data and indexes rather than in the shared tablespace.
There are a number of configuration parameters that you can use with the InnoDB storage engine
innodb_buffer_pool_size Determines the size of the buffer that the Innodb storage engine uses to cache both data and indexes
innodb_flush_log_at_trx_commit
Configures how often the log buffer is flushed to disk
0 – log buffer is written every second
1 – every commit will make the log buffer flush
2 – basically is a combination of 0 and 1
innodb_log_file_size Determines the size (in bytes) of each of the Innodb log files, by default they are 5MB. Remember the bigger the file the slower recovery after a server crash.
The larger the buffer the more data you can hold in memory which in turn increases performance, thus try to keep your most frequently used data in memory. Don’t go too mad as to much memory could cause swapping by the operating system which in turn will degrade performance.
show engine innodb statusInnoDB uses shared tablespace for all tables and indexes that can consist of one or more files, hey are generally located in the datadir directory, the files contain metadata and are referred to as ibdata files. When the tablespace becomes fragmented the files are not shrunk, but the free space is still available to the database, you can view this free space by using the data_free field of the information_schema.tables system view and the data_free field of show table status.
to defrag the tablespace use the optimize table commandMEMORY:
MEMORY storage engine creates a table in memory only, this allows for extremely fast data access, however the drawback is that data is not persisted across reboots, also if you replicate this table to a slave server, it will also truncate the slave table as well even though it did not restart. If rows are deleted from a memory table you must use either alter table or optimize table to defrag the table and reduce the amount of memory used.
The MEMORY storage has the following features
All data is stored in memory
Non-transactional
No foreign key support
very fast read and write activity due to being entirely in memory
table-level locking
A MEMORY table can include yo to 32 indexes per table
implements both hash and b-tree indexes
tables use a fixed length row storage format and thus cannot be defined with data types of blob or text
The MEMORY storage engine has a few parameters that you can tweak
max_heap_table_size the maximum size of MEMORY tables is limited by the system variables, default is 16MB
init_file this can be used to specify a file to use to populate a MEMORY table when mysqld starts
CSV storage engine:
CSV storage engine (Comma Separated Value), it is simply a text file that can be manipulated with a simple text editor, this is ideal for data exchange or data importing.
CSV storage has the following features
Plain text data in CSV format
Easily imported into programs such as Microsoft Excel
Table-level locking
No foreign key support
Non-transactional
Trivial backups and restores (just copy the files)
Does not support indexes or partitioning
Three files are created .frm which contains the table format, .CSM which contains metadata and lastly .CSV file which contains the data.
Inside MySQL, firstly you would create a database (or schema as it means the same thing), then inside each database you create tables that can be associated with a different type of storage engine, for example I could create four tables all using different storage engines all within the same database (schema), the picture below describes a single mysqld daemon running two databases/schemas each with fours tables, the tables can be associated with different storage engines.
Lastly the MySQL data dictionary is stored in the mysql database, a view only copy is stored in the information_schema database, it is a simple set of tables.
–You also have the option to feed in either a sql file or sql commands from the commandline
mysql -u pvalle -h mysqldb -ppassword -e “select * from mysql”
mysql -u pvalle -h mysqldb -ppassword < sql_commands.sql
Once you have connected to MySQL, there are a number of useful commands
List all commands help
\h
?
List databases show databases;
Select a particular database \u mysql
use mydatabase
Change the delimiter \d @
delimiter @
Change the output to vertical # Just put a \G at the end of the select statement instead of the normal delimiter
select * from tables\G
Run a shell command ! uptime
list connected database and user select user(), database();
mysqladmin:
useful command is mysqladmin, which allows you to perform adminisitive tasks
Command Usage Description
create mysqladmin create test2 create a database called test2
debug mysqladmin debug send additional detailed logging to the error log
drop mysqladmin drop test2 drop the database called test2
extended-status mysqladmin extended-status displays mysqld system variables and their current values
flush-hosts mysqladmin flush-hosts clears internal information about hosts including the DNS cache and hostnames blocked due to too many connection errors
flush-logs mysqladmin flush-logs flushes server logs by closing current logs and reopening new log files.
flush-privileges mysqladmin flush-privileges reloads the grant tables, thus refreshing users privileges
flush-status mysqladmin flush-status resets most server status variables
flush-tables mysqladmin flush-tables closes currently open table file handles, it waits for the current thread connections to finish before releasing file handles used by those connections
flush-threads mysqladmin flush-threads resets the thread cache
kill mysqladmin kill 50 kill specified client threads
password mysqladmin password change the connection password for the user account specified
old-password mysqladmin old-password change the connection password for the user account specified, however stores the password in the old less secure way using 16 characters instead of 41 characters
ping mysqladmin ping determines if the server is online and available
processlist mysqladmin processlist displays all active server threads
reload mysqladmin reload see flush-privileges
refresh mysqladmin refresh similar to flush-hosts and flush-logs
shutdown mysqladmin shutdown stop the mysqld daemon cleanly
start-slave mysqladmin start-slave starts replication
status mysqladmin status Displays a number of global status variables
stop-slave mysqladmin stop-slave stop replication
variables mysqladmin variables displays the global status variables and values
version mysqladmin version displays the version of MySQL
