aws ec2 mysql

Posted on | Edited on | In

Install

1
2
3
4
5
yum install mysql mysql-server mysql-libs

# change owner & group
sudo chgrp -R mysql /var/lib/mysql
chmod -R 770 /var/lib/mysql

Settings

1
2
3
4
5
6
7
8
9
10
11
12
13
SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
set character_set_server=utf8;
set character_set_database=utf8;
SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Passwd

  1. mysqladmin

    1
    mysqladmin -u root password 'psswd'

  2. update

    1
    2
    3
    4
    mysql -u root
    　mysql> use mysql;
    　mysql> UPDATE user SET Password = PASSWORD('passwd') WHERE user = 'root';
    　mysql> FLUSH PRIVILEGES;

  3. set

    1
    2
    MySQL -u root
    　　mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('passwd');

  4. skip

    1
    2
    3
    4
    5
    vim /etc/my.cnf
    # remove after the job
    add `skip-grant-tables`
    wq
    service mysqld restart
1
2
service mysqld stop
mysqld_safe --skip-grant-tables&

User & Db

1
2
3
4
5
create user 'test'@'localhost' identified by '123456';
create database testdb default charset utf8 collate utf8_general_ci;
grant all privileges on `testdb`.* to 'test'@'localhost' identified by '123456';
flush privileges;
mysql -u test -h 1.1.1.1 -p

Exception

ERROR 1227 (42000): Access denied; you need (at least one of) …
If sign in mysql in localhost like this:

1
mysql -u root -p

no privileges on create user/ create db/ show process …

1
2
3
4
5
6
show grants for ''@'localhost';
+----------------------------------------------------------------------+
| Grants for @localhost                                                |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' IDENTIFIED BY PASSWORD <secret> |
+----------------------------------------------------------------------+

the command should be like this:

1
mysql -u root -h 127.0.0.1 -p

Amazon EC2
AWS Linux MySQL Install
ERROR 1227 (42000)
ERROR 1045 (28000)