MySQL Administration (2024)

Section 1. Exploring MySQL Server

• MySQL Architecture – Learn about MySQL architecture and understand its key components.
• mysqld – Explain the mysqld, which is known as MySQL server.
• Start MySQL Server – Show you how to start the MySQL Server on Windows and Linux.
• Stop MySQL Server – Describe the steps of stopping MySQL Server on Windows and Linux.
• Restart MySQL Server – Guide you on how to restart MySQL Server on Windows and Linux.
• MySQL configuration file – Explore the MySQL configuration file, discover its location, and understand its structure.
• MySQL Data Directory – Learn about the data directory that stores databases, tables, log files, and other essential data for MySQL Server
• Customizing MySQL prompt – Show you how to customize the MySQL prompt to make it more obvious.

Section 2. System Variables

• Global variables – Show you how to use global variables to change the behaviors of the MySQL server.
• Session variables – Guide you on how to use the session variables to change the settings specific to a database session.
• Display MySQL version – Learn how to display the version of the MySQL server.

Section 3. MySQL Storage Engines

• Storage Engines – Briefly explain the storage engines and their features.
• InnoDB – Learn about the InnoDB storage engine, which is the default storage engine of MySQL.
• MyISAM – Show you how to use the MyISAM storage engine for speed and simplicity.
• MERGE – Learn how to merge multiple MyISAM tables into a MERGE table and manage them as one table.
• ARCHIVE – Learn how to use the ARCHIVE storage engine to create tables for archiving data with minimal space.
• BLACKHOLE – Use the BLACKHOLE storage engine not to store table data locally.
• MEMORY – Explain the MEMORY storage engine and how to store table data entirely in the memory.
• CSV – Learn how to use the CSV storage engine to store table data in CSV files.

Section 4. InnoDB Storage Engine Configuration

This section introduces the InnoDB architecture and shows you how to configure key InnoDB variables to optimize the MySQL server.

• InnoDB Architecture – introduce the InnoDB architecture and its in-memory and on-disk structures.
• innodb_buffer_pool_size – configure the buffer pool size to improve MySQL performance.
• innodb_buffer_pool_instances – configure multiple buffer pool instances to improve concurrency.
• innodb_buffer_pool_chunk_size – configure the chunk size of the buffer pool.
• innodb_flush_method – configure the InnoDB flush method for flushing data from memory to disk.
• innodb_dedicated_server – automatically configure InnoDB key variables when MySQL runs on a dedicated server.

Section 5. MySQL Server Logs

• Binary Logs – introduce the binary logs concepts and how to manage binary logs effectively.
• Disable Binary Logs – learn step-by-step how to disable binary logs.
• Slow Query Logs – guide you on how to enable the slow query logs, configure various parameters, and examine the slow queries.

Section 6. Authentication & Authorizations

• Create Users – Learn how to create a new user in MySQL Server.
• Grant Privileges – Grant privileges to a user account.
• Revoke Privileges – Revoke privileges from a user account.
• Manage Roles – Manage roles in the database.
• Show Granted Privileges – Display privileges associated with a user account or role.
• Drop users – Show how to delete a user account.
• Change password – Describe ways to change passwords for users.
• Show Users – Learn how to show user accounts in MySQL Server.
• Rename users – Rename a user to another.
• Lock user accounts – How to lock user accounts.
• Unlock user accounts – How to unlock user accounts.
• mysql_config_editor – Show you how to use the mysql_config_editor utility to manage passwords securely.

Section 7. Show commands

• Show Databases – Display all databases in the MySQL Server.
• Show Tables – List all tables in a given database.
• Show Columns – List all columns in a table.
• Show Processlist – Show the current processes in the MySQL Server.

Section 8. MySQL Backup & Restore

• MySQL Backup – Learn about backup types in MySQL, including physical and logical backups.
• Back up and restore a database – show you how to back up and restore a single database.
• Back up and restore all databases – learn how to back up and restore all databases on a MySQL server.
• Back up one or more tables – guide you on how to back up one or more tables from a database.
• Restore from a dump file – learn how to restore from a dump file.
• Perform a Point-in-time Recovery – show you how to perform a point-in-time recovery that allows you to restore a database to a specified time.

Section 9. Table maintenance

• Check tables – Guide you on how to check one or more tables or views for errors.
• Analyze tables – Show you how to update the table statistics to help the query optimizer generate the optimal query execution plans.
• Repair tables – Learn how to repair possibly corrupted tables including MyISAM, ARCHIVE, and CSV tales.
• Optimize tables – Show you how to optimize tables to reclaim wasted storage space & improve table access.
• mysqlcheck – Learn how to use the mysqlcheck command-line utility to check, repair, analyze, and optimize MySQL database tables.

Section 10. Using mysqladmin tool

In this section, you will learn how to use the mysqladmin client tool to perform database administrative tasks.

• mysqladmin – Show you how to efficiently carry out database administrative tasks using the mysqladmin command-line utility.
• Create a new database – show you how to use the mysqladmin to create a new database.
• Drop a database – Guide you on how to use the mysqladmin command to drop an existing database.
• Change user’s password – Learn how to change the user’s password.

Section 11. Other MySQL Administration Tasks

• Execute SQL files – Guide you on how to use the SOURCE command to execute SQL statements in a file.
• Load Timezones Data – Learn how to load time zone data into the time zone tables in MySQL server on Windows, macOS, and Linux.
• Kill a process in MySQL – Learn how to kill a process in MySQL using the KILL statement.
• MySQL port – show you how to find the port that MySQL is using.