MySQL

Recently I came across this piece of gold when dealing with databases, particularly relational ones like MySQL, managing transactions efficiently is crucial to ensure data integrity and consistency. In MySQL, transactions are used to group several SQL commands into a single unit that either completely succeeds or completely fails, ensuring that a database remains in a consistent state.

Goal: I want to download a table list to a txt or csv file.

Initially, as a root user I tried using drush

drush sql-dump --tables-list=media_field_data > db-list.sql

Of course as a root user and Drush set-up not as root. failed with 

Command 'drush' not found, did you mean:

  command 'rush' from deb rush (1.8+dfsg-1.1)

My bad.

These steps are for Drupal 8 and 9.

Export your database

Order here is important.  First you want to clear all the Drupal caches.  Then export / dump the db the sql database to a file in your home directory.

drush cr
drush sql-dump > path/to/your/file/ourpout/sql-dump-file-name.sql

or

On your local OSX environment using Terminal or iTerm you can create a MySQL database, database user, and password, as well as, assign all privileges to the user for the database.

Knowing your credentials, before beginning you will need to know the following:

user: {user}
password: {password}
database: {database}

For my local environment, I'll be using these credentials

This article assumes that you are running Docker and Lando already.  Don't meet this... have a read through the following page "Building a Drupal or Laravel instance on OSX using Lando and Docker" for more.

I'll be using the following: .lando.yml file:

Running Docker and attempting to install a web proxy environment where I'm attempting to create local web set up the consist of Drupal 9 with Composer + Docker-compose + Nginx + MariaDB + PHP8.1

However, when I run the command

docker-compose up

The terminal hangs at

Working on an Azure server I ran a mysqldump command to generate a copy of the db.  No issues, the file downloaded on to the serve without cause.

The Problem

On my local environment, using Sequel Ace I imported the file, but it suddenly it stopped with a red message “Unknown collation: ‘utf8mb4_0900_ai_ci‘”

When attempting to backup mysql using the mysqldump command

mysqldump -u root -p  database_name > backup.sql

However, it is throwing an error:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

What to do?

How to Create New MySQL User

Before you can create a new MySQL user, you need to open a terminal window and launch the MySQL shell as the root user. To do so, enter the following command:

sudo mysql –u root –p

or rather than typing sudo each time you can enter:

sudo su

mysql -u root -p

In either scenario next type in the root password and press Enter.

The prompt will change to

mysql>

The generally accepted answer to a mysqldump is:

mysqldump -h [host] -u [user] -p [database_name] [table_name] > [file].sql

Or a variant there of...  Of course if you are logged in to the server and working from it you don't need to express the -h [host]

mysqldump -u [user] -p [database_name] [table_name] > [file].sql