mysql -vvv_MySQL

MySQL编码

一、关闭、启动

二、MySQL版本

$ mysql -V

$ mysql--help | grepDistrib

mysql>status;

mysql> select version();

三、配置文件位置

mysqld --help --verbose|grep -A1 -B1 cnf

四、用户及权限管理

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';create user 'gaoying'@'localhost' identified by 'gaoying';

删除用户

DROP USER 'username'@'host';

授权

GRANT privileges ON databasename.tablename TO 'username'@'host'

grant all privileges on *.* to 'gaoying'@'localhost';

取消权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';revoke insert on *.* from 'gaoying'@'localhost';

flushprivileges;

查看mysql中存在的用户

select user,host from mysql.user;

查看用户的授权

show grants for 'root'@'localhost';

五、导入、导出

导入打开终端输入：(前提是已经配置过MySQL环境变量)

mysql -u root -pcreate databasename;usename;

source 『将.sql文件直接拖拽至终端，自动补全其文件目录』

导出：

mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql

只导出表结构不导出数据，--no-data

mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

跨服务器导出导入数据

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错

mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test

加上-C参数可以启用压缩传递。

压缩备份

mysqldump -uroot -p -P3306 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases abc 2>/abc.err |gzip >/abc.sql.gz

还原

gunzip -c abc.sql.gz |mysql -uroot -p -vvv -P3306 --default-character-set=utf8 abc 1> abc.log 2>abc.err

备注： 线上环境导出和导入数据可以参考“14.压缩备份”的导出和导入参数。

六、日志(错误日志、查询日志、慢查询日志、事务日志、二进制日志)

SQL查询日志变量

SHOW GLOBAL VARIABLES LIKE '%log%';

+-----------------------------------------+---------------------------------------------------------+

| Variable_name | Value |

+-----------------------------------------+---------------------------------------------------------+

| back_log | 80 |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | ABORT_SERVER |

| binlog_format | ROW |

| binlog_group_commit_sync_delay | 0 |

| binlog_group_commit_sync_no_delay_count | 0 |

| binlog_gtid_simple_recovery | ON |

| binlog_max_flush_queue_time | 0 |

| binlog_order_commits | ON |

| binlog_row_image | FULL |

| binlog_rows_query_log_events | OFF |

| binlog_stmt_cache_size | 32768 |

| expire_logs_days | 0 |

| general_log | OFF |定义查询日志是否开启| general_log_file | /usr/local/mysql/data/gaoyingdeMacBook-Pro.log |定义查询日志的文件地址名称| innodb_api_enable_binlog | OFF |

| innodb_flush_log_at_timeout | 1 |在事务提交时innodb是否同步日志从缓冲到文件中1表示事务以提交就同步不提交每隔一秒同步一次，性能会很差造成大量的磁盘I/O；定义为2表示只有在事务提交时才会同步但是可能会丢失整个事务| innodb_flush_log_at_trx_commit | 1 |

| innodb_locks_unsafe_for_binlog | OFF |

| innodb_log_buffer_size | 16777216 |

| innodb_log_checksums | ON |

| innodb_log_compressed_pages | ON |

| innodb_log_file_size | 50331648 |

| innodb_log_files_in_group | 2 |至少有两个| innodb_log_group_home_dir | ./ |定义innodb事务日志组的位置| innodb_log_write_ahead_size | 8192 |

| innodb_max_undo_log_size | 1073741824 |

| innodb_online_alter_log_max_size | 134217728 |

| innodb_undo_log_truncate | OFF |

| innodb_undo_logs | 128 |

| log_bin | ON |

| log_bin_basename | /usr/local/mysql/data/mysql-bin |

| log_bin_index | /usr/local/mysql/data/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_builtin_as_identified_by_password | OFF |

| log_error | /usr/local/mysql/data/mysqld.local.err |定义错误日志| log_error_verbosity | 3 |

| log_output | FILE |日志的输出的位置| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| log_statements_unsafe_for_binlog | ON |

| log_syslog | OFF |

| log_syslog_facility | daemon |

| log_syslog_include_pid | ON |

| log_syslog_tag | |

| log_throttle_queries_not_using_indexes | 0 |

| log_timestamps | UTC |

| log_warnings | 2 |是否把警告信息写入错误日志中| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| max_relay_log_size | 0 |

| relay_log | |

| relay_log_basename | |

| relay_log_index | |

| relay_log_info_file | relay-log.info |

| relay_log_info_repository | FILE |

| relay_log_purge | ON |

| relay_log_recovery | OFF |

| relay_log_space_limit | 0 |

| slow_query_log | OFF |定义慢查询日志的| slow_query_log_file | /usr/local/mysql/data/gaoyingdeMacBook-Pro-slow.log |输出方式为file(文件)时定义慢查询日志的位置| sql_log_off | OFF |

| sync_binlog | 1 |

| sync_relay_log | 10000 |

| sync_relay_log_info | 10000 |

+-----------------------------------------+---------------------------------------------------------+

http://blog.51cto.com/pangge/1319304

七、SQL_MOD

show variables like "sql_mode";

查看MySQL配置文件加载顺序

mysqld--help --verbose|grep -A1 -B1 cnf

查看当前mysqld启动时的加载文件ps ax | grep '[m]ysqld'