MySQL für Oracle DBA's
of 28
-
Upload
oli-sennhauser -
Category
Documents
-
view
230 -
download
0
Transcript of MySQL für Oracle DBA's
-
7/25/2019 MySQL fr Oracle DBA's
1/28
-
7/25/2019 MySQL fr Oracle DBA's
2/28
www.fromdual.com
2 / 28
%er $romDual Gm%,
Suor#
remo#e0DBA
Sc*ulu"
Bera#u"
-
7/25/2019 MySQL fr Oracle DBA's
3/28
www.fromdual.com
/ 28
"*al#
MySQL fr Oracle DBA's
Gesc*!c*#e Bra"c*es u"d $or3s "s#alla#!o" 4ur!ff auf MySQL 5ser u"d Sc*ema S#orae 6"!"es ""oDB S#orae 6"!"e
7erforma"ce u"!" Mo"!#or!" Lo!" ,oc*9erf%ar3e!# MySQL !" der +loud
-
7/25/2019 MySQL fr Oracle DBA's
4/28
-
7/25/2019 MySQL fr Oracle DBA's
5/28
www.fromdual.com
/ 28
Ho s#e*# MySQL *eu#eI
*##?//d%0e"!"es.com/e"/ra"3!"
-
7/25/2019 MySQL fr Oracle DBA's
6/28
www.fromdual.com
& / 28
Bra"c*es u"d $or3s
MySQL !s# Oe" Source JG7LK d. *. Cede/r darf sel%er Bra"c*es u"d $or3s
Her se#
-
7/25/2019 MySQL fr Oracle DBA's
7/28
www.fromdual.com
/ 28
MySQL Su%s3r!#!o"e"
MySQL +ommu"!#y 6d!#!o" J+6K Oe" Source MySQL Ser9er *!lf d!r sel%er
MySQL S#a"dard 6d!#!o" JS6K Oe" Source oder +ommerc!al MySQL Ser9er MySQL Suor#
MySQL 6"#err!se 6d!#!o" J66K S#a"dard 6d!#!o" JS6K 6"#err!se ools JMo"!#or) Bac3u) Aud!#) ...K
MySQL +ommer!cal O6M) S=) =AR JL!
-
7/25/2019 MySQL fr Oracle DBA's
8/28
www.fromdual.com
8 / 28
MySQL 7la##form
6Po#!sc*eN 7la##forme" f*re"wa*rsc*e!"l!c* e*er
-
7/25/2019 MySQL fr Oracle DBA's
9/28
www.fromdual.com
; / 28
"s#alla#!o"
H!c*#!er Gru"dsa#< %e! MySQL? ESS Harum e!"fac* mac*e") we"" es auc* 3oml!
Oracle? O5 JMySQL
-
7/25/2019 MySQL fr Oracle DBA's
10/28
www.fromdual.com
1( / 28
6rs#elle" der Da#e"%a"3
D!s#r!%u#!o" le# e!"e Da#e"%a"3 a"? /var/lib/mysql
=o" ,a"d?mysql_install_db --user=mysql --datadir=...
@eu !" .?mysqld --initialize --user=mysql --datadir=...
Mul#!0"s#a"
-
7/25/2019 MySQL fr Oracle DBA's
11/28
www.fromdual.com
11 / 28
Eo"f!ura#!o"
MySQL Eo"f!ura#!o"sda#e!?my.cnfodermy.iniJH!"K
L!e# u"#er? /etc/oder /etc/mysql
MySQL? 3e!" SPFILE "deru" !"my.cnf Res#ar# oder
SET GLOBAL variable = wert;
9o" ,a"d !"my.cnfsc*re!%e" Gu#es emla#e?
*##?//fromdual.com/mysl0co"f!ura#!o"0f!le0samle
-
7/25/2019 MySQL fr Oracle DBA's
12/28
www.fromdual.com
12 / 28
S#ar#e"/s#oe" 9o" MySQL
Au#oma#!sc*? !" Boo#07ro
-
7/25/2019 MySQL fr Oracle DBA's
13/28
www.fromdual.com
1 / 28
7ro
-
7/25/2019 MySQL fr Oracle DBA's
14/28
www.fromdual.com
1: / 28
4ur!ff auf MySQL
+L 9s. G5 +L?mysqlJOracle? SQLT7lusK
@!c*# so mFc*#! w!e SQLT7lus
shell> mysql --user=... --password=...--host=... --port=... database
mysql> help
G5? MySQL 6"#err!se Hor3%e"c* JHBK Adm!"!s#ra#!o" JBac3u) Mo"!#or!") ...K 6"#w!c3lu" JSQL) M!ra#!o"K Des!"er J6R0D!arammeK
-
7/25/2019 MySQL fr Oracle DBA's
15/28
www.fromdual.com
1 / 28
+o""ec#!o"s / +o""ec#ors
=er%!"du" " MySQL %!ll!? of# E6@ +o""ec#!o"07ool!" 1 =er%!"du" U 1 *read 1 Query 1 +ore *read 7ool J1(((e 9o" =er%!"du"e"K
+o""ec#ors? DB+/ODB+
7,7) 7erl) 7y#*o") Ru%y) .@6
-
7/25/2019 MySQL fr Oracle DBA's
16/28
www.fromdual.com
1& / 28
5ser u"d Sc*ema
5ser 'oli'@'localhost' 5"!P Soc3e#
'oli'@'127.0.0.1' +7 9o" 7
'oli'@'%' +7 9o" %erall *er
MySQL 7or#? (&
7r!9!le!e" Glo%al? *.*) ro Sc*ema ) ro a%elle) ro Sal#e
Sc*ema JU Da#a%aseK O%Ce3#e u"a%*F"! 9om 5ser J e*Vr# Sys#emK
-
7/25/2019 MySQL fr Oracle DBA's
17/28
www.fromdual.com
1 / 28
H!c*#!s#e Befe*le
SHOW DATABASES;
use mysql;
SHOW TABLES;
SHOW [FULL] PROCESSLIST; SELECT user, host FROM mysql.user;
SHOW GRANTS FOR user@host;
SHOW GLOBAL VARIABLES; SHOW GLOBAL STATUS;
SHOW ENGINES;
-
7/25/2019 MySQL fr Oracle DBA's
18/28
www.fromdual.com
18 / 28
S#orae 6"!"es
MySQL Arc*!#e3#ur !s# se
=ersc*!ede"e S#orae 6"!"es JDB 6"!"eK?""oDB) MySAM) M6MOR>
SELECT * FROM information_schema.tables;
$r*er MySAM JWU .1K *eu#e? ""oDB JXU .K
SQL Layer
,a"dler "#erface
""oDB MySAM Memory
rad!#!o"alJRKDBMS
U Mo"ol!#*
#$%W C&'()' )(*L' +es+,C&'()' )(*L' .+es+. / '2I2'3I5D*
-
7/25/2019 MySQL fr Oracle DBA's
19/28
www.fromdual.com
1; / 28
""oDB S#orae 6"!"e
ra"sa3#!o"e" JA+DK sola#!o" Le9el Jdefaul# reea#a%le0readK
""oDB Buffer 7ool Da#a%ase Buffer +ac*e innodb_buffer_pool_size
1&3 7aes
""oDB Lo $!le Jib_logfile?K Redo Los
innodb_log_file_size
""oDB Sys#em a%lesace Jibdata1K
""oDB S Eo"
-
7/25/2019 MySQL fr Oracle DBA's
20/28
www.fromdual.com
2( / 28
7erforma"ce u"!"
mysql> SHOW GLOBAL STATUS; PERFORMANCE_SCHEMA
Slow Query Lo
slow_query_log = 1 long_query_time = 0.5
shell> mysqldumpslow -s t slow.log > profile
Query 6Pecu#!o" 7la"?
mysql> EXPLAIN SELECT * FROM test;
-
7/25/2019 MySQL fr Oracle DBA's
21/28
www.fromdual.com
21 / 28
H!c*#!s#e u"!"07arame#er
innodb_buffer_pool_sizeJdefaul#
-
7/25/2019 MySQL fr Oracle DBA's
22/28
www.fromdual.com
22 / 28
Mo"!#or!" 9o" MySQL
MySQL 6"#err!se Mo"!#or JM6MK Oracle +loud +o"#ol JO6M/OMSK m!# MySQL
7lu0!"
rd7ar#y ools
-
7/25/2019 MySQL fr Oracle DBA's
23/28
www.fromdual.com
2 / 28
Lo!"
6rror Lo $e*ler u"d Har"u"e" Jlog_warnings = 2K
Slow Query Lo la"sameN A%frae" sl5_q8ery_l5g 3 %2
l5g_q8ery_+9me 3 1:"
Ge"eral Query Lo alleN A%frae"
general_query_log = ON
-
7/25/2019 MySQL fr Oracle DBA's
24/28
www.fromdual.com
2: / 28
,oc*9erf%ar3e!#
Mas#er/Sla9e Rel!3a#!o" Da#a0Guard
a3#!9/ass!9 $a!lo9er0+lus#er d!#o
Galera +lus#er fr MySQL Oracle RA+
-
7/25/2019 MySQL fr Oracle DBA's
25/28
www.fromdual.com
2 / 28
MySQL Rel!3a#!o"
MySQL Mas#er/Sla9e Rel!3a#!o" 6"#sr!c*# Oracle Da#a Guard
6!"sa#
-
7/25/2019 MySQL fr Oracle DBA's
26/28
www.fromdual.com
2& / 28
RA+? Galera +lus#er
A A A
Load %ala"c!" JLBK
@ode 2 @ode @ode 1
wsre
Galera rel!ca#!o"wsre wsre
rwrw
Oracle Real Al!ca#!o" +lus#er JRA+K MySQL? Galera +lus#er
S*ared0@o#*!" Arc*!#e3#ur
-
7/25/2019 MySQL fr Oracle DBA's
27/28
www.fromdual.com
2 / 28
MySQL !" der +loud
a e*# Sowo*l !"?
7r!9a#e +loud
7u%l!c +loud Rec*e"
-
7/25/2019 MySQL fr Oracle DBA's
28/28
