mysql--高级篇

一、配置文件：/etc/my.cnf

[client] //client配置标记
[mysqld] //server配置开始标记。之前是client的

• character_set_server=utf8 //字符集
• log-bin=/opt/mysql/binlog/mysql-bin //binlog位置
• binlog_format=ROW //binlog格式
• server-id=11

二、权限

• mysql权限表有4个:
  
  1. SELECT * FROM user where mysql.User =’user_abc’;
  2. select * from db where mysql.User =’user_abc’;
  3. select * from mysql.tables_priv;
  4. select * from mysql.columns_priv;

• 权限命令：

  1. show GRANTS for ‘user_abc’ //列出权限，一般是user表,db表的数据
  2. grant all privileges on database.* to user_abc@localhost identified by ‘passwd’;
  3. grant all privileges on . to ‘user_abc’@’%’ identified by ‘passwd’;
  4. grant all privileges on . to ‘user_abc’@’1.1.%.%’ identified by ‘passwd’;
  5. flush privileges; //权限立即生效

• 主从复制/binlog需要以下4个权限：

  1. REPLICATION CLIENT，REPLICATION SLAVE，SUPER，RELOAD
  2. SHOW master STATUS //binlog信息
  3. SHOW slave STATUS //master_host,master_port, master_user, binglog等信息

三、binlog命令

• show binary logs; //binlog文件列表
• show binlog events in ‘mysql-bin.000009’ //binlog详情
• show binlog events in ‘mysql-bin.000009’ from 1012
• mysqladmin -uroot -p123456 flush-logs //强制生产新的binlog文件
• mysqlbinlog查看binlog
  
  1. mysqlbinlog –base64-output=decode-rows -v –start-datetime=’2017-12-01 18:00:00’ –stop-datetime=’2017-12-01 19:00:00’ mysql-bin.000009

四、事务锁&负载

• SELECT * FROM information_schema.innodb_trx ;
• SELECT * FROM information_schema.innodb_locks;
• SELECT * FROM information_schema.innodb_lock_waits;