Exporting a MySQL table to a file
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.
Importing and Exporting a MySQL database with Drush
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
Create a MySQL database using command line (CLI)
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
Connecting your Lando DB with an external MySQL app (Sequel Ace) on an local environment
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:
Mariadb hangs on start up in Docker
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
Unknown collation: ‘utf8mb4_0900_ai_ci' Error
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‘”
Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
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
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>
How to mysqldump remote db on a AWS server?
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
Adding ejabbed MYSQL schema to your database
The starting point is to read through and follow the notes outlined on the ejabberd site regarding adding database schema. The purpose of this documentation is to clarify where the files were located for me.
The schema files can be found in ejabberd priv directory. To find the priv directory, use the find command