* 특정계정 정보

 

sp_displaylogin userA

 

 

 

* 계정별 가진 권한 확인

 

use master
go

 

select t1.name, t3.name
from syslogins t1, sysloginroles t2, syssrvroles t3
where t2.suid = t1.suid and
t3.srid = t2.srid
order by 1,2
go

 

--------- -------------- 
userA      sa_role

 

* 특정 role을 누가 가지고 있는지 확인

 

use master
go

 

select sr.name, sl.name
from syssrvroles sr, syslogins sl, sysloginroles slr
where sl.suid = slr.suid
and sr.srid = slr.srid
and sr.name like "sa_role"

 

 

------- ---------
sa_role sa
 

 

 

 

* Role 부여

 

Syntax

sp_role {"grant" | "revoke"}, rolename, loginame

 

ex) sp_role 'grant', test_user_role, userB

 

      또는 grant create table to test_user_role

 

 

 

* role에 부여된 권한 확인

 

sp_helprotect test_user_role

 

 

1. 복제 설정

1.1 GRANT REPLICATION SLAVE

 

@all

 

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*

TO replicant@'%'

IDENTIFIED BY 'replicant';

 

 

1.2 my.cnf 편집

 @Master

 

 cd /var/log

 mkdir /var/log/mysql

 

 chown –R mysql.mysql ./mysql

 

 service mysqld stop

 

  vi /etc/my.cnf

 

log_bin = /var/log/mysql/mariadb-bin

 

[mysqld]

server_id = 101

relay_log = db01-relay-binlog

@Slave

 

service mysqld stop

 

vi /etc/my.cnf

 

[mysqld]

server_id = 102

read_only
 

@Master, Slave

service mysqld start

 

1.3 복제 설정

@Master

 

Ifconfig

 

mysql -u root -p

show master status;

@Slave

 

CHANGE MASTER TO MASTER_HOST='10.10.10.51',

MASTER_USER = 'replicant',

MASTER_PASSWORD = 'replicant',

MASTER_PORT=33060,

MASTER_LOG_FILE = 'mariadb-bin.000002',

MASTER_LOG_POS = 330;

 

START SLAVE; 

 

2. 복제 동작 확인

2.1show slave status 로 확인

 

@Slave

 

 

SHOW SLAVE STATUS;

Slave_IO_Running | Slave_SQL_Running  => yes 인가?

2.2 global_status 에서 확인

 

use mysql

 

SELECT variable_value

FROM information_schema.global_status

WHERE variable_name='SLAVE_RUNNING';

 

3. 복제 테스트

3.1 기본 설정

 

@Master

 

CREATE DATABASE IF NOT EXISTS temp;

USE temp;

 

CREATE TABLE doctors(

id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

given_names varchar(255),

surname varchar(255),

birthdate date);

 

INSERT INTO doctors VALUES(1,'William','Hartnell','1908-01-08');

INSERT INTO doctors VALUES(2,'Patrick','Troughton','1920-03-25');

INSERT INTO doctors VALUES(3,'John','Pertwee','1919-07-07');

INSERT INTO doctors VALUES(4,'Tom','Baker','1934-01-20');

 

 

@Slave

 

show databases;

use temp

show tables;

select * from doctors;

 

3.2 Replicate_ignore_db

 

 

-     stop slave, 확인

 

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> SELECT variable_value

               -> FROM information_schema.global_status

               -> WHERE variable_name='SLAVE_RUNNING';

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

| variable_value |

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

| OFF            |

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

1 row in set (0.00 sec)

 

MariaDB [(none)]> exit

Bye

 

-     my.cnf 에 replicate_ignore_db 추가

 

[mysql@localhost ~]$ vi /etc/my.cnf

 

...

[mysqld]

replicate_ignore_db='information_schema'

replicate_ignore_db='performance_schema'

replicate_ignore_db='mysql'

...

 

 

-    mysqld 재기동, slave 재시작

 

[mysql@localhost ~]$ service mysqld restart

 

 

[mysql@localhost ~]$ sql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 13

Server version: 10.2.7-MariaDB-log MariaDB Server

 

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

 

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

 

MariaDB [(none)]> start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [(none)]> show slave status;

 

 

-     복제 테스트 ( ignore 확인)

 

@Master

 

MariaDB [temp]> use mysql

Database changed

MariaDB [mysql]> show tables;

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

| Tables_in_mysql           |

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

| column_stats              |

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| gtid_slave_pos            |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| index_stats               |

| innodb_index_stats        |

| innodb_table_stats        |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| reptest                   |

| roles_mapping             |

| servers                   |

| slow_log                  |

| table_stats               |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

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

31 rows in set (0.00 sec)

 

MariaDB [mysql]> create table aaaa(a int);

Query OK, 0 rows affected (0.02 sec)

 

 

 

MariaDB [(none)]> use mysql

Database changed

MariaDB [mysql]> show tables;

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

| Tables_in_mysql           |

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

| column_stats              |

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| gtid_slave_pos            |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| index_stats               |

| innodb_index_stats        |

| innodb_table_stats        |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| reptest                   |

| roles_mapping             |

| servers                   |

| slow_log                  |

| table_stats               |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

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

31 rows in set (0.00 sec)

 

 

-    복제 테스트 ( 나머지는 되는가 확인)

 

MariaDB [mysql]> use temp

Database changed

MariaDB [temp]> show tables;

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

| Tables_in_temp |

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

| a              |

| doctors        |

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

2 rows in set (0.00 sec)

 

MariaDB [temp]> create table aaa(a int);

Query OK, 0 rows affected (0.02 sec)

 

 

 

MariaDB [mysql]> use temp;

Database changed

MariaDB [temp]> show tables;

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

| Tables_in_temp |

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

| a              |

| aaa            |

| doctors        |

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

3 rows in set (0.00 sec)

 

- 1. mysql client 접속 

mysql -u root -p

use mysql
select host, user, password from user;

 

 - 2.1 create user 구문으로 유저생성

 

create user 'test'@'%' identified by 'test';

CREATE USER 'test'@'10.10.10.10' IDENTIFIED WITH 'mysql_native_password' AS
  #authentication_string# REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
=> IP추가 시 비밀번호 직접 넣지 않고 동일하게 생성

 - 2.2 insert 구문으로 유저생성

 

insert into user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject, authentication_string)
values ('localhost','test', password('test'),'','','','');

 

insert into user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject, authentication_string)
values ('%','test', password('test'),'','','','');

 

flush privileges;     -- * insert 구문으로 유저생성시엔 반드시 flush 해줘야 적용됨.

 

 - 3. 권한 grant

 

grant all privileges on temp.* to test@'localhost';
grant all privileges on temp.* to test@'%';

grant all privileges on *.* to test@'%';

 

  - 4. 권한 확인

 

show grants for test@'%';