mysql cli snippets, mysql import , mysql export, mysql truncate, mysql create user

configure mysql client in dbeaver.md

Mysql binaries are required to perform complex tasks like backup or restore. If you are inside of mysql server, these executables already exist:

• /usr/bin/mysql
• /usr/bin/mysqldump

If you are just a client, follow one of this tips to get the binaries

docker

Start a mysql container 5 or 8 dependig of your requirements

docker run -it --name mysql-5 --entrypoint bash  mysql:5.7.44
docker run -it --name mysql-8 --entrypoint bash  mysql:8.2.0

To get other versios check: https://hub.docker.com/_/mysql

Then, just get the binary from the mysql container

docker cp mysql-5:/usr/bin/mysql /home/drivers/mysql-5
docker cp mysql-8:/usr/bin/mysql /home/drivers/mysql-8

Same for mysqldump and others

public download

mysql 8

• download from: https://dev.mysql.com/downloads/mysql/ in Linux - Generic (glibc 2.17) (x86, 64-bit), Compressed TAR Archive Minimal Install section
• or directly at https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.25-linux-glibc2.17-x86_64-minimal.tar.xz

mysql 5

• https://dev.mysql.com/downloads/mysql/ choose previous
• directly https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

---

errors

libncurses.so.5: cannot open shared object file

solution sudo apt-get install libncurses5

custom entrypoint.md

entrypoint: /bin/bash command: "--init-file ~/.env/api/bin/activate p-api config"

https://forums.docker.com/t/overriding-entrypoint-and-command-in-docker-compose-yml/38589/3

https://stackoverflow.com/questions/67138684/how-to-wait-for-mysql-docker-entrypoint-initdb-before-starting-a-service

docker manage.sh

#!/bin/bash

echo "Parameters"
for ARGUMENT in "$@"
do
    if [[ -z "${ARGUMENT// }" ]]
    then
      continue;
    fi

    KEY=$(echo $ARGUMENT | cut -f1 -d=)
    KEY_LENGTH=${#KEY}
    VALUE="${ARGUMENT:$KEY_LENGTH+1}"
    export "$KEY"="$VALUE"
    echo "$KEY"=$VALUE
done

function import_db { 
  echo	
  echo "starting import..."
  mysql -u $user -p $database_name < $file_to_import
}


function export_db {
  echo
  echo "starting export..."
  now=$(date '+%Y-%m-%d_%H-%M-%S')
  file_to_export=$HOME/$database_name-$now.sql
  mysqldump -u $user -p $database_name > $file_to_export
  sed -n 1,10p  $file_to_export
  echo ""
  echo ""
  echo "run this from host to get the file"
  filename=$(basename -- "$file_to_export")
  extension="${filename##*.}"
  filename="${filename%.*}"  
  echo "docker cp container:$file_to_export /foo/$filename.$extension"
}

function clear_db {
  echo
  echo "starting back up"
  now=$(date '+%Y-%m-%d_%H-%M-%S')
  file_to_export=$HOME/$database_name-$now.sql
  export_db
  echo
  echo
  echo "starting tables deletion..."  
  drop_script=$HOME/drop_all_tables-$now.sql
  echo "SET FOREIGN_KEY_CHECKS = 0;" > $drop_script
  ( mysqldump --add-drop-table --no-data -u $user -p $database_name | grep 'DROP TABLE' ) >> $drop_script
  echo "SET FOREIGN_KEY_CHECKS = 1;" >> $drop_script

  echo
  cat $drop_script
  echo
  echo
  mysql -u $user -p $database_name < $drop_script

}

function truncate_tables {
  echo
  echo "starting back up"
  now=$(date '+%Y-%m-%d_%H-%M-%S')
  file_to_export=$HOME/$database_name-$now.sql
  export_db
  echo
  echo
  echo "starting truncate tables..."  
  truncate_tables_script=$HOME/truncate_tables-$now.sql
  echo "SET FOREIGN_KEY_CHECKS = 0;" > $truncate_tables_script
  ( mysqldump --add-drop-table --no-data -u $user -p $database_name | grep ^DROP | sed -e 's/DROP TABLE IF EXISTS/TRUNCATE TABLE/g') >> $truncate_tables_script
  echo "SET FOREIGN_KEY_CHECKS = 1;" >> $truncate_tables_script

  echo
  cat $truncate_tables_script
  echo
  echo
  mysql -u $user -p $database_name < $truncate_tables_script

}

function create_user_and_db {
  echo
  now=$(date '+%Y-%m-%d_%H-%M-%S')
  script_file=$HOME/$script_file-$now.sql  
  echo "creating db"
  echo "CREATE DATABASE $database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"> $script_file
  echo "CREATE USER '$new_user_name'@'%' IDENTIFIED BY '$new_user_password';">> $script_file
  echo "GRANT SELECT,INSERT,CREATE,ALTER,DROP,LOCK TABLES,CREATE TEMPORARY TABLES, DELETE,UPDATE,EXECUTE ON $database_name.* TO '$new_user_name'@'%';">> $script_file
  mysql -u $admin_user -p < $script_file
}


case $operation in

  create_user_and_db)
    create_user_and_db
    ;;

  import_db)
    import_db
    ;;

  export_db)
    export_db
    ;;

  clear_db)
    clear_db
    ;;

  truncate_tables)
    truncate_tables
    ;;

  *)
    echo "operation is not supported: $operation"
    ;;
esac

docker mysql manager.md

copy script

docker cp /tmp/workspace/manage.sh my_mysql_container:/usr/local/bin/

copy dump

docker cp /tmp/workspace/dump.sql my_mysql_container:/tmp

run import

docker exec -it my_mysql_container bash manage.sh operation=import_db user=root file_to_import=/tmp/acme.sql database_name=acme

run export

docker exec -it my_mysql_container bash manage.sh operation=export_db user=root database_name=acme

run clear db

docker exec -it my_mysql_container bash manage.sh operation=clear_db database_name=zordon  user=root

run truncate only tables

docker exec -it my_mysql_container bash manage.sh operation=truncate_db database_name=zordon  user=root

create user and add it to a new database

docker exec -it mysql bash manage.sh database_name=acme new_user_name=jane new_user_password=changeme operation=create_user_and_db admin_user=root

enable remote connections.md

in one of these files

• /etc/mysql/my.cnf
• /etc/mysql/mysql.conf.d/mysqld.cnf
• /etc/my.cnf
• /etc/mysql/my.cnf
• $MYSQL_HOME/my.cnf
• [datadir]/my.cnf
• ~/.my.cnf

comment this line

bind-address = 127.0.0.1

restart

sudo service mysql restart

source:

- https://stackoverflow.com/a/61012107/3957754
- https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location

mysql_cli_snippets.md

mysql -u root -psecret
SHOW DATABASES;
CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE app;
source \tmp\test.sql;

CREATE TABLE `app_variables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `externalId` varchar(100) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `var_type` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

export

mysqldump -u root -p db_name > db_backup.sql

mysqldump -P 3310 -h 127.0.0.1 -u mysql_user -p database_name table_name

import

docker cp /tmp/workspace/ragu/dump.sql database:/tmp
docker exec -it mysql_container bash   
mysql -u root -p db_name < /tmp/dump.sql


mysql> use db_name;

mysql> SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;

remote connect

mysql -u root -p'changeme' -h remotemysql.com -P 3306 -D my_database_name

DEFINER

free databases and the awesome aws does not support DEFINER. The following sentence, delete them

mysqldump  -u root -p my_database_name | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'  > /tmp/my_database_name.sql

These

• https://stackoverflow.com/questions/11601692/mysql-amazon-rds-error-you-do-not-have-super-privileges
• https://aws.amazon.com/premiumsupport/knowledge-center/error-1227-mysqldump/

does not work me. log_bin_trust_function_creators to 1 and sed worked for me

This could be the key but I did not try:

This solution works, but there is one detail to consider... wait at least 5 minutes before using the "parameter group" https://aws.amazon.com/es/premiumsupport/knowledge-center/rds-mysql-functions/

phpmyadmin

docker run --name phpmyadmin  -d -e "PMA_ARBITRARY=1" -p 8082:80 phpmyadmin/phpmyadmin
docker run --name my-own-phpmyadmin -d --link my-own-mysql:db -p 8081:80 phpmyadmin/phpmyadmin

https://migueldoctor.medium.com/run-mysql-phpmyadmin-locally-in-3-steps-using-docker-74eb735fa1fc

create database

Before, check the collation to create similar databases

SELECT @@character_set_database, @@collation_database; 
SELECT TABLE_SCHEMA     , TABLE_NAME     , TABLE_COLLATION  FROM INFORMATION_SCHEMA.TABLES;

CREATE DATABASE acme CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

create admin user for acme db

CREATE DATABASE acme CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'usr_acme'@'%' IDENTIFIED BY 'changeme';
GRANT SELECT,INSERT,CREATE,ALTER,DROP,LOCK TABLES,CREATE TEMPORARY TABLES, DELETE,UPDATE,EXECUTE,REFERENCES,CREATE VIEW ON acme.* TO 'usr_acme'@'%';

create application user for acme db

CREATE DATABASE acme CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'usr_acme'@'%' IDENTIFIED BY 'changeme';
GRANT SELECT,INSERT,DELETE,UPDATE,EXECUTE ON acme.* TO 'usr_acme'@'%';

create read only user in acme db

CREATE DATABASE acme CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'usr_acme'@'%' IDENTIFIED BY 'changeme';
GRANT SELECT,SHOW VIEW ON acme.* TO 'usr_acme'@'%';

Sources:

• https://docs.oracle.com/en/industries/communications/cloud-native-core/2.2.0/cnc_install/create-mysql-database-and-user.html
• https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql
• https://www.a2hosting.com/kb/developer-corner/mysql/managing-mysql-databases-and-users-from-the-command-line

official github.md

https://github.com/docker-library/mysql/tree/master/5.7

sandbox.md

source /usr/local/bin/docker-entrypoint.sh

mysql_note ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
mysql_note "Creating multiple databases"

echo "
CREATE DATABASE IF NOT EXISTS \`$MYSQL_DATABASE_1\`;
GRANT ALL PRIVILEGES ON \`$MYSQL_DATABASE_1\`.* TO '$MYSQL_USER_1'@'%' IDENTIFIED BY '$MYSQL_USER_1_PASSWORD';
" | docker_process_sql

echo "
CREATE DATABASE IF NOT EXISTS \`$MYSQL_DATABASE_2\`;
GRANT ALL PRIVILEGES ON \`$MYSQL_DATABASE_2\`.* TO '$MYSQL_USER_2'@'%' IDENTIFIED BY '$MYSQL_USER_2_PASSWORD';
" | docker_process_sql


mysql_note ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
sed -i -e 's/-- Schema eventhos/use eventhos;/g' /custom-docker-entrypoint-initdb.d/dump.sql

# cat /custom-docker-entrypoint-initdb.d/dump.sql  | docker_process_sql

# docker_process_init_files /custom-docker-entrypoint-initdb.d/*
mysql -u $MYSQL_USER_1 -p $MYSQL_USER_1_PASSWORD $MYSQL_DATABASE_1 < /custom-docker-entrypoint-initdb.d/dump.sql