mysql Oracle(Ingles)
3 pág.

mysql Oracle(Ingles)

Disciplina:<strong>mysql</strong>1 materiais8 seguidores
Pré-visualização3 páginas
MySQL for Oracle
DBAs and Developers

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 2

MySQL for Oracle Dudes

How can you tell an
Oracle DBA

has touched your
 MySQL Installation?

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 3

MySQL for Oracle Dudes

MYSQL_HOME=/home/oracle/products/mysql-version

mysqld_safe \u2013user=oracle &

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 4

MySQL for Oracle Dudes

Outline

\uf0a7 DBA Tips, Tricks, Gotcha's & Tools
\uf0a7 Key Differences for Developers
\uf0a7 Migrating from Oracle to MySQL
\uf0a7 Questions & Answers

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 5

MySQL for Oracle Dudes

My Background

\uf0a7 18 years in Database Technologies (1989)
\uf0a7 10 years Oracle Experience (1996)
\uf0a7 8 years MySQL Experience (1999)
\uf0a7 Active in MySQL, Java, XP User Groups
\uf0a7 Joined MySQL Professional Services (2006)

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 6

MySQL for Oracle Dudes

DBA

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 7

MySQL for Oracle Dudes

\uf0a7 Terminology
\uf0a7 Installation
\uf0a7 Directories
\uf0a7 Log Files
\uf0a7 Processes
\uf0a7 Ports & Sockets

\uf0a7 MySQL Data Dictionary
\uf0a7 Backup
\uf0a7 Tools
\uf0a7 Inherited LAMP Stack

Important DBA Stuff

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 8

MySQL for Oracle Dudes

Terminology

Database (files) --> Database Server Instance

Database Instance (memory) --> Database Server Instance

Schema User --> Database

User --> User

Table Space --> Table Space

 --> Storage Engine

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 9

MySQL for Oracle Dudes

MySQL Installation

\uf0a7 OS packages place files in many areas and varies

- e.g. /usr/lib, /var/lib, /var/log, /etc
\uf0a7 Recommend installing using .tar.gz

- Centrally Manageable e.g. /opt/mysql-version

- Upgradeable

- Multiple Versions possible
\uf0a7 $MYSQL_HOME

Tip

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 10

MySQL for Oracle Dudes

MySQL Configuration

\uf0a7 Multiple MySQL Instances
\uf0a7 my.cnf

- Watch out for /etc/my.cnf, /etc/mysql/my.cnf
- Best Practice $MYSQL_HOME/my.cnf
- --defaults-file=<file>

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

GOTCHA

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 11

MySQL for Oracle Dudes

MySQL Directories

\uf0a7 basedir ($MYSQL_HOME)
- e.g. /opt/mysql-5.1.16-beta-linux-i686-glib23

\uf0a7 datadir (defaults to $MYSQL_HOME/data)
\uf0a7 tmpdir (important as mysql behaves unpredictability if full)
\uf0a7 innodb_[...]_home_dir

- mysql> SHOW GLOBAL VARIABLES LIKE '%dir'

my.cnf options

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 12

MySQL for Oracle Dudes

MySQL Ports & Sockets

\uf0a7 Configured to listen on TCP/IP Port (default 3306)
\uf0a7 Additional Instances

- Different Ports
- Different IP's using default Port

\uf0a7 Local connection uses Socket
- Even specifying Port, local client may use socket

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 13

MySQL for Oracle Dudes

MySQL Log Files

\uf0a7 Error Log
- log-error

\uf0a7 Binary Log
- log-bin (my.cnf or mysqld arg)

\uf0a7 Slow Query Log
- log-slow-queries,slow-query-time,log-queries-not-using-indexes

\uf0a7 General Log
- log

http://dev.mysql.com/doc/refman/5.0/en/log-files.html

my.cnf options
mysqld arg

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 14

MySQL for Oracle Dudes

MySQL Meta Data

\uf0a7 mysql Database
- general_log, slow_log (5.1)

mysql> SET GLOBAL GENERAL_LOG=1;
mysql> ...
mysql> SELECT * FROM mysql.general_log;

http://dev.mysql.com/doc/refman/5.1/en/log-tables.html

New Feature

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 15

MySQL for Oracle Dudes

MySQL Data Dictionary

\uf0a7 INFORMATION_SCHEMA
- TABLES, COLUMNS, VIEWS, USER_PRIVILEGES
- PROCESSLIST (5.1)
- [GLOBAL|SESSION]_[STATUS|VARIABLES] (5.1)

http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

http://www.xcdsql.org/MySQL/information_schema/5.1/MySQL_5_1_INFORMATION_SCHEMA.html

[DBA|USER|ALL]_ tables, V$

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 16

MySQL for Oracle Dudes

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 17

MySQL for Oracle Dudes

MySQL Data Dictionary

\uf0a7 SQL Examples

SELECT TABLE_SCHEMA, SUM((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) AS SIZE_MB
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA ORDER BY SIZE_MB DESC

SELECT ROUTINE_TYPE, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='dbname';

SELECT TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,
ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='dbname';

SELECT CONCAT('DROP TABLE ',table_name,';')
INTO OUTFILE '/sql/drop_tables.sql'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 18

MySQL for Oracle Dudes

MySQL Data Dictionary

\uf0a7 SQL Examples

SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+
SUM(t.index_length))/1024/1024,2),0.00),'Mb') total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-
SUM(t.data_free))/1024/1024,2),0.00),'Mb')
data_used,CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),'Mb') data_free,

 IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-
SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables

FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
WHERE s.schema_name != 'information_schema'
GROUP BY s.schema_name ORDER BY pct_used DESC\G

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 19

MySQL for Oracle Dudes

SHOW Commands
\uf0a7 SHOW TABLES;
\uf0a7 SHOW WARNINGS;
\uf0a7 SHOW STATUS; FLUSH STATUS;
\uf0a7 SHOW VARIABLES;
\uf0a7 SHOW VARIABLES LIKE '%size%';
\uf0a7 SHOW VARIABLES LIKE 'sort_buffer_size';
\uf0a7 SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 20

MySQL for Oracle Dudes

Backup

\uf0a7 Missing Functionality
\uf075 Commercial strength \u2013 unbreakable (Planned 6.0)

\uf0a7 Storage Engine Driven
\uf0a7 Innodb

- Hot Backup
- mysqldump --single-transaction --master-data
- InnoDB Hot Backup

Also PBXT, Falcon

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 21

MySQL for Oracle Dudes

Backup

\uf0a7 MyISAM Only
- Cold Backup via File Copy

- LVM Snapshot
\uf0a7 SE Mixture

- Use Replication Slave

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 22

MySQL for Oracle Dudes

Online Backup/Recovery

\uf0a7 Cross-engine. All major internal engines supported.
\uf0a7 Online, non-blocking for DML. DDL still blocked.
\uf0a7 SQL-command driven. Run from any mysql client.
\uf0a7 Backup to local disk, tape or remote file system.
\uf0a7 Full Server, database, and point-in-time recovery.

Backup ALL\u2026

MySQL 6.0 Demo

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 23

MySQL for Oracle Dudes

Tools

\uf0a7 Missing Functionality
- Enterprise Level Monitoring

\uf0a7 SHOW PROFILE (5.0.38 - Community)
\uf0a7 Microsecond Patch (5.0.33 - Slow query granularity)
\uf0a7 mytop/innotop/ndbtop
\uf0a7 MySQL Toolkit http://sourceforge.net/projects/mysqltoolkit
\uf0a7 phpMyAdmin

Ronald Bradford, MySQL Inc

MySQL Conference & Expo 2007 Page: 24

MySQL for Oracle Dudes

SHOW PROFILE

\uf0a7 95% time in one step
\uf0a7 Reference to Source Code
\uf0a7 \u201cpoor\u201d Status names (internal