Exam Preparation Final: MySQL Cloud Service 2018 [1Z0-320]

In my preview blog posts, I have written about this exam topics and this blog post will cover remaining topics.

Let start with it,

MySQL Security

This MySQL Security section covers general MySQL server inbuild option plus enterprise tools offered by MySQL enterprise edition.

  • Execute MySQL Authorization and Privilege Management

Reference doc: MySQL privilege and access control

  • Manage MySQL Password Policies

Explore for password_validation plugin and Password Expiration policies for MySQL USER.

Leverage MySQL Enterprise Monitor

  • Explain MySQL Enterprise Monitor benefits and features
  • Describe the topology of the MySQL Enterprise Monitor (Service Manager, Agents)
  • Manage MySQL Query Analyzer
  • Manage the customizability of MySQL Enterprise Advisors

Reference Link: MySQL Enterprise Monitor

Leverage MySQL Backup

Logical (mysqldump) Backup import and export from MySQL Workbench.

  • Describe the MySQL Enterprise Backup process
  • Install a MySQL Enterprise Backup
  • Configure MySQL backups
  • Configure MySQL Encryption and Compression

Reference doc: MySQL Enterprise Backup

MySQL Overview of High Availability and Replication

  • Describe MySQL High Availability solutions
  • Explain Replication
  • Set up MySQL Replication using global transaction identifiers (GTID’s) and binlog
  • Set up MySQL Cluster
  • Explain Replication utilities
  • Deploy High Availability and Scalability features

To prepare these topics, learn about MySQL replication in depth including GTID replication knowledge like how to enable GTID, setup replication, and troubleshooting.

GTID Replication BLOG POST

MySQL Cluster is another MySQL High Availability solutions.

Learn about Replication Utilities:

  1. mysqlrpladmin — Administration utility for MySQL replication
  2. mysqlfailover — Automatic replication health monitoring and failover
  3. mysqlreplicate — Set Up and Start Replication Between Two Servers
  4. mysqlrplms — Set Up and Start Replication from a Slave to Multiple Masters
  5. mysqlrplcheck — Check Replication Prerequisites
  6. mysqlrplshow — Show Slaves for Master Server

MySQL Cloud Service

  • Describe Cloud Services options
  • Establish Cloud Service connection via Secure Shell (SSH)
  • Describe security rules within Oracle Compute Cloud Service
  • Configure security rules within Oracle Compute Cloud Service
  • Troubleshoot Cloud Service connections issues
  • Describe alerts and notification options using Cloud Service
  • Configure MySQL Cloud Service Backup

For MySQL Cloud Service, I read cloud documents provided by Oracle and also did practice on Oracle MySQL Could server.

These include MySQL implementation options, security, access from SSH, Backup -Restore on cloud container / local disk. How to patch MySQL server, configuring notification for managing MySQL cloud service.

Reference document:  MySQL Cloud Service

And that’s all !!

Do study everything in detail and practice is must for these topics.

Advertisements

Mysql table locking

Locking is important in many scenarios to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. for example altering table definition online or any kind of table definition changes.locking Mysql provides an option to lock table/s with different types of locks, depends on need.

syntax for lock table:

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

UNLOCK TABLES

Following are the examples for READ and WRITE LOCK:

READ LOCK:

session1> create table t1( c1 int);
Query OK, 0 rows affected (0.06 sec)

session1> insert into test.t1 values(1001);
Query OK, 1 row affected (0.01 sec)

session1> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session1> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
session1> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

Session1 acquired READ lock on table t1 explicitly. After applying READ lock on table users can read the table but not write it.

session2> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session2> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

session3> select * from t1;
+------+
| c1 |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

Multiple sessions can acquire a READ lock on the table at the same time and other sessions can read the table without explicitly acquiring a READ lock.

currently, READ lock s acquired by session1 and session2, both locks need to be unlocked in order to perform a write operation on lock table.

session1> UNLOCK TABLES;
session1> insert into t1 values(1005);

INSERT operation executed from session1  will go in waiting state, since READ lock acquired on table t1 by session2 and not  released yet.

You can see this using:

session3> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| 2 | session1 | localhost | test | Query | 89 | Waiting for table metadata lock | insert into test.t1 values(1005) |
| 3 | session3 | localhost | test | Query | 0 | starting | show processlist |
| 4 | session2 | localhost | test | Sleep | 77 | | NULL |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
3 rows in set (0.00 sec)

After releasing READ lock by session2 insert operation will execute on t1 table.

session2> UNLOCK TABLES;
session1>  select * from t1;
+------+
| c1 |
+------+
| 1001 |
| 1005 |
+------+
2 rows in set (0.00 sec)

NOTE: FLUSH TABLES Different form UNLOCK TABLES:

FLUSH TABLE:  Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement. FLUSH TABLE will not work when table acquires READ LOCK.

UNLOCK TABLES: UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

********************************************************************************************************************************************

WRITE LOCK:

-The session that holds the lock can read and write the table.

session1> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)

session1> insert into test.t1 values(1006);
Query OK, 1 row affected (0.01 sec)

session1> select count(*) from t1;
+------+
| c1 |
+------+
| 1001 |
| 1005 |
| 1006 |
+------+
3 rows in set (0.00 sec)

-Only the session that holds the lock can access the table. No other session can access it until the lock is released.

session2> select count(*) from t1;
and 
session3> insert into test.t1 values(1002);

session1> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| 2 | session1 | localhost | test | Query | 0 | starting | show processlist |
| 3 | session2 | localhost | test | Query | 127 | Waiting for table metadata lock | select count(*) from t1 |
| 4 | session3 | localhost | test | Query | 116 | Waiting for table metadata lock | insert into test.t1 values(1002) |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
3 rows in set (0.00 sec)

Look into performance_schema.metadata_locks table for more status information on table locks. (Thanks for the hint daniel )

– Enable Locking related instruments (if it’s not enabled) :

UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’;

-Lock requests for the table by other sessions block while the WRITE lock is held.

All set  🙂 ……

mysql 5.6 GTID replication errors and fixes

What is GTID? 

4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4

  • This is the server’s 128 bit identification number (SERVER_UUID). It identifies where the transaction was originated. Every server has its own SERVER_UUID.

What problems GTID solves?

  • It is possible to identify a transaction uniquely across the replication servers. Make the automation of failover process much easier. There is no need to do calculations, inspect the binary log and so on. Just MASTER_AUTO_POSITION=1.
  • At application level, it is easier to do WRITE/READ split. After a write on the MASTER, you have a GTID so just check if that GTID has been executed on the SLAVE that you use for reads.
  • Development of new automation tools isn’t a pain now.

How can I implement it?

Three variables are needed in ALL servers of the replication chain

  • gtid_mode: It can be ON or OFF (not 1 or 0). It enables the GTID on the server.
  • log_bin: Enable binary logs. Mandatory to create a replication environment.
  • log-slave-updates: Slave servers must log the changes that come from the master in its own binary log.
  • enforce-gtid-consistency: Statements that can’t be logged in a transactionally safe manner are denied by the server.

ref: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html

Replication errors and fixes:

“‘Got fatal error 1236 from master when reading data from binary log: ”The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.” slave_io thread  stop running.

Resolution: Considering  following  are the master – slave UUID’s

MASTER UUID: 4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4

SLAVE UUID: 5b37def1-6189-11e3-bee0-e89a8f22a444

Steps:

slave>stop slave;

slave> FLUSH TABLES WITH READ LOCK;

slave>show master  status;

‘4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4:1-83345127,5b37def1-6189-11e3-bee0-e89a8f22a444:1-13030:13032-13317:13322-13325:13328-653183:653185-654126:654128-1400817:1400820-3423394:3423401-5779965′

(HERE 83345127  Last GTID executed on master and 5779965 Last slave GTID executed on Master )

slave> reset master;

slave>set global GTID_PURGED=’4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4:1-83345127,5b37def1-6189-11e3-bee0-e89a8f22a444:1-5779965′;

slave>start slave;

slave>unlock  tables;

  slave>show slave status;

NOTE: After this Re-start slave other chain-slaves  if  they stop replicating;


ERROR: ‘Error ”Table … ‘doesn”t exist” on query. Default database: …Query: ”INSERT INTO OR Last_SQL_Error: ….Error ‘Duplicate entry’ SKIP Transaction on slave (slave_sql Thread stop  running) NOTE:

  • SQL_SLAVE_SKIP_COUNTER doesn’t work anymore with GTID.
  • We need to find what transaction is causing the replication to fail.
    • –  From binary log
    • –  From SHOW SLAVE STATUS (retrieved vs executed)

Type of  errors: (check  last sql error in show slave status)

Resolution: Considering  following  are the master – slave UUID’s

MASTER UUID: 4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4

SLAVE UUID: 5b37def1-6189-11e3-bee0-e89a8f22a444

slave>show slave status;

copy  the ‘Executed_Gtid_Set’ value. ‘4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4:1-659731804,5b37def1-6189-11e3-bee0-e89a8f22a444:1-70734947-80436012:80436021-80437839

-Seems that slave (with  uuid 5b37def1-6189-11e3-bee0-e89a8f22a444)  transaction ‘80437840‘ is causing the problem here.

slave> STOP SLAVE;

slave> SET GTID_NEXT=”5b37def1-6189-11e3-bee0-e89a8f22a444:80437840“;  (last_executed_slave_gtid_on_master + 1)

slave> BEGIN; COMMIT;

slave> SET GTID_NEXT=”AUTOMATIC”;

slave> START SLAVE;

slave>  show slave status;

and it’s ALL SET !!!