MySQL账户管理和主从同步

 

账户管理
    在生产环境下操作数据库时,绝对不可以使用root账户连接,而是创建特定的账户,授予这个账户特定
    的操作权限,然后连接进行操作,主要的操作就是数据的CRUD(增删改查)
    MySQL账户体系：根据账户所具有的权限的不同,MySQL的账户可以分为以下几种
    　　服务实例级账号:启动了一个mysqld,即为一个数据库实例,如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
   　　 数据库级别账号:对特定数据库执行增删改查的所有操作
    　　数据表级别账号:对特定表执行增删改查等所有操作
    　　字段级别的权限:对某些表的特定字段进行操作
    　　存储程序级别的账号:对存储程序进行增删改查的操作
    账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
    
    注意：
        进行账户操作时,需要使用root账户登录,这个账户拥有最高的实例级权限通常都使用数据库级操作权限
        
授予权限
    需要使用实例级账户登录后操作,以root为例
    
    主要操作包括：
        查看所有用户
            所有用户及权限信息存储在mysql数据库的user表中
                查看user表的结构
                    desc user;
                主要字段说明：
                    Host表示允许访问的主机
                    User表示用户名
                    authentication_string表示密码,为加密后的值
                    
                select host,user,authentication_string from user;
                
        创建账户、授权        
            需要使用实例级账户登录后操作,以root为例
            常用权限主要包括：create、alter、drop、insert、update、delete、select
            如果分配所有权限,可以使用all privileges
            
             创建账户&授权
                grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
                grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';
                
                说明：
                    可以操作python数据库的所有表,方式为:jing_dong.*
                    访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
                    访问主机可以设置成 localhost或具体的ip,表示只允许本机或特定主机访问
                    
                查看用户有哪些权限
                    show grants for laowang@localhost;    
                    
                创建一个laoli的账号,密码为12345678,可以任意电脑进行链接访问,
                并且对jing_dong数据库中的所有表拥有所有权限
                    grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"    
                    
        修改权限
            grant 权限名称 on 数据库 to 账户@主机 with grant option;            
                
        修改密码
            使用root登录,修改mysql数据库的user表
            使用password()函数进行密码加密
            update user set authentication_string=password('新密码') where user='用户名';
            例如：
                update user set authentication_string=password('123') where user='laowang';
            注意修改完成后需要刷新权限

            刷新权限：flush privileges
            
            重启mysql
                    service mysql restart
            如果依然连不上，可能原因：

            1) 网络不通
                通过 ping xxx.xxx.xx.xxx可以发现网络是否正常
            2)查看数据库是否配置了bind_address参数
                本地登录数据库查看my.cnf文件和数据库当前参数show variables like 'bind_address';
                如果设置了bind_address=127.0.0.1 那么只能本地登录
            3)查看数据库是否设置了skip_networking参数
                如果设置了该参数,那么只能本地登录mysql数据库
            4)端口指定是否正确        
                
        删除用户
            语法1：使用root登录
                drop user '用户名'@'主机';
                例：
                　　drop user 'laowang'@'%';
                
            语法2：使用root登录,删除mysql数据库的user表中数据
                delete from user where user='用户名';
                例如：
                delete from user where user='laowang';

                操作结束之后需要刷新权限
                    flush privileges    

MySQL性能的优化
    查询效率慢的原因
        1)没有加索引或者索引失效
            where条件使用如下语句会索引失效：null、！=、<>、or连接、in（非要使用，可用关键字exist替代）和not in、'%abc%'；
            使用参数：num=@num、表达式操作：
            　　where num/2=100、函数操作：wheresubstring（name，1,3）=‘abc’-name；
        2)查询的数据量过大，返回不必要的行和列
            只查询有用的字段，不要用*查询出所有字段。采用多线程多次查询。如果查询条件是某段时间之类的范围条件，可以把时间条件切分，多次查询结果合并。
        3)锁或者死锁
        4)I/O吞吐量小，形成瓶颈效应。
        5)内存不足。
            少造对象，对象只在需要使用时创建，不要在整个上下文传递。及时清理jvm内存。
        6)网络速度慢
        
    一些SQL优化方法
        1)如果索引是复合索引，必须使用该索引的第一个字段作为条件才能保证系统使用该索引，
            否则索引不会被引用，并且应尽可能的让字段顺序与索引顺序一致。
        2)索引并不是越多越好，一个表索引最好不要超过6个。索引固然可以提高select效率，但是也降低了
            insert效率和update效率，因为insert和update会使索引重建，所以怎么建索引需要慎重考虑。
        3)建表的一些优化：
            尽量使用数字型字段，若数据只含有数值信息尽量不要设计成字符型，这会降低查询和连接的性能，并会增加存储开销。因为引擎在处理查询和连接时会
　　　　 　　 逐个比较字符串中每个字符，而对于数字型而言只需比较一次就够了。尽量使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，
        　　可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些。
        4)任何地方都不要使用select * from table，用具体的字段列表代替*，不要返回用不到的任何字段。
        5)尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
        6)并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，
            SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
        7)尽量避免大事务操作，提高系统并发能力。          

MySQL主从同步配置
    主从同步的定义
        主从同步使得数据可以从一个数据库服务器复制到其他服务器上,在复制数据时,一个服务器充当主服务器(master),其余的服务器充当从服务器(slave),
　　 因为复制是异步进行的,所以从服务器不需要一直连接着主服务器,从服务器甚至可以通过拨号断断续续地连接主服务器,通过配置文件,可以指定复制
    所有的数据库,某个数据库，甚至是某个数据库上的某个表

        使用主从同步的好处：
            1)通过增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上向外提供查询功能,可以动态地调整从服务器的数量,从而调整整个数据库的性能
            2)提高数据安全,因为数据已复制到从服务器,从服务器可以终止复制进程,所以,可以在从服务器上备份而不破坏主服务器相应数据
            3)在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能
            
    主从同步的机制    
        1)Mysql服务器之间的主从同步是基于二进制日志机制,主服务器使用二进制日志来记录数据库的变动情况,从服务器通过读取和执行该日志文件来保持和主服务器的数据一致
        2)在使用二进制日志时,主服务器的所有操作都会被记录下来,然后从服务器会接收到该日志的一个副本.
          　　从服务器可以指定执行该日志中的哪一类事件(譬如只插入数据或者只更新数据),默认会执行日志中的 所有语句
        3)每一个从服务器会记录关于二进制日志的信息:文件名和已经处理过的语句,这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分,并且从服务器可以
　　　　　　　　随时连接或者中断和服务器的连接
        4)主服务器和每一个从服务器都必须配置一个唯一的ID号(在my.cnf文件的[mysqld]模块下有一个server-id配置项);另外,每一个从服务器
　　　　　　　　还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址,日志文件名称和该日志里面的位置(这些信息存储在主服务器的数据库里)
         
    配置主从同步的基本步骤
        有很多种配置主从同步的方法,可以总结为如下的步骤：
            1)在主服务器上,必须开启二进制日志机制和配置一个独立的ID
            2)在每一个从服务器上,配置一个唯一的ID,创建一个用来专门复制主服务器数据的账号
            3)在开始复制进程前,在主服务器上记录二进制文件的位置信息
            4)如果在开始复制之前,数据库中已经有数据,就必须先创建一个数据快照(可以使用mysqldump导出数据库,或者直接复制数据文件)
            5)配置从服务器要连接的主服务器的IP地址和登陆授权,二进制日志文件名和位置