DDEV MySQL Guide

Mastering MySQL with DDEV: A Quick Guide

If you’re a developer working with MySQL databases using DDEV for your local development environment, this guide will walk you through some essential commands and best practices. We’ll cover everything from listing databases to importing and exporting data, ensuring you have a solid foundation to manage your MySQL databases effectively.

Starting with DDEV

Before diving into MySQL commands, ensure your DDEV project is up and running. You can start your project with the following command:

ddev start

Listing Databases

To see all the databases available in your MySQL instance, use the following command:

ddev mysql -e "SHOW DATABASES;"

Alternatively, you can enter the MySQL prompt and list the databases interactively:

ddev mysql

Once inside the MySQL prompt, type:

SHOW DATABASES;

Accessing a Specific Database

To switch to a specific database, say ‘db’, you can do this directly from the terminal:

ddev mysql -D db

Or from within the MySQL prompt:

USE db;

Listing Tables

To list all the tables in the currently selected database:

SHOW TABLES;

Exiting MySQL

When you’re done, you can exit the MySQL prompt by typing:

EXIT;

QUIT;

Logging in as Root User

Sometimes, you may need elevated privileges to perform certain tasks. To log in as the root user:

ddev mysql -uroot -proot

By default, DDEV sets the root user’s password to root.

Exporting a Database

Exporting your database is crucial for backups and migrations. To export a database named ‘kratecms’, you can use:

ddev exec mysqldump -u root -proot kratecms > kratecms.sql

Importing a Database

To import a previously exported database file named kratecms.sql:

ddev import-db --src=kratecms.sql --database=kratecms

Or using mysql directly:

ddev exec mysql -u root -proot kratecms < kratecms.sql

Commonly Used SQL Commands

Here are some essential SQL commands you’ll frequently use:

Listing Databases:
```
SHOW DATABASES;
```
Using a Specific Database:
```
USE db;
```
Listing Tables:
```
SHOW TABLES;
```
Creating a Database:
```
CREATE DATABASE database_name;
```
Dropping a Database:
```
DROP DATABASE database_name;
```

Creating a Table:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Dropping a Table:
```
DROP TABLE table_name;
```

Inserting Data:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Selecting Data:

SELECT column1, column2, ...
FROM table_name;

Updating Data:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Deleting Data:

DELETE FROM table_name
WHERE condition;

Creating a User:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Granting Privileges:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

Flushing Privileges:
```
FLUSH PRIVILEGES;
```

Conclusion

Managing MySQL databases within a DDEV environment can be straightforward and efficient with the right commands and practices. Whether you’re exporting databases for backups, importing them for migrations, or just performing routine data management tasks, these commands will help you streamline your workflow and keep your projects running smoothly. Happy coding!