MySQL - HVboom/HowTo-DigitalOcean GitHub Wiki

Description

MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server.

:speech_balloon: copied from package description

• MySQL.com

Setup MySQL

• Follow the instruction to setup MySQL:

  sudo pkg install mysql57-server
  sudo sysrc mysql_enable=yes
  sudo service mysql-server start
  

• Secure MySQL by running: sudo mysql_secure_installation

• Test the installation by executing: mysqladmin --user root --password version status proc

  • Troubleshooting:

    mysqladmin: connect to server at 'localhost' failed
    error: 'Your password has expired. To log in you must change it using a client that supports expired passwords.'
    

    Login with mysql --user root --password and execute ALTER USER USER() IDENTIFIED BY '<new_password>';

• Setup phpMyAdmin to maintain your data bases

Backup all databases

• Create a backup script /usr/local/share/bin/mysql_backup

  #!/usr/local/bin/bash
  
  TODAY=$(date '+%Y%m%d_%H%M%S')
  BACKUPDIR=$HOME/mysql_backup/$TODAY
  
  # Options for InnoDB
  OPTIONS="--single-transaction --set-gtid-purged=OFF --add-drop-database --triggers --routines --events"
  
  echo "Save MySQL backups into $BACKUPDIR"
  mkdir -p $BACKUPDIR
  cd $BACKUPDIR
  
  DATABASES=`mysql --login-path=local -Bse 'show databases'`
  for DATABASE in $DATABASES; do
    if [ "$DATABASE" != "information_schema" ]; then
      echo "  Backup $DATABASE..."
      mysqldump --login-path=local $DATABASE $OPTIONS > ${DATABASE}.sql
    fi
  done
  

• Store an encrypted version of the root user password

  • you will be prompted to enter the password
  • the command will create the file $HOME/.mylogin.cnf

  mysql_config_editor set --login-path=local --host=localhost --user=root --password
  

• Run the backup script at any time: mysql_backup

Upgrade to new version

• Create a backup of your databases: mysql_backup
• Follow the instructions to check prerequisites
• Shutdown MySQL: sudo service mysql-server stop or mysqladmin --login-path=local shutdown
• Upgrade to newest version: sudo pkg install mysql80-server
• Start the MySQL daemon again, which will automatically run necessary upgrades: sudo service mysql-server start