Tweet
Tweet

Slide 1

Slide 1 text

MySQL for Beginners Overview of the world's most popular open source database Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz

Slide 2

Slide 2 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 2 Me, Myself & I  MySQL Geek  Addicted to MySQL for 15+ years  Playing with databases for 20+ years  MySQL Writer, Blogger and Speaker  Also: DBA, Consultant, Architect, Trainer, ...  MySQL Cloud Principal Solutions Architect EMEA at Oracle  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Twitter: @freshdaz Olivier DASINI

Slide 3

Slide 3 text

• Choose MySQL or Fork, Community or Enterprise • Download Software • Install: Windows and Linux (Overview) • Connect to MySQL • Upload Data and Execute Queries • Back up your Database • Upgrade your Database • MySQL in the Cloud • MySQL Enterprise Edition Agenda 4 Copyright © 2022, Oracle and/or its affiliates

Slide 4

Slide 4 text

5 The world’s most popular open source database Copyright © 2022, Oracle and/or its affiliates

Slide 5

Slide 5 text

6 MySQL powers the most demanding • MySQL powers the most demanding Web, Social, E-commerce, SaaS, Finance, FinTech applications • It is a fully integrated transaction-safe, ACID compliant database with full commit, rollback, crash recovery and row level locking capabilities • MySQL delivers the ease of use, reliability, scalability, and performance to power: Copyright © 2022, Oracle and/or its affiliates

Slide 6

Slide 6 text

• Newer MySQL Releases = Faster at Scale • More Secure • More Reliable MySQL Lifecycle & Support Copyright © 2022, Oracle and/or its affiliates 9 https://www.mysql.com/why-mysql/benchmarks/mysql/ Release GA Date Premier Support End Extended Support End Sustaining Support End MySQL 5.0 Oct 2005 Dec 2011 N/A Indefinite MySQL 5.1 Dec 2008 Dec 2013 N/A Indefinite MySQL 5.5 Dec 2010 Dec 2015 Dec 2018 Indefinite MySQL 5.6 Feb 2013 Feb 2018 Feb 2021 Indefinite MySQL 5.7 Oct 2015 Oct 2020 Oct 2023 Indefinite MySQL 8.0 Apr 2018 Apr 2023 Feb 2026 Indefinite

Slide 7

Slide 7 text

Copyright © 2022, Oracle and/or its affiliates 10 Decisions – MySQL, Fork, Community, Enterprise

Slide 8

Slide 8 text

Choose: MySQL or MySQL Fork Copyright © 2022, Oracle and/or its affiliates 11 FORK - When developers take a copy of source code from one software package and start independent development on it, creating a distinct and separate piece of software.

Slide 9

Slide 9 text

Open Source Models Copyright © 2022, Oracle and/or its affiliates 12 • Engineered by many Developed at Oracle Source of all forks •Clear Roadmap •Direct Access To Support •Support Access To Engineers •Fixes and Updates in SOURCE

Slide 10

Slide 10 text

MySQL 8.0.x: thank you for the contributions Copyright © 2022, Oracle and/or its affiliates 13 https://lefred.be/?s=thank+you+for+the+contributions&ct_post_type=post%3Apage https://lefred.be/content/mysql-8-0-29-thank-you-for-the-contributions/

Slide 11

Slide 11 text

Choose: MySQL or MySQL Fork Copyright © 2022, Oracle and/or its affiliates 14 MySQL 5.0 • MySQL AB MySQL 5.1 • Sun Microsystems MySQL 5.5 • Improved Windows OS • Performance Schema • Semi-sync repl MySQL 5.6 • Robust replication • Stricter SQL • Stronger security MySQL 5.7 • Native JSON • Cost-based optimizer • Group Replication MySQL 8.0 • Document Store • Data dictionary • OLAP Percona 5.7.. MariaDB. .. 2010 5.5, 10.0, 10.1, 10.2, 10.3 Percona 8.0 MySQL – https://www.mysql.com https://dasini.net/blog/2017/07/19/mysql-first-public-releases/

Slide 12

Slide 12 text

Choose: Community or Enterprise Copyright © 2022, Oracle and/or its affiliates 15 Security Support Hot Backup Monitor & Query Analyzer  Transparent Data Encryption  Audit  MySQL Enterprise Firewall  Authentication  Data Masking Support for Community & Enterprise

Slide 13

Slide 13 text

Copyright © 2022, Oracle and/or its affiliates 16 Downloading

Slide 14

Slide 14 text

Enterprise – Trial http://edelivery.oracle.com • 30 day trial license • Latest version only Enterprise – Existing Customer https://support.oracle.com Community https://dev.mysql.com Where to Download Copyright © 2022, Oracle and/or its affiliates 17

Slide 15

Slide 15 text

1. MySQL Server 2. MySQL shell 3. MySQL Workbench 4. Sample tables What to Download Copyright © 2022, Oracle and/or its affiliates 18 https://www.mysql.com/support/supportedplatforms/database.html Partial Listing

Slide 16

Slide 16 text

Microsoft Windows - Downloads Copyright © 2022, Oracle and/or its affiliates 19

Slide 17

Slide 17 text

1. Select mysql-installer-web-community if you have internet connection 2. 32-bit installer will install 64 bit MySQL 3. All tools you need are included • MySQL Shell • Workbench • Connectors Microsoft Windows – Downloads continued Copyright © 2022, Oracle and/or its affiliates 20

Slide 18

Slide 18 text

https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html Microsoft Windows – pre-install Copyright © 2022, Oracle and/or its affiliates 21

Slide 19

Slide 19 text

1. MySQL runs as a service 2. Service reads my.ini • Equivalent to my.cnf for linux • Key settings for MySQL • InnoDB Buffer Pool • Log locations • Restart Service after changes Microsoft Windows – post installation Copyright © 2022, Oracle and/or its affiliates 22

Slide 20

Slide 20 text

Microsoft Windows – post installation continued Copyright © 2022, Oracle and/or its affiliates 23

Slide 21

Slide 21 text

Microsoft Windows – post installation continued Copyright © 2022, Oracle and/or its affiliates 24

Slide 22

Slide 22 text

Microsoft Windows – post installation continued Copyright © 2022, Oracle and/or its affiliates 25 https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html

Slide 23

Slide 23 text

1. For beginners/advanced – use YUM or APT 2. Follow documentation steps carefully What could go wrong? #1. Read your error message when you try to start the service #2. Look in mysql log file /var/log/mysqld.log or /var/log/mysql/mysqld.log Linux Installation Copyright © 2022, Oracle and/or its affiliates 26 https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/linux-installation.html

Slide 24

Slide 24 text

3. MySQL or a MySQL Fork is Already Running • Linux Distribution may have installed MySQL or MySQL Fork ps aux | grep mysqld yum --disablerepo=\* provides mysql\* Linux Installation – what could go wrong? Copyright © 2022, Oracle and/or its affiliates 27 https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/starting-server-troubleshooting.html https://lefred.be/content/using-mysql-community-repository-with-ol-8-rhel-8-centos-8/ https://dev.mysql.com/doc/refman/5.7/en/replace-third-party-yum.html

Slide 25

Slide 25 text

MySQL Shell ● DevOps Tool – Designed for DevOps operations ● Scripting for Javascript, Python, and SQL mode – Rapid prototyping capabilities ● InnoDB Cluster Support – Setup your HA solution within minutes ● Supports MySQL Standard and X Protocols ● Document and Relational Models – CRUD Document and Relational APIs via scripting ● Traditional Table, JSON, Tab Separated output results formats ● Both Interactive and Batch operations Interface for Development and Administration of MySQL Copyright © 2022, Oracle and/or its affiliates 30 https://dev.mysql.com/doc/mysql-shell/8.0/en/

Slide 26

Slide 26 text

MySQL Server 5.7 MySQL 8.0 Upgrade Checker Prompt Themes Auto Completion & Command History MySQL Server 8.0 Document Store X Dev API SQL CLI Output Formats (Table, JSON, Tabbed) Batch Execution JavaScript Python SQL importJSON Dump Utilities InnoDB Cluster / ReplicaSet / ClusterSet etc... MySQL Shell Overview Interface for Development and Administration of MySQL Copyright © 2022, Oracle and/or its affiliates 31

Slide 27

Slide 27 text

1. $ sudo yum install mysql-shell 2. $ mysqlsh MySQL Shell Installation Copyright © 2022, Oracle and/or its affiliates 32 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux-quick.html

Slide 28

Slide 28 text

1. Find your root password • Debian – specified in install • YUM or RPM – first-time password is in error log $ sudo grep 'temporary password' /var/log/mysqld.log $ mysqlsh root@localhost --sql SQL> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!’; Linux – Connecting the First Time Copyright © 2022, Oracle and/or its affiliates 33 Or with mysql client: $> mysql -u root –p

Slide 29

Slide 29 text

1. Try to connect from host machine with mysql command-line utility $ mysql -uroot –p $ mysql -uroot –p –protocol=TCP (eliminates possible permission issues with socket file) The error (2002) Can't connect to ... normally means that there is no MySQL server running on the system or that you are using an incorrect Unix socket file name or TCP/IP port number when trying to connect to the server. You should also check that the TCP/IP port you are using has not been blocked by a firewall or port blocking service. The error (2003) Can't connect to MySQL server on 'server' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server. The error 1045 (Access denied) – good news! Your connecting, just a MySQL access control issue https://dev.mysql.com/doc/refman/8.0/en/can-not-connect-to-server.html Connection Errors Copyright © 2022, Oracle and/or its affiliates 34

Slide 30

Slide 30 text

1. Connect from Host 2. Make sure MySQL is running: ps aux | grep mysql[d] 3. Use Netstat (windows or linux) to verify mysql is listening on port: sudo netstat -tlpn | grep mysql https://dev.mysql.com/doc/refman/8.0/en/can-not-connect-to-server.html Connection Errors – 2002/2003 errors Copyright © 2022, Oracle and/or its affiliates 35

Slide 31

Slide 31 text

Localhost Success Copyright © 2022, Oracle and/or its affiliates 36

Slide 32

Slide 32 text

Can connect from localhost (127.0.0.1) but not from another machine $ mysqlsh [email protected] 1. Check bind address in my.cnf file (usually only Debian/Ubuntu) • bind-address = 127.0.0.1 • Comment out (# bind-address = 127.0.0.1 ) and restart server 2. Check Firewalls a. https://linuxize.com/post/mysql-remote-access/ Iptables, UFW (Ubuntu), FirewallD (CentOS, RedHat, Oracle Linux) b. Windows Defender Firewall Control Panel | Security | Firewall | Advanced Settings | Inbound Rules TCP for port 3306 Connection Errors -- remote server – 2002/2003 Copyright © 2022, Oracle and/or its affiliates 37

Slide 33

Slide 33 text

1. Your identity is based on a. Your user name: ethan, théo, root … b. The client host from which you connect Authentication Error-- remote server -- 1045 Copyright © 2022, Oracle and/or its affiliates 38 https://dev.mysql.com/doc/refman/8.0/en/connection-access.html https://dev.mysql.com/doc/refman/8.0/en/create-user.html

Slide 34

Slide 34 text

Connection –Remote Connection Copyright © 2022, Oracle and/or its affiliates 39

Slide 35

Slide 35 text

Copyright © 2022, Oracle and/or its affiliates 40 GUI: MySQL Workbench

Slide 36

Slide 36 text

Load Sample Data Copyright © 2022, Oracle and/or its affiliates 41 https://dev.mysql.com/doc/index-other.html

Slide 37

Slide 37 text

Load Sample Data – MySQL Shell Copyright © 2022, Oracle and/or its affiliates 42

Slide 38

Slide 38 text

Load Sample Data – MySQL Shell Copyright © 2022, Oracle and/or its affiliates 43

Slide 39

Slide 39 text

Connect and Load Data with MySQL Workbench Copyright © 2022, Oracle and/or its affiliates 44

Slide 40

Slide 40 text

Execute Queries From MySQL Workbench Copyright © 2022, Oracle and/or its affiliates 45

Slide 41

Slide 41 text

Manager Users Copyright © 2022, Oracle and/or its affiliates 46

Slide 42

Slide 42 text

Manage Tables/Indexes Copyright © 2022, Oracle and/or its affiliates 47

Slide 43

Slide 43 text

Manage Tables/Indexes Copyright © 2022, Oracle and/or its affiliates 48

Slide 44

Slide 44 text

Query Tuning Copyright © 2022, Oracle and/or its affiliates 49

Slide 45

Slide 45 text

Moving Data with Workbench (Note: Use MySQL Shell for Large Datasets) Copyright © 2022, Oracle and/or its affiliates 50

Slide 46

Slide 46 text

● MySQL Shell for VS Code is a brand new extension for MS Visual Studio Code ● We have taken the full power of MySQL Shell - our advanced MySQL Client for Developers and DBAs - and made it available directly inside Visual Studio Code ● Together with support for the MySQL Database Service on Oracle Cloud, our new extension offers tons of new features for MySQL developers – Full OCI MDS Integration – Notebook Interface – MySQL Shell GUI Console – Tight Workflow Integration ● https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code MySQL Shell for VS Code Copyright © 2022, Oracle and/or its affiliates 51 https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code NEW!

Slide 47

Slide 47 text

Copyright © 2022, Oracle and/or its affiliates 52 Key Tasks – Back up your data, Upgrade your database

Slide 48

Slide 48 text

1. MySQL Shell – Instance, Schema and Table Dump Utility 2. MySQL Enterprise Backup 3. mysqldump and mysqlpump (legacy tools) Back up your data Copyright © 2022, Oracle and/or its affiliates 53

Slide 49

Slide 49 text

• Multi-threaded dump, splits large tables into chunks • Loads chunks in parallel • Load while Dump still in progress • Abort and resume loading data • Built-in Compression • Option to send to Oracle cloud object storage MySQL Shell – Instance, Schema and Table Dump Utility Copyright © 2022, Oracle and/or its affiliates 54 https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-1-demo/

Slide 50

Slide 50 text

MySQL Enterprise Backup Copyright © 2022, Oracle and/or its affiliates 55 • Online, Hot Backup and Recovery – Complete MySQL instance backup (data and config) • Full, Incremental, Partial: Backups & Restores – Compatible with Transparent Data Encryption – Compressed & Encrypted Backups

Slide 51

Slide 51 text

Produce a set of SQL statements to re-produce schema objects, tables and data Mysqlpump released in 5.7 and provides parallelization mysqldump -uroot -p --routines --events --single-transaction --databases world > dump.sql mysqldump/ mysqlpump Copyright © 2022, Oracle and/or its affiliates 56 Caution – slow restore times for large tables/databases and under-resourced hardware

Slide 52

Slide 52 text

1. Subscribe to Oracle security alerts • https://www.oracle.com/security-alerts/ • Quarterly updates provided for MySQL Server 2. Download new version of MySQL Shell • JS> util.checkForServerUpgrade() 3. Check documentation • https://dev.mysql.com/doc/refman/8.0/en/upgrading.html • Back up data 4. Replace the old binaries with the new ones: • Linux: ex. $ sudo yum update mysql-server • Windows: Start MySQL Installer, select Catalog, dashboard will show if upgrade is available Plan To Upgrade at Least Quarterly Copyright © 2022, Oracle and/or its affiliates 57

Slide 53

Slide 53 text

Oracle Cloud Infrastructure (OCI) Copyright © 2022, Oracle and/or its affiliates 58

Slide 54

Slide 54 text

Copyright © 2022, Oracle and/or its affiliates 59 MySQL Database Service / MySQL HeatWave creation wizard

Slide 55

Slide 55 text

100% developed, managed, and supported by Oracle MySQL HeatWave on Oracle Cloud Infrastructure Automation MySQL On-Premises MySQL Database Service Database Scaling Backup Security Patch & Upgrade Provision & Configure OS OS Security Patch & Upgrade OS Installation Server Hardware Purchase & Maintenance Storage Storage Purchase & Maintenance Data Center Rack & Space Power, HVAC, Networking

Slide 56

Slide 56 text

Get Started with a Step-by-Step Lab Copyright © 2022, Oracle and/or its affiliates 61 https://oracle.github.io/learning-library/data-management-library/mysql/heatwave-intro/workshops/freetier/?lab=introduction&nav=open

Slide 57

Slide 57 text

Discovering MySQL Database Service ● Discovering MySQL Database Service – Episode 1 – Introduction – http://dasini.net/blog/2021/08/03/discovering-mysql-database-service-episode-1-introduction/ ● Discovering MySQL Database Service – Episode 2 – Create a compartment – http://dasini.net/blog/2021/08/10/discovering-mysql-database-service-episode-2-create-a-compartment/ ● Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network – http://dasini.net/blog/2021/08/17/discovering-mysql-database-service-episode-3-create-a-virtual-cloud-network/ ● Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket – http://dasini.net/blog/2021/08/24/discovering-mysql-database-service-episode-4-dump-your-mysql-data-into-an-object-storage-bucket/ ● Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump – http://dasini.net/blog/2021/08/31/discovering-mysql-database-service-episode-5-create-a-mysql-db-system-from-a-mysql-shell-dump/ ● Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List – http://dasini.net/blog/2021/09/07/discovering-mysql-database-service-episode-6-update-the-private-subnet-security-list/ ● Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session – http://dasini.net/blog/2021/09/14/discovering-mysql-database-service-episode-7-use-a-bastion-ssh-port-forwarding-session/ ● Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell – http://dasini.net/blog/2021/09/21/discovering-mysql-database-service-episode-8-connect-to-mysql-database-service-using-mysql-shell/ ● Discovering MySQL Database Service – Episode 9 – Connect to MySQL Database Service Using MySQL Workbench – http://dasini.net/blog/2021/09/28/discovering-mysql-database-service-episode-9-connect-to-mysql-database-service-using-mysql-workbench/ ● Discovering MySQL Database Service – Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell – http://dasini.net/blog/2021/10/05/discovering-mysql-database-service-episode-10-connect-to-mysql-database-service-using-oci-cloud-shell/ Tutorials where I show you, step by step, how to use MDS and some other OCI services http://dasini.net/blog/en Copyright © 2022, Oracle and/or its affiliates 62

Slide 58

Slide 58 text

Choose: Community or Enterprise Copyright © 2022, Oracle and/or its affiliates 63 Security Support Hot Backup Monitor & Query Analyzer  Transparent Data Encryption  Audit  MySQL Enterprise Firewall  Authentication  Data Masking Support for Community & Enterprise https://www.mysql.com/products/enterprise/

Slide 59

Slide 59 text

1. High Availability, Performance Tuning, Disaster Recovery, Migrations • https://go.oracle.com/MySQLFrenchWebinars 2. MySQL Training and Certification • https://education.oracle.com/database/mysql/pfamily_406 3. Forums • https://forums.mysql.com/ 4. Contact Us • https://www.mysql.com/about/contact/ What’s Next? Copyright © 2022, Oracle and/or its affiliates 64

Slide 60

Slide 60 text

Links to Download MySQL http://edelivery.oracle.com https://support.oracle.com https://dev.mysql.com Supported platform list https://www.mysql.com/support/supportedplatforms/database.html Windows Install https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html Linux Install https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/linux-instal lation.html Remove forks https://dev.mysql.com/doc/refman/5.7/en/replace-third-party-yum.html Install Troubleshooting https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/starting-se rver-troubleshooting.html Install layouts https://dev.mysql.com/doc/refman/8.0/en/installation-layouts.html Connection errors: https://dev.mysql.com/doc/refman/8.0/en/can-not-connect-to-server.ht ml Linux firewalls https://linuxize.com/post/mysql-remote-access/ Users and access https://dev.mysql.com/doc/refman/8.0/en/connection-access.html https://dev.mysql.com/doc/refman/8.0/en/create-user.html MySQL Shell https://dev.mysql.com/doc/mysql-shell/8.0/en/ https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux- quick.html Sample data https://dev.mysql.com/doc/index-other.html MySQL Shell dump and load https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-1-demo / Security/upgrade https://www.oracle.com/security-alerts/ https://dev.mysql.com/doc/refman/8.0/en/upgrading.html https://oracle.github.io/learning-library/data-management-library/mysql /heatwave-intro/workshops/freetier/?lab=introduction&nav=open MySQL Enterprise Edition https://www.mysql.com/products/enterprise/ Additional resources https://www.mysql.com/news-and-events/web-seminars/ https://education.oracle.com/database/mysql/pfamily_406 https://forums.mysql.com/ https://www.mysql.com/about/contact/ Thank you! Copyright © 2022, Oracle and/or its affiliates 65

Slide 61

Slide 61 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 67 Follow us on Social Media

Slide 62

Slide 62 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 68 Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz

Slide 63

Slide 63 text

No content