MySQL Database Management Flashcards
(25 cards)
What is MySQL in the LAMP stack?
MySQL is the relational database management system that stores data for PHP and WordPress applications.
MySQL manages WordPress data like posts, users, and settings in tables. Freelancers use MySQL to store client site data, while enterprise architects design scalable schemas for high-traffic systems, aligning with your PHP database interaction experience.
How do you install MySQL on a Linux server?
Use sudo apt install mysql-server on Debian/Ubuntu, then run sudo mysql_secure_installation.
Installs MySQL and secures it for LAMP. For WordPress, this sets up the database backend. Freelancers configure MySQL for clients, while enterprise architects automate installations, building on your Linux basics knowledge.
What is the MySQL command-line client?
The MySQL client (mysql) connects to the database, like mysql -u root -p.
Accesses WordPress databases for queries. Freelancers manage databases via the client, while enterprise architects use it for manual checks or scripting, complementing your XAMPP MySQL setup.
How do you create a MySQL database?
Use CREATE DATABASE mydb;, executed in the MySQL client.
Creates a database for WordPress (e.g., wp_database). Freelancers set up databases for client sites, while enterprise architects ensure proper naming conventions for multi-site setups.
How do you create a MySQL user?
Use CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;.
Creates a user for WordPress’s wp-config.php. Freelancers configure secure users, while enterprise architects restrict user access for security, per your security decks.
How do you grant privileges in MySQL?
Use GRANT ALL PRIVILEGES ON mydb.* TO ‘username’@’localhost’;, then FLUSH PRIVILEGES;.
Grants WordPress database access. Freelancers assign privileges, while enterprise architects apply least privilege principles, aligning with your PHP security knowledge.
What is a MySQL table?
A table is a structured collection of rows and columns, like WordPress’s wp_posts.
Stores data (e.g., posts, comments). Freelancers create tables for custom plugins, while enterprise architects design normalized tables for scalability.
How do you create a MySQL table?
Use CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));.
Defines a table for WordPress custom data. Freelancers build tables for client needs, while enterprise architects optimize column types for performance.
What is a PRIMARY KEY in MySQL?
A PRIMARY KEY uniquely identifies rows, like id INT AUTO_INCREMENT PRIMARY KEY.
Used in WordPress tables (e.g., wp_posts.ID). Freelancers ensure unique IDs, while enterprise architects index keys for fast queries, per your database deck.
What is the SELECT query in MySQL?
SELECT retrieves data, like SELECT * FROM wp_posts WHERE post_status = ‘publish’;.
Fetches WordPress posts. Freelancers query data for plugins, while enterprise architects optimize SELECT for large datasets, building on your $wpdb experience.
What is the INSERT query in MySQL?
INSERT adds data, like INSERT INTO wp_users (user_login, user_email) VALUES (‘john’, ‘john@example.com’);.
Adds WordPress users. Freelancers insert custom data, while enterprise architects use prepared statements for security, as in your PHP database deck.
What is the UPDATE query in MySQL?
UPDATE modifies data, like UPDATE wp_posts SET post_title = ‘New Title’ WHERE ID = 1;.
Updates WordPress posts. Freelancers edit data for clients, while enterprise architects ensure safe updates with transactions.
What is the DELETE query in MySQL?
DELETE removes data, like DELETE FROM wp_comments WHERE comment_ID = 1;.
Deletes WordPress comments. Freelancers clean data, while enterprise architects use DELETE cautiously to maintain integrity, per your $wpdb->delete() knowledge.
What is a MySQL index?
An index speeds up queries by organizing data, like CREATE INDEX idx_name ON users(name);.
Optimizes WordPress searches. Freelancers add indexes for performance, while enterprise architects balance index overhead, aligning with your performance deck.
How do you view MySQL indexes?
Use SHOW INDEX FROM table_name;, like SHOW INDEX FROM wp_posts;.
Checks WordPress table indexes. Freelancers verify optimization, while enterprise architects analyze indexes for query efficiency.
What is the EXPLAIN command in MySQL?
EXPLAIN analyzes query execution, like EXPLAIN SELECT * FROM wp_posts;.
Identifies slow WordPress queries. Freelancers optimize queries, while enterprise architects use EXPLAIN for performance tuning, per your performance goals.
What is mysqldump in MySQL?
mysqldump exports databases, like mysqldump -u root -p mydb > backup.sql.
Backs up WordPress databases. Freelancers create backups, while enterprise architects automate mysqldump for disaster recovery, as in your security deck.
How do you restore a MySQL database?
Use mysql -u root -p mydb < backup.sql to import a dump.
Restores WordPress data. Freelancers recover client sites, while enterprise architects script restorations for high availability.
What is a MySQL transaction?
A transaction groups queries to ensure all succeed or none apply, like START TRANSACTION; COMMIT;.
Ensures WordPress data integrity. Freelancers use transactions in plugins, while enterprise architects implement them for complex operations, per your PHP database deck.
What is the ROLLBACK command in MySQL?
ROLLBACK cancels a transaction’s changes, like ROLLBACK;.
Reverts WordPress updates on failure. Freelancers use it for safe operations, while enterprise architects ensure transaction reliability.
What is MySQL user security?
User security restricts access with specific privileges, like GRANT SELECT ON mydb.* TO ‘user’@’localhost’;.
Secures WordPress databases. Freelancers limit user rights, while enterprise architects enforce least privilege, aligning with your security interests.
How do you revoke MySQL privileges?
Use REVOKE, like REVOKE ALL PRIVILEGES ON mydb.* FROM ‘user’@’localhost’;.
Removes WordPress database access. Freelancers manage user permissions, while enterprise architects audit access for compliance.
What is the SHOW DATABASES command?
SHOW DATABASES; lists all databases on the MySQL server.
Identifies WordPress databases. Freelancers check available databases, while enterprise architects manage multiple databases for multi-site setups.
What is the DESCRIBE command in MySQL?
DESCRIBE table_name; shows a table’s structure, like DESCRIBE wp_posts;.
Inspects WordPress table columns. Freelancers verify schemas, while enterprise architects document table structures for system design.
