MySQL进阶
一、MySQL授权
grant all on *.* to 'yunjisuan'@'192.168.200.128' identified by '123123';
授权 所有类型 on 库名.表名 to 用户名@允许远程的IP identified by '密码';
revoke select on *.* from 'yunjisuan'@'192.168.200.128';
撤销授权 查看类型 on 库名.表名 from 用户名@IP地址
MySQL通配符==>_(任意一个字符) %(任意字符)
刷新==>flush privileges;
查看当前登录用户==>select user();
MySQL用户修改密码：
update mysql.user set password=password('123123') where host='192.168.200.127';
password=password('密码')==>调用password函数生成加密密码
查看当前权限==>show grants;
查看其它权限==>show grants for '用户名@IP地址';
二、索引
索引的分类：普通索引、唯一索引、主键、全文索引、单列索引和多列索引
三、事物
事物的四个属性：ACID==>原子性、一致性、隔离性、持久性
触发事物==>begin;
触发回滚==>rollback;
事物结束提交==>commit;
查看MySQL全局变量==>show variables like '%autocommit%';
set autocommit=0(禁止自动提交) set autocommit=1(开启自动提交)
四、MySQL日志管理
包括：错误日志、通用查询日志、二进制日志、慢速查询日志
慢速查询日志：记录所有执行时间超过long_query_time的SQL语句
用于找到执行时间长的查询，加以优化
开启慢查询日志的命令==>log-slow-queries=mysql_slow.log
五、数据库备份
从物理与逻辑的角度可分为：物理备份和逻辑备份
物理备份可分为：①冷备份：在关闭数据库进行的
②热备份：数据库处于运行状态
③温备份：数据库锁定表格(不可写入但可读)的状态下进行的
备份命令：mysqldump(用了没法写数据，不能再主库上操作)
备份格式==>mysqldump -uroot -p123123 yunjisuan > /tmp/yunjisuan$(date +%F).sql
↑
库名(写库名备份的是表)
对数据库进行备份需要加--databases参数:
mysqldump -uroot -p123123 --databases yunjisuan > /tmp/yunjisuan$(date +%F).sql
↑
对yunjisuan这个库进行备份
对所有库进行备份==> --all-databases:
mysqldump -uroot -p123123 --opt --all-databases > /tmp/all$(date +%F).sql
↑ ↑
加速备份 所有库
备份后恢复数据库:
非交互式==>mysql -uroot -p123123 < /备份路径(恢复表需要些库名:mysql -uroot -p123123 yunjisuan < /备份路径)
交互式==>进入mysql==>source /备份路径
mysql增量备份
mysql二进制配置文件==>/etc/my.cnf
52行==>max_binlog_size = 1024000 ==>二进制日志最大1M
log-bin=mysql-bin==>开启二进制日志增量备份的功能
六、MySQL存储引擎
MyISAM特点:①不支持事物
②读写互相阻塞
③表级锁定，数据库在更新时锁定整个表
④可通过key_buffer_size来设置缓存索引，提高访问性能，减少磁盘压力
InnoDB特点:①写支持好，读支持差
②支持事物，功能复杂
③对硬件要求高
④缓存索引和数据占用内存高
七、MySQL主从复制
应用场景：主从同步、主从服务器实现读写分离
原理：MySQL主服务器开启binlog日志，将server id=1开启，开启IO线程，
MySQL从服务器开启中继日志，将server id=3开启(不能与主库一致)，开启IO线程，
从库要验证主库的五个信息，成功后主从服务器通过IO线程将日志数据传递，
从服务器得到主二进制日志写入中继日志中，从服务器开启SQL线程将日志执行。
binlog日志==>log-bin=mysql-bin 中继日志==>relay-log=relay-bin
创建主从同步步骤：
主库：①打开my.cnf配置文件，开启binlog日志，开启server id=1，重启mysql
②grant replication slave on *.* to 'yunjisuan'@'192.168.200.%' identified by '123123';
replication slave==>mysql同步的必须权限，与root类似
③刷新==>flush privileges;
④对主库锁表只读==>flush table with read lock;(手动解锁==>unlock tables;)
⑤对主库做全备，并推送到从库中，使主从数据一致
⑥查看show master status;==>告诉从库验证信息
从库：①打开my.cnf配置文件，开启中继日志(relay-log),server id=3(不能与主库一致)
②输入验证信息:例
CHANGE.MASTER TO MASTER_HOST='192.168.200.127',MASTER_PORT=3306,MASTER_USER='yunjisuan',MASTER_PASSWORD='123123',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=580
③激活主从同步==>start slave; 关闭主从同步==>stop slave;
④查看从库状态==>show slave statusG (有双yes代表成功)
master.into(IO线程记录的数据)==>记录验证信息和二进制文件位置信息
relay-log.info(SQL线程记录数据)
从库永久只读：在my.cnf配置文件[mysql]模块里加入read-only
SQL线程宕了，跳过执行一次==>set global sql_slave_skip_counter=1;