mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
mysql [2019/03/09 09:32] – [Get all attributes of a user] adminmysql [2019/03/28 13:50] (current) – [Manually create a backup] dani
Line 8: Line 8:
  
   - Get the user and password to access the MySQL database. This is usually the user ''root''!   - Get the user and password to access the MySQL database. This is usually the user ''root''!
-  - Check for the database data directory: ''mysql -e "show variables;"|grep datadir'' +  - Check for the database data directory:<code bash>mysql -e "show variables;" | grep '^datadir'</code> 
-  - 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! +  - 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: ''du -sh $(mysql -e "show variables;"|grep datadir|awk '{print $NF}')'' +  - Amount of data:<code bash>du -sh $(mysql -e "show variables;" | awk '/^datadir/ {print $NF}')</code> 
-  - Socket: ''mysql -e "show variables;"|grep socket'' +  - Socket:<code bash>mysql -e "show variables;"|grep '^socket'</code> 
-  - Network port: ''mysql -e "show variables;"|grep '^port'''+  - Network port:<code bash>mysql -e "show variables;"|grep '^port'</code>
  
 ==== Setup automysqlbackup configuration file ==== ==== Setup automysqlbackup configuration file ====
 Now, you are ready to edit the configuration file of automysqlbackup: Now, you are ready to edit the configuration file of automysqlbackup:
  
-  - Add user: ''CONFIG_mysql_dump_username='root''' +  - Add user: ''CONFIG_mysql_dump_username='root' '' 
-  - Add user’s password: ''CONFIG_mysql_dump_password='<password>''' +  - Add user’s password: ''CONFIG_mysql_dump_password='<password>' '' 
-  - Add the backup directory (usually a separate filesystem: ''CONFIG_backup_dir='<backup dir>''' +  - 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 pre backup script, if needed: ''PREBACKUP="<prebackup-script>" '' 
-  - Add the post backup script, if needed: ''POSTBACKUP="<postbackup-script>"''+  - Add the post backup script, if needed: ''POSTBACKUP="<postbackup-script>" ''
  
 The configuration file is expected here: ''/etc/automysqlbackup/automysqlbackup.conf'' The configuration file is expected here: ''/etc/automysqlbackup/automysqlbackup.conf''
Line 42: Line 42:
 gzip -cd daily_<dbname>_2019-03-09_05h00m_Saturday.sql.gz | mysql <dbname> gzip -cd daily_<dbname>_2019-03-09_05h00m_Saturday.sql.gz | mysql <dbname>
 </code> </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 ===== ===== 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''. 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> <code mysql>
-mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='root' AND Host='::1';   +mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='<username>' AND Host='::1';   
-mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='root' AND Host='127.0.0.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='root' AND Host='localhost';+mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='<username>' AND Host='localhost';
 </code> </code>
  
Line 54: Line 66:
  
 <code mysql> <code mysql>
-mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='root+mysql> UPDATE mysql.user SET Password=PASSWORD('<password>') WHERE User='<username>'
 </code> </code>
  
Line 61: Line 73:
  
 <code mysql> <code mysql>
-mysql> SELECT * FROM user WHERE user='<username>' \G+mysql> SELECT * FROM mysql.user WHERE user='<username>' AND host='localhost' \G
 </code> </code>
  
Line 69: Line 81:
     *************************** 1. row ***************************     *************************** 1. row ***************************
                       Host: localhost                       Host: localhost
-                      User: user+                      User: <username>
                   Password: *D115A249596ECD669091C1C6535DE65DE8427472                   Password: *D115A249596ECD669091C1C6535DE65DE8427472
                Select_priv: Y                Select_priv: Y
Line 110: Line 122:
                     plugin:                     plugin:
      authentication_string:      authentication_string:
-    *************************** 2. row *************************** +    1 row in set (0.01 sec)
-                      Host: 127.0.0.1 +
-                      User: user +
-                  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: +
-    *************************** 3. row *************************** +
-                      Host: ::1 +
-                      User: user +
-                  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: +
-    3 rows in set (0.01 sec)+
 </code> </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 ===== ===== Setup user permissions =====
  
   * Allow all grants for a user:<code mysql>   * Allow all grants for a user:<code mysql>
-mysql> GRANT ALL PRIVILEGES ON `<username>`.* TO '<username>'@'localhost' WITH GRANT OPTION;+mysql> GRANT ALL PRIVILEGES ON `<dbname>`.* TO '<username>'@'localhost' WITH GRANT OPTION;
 </code> </code>
   * Allow specified grants for a user:<code mysql>   * Allow specified grants for a user:<code mysql>
-mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, SELECT, UPDATE ON `<username>`.* TO '<username>'@'localhost' WITH GRANT OPTION;+mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, SELECT, UPDATE ON `<dbname>`.* TO '<username>'@'localhost' WITH GRANT OPTION;
 </code> </code>
  
  • mysql.1552120326.txt.gz
  • Last modified: 2019/03/09 09:32
  • by admin