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'