MySQL

Backups

Export

mysqldump --opt -B $1 --add-drop-database -p > "${1}_export_$(date -u + "%Y-%m-%dT%H:%M:%SZ").sql"
  • $1 = Database Name

Import

cat $1 | mysql -u $2 "-p$3"
  • $1 = Input Filename

  • $2 = Database Username

  • $3 = User Password

Table Copy

CREATE TABLE IF NOT EXISTS $1_bk SELECT * FROM $1
  • $1 = Table Name

User Management

Create User

GRANT ALL PRIVILEGES ON $1.* TO '$2'@'localhost' IDENTIFIED BY '$3';
  • $1 = Database Name

  • $2 = Username

  • $3 = Password

Database Info

Engine Name

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$1'
  • $1 = Database Name

Database Recovery

In the case a MySQL or MariaDB database is corrupted, it has been proven to work doing one of the following.

Go through those options from top to bottom.

One of these options might work.

File Permission Check

Check for the File permissions, they have to be the docker user’s.

The user needs to have Read and Write permissions!

Boot database in Safe-Mode

Start the database in safe-mode:

mysqld_safe --skip-grant-tables

User Permission Check

Check if the Accessing User exists and has all required permissions on the Database.

Update to a newer Database

Update to a newer version of the Database:

mysql-upgrade

Clone Database

Sometimes cloning/copying a database to a new name is required.

mysqldump $1 > $2.sql
mysqladmin create $3
mysql $3 < $2.sql
  • $1 = Source Database

  • $2 = Database Dump File

  • $3 = Target Database