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/
|2010-09-24 09:37||Sean Chen||
MySQL got installed on a CentOS server. It is very easy to install MySQL by using yum: server and client are separate packages. It is also easy to create databases and users by using the mysql client as while as grant privileges.
-- 1.0 Hours, 80.0% Done