Data Science Lab

Host

IP

VIP

Role

DevMMM-Witness

192.168.0.1

Monitoring

DevMySQLMMM-1

192.168.0.2

192.168.0.102

Master 1

DevMySQLMMM-2

192.168.0.3

192.168.0.103

Master 2

DevMMM-Witness

DevMySQLMMM-1

DevMySQLMMM-2

MMM에 사용할 계정 생성

CREATE USER 'mmm_monitor'@'%' IDENTIFIED BY 'PASSWORD';

CREATE USER 'mmm_agent'@'%' IDENTIFIED BY 'PASSWORD';

GRANT ALL PRIVILEGES on *.* TO'mmm_monitor'@'%';

GRANT ALL PRIVILEGES on *.* TO'mmm_agent'@'%';

FLUSH PRIVILEGES;

호스트에 사용자 계정 추가

useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

MMM 설치

sudo yum install mysql-mmm mysql-mmm-monitor

sudo yum install mysql-mmm mysql-mmm-agent

sudo yum install mysql-mmm mysql-mmm-agent

mmm_common.conf 설정

sudo vi /etc/mysql-mmm/mmm_common.conf

아래 설정 값 입력

active_master_role          writer

<host default>

        cluster_interface eth0

        pid_path /run/mysql-mmm-agent.pid

        bin_path /usr/libexec/mysql-mmm/

        replication_user repluser

        replication_password PASSWORD

        agent_user mmm_agent

        agent_password PASSWORD

</host>

<host DevMySQLMMM-1>

        ip 192.168.0.2

        mode master

        peer DevMySQLMMM-2

              mysql_port 3306

</host>

<host DevMySQLMMM-2>

        ip 192.168.0.3

        mode master

        peer DevMySQLMMM-1

              mysql_port 3306

</host>

<role writer>

        hosts DevMySQLMMM-1, DevMySQLMMM-2

        ips 192.168.0.101

        mode exclusive

</role>

<role reader>

        hosts DevMySQLMMM-1,DevMySQLMMM-2

        ips 192.168.0.102,192.168.0.103

        mode balanced

</role>

mmm_agent.conf 설정

sudo vi /etc/mysql-mmm/mmm_agent.conf

아래 설정 값 입력

include mmm_common.conf

this DevMySQLMMM-1

sudo vi /etc/mysql-mmm/mmm_agent.conf

아래 설정 값 입력

include mmm_common.conf

this DevMySQLMMM-2

mmm_mon.conf 설정

sudo vi /etc/mysql-mmm/mmm_mon.conf

아래 설정 값 입력

include mmm_common.conf

<monitor>

    ip                  127.0.0.1

    pid_path            /run/mysql-mmm-monitor.pid

    bin_path            /usr/libexec/mysql-mmm/

    status_path         /var/lib/mysql-mmm/mmm_mond.status

    ping_ips            192.168.0.2,192.168.0.2

    auto_set_online     60

</monitor>

<host default>

    monitor_user        mmm_monitor

    monitor_password    PASSWORD

</host>

debug 0

부팅시 자동 시작 등록

sudo systemctl enable mysql-mmm-monitor

sudo systemctl enable mysql-mmm-agent

sudo systemctl enable mysql-mmm-agent

MMM 시작

sudo systemctl start mysql-mmm-monitor

sudo systemctl start mysql-mmm-agent

sudo systemctl start mysql-mmm-agent

sudo mmm_control move_role writer DevMySQLMMM-1

mmm_control set_online mysqltest-2

ET

External table

IT

Internal table

PC

Assembly (CLR) stored procedure

PG

Plan guide

RF

Replication-filter-procedure

S

System table

SQ

Service queue

TA

Assembly (CLR) DML Trigger

X

Extended procedure

-

CLR User-Defined Data Type (UDDT)

C

Check constraint

D

Default constraint

PK

Primary key constraint

TR

DML trigger

UQ

Unique constraint

1

F

Foreign key constraint

Need to be dropped before tables, as described below.

2

V

View

Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.

FN

SQL scalar function

IF

SQL inline table-valued function

TF

SQL table-valued-function

3

P

Stored procedure

Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).

4

U

Table (user-defined)

Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.

5

TT

Table type

Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.

Alias types

6

SO

Sequence object

Dropped after tables/modules because they can't be dropped if any table or module references them.

R

Rule (old-style, stand-alone)

D

Old-style CREATE DEFAULT

Partition functions

7

Partition schemes

Can't be dropped until after partition functions.

8

SN

Synonym

I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).

9

Schemas

I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.

10

Roles and Users

For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas. 

(For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.) 

For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.

try { drop view 1; go; drop view 2; }

-- script to drop Foreign Keys

USE [master];

GO

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER TABLE ' + objectname + N'

    DROP CONSTRAINT ' + fkname + N';

END TRY

BEGIN CATCH

  SELECT N''FK ' + fkname + N' failed. Run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT fkname = QUOTENAME(fk.[name]),

    objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])

  FROM sys.foreign_keys AS fk

  INNER JOIN sys.objects AS t

  ON fk.parent_object_id = t.[object_id]

  INNER JOIN sys.schemas AS s

  ON t.[schema_id] = s.[schema_id]

) AS src;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop views

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  DROP VIEW ' + objectname + N';

END TRY

BEGIN CATCH

  SELECT N''View ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])

  FROM sys.views AS v

  INNER JOIN sys.schemas AS s

  ON v.[schema_id] = s.[schema_id]

  WHERE v.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND o.[type] IN ('FN','IF','TF')

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop procedures

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Procedure ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.[type] = 'P'

  AND o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop user tables

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Table ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.tables AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop table types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_table_type = 1

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop alias types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.user_type_id > 256

  AND o.is_table_type = 0

  AND o.is_assembly_type = 0  -- not a CLR UDT

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop sequences

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Sequence ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.sequences AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop old-style Rules

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Rule ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'R'

  AND parent_object_id = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop old-style Defaults

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Default ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'D'

  AND parent_object_id = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop partition functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_functions

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop partition schemes

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_schemes

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop synonyms

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Synonym ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.synonyms AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Schema ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name])

  FROM sys.schemas AS s

  WHERE [schema_id] BETWEEN 5 AND 16383

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to re-route default schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;

 END TRY

 BEGIN CATCH

  SELECT N''User ' + p.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS p

WHERE p.default_schema_name IS NOT NULL

AND NOT EXISTS

(

  SELECT 1

  FROM sys.schemas AS s

  WHERE name = p.default_schema_name

);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to change ownership of roles to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

INNER JOIN sys.database_principals AS u

ON r.owning_principal_id = u.principal_id

WHERE r.[type] = 'R'

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!

AND r.is_fixed_role = 0;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to change ownership of schemas to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;

 END TRY

 BEGIN CATCH

  SELECT N''Schema ' + name + N' failed - run the script again.'',

    ERROR_MESSAGE(); END CATCH

'

FROM sys.schemas

WHERE [schema_id] BETWEEN 5 AND 16383

AND principal_id BETWEEN 5 AND 16383;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to remove members from roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

' + CASE WHEN CONVERT(tinyint,

  PARSENAME(CONVERT(nvarchar(128),

  SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+

 THEN

  N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER '

   + QUOTENAME(m.name) + N';'

 ELSE

  N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name

    + ''', @membername = N''' + m.name + N''';'

 END + N'

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_role_members AS rm

INNER JOIN sys.database_principals AS r

ON rm.role_principal_id = r.principal_id

AND r.is_fixed_role = 0

INNER JOIN sys.database_principals AS m

ON rm.member_principal_id = m.principal_id;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  DROP ROLE ' + QUOTENAME(r.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

WHERE r.[type] = 'R'

AND r.name <> N'public'

AND r.is_fixed_role = 0;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop users

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  DROP USER ' + QUOTENAME(u.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''User ' + u.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS u

WHERE u.[type] IN ('U','S')

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##';

SELECT @sql;

--EXEC sys.sp_executesql @sql;