Zaawansowana administracja MySQL

Target group:

It is addressed to people who have basic knowledge of using Windows/Linux as a server, know SQL,

Physical/logical architecture MySQL

• Server files and startup scripts
• Server configuration MySQL
• My.cnf
• Basic parameters
• Server log files
• Querylog
• Slow query log
• Errorlog

Management

• User accounts
• Permissions and security in MySQL
• Backup and Restore - Mysqldump vs. Percona Backup
• Database maintenance

Schema optimization

• InnoDB: specific presentation - advantages and disadvantages, what is it really different from MyISAM - when to use what?
• How to choose primary keys (when spatial, when b-tree, etc.)?
• Fulltext search on the InnoDB engine (<5.6 only MyISAM possible, above 5.6 MyISAM+InnoDB)

MySQL Query Cache

• Why, for what queries, what value, is it worth making it large?

Performance testing, determining performance problems

• What to test – depending on the results we want to obtain
• Solving performance problems (slow queries, waits, performance tools, etc.)

Data sharding (horizontally and vertically)

• Differences, costs, limitations

Schema optimization

Indexing

• Indexes on very large tables
• Primary keys (when complex, in what fields)

Data types

• Tips in selecting data types, correct use of field types (int/float, time, geolocation) - benefits, limitations
• Is a primary key other than int acceptable and equally efficient?
• Should every table have a primary key?
• Transactions - when to use and when not - advantages and disadvantages.

Optimization of the operating system and hardware utilization

• The most important parameters in my.cnf

Optimization at the application level

• Optimization tools
• Scripts that analyze settings and display suggestions

Database replication

• Replications, clusters - how to achieve high availability?
• Replication Configuration (ROW/PAGE, troubleshooting, rebuilding, monitoring/diagnosing the replication process

MySQL Proxy vs. HAProxy

• Principle of operation, reliability(?), advantages, disadvantages

MySQL Cluster

• Principle of operation
• Configuration
• Efficiency
• Security

Cache

• Cache MySQL, temporary tables
• Is it worth moving relationships to databases or is it better to keep them in the code?
• Subqueries & joins - should they be used, how should they be optimized?

Explain as an aid for query testing

• Using indexes to build queries
• Query profiling - what tools and how to do it effectively?
• Visual design tools - is it worth using or simplifying structures?

Good practices and naming conventions - keys, columns, indexes, tables

Building optimal table structures

• Triggers: good practices when it comes to maintaining logic in procedures and triggers - how to manage, how to test, when it is worth using?
• Design patterns and anti-patterns

Is it worth switching to MariaDB?

Management tools

• MySQL Workbench
• Navicat
• Heidi SQL