RDS Patrick Custom2

From Training Material
Jump to navigation Jump to search


title
MySQL for Amazon RDS Training Course (con't)
author
Patrick Mazzotta


Thursday Follow-Ups⌘

USING (column) clause

  • (column) is a list of column names required for a join
  • must be present in both tables
  • introduced to bring NATURAL joins in line with standards & SQL Server
SELECT * FROM table1 LEFT JOIN table2 USING (id);

Views & Index⌘

Can Views have indexs?

  • nope

Also

  • FROM clause of views cannot use subqueries
  • if you drop or alter a table that's used by a view, no warning is given
    • an error will result when the view is used
    • You can use CHECK TABLE to see if a view has been invalidated by table changes
  • If a user is granted the basic privileges necessary to create a view (the CREATE VIEW and SELECT privileges), that user will be unable to call SHOW CREATE VIEW on that object unless the user is also granted the SHOW VIEW privilege

Stored Procedures⌘

Table alters in stored procedures Below is an example given that cites a case on why alters in stored routines are no longer allowed (they used to be). 

CREATE VIEW v AS SELECT 1;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 5 DO
    SELECT * FROM v;
    SET i = i + 1;
    ALTER VIEW v AS SELECT 2;
  END WHILE;
END;
//
delimiter ;
CALL p();

Because the definition of a view is "locked in" the first time a routine uses a view, p() returns 1 from each iteration of SELECT within the loop.

ALTER PROCEDURE⌘

ALTER PROCEDURE proc_name [characteristic ...] 

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  • we can't change the parameters/body of a stored procedure, but you can update the "characteristics" (i.e. metadata) of the SP

MySQL's philosphy on SP's is that they are not transient. It may seem overly prescriptive, the common sentiment is "why would you need to?"

Stored Procedures on RDS⌘

  • we can't create triggers or stored procedures out of the box in RDS because we won't get super access to the database server
    • if you try, you get something like:
ERROR 1419 (HY000) at line 49: You do not have the SUPER privilege and binary logging is enabled
 (you *might* want to use the less safe log_bin_trust_function_creators variable)

Datatypes Requiring Conversions⌘

SQL Server MySQL
IDENTITY AUTO_INCREMENT
NTEXT, NATIONAL TEXT TEXT CHARACTER SET UTF&
SMALLDATETIME DATETIME
MONEY DECIMAL(19,4)
SMALL MONEY DECIMAL(10,4)
UNIQUEIDENTIFIER BInARY(16)
SYSNAME CHAR(256)

Common Table Expressions⌘

  • no direct/linear equivalent in MySQL
  • derived tables can be self-referencial in a query
  • temporary tables are a reasonable alternative
    • exists for the session, dropped when sesion closes
    • table names can't trudge on other session temp table names
    • requires CREATE TEMPORARY TABLE privilege
CREATE TEMPORARY TABLE db.custrollup
SELECT p.customerNumber, 
      c.customerName, 
      FORMAT(SUM(p.amount),2) total
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY total DESC
LIMIT 10

Common Table Expressions (con't)⌘

Using derived tables: 

SELECT p.customerNumber,
  p.customerName,
  c.amount AS total
FROM payments AS p
  INNER JOIN (SELECT customerNumber, FORMAT SUM(amount),2) AS amount
      FROM db.payments
      GROUP BY customerNumber) AS c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY total DESC
LIMIT 10

IMPORTS - Tips from AWS⌘

  • Create all secondary indexes prior to loading. This is counter-intuitive for those familiar with other databases. Adding or modifying a secondary index causes MySQL to create a new table with the index changes, copy the data from the existing table to the new table, and drop the original table.
  • Load data in PK order. This is particularly helpful for InnoDB tables where load times can be reduced by 75-80% and data file size cut in half.
  • Disable foreign key constraints foreign_key_checks=0 For flat files loaded with LOAD DATA INFILE, this is required in many cases. For any load, disabling FK checks will provide significant performance gains. Just be sure to enable the constraints and verify the data after the load.
  • Load in parallel unless already near a resource limit. Use partitioned tables when appropriate.
  • Use multi-value inserts when loading with SQL to minimize statement execution overhead. When using mysqldump, this is done automatically.
  • Reduce InnoDB log IO innodb_flush_log_at_trx_commit=0
    • Using innodb_flush_log_at_trx_commit=0 causes InnoDB to flush its logs every second instead of at each commit. This provides a significant speed advantage, but can lead to data loss during a crash. Switch it back!

rtree Index⌘

  • rtree index is primarily for indexing multi-dimensional data like points and regions
  • only in MyISAM storage engine

XML Functionality⌘

[https://dev.mysql.com/doc/refman/5.7/en/xml-functions.html] - highlights

  • ExtractValue() extracts a value from an XML string using XPath notation
    • NOTE: only returns CDATA, does not return tags that might be contianed within a matching tag
  • UpdateXML() returns replaced XML fragment
  • watch for XPath injections!
  • No XML data type, and hence no XML columns.
  • No support for the SQL/XML or XQuery standards.
  • Only a subset of XPath is supported.
  • No XQuery Update expressions.
  • No XML indexes.
  • No support for XML Schemas or document validation.
  • No support for namespaces, such as default namespaces or namespace declarations in queries.
  • Cannot extract XML fragments or XML attribute values from a document – can only extract atomic XML * element values (text nodes).
  • No built-in function for XSLT.

CORRECTION!⌘

  • yesterday we talked about LOW_PRIORITY and made some jokes about HIGH_PRIORITY statement.
  • turns out it does exist (though only affects storage engines that use table-level locking)
  • usecase:
    • table tracking financial transactions
    • writes may be HIGH_PRIORITY and reporting/read queries may be LOW_PRIORITY
    • ensure that the recording of transactions always takes precidence (at the engine-level) over reporting processes

Some Read/Write Notes⌘

  • MySQL has some under-the-covers mechanisms for automatic contention management
  • MyISAM supports concurrent inserts (enabled by default)
    • may be affected by enabling binary logging
  • you can explicitly lock InnoDB tables using LOCK TABLES, but don't!
  • can distribute activity using the MERGE storage engine
    • takes a lot more coordination
    • RDS makes this mostly unnecessary
  • avoid external locking

Federation⌘

What it isn't:

  • Notthefederation.png
  • replication
  • a way to give outside people access to your tables (security connection exposure)
  • a way to "lighten the load/demand" on a table

Federation (con't)⌘

What it is:

  • a MySQL storage engine
  • a tunnel to tables on another DB instance
  • a good way to distribute query loads within a VPN/VPC

MERGE INTO⌘

  • there is no direct/linear equivalent of the MERGE statement in MySQL
  • There are optional conditions that can be added to INSERT statement that can be close to the mark, ex:
INSERT INTO db.tablename (id, name)
VALUES (1, 'Greg')
  ON DUPLICATE KEY UPDATE name=VALUES(name);
  • using VALUES() allows you to specifiy the same value as what's used in the INSERT statement without repitition
  • needs UPDATE privileges to handle row updates

What is Replication? ⌘

  • Data from a master server is replicated to one or more slave servers
  • Replication is asynchronous - slaves do not need to be connected permanently to receive updates from the master
  • Updates can occur over long-distance connections (i.e. slow/long polling)
  • You can replicate all databases, selected databases, or even selected tables within a database

Why replication? ⌘

  • Scale-out solutions
    • all writes and updates must take place on the master
    • reads take place on one or more slaves
    • the master is dedicated to updates, slaves are dedicated for reads
  • Data security
    • to run "backup" services on the slave without corrupting the corresponding master data
    • switch to slave if the master is unavailable or corrupted
  • Analytics, Data Warehouses
    • live data on the master
    • analysis of the information takes place on the slave
  • Long-distance data distribution and security
    • a branch office would like to work with a copy of your main data
    • slave can be used without requiring permanent access to the master

Synchronous vs. Asynchronous ⌘

  • MySQL support for one-way, asynchronous replication (master -> slave(s))
    • Master-slave.png
  • The synchronous replication is implemented by MySQL NDB Cluster (master <-> master)
    • Multi-master.png

Types of Replication

  • Statement Based Replication (SBR)
    • replicates entire SQL statements
  • Row Based Replication (RBR)
    • replicates only the changed rows
  • Mixed Based Replication (MBR)
    • the default mode within MySQL 5.1.12 and later

How Replication Works (part 1)⌘

  • Replication uses binary logging mechanism
  • The master writes updates and changes as “events” to the binary log
  • Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database
  • The master is “dumb” in this scenario (knows nothing about slaves)
  • Each slave will receive a copy of the entire contents of the binary log

How Replication Works (part 2)⌘

  • It is the responsibility of the slave to decide which statements in the binary log should be executed
  • You cannot configure the master to log only certain events
  • If you do not specify otherwise, all events in the master binary log are executed on the slave
  • If required, you can configure the slave to process only events that apply to particular databases or tables.
  • Each slave keeps a record of the binary log file and position within the log file that it has read and processed from the master (relay)
  • This means that multiple slaves can be connected to the master and executing different parts of the same binary log

How Replication Works (part 3)⌘

  • Because the slaves control this process, individual slaves can be connected and disconnected from the server without affecting the master's operation
  • Slaves can be disconnected, reconnect and then “catch up” by continuing from the recorded position.
  • The master and each slave must have a unique ID (server-id)
  • The slave must know the master host name, log file name and position within that file
  • CHANGE MASTER TO statement sets 
    • the address of the master
    • binary log file name
    • position in the binary log
  • CHANGE MASTER TO statement stores the data in the master.info file

How to Set Up Replication (from new database) ⌘

  1. Create a separate user that will be used by your slaves to Authenticate with the master to read the binary log for replication (optional)
  2. Configure the master to support the binary log and configure a unique ID (required)
  3. Configure a unique ID for each slave (required)
  4. Configure the slave with the master settings, such as the host name, login credentials and binary log name and positions
  5. Start replication

1. Creating a User for Replication ⌘

  • Each slave must connect to the master using a standard MySQL user name and password
  • Any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege
  • You do not need to create a specific user for replication. you should be aware that the user name and password will be stored in plain text within the master.info file
 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

2. Setting Master Configuration⌘

  • You must enable binary logging on the master
  • Each server within a replication group must be configured with a unique server-id value
  • The server ID is used to identify individual servers within the group, and must be between 1 and 231
  • Edit my.cnf or my.ini file
  • Add the lines below
[mysqld]
log-bin=mysql-bin
server-id=1

3. Setting Slave Configuration⌘

  • You must set the unique server ID
[mysqld]
server-id=2
  • If you do not specify a server-id value, it defaults to 0.
  • If you omit server-id (or set it explicitly to 0), a master refuses connections from all slaves, and a slave refuses to connect to a master.
  • Omitting server-id is good only for backup with a binary log.
  • You do NOT have to enable binary logging on the slave for replication to be enabled

4a. Obtaining Master Information⌘

  • You must determine the master's current point within the master binary log
  • Execute FLUSH TABLES WITH READ LOCK; on the master server and leave the session open
  • Use the SHOW MASTER STATUS on the master server to get
    • Binary Log Position
    • Binary Log Filename
  • You now have the information you need to enable the slave to start reading from the binary log in the correct place to start replication.

4b. Configure slave with master settings⌘

  • On the master, release the read lock:
    • UNLOCK TABLES;
  • Start up the MySQL slave
  • Execute the CHANGE MASTER TO
CHANGE MASTER TO
   MASTER_HOST='localhost',
   MASTER_USER='rep',
   MASTER_PASSWORD='asdf',
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=106;

5. Start Replication⌘

  • START SLAVE starts both of the slave threads.
    • The I/O thread reads queries from the master server and stores them in the relay log.
    • The SQL thread reads the relay log and executes the queries
  • START SLAVE requires the SUPER privilege
  • Slave threads can start and then later stop
    • for example they do not manage to connect to the master or read its binary logs
    • START SLAVE does not warn you about this
    • You must check the slave's error log for error messages generated by the slave threads
    • SHOW SLAVE STATUS shows whether replication is working

Patrick's Bad Art⌘

(

DML/DDL/DCL⌘

  • Data Manipulation Language
    • most of the DML driven by SQL standards
    • some flavour differences, but the menu is mostly the same
  • Data Definition Language
    • as we saw yesterday, some minor changees in the syntax order
    • core concepts remain the same
    • truncate remains one of the bigger differences
MySQL has a TRUNCATE TABLE statement, but it doesn't always follow the standard.Note that in some cases, MySQL's truncate command is really the equivalent of an unrestricted DELETE command (i.e.: potentially slow and trigger-invoking). Its behaviour depends on whichstorage engine the table is managed by.When using InnoDB (transaction safe) tables, TRUNCATE TABLE is allowed in a transactioninvolving other operations, however TRUNCATE TABLE implies an immediate COMMIT operation.
  • Data Control Language
    • GRANT, REVOKE, etc. function as expected

Tools⌘

Retrieved from ‘https://training-course-material.com/index.php?title=RDS_Patrick_Custom2&oldid=23978’