How to setup MySQL

Install(in ubuntu)

apt-get install mysql-server libmysqlclient15-dev

Create user and database

# login as root at first
mysql -u root -p

# create a user and set password
mysql> create user 'your-name'
mysql> upadte user set password=PASSWORD('your-password') where user='your-name';
mysql> flush privileges;

# grant user's privileges for local access
mysql> grant all privileges on *.* to your-name@"localhost" identified by "your-local-password";

# grant user's privileges for remote access(optional)
mysql> grant all privileges on *.* to your-name@"%" identified by "your-remote-passwd";
mysql> flush privileges;

# show users
mysql> select * from user;

# create database
mysql> create database your-database

Charset(UTF-8) and storage engine(INNODB)

# modify default settings
nano /etc/mysql/my.cnf

# add below settings to my.cnf
[client]
default-character-set=utf8

[mysqld]
# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8

# The default storage engine that will be used when create new tables
default-storage-engine=INNODB

Change default data path(Optional)

eg. change default data path from /var/lib/mysql to /mnt/ebs/data/mysql

# copy data files,copy with -p to preserve folder's attibutes
cp -r -p /var/lib/mysql/ /mnt/ebs/data

# change mysqld's apparmor, make sure data path correct everywhere.
nano /etc/apparmor.d/usr.sbin.mysqld

# modify old path to new's
#/var/lib/mysql r,
#/var/lib/mysql/** rwk,

/mnt/ebs/data/mysql r,
/mnt/ebs/data/mysql** rwk,

Restart

/etc/init.d/apparmor restart
/etc/init.d/mysql restart

Check charset setting

# execute it in mysql console or any mysql client tools
show variables like '%char%';

Remote access(Optional)

nano /etc/mysql/my.cnf
# comment bind-address in my.cnf, and grant privilages for remote access(Refer 
# to create user section)

#bind-address = 127.0.0.1

Access issue

Some guys may get ERROR 1045 (28000) when they try to login MySQL as root at first time, Error message like "Access denied for user 'root'@'localhost' (using password: YES)". You can use reserved debian-sys-maint user to login in ubuntu or debian:

mysql -u  debian-sys-maint -p
# enter password in /etc/mysql/debian.cnf
# then update root's password
mysql> upadte user set password=PASSWORD('new-password') where user='root';
mysql> flush privileges;
Felinx Lee   August 27, 2010
Comments blog comments powered by Disqus