MySQL how-to

Some useful knowledge for MySQL databases.

To backup smaller databases, automysqlbackup is a good choice for a MySQl database backup. In this chapter, I show the procedure to setup it.

Following informations are quite important for backup purposes:

1. Get the user and password to access the MySQL database. This is usually the user root!
2. Check for the database data directory: mysql -e “show variables;”|grep datadir
3. Check, if the binlog option is enabled: ls $(mysql -e “show variables;”|grep datadir|awk '{print $NF}')/|grep bin.index. If this command does not result with a filename, the binlog is not activated!
4. Amount of data: du -sh $(mysql -e “show variables;”|grep datadir|awk '{print $NF}')
5. Socket: mysql -e “show variables;”|grep socket
6. Network port: mysql -e “show variables;”|grep '^port'

Now, you are ready to edit the configuration file of automysqlbackup:

1. Add user: CONFIG_mysql_dump_username='root'
2. Add user’s password: CONFIG_mysql_dump_password='<password>'
3. Add the backup directory (usually a separate filesystem: CONFIG_backup_dir='<backup dir>'
4. Add the pre backup script, if needed: PREBACKUP=“<prebackup-script>”
5. Add the post backup script, if needed: POSTBACKUP=“<postbackup-script>”

The configuration file is expected here: /etc/automysqlbackup/automysqlbackup.conf

It's not a bad idea to test the configuration first. Just un comment the line containing this: CONFIG_dryrun=0 and change it to CONFIG_dryrun=1. Just run the script: /usr/bin/automysqlbackup.

If no error was listed, you can disable the CONFIG_dryrun=1 option in /etc/automysqlbackup/automysqlbackup.conf

There are several ways to restore a database dump file:

• Restore a plain SQL file:

  mysql <dbname> -e "source daily_<dbname>_2019-03-09_05h00m_Saturday.sql;"

• Restore a bzip2 compressed database dump file:

  bzip2 -cd daily_<dbname>_2019-03-09_05h00m_Saturday.sql.bz2 | mysql <dbname>

• Restore a gzip compressed database dump file:

  gzip -cd daily_<dbname>_2019-03-09_05h00m_Saturday.sql.gz | mysql <dbname>

In this example, the password for the MySQL user root will be updated for following hosts: ::1, 127.0.0.1 and localhost.

mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='<username>' AND Host='::1';  
mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='<username>' AND Host='127.0.0.1';  
mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='<username>' AND Host='localhost';

or, if you want to change the password for all hosts of this user:

mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='<username>'

To get all user permissions and attributes for a user, following command is helpful:

mysql> SELECT * FROM mysql.user WHERE user='<username>' AND host='localhost' \G

The result:

    *************************** 1. row ***************************
                      Host: localhost
                      User: <username>
                  Password: *D115A249596ECD669091C1C6535DE65DE8427472
               Select_priv: Y
               Insert_priv: Y
               Update_priv: Y
               Delete_priv: Y
               Create_priv: Y
                 Drop_priv: Y
               Reload_priv: Y
             Shutdown_priv: Y
              Process_priv: Y
                 File_priv: Y
                Grant_priv: Y
           References_priv: Y
                Index_priv: Y
                Alter_priv: Y
              Show_db_priv: Y
                Super_priv: Y
     Create_tmp_table_priv: Y
          Lock_tables_priv: Y
              Execute_priv: Y
           Repl_slave_priv: Y
          Repl_client_priv: Y
          Create_view_priv: Y
            Show_view_priv: Y
       Create_routine_priv: Y
        Alter_routine_priv: Y
          Create_user_priv: Y
                Event_priv: Y
              Trigger_priv: Y
    Create_tablespace_priv: Y
                  ssl_type:
                ssl_cipher:
               x509_issuer:
              x509_subject:
             max_questions: 0
               max_updates: 0
           max_connections: 0
      max_user_connections: 0
                    plugin:
     authentication_string:
    1 row in set (0.01 sec)

• Allow all grants for a user:

  mysql> GRANT ALL PRIVILEGES ON `<dbname>`.* TO '<username>'@'localhost' WITH GRANT OPTION;

• Allow specified grants for a user:

  mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, SELECT, UPDATE ON `<dbname>`.* TO '<username>'@'localhost' WITH GRANT OPTION;