centos7 MariaDB 10 설치와 설정

centos7 MariaDB 10 설치와 설정

.yum 저장소 생성

  # yum install -y epel-release

  # rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm

  ]# vi /etc/yum.repos.d/MariaDB.repo 

  [mariadb]

  name = MariaDB

  baseurl = http://yum.mariadb.org/10.1/centos7-amd64

  gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

  gpgcheck=1

  참고 : 저장소 파일 생성시 앞부분 공백이 있으면  아래와 같은 애러가 나는 것 같음.

  ]# yum install MariaDB-server MariaDB-client MariaDB-compat

Loaded plugins: fastestmirror, langpacks

File contains parsing errors: file:///etc/yum.repos.d/MariaDB.repo

        [line  2]:      name = MariaDB

        [line  3]:      baseurl = http://yum.mariadb.org/10.1/centos7-amd64

        [line  4]:      gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

        [line  5]:      gpgcheck=1

2. yum으로 MariaDB 설치

  # yum install MariaDB-server MariaDB-client MariaDB-compat 

3. 메모리 설정 - 주로 2GB를 사용함

   - 512MB: my-large.cnf

   - 2GB: my-huge.cnf

   - 4GB: my-innodb-heavy-4G.cnf

   # cp -av /usr/share/mysql/my-huge.cnf /etc/my.cnf.d/ 

4. MariaDB 옵션 설정

   ##  언어셋 및 옵션설정 - 꼭 하지 않아도 무관

   # vi /etc/my.cnf.d/server.cnf 

      [client]

      default-character-set=utf8mb4

      [mysql]

      default-character-set=utf8mb4

      [mysqld]

port = 3306

#socket = /tmp

thread_concurrency = 8

# 이부분 추가 - 동시 접속자수 제한 늘리기

max_connections = 505   

  

      collation-server = utf8mb4_unicode_ci

      #init-connect=SET NAMES utf8mb4

      character-set-server = utf8mb4

      max_allowed_packet = 32M

      slow_query_log

      long_query_time = 2      

      [mysqldump]

      default-character-set=utf8mb4

      max_allowed_packet = 32M

5. MariaDB 서비스 설정 및 시작 그리고 확인

   # systemctl start mariadb

   # systemctl enable mariadb

   # systemctl status mariadb 

   

   버젼확인

   ]# mysql -V

      mysql  Ver 15.1 Distrib 10.1.26-MariaDB, for Linux (x86_64) using readline 5.1

6.db 설정

]# mysql_secure_installation

Enter current password for root (enter for none):  //엔터

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

Set root password? [Y/n] y

New password: //루트패스워드 입력

Re-enter new password: //패스워드 제입력

       # 나머지는 모두 엔터로 통과하셔도 됩니다.

       Password updated successfully!

Reloading privilege tables..

 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

to log into MariaDB without having to have a user account created for

them.  This is intended only for testing, and to make the installation

go a bit smoother.  You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n]

 ... Success!

Normally, root should only be allowed to connect from localhost.  This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]

 ... Success!

By default, MariaDB comes with a database named test that anyone can

access.  This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n]

 - Dropping test database...

 ... Success!

 - Removing privileges on test database...

 ... Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n]

 ... Success!

Cleaning up...

All done!  If youve completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

         

7. 데이터 베이스 접속

   - 앞서 설정한 root 패스워드 입력

   # mysql -u root -p

      Enter password: 

     MariaDB [(none)]> show databases;

     +--------------------+

     | Database           |

     +--------------------+

     | information_schema |

     | mysql              |

     | performance_schema |

     +--------------------+

     3 rows in set (0.00 sec)

MariaDB [(none)]> use mysql;

Database changed

MariaDB [mysql]> select host, user, password from user;

+-----------+------+-------------------------------------------+

| host      | user | password                                  |

+-----------+------+-------------------------------------------+

| localhost | root | *810DDCCF750D7230EF9357B05430CCCB5BB843AB |

| 127.0.0.1 | root | *810DDCCF750D7230EF9357B05430CCCB5BB843AB |

| ::1       | root | *810DDCCF750D7230EF9357B05430CCCB5BB843AB |

+-----------+------+-------------------------------------------+

3 rows in set (0.00 sec)

select * from user;

이 경우 host 를 %로 해 주어야 외부에서도 접속이 가능해 집니다..

 INSERT INTO mysql.user (Host, User, Password) VALUES (%, root, password(패스워드));

-- %를 쓰지 않고 localhost를 쓸 경우 remote 접속이 불가능하게 되므로 주의

mysql> update mysql.user set password = password(root-password) where user = root;

MariaDB [mysql]> select host, user, password from user;                         

+-----------+------+-------------------------------------------+

| host      | user | password                                  |

+-----------+------+-------------------------------------------+

| localhost | root | *810DDCCF750D7230EF9357B05430CCCB5BB843AB |

| 127.0.0.1 | root | *810DDCCF750D7230EF9357B05430CCCB5BB843AB |

| ::1       | root | *810DDCCF750D7230EF9357B05430CCCB5BB843AB |

| %         | root | *810DDCCF750D7230EF9357B05430CCCB5BB843AB |

+-----------+------+-------------------------------------------+

4 rows in set (0.00 sec)

      MariaDB [mysql]>FLUSH privileges;

■ MySQL 에서 저장된 DATABASE 경로 확인하는 방법

 

1.  #mysql -u root -p

2. root 패스워드 입력

3. mysql> show variables like datadir; 

MariaDB [(none)]> show variables like datadir;

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| datadir       | /var/lib/mysql/ |

+---------------+-----------------+

1 row in set (0.00 sec)

■ my.cnf 수정 

  Mysql 동시 접속자수 제한 늘리기

]# vi /etc/my.cnf.d/server.cnf

  

 [mysqld]

port = 3306

socket = /tmp/mysql.sock

……

thread_concurrency = 8

# 이부분 추가

max_connections = 505   

]# systemctl restart mariadb

확인

# mysql -u root -p

Enter password:

MariaDB [(none)]> show variables like %max_connect%;

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| extra_max_connections | 1     |

| max_connect_errors    | 10    |

| max_connections       | 505   |

+-----------------------+-------+

3 rows in set (0.00 sec)