MySQL for Beginners - Overview of the world's most popular open source database

MySQL is the world's most popular open source database. It powers the most-trafficked websites in the world such as Facebook, Twitter and YouTube. It's also one of the easiest databases to use and it's perfect for beginners.

In this session, you'll learn about:
· Which tools are essential and where to download them.
· Basic steps to install and configure MySQL Server.
· How to load a sample database and execute basic queries.
· How to avoid common pitfalls.

In addition, we'll briefly discuss the next steps for building and connecting applications to your database. Finally, we’ll demonstrate how you can easily launch MySQL in the cloud to speed up the entire getting-started process.

Transcript

1. MySQL for Beginners Overview of the world's most popular open source database Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz
2. 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
3. • 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
4. 5 The world’s most popular open source database Copyright © 2022, Oracle and/or its affiliates
5. 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
6. • 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
7. Copyright © 2022, Oracle and/or its affiliates 10 Decisions – MySQL, Fork, Community, Enterprise
8. 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.
9. 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
10. 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/
11. 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/
12. 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
13. Copyright © 2022, Oracle and/or its affiliates 16 Downloading
14. 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
15. 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
16. Microsoft Windows - Downloads Copyright © 2022, Oracle and/or its affiliates 19
17. 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
18. https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html Microsoft Windows – pre-install Copyright © 2022, Oracle and/or its affiliates 21
19. 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
20. Microsoft Windows – post installation continued Copyright © 2022, Oracle and/or its affiliates 23
21. Microsoft Windows – post installation continued Copyright © 2022, Oracle and/or its affiliates 24
22. 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
23. 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
24. 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
25. 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/
26. 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
27. 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
28. 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 <enter password from mysqld.log> 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
29. 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
30. 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
31. Localhost Success Copyright © 2022, Oracle and/or its affiliates 36
32. Can connect from localhost (127.0.0.1) but not from another machine $ mysqlsh root@192.10.10.10 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
33. 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
34. Connection –Remote Connection Copyright © 2022, Oracle and/or its affiliates 39
35. Copyright © 2022, Oracle and/or its affiliates 40 GUI: MySQL Workbench
36. Load Sample Data Copyright © 2022, Oracle and/or its affiliates 41 https://dev.mysql.com/doc/index-other.html
37. Load Sample Data – MySQL Shell Copyright © 2022, Oracle and/or its affiliates 42
38. Load Sample Data – MySQL Shell Copyright © 2022, Oracle and/or its affiliates 43
39. Connect and Load Data with MySQL Workbench Copyright © 2022, Oracle and/or its affiliates 44
40. Execute Queries From MySQL Workbench Copyright © 2022, Oracle and/or its affiliates 45
41. Manager Users Copyright © 2022, Oracle and/or its affiliates 46
42. Manage Tables/Indexes Copyright © 2022, Oracle and/or its affiliates 47
43. Manage Tables/Indexes Copyright © 2022, Oracle and/or its affiliates 48
44. Query Tuning Copyright © 2022, Oracle and/or its affiliates 49
45. Moving Data with Workbench (Note: Use MySQL Shell for Large Datasets) Copyright © 2022, Oracle and/or its affiliates 50
46. • 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!
47. Copyright © 2022, Oracle and/or its affiliates 52 Key Tasks – Back up your data, Upgrade your database
48. 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
49. • 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/
50. 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
51. 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
52. 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
53. Oracle Cloud Infrastructure (OCI) Copyright © 2022, Oracle and/or its affiliates 58
54. Copyright © 2022, Oracle and/or its affiliates 59 MySQL Database Service / MySQL HeatWave creation wizard
55. 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
56. 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
57. 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
58. 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/
59. 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
60. 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
61. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 67 Follow us on Social Media
62. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 68 Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz