Converting non-CDB database as PDB in existing CDB

This is a short article on converting existing non-container (non-cdb) 12c database as pluggable database (pdb) to existing container database (cdb).

Version of Non-CDB database = 12.1.0.1

Version of CDB database = 12.1.0.2

Following steps will plug-in non-cdb database into cdb as pdb and also will upgrade the version to 12.1.0.2

My non-cdb database name is deo12c

My cdb database name is deocdb

Step 1) Make sure that non-cdb database is at least 12.1.0.1 version. This is the first version available.

Step 2) Compatible is set to at least 12.0.0 in non-cdb database

Step 3) Open non-cdb database in read-only to create XML file for the PDB

Shutting down non-cdb database (deo12c)

SYS.DEO12C.PRIMARY>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Mounting the database and opening in read-only

SYS.DEO12C.PRIMARY>startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size            3719064 bytes
Variable Size         4177526888 bytes
Database Buffers      100663296 bytes
Redo Buffers           13058048 bytes
Database mounted.
SYS.DEO12C.PRIMARY>alter database open read only;

Database altered.

SYS.DEO12C.PRIMARY>

Step 4) Create XML file for PDB

SYS.DEO12C.PRIMARY-RO>exec DBMS_PDB.DESCRIBE('/fs-a01-a/backups/deo12c/deopdb2.xml');

PL/SQL procedure successfully completed.

Step 5) Shutdown non-cdb database

SYS.DEO12C.PRIMARY-RO>SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS.DEO12C.PRIMARY-RO>

Step 6) Plugin non-cdb database as PDB into CDB

Running following command on CDB database – deocdb

SYS.DEOCDB.PRIMARY>CREATE PLUGGABLE DATABASE deopdb2 USING '/fs-a01-a/backups/deo12c/deopdb2.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SYS.DEOCDB.PRIMARY>

Step 7) Convert the dictionary of new plugged in database to the PDB type

Change the container to new pluggable database

SYS.DEOCDB.PRIMARY>ALTER SESSION set container=deopdb2;

Session altered.

SYS.DEOCDB.PRIMARY>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 8) Start new pluggable database

SYS.DEOCDB.PRIMARY>alter pluggable database deopdb2 open;

Warning: PDB altered with errors.

When we opened the pluggable database, we got a warning. You can check the details of warning by querying PDB_PLUG_IN_VIOLATIONS table

SYS.DEOCDB.PRIMARY>select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name = 'DEOPDB2' and status != 'RESOLVED';

NAME                   CAUSE                  TYPE    MESSAGE                        STATUS
------------------------------ ------------------------------ --------- -------------------------------------------------- ---------
DEOPDB2                NATIONAL CHARACTER SET          ERROR    National character set mismatch: PDB national char PENDING
                                    acter set AL16UTF16. CDB national character set UT
                                    F8.

DEOPDB2                OPTION                  WARNING    Database option APS mismatch: PDB installed versio PENDING
                                    n NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option CATJAVA mismatch: PDB installed ve PENDING
                                    rsion NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option CONTEXT mismatch: PDB installed ve PENDING
                                    rsion NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option DV mismatch: PDB installed version PENDING
                                     NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option JAVAVM mismatch: PDB installed ver PENDING
                                    sion NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option OLS mismatch: PDB installed versio PENDING
                                    n NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option ORDIM mismatch: PDB installed vers PENDING
                                    ion NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option OWM mismatch: PDB installed versio PENDING
                                    n NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option SDO mismatch: PDB installed versio PENDING
                                    n NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option XML mismatch: PDB installed versio PENDING
                                    n NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                OPTION                  WARNING    Database option XOQ mismatch: PDB installed versio PENDING
                                    n NULL. CDB installed version 12.1.0.2.0.

DEOPDB2                APEX                  WARNING    APEX mismatch: PDB installed version NULL CDB inst PENDING
                                    alled version 4.2.5.00.08


13 rows selected.

These warnings can be ignored. These are basically coming because dba_registry of container database doesn’t match new pluggable database.

In my case container database has following registry

SYS.DEOCDB.PRIMARY>alter session set container=cdb$root;

Session altered.

SYS.DEOCDB.PRIMARY>col comp_name format a40;
SYS.DEOCDB.PRIMARY>SELECT comp_id,comp_name,version FROM dba_registry;

COMP_ID                COMP_NAME                VERSION
------------------------------ ---------------------------------------- ------------------------------
DV                   Oracle Database Vault            12.1.0.2.0
APEX                   Oracle Application Express        4.2.5.00.08
OLS                   Oracle Label Security            12.1.0.2.0
SDO                   Spatial                    12.1.0.2.0
ORDIM                   Oracle Multimedia            12.1.0.2.0
CONTEXT                Oracle Text                12.1.0.2.0
OWM                   Oracle Workspace Manager         12.1.0.2.0
XDB                   Oracle XML Database            12.1.0.2.0
CATALOG                Oracle Database Catalog Views        12.1.0.2.0
CATPROC                Oracle Database Packages and Types    12.1.0.2.0
JAVAVM                   JServer JAVA Virtual Machine        12.1.0.2.0
XML                   Oracle XDK                12.1.0.2.0
CATJAVA                Oracle Database Java Packages        12.1.0.2.0
APS                   OLAP Analytic Workspace            12.1.0.2.0
XOQ                   Oracle OLAP API                12.1.0.2.0
RAC                   Oracle Real Application Clusters     12.1.0.2.0

16 rows selected.

In my pluggable database, registry has only following components

SYS.DEOCDB.PRIMARY>alter session set container=deopdb2;

Session altered.

SYS.DEOCDB.PRIMARY>alter session set container=deopdb2;

Session altered.

SYS.DEOCDB.PRIMARY>SELECT comp_id,comp_name,version FROM dba_registry;

COMP_ID                COMP_NAME                VERSION
------------------------------ ---------------------------------------- ------------------------------
XDB                   Oracle XML Database            12.1.0.2.0
CATALOG                Oracle Database Catalog Views        12.1.0.2.0
CATPROC                Oracle Database Packages and Types    12.1.0.2.0
RAC                   Oracle Real Application Clusters     12.1.0.2.0

4 rows selected.

You can get rid of above warnings by installing the required components in your pluggable database and match it with container database.

References:

https://docs.oracle.com/database/121/UPGRD/upgrade.htm#CHDBEDDA

Hope this helps !!

MySQL Performance Schema

Performance schema, as name suggest is a tool for monitoring MySQL performance. It was implemented staring from MySQL 5.5 and improved further in MySQL 5.6. Performance Schema includes set of tables that gives information on how database is performing. It provides a way to inspect internal execution of the server at run time. Performance schema monitors every “event” the server does that takes time and has been instrumented so that timing information can be collected. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database.

Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata. Having its own engine allows us to access information about server execution while having minimal impact on server performance. Tables created in performance_schema are persistent in memory. Information from those tables will be wiped out if the database is rebooted. Memory allocation for performance_schema table is all done at server startup, so there is no further memory reallocation or sizing, which greatly streamlines performance.

In this article we will see how different tables are organized and what kind of information each table store. We will also see how we can customize instrumentation in performance schema to gather required performance stats. Performance schema is a very big topic and there have been very exhaustive documentation on the same. I tried to make this article brief and cover major areas of performance schema. For further details readers can check out the document links provided in reference section.

Tables in Performance schema

We can categorize the tables in performance schema in 5 broad categories as described below.

1) Setup tables – These tables contains information about setting up monitoring/instrumentation. We can modify (update) these tables to customize what kind of performance data to be collected. These tables define who, what, how  to monitor, and where to save this data.

2) Current Events tables – These tables records performance data about the current events happening in database. This also depends on what kind of instrumentation we have enabled in setup tables. These tables contains most recently collected monitored events.

3) History events table – Performance data collected in current events table move to these history table after a period of time. So structure wise history tables are same as current events table but they contains historical data

4) Summary tables – These tables contacts performance data summarized over an entity. So performance data of events are summarized by various entities like account_id, host_name or by thread. These tables has aggregate value of events grouped by different entities.

5) Object Instances tables – These tables records what instances of objects (files, locks, mutexes, etc) are currently in use. Example if a mutex is currently in use, you will see an entry corresponding t that mutex in mutex_instances table.

Lets try to dig into each category and see what tables we have and what they store.

1) Setup tables

Most of the tables in performance_schema database is read only tables. There are very few tables which we can modify to adjust instrumentation. Setup tables are among those few tables which can be modified to define what kind of performance data we want to collect.

SETUP_ACTORS – This table defines who to monitor. We can insert username@hostname combination in this table that we want to monitor.

By default it has only 1 entry and every user is monitored

mysql> select * from setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+
1 row in set (0.00 sec)

 

 

SETUP_OBJECTS – This table defines which objects (table) to monitor. By details mysql does not monitor any activity in performance_schema, information_schema and MySQL databases. Everything else is monitored. If you are interested in monitoring only 1 or 2 databases, you can remove the entry of monitoring all databases and insert into databases that you want to monitor.

Default entries looks file following

mysql> select * from setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+
4 rows in set (0.02 sec)

 

SETUP_INSTRUMENTS – This table defines the “what” part – what to monitor. This is a major table in performance_schema database. This table has list of all instrumentation that is available in mysql. This table lists classes of instrumented objects for which events can be collected. Each instrument added to the source code provides a row for this table, even when the instrumented code is not executed. You can enable or disable each instrumentation in this table.

This table looks like following

mysql> select * from setup_instruments;
+---------------------------------------------------------------------------------------+---------+-------+
| NAME                                                                                  | ENABLED | TIMED |
+---------------------------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock                                                       | NO      | NO    |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync                                           | NO      | NO    |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active                                         | NO      | NO    |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool                                           | NO      | NO    |
| wait/synch/mutex/sql/LOCK_des_key_file                                                | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit                                       | NO      | NO    |
...
...
...
| statement/com/Set option                                                              | YES     | YES   |
| statement/com/Fetch                                                                   | YES     | YES   |
| statement/com/Daemon                                                                  | YES     | YES   |
| statement/com/Binlog Dump GTID                                                        | YES     | YES   |
| statement/com/Error                                                                   | YES     | YES   |
| statement/abstract/Query                                                              | YES     | YES   |
| statement/abstract/new_packet                                                         | YES     | YES   |
| statement/abstract/relay_log                                                          | YES     | YES   |
| wait/io/socket/sql/server_tcpip_socket                                                | NO      | NO    |
| wait/io/socket/sql/server_unix_socket                                                 | NO      | NO    |
| wait/io/socket/sql/client_connection                                                  | NO      | NO    |
| idle                                                                                  | YES     | YES   |
+---------------------------------------------------------------------------------------+---------+-------+

Instrument names have multiple parts and form a hierarchy. An instrument name consists of a sequence of components separated by ‘/’ characters. Example names:

wait/io/file/myisam/log
wait/io/file/mysys/charset
wait/lock/table/sql/handler
wait/synch/cond/mysys/COND_alarm
wait/synch/cond/sql/BINLOG::update_cond
wait/synch/mutex/mysys/BITMAP_mutex
wait/synch/mutex/sql/LOCK_delete
wait/synch/rwlock/sql/Query_cache_query::lock
stage/sql/closing tables
stage/sql/Sorting result
statement/com/Execute
statement/com/Query
statement/sql/create_table
statement/sql/lock_tables

The instrument name space has a tree-like structure. The components of an instrument name from left to right provide a progression from more general to more specific. The number of components a name has depends on the type of instrument.

The interpretation of a given component in a name depends on the components to the left of it. For example, myisam appears in both of the following names, but myisam in the first name is related to file I/O, whereas in the second it is related to a synchronization instrument:

wait/io/file/myisam/log
wait/synch/cond/myisam/MI_SORT_INFO::cond

SETUP_CONSUMERS – This defines type of consumers for which event information is getting collected. In a way this defines is history information should be kept or not or just current event information should be collected and stored.

mysql> select * from setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
| events_waits_current           | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+
12 rows in set (0.00 sec)

If you enable history table for any of the event type, mysql will keep history of performance data collecetd for that event type.
Instrument data will be collected for only those consumers which are enabled in above table.

While we are talking above setup_consumers table, I would like to explain about 3 consumers mentioned in above tables

global_instrumentation – is the highest level consumer. If global_instrumentation is NO, it disables all instrumentation. All other settings are lower level and are not checked; it does not matter what they are set to. If global_instrumentation is YES, the Performance Schema maintains information for global states and also checks the thread_instrumentation consumer.

thread_instrumentation – is checked only if global_instrumentation is YES. Otherwise, if thread_instrumentation is NO, it disables thread-specific instrumentation and all lower-level settings are ignored. No information is maintained per thread and no individual events are collected in the current-events or event-history tables. If thread_instrumentation is YES, the Performance Schema maintains thread-specific information and also checks events_xxx_current consumers.

statement_digest – This consumer requires global_instrumentation to be YES or it is not checked. There is no dependency on the Statement Event consumers. This means you can obtain statistics per digest without having to collect statistics in events_statements_current, which is advantageous in terms of overhead.

Following figure show instrumentation hierarchy:

instru

 

 

 

 

 

 

 

 

SETUP_TIMERS – This table defines that is the unit of values for all events that are collecting performance data. For some time of event it could be timing information, in that case it defines that unit of time that is used for those events. If for few events, unit should be number of instances occurring then this table should be updated to reflect the same.

By default it defines following unit of measure for each type of event

mysql> select * from setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | NANOSECOND  |
| statement | NANOSECOND  |
+-----------+-------------+
4 rows in set (0.00 sec)

CYCLE refers to the timer that is based on the CPU (processor) cycle counter

2) Current Events tables

Once you update setup tables to set correct monitoring in place, MySQL will start collecting performance data for the events for which instrumentation is enabled.
Following are the current tables

mysql> show tables like '%current%';
+------------------------------------------+
| Tables_in_performance_schema (%current%) |
+------------------------------------------+
| events_stages_current                    |
| events_statements_current                |
| events_waits_current                     |
+------------------------------------------+
3 rows in set (0.00 sec)

 

Each current table corresponds to high level instrument type. As we have seen above, instrument name is a tree type hierarchy structure and top of the tree is high level instrument type.
If we want to find out high level instrument name, we can use following SQL

mysql> select distinct substr(name,1,instr(name,'/')) from setup_instruments where name != 'idle';
+--------------------------------+
| substr(name,1,instr(name,'/')) |
+--------------------------------+
| wait/                          |
| stage/                         |
| statement/                     |
+--------------------------------+
3 rows in set (0.08 sec)

 

Statement – indicates a sql statement (like SELECT) or command (like CONNECT)
Stages  – is the stage of statement processing, the , like SORTING RESULT
Wait – is an instrumented wait event, like WAIT I/O/FILE or WAIT/LOCK
Idle – is when a connection is idle, that is,  a socket is idle if it is waiting for a request from the client

So corresponding to each of above instrument type, we have current tables.
These tables are populated they are enabled in setup_consumers tables. If you check above the output of setup_consumers table, you will see entries corresponding to these tables.

mysql> select * from setup_consumers where name like '%current%';
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| events_stages_current     | NO      |
| events_statements_current | YES     |
| events_waits_current      | NO      |
+---------------------------+---------+
3 rows in set (0.01 sec)

 

So by default, instrumentation for current statements are enabled by default

EVENTS_STAGES_CURRENT

This table contains current stage events, one row per thread showing the current status of the thread’s most recent monitored stage event.
Every statement passes through various stages and we can enable/disable instrumentation for every stage. If you have enabled instrumentation for a stage and if your statement is currently in that stage, we will see a record in this table.
Again, if query taking less time, you will hardly see anything in this table for that thread. Advice is to enable history table.

mysql> select * from setup_instruments where name like 'stage%';
+---------------------------------------------------------------------------------------+---------+-------+
| NAME                                                                                  | ENABLED | TIMED |
+---------------------------------------------------------------------------------------+---------+-------+
| stage/sql/After create                                                                | NO      | NO    |
| stage/sql/allocating local table                                                      | NO      | NO    |
| stage/sql/preparing for alter table                                                   | NO      | NO    |
| stage/sql/altering table                                                              | NO      | NO    |
| stage/sql/committing alter table to storage engine                                    | NO      | NO    |
| stage/sql/Changing master                                                             | NO      | NO    |
...
...
...

 

EVENTS_STATEMENTS_CURRENT

This table has data about current statement events, one row per thread showing the current status of the thread’s most recent monitored statement event.
Any user runs a statement, performance data about that statement gets recorded in this table.
Please note that this table stores only 1 row per thread, so as soon as user runs another SQL in same connection, previous SQL will be gone.
That’s why its useful to enable history tables in setup_consumers.

This table has many important columns to store performance data like

TIMER_START, TIMER_END, TIMER_WAIT – Which defines how much time the SQL took
LOCK_TIME – defines how much time lock was held
ROWS_AFFECTED, ROWS_SENT, ROWS_EXAMINED – defines how much work was done
CREATED_TMP_DISK_TABLES, CREATED_TMP_TABLES – defines if temp tables where created and if they got spiled over on disk
SELECT_FULL_JOIN, SELECT_FULL_RANGE_JOIN, SELECT_RANGE, SELECT_RANGE_CHECK, SELECT_SCAN – defines access path

mysql> select * from performance_schema.events_statements_current where thread_id = 31 \G
*************************** 1. row ***************************
              THREAD_ID: 31
               EVENT_ID: 89
           END_EVENT_ID: 112
             EVENT_NAME: statement/sql/select
                 SOURCE: mysqld.cc:939
            TIMER_START: 351432249673000000
              TIMER_END: 351432249845000000
             TIMER_WAIT: 172000000
              LOCK_TIME: 73000000
               SQL_TEXT: select * from event
                 DIGEST: 4f58e5f352636c00d01edc576732852b
            DIGEST_TEXT: SELECT * FROM EVENT
         CURRENT_SCHEMA: deo
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 20
          ROWS_EXAMINED: 20
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
1 row in set (0.00 sec)

EVENTS_WAITS_CURRENT

This table defines wait event information for current wait events, one row per thread showing the current status of the thread’s most recent monitored wait event.
If a thread has passed that wait event, it will remove that record from this table and insert another record about wait event that thread is experiencing right now.
Its bit difficult to use this view unless thread is experience waits currently and for longer time (enough to get hold of data from this table). Else its good idea to enable history tables.

mysql> select * from events_waits_current \G
*************************** 1. row ***************************
            THREAD_ID: 30
             EVENT_ID: 135
         END_EVENT_ID: 135
           EVENT_NAME: idle
               SOURCE: mysqld.cc:909
          TIMER_START: 351281970836000000
            TIMER_END: 351286937566000000
           TIMER_WAIT: 4966730000000
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
           INDEX_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 0
     NESTING_EVENT_ID: NULL
   NESTING_EVENT_TYPE: NULL
            OPERATION: idle
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL
1 row in set (0.00 sec)

You might see the same timing columns in this table as in events_statements_current table. But note that these timings column belongs to wait event. They represent how much was the wait time, whereas timings column in events_statements_current table represent how much was the time taken by the entire statement.

3) History events table

These tables as name says, stores historical information of the event that happened. As we have learned above that current table stores only current event that is happening for every thread. If the thread has passed that event, data in current table changes. So history tables are available in MySQL which will store history of passed event happened in a thread.
Corresponding to every current table, we have 2 history table _history and _history_long

mysql> show tables like '%history%';
+------------------------------------------+
| Tables_in_performance_schema (%history%) |
+------------------------------------------+
| events_stages_history                    |
| events_stages_history_long               |
| events_statements_history                |
| events_statements_history_long           |
| events_waits_history                     |
| events_waits_history_long                |
+------------------------------------------+
6 rows in set (0.00 sec)

_history table contains the most recent 10 records for every thread and _history_long table contains most recent 10,000 records for every thread.
Example if a thread is executing 20 SQL statements, events_statements_history will records latest 10 SQL statements executed by that thread, where as events_statements_history_long table will record all 20 sql statements. As new events are added to the table, older events are discarded if the table is full.  Events are not added to the table until they have ended.

Structure wise they are same as current table. They just store more data per thread.
You can enable history tables and history_long table from setup_consumers table

mysql> select * from setup_consumers where name like '%history%';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_history      | NO      |
| events_statements_history_long | NO      |
| events_waits_history           | NO      |
| events_waits_history_long      | NO      |
+--------------------------------+---------+
6 rows in set (0.00 sec)

If you think that storing 10 records in history table and 10000 records in history_long table is less, you can increase how many records you want to store in history and history long for each of the instrument type table.
Following system level variables are available which can be set at start of instance which defines how many records to store in these tables

mysql> show variables like 'performance_schema_%history%';
+--------------------------------------------------------+-------+
| Variable_name                                          | Value |
+--------------------------------------------------------+-------+
| performance_schema_events_stages_history_long_size     | 10000 |
| performance_schema_events_stages_history_size          | 10    |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size      | 10    |
| performance_schema_events_waits_history_long_size      | 10000 |
| performance_schema_events_waits_history_size           | 10    |
+--------------------------------------------------------+-------+
6 rows in set (0.00 sec)

Note that changing these values will need mysql reboot to take affect and also increasing these values means that you will need more memory to store these values. If you increase number of records setting in system variables above, but there is no sufficient memory available to store those many records, MySQL will not be able to load history table.
You can check if this situation is happening by tracking some status variables in MySQL. The Performance Schema implements several status variables that provide information about instrumentation that could not be loaded or created due to memory constraints:

mysql> show status like 'performance%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Performance_schema_accounts_lost              | 0     |
| Performance_schema_cond_classes_lost          | 0     |
| Performance_schema_cond_instances_lost        | 0     |
| Performance_schema_digest_lost                | 0     |
| Performance_schema_file_classes_lost          | 0     |
| Performance_schema_file_handles_lost          | 0     |
| Performance_schema_file_instances_lost        | 0     |
| Performance_schema_hosts_lost                 | 0     |
| Performance_schema_locker_lost                | 0     |
| Performance_schema_mutex_classes_lost         | 0     |
| Performance_schema_mutex_instances_lost       | 0     |
| Performance_schema_rwlock_classes_lost        | 0     |
| Performance_schema_rwlock_instances_lost      | 0     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Performance_schema_socket_classes_lost        | 0     |
| Performance_schema_socket_instances_lost      | 0     |
| Performance_schema_stage_classes_lost         | 0     |
| Performance_schema_statement_classes_lost     | 0     |
| Performance_schema_table_handles_lost         | 0     |
| Performance_schema_table_instances_lost       | 0     |
| Performance_schema_thread_classes_lost        | 0     |
| Performance_schema_thread_instances_lost      | 0     |
| Performance_schema_users_lost                 | 0     |
+-----------------------------------------------+-------+
23 rows in set (0.04 sec)

Each of the above status variables will get updated if corresponding performance schema table is not able to store records. We will see details about above variables in later section of this article.

 

4) Summary tables

Summary tables provide aggregated information for terminated events over time. The tables in this group summarize event data in different ways.
There are bunch of summary tables available in performance schema

 mysql> show tables like '%summary%';
+----------------------------------------------------+
| Tables_in_performance_schema (%summary%)           |
+----------------------------------------------------+
| events_stages_summary_by_account_by_event_name     |
| events_stages_summary_by_host_by_event_name        |
| events_stages_summary_by_thread_by_event_name      |
| events_stages_summary_by_user_by_event_name        |
| events_stages_summary_global_by_event_name         |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| events_waits_summary_by_account_by_event_name      |
| events_waits_summary_by_host_by_event_name         |
| events_waits_summary_by_instance                   |
| events_waits_summary_by_thread_by_event_name       |
| events_waits_summary_by_user_by_event_name         |
| events_waits_summary_global_by_event_name          |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |
| objects_summary_global_by_type                     |
| socket_summary_by_event_name                       |
| socket_summary_by_instance                         |
| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |
+----------------------------------------------------+
25 rows in set (0.00 sec)

Each summary table has grouping columns that determine how to group the data to be aggregated, and summary columns that contain the aggregated values
I will give 1 liner for few tables to understand how data is stored in these tables.

Event Wait Summaries:

  •     events_waits_summary_global_by_event_name: Wait events summarized per event name
  •     events_waits_summary_by_instance: Wait events summarized per instance
  •     events_waits_summary_by_thread_by_event_name: Wait events summarized per thread and event name

Stage Summaries:

  •     events_stages_summary_by_thread_by_event_name: Stage waits summarized per thread and event name
  •     events_stages_summary_global_by_event_name: Stage waits summarized per event name

Statement Summaries:

  •     events_statements_summary_by_thread_by_event_name: Statement events summarized per thread and event name
  •     events_statements_summary_global_by_event_name: Statement events summarized per event name

4.1 Digest summary Table

In above summary table, you see a table – events_statements_summary_by_digest
So what exactly is digest table

As of MySQL 5.6.5, the Performance Schema maintains statement digest information. Digesting converts a SQL statement to normalized form and computes a hash value for the result.
Normalization permits statements that are similar to be grouped and summarized to expose information about the types of statements the server is executing and how often they occur.

We can enable digest summary by enabling the same in setup_consumers table

The statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) have DIGEST and DIGEST_TEXT columns that contain digest MD5 values and the corresponding normalized statement text strings.

Normalizing a statement transforms the statement text to a more standardized string representation that preserves the general statement structure while removing information not essential to the structure.Object identifiers such as database and table names are preserved. Values and comments are removed, and whitespace is adjusted.

Consider these statements:

SELECT * FROM orders WHERE customer_id=10 AND quantity>20
SELECT * FROM orders WHERE customer_id = 20 AND quantity > 100

To normalize these statements, the Performance Schema replaces data values by ? and adjusts whitespace. Both statements yield the same normalized form and thus are considered “the same”:

SELECT * FROM orders WHERE customer_id = ? AND quantity > ?

The normalized statement contains less information but is still representative of the original statement. Other similar statements that have different comparison values have the same normalized form.

5) Object Instances tables

Instance tables records synchronization object instances. There are three types of synchronization objects: cond, mutex, and rwlock.

cond – represents condition. condition is a synchronization mechanism used in the code to signal that a specific event has happened, so that a thread waiting for this condition can resume work
mutex – mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to some common resource. The resource is said to be “protected” by the mutex.
rwlock – synchronization mechanism used in the code to enforce that threads at a given time can have access to some common resource following certain rules. The resource is said to be “protected” by the rwlock. The access is either shared (many threads can have a read lock at the same time) or exclusive (only one thread can have a write lock at a given time).

Corresponding to this we have following 5 instances table

mysql> select TABLE_NAME from information_schema.tables where TABLE_NAME like '%instance%' and table_name not like '%summary%';
+------------------+
| TABLE_NAME       |
+------------------+
| cond_instances   |
| file_instances   |
| mutex_instances  |
| rwlock_instances |
| socket_instances |
+------------------+
5 rows in set (0.03 sec)

cond_instances – table lists all the conditions seen by the Performance Schema while the server executes
file_instances – table lists all the files seen by the Performance Schema when executing file I/O instrumentation. If a file on disk has never been opened, it will not be in file_instances. When a file is deleted from the disk, it is also removed from the file_instances table.
mutex_instances – table lists all the mutexes seen by the Performance Schema while the server executes.
rwlock_instances – table lists all the rwlock instances (read write locks) seen by the Performance Schema while the server executes.
socket_instances – table provides a real-time snapshot of the active connections to the MySQL server.

You can enable this instrumentation using setup_instruments table. Every instance tables mentioned above will get an entry if those events are enabled and executed.

setup_instruments table has many instruments as explained above. Enabling these instruments will populate specific instances table

 

+------------------------------------------------------------+---------+-------+
| NAME                                                       | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock                            | NO      | NO    |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync                | NO      | NO    |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active              | NO      | NO    |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool                | NO      | NO    |
| wait/synch/mutex/sql/LOCK_des_key_file                     | NO      | NO    |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit            | NO      | NO    |
...
| wait/synch/rwlock/sql/Binlog_transmit_delegate::lock       | NO      | NO    |
| wait/synch/rwlock/sql/Binlog_relay_IO_delegate::lock       | NO      | NO    |
| wait/synch/rwlock/sql/LOCK_grant                           | NO      | NO    |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger                  | NO      | NO    |
| wait/synch/rwlock/sql/LOCK_sys_init_connect                | NO      | NO    |
| wait/synch/rwlock/sql/LOCK_sys_init_slave                  | NO      | NO    |
...
| wait/synch/cond/sql/TC_LOG_MMAP::COND_active               | NO      | NO    |
| wait/synch/cond/sql/TC_LOG_MMAP::COND_pool                 | NO      | NO    |
| wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done               | NO      | NO    |
| wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond             | NO      | NO    |
| wait/synch/cond/sql/MYSQL_BIN_LOG::prep_xids_cond          | NO      | NO    |
| wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_done             | NO      | NO    |
...
| wait/io/file/sql/map                                       | YES     | YES   |
| wait/io/file/sql/binlog                                    | YES     | YES   |
| wait/io/file/sql/binlog_index                              | YES     | YES   |
| wait/io/file/sql/relaylog                                  | YES     | YES   |
| wait/io/file/sql/relaylog_index                            | YES     | YES   |
| wait/io/file/sql/casetest                                  | YES     | YES   |
...
| wait/io/socket/sql/server_tcpip_socket                     | NO      | NO    |
| wait/io/socket/sql/server_unix_socket                      | NO      | NO    |
| wait/io/socket/sql/client_connection                       | NO      | NO    |
+------------------------------------------------------------+---------+-------+

Lets take a example of mutex_instance table and understand how it works and how these tables play a role.

The mutex_instances table lists all the mutexes seen by the Performance Schema while the server executes. A mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to some common resource. The resource is said to be “protected” by the mutex.

When two threads executing in the server (for example, two user sessions executing a query simultaneously) do need to access the same resource (a file, a buffer, or some piece of data), these two threads will compete against each other, so that the first query to obtain a lock on the mutex will cause the other query to wait until the first is done and unlocks the mutex.

For every mutex instrumented in the code, the Performance Schema provides the following information.

  •     The setup_instruments table lists the name of the instrumentation point, with the prefix wait/synch/mutex/.
  •     When some code creates a mutex, a row is added to the mutex_instances table. The OBJECT_INSTANCE_BEGIN column is a property that uniquely identifies the mutex.
  •     When a thread attempts to lock a mutex, the events_waits_current table shows a row for that thread, indicating that it is waiting on a mutex (in the EVENT_NAME column), and indicating which mutex is waited on (in the OBJECT_INSTANCE_BEGIN column).
  •     When a thread succeeds in locking a mutex:
    • events_waits_current shows that the wait on the mutex is completed (in the TIMER_END and TIMER_WAIT columns)
    • The completed wait event is added to the events_waits_history and events_waits_history_long tables
    • mutex_instances shows that the mutex is now owned by the thread (in the THREAD_ID column).
  •     When a thread unlocks a mutex, mutex_instances shows that the mutex now has no owner (the THREAD_ID column is NULL).
  •     When a mutex object is destroyed, the corresponding row is removed from mutex_instances.

By performing queries on both of the following tables, a monitoring application or a DBA can detect bottlenecks or deadlocks between threads that involve mutexes:

  •     events_waits_current, to see what mutex a thread is waiting for
  •     mutex_instances, to see which other thread currently owns a mutex

 

6) Other remaining tables

Apart from above 5 categories there are few other tables which records account level and thread level information.

account table – Connection statistics per client account. “Account” here means username + hostname combination


mysql> select * from accounts;
+------+-----------+---------------------+-------------------+
| USER | HOST      | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+-----------+---------------------+-------------------+
| NULL | NULL      |                  20 |                26 |
| root | localhost |                   1 |                 7 |
+------+-----------+---------------------+-------------------+
2 rows in set (0.01 sec)

users table – Connection statistics per client user name


mysql> select * from users;
+------+---------------------+-------------------+
| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+---------------------+-------------------+
| NULL |                  20 |                26 |
| root |                   1 |                 7 |
+------+---------------------+-------------------+
2 rows in set (0.01 sec)

hosts table – Connection statistics per client host name


mysql> select * from hosts;
+-----------+---------------------+-------------------+
| HOST      | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+-----------+---------------------+-------------------+
| NULL      |                  20 |                26 |
| localhost |                   1 |                 7 |
+-----------+---------------------+-------------------+
2 rows in set (0.00 sec)

host_cache – Information from the internal host cache.

performance_timers – Shows which event timers are available

This table shows available times in your mysql server. If a timer is not listed in this table it cannot be used. value of timers for various event types set in setup_timers should be from performance_timers table


mysql> select * from performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2932537313 |                1 |             22 |
| NANOSECOND  |      1000000000 |             1000 |            264 |
| MICROSECOND |         1000000 |                1 |            116 |
| MILLISECOND |            1037 |                1 |            135 |
| TICK        |             101 |                1 |            254 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

threads – table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring is enabled for it

Performance Schema status variables

We have following performance_schema status variables


mysql> show status like '%performance%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Performance_schema_accounts_lost              | 0     |
| Performance_schema_cond_classes_lost          | 0     |
| Performance_schema_cond_instances_lost        | 0     |
| Performance_schema_digest_lost                | 0     |
| Performance_schema_file_classes_lost          | 0     |
| Performance_schema_file_handles_lost          | 0     |
| Performance_schema_file_instances_lost        | 0     |
| Performance_schema_hosts_lost                 | 0     |
| Performance_schema_locker_lost                | 0     |
| Performance_schema_mutex_classes_lost         | 0     |
| Performance_schema_mutex_instances_lost       | 0     |
| Performance_schema_rwlock_classes_lost        | 0     |
| Performance_schema_rwlock_instances_lost      | 0     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Performance_schema_socket_classes_lost        | 0     |
| Performance_schema_socket_instances_lost      | 0     |
| Performance_schema_stage_classes_lost         | 0     |
| Performance_schema_statement_classes_lost     | 0     |
| Performance_schema_table_handles_lost         | 0     |
| Performance_schema_table_instances_lost       | 0     |
| Performance_schema_thread_classes_lost        | 0     |
| Performance_schema_thread_instances_lost      | 0     |
| Performance_schema_users_lost                 | 0     |
+-----------------------------------------------+-------+
23 rows in set (0.00 sec)

If an instrumentation could not be loaded because of memory constraints, corresponding status variable will get updated.
Non-zero values of these variables does not mean there is any issue with end user operation. Queries and DMLs will get executed as expected. Non-Zero values of these variables only mean corresponding instrumentation is not getting recorded because of less memory.

Names for these variables have several forms:

Performance_schema_accounts_lost – The number of times a row could not be added to the accounts table because it was full.
Performance_schema_xxx_classes_lost – How many instruments of type xxx could not be loaded.
Performance_schema_hosts_lost – The number of times a row could not be added to the hosts table because it was full.
Performance_schema_xxx_instances_lost – How many instances of object type xxx could not be created.
Performance_schema_xxx_handles_lost – How many instances of object type xxx could not be opened.
Performance_schema_locker_lost – How many events are “lost” or not recorded for various reason.
Performance_schema_session_connect_attrs_lost – The number of times a connection attribute string was larger than the reserved storage.
Performance_schema_users_lost – The number of times a row could not be added to the users table because it was full.

 

References:

http://manuales.guebs.com/mysql-5.6/performance-schema.html

http://dev.mysql.com/doc/refman/5.6/en/performance-schema-quick-start.html

http://mysqlintheenterprise.com/2013/03/21/a-visual-guide-to-the-mysql-performance-schema/

http://www.databasejournal.com/features/mysql/an-overview-of-the-mysql-performance-schema.html

 

Creating Oracle 12c Multitenant Container Database

Oracle has come up with new feature called multitenant database. Using this feature we can have many pluggable databases plugged into single container database.
This article explains pluggable databases architecture and how to create them.
I am not covering administration part. Viewers can check the documentation mentioned in reference section to check administrative part for managing pluggable databases.

Multitenant Architecture

One of the high-profile new features of Oracle 12c Enterprise Edition(EE) is the multitenant option – multitenant container database (CDB). This option allows the creation of many pluggable databases (PDBs) inside a CDB. The PDBs share resources provided by the CDB, such as memory, background processes, UNDO, REDO, and control files. This enables more databases to run on a single platform than was possible with the former Oracle 11gR2 architecture.
You can consider all databases till 11g as non-container databases or non-CDB. In oracle 12c we can still create non-CDB databases using normal “create database” command and administration wise there is no difference in managing those 12c databases compared to 11g database.

Oracle Multitenant architecture contains following:

One root container database
This looks like a normal database, but the purpose is different. This stores Oracle-supplied metadata and common users. It has all component that a normal database has – controlfile, online redo logs, SYSTEM and SYSAUX tablespace, Pfile and SPfile, archive logs and flasback logs etc. You can also create additional users, tablespace in this database.

One seed pluggable database
This is a seed databases which gets created when we create container database. This is nothing but set of datafiles in different location. This seed database can be used to create new pluggable databases.

Zero or more user-created PDBs
We can create multiple pluggable databases in single container databases. There are different methods of creating pluggable databases. Using above seed database to create pluggable database is just 1 method. We can also clone existing pluggable database from same container database or different container database. We can also make other non-CDB oracle 12c databases as pluggable database in our container database. You can refer to doc mentioned in reference section for more details on this.

Following figure show multitenant architecture:

cdbContainer database is same as normal database having all required component same as normal database.
Pluggable database has its own SYSTEM, SYSAUX and data tablespace. All other components like UNDO tablespace, online redo logs, controlfile, archive logs etc are common for container database and pluggable database.

When compared to RAC, I feel this architecture is exactly opposite. In RAC we have multiple instances and 1 database. In multitenant architecture we have single instance and multiple databases.

Creating Multitenant Container database

Creating container database is similar to creating non-container database but with few changes. Following changes should be made if we want to create container database

1) Define enable_pluggable_database parameter to true.
By default this parameter is false. Meaning that by default database will be created as non-multitenant database. If you want to create multitenant database, you have to make this parameter true.
Failing to make this parameter true will give you following error if you try to create multitenant database

ORA-65093: multitenant container database not set up properly

2) Change in “Create database” command
So if you are going to create multitenant database using “create database” command, you need to add couple of clause to this command.
Following clause needs to be added

  • ENABLE PLUGGABLE DATABASE
  • SEED

Following steps can be used to create multitenant database

Step 1) Create pfile and spfile

You can use following parameters as generic to create test database. You can alter the memory parameters and include/remove many parameters as required. But these are good enough to create test instance.

cat initdeocdb.ora

db_name='deocdb'
sga_max_size=4G
shared_pool_size=1200M
shared_pool_reserved_size=300M
java_pool_size = 200M
pga_aggregate_target=1G
processes = 300
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/oradata/databases/flashback'
db_recovery_file_dest_size=50G
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDO_T1'
control_files = (/ctl-01/databases/deocdb/control.ctl, /ctl-02/databases/deocdb/control.ctl)
compatible ='12.0.0'
audit_file_dest = /dumps-01/databases/deocdb/adump
core_dump_dest = /dumps-02/databases/deocdb/cdump
diagnostic_dest = /dumps-01
sec_case_sensitive_logon = FALSE
utl_file_dir = /dumps-01/databases/deocdb/output
enable_pluggable_database=true

create spfile from pfile;


***Its important to set java_pool_size in pfile/spfile. If you do not set java_pool_size, we will see following errors in alert log while creating container database

ORA-04031: unable to allocate 4096 bytes of shared memory (“java pool”,”/51835a0f_ReflectionFactoryGet”,”JOXLE^5cb1fb41″,”:SGAClass”)

Step 2) Create database command

I used following create database command to create container database. Highlighted lines are additional clauses for creating container database.


startup nomount;

CREATE DATABASE deocdb
USER SYS IDENTIFIED BY welcome
USER SYSTEM IDENTIFIED BY welcome
LOGFILE GROUP 1 ('/redo-01-a/databases/deocdb/redo-t01-g01-m1.log',
                 '/redo-03-a/databases/deocdb/redo-t01-g01-m2.log') SIZE 100M BLOCKSIZE 512,
        GROUP 2 ('/redo-02-a/databases/deocdb/redo-t01-g02-m1.log',
                 '/redo-04-a/databases/deocdb/redo-t01-g02-m2.log') SIZE 100M BLOCKSIZE 512,
        GROUP 3 ('/redo-01-a/databases/deocdb/redo-t01-g03-m1.log',
                 '/redo-03-a/databases/deocdb/redo-t01-g03-m2.log') SIZE 100M BLOCKSIZE 512,
        GROUP 4 ('/redo-02-a/databases/deocdb/redo-t01-g04-m1.log',
                 '/redo-04-a/databases/deocdb/redo-t01-g04-m2.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/oradata/databases/deocdb/system01.dbf'   
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/oradata/databases/deocdb/sysaux01.dbf'   
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE admin
DATAFILE '/u01/oradata/databases/deocdb/admin-01.dbf' 
    SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED  
DEFAULT TEMPORARY TABLESPACE TEMP 
TEMPFILE '/u01/oradata/databases/deocdb/temp01.dbf' 
    SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undo_t1
DATAFILE '/u01/oradata/databases/deocdb/undo_t1-01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
    FILE_NAME_CONVERT = ('/u01/oradata/databases/deocdb/', 
                         '/u01/oradata/databases/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE users
DATAFILE '/u01/oradata/databases/pdbseed/users-01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 3) run catcdb.sql

DO NOT RUN catlog.sql and catproc.sql
If you are creating container database, you need to run only catcdb.sql.
This script takes care of running all other dependent scripts internally.

If you run catlog.sql and catproc.sql, this script will fail later at many stages with following errors

catcon: ALL catcon-related output will be written to dbmsxdbt_catcon_2527.lst
catcon: See dbmsxdbt*.log files for output generated by scripts
catcon: See dbmsxdbt_*.lst files for spool files, if any
catconInit: database is not open on the default instance
Unexpected error encountered in catconInit; exiting

In case of normal database creation, we usually run catlog.sql and catproc.sql as mentioned in http://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN11082
But that’s for normal database creation and process is same in 12c. But for creating container database, we should be running only catcdb.sql

catcdb.sql will ask for 3 inputs – new sys password, new system password and temp tablespace

SQL> @?/rdbms/admin/catcdb.sql

Session altered.

Enter new password for SYS:
Enter new password for SYSTEM:
Enter temporary tablespace name: TEMP

Once you provide this, it will create root container database with whatever database name you give. Container name for root container DB will be CDB$ROOT.
It will also create seed pluggable container database. Container name will be seed$pdb

Step 4) Run utlrp.sql to compile invalid objects.

There will be few invalid objects in database. You can run utlrp.sql to compile the same

Validation

You can run few commands to validate if you database is container databases and what all containers it has.

We have a new column in v$database called CDB. If your database is container database it will show as YES

SYS.DEOCDB.PRIMARY>select name, cdb from v$database;

NAME    CDB
--------- ---
DEOCDB    YES

1 row selected.

You can also check different containers in your database using following

SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

    CON_ID NAME         OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
   1 CDB$ROOT       READ WRITE        0
   2 PDB$SEED       READ ONLY  1599078400

V$CONTAINER is the new view available in 12c. When this is run from root container, it shows all containers CDB as well as PDB. But if you run same command from any PDB, it will show that container.

SYS.DEOCDB.PRIMARY>alter session set container=PDB$SEED;

Session altered.

SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

    CON_ID NAME         OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
   2 PDB$SEED       READ ONLY  1599078400

Creating Pluggable database

Following command can be used to create pluggable database from seed pluggable database PDB$SEED

SYS.DEOCDB.PRIMARY>create pluggable database deopdb1 admin user deodba identified by welcome FILE_NAME_CONVERT=('/u01/oradata/databases/pdbseed/','/u01/oradata/databases/deopdb1/');

Pluggable database created.

Above command is in its basic form and all parameter/clauses mentioned are mandatory. You need to provide FILE_NAME_CONVERT so that files from seed database will be copied to location you provided for your pluggable database. There are many parameters to this command and you can explore them in documentation link provided in reference.

Initially when you create pluggable database it will be in MOUNTED state. This is because pluggable database use same memory structure and control file as root container database. So memory structure and control file is already allocated.

SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

    CON_ID NAME         OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
   1 CDB$ROOT       READ WRITE        0
   2 PDB$SEED       READ ONLY  1599078400
   3 DEOPDB1        MOUNTED       0

You need to just open the database.
You can open pluggable database from root container when you logged in using following command

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
-------------------------------------
CDB$ROOT

SYS.DEOCDB.PRIMARY>alter pluggable database deopdb1 open;

Pluggable Database opened.

Or you can change container name and simply start

SYS.DEOCDB.PRIMARY>alter session set container=DEOPDB1;

Session altered.

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
-------------------------------------
DEOPDB1

SYS.DEOCDB.PRIMARY>startup
Pluggable Database opened.

You can shutdown pluggable database using similar commands

if you are in root container, you can use following commands

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
-------------------------------------
CDB$ROOT
SYS.DEOCDB.PRIMARY>alter pluggable database deopdb1 close;

Pluggable database altered.

Else you can change container and shutdown the database.

SYS.DEOCDB.PRIMARY>alter session set container=DEOPDB1;

Session altered.

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
--------------------------------------
DEOPDB1
SYS.DEOCDB.PRIMARY>
SYS.DEOCDB.PRIMARY>
SYS.DEOCDB.PRIMARY>shut immediate;
Pluggable Database closed.

Reference

http://docs.oracle.com/database/121/ADMIN/cdb_intro.htm#ADMIN13508
http://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13556
http://docs.oracle.com/database/121/ADMIN/cdb_create.htm#ADMIN13521
https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234

Hope this helps !!

Setting up Replication Slave – MySQL

Introduction:

Replication is one of the basic and most essential feature available in MySQL. Replication allows you to maintain additional copy of database on another server and keep itself updated with latest changes in master databases. Having another copy of data (other than backup) is always a good option. In case of any issues on master server we can always failover to slave and make it a master. This provides high availability. You can have multiple slaves pulling data from a single master, and you can have slaves replicating data off other slaves

There are 2 parts involved in setting up a replication.

Part 1 – Cloning of existing MySQL instance and create a duplicate MySQL environment (similar to master).

This can be done by exporting data using mysqldump and run the sql file created on target database (if data is relatively small) or use “SELECT ... INTO OUTFILE” and “LOAD DATA INFILE”

But relatively easiest way is to setup master slave relationship and set gtid_next variable to the starting gtid of master. This way it will fetch all transactions and make it in sync. Of course you will need GTID to be enabled for this.

Part 2 – Enabling Master – Slave relationship to replicate changes happened on master to slave.

If GTID is enabled, you can configure master – slave using GTID.

If GTID is not enabled, you can use bin_log name and position to configure master – slave.

Setting up replication when GTID is enabled:

So in my current configuration, I have a master where GTID is enabled. After enabling GTID I have loaded the required database “deo”.

I have installed another MySQL setup on another server and enabled GTID.

Parameter Changes:

We need to enable following parameters

Master

server-id=1

We need to assign server ID to master and slave. Remember that server ID should be unique for every MySQL instances in replication.

Also, since GTID is enabled, I am assuming following parameters are setup correctly

gtid_mode=ON
enforce_gtid_consistency=true
log_bin
log_slave_updates=true

Even if GTID is not enabled, you need to setup server_id and log_bin parameters for setting up replication.

Bounce the instance after setting up server_id parameters (and many be GTID parameters if they were not set before).

Slave

server_id=2

Again, I am assuming GTID is enabled on slave mysql instance and above parameters are set correctly.

Bounce the instance after setting up server_id parameters (and many be GTID parameters if they were not set before).

Setting up replication

At this point master looks like following


mysql> show global variables like '%gtid%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-21 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------------------------------------------+

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| deo |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 1 |
| server_id_bits | 32 |
+----------------+-------+

It has one extra database “deo” with few tables in that database.

I will create a replication user on master and grant replication privileges to that user

mysql> CREATE USER 'replicate'@'%' IDENTIFIED BY 'welcome';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show master status\G
*************************** 1. row ***************************
File: bin_log.000002
Position: 6740
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-23
1 row in set (0.00 sec)

At this point my slave looks like following


mysql> show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-----------+

mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 2 |
| server_id_bits | 32 |
+----------------+-------+

mysql> show slave status\G
Empty set (0.00 sec)

At this point we should point the slave to master and start the slave.

Since we are using GTID, we do not have to provide MASTER_LOG_FILE and MASTER_LOG_POS parameters in CHANGE MASTER TO command.

We can simply use MASTER_AUTO_POSITION=1 and MySQL will automatically find the changes it has to replicate.

Pointing slave to master

Use following command to point slave to master

CHANGE MASTER TO MASTER_HOST = 'master_host', MASTER_PORT = master_port, MASTER_USER = 'replicate', MASTER_PASSWORD = 'welcome', MASTER_AUTO_POSITION = 1;
MASTER_HOST is the host name on which master is running
MASTER_PORT is the MySQL port of master
MASTER_USER is the replication user we created in above step. "replicate" in our case
MASTER_PASSWORD is the password we provided for replicate user.
MASTER_AUTO_POSITION = 1. If GTID is enabled, setting this parameter will automatically replicate the changes.

Start Slave

Once we point slave to master, we need to start the slave.

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Once you start the slave you can check the status


mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: advait-1.desktop
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin_log.000003
Read_Master_Log_Pos: 191
Relay_Log_File: advait-2-relay-bin.000003
Relay_Log_Pos: 397
Relay_Master_Log_File: bin_log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 191
Relay_Log_Space: 7243
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 2441a23c-b4ae-11e3-9d0b-80c16e2272cc
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-23
Executed_Gtid_Set: 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-23,
8c593930-b4da-11e3-9e2d-b499ba05473c:1-4
Auto_Position: 1
1 row in set (0.00 sec)

So if you consider RETRIEVED_GTID and EXECUTED_GTID variable, you will see it has pulled up all transactions from master and applied on slave.

Retrieved_Gtid_Set: 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-23
Executed_Gtid_Set: 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-23, 8c593930-b4da-11e3-9e2d-b499ba05473c:1-4

Also, if you check databases on slave, you will see “deo” database is visible and it has all the data same as master.

In EXECUTED_GTID_SET variable, you will see 2 GTIDs separated by comma. This is because if you execute some transactions on slave directly, slave will generate its own GTID, which is 8c593930-b4da-11e3-9e2d-b499ba05473c:1-4 in this case.

In order to prevent any transaction happening on slave, we can make slave read only. This will prevent any user from executing any transactions on slave. But this does allow replicate user to keep replicating transactions happening on master.

Making slave readonly

Add a parameter read_only=ON in /etc/my.cnf on slave side and bounce the slave.


mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| tx_read_only | OFF |
+------------------+-------+

Once slave is readonly, no user can do any transactions on slave, except replicate user and root user.

Hope this helps !!

Enabling GTID in MySQL

What is GTID ?

GTID stands for Global Transaction Identifier. This is a new feature introduced in 5.6 version.

I have given a link in reference section of this article which explains the concept of GTID.

Global transaction identifier is a unique transaction ID assigned to every transaction that happens in MySQL database. This ID has specific importance in maintaining replication slave. Using this ID we can easily track which transactions has been applied to slave and which are yet to be applied. This also prevents applying same transaction again to slave.

Here is how it works. When the master commits a transaction, it generates an identifier consisting of two components:

  • The first component is the server’s server_uuid (server_uuid is a global variable introduced in MySQL 5.6 which holds a UUID, i.e. a 128-bit random number generated so that the probability that two servers in the world have the same server_uuid is so small that we can assume every UUID is unique).
  • The second component is an integer; it is 1 for the first transaction committed on the server, 2 for the second transaction, and so on. Much like an autoincrement column, it is incremented by one for each commit.

The first component ensures that two transactions generated on different servers have different GTIDs. The second component ensures that two transactions generated on the same server have different GTIDs.

In text, we write the GTID as “UUID:N”, e.g., 22096C54-FE03-4B44-95E7-BD3C4400AF21:4711.

The GTID is written as an event to the binary log prior to the transaction

The binary log including the GTID is replicated to the slave. When the slave reads the GTID, it makes sure that the transaction retains the same GTID after it has been committed on the slave. Thus, the slave does not generate a new GTID;

Just to give a brief idea about binary logs – Binary logs are basically logs written for every transactions. They are like the archive logs/online redo logs in Oracle database terminology. I also see GTID is kind of a SCN number in Oracle database terminology.

Steps to enable GTID

You need to set following parameters either in /etc/my.cnf or while starting MySQL instance to enable GTID.

gtid_mode=ON
enforce_gtid_consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) 
log_bin=/usr/local/mysql/log/bin_log 
log_slave_updates=true

If you are starting the instances using above parameters as arguments inline with mysqld_safe binary, you need to replace underscore(_) with hypen(-) in above parameters and include double hypens at the beginning as below

--gtid-mode=ON
--enforce-gtid-consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) 
--log-bin=/usr/local/mysql/log/bin_log 
--log-slave_updates=true

I have updated /etc/my.cnf with above parameters and bounced MySQL instance

I can see GTID is now enabled


mysql> show global variables like '%GTID%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------+

mysql> show variables like '%gtid_next%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| gtid_next | AUTOMATIC |
+---------------+-----------+

mysql> select @@global.gtid_executed;
+------------------------+
| @@global.gtid_executed |
+------------------------+
| |
+------------------------+
1 row in set (0.00 sec)

I will now import a database named “deo” into this MySQL instance.

Exporting deo database from my desktop server MySQL instance:

advait.desktop$ mysqldump --databases deo -u root -p > deo.sql
Enter password:
advait.desktop$

Importing deo database into my new MySQL instance:

/usr/local/mysql/bin/mysql -u root < deo.sql

If I check in my new MySQL instance, I can see deo database


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| deo |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

If I check the GTID value now


mysql> select @@global.gtid_executed;
+-------------------------------------------+
| @@global.gtid_executed |
+-------------------------------------------+
| 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-21 |
+-------------------------------------------+
1 row in set (0.00 sec)

It means that 21 transactions has happened.

References:

GTID concepts – http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html

http://svenmysql.blogspot.in/2012/10/failover-and-flexible-replication.html

Hope this helps !!

Installing MySQL Utilities

MySQL utilities are set of scripts provided for DBAs for general database administration. These are basically command line utilities which can be used to perform various tasks.

Following is the listing of MySQL utility version 1.3 that I installed in my environment

advait.desktop$ ls -lL /usr/local/bin/mysql*
-rwxr-xr-x 1 root root 10546 Mar 26 00:34 /usr/local/bin/mysqlauditadmin
-rwxr-xr-x 1 root root 13443 Mar 26 00:34 /usr/local/bin/mysqlauditgrep
-rwxr-xr-x 1 root root 10142 Mar 26 00:34 /usr/local/bin/mysqldbcompare
-rwxr-xr-x 1 root root 13509 Mar 26 00:34 /usr/local/bin/mysqldbcopy
-rwxr-xr-x 1 root root 14781 Mar 26 00:34 /usr/local/bin/mysqldbexport
-rwxr-xr-x 1 root root 12719 Mar 26 00:34 /usr/local/bin/mysqldbimport
-rwxr-xr-x 1 root root 9551 Mar 26 00:34 /usr/local/bin/mysqldiff
-rwxr-xr-x 1 root root 6339 Mar 26 00:34 /usr/local/bin/mysqldiskusage
-rwxr-xr-x 1 root root 13554 Mar 26 00:34 /usr/local/bin/mysqlfailover
-rwxr-xr-x 1 root root 16487 Mar 26 00:34 /usr/local/bin/mysqlfrm
-rwxr-xr-x 1 root root 5218 Mar 26 00:34 /usr/local/bin/mysqlindexcheck
-rwxr-xr-x 1 root root 4717 Mar 26 00:34 /usr/local/bin/mysqlmetagrep
-rwxr-xr-x 1 root root 5298 Mar 26 00:34 /usr/local/bin/mysqlprocgrep
-rwxr-xr-x 1 root root 6452 Mar 26 00:34 /usr/local/bin/mysqlreplicate
-rwxr-xr-x 1 root root 14169 Mar 26 00:34 /usr/local/bin/mysqlrpladmin
-rwxr-xr-x 1 root root 5612 Mar 26 00:34 /usr/local/bin/mysqlrplcheck
-rwxr-xr-x 1 root root 5796 Mar 26 00:34 /usr/local/bin/mysqlrplshow
-rwxr-xr-x 1 root root 7228 Mar 26 00:34 /usr/local/bin/mysqlserverclone
-rwxr-xr-x 1 root root 4881 Mar 26 00:34 /usr/local/bin/mysqlserverinfo
-rwxr-xr-x 1 root root 6194 Mar 26 00:34 /usr/local/bin/mysqluc
-rwxr-xr-x 1 root root 6667 Mar 26 00:34 /usr/local/bin/mysqluserclone

You can find the complete usage of these scripts in official documentation of mysql utilities. I have provided the link for the same in reference section of this article.

I will cover the installation part of MySQL utilities in this article.

Step 1) Download MySQL utilities from http://dev.mysql.com/downloads/tools/utilities/ location

Select the OS as “Linux – Generic” and download .tar.gz file

Step 2) Extract the file

Copy the file to the server on which you have installed MySQL or on any server where you want to install utilities and extract the tar file.

In my case I am going to install this on my desktop where MySQL is running

So my tar file is residing at /home/advait/mysql/mysql-utilities-1.3.6.tar.gz

I can extract the content in one of the directory under my MySQL installation.

So I extracted above tar file at /usr/local/mysql

cd /usr/local/mysql
tar xvfz /home/advait/mysql/mysql-utilities-1.3.6.tar.gz

Step 3) Build and install utilities

Once you extract you will see mysql-utilities-1.3.6 directory. Inside this directory we set setup.py

Use setup.py to build and install utilities

Build should be done using mysql (non root) user. In my case I am using advait user to start/stop and manage mysql. So I will build using advait user

python setup.py build
advait.desktop$ python setup.py build
running build
running build_py
creating build
creating build/lib
creating build/lib/mysql
copying mysql/__init__.py -> build/lib/mysql
creating build/lib/mysql/utilities
copying mysql/utilities/exception.py -> build/lib/mysql/utilities
copying mysql/utilities/__init__.py -> build/lib/mysql/utilities
creating build/lib/mysql/utilities/command
copying mysql/utilities/command/read_frm.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/rpl_admin.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/serverinfo.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/utilitiesconsole.py -> build/lib/mysql/utilities/command
...
changing mode of build/scripts-2.7/mysqlserverclone from 644 to 755
changing mode of build/scripts-2.7/mysqldbcompare from 644 to 755
changing mode of build/scripts-2.7/mysqlserverinfo from 644 to 755
changing mode of build/scripts-2.7/mysqluserclone from 644 to 755

Install should be done using “root” user since we have installed MySQL using root user. If you use any other user (other than root), that user may not have permissions on python libraries and so install can fail.

advaitd-2.desktop$ python setup.py install
running install
running build
running build_py
running build_scripts
running install_lib
creating /usr/local/lib/python2.7/site-packages/mysql
copying build/lib/mysql/__init__.py -> /usr/local/lib/python2.7/site-packages/mysql
creating /usr/local/lib/python2.7/site-packages/mysql/utilities
copying build/lib/mysql/utilities/exception.py -> /usr/local/lib/python2.7/site-packages/mysql/utilities
creating /usr/local/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/lock.py -> /usr/local/lib/python2.7/site-packages/mysql/utilities/common
...
changing mode of /usr/local/bin/mysqldbexport to 755
changing mode of /usr/local/bin/mysqldbimport to 755
changing mode of /usr/local/bin/mysqlindexcheck to 755
changing mode of /usr/local/bin/mysqlrplcheck to 755
changing mode of /usr/local/bin/mysqlserverinfo to 755
changing mode of /usr/local/bin/mysqlfrm to 755
running install_egg_info

 

Step 4) Testing

Once install is done, you can verify running one of the script if things are working fine.

All MySQL utilities will get installed at /usr/local/bin directory and they should be part of PATH variable. So no need to give complete path.

Example:

advait.desktop$ mysqldiskusage --server=root:<password>@localhost
# Source on localhost: ... connected.
# Database totals:
+---------------------+------------+
| db_name | total |
+---------------------+------------+
| deo | 546,720 |
| mysql | 1,542,216 |
| performance_schema | 396,071 |
+---------------------+------------+
Total database disk usage = 2,485,007 bytes or 2.37 MB
#...done.

You might face following error if python connector module is not installed

advait.desktop$ mysqldiskusage --server=root:<password>@localhost
Traceback (most recent call last):
 File "/usr/local/bin/mysqldiskusage", line 35, in <module>
 from mysql.utilities.common.server import connect_servers
 File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 31, in <module>
 import mysql.connector
ImportError: No module named connector

If you face this issue, you need to install python connector from http://dev.mysql.com/downloads/connector/python/ location

Step 5) Installing python connector

Again download the connector from http://dev.mysql.com/downloads/connector/python/ location

scp the same to the machine on which you were installing MySQL utilities

Extract the .tar.gz file to one of the directories in MySQL installation. In my case I am installing them under utilities directory – /usr/local/mysql/mysql-utilities-1.3.6

Build using non-root user (advait in my case).

python setup.py build

Install using root user

python setup.py install

After this you should be good to use MySQL utilities.

Reference:

MySQL Utilities download – http://dev.mysql.com/downloads/tools/utilities/

Connector download – http://dev.mysql.com/downloads/connector/python/

MySQL Utilities overview – http://dev.mysql.com/doc/mysql-utilities/1.3/en/utils-overview.html

Hope this helps !!

Installing MySQL

I am exploring another database storage solution “MySQL” to understand practical applications of using MySQL in our environment.

This is one of the many articles that I will be writing on MySQL. This article gives you brief steps to installing MySQL on Linux.

Nothing different than what is provided in MySQL documentation except that this is less elaborate with only required steps compared to MySQL documentation.

This is how you can do the installation:-

Step 1) Down the software from http://dev.mysql.com/downloads/

If you are downloading only for checking out usability and features and for personal learning – go for community version. Click on “MySQL Community Server” link.

Select the OS as “Linux – Generic”

Scroll to the bottom and you will see last 2 lines provides the option to download binaries in the form of tar.gz files – “Linux – Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive” andLinux – Generic (glibc 2.5) (x86, 32-bit), Compressed TAR Archive”

Depending on the hardware server you have download either 32 bit or 64 bit version

Step 2) Extract the files on server (login as root) 

Once you download the software (may be on your laptop), SCP the same to the linux server on which you want to install MySQL.

From here on you should be logged in as “root” user on your linux server

Extract the software on linux server at /usr/local location

In my case software was lying on my linux server at /home/advait/mysql location. I extracted using following commands

cd /usr/local 
tar xvfz /home/advait/mysql/mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz

Create a new symlink “mysql” to point to extracted directory (it looks neat without version number and other details).

ln -s mysql-5.6.16-linux-glibc2.5-x86_64 mysql

Step 3) Run following steps to change ownerships

Since you want a superuser to have control of mysql and you dont want to login every time as “root” user on your server, you should be changing the ownership to the user which should be the administrator of mysql.

Usual convention suggests creating “mysql” user on your linux host and giving ownership to “mysql” user.

But on my desktop, I have a user called “advait” and I will be giving ownership to this user.

chown -R advait mysql
cd mysql
chown -R advait .
chgrp -R <grp name> .

Above <grp name> should be the group your user ID belongs to. In my case its again “advait”. To avoid confusion I have used <grp name> instead.

Step 4) Create MySQL database

you should be in /usr/local/mysql directory. Following script will create MySQL database named “mysql” and all required metadata tables inside that.

it will also create a test database for testing purpose.

scripts/mysql_install_db --user=advait

Step 5) Change permissions and create Log directory

you should be in /usr/local/mysql directory

chown -R advait data
chgrp -R <grp name> data
mkdir log
chown -R advait log
chgrp -R <grp name> log

data directory will hold data about tables and log directory will hold log files.

Step 6) Copy config file and modify

Copy my.cnf from /usr/local/mysql to /etc/

you should be in /usr/local/mysql directory

cp my.cnf /etc/my.cnf

modify following parameters in /etc/my.cnf and put them under [mysql.server] heading

[mysql.server]
log_bin = /usr/local/mysql/log
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
socket = /tmp/mysql.sock
user = advait
join_buffer_size = 256M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M

These parameters will be used by mysql instance to during startup.

Step 7) Start MySQL instance

You should start mysql server using your user ID to which you have given the ownership. In my case “advait”.

Login as “advait” and run following commands to start MySQL instance

cd /usr/local/mysql

Use one of the following command to start MySQL instance.

bin/mysqld_safe &
support-files/mysql.server start

You can actually copy support-files/mysql.server in /etc/init.d and make MySQL instance start automatically when server starts

Step 8) Make MySQL instance start automatically with server reboot

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql

Reference:

http://dev.mysql.com/doc/refman/5.0/en/installing.html

Hope this helps !!