CentOS에 MySQL 설치 및 기본명령어, 백업방법

순서

1. 설치

2. 기본명령어

3. 백업방법

---

1. MySQL 설치

설치 유무확인

[user@localhost ~]# rpm -qa | grep ^mysql-server

만약 설치가 안되어 있는경우 아무것도 출력되지 않음

설치

[user@localhost ~]# yum install mysql-server

MySQL 실행

[user@localhost ~]# service mysqld start
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 localhost.localdomain 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 /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

MySQL의 관리자 비밀번호 설정

비밀번호를 잊어버렸을때도 이 방법으로 초기화한다.

[user@localhost ~]# /usr/bin/mysqladmin -u root password '설정할비밀번호'

접속

[user@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

---

2. MySQL 기본명령어

존재하는 Database 출력

mysql> SHOW DATABASES;

Database 생성

mysql> CREATE DATABASE 이름;

Database 제거

mysql> DROP DATABASE 이름;

유저생성

mysql> create user 유저명@localhost identified by '비밀번호';
Query OK, 0 rows affected (0.37 sec)

유저에게 DB 권한 부여

mysql> grant all privileges on DB이름.* to 유저명@localhost identified by '비밀번호' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)

유저제거

mysql> drop user 유저명@localhost;

유저목록 확인

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT User, Host from user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
|                  | localhost |
| debian-sys-maint | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

Table 생성 (DB안에 있는 데이터 저장소)

mysql> USE dbname;
mysql> CREATE TABLE users(
    -> username varchar(20),
    -> password varchar(100),
    -> email varchar(50),
    -> serveradd varchar(50),
    -> serverport int(10),
    -> serverusername varchar(50),
    -> serverpassword varchar(100)
    -> );
mysql> describe users;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| username       | varchar(20)  | YES  |     | NULL    |       |
| password       | varchar(100) | YES  |     | NULL    |       |
| email          | varchar(50)  | YES  |     | NULL    |       |
| serveradd      | varchar(50)  | YES  |     | NULL    |       |
| serverport     | int(10)      | YES  |     | NULL    |       |
| serverusername | varchar(50)  | YES  |     | NULL    |       |
| serverpassword | varchar(100) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec

테이블 내용 수정

예) users 테이블에서 id가 AAA인 항목을 BBB로 고치기

mysql> update info set id='BBB' where id='AAA';

테이블에서 값 제거

예) users 테이블에서 id가 BBB인 항목 제거

mysql> delete from users where id="BBB";

---

3. MySQL 데이터베이스 백업 및 복원

DB백업

[user@localhost ~]# mysqldump -u 아이디 -p 디비이름 > backupdb.sql

DB복원

[user@localhost ~]# mysql -u 아이디 -p 디비네임 < backupdb.sql

---