MySQL Quick Review

Installation on CentOS

Using yum is very convenient for CentOS.  MySQL came with many packages: server, client, devel, php, etc.  We need install at lease server and client.

$ yum list | grep mysql
$ sudo yum install mysql mysql-devel mysql-server 

The message of starting MySQL server at the first time:

Quick Manual for MySQL

mysql configuration file: etc/my.conf

connect to mysql database: mysql -u USERNAME -p DBNAME

create database: create database dbname

create user and grant privileges: grant all on dbname.* to 'username'@'hostname' identified by 'password'

Don't forget to flush privileges: flush privileges;

show all triggers for a database: show triggers from dbname;

By default, MySQL is using MD5 hash code to encrypt password column.  We could reset a password column by using UPDATE statement:

mysql> update users set user_pass = MD5('newpassword') where id = userid;

Storage Engines for MySQL

By default MySQL came with MyISAM storage engine.  The command show engines will show all available storage engine for your MySQL server.

mysql> show engines

In MySQL you could specify the storage engine for each table.  Different tables may use different storage engines.  You may set the engine when you create table (using CTEATE TABLE) and you also can change the storage engine for an existing table by using ALTER TABLE table_name ENGINE = engine_name.

CREATE TABLE mytable (id INT) ENGINE = INNODB;
ALTER TABLE mytable ENGINE = MYISAM;

Best Practices for Performance

Using the innodb might be a good idea to improve the performance in most cases.

Some blogs about MySQL performance tunning:

• WordPress MySQL Tunning: http://dltj.org/article/wordpress-mysql-tuning/
• What to tune in MySQL Server after Installation: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/