Personal tools
You are here: Home Plone XP Open Source Software Review MySQL Quick Review

MySQL Quick Review

— filed under: ,

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:

Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h dhcp-10-1-1-101 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[  OK  ]
Starting MySQL:                                            [  OK  ]

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:

Tracking History

When Who What Done
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
Document Actions