User and log management for MySQL database

User management of the MySQL database user and log management database

1.mysql user account management user account [email protected] Username: Account name host: Which client host requests this account can create connection threads, which can be IP, hostname, or network. %: Any character of any length; _: any single character; 1) create user ' User_        Name ' @ ' source address ' [identified by [password] ' password ']; Help create user Create user user_specification [, user_specification] ... user_specification:us            ER [identified by [password] ' password ' | identified with Auth_plugin [as ' auth_string ']] 2) Set password 1 "set Set Password=password (' Password '); # #修改当前登录用的密码 Set password for ' user_name ' @ ' Source address ' =password (' password '); # #设置其他用户的密码 Help Set password set password [for User]={password (' cleartext password ') | old_passw Ord (' cleartext password ') | ' Encrypted password ' 2 ' Update update library. Table Set Password=password (") where user= ' user_name ' and hos        t= ' host_addr '; 3 "mysqladmin-uuser_name-hhost_addr-p password ' new_password' 3) Delete user drop ' user_name ' @ ' Source address ' ... help drop drop user user [, user] ... 4) Rename and modify host rename user ' User_name_old ' @ ' host_old ' to ' user_name_new ' @ ' host_new ';        # #可以只是修改用户名或只是修改主机.        Help Rename user rename user old_user to New_user [, Old_user to New_user] ...                5) MySQL Reload authorization form flush Privileges 2. Workaround method for the forgotten root password: 1) Close MYSQLD Services service mysqld stop                                    Shutting down MySQL .... [OK] 2) Skip Grant table authorization, enter Safe mode, use the--skip-grant-tables option, and run Mysqld_safe in the background--skip-grant-tables&            Amp Jobs [1]+ Running mysqld_safe--skip-grant-tables & 3) Enter Safe mode to change password Mys            QL MariaDB [(none)]> use MySQL;            MariaDB [mysql]> Update user set Password=password (' xm1234 ') where user= ' root ';            MariaDB [mysql]> flush Privileges; MariaDB [mysql]> exit killall-u MySQL 4) Restart the MySQL service and try the service mysqld start mysql-uroot-p Method Two: vim/etc/mysql/my.cnf skip_grant_tables service mysqld restart MySQL Mariad        B [(none)]> use MySQL;        MariaDB [mysql]> Update user set Password=password (' xm1234 ') where user= ' root ';        MariaDB [mysql]> flush Privileges;        MariaDB [mysql]> exit vim/etc/mysql/my.cnf skip_grant_tables Remove service mysqld restart            Mysql-uroot-p3. User rights settings MySQL permissions category library level, table level, field level, management class, program class management class: Create user Reload  Lock tables replication client, replication slave shutdown file Show                Databases process Super Program class: Create,alter,drop,execute function Procedure trigger Library and table level: Create,alter,drop,shOw,grant Index View field level: metadata: DB, host, user Ta Bles_priv, Column_priv, Procs_priv, proxies_priv All rights: All, all privileges show grants FO        R user name;                Grant Select on database name. * to user name;　　　　# #给用户的数据库的所有权限 Revoke select on database name. * to user name;        # #grant的反操作, remove permissions; 1 set user rights (the new user is not present) grant permission list on Library name. Table name to ' user name ' @ ' source address ' [identified by ' Password '];        Grant Select on database name. * to user name;        Grant all on the * * to user name; Flush privileges; # #刷新权限 Help Grand Grant Priv_type [(column_list)] [, Priv_type [(column_list]]] ... on [o Bject_type] Priv_level to User_specification [, user_specification] ...                [Require {none | ssl_option [[and] ssl_option] ...}]            [With With_option ...]         Grant Proxy on User_specification to User_specification [, user_specification] ...        [WITH GRANT option] object_type:table | function | Procedure Priv_level: * | *.* | db_name.* | Db_name.tbl_name | Tbl_name |  Db_name.routine_name User_specification:user [identified by [password] ' password ' | Identified with Auth_plugin [as ' auth_string '] ssl_option:ssl | X509 | Cipher ' cipher ' | Issuer ' issuer ' | Subject ' subject ' with_option:grant option | Max_queries_per_hour Count | Max_updates_per_hour Count | Max_connections_per_hour Count |            Max_user_connections Count Example: Create user ' jeffrey ' @ ' localhost ' identified by ' mypass ';            Grant all on db1.* to ' Jeffrey ' @ ' localhost ';            Grant SELECT on Db2.invoice to ' Jeffrey ' @ ' localhost '; Grant usage on *. Jeffrey ' @ ' localhost ' with Max_querieS_per_hour 90;            Grant all on * * to ' root ' @ '% ' indentified by ' xxxxx ';            Grant SELECT on imployee_salary.* to ' amber ' @ ' Localhsot ' identified by ' XXXX '; Flush privileges;# #刷新权限 2 "View user rights show grants;        # #查看当前登录用户的授权信息.        Show grants for ' username ' @ ' host address ';            Help show grants show grants [for user] show grants;            Show grants for Current_User;                Show grants for Current_User ();        3 REVOKE user Rights revoke permissions list on library name. Table name from ' username ' @ ' source address ' revoke select on ' database name '. * from ' username ' @ ' source address ';        Revoke all on * * from ' username ' @ ' source address '; Flush privileges; # #刷新权限 Help Revoke Revoke priv_type [(column_list)] [Priv_type [(column_list]]] ... on [Object_type] priv_level from user [, user] ... revoke all privileges, grant option from user [    , user] ... revoke proxy on user from user [, user] ... 4 List of common permissions

Log management of the database

MySQL log: Error log: Log_error,log_warnings General query log: General_log binary log: Binlog slow query log: Log_slow_queries relay log: Relay_log        Transaction log: Innodb_log depending on the version, the following directory file directories are also different 1) the error log error log itself is defined by the content itself.                Error logging information: 1 mysqld The information that is output during startup and shutdown.                Not necessarily error messages, such as how MySQL is going to initialize the storage engine is logged in the error log, and so on 2 "mysqld" error messages generated during the run.            For example, the sock file cannot be found, unable to load the MySQL database data file, if you forget to initialize MySQL or the data dir path cannot be found, or the permissions are not correct, etc., will be recorded here.                3 The information generated by the event Scheduler (Scheduler) runtime.            Once the MySQL scheduler starts a scheduled task, it also logs the relevant information in the error log. 4 "In the master-slave replication architecture, the log generated from the server copy thread startup, modify the master profile my.cnf [mysqld] log-error=mysql_error.log (absolute path or if the direct file name is stored to Data Directory) Log_warnings={on|off|2}: Warning information is not logged log_warnings indicates whether the warning information is logged in the error log, 1 and 0 that is on and off for logging and not logging, 2        Represents the connection information for a failed deny.            View the configuration of the error log on the MySQL server mysql> show global variables like '%log% '; |            Log_error |/mydata/data/localhost.err | | Log_warnings |                1 |        2) General query log MySQL All query statements will be logged.        By default, this record is turned off, usually for debugging purposes, and it usually logs large amounts of data to occupy disk space.        Storage location: File, table (table,mysql.general_log) default, stored in the data directory.            Modify the master configuration file MY.CNF.            [Mysqld] General_log={on|off} general_log_file=mysql_general.log (absolute path or if direct file name is stored in data directory) Log_output={file|table|file,table|none}: Log Output Type View the configuration of the query log on the MySQL server MariaDB [(none)]> show global Vari            Ables where variable_name like '%general_log% ' or variable_name= ' log_output '; +------------------+---------------+            | variable_name |            Value | +------------------+---------------+            | General_log |            OFF | | General_log_file |            Localhost.log | | Log_output |            FILE | +------------------+---------------+ 3) binary log (very important) 1 is used to record statements (statement) or altered results that cause data changes or the potential for data changes ( Row, or they may be mixed.        2 contains all the updated data or all statements that have potentially updated data, records changes to the data, and event events and location position for data changes.        3 "The main purpose is to restore the database at the most possible time, by default.            4. Modify the master configuration file (my.cnf).            Log_bin=/path/to/bin_log_file: This is a read-only variable that indicates the directory location where the log is stored, cannot be written on or off here, and if the path is not specified, it will be stored in the data directory.            max_binlog_size=1073741824: Sets the maximum size of a single binary file, in bytes, that will automatically scroll if it exceeds this value. sync_binlog={1|0|                N}: Indicates whether the in-memory binary logs are synchronized to memory (Binlog_cache) immediately after each transaction commit. 1 means commit immediately, 0 not commit, n can be any value, mean every n times, different values correspond to different performance, 0 and 1 performance difference can be as much as 5 times times.            The operation to write to the disk is to use the Fdatasync () function.         binlog_format={statement|row|mixed}: Binlog log stored in format expire_logs_days=n: Number of days for binary log                5 "Function: Replay 6" related instructions binlog_format={statement|row|mixed} statement: statement, record the multi-database to make changes to the statement;                 Row: Rows, which record the data rows affected by the statements modified by the database, and the modifications of those rows; mixed: mixed, mixing the above two modes; Sql_log_bin={on|off} Controls whether the write action statement in a session is logged in the log file as a session-level variable and can only be set using the SET command in the current session SessioN.sql_log_bin={on|off} cannot be configured in the My.cnf file 7 "You can view the binary log file with the Mysqlbinlog command.                 MYSQLBINLOG:YYYY-MM-DD Hh:mm:ss--start-datetime=--stop-datetime= -j,--start-position=#--stop-position=#--user,--host,--password 8 "in             View binary View binary log file list in MySQL: mysql> show master|binary logs; To view the binary log files currently in use: Mysql> show master status; viewing events in binary log files: mysql> Show bin Log events [in ' Log_name '] [from POS] [limit [offset,] row_count] View binary log parameter configuration MariaDB [(None)]& Gt                Show global variables where variable_name like '%log_bin% ' or variable_name like '%binlog% '; +-----------------------------------------+----------------------+                | variable_name |                Value | +-----------------------------------------+----------------------+                | binlog_annotate_row_events |                OFF | | Binlog_cache_size |                32768 | | Binlog_checksum |                NONE | | Binlog_direct_non_transactional_updates |                OFF | | Binlog_format |                STATEMENT | | binlog_optimize_thread_scheduling |                On | | Binlog_stmt_cache_size |                32768 | | Innodb_locks_unsafe_for_binlog |                OFF | | Log_bin |                On | | log_bin_trust_function_creators |                OFF | | Max_binlog_cache_size |                18446744073709547520 | | Max_binlog_size | 1073741824 | | Max_binlog_stmt_cache_size |                18446744073709547520 | | Sql_log_bin |                On | | Sync_binlog |                0 | +-----------------------------------------+----------------------+ log scrolling, scroll when a single binary reaches the specified size, scroll automatically after restarting MySQL, or manually roll            Move. Manual scrolling binary log MariaDB [(none)]>flush logs 9 "Clears the binary log clears all logs (no master-slave replication relationship) MySQL            > Reset Master;            Clears all logs before the specified log mysql> purge master logs to ' log ';            Clears all logs before a point in time mysql> purge master logs before ' year-month-day time: minutes: seconds ';            Clears all logs from N days ago mysql> purge master logs before Current_date-interval day;                    Because of the importance of the binary log, delete is done only if you are sure that the binary that will be deleted is no longer required, or if you have already archived a backup of the binary log file, or if you have already made a database backup, and do not use the RM command to delete it. 10 "Binary Log Event format: # at 553 #160831 9:56:08 Server ID 1 end_log_pos 624 query thread_id=2 exec_time=0 error_code=0            Set timestamp=1472608568/*!*/;            Begin/*!*/; Start of event: # at 553 datetime: #160831 9:56:08 The end of the server Id:server ID 1 event where the event occurred: End_log_pos 62            4 Event Type: The time stamp of the id:thread_id=2 statement of the thread that the server is executing on when the query event occurred, and the difference in writing it to the binary log file: exec_time=0            Error code: error_code=0 Set the timestamp when the event occurred: set timestamp=1472608568/*!*/;        Event content: Begin 4) The slow query log records all SQL statements that have been executed for more than long_query_time seconds, and can be used to find queries with long execution times for optimization.        Default is not turned on, the priority is higher than the query log, the default is more than 10 seconds to be logged. Storage location: File, table (table,mysql.slog_log) Modify Master Profile/etc/my.cnf, add "Long_query_time" and "log-slow-queries= file path name" under [Mysqld],            Restart the MYSQLD service. Log_slow_queries={on|off}: Whether to turn on slow query log (before 5.5) Slow_query_log={on|off}: whether to open the slow query log (and no difference above, 5.6 later) Slow_que Ry_log_file=xxxx-slom.log: Slow query log storage location, default is "hostname-slow.log". Relative path, the defaultThink of the data directory.            Log_output={file|table|file,table|none}: Indicates how the log is stored log_query_time=n: The query that represents how long is considered slow, the default is 10 seconds.            Log_queries_not_using_indexes={on|off}: Indicates that the running SQL statement is not used to the index and is also logged as a slow query statement.            log_throttle_queries_not_using_indexes={on|off}:5.6.5 introduced, do not use the index of the query statement whether Hu is used as a slow query statement recorded in the slow query log. Log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp _table_on_disk log_slow_rate_limit log_slow_verbosity View the parameters of the slow query on the MySQL server MariaDB [ (none)] >show Global variables where variable_name like '%slow_query% ' or variable_name= ' log_output ' or variable_name= ' long_                Query_time ' or variable_name like ' log_slow% '; MySQL comes with a statistical analysis of the slow query log: Mysqldumpslow 5) Relay log: The relay log is used for slave servers in the master-detail replication schema, from the server's slave process to obtain the contents of the binary log from the primary server and write to the relay log, and then            The statements in the relay log are read and executed by the I/O process. 6) transaction (Redo) log: 1 transactional storage Engine InnoDB log files for transaction characteristics, redo log and undo log 2 for performance and recovery reasons, MySQL server does not immediately executeTransactions, instead of logging transactions in the log, which translates random I/O into sequential I/O, which improves I/O performance. 3 "Things log by default there will be two files, the names of which are IB_LOGFILE0 and Ib_logfile1, respectively.        When one is full, MySQL writes the transaction log to another log file (empty the original content first).        4 "When MySQL resumes from a crash, it reads the transaction log and writes the COMMIT transaction to the database without a COMMIT transaction rollback. 5 "When the thing is submitted, InnoDB will buffer to the file synchronization, as long as the commit is synchronized immediately, but also does not guarantee that each statement is synchronized, so performance will not have a particularly large impact 6" MySQL shows the relevant parameters of the transaction log MariaDB [(none)]&G T Show global variables where variable_name like '%innodb_log% ' or variable_name like ' innodb_%_log% ' or variable_name like            ' innodb_locks% '; +-------------------------------------------+---------+            | variable_name |            Value | +-------------------------------------------+---------+            | Innodb_flush_log_at_trx_commit |            1 | | Innodb_locks_unsafe_for_binlog |            OFF | | Innodb_log_block_size |            512 | | Innodb_log_buffer_size |            8388608 | |      Innodb_log_file_size                |            5242880 | | Innodb_log_files_in_group |            2 | |            Innodb_log_group_home_dir |./| | innodb_mirrored_log_groups |            1 | | Innodb_recovery_update_relay_log |            OFF | | Innodb_use_global_flush_log_at_trx_commit |            On | +-------------------------------------------+---------+ if Innodb_flush_log_at_trx_commit is set to 0,log bu                The Ffer will be written to log file once per second, and the flush (swipe to disk) operation of log file is performed concurrently.             In this mode, the operation to write to the disk is not actively triggered when the transaction commits.             If Innodb_flush_log_at_trx_commit is set to 1, MySQL writes log buffer data to log file each time the transaction commits, and flush (swipe to disk).                If Innodb_flush_log_at_trx_commit is set to 2, MySQL writes log buffer data to log file each time the transaction commits, but the flush (swipe to disk) operation does not take place at the same time.                            In this mode, MySQL executes the flush (swipe to disk) operation once per second. Define the size of the memory space, in case it is written in buffer, if the process crashes, will also lose things, so to avoid this situation, once the things submitted, then need to synchronize to disk immediately, instead of intermittent synchronization, so there are parameters: |innodb_log_buffer_size |8388608 |             The unit size of each log is 5MB, if some big data, you need to turn it up, or restore it will be slower, but too big will cause recovery is slow |innodb_log_file_size |5242880            |            Provide 2 files in each group, mentioned above |innodb_log_files_in_group |            Define the location of the thing log group, generally there will be 2 logs, a full after the file will be re-built (to the polling function, after the full write will be synchronized to the disk and emptied). In general, the log file size is fixed, all MySQL started log space is immediately allocated on the disk, because their primary function is to convert random I/O to sequential I/O, the default size is 5MB per file, the path of the clear description of the things log is saved in the./representation under current path | Innodb_log_group_home_dir |.            /               | The same log file mirrors the log group, which, of course, needs to be stored on a different disk |innodb_mirrored_log_groups |

User and log management for MySQL database