MySQL Administration
MAMPU
Tag : MySQL, Administration
Tarikh Kursus : 21 - 25 February 2022
Lokasi : I-World, Jalan Yap Kwan Seng, KL
Introduction :
This course will use the concept of "From Zero to Hero", meaning that we will teach you from basic to mastering MySQL Administration. In order to fully grasp the content of the class, students are expected to have a basic database concept and SQL. Student without basic concept of datbase are welcome to join the class but must expect not to fully grasp the whole content.
Tools that will be used in this class are as follows :-
- MySQL 8
- Linux (Centos)
- Windows 10 / 11
- MySQL Workbench
- Terminal / Command Line
Each student should have the following minimum PC / Notebook specification recommendation:
- Window 10 with 8 GB RAM
- 45 GB Free Storage
Syllabus
|
Chapter 1: An Introduction to MYSQL 8
|
Chapter 8: Replication in MySQL 8
|
|
Chapter 2: Installing and Upgrading MySQL 8
|
Chapter 9: Partitioning in MySQL 8
|
|
Chapter 3: MySQL 8 – Using Programs and Utilities
|
Chapter 10: MySQL 8 – Scalability and High Availability
|
|
Chapter 4: MySQL 8 Data Types
|
Chapter 11: MySQL 8 – Security
|
|
Chapter 5: MySQL 8 Database Management
|
Chapter 12: Optimizing MySQL 8
|
|
Chapter 6: MySQL 8 Storage Engines
|
Chapter 13: Extending MySQL 8
|
|
Chapter 7: Indexing in MySQL 8
|
Chapter 14: MySQL 8 Best Practices and Benchmarking
|
|
Chapter 15: Troubleshooting MySQL 8
|
Training Materials
Sakila DB
Oracle Virtual Box
MySQL Download
MySQL Workbench
Trainer Profile
 |
Nama : Azman bin Zakaria
|
|
Azman merupakan senior programmer yang passionate dalam bidang pengaturcaraan komputer dari awal mulai beliau bekerja pada tahun 2000 sehingga hari ini. Beliau mempunyai pengalaman bekerja yang luas di agensi kerajaan, swasta dan sebagai freelancer.
|
|
Notes
Windows Installation
Windows Service:
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" MYSQL80
mysql
------
>cd C:\Program Files\MySQL 8.0\bin
>mysql -u root -p
>mysql -u root -p sakila
- login dan terus pilih database
>mysql --version
>mysql --user=root --password=Azman@1204
mysql>show databases;
mysql>use sakila;
mysql>show tables;
mysql>select * from actor limit 10;
mysql>status
mysql>\q
mysql>create database training;
mysql>source c:\users\azman\sakila_backup.sql
mysql>desc <table name>
- show table details
mysqladmin
------------
>mysqladmin --help
>mysqladmin -u root -p status
>mysqladmin -u root -p variables
- show semua setting variable. i.e timezone, admin port etc..
- we can edit this variables (if necessary)
>mysqladmin -u root -p processlist
>mysqladmin -u root -p shutdown
- stop mysql service (mysqld)
- utk start, tiada specific command, boleh guna windows net start mysql80 (login as admin)
mysqldump
------------
>mysqldump -u root -p sakila > sakila_backup.sql
>mysql training2 < c:\users\azman\sakila_backup.sql
mysqlimport
------------
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
>mysqlimport -u root -p --fields-terminated-by=, training c:\actor.csv
actor.csv
---------
207,Azman,Zakaria,2022-02-21 00:00:01
208,John,Doe,2022-02-22 00:00:01
mysql>load data infile 'c:/actor.csv' into table actor fields terminated by ',' ignore 0 lines;
mysqlpump -u root -pAzman@1204 --databases sakila > sakila3.sql
mysqlsh
--------
3 modes:
1. Js (V8)
2. Python
3. SQL
- hanya dlm MySQL 8.0 shj
mysqlslap
---------
>mysqlslap -u root -p -q "select * from sakila.film" -c 10
Linux installation
------------------
alter password, after using default pwd
- /var/log/mysqld.log
>cat /var/log/mysqld.log | grep password
>mysql -u root -p
>alter user 'root'@'localhost' identified by 'password';
>mysql_secure_installation
- password & conformation password
Disabled SELinux
-------------------
edit file ini --> /etc/selinux/config
- SELINUX=disabled
- save & reboot
>getenforce
- check status Se Linux
>firewall-cmd --add-port=3306/tcp --permanent
>firewall-cmd --reload
mysql>select user, host from mysql.user;
- list semua user dlm mysql
mysql>create user 'training'@'%' identified by 'password';
- % = boleh access dari mana2 shj
>mysql -h 192.168.0.112 -u training -p
Chapter 4 - Data Type
----------------------
mysql>create table person(name varchar(50), bod date, person_id int);
mysql>alter table person add column salary numeric(10,2) after name;
mysql>insert into person (name, salary, bod, person_id) values('Azman', 5000.00, '2022-02-22', 1);
mysql>update person set name='John', salary=7000.00, person_id=3 where name='Azman';
update person set created_at = '2022-02-22 15:38:00' where person_id=1;
insert into person(person_id, name) values(4, 'Ronaldo');
update person set created_at = now() where person_id=2;
select now() as masa_sekarang;
select 1 + 3;
select time(now()) as masa;
SELECT * FROM chapter4.person;
update person set time_from = '22:00:00' where person_id=1;
update person set time_from = current_time() where person_id=2;
-- time() return current time
update person set year_born = 1974 where person_id=2;
update person set address = '{"no":"22A", "poskod":"07000"}' where person_id = 1;
update person set address = json_object("no", "23", "poskod", "08000") where person_id = 2;
revision on day 3
-------------------
- Security
- create user, set role dan permission / privileges
>create user 'userid'@'ip / %' identified by 'password'
ip = ip yg client boleh connect
>grant all privileges on sakila.* to 'userid'@'...'
Roles
--------
- a group of privileges
>create role '';
>grant role to ...
Default DB
--------------
information_schema
- table, column, ...
sys
- metadata berkaitan global MySQL setting
mysql
- user
performance_schema
- analisa event / aktiviti dlm MySQL
MySQL Enterprise
------------------
- Firewall
- Mysql Ent. Monitor (MEM)
- web based, Tomcat (java)
- 18443 (https), 18080(http)
- Graph - bil connection, query
- Backup
- MySQL Workbench
- mysql general log, my.ini
Agenda :
---------
1. Replication
- Master -> slave
2. indexing
- performance
3. MySQl administration
- setting
4. Sample Exam Questions
Replication Steps
----------------------
Master Server (server 1)
-----------------------
1. sudo vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=sakila
2. >systemctl restart mysqld
3. mysql>create user 'slaveone'@'%' identified by 'password';
mysql>grant replication slave on *.* to 'slaveone'@'%';
4. Download sakila db
google: mysql sakila download
mysql>source /home/azman/Downloads/sakila-db/sakila-schema.sql
mysql>source /home/azman/Downloads/sakila-db/sakila-data.sql
5. mysql>show master status;
6. update actor set first_name = 'azman' where actor_id = 1;
7. show grants for 'slaveone'@'%';
8. grant all privileges on sakila.* to 'slaveone'@'%';
Slave setup (server 2)
--------------
1. Download sakila db
google: mysql sakila download
mysql>source /home/azman/Downloads/sakila-db/sakila-schema.sql
mysql>source /home/azman/Downloads/sakila-db/sakila-data.sql
2. sudo nano /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-do-db=sakila
3. >systemctl restart mysql
4. mysql>change master to master_host='192.168.0.139',
master_user='slaveone', master_password='Azman@1204',
master_log_file='mysql-bin.000004', master_log_pos=1362289;
5. start slave;
6. show slave status \G;
Server-side startup configuration
Chap 11 - security
- create user
- create role
- grant role & privileges
- lupa password
MySQL Enterprise
- Monitoring
- Backup
- Application Firewall
- prevent SQL Injection
mysql>create user 'training'@'192.168.0.100' identified by 'Azman@1204';
mysql>grant all privileges on sakila.* to 'training'@'%';
- grant semua priv dlm database sakila shj
mysql>grant select on sakila.film to 'hr'@'%';
mysql>show processlist;
mysql>create role 'developer_role';
mysql>grant select on sakila.film to 'developer_role'@'%';
mysql>grant select on sakila.actor to 'developer_role'@'%';
mysql>show grants for 'developer_role'@'%';
mysql>revoke SELECT ON `sakila`.`actor` from `hr`@`%`;
- delete priv dari a user
mysql>grant developer_role to 'hr'@'%';
--- login sbg hr
mysql>set role developer_role
- switch role
- baru boleh nampak semua table
mysql>select user, host from mysql.user
- show semua user dan role yg ada
mysql>set default role developer_role to 'hr'@'%';
- ini root yg kena buat
Password Expiration
mysql>alter user 'hr'@'%' password expire;
mysql> select user, host, password_expired from mysql.user;
mysql> alter user 'hr'@'%' identified by 'Azman@1204';
Borang Penilaian Keberkesanan Latihan
Training evaluation · MySQL Administration
Log in with the account you registered for this training to fill in the survey.
Log in to fill in the surveyAttendance
Students (0)
No students have registered yet.