Operational DBA in a Nutshell

1 .Operational DBA in a nutshell Tom De Cooman and Dimitri Vanoverbeke SDM Team Manager Solution engineer 

2 .Welcome! • Welcome! • Who are we? – Fans of funny gifs... • What will we talk about? • When are the breaks? 

3 .Basic housekeeping • Full Day Tutorial • Hands On! Laptop Required! • Breaks • Wireless SSID/PWD = Percona 2019 / PL19live • How to ask for help • Using the documentation • Text in red you can execute on the commandline or MySQL prompt – # for commandline – > for mysql 

4 .Beware! This tutorial is not for you if … … • You know what replicate_same_server_id is! • You know all GRANTs by heart! • pt-query-digest is your favourite tool! • You’ve been dealing with replication inconsistencies for a while already! • Your backup environment is the most awesome there is! 

5 .Beware! … Part 2 • This tutorial may go very fast at some times ... • There is a lot of material to cover • Don’t worry! • The main idea is to introduce you to approaches, concepts, tech ... • Don’t hesitate to get our help! 

6 .Operational DBA In A Nutshell • Setting up today’s environment • MySQL Installation • Logging In • MySQL Privileges • Diagnostics • Troubleshooting • Backups • Replication • Schema Changes • Configuration Optimization 

7 .Operational DBA In A Nutshell • Setting Up Today’s • Replication Environment • Schema Changes • MySQL Installation • Configuration Optimization • Logging In • MySQL Privileges • Diagnostics • Troubleshooting • Backups 

8 .Intermezzo: What versions of MySQL do you run? • Do you know? • MySQL 5.7/8 • Percona Server 5.7/8? • MariaDB 10.2/10.3/10.4? • Another blend? 

9 .What is MySQL Community edition? • Standard Oracle Community Version (previously MySQL AB, Sun) • Contains a standard MySQL version with InnoDB and MyISAM as a standard • The original source • Packages for a large array of operating systems including Windows and MacOS • Open Source GPL v2 

10 .What is Percona Server? • Enhanced version of MySQL Community edition • Added plugins for – Authentication (PAM) – Auditing – Diagnostics – Improved and Extra storage engines • Improved Performance (Patches) • Improved backup and management capabilities • Hotbackup using Percona XtraBackup • Monitoring using PMM or other tools • Packages for Linux distributions 

11 .What is MariaDB server • Fork of MySQL community Edition – New features of MySQL not included • Improved Functionalities – Connect storage engine – Additional storage engines – Additional plugins • Packages for Linux and Windows 

12 .Other existing or older options • WebScaleSQL – database developed by large web organisations based on MySQL community 5.6 – No ready available packages – No longer maintained • Drizzle • MySQL Cluster – NDB storage engine (specific use case) • MySQL 5.7 Enterprise edition – MySQL community edition with additional plugins •Authentication •Firewall •MySQL enterprise backup and monitor 

13 .Hold my beer: Percona Toolkit • Commandline Tools • System tasks • Dataset management and tooling • Percona Server for MySQL, MySQL, MariaDB, Percona Server for MongoDB and MongoDB • Open Source • Packages for RHEL and Debian based systems • https://www.percona.com/software/database-tools/percona-to olkit 

14 .Setting up Today’s environment • Copy Files From USB Stick (if you have not downloaded them yet) • Install VirtualBox, open (doubleclick) the vbox files, start all VMs • Test Connectivity – ssh -p 2221 root@localhost (password: vagrant) – ssh -p 2222 root@localhost (password: vagrant) – http://localhost:8080/ (you should see an apache test page) 

15 .Setting up Today’s environment (Amazon) • Amazon based infrastructure (IAAS) – Automation using Hashicorp’s Terraform – Combination of bash scripts, terraform scripts, etc • Based on CentOS 7.6 • Installs multiple repos • Has a separate VPC and security group – You are RC testers! 

16 .Setting up Today’s environment • Connection details – IP – User – PWD • Connecting to via a terminal/putty – # ssh centos@<IP> – Become root: # sudo -i – [root@perconalive-1-master ~]# 

17 .Setting up Today’s environment (Amazon) • Download the private key: https://tinyurl.com/yyglnrmr • ssh into the new machines: – take the perconalive.pem key • Test Connectivity – ssh -i perconalive.pem centos@<perconalive master host> – ssh -i perconalive.pem centos@<perconalive slave host> – http://<perconalive master host IP>/ (you should see an apache test page) • Become root: # sudo -i → Not a best practice! 

18 .Operational DBA In A Nutshell • Setting Up Today’s • Replication Environment • Schema Changes • MySQL Installation • Configuration Optimization • Logging In • MySQL Privileges • Diagnostics • Troubleshooting • Backups 

19 .Installation of MySQL • Install Percona Server # yum install Percona-Server-server-57 # service mysql start <ONLY ON MASTER> • Verify if you can connect # mysql [root@node1 ~]# mysql • Verify the application: Welcome to the MySQL monitor. Commands end with ; Your MySQL connection id is 270 – http://<MASTER_IP>/my-movies Server version: 5.7.11 Percona Server (GPL), Relea Copyright (c) 2000, 2013, Oracle and/or its affili Oracle is a registered trademark of Oracle Corpora affiliates. Other names may be trademarks of their owners. master mysql> 

20 .Today’s application 

21 .Operational DBA In A Nutshell • Setting Up Today’s • Replication Environment • Schema Changes • Installation • Configuration Optimization • Logging In • MySQL Privileges • Diagnostics • Troubleshooting • Backups 

22 . Intermezzo! What are DDL queries? • Data Definition Language • Example: CREATE TABLE Accounts ( Account_number Bigint(16) , Account_name varchar, Amount Bigint(16), PRIMARY KEY (Account_number) ); • Usage DDL queries define the structure on which you develop your application. Your structure will also define how the database server searches for information in a table. 

23 .What are DML statements? • Data Manipulation Language • Example: INSERT into resto_visitor values(5,'Julian',’highway 5’,12); INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); UPDATE resto_visitor set name='Evelyn',age=17 where id=103; • Usage It’s how you can interact with a Database. Fetch information, update information, remove or add information as in: SELECT, UPDATE, DELETE, ADD 

24 .Other types of queries • DCL (Data control language) • GRANT, REVOKE, … • TCL (Transaction control language) -- start a new transaction start transaction; -- Debit UPDATE sb_accounts SET balance = balance - 1000 WHERE account_no = 932656; -- Credit UPDATE ca_accounts SET balance = balance + 1000 WHERE account_no = 933456 ; -- commit changes commit; 

25 .Logging in - Handson! 

26 .Logging in - Handson! [root@perconalive-1-master ~]# mysql mysql> help mysql> SHOW DATABASES; mysql> show schemas; mysql> USE imdb; mysql> SHOW TABLES; mysql> describe users; mysql> show create table users; mysql> SELECT * FROM users LIMIT 1; mysql> SHOW PROCESSLIST; mysql> exit 

27 .MySQL CLI - Basic features • Prompt mysql> prompt Master > PROMPT set to 'Master > ' Master > prompt mysql> PROMPT set to 'mysql>' Undo: mysql> prompt • Edit mysql> edit Opens up a beautiful little editor: use sakila; select distinct(customer_id) from payment limit 5; 

28 .To Tee or not to Tee ... • Tee mysql> tee /tmp/tee.log Logging to file ‘/tmp/tee.log' mysql> select * from sakila.city limit 10; mysql> notee mysql> exit # cat /tmp/tee.log • Formatting help • help! • \g • \G                                                                                                                           

29 .MySQL CLI - basic functions • Pager mysql> pager grep queries mysql> show engine innodb status \G 0 queries inside InnoDB, 0 queries in queue mysql> pager md5sum PAGER set to 'md5sum' mysql> select * from city limit 10; 449d5bcae6e0e5b19e7101478934a7e6  - 10 rows in set (0.00 sec)   mysql> select city_id, city, country_id, last_update FROM city LIMIT 10 ; 449d5bcae6e0e5b19e7101478934a7e6  - 10 rows in set (0.00 sec)