1. ๋ก์ปฌ ์๋ฒ๋ก ์ ์ํ๋ ๋ฐฉ๋ฒ
C:\>mysql -u root -p mysql
Enter password: ****************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> ==> ์ฑ๊ณต
2. ๊ด๋ฆฌ์ ํจ์ค์๋ ๋ณ๊ฒฝํ๋ ๋ฐฉ๋ฒ
C:\>mysqladmin -h localhost -uroot -p PASSWORD 112233
Enter password: **********
mysqladmin -u root -p reload ==> ๋ก๋ฉ ์ํค๋ ๋ช
๋ น
Enter password: **********
๊ด๋ฆฌ์ ์ํธ ๋ณ๊ฒฝ ๋ช
๋ น๋ฌธ
mysql> set password for 'root'@'localhost' = password('1234567');
Query OK, 0 rows affected (0.00 sec)
ํ์ ๊ด๋ฆฌ์์ธ root์ ์ด๋ฆ์ ๋ค๋ฅธ ์ด๋ฆ์ผ๋ก ๋ฐ๊พผ๋ค์ ๋ชจ๋ ์ ์ ์ ๊ทผ ์ฐจ๋จํ๊ธฐ
mysql> update mysql.user set user="myadmin" where user="root";
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
3. ๋๋น ์ฌ์ฉ์ ์ถ๊ฐ ์ญ์ ๋ฐ ํจ์ค์๋ ์ค์ ๋ฐฉ๋ฒ
C:\>mysql -h localhost -u root -p mysql
์ฌ์ฉ์ ์ถ๊ฐ ๋ช
๋ น๋ฌธ
mysql> GRANT ALL PRIVILEGES ON *.*<๋๋น ๊ถํ> TO sunny@localhost <์ฌ์ฉ์ID> IDENTIFIED BY 'win123 <ํจ์ค์๋>' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO lala@localhost IDENTIFIED BY 'win123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO zoo@localhost IDENTIFIED BY 'win123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES; ==> ์ค์ ๋ด์ฉ ๋ก๋
Query OK, 0 rows affected (0.14 sec)
mysql> delete from mysql.user where not (host="localhost" and user ="study");
==> ์คํฐ๋๋ผ๋ ์ฌ์ฉ์๋ฅผ ์ ์ธํ ๋ชจ๋ ์ฌ์ฉ์๋ฅผ ์ง์ด๋ค. ํน๋ณํ ๊ฒฝ์ฐ ์๋๊ณ ๋ ์ฌ์ฉ๊ธ์ง
Query OK, 5 rows affected (0.13 sec)
mysql> delete from mysql.user where not (host="localhost" and user ="root");
Query OK, 1 row affected (0.00 sec)
์ผ๋ฐ ์ ์ธ ์ญ์ ๋ฐฉ๋ฒ
mysql> select host, user, password from user;
| localhost | linux | *DDC477F7A67434E2DA69BD5C6ED8109C84D91CE7 |
| localhost | linux2 | *DDC477F7A67434E2DA69BD5C6ED8109C84D91CE7 |
mysql> delete from user where user = 'linux'; ==>๋ฆฌ๋
์ค๋ผ๋ ๊ณ์ ์ ์ญ์ ํ๋ค.
Query OK, 1 row affected (0.03 sec)
mysql> delete from user where user = 'linux2'; ==> ๋ง์ฐฌ๊ฐ์ง๋ก ์ญ์
Query OK, 1 row affected (0.00 sec)
mysql> delete from db where user = 'blog'; ==> ๋ถ๋ก๊ทธ๋ผ๋ ์ฌ์ฉ์์ ๋๋น ๊ถํ์ ์ญ์ ํ๋ค.
Query OK, 0 rows affected (0.00 sec)
mysql> show databases; ==> ๋ฑ๋ก์ค์ธ ๋๋น ๋ฆฌ์คํธ ์ถ๋ ฅ
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| phpmyadmin |
+--------------------+
3 rows in set (0.01 sec)
mysql> use mysql; ==> ๋๋น ์ ํ ์ด๋
Database changed
mysql> select host, db, user from db; ==> ํธ์คํธ ๋ฐ ๋๋น๋ช
์ ์ ์ ๋ณด ์ถ๋ ฅ
+-----------+------------+------------+
| host | db | user |
+-----------+------------+------------+
| localhost | phpmyadmin | phpmyadmin |
+-----------+------------+------------+
1 row in set (0.01 sec)
mysql> select host, user, password from user; ==> ๊ณ์ ์ด๋ฆ ๋ฐ ํจ์ค์๋ ์ ๋ณด ์ถ๋ ฅ
+-----------+------------+-------------------------------------------+
| host | user | password |
+-----------+------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EXXXXX7DC67ED2CA2AD9 |
| localhost | phpmyadmin | *387B36DZZZXXXC5EC3AFXXXXXDAA274EF94EF |
+-----------+------------+-------------------------------------------+
2 rows in set (0.06 sec)
4. ์ผ๋ฐ ๊ณ์ ๊ถํ์ผ๋ก ๋๋น ์์ฑํ๊ณ ๋๋น ์ญ์ ํ๋ ๋ฐฉ๋ฒ
C:\>mysql -h localhost -u sunny -p mysql ==> ์ผ๋ฐ ๊ณ์ ์ ์
Enter password: **********************
mysql> create database mydb1; ==> ์ฌ์ฉ ๋๋น ์์ฑ
Query OK, 1 row affected (0.08 sec)
mysql> create database mydb2; ==> 2์ฐจ ๋๋น ์์ฑ
Query OK, 1 row affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)
mysql> use mydb1;
mysql> show tables; ==> ํ
์ด๋ธ ๋ณด๊ธฐ
Empty set (0.01 sec)
mysql> create table phpbb_cool( name CHAR(10), id VARCHAR(10) NOT NULL, url VARC
HAR(20), email VARCHAR(20), milage INT(11), level CHAR(2), PRIMARY KEY(id) ); ==> ํ
์ด๋ธ ์์ฑ
Query OK, 0 rows affected (0.08 sec)
mysql> drop table phpbb_cool; ==> ํด๋น ํ
์ด๋ธ ์ญ์
mysql> drop database mydb1; ==> ํด๋น ๋๋น๋ช
์ญ์
Query OK, 1 row affected (0.02 sec)
mysql> drop database mydb2;
Query OK, 0 rows affected (0.01 sec)
ํน์ ๋ฐ์ดํ๋ฒ ์ด์ค์ admin ๊ถํ์ด ์๋ ๋ค๋ฅธ ์ ์ ๋ฅผ ์ถ๊ฐํ๊ธฐ
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE
TEMPORARY TABLES, LOCK TABLES ON sunny_bbs.* TO 'sunny'@'localhost' IDENTIFIED BY '1234567';
Query OK, 0 rows affected (0.00 sec)
5. ๋๋น ์ฌ์ฉ์ ์ถ๊ฐํ๊ณ ํจ์ค์๋ ์ค์ ๋ฐฉ๋ฒ
๋ฐ์ดํฐ ๋ฒ ์ด์ค ํ
์ด๋ธ ๊ตฌ์กฐ๋ณด๊ธฐ
C:\>mysqlshow -h localhost -u root -p
Enter password: ******
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| phpmyadmin |
+--------------------+
ํ๋์ ๋ด์ฉ๋ณด๊ธฐ
C:\>mysqlshow -h localhost mysql -u root -p
Enter password: ******
Database: mysql
+---------------------------+
| Tables |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| time_zone_transition_type |
| user |
+---------------------------+