In this homework assignment, you will practice some basic database administration skills using MySQL. You will learn how to create and manage users, grant and revoke privileges, backup and restore databases, and monitor database performance. You will also use some common tools and commands for database administration.
To complete this homework, you will need to have access to a MySQL server and a MySQL client. You can use any MySQL server and client that you prefer, as long as they are compatible with the version of MySQL that you are using. You can also use a web-based interface such as phpMyAdmin if you are more comfortable with that.
The homework consists of three parts: Part A, Part B, and Part C. Each part has a set of questions and tasks that you need to answer and perform. You can submit your answers and screenshots of your work as a single document or as separate files. Please follow the instructions and guidelines given by your instructor for submission.
Part A: Creating and Managing Users
Create a new user called 'hw3' with the password 'hw3pass'.
Grant the user 'hw3' all privileges on the database 'hw3db'.
Revoke the privilege to drop tables from the user 'hw3' on the database 'hw3db'.
Show the privileges of the user 'hw3' on the database 'hw3db'.
Delete the user 'hw3' from the MySQL server.
Part B: Backup and Restore Databases
Create a backup of the database 'hw3db' using the mysqldump command. Save the backup file as 'hw3db.sql'.
Drop the database 'hw3db' from the MySQL server.
Restore the database 'hw3db' from the backup file 'hw3db.sql' using the mysql command.
Show the tables and data of the restored database 'hw3db'.
Part C: Monitor Database Performance
Show the status and variables of the MySQL server using the SHOW STATUS and SHOW VARIABLES commands.
Show the process list of the MySQL server using the SHOW PROCESSLIST command.
Show the slow query log of the MySQL server using the mysqldumpslow command.
Analyze and optimize one of the tables in the database 'hw3db' using the ANALYZE TABLE and OPTIMIZE TABLE commands.
Database administration is an essential skill for any data professional. Database administrators (DBAs) are responsible for ensuring the availability, security, performance, and integrity of the data stored in databases. They also perform tasks such as installing, configuring, upgrading, patching, backing up, restoring, tuning, and troubleshooting databases.
MySQL is one of the most popular and widely used open source relational database management systems (RDBMS) in the world. It is used by many websites, applications, and organizations for storing and managing data. MySQL offers many features and options for database administration, such as user management, privilege control, backup and recovery, replication, clustering, partitioning, logging, monitoring, and optimization.
This homework assignment is designed to help you learn and practice some of the basic database administration skills using MySQL. By completing this homework, you will gain a better understanding of how to create and manage users, grant and revoke privileges, backup and restore databases, and monitor database performance. You will also get familiar with some of the common tools and commands for database administration using MySQL. aa16f39245