mysql [DS Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== MySQL how-to ======
Some useful knowledge for MySQL databases.
===== Backup =====
To backup smaller databases, ''automysqlbackup'' is a good choice for a MySQl database backup. In this chapter, I show the procedure to setup it.

==== Get environmet ====
Following informations are quite important for backup purposes:

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

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

  - Add user: ''CONFIG_mysql_dump_username='root' ''
  - Add user’s password: ''CONFIG_mysql_dump_password='<password>' ''
  - Add the backup directory (usually a separate filesystem: ''CONFIG_backup_dir='<backup dir>' ''
  - Add the pre backup script, if needed: ''PREBACKUP="<prebackup-script>" ''
  - Add the post backup script, if needed: ''POSTBACKUP="<postbackup-script>" ''

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

==== Testing ====
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'' 

===== Restore a backup =====
There are several ways to restore a database dump file:

  * Restore a plain SQL file:<code bash>
mysql <dbname> -e "source daily_<dbname>_2019-03-09_05h00m_Saturday.sql;"
</code>
  * Restore a bzip2 compressed database dump file:<code bash>
bzip2 -cd daily_<dbname>_2019-03-09_05h00m_Saturday.sql.bz2 | mysql <dbname>
</code>
  * Restore a gzip compressed database dump file:<code bash>
gzip -cd daily_<dbname>_2019-03-09_05h00m_Saturday.sql.gz | mysql <dbname>
</code>

===== Manually create a backup =====
In case, you have to create a backup manually (without automysqlbackup script), you can do it like this:

  * Using ''gzip'' and backup all databases:<code bash>
mysqldump --all-databases -q | gzip -c > mysql-20100212.sql.gz
</code>
  * Using ''bzip2'' and backup all databases:<code bash>
mysqldump --all-databases -q | bzip2 -c > mysql-20100212.sql.bz2
</code>


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

<code mysql>
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';
</code>

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

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

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

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

The result:

<code mysql>
    *************************** 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)
</code>

<WRAP center round tip 60%>
If you want to get attributes for all hosts, just remove the ''AND host='localhost''' query in the ''WHERE'' clause!
</WRAP>

===== Setup user permissions =====

  * Allow all grants for a user:<code mysql>
mysql> GRANT ALL PRIVILEGES ON `<dbname>`.* TO '<username>'@'localhost' WITH GRANT OPTION;
</code>
  * Allow specified grants for a user:<code mysql>
mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, SELECT, UPDATE ON `<dbname>`.* TO '<username>'@'localhost' WITH GRANT OPTION;
</code>