Hi Everyone
Here is some type of cheatsheet for someone with Oracle Background to catch up quickly on MySQL.
Latest version MySQL at the time of this article is 8, pretty interesting it has plenty of features in community edition Free of Cost on Licensing. Paid version is also available from Oracle, see MySQL List Price PDF.
| Terminology | MySQL | Oracle |
| Connection admin | mysql -h localhost -u root mysql -h localhost -u root -p (for system with password set ) | sqlplus sys/password@hostname as sysdba sqlplus '/as sysdba' |
| Connectivity Command line | mysql mysqlsh MySQL Shell is a superset of the functionality of the mysql client. It has several features that the old client doesn't have, for example: You can write statements in Python or Javascript in addition to SQL statements, so you can write scripts that do any kind of loop or conditional code you can imagine in Python or Javascript. You can connect to multiple MySQL sessions concurrently, for example to connect to multiple servers. You can output query results in different formats, including JSON. You can use the "X Protocol" which allows you to query MySQL like a NoSQL server instead of an SQL server. MySQL Shell is sort of being presented as a successor to the traditional mysql client tool. But it's a relatively new tool and probably has some undiscovered bugs (any new software does | SQL*PLUS "sqlplus" |
| dev site | dev.mysql.com | otn.oracle.com |
| Community discussions | forums.mysql.com | community.oracle.com |
| Support | www.mysql.com/support | www.oracle.com/support |
| training/learning | www.mysql.com/training | education.oracle.com |
| Documentation | dev.mysql.com/doc/ | docs.oracle.com/en |
| Connectivity Tool | MySQL Workbench | SQL Developer |
| Cluster | InnoDB Cluster | Oracle RAC |
| Backup | MySQL Enterprise Backup (loosely traslated) mysqlbackup Percona Xtrabackup | RMAN |
| Replication Technology | MySQL Replication | Dataguard |
| Security | MySQL Enterprise TDE/Auth | Advanced Security (ASO) |
| Comparable technical terms | none | Controlfile |
| Comparable technical terms | none | Listener |
| Comparable technical terms | Database Instance | Database |
| Comparable technical terms | Database | Schema/PDB |
| Comparable technical terms | Tablespace | Tablespace |
| parameter file | my.cnf | init.ora |
| Dynamic parameter file | Any GLOBAL DYNAMIC configuration variable can be modified with the SET PERSIST statement. Under the hood the settings will be persisted to a file named mysqld-auto.cnf which will be created in the data directory. This file will be read during server startup just like any other configuration file, and all variables present in this file will be applied as the highest priority. That means the file mysqld-auto.cnf will be the last file to be applied on server startup (even after command-line options) and takes precedence if a specific setting has been specified in more than one location.(MySQL 8.0) | spfile.ora (under $ORACLE_HOME/dbs) |
| Find parameter values | Show global variables like %max_connections% select @@global.max_connections; | show parameters process |
| Comparable technical terms | Storage Engine (MyIASM, InnoDB) | No Concept of Storage engine |
| Memory Caches | InnoDB Data Cache Innodb Log Cache MyIASM Key Cache Dictionary Cache Query Cache User Cache | Data Cache (Variants) Log Buffer Shared Pool Large Pool PGA Query Cache(11g) |
| Redo/Undo Logs | InnoDB undo space InnoDB Logs Binary Log | Undo Tablespace (9i+) Redo Logs Archive Logs |
| Data Storage | Tablespaces Table/Index files Format Files | Tablespaces Datafiles |
| Optimizer | Cost Based | Cost Based |
| Open Source | Yes | No |
| PLSQL Capability | No | Yes |
| Built In Accounts | root | sys, system |
| Object privileges | Grant, Revoke, host consideration | Grant, Revoke |
| Audit | Audit subsystem | Audit API, MySQL Plugin |
| Version | mysql> select version() -> ; +--------------------+ | version() | +--------------------+ | 5.6.20-68.0-56-log | +--------------------+ 1 row in set (0.54 sec) mysql> or use mysqladmin version | select * from v$version; |
| date/time | mysql> select current_date; +--------------+ | current_date | +--------------+ | 2022-10-08 | +--------------+ 1 row in set (0.02 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2022-10-08 22:14:22 | +---------------------+ 1 row in set (0.00 sec) mysql> | SQL> select sysdate from dual; SYSDATE --------- 08-OCT-22 SQL> select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'D ------------------ 08-oct-22 15:15:36 SQL> |
| To get the list of databases | show databases | SELECT username FROM all_users ORDER BY username; (pre container era) SELECT NAME FROM V$CONTAINERS |
| To get the where I have connceted to | select DATABASE(); | SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual; (pre container era) show con_name |
| Show what user is session connected as | mysql> select user(); +---------------+ | user() | +---------------+ | ggs@localhost | +---------------+ 1 row in set (0.00 sec) mysql> | SQL> show user USER is "SYS" SQL> |
| To get the list of tables within the current database | use database_name; show tables; | select * from user_tables; assuming you have dba privs SELECT table_name FROM dba_tables WHERE owner = <<name of schema>> |
| To get the connected connection info | show processlist SELECT * FROM performance_schema.threads | SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER'; |
| To limit the selection | select * from user limit 10; select username from user limit 10, 15; | select * from table_name where ROWNUM <= 10; select element_name from (select element_name, ROWNUM as row_number from table_name) as t1 where t1.row_number > 10 and t1.row_number <= 15; |
| Describe table | desc table_name; | desc table_name; |
| To view errors/warnings | show warings / show errors | select * from user_errors;/ show errors |
| Port | Default port 3306 No separate Listener | Default port 1521 |
| To get the table DDL | help show show create table table_name; --similarly SHOW CREATE FUNCTION func_name SHOW CREATE PROCEDURE proc_name | set pagesize 999 set long 9000 select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', 'database_name') from dual; |
| To get the session variables | show variables; --or show variables like 'inno%'; to get all possible configuration options mysqld --verbose --help | SELECT name, value FROM gv$parameter; -- or SELECT sys_context('USERENV', ) FROM dual; |
| Explain the execution plan of a sql statement | explain select * from table_name; | explain plan select * from table_name; --then query plan table select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id; |
| Stats gather | Analyze stores key distribution stats of a table optimizer use these stats to decide better query execution | Analyze, DBMS_STATS ANALYZE table scott compute statistics; ANALYZE table scott estimate statistics sample 25 percent; ANALYZE table scott estimate statistics sample 1000 rows; analyze index sc_idx compute statistics; analyze index sc_idx validate structure; ANALYZE can be used to collect the statistics like CHAIN_CNT, AVG_SPACE, and EMPTY_BLOCKS. DBMS_STATS will not collect these statistics. We might need to use ANALYZE in case if we want to see chained rows, average space and empty blocks. The difference is you use dbms_stats to gather statistics (as per the documentation) - the optimizer is developed expecting the statistics dbms_stats collects. And you use analyze to list chained rows, to validate the structure. The use of analyze to gather statistics is a deprecated feature. |
| Optimize reorganizes physical storage of a table data and associated indexes, reduces storage spaces, improves IO efficiency | Rebuild/Reorg table | |
| Default transaction mode | Autocommit On; | Autocommit Off; |
| Batch SQL execution | mysql -h host -u user -p < batch_file.sql > mysql.out use --force if you want to connect even if some error occurs during execution | sqlplus apps/password @batch_file.sql |
| Spool equivalent | tee data.txt; //SQL sentences... notee; | spool data.txt spool off |
| Auto Increase | Using AUTO_INCREMENT | Sequence.nextval |
| Install | yum -y install @mysql (module will install the most recent version of MySQL with all dependencies.) | # Interactive mode. ./runInstaller # Silent mode. ./runInstaller -ignorePrereq -waitforcompletion -silent \ blah blah |
| Enable Autorestart | # systemctl start mysqld # systemctl enable --now mysqld # systemctl status mysqld | |
| Secure after install of mysql | # mysql_secure_installation | not exactly equivalent - Oracle Database Security Assessment Tool (DBSAT) |
| Roles | CURRENT_ROLE() (no roles pre mysql 8) | DBA_ROLES |
| Update config variables | To persist a global system variable to the mysqld-auto.cnf option file in the data directory, precede the variable name by the PERSIST keyword or the @@PERSIST. qualifier: SET PERSIST max_connections = 1000; SET @@PERSIST.max_connections = 1000; SET variable = expr [, variable = expr] ... variable: { user_var_name | param_name | local_var_name | {GLOBAL | @@GLOBAL.} system_var_name | {PERSIST | @@PERSIST.} system_var_name | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name | [SESSION | @@SESSION. | @@] system_var_name } | alter system processes=1500 scope=both sid='*'; scope options memory, spfile |
| Start config file | my.cnf | pfile or spfile |
| Transaction logs | Binary Logs(Statements that change data (also used for replication) log_bin = 1 max_binlog_size= sync_binlog=1 (innodb) binlog_format=STATEMENT (logical replication) binlog_format=ROW (Physical Replication) binlog_format=MIXED expire_logs_days | online redo logs; archive redo logs ARCHIVELOG DELETION POLICY ... delete nopromt archivelog until time 'sysdate-7'; |
| Standby log | relay logsRelay Logs(Data changes received from a replication source server) | standby redo logs |
| error logs General Log | error log (diag messages, startups, shutdowns,abnormal conditions) variable: log_error log-error = mysqld.log variable: general_log = 1 general_log_file=PATH (dynamic parameter) | alert_sid.log |
| slow queries | slow_query_log = true slow_query_log_file = /var/log/mysqlslow.logslow long_query_time = 5 (in seconds) slow query logs that are taking more than 5 secs to execute mysqldumpslow to analyse | SELECT * FROM (SELECT sql_fulltext, sql_id, child_number, disk_reads, executions, first_load_time, last_load_time FROM v$sql ORDER BY elapsed_time DESC) WHERE ROWNUM < 10 / |
| Audit | audit log files (part of enterprise edition) - standard policy bsaed logging | sys.aud$, *.aud files under audit_file_dest log location unified auditing |
| Queries history | general query log (all queries the server receives from client) To disable or enable the general query log or change the log file name at runtime, use the global general_log and general_log_file system variables. Set general_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set general_log_file to specify the name of the log file. | v$sql, awr history queries |
| Listener Log | Not applicable as no listener | tns listener.log |
| memory settings | innodb_buffer_pool_size innodb_buffer_pool_instances online sizing in 5.7 key_buffer (MyIASM) | buffer_cache |
| Storage Engine to store table | CREATE TABLE innodb_table (id int, value int) ENGINE=INNODB; CREATE TABLE myisam_table (id int, value int) ENGINE=MYISAM; CREATE TABLE default_table (id int, value int); To find table is using InnoDB or MYISAM SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='your_table_name' AND TABLE_SCHEMA='your_database_name'; -- or use TABLE_SCHEMA=DATABASE() if you have a default one. | No Concept of Storage engine/plugin |
| Comment | # comment goes here -- comment goes here (after -- there must be space) /* comment goes here */ | Comment can be either of below -- comment goes here (after -- there must be space) /* comment goes here */ |
| DB status | mysql> show status mysql> show engine innodb status \G; | like utlbstat utlestat |
| Load data | mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; or use utility mysqlimport To restrict use --local-infile=0 variable | SQL*Loader |
| Monitoring | Performance_Schema Show Session Status Show Global Status Like Variable | Statspack, AWR Sys v$performance Views |
| SET PASSWORD = PASSWORD('new_pass'); SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass'); SET PASSWORD = OLD_PASSWORD('new_pass'); | Alter user <username> identified by <pass>; | |
| Kill user session | Kill id | alter system kill session 'sid,serial#,@inst_id'; |
| Like in where clause | it is case insensitive by default searching metadata LIKE 't%' returns both strings starts with T and t. use binary to make it sensitive like binary 't%'; | case sensitive searching MetaData to achieve insensitivity use LIKE upper(string%) ='STRING%'; |
| Logical Data Migration | 1. mysqldump mysqldump [options] --result-file=dump.sql mysql newdbname < dump.sql 2. mysqlpump (parallelism, create index after inserts done for innoDB, compressed output etc features) SELECT * INTO OUTFILE 'file_name' FROM tbl_name. | exp, imp(old way) expdp, impdb (new way) |
| show | mysqlshow to view the database, table, column and index information etc | no equivalence |
| Stress test | mysqlslap | Consolidated Database Replay of Real Application Testing (RAT) |
| Reading Transaction logs | mysqlbinlog The server's binary log consists of files containing “events” that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. | LogMiner is what you can use for the purpose |
| DDl log | ddl_log.log | ENABLE_DDL_LOGGING=true enabling parameter will create ddl log |
| Switch Logfile | FLUSH BINARY LOGS; | alter system switch logfile; |
| Purge Binary logs | PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'; mysql> SHOW BINARY LOGS; mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY); | rman> delete archivelog all completed before 'sysdate-5'; |
| mysqld debug | mysqladmin debug mysqladmin -i10 processlist status | connect / as sysdba oradebug setmypid oradebug unlimit oradebug dump systemstate 266 -g; oradebug dump systemstate 266 -g; oradebug dump systemstate 266 -g; |
| Debug client | mysql --debug=d:t:O,/tmp/client.trace mysql> SET SESSION debug = 'debug_options'; | SQLPLUS debug mode Insert below entries in sqlnet.ora on client machine. DIAG_ADR_ENABLED = OFF TRACE_LEVEL_CLIENT = SUPPORT TRACE_DIRECTORY_CLIENT = /var/tmp/sqlplus TRACE_TIMESTAMP_CLIENT = ON |
| Privilege Metadata | SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE table_schema = 'sample' AND table_name = 'employee'; INFORMATION_SCHEMA.COLUMN_PRIVILEGES INFORMATION_SCHEMA.SCHEMA_PRIVILEGES INFORMATION_SCHEMA.TABLE_PRIVILEGES INFORMATION_SCHEMA.USER_PRIVILEGES | DBA_TAB_PRIVS DBA_ROLE_PRIVS Role_ROLE_PRIVS |
| Strong Password validation | install validate_password component | Use Password Verify Function case sensitive sec_case_sensitive_logon=true |
| Resource Limits | CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank' WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2; | Resource Manager https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-resources-with-oracle-database-resource-manager.html#GUID-040A4CC4-B8B3-40F8-9984-FA79D46521BB |
| Export/Import Table content as delimited file | mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 -- run from mysql commands for import data: mysql> USE db1; mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1; with options: mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt | set colsep ' ' set echo off set feedback off set heading off set linesize 32767 set pagesize 0 set termout off set trimspool on set verify off spool &SPOOL_FILE <optional: a select statement to provide column headings> <your select statement here> spool off SQ* Loader to Import |
| Clone DB | Making a Copy of a Database $> mysqldump db1 > dump.sql $> mysqladmin create db2 $> mysql db2 < dump.sql Do not use --databases on the mysqldump command line because that causes USE db1 to be included in the dump file, which overrides the effect of naming db2 on the mysql command line. | RMAN duplicate Database |
| Export metadata alone Export Data alone NO ddls | $> mysqldump --no-data test > dump-defs.sql $> mysqldump --no-create-info test > dump-data.sql | expdp username/password DIRECTORY=directory_object_name DUMPFILE=dumpfile_name TABLES=table_names|TABLESPACES=tablespace_names|FULL=y CONTENT=metadata_only CONTENT= DATA_ONLY |
| Query External DBs | The FEDERATED Storage Engine SELECT <<columns>> FROM <<database>>.<<table>> (colocated on same mysql engine) | Oracle DB Links |
| Corruption | MyIASM: CHECK TABLE and REPAIR TABLE commands | Corruption prevention: Oracle Data Guard: Protects enterprise databases in the event of disaster or corruption. Data Recovery Advisor: Diagnoses data loss or corruption and performs repairs. Oracle Flashback: Restores database tables or rows to an earlier state. Oracle Recovery Manager: Backs up and recovers Oracle databases. Oracle Secure Backup: Backs up file system data and Oracle Database files to tape. Corruption Detection and repair: Oracle Recovery Manager (RMAN) DBVerify ANALYZE command DB_BLOCK_CHECKING parameter Block media recovery (BMR) DBMS_REPAIR |
| SELECT @@optimizer_switch\G show variables like '%optimizer%' | OPTIMIZER_FEATURES_ENABLE | |
| Character Set | [mysqld] character-set-server=latin1 collation-server=latin1_swedish_ci CREATE DATABASE mydb CHARACTER SET latin1 COLLATE latin1_swedish_ci; SHOW VARIABLES LIKE 'character_set_system'; SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name'; USE db_name; SELECT @@character_set_database, @@collation_database; SHOW CHARACTER SET; | select * from NLS_DATABASE_PARAMETERS |
| Download | Community Release: https://dev.mysql.com/downloads/ Enterprise Release: edelivery.oracle.com | edelivery.oracle.com |
| INFORMATION_SCHEMA.INNODB_TABLESPACES | DBA_TABLESPACES | |
| INFORMATION_SCHEMA.INNODB_TABLES | DBA_TABLES | |
| Replication | mysql> START SLAVE IO_THREAD; mysql> START SLAVE SQL_THREAD; | edit database set state='transport-off' edit database set state='apply-off' |
| Replication/Update Commit to Disk Log | innodb_flush_log_at_trx_commit=1 sync_binlog=1 (Critical for durability of data) | alter database force logging; (every transaction is logged, guratees replica will be in sync, statement level nologging has no effect) |
| Unique Identifier in replication user defined (or) Server UUID (auto generated) | [mysqld] server-id=21 | db_unique_name=finprod |
| Master Slave setup - Binary log position based replication Protocol | mysql> CHANGE MASTER TO -> MASTER_HOST='source_host_name', -> MASTER_USER='replication_user_name', -> MASTER_PASSWORD='replication_password', -> MASTER_LOG_FILE='recorded_log_file_name', -> MASTER_LOG_POS=recorded_log_position; | dgmgrl > create configuration finprod_repl as primary database is 'mastername' connect identifier is master_tns; dgmgrl > Add database 'slavename' as connect identifier is slave_tns maintained as physical; |
| The replica stores information about the source you have configured in its connection metadata repository. The connection metadata repository can be in the form of files or a table, as determined by the value set for the master_info_repository system variable. When a replica runs with master_info_repository=FILE, two files are stored in the data directory, named master.info and relay-log.info. If master_info_repository=TABLE instead, this information is saved in the table in the database. | dgmgrl> show configuration dgmgrl> show database verbose mastername dgmgrl> show database verbose slavename | |
| show master status show slave status | dgmgrl > show database 'mastername'; dgmgrl > show database 'slavename'; | |
| GTID based replication | gtid_mode=ON enforce-gtid-consistency=ON mysql> CHANGE MASTER TO > MASTER_HOST = host, > MASTER_PORT = port, > MASTER_USER = user, > MASTER_PASSWORD = password, > MASTER_AUTO_POSITION = 1; SET GTID_NEXT=automatic; | dgmgrl > create configuration finprod_repl as primary database is 'mastername' connect identifier is master_tns; dgmgrl > Add database 'slavename' as connect identifier is slave_tns maintained as physical; |
| ReadOnly DB | mysql> FLUSH TABLES WITH READ LOCK; mysql> SET GLOBAL read_only = ON; mysql> SET GLOBAL read_only = OFF; mysql> UNLOCK TABLES; | SQL>alter database open read only; (can be done only during startup) |
No comments:
Post a Comment