基于mysql-proxy实现mysql数据库的读写分离

在我们的业务系统中,随着业务的增长,数据库的压力也随之产生,特别对于查询多于写入的业务环境下,迫切需要对读操作和写操作分离开来,这篇博文,主要描述mysql官方提供的mysql-proxy软件来实现读写分离[其实主要是通过lua脚本实现的],再结合mysql的主从复制,可以提升数据库的并发负载能力。但其稳定性较差,不建议使用,如果公司可以对lua脚本的读写分离重新定制开发,倒可以尝试,从途牛的招聘来看,途牛可能用的这一套,仅是推测。

附上简要原理图:

MY-PROXY

下面描述一下实验环境:

mysql-proxy:192.168.1.104

mysql1:192.168.1.104(master — w)

mysql2:192.168.1.102(slave — r)

mysql3:192.168.1.100(slave — r)

为能看到读写分离的效果,我这里就不配置数据库之间的主从关系,仅做mysql-proxy的配置[这里采用编译安装]

1.编译安装需要安装如下几个软件:[以下几个软件我都解压放在/tmp下]

glib,libenent,lua,pkg-config,mysql-proxy

软件共享下载地址:http://www.kuaipan.cn/file/id_119710994921422889.htm

2.安装pkg-config-0.23.tar.gz

  1. [root@centos tmp]# yum -y install gcc-c++.x86_64  
  2. [root@centos tmp]# tar -xf pkg-config-0.23.tar.gz  
  3. [root@centos tmp]# cd pkg-config-0.23  
  4. [root@centos pkg-config-0.23]# ./configure  
  5. [root@centos pkg-config-0.23]# make  
  6. [root@centos pkg-config-0.23]# make install  
  7. # 确保PKG_CONFIG_PATH环境变量包含了相关的pkg-config配置文件路径:  
  8. [root@centos pkg-config-0.23]# export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/local/lib/pkgconfig  

3.安装libevent-1.4.11-stable.tar.gz

  1. [root@centos tmp]# tar -xf libevent-1.4.11-stable.tar.gz  
  2. [root@centos tmp]# cd libevent-1.4.11-stable  
  3. [root@centos libevent-1.4.11-stable]# ./configure  
  4. [root@centos libevent-1.4.11-stable]#make  
  5. [root@centos libevent-1.4.11-stable]#make install  

4.安装glib-2.20.0.tar.bz2

  1. [root@centos tmp]# tar -xf glib-2.20.0.tar.bz2  
  2. [root@centos tmp]# cd glib-2.20.0  
  3. [root@centos glib-2.20.0]# ./configure  
  4. [root@centos glib-2.20.0]#make  
  5. [root@centos glib-2.20.0]#make install  

5.安装lua-5.1.4.tar.gz

  1. [root@centos lua-5.1.4]# yum -y install readline-devel.x86_64  
  2. [root@centos tmp]# tar -xf lua-5.1.4.tar.gz  
  3. [root@centos tmp]#cd lua-5.1.4  
  4. # 如果你的服务器是64位的,这时要调整一下Makefile:vi src/Makefile,在CFLAGS里加上-fPIC,否则会出错  
  5. # 接下来不用执行常见的configure,直接make,如下:  
  6. [root@centos lua-5.1.4]# make linux  
  7. [root@centos lua-5.1.4]# make install  
  8. # 复制相应文件到指定目录  
  9. [root@centos lua-5.1.4]# cp etc/lua.pc /usr/local/lib/pkgconfig/lua5.1.pc  

6.安装mysql,我这里的mysql使用rpm安装mysql-devel[我这里已经安装好了,这一步我省略了]

  1. 注:编译安装完成后,要声明PATH变量,这样可以保证系统能找到mysql_config,后面编译mysql-proxy会用到它  
  2. export PATH=$PATH:/usr/local/mysql/bin  
  3. 同时,还要保证系统能找到mysql库文件:  
  4. vi /etc/ld.so.conf  
  5. 加入/usr/local/mysql/lib目录  
  6. 执行:/sbin/ldconfig /etc/ld.so.conf  

7.安装mysql-proxy-0.8.3.tar.gz

  1. [root@centos tmp]# tar -xf mysql-proxy-0.8.3.tar.gz  
  2. [root@centos tmp]# cd mysql-proxy-0.8.3  
  3. [root@centos mysql-proxy-0.8.3]# ./configure  
  4. [root@centos mysql-proxy-0.8.3]# make  
  5. [root@centos mysql-proxy-0.8.3]# make install  
  6. # 创建相应目录  
  7. [root@centos mysql-proxy-0.8.3]# mkdir -p /usr/local/mysql-proxy/share/doc/mysql-proxy  
  8. # 复制读写分离脚本 和 管理接口脚本  
  9. [root@centos mysql-proxy-0.8.3]#cp /tmp/mysql-proxy-0.8.3/lib/rw-splitting.lua /usr/local/mysql-proxy/share/doc/mysql-proxy/  
  10. [root@centos mysql-proxy-0.8.3]#cp /tmp/mysql-proxy-0.8.3/lib/admin.lua /usr/local/mysql-proxy/share/doc/mysql-proxy/  
  11. # 手动启动mysql-proxy测试  
  12. [root@centos ~]# mysql-proxy –daemon –log-level=debug –log-file=/var/log/mysql-proxy.log \  
  13. > –plugins=”proxy” –proxy-backend-addresses=”192.168.1.104:3306″ \  
  14. > –proxy-read-only-backend-addresses=”192.168.1.102:3306″ \  
  15. > –proxy-read-only-backend-addresses=”192.168.1.102:3307″ \  
  16. > –proxy-lua-script=”/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua” \  
  17. > –plugins=admin \  
  18. > –admin-username=”admin” \  
  19. > –admin-password=”admin” \  
  20. > –admin-lua-script=”/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua”  
  21.   
  22. #/* –daemon 采用daemon方式启动  
  23. #/* –proxy-backend-addresses=** 指定读写都可以的mysql数据库的IP地址和端口  
  24. #/* –proxy-read-only-backend-addresses=**指定只读mysql数据库的IP地址和端口#/* –proxy-lua-script=** 指定读写分离脚本的位置  
  25. #/* –admin-username=** 指定mysql-proxy管理的用户  
  26. #/* –admin-password=** 指定mysql-proxy管理用户的密码  
  27. #/* –admin-lua-script=** 指定mysql-proxy管理接口lua脚本的位置  
  28. #/* –log-level=debug 定义log日志级别,由高到低分别有(error|warning|info|message|debug)  
  29. #/* –log-file=** 定义log日志文件路径  
  30. #/* –admin-address=:4041 指定mysql proxy的管理端口,在这里,表示本机的4041端口[可以省略]  
  31. #/* –proxy-address=:3307 指定mysql proxy的监听端口(默认4040),也可以用 127.0.0.1:3307表示[可以省略],此接口是提供给应用访问数据库的地址和端口  

8.启动数据库[记的关闭防火墙,不然会有问题,也可以添加防火墙策略]

  1. [root@client102 ~]# mysqld_multi start  
  2. [root@client102 ~]# mysqld_multi report  
  3. Reporting MySQL servers  
  4. MySQL server from group: mysqld3306 is running  
  5. MySQL server from group: mysqld3307 is running  
  6. # 登陆数据库 授予mysql-proxy服务器的访问  
  7. [root@client102 ~]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p  
  8. mysql> GRANT all privileges ON *.* TO ‘kongzhong’@’192.168.1.104’ IDENTIFIED BY ‘kongzhong’;  
  9. mysql> flush privileges;  
  10. [root@client102 ~]# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
  11. mysql> GRANT all privileges ON *.* TO ‘kongzhong’@’192.168.1.104’ IDENTIFIED BY ‘kongzhong’;  
  12. mysql> flush privileges;  
  13. [root@centos lib]# /etc/init.d/mysqld start  
  14. [root@centos lib]# mysql -u root -p  
  15. mysql> GRANT all privileges ON *.* TO ‘kongzhong’@’192.168.1.104’ IDENTIFIED BY ‘kongzhong’;  
  16. mysql> flush privileges;  

9.登陆测试

  1. # 这里是mysql-proxy服务器所在的iP地址和提供给外面的访问端口[使用任何一台提供mysql登陆功能的机器,记得关闭防火墙]  
  2. [root@client102 ~]# mysql -ukongzhong -p -h192.168.1.104 –port=4040  
  3. # 正常登陆基本上就证明mysql-proxy已经配置成功  
  4. # 测试的话,使用下面的语句,但需要反复测试,没有压力是没法测出读写分离效果的  
  5. [root@client102 ~]# mysql -ukongzhong -p -h192.168.1.104 –port=4040 -e “select * from mysql.user”  
  6. [root@client102 ~]# mysql -ukongzhong -p -h192.168.1.104 –port=4040 -e “create database kongzhong”  
  7. # 管理接口[此接口也仅能执行这一条命令,查看读写分离状态]  
  8. [root@client102 ~]# mysql -h192.168.1.104 –port=4041 -uadmin -p  
  9. mysql> select * from backends;  
  10. +————-+——————–+———+——+——+——————-+  
  11. | backend_ndx | address | state | type | uuid | connected_clients |  
  12. +————-+——————–+———+——+——+——————-+  
  13. | 1 | 192.168.1.104:3306 | up | rw | NULL | 0 |  
  14. | 2 | 192.168.1.102:3306 | unknown | ro | NULL | 0 |  
  15. | 3 | 192.168.1.102:3307 | unknown | ro | NULL | 0 |  
  16. +————-+——————–+———+——+——+——————-+  
  17. 注意: 在测试读写分离的时候,我们可以分布测试,比如先测试读的语句,查看读写状态是否UP ,然后在测试写的语句,反反复复多测试几次才能看出效果,本人在测试的时候也出现过读的状态不能up 解决办法是 多测试即便就出来了。  
  18. # 因为我们现在还没有写启动脚本,所以只能手动杀掉进程  
  19. [root@centos mysql-proxy]# killall mysql-proxy  

10.配置启动脚本和启动配置文件

  1. # mysql-proxy启动脚本配置  
  2. [root@centos run]# vim /etc/init.d/mysql-proxy  
  3. # 脚本内容如下  
  4. #!/bin/sh  
  5. # Source function library.  
  6. . /etc/rc.d/init.d/functions  
  7. # PROXY_PATH 这里需要指向你mysql-proxy命令所在的路径  
  8. PROXY_PATH=/usr/local/bin  
  9. prog=”mysql-proxy”  
  10. # Source networking configuration.  
  11. . /etc/sysconfig/network  
  12. # Check that networking is up.  
  13. [ ${NETWORKING} = “no” ] && exit 0  
  14. # Set default mysql-proxy configuration.  
  15. PROXY_OPTIONS=”–daemon”  
  16. PROXY_PID=/var/run/mysql-proxy.pid  
  17. # Source mysql-proxy configuration.  
  18. if [ -f /etc/sysconfig/mysql-proxy ] ; then  
  19. . /etc/sysconfig/mysql-proxy  
  20. fi  
  21. PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH  
  22. # By default it’s all good  
  23. RETVAL=0  
  24. # See how we were called.  
  25. case “$1” in  
  26. start)  
  27. # Start daemon.  
  28. echo -n $”Starting $prog: ”  
  29. daemon $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS –pid-file $PROXY_PID  
  30. RETVAL=$?  
  31. echo  
  32. if [ $RETVAL = 0 ]; then  
  33. touch /var/lock/subsys/mysql-proxy  
  34. fi  
  35. ;;  
  36. stop)  
  37. # Stop daemons.  
  38. echo -n $”Stopping $prog: ”  
  39. killproc $prog  
  40. RETVAL=$?  
  41. echo  
  42. if [ $RETVAL = 0 ]; then  
  43. rm -f /var/lock/subsys/mysql-proxy  
  44. rm -f $PROXY_PID  
  45. fi  
  46. ;;  
  47. restart)  
  48. $0 stop  
  49. sleep 3  
  50. $0 start  
  51. ;;  
  52. condrestart)  
  53. [ -e /var/lock/subsys/mysql-proxy ] && $0 restart  
  54. ;;  
  55. status)  
  56. status mysql-proxy  
  57. RETVAL=$?  
  58. ;;  
  59. *)  
  60. echo “Usage: $0 {start|stop|restart|status|condrestart}”  
  61. RETVAL=1  
  62. ;;  
  63. esac  
  64. exit $RETVAL  
  65.   
  66. # 写配置文件 启动脚本会读取此配置文件:  
  67. [root@centos run]# vim /etc/sysconfig/mysql-proxy  
  68. # 文件内容如下[此内容需写在一行,不可主从换行,不然会有错]
  69. PROXY_OPTIONS=”–daemon –log-level=debug –log-file=/var/log/mysql-proxy.log –plugins=proxy –proxy-backend-addresses=192.168.1.104:3306 –proxy-read-only-backend-addresses=192.168.1.102:3306 –proxy-read-only-backend-addresses=192.168.1.102:3307 –proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua –plugins=admin –admin-username=admin –admin-password=admin –admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua”  
  70. # 赋予脚本执行权限  
  71. [root@centos run]# chmod +x /etc/init.d/mysql-proxy  
  72. # 测试脚本使用:  
  73. [root@centos run]# /etc/init.d/mysql-proxy start  
  74. Starting mysql-proxy: [ OK ]  
  75. [root@centos run]# netstat -tulnap|grep mysql  
  76. tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 5454/mysql-proxy  
  77. tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 5454/mysql-proxy  
  78. [root@centos run]# /etc/init.d/mysql-proxy stop  
  79. Stopping mysql-proxy: [ OK ]  
  80. # 以上mysql-proxy就配置好了,读写分离的效果需要有压力,大家需要反复测试,才能在管理接口上看到  
  81. # mysql的主从搭建,这里就不配置,各位可以参考上面的文章,如果有疑问,可在下面留言反馈。

rhel6系统中,mysql 5.6复制新特性下主从复制配置[基于GTID]

1.mysql5.6在复制方面的新特性:

(1).支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sql thread).针对这样的改进,如果我们想实现多线程复制,无疑要对现存的数据库结构进行重新设计,分库分表.对于压力都集中在个别database的,多线程并发复制特性就没有意义.

(2).支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向.在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步.

(3).基于Row复制只保存改变的列,大大节省Disk Space/Newwork resources和Memory usage.

(4).支持把Master 和Slave的相关信息记录在Table中,原来是记录在文件里,记录在表里,增强可用性

(5).支持延迟复制

注:

关于 server_uuid 的解释:服务器身份ID。在第一次启动Mysql时,会自动生成一个server_uuid并写入到数据目录下auto.cnf文件里,官方不建议修改。

  1. [root@client102 ~]# cat /home/mysql/data/auto.cnf  
  2. [auto]  
  3. server-uuid=14be3ddd-4e92-11e3-8335-000c299c1b31  

关于GTID的解释: 全局事务标识符。当开启这个功能时,每次事务提交都会在binlog里生成一个唯一的标示符,它由server_uuid和事务ID组成。首次提交的事务ID为1,第二次为2,第三次为3,依次类推。

2.mysql主从复制的原理图:[此原理没有变化]

mysql主从复制原理图

3. MySQL5.6开始主从复制有两种方式:基于日志(binlog)[基于日志的搭建,上篇文章已搭建过];基于GTID(全局事务标识符)

本次配置是基于GTID(全局事务标识符),但其也有劣势,实际生产环境中,暂时不推荐使用

基于GTID(全局事务标识符)的局限性:

(1).GTID同步复制是基于事务。所以Myisam表不支持,这可能导致多个GTID分配给同一个事务。(5.6.9版本已经修改,支持修改Myisam表)

(2).gtid_mode和enforce-gtid-consistency=true 必须同时使用,不同时使用,启动Mysql报错。

(3).无法修改myisam表的数据,会提示”Updates to non-transactional tables are forbidden when disable-gtid-unsafe-statements”   –> 这个我测试5.6.14,是可以正常修改数据,所以这点劣势待定,大家可以分享测试结果

(4).不支持对临时表操作:CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE     –> 这个劣势,5.6.14也可以做,大家可以测试,留言反馈,最近,在5.6.19上测试,是不可以操作,以前5.6.14可能测试有误,这里补充一下。

(5).不支持CREATE TABLE … SELECT语句。因为该语句会被拆分成create table 和insert两个事务,并且这个两个事务被分配了同一个GTID,这会导致insert被备库忽略掉[这条语句在游戏数据库用的比较多,通常用来将大表分成小表]

4.基于GTID(全局事务标识符)mysql主从复制配置演示:

(1).默认主DB server和从DB server数据库都已经安装好,我的两台DB server都已经安装好(5.6.14版本),都会是双实例[这里我使用3307端口的实例]

注:两台机器的的selinux都是disable(永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled),防火墙可以选择关闭,开启的话也行[不行的话,添加防火墙策略]

主DB server:192.168.1.102:3307

从DB server:192.168.1.100:3307

(2).修改主DB server的配置文件(/etc/my.cnf) [client102为主DB server] –> 主DB sever的配置文件和从DB server文件基本一样,方便搭建HA

  1. [root@client102 ~]# vim /etc/my.cnf  
  2. # 在[mysqld]里加入如下代码[里面原代码保留,有重复的部分,以这部分为准]  
  3. # 设置server_id,一般建议设置为IP,或者再加一些数字[在以前版本为server-id]  
  4. server_id =1021  
  5. # 二进制日志的格式:有row、statement和mixed三种  
  6. # 注:当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致; 推荐使用 row  
  7. binlog-format=ROW  
  8. # 这个选项允许应用程序只能对行的镜像数据进行复制,而不在关心行是否已经进行了DML操作.这提高了主从机器的复制吞吐量,减少了二进制日志所占用的磁盘空间、网络资源和内存占用.  
  9. binlog-row-image = minimal  
  10. # 开启二进制日志功能,可以随便取,最好有含义  
  11. log-bin=mysql3307-bin  
  12. # log-slave-updates/gtid-mode/enforce-gtid-consistency/report-port/report-host:用于启动GTID及满足附属的其它需求[其中启动GTID必须同时设置gtid-mode/enforce-gtid-consistency/]  
  13. report-host=192.168.1.102  
  14. report-port=3307  
  15. gtid-mode=on  
  16. enforce-gtid-consistency=true  
  17. log-slave-updates=true  
  18. # master-info-repository/relay-log-info-repository都设置为TABLE,mysql.slave_master_info与 mysql.slave_relay_log_info 中,table都是innodb类型的,支持事务,比文件安全  
  19. # 默认值是FILE, 比如master info就保存在master.info文件中,relay log info保存在relay-log.info文件中,如果服务器意外关闭,正确的relay info 没有来得及更新到 relay-log.info文件,这样会造成数据丢失  
  20. master-info-repository=TABLE  
  21. relay-log-info-repository=TABLE  
  22. # 启用之后,使binlog在每N次binlog写入后与硬盘 同步  
  23. sync-master-info=1  
  24. # 以下是对二进制日志一些设置  
  25. binlog_cache_size = 4M  
  26. max_binlog_size = 1G  
  27. max_binlog_cache_size = 2G  
  28. # 以下这几个参数是启用binlog/relaylog的校验,防止日志出错  
  29. binlog-checksum=CRC32  
  30. slave_allow_batching = 1  
  31. master-verify-checksum=1  
  32. slave-sql-verify-checksum=1  
  33. # 启用这个参数,可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度  
  34. # 只对row binlog格式有效.启用后,会向binlog中写入更多的调试信息,比如sql语句自身都会被写进去. mysqlbinlog -vv 可以看到.  
  35. binlog-rows-query-log_events=1  
  36. # 开启基于库的多线程复制.默认是0,不开启,最大并发数为1024个线程  
  37. slave-parallel-workers=4  
  38. # 这两个是启用relaylog的自动修复功能,避免由于网络之类的外因造成日志损坏,主从停止.  
  39. relay_log_purge = 1  
  40. relay_log_recovery = 1  

(3).启动数据库服务器,并登陆数据库,授予相应的用户用于同步

  1. # 查看GTID是否开启[enforce_gtid_consistency/gtid_mode 为ON,表示已经开启]  
  2. mysql> show global variables like ‘%gtid%’;  
  3. +————————–+——-+  
  4. | Variable_name | Value |  
  5. +————————–+——-+  
  6. | enforce_gtid_consistency | ON |  
  7. | gtid_executed | |  
  8. | gtid_mode | ON |  
  9. | gtid_owned | |  
  10. | gtid_purged | |  
  11. +————————–+——-+  
  12. # 我这里是多实例mysql,所以启动是这样的,如果大家是单实例的,就直接启动就可以[/etc/init.d/mysqld start]  
  13. [root@client102 ~]# mysqld_multi start 3307  
  14. # 登陆mysql 服务器  
  15. [root@client102 ~]# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
  16. # 授予用户权限用于主从同步  
  17. mysql> grant replication slave on *.* to ‘kongzhong’@’192.168.1.100’ identified by ‘kongzhong’;  
  18. # 刷新授权表信息  
  19. mysql> flush privileges;  

(4).从DB server配置文件添加代码如下,和主基本一样

  1. [root@client100 ~]# vim /etc/my.cnf  
  2. # 在[mysqld]下添加如下代码[里面原代码保留,有重复的部分,以这部分为准]  
  3. server_id =1002 # 此处和主DB server不一样,唯一值  
  4. binlog-format=ROW  
  5. binlog-row-image = minimal  
  6. log-bin=mysql33071-bin # 此处和主DB server不一样  
  7. report-host=192.168.1.100 # 此处和主DB server不一样  
  8. report-port=3307  
  9. gtid-mode=on  
  10. enforce-gtid-consistency=true  
  11. master-info-repository=TABLE  
  12. relay-log-info-repository=TABLE  
  13. sync-master-info=1  
  14. slave-parallel-workers=4  
  15. binlog_cache_size = 4M  
  16. max_binlog_size = 1G  
  17. max_binlog_cache_size = 2G  
  18. binlog-checksum=CRC32  
  19. master-verify-checksum=1  
  20. slave-sql-verify-checksum=1  
  21. binlog-rows-query-log_events=1  
  22. log-slave-updates=true  
  23. relay_log_purge = 1  
  24. relay_log_recovery = 1  

(5).启动从数据库,添加主DB server指向[关键位置]

  1. # 启动数据库  
  2. [root@client100 ~]# mysqld_multi start 3307  
  3. # 登陆数据库,添加相关参数(主DBserver的ip/端口/同步用户/密码/****) 这里也需要看是否启动GTID,我这里省略  
  4. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
  5. mysql> change master to  
  6. -> master_host=’192.168.1.102′,  
  7. -> master_user=’kongzhong’,  
  8. -> master_password=’kongzhong’,  
  9. -> master_port=3307,  
  10. -> master_auto_position = 1,  
  11. -> master_delay=30;  
  12. #/* 下面是一部分注解:  
  13. #/* 指定主DB server的IP地址  
  14. master_host=’192.168.1.102′  
  15. #/* 指定用于同步的用户[这个就是我们在主DB server授权的用户]  
  16. master_user=’kongzhong’  
  17. #/* 指定用于同步的用户的密码  
  18. master_password=’kongzhong’  
  19. #/* 指定主DB server的端口[下面一个例子,可以重点看这个]  
  20. master_port=3306  
  21. #/* 自动寻找position号  
  22. master_auto_position = 1  
  23. #/* 延时30秒执行复制,relay日志会及时同步到slave机,只是日志的中的事件会根据事件的时间戳延时30秒执行.此参数具有实用性  
  24. master_delay=30  

(6).开启同步,验证同步状态

  1. # 开启主从同步[以前的版本slave start,新版本已不支持此写法]  
  2. mysql> start slave;  
  3. # 查看主从同步状态  
  4. mysql> show slave status\G;  
  5. # 主要看以下两个参数:[这两个参数如果是yes就表示主从同步正常]  
  6. Slave_IO_Running: Yes  
  7. Slave_SQL_Running: Yes  
  8. # 在新特性下,我们还可以看到如下几个参数  
  9. Retrieved_Gtid_Set: 1f14026d-53e7-11e3-a5f9-000c299c1b31:4  
  10. Executed_Gtid_Set: 1f14026d-53e7-11e3-a5f9-000c299c1b31:1-4  
  11. #/* Retrieved_Gtid_Set项:记录了relay日志从Master获取了binlog日志的位置  
  12. #/* Executed_Gtid_Set项:记录本机执行的binlog日志位置(如果是从机,包括Master的binlog日志位置和slave本身的binlog日志位置)  
  13. # 此时,我们可以在主DB sever 查看一下,现在gtid标识是否和从DB server 一致:  
  14. # 登陆到主DB server执行如下命令[查看gtid_executed和从DB server上的Executed_Gtid_Set是否一致,一致表示同步完成]  
  15. mysql> show global variables like ‘%GTID%’;  
  16. +————————–+——————————————+  
  17. | Variable_name | Value |  
  18. +————————–+——————————————+  
  19. | enforce_gtid_consistency | ON |  
  20. | gtid_executed | 1f14026d-53e7-11e3-a5f9-000c299c1b31:1-4 |  
  21. | gtid_mode | ON |  
  22. | gtid_owned | |  
  23. | gtid_purged | |  
  24. +————————–+——————————————+  
  25. # 此时可以大家可以在主上执行操作,看是否能同步到从上,并时刻对比上面几个参数  
  26. 上面的搭建,省了数据一致部分[我这里是新装的实例,所以数据是一致的],各位如果不是新环境,可以备份,还原数据  
  27. 备份使用如下语句, –triggers –routines –events 这几个参数没有认真揣摩其含义,如有对其了解的,可留言告知  
  28. [root@client102 ~]# mysqldump -uroot -p -S /usr/local/mysql/mysqld3307.sock –all-databases –triggers –routines –events >/tmp/mysql3307.sql  
  29. # 还原语句还和原理一样  
  30. [root@client100 ~]# mysql -uroot -p -S /usr/local/mysql/mysqld3307.sock < /tmp/mysql3307.sql   

mysql 5.6新特性下的主从,这里研究的还不够深入,有时间还需进一步研究。

各位如果发现文章中的错误,请及时指正,交流,谢谢!

mysql 5.6的新特性一览

随着oralce发布mysql 5.6版本,其新特性也备受瞩目,下面整理一些个人感觉比较有用的特性:

部分内容来自网络,如有错误或遗漏,可留言补充,谢谢!

官方描述的新特性如下:

新增 在线 DDL /更改数据架构支持动态应用程序和开发人员灵活性

新增 复制全局事务标识可支持自我修复式集群

新增 复制无崩溃从机可提高可用性

新增 复制多线程从机可提高性能

新增 对 InnoDB 进行 NoSQL 访问,可快速完成键值操作以及快速提取数据来完成大数据部署

改进 在 Linux 上的性能提升多达 230%

改进 在当今多核、多 CPU 硬件上具备更高的扩展力

改进 InnoDB 性能改进,可更加高效地处理事务和只读负载

改进 更快速地执行查询,增强的诊断功能

改进 Performance Schema 可监视各个用户/应用程序的资源占用情况

改进 通过基于策略的密码管理和实施来确保安全性

复制功能 支持灵活的拓扑架构,可实现向外扩展和高可用性

分区 有助于提高性能和管理超大型数据库环境

ACID 事务 支持构建安全可靠的关键业务应用程序

存储过程 可提高开发人员效率

触发器 可在数据库层面实施复杂的业务规则

View 可确保敏感信息不受攻击

Information Schema 有助于方便地访问元数据

插入式存储引擎架构 可最大限度发挥灵活性

—————————————————————————————————————–

以下为网友结合官方总结的一些有用的新特性:[有遗漏,请留言补充]

1.权限认证,不用输入用户名和密码

2.用户密码有效期设置

3.Innodb全文检索

4.Innodb在线DDL功能增强,修改列名等不用复制数据

5.Innodb使用独享表空间时,可自定义表的数据文件存放的位置,繁忙的放SSD,支持单表在不同实例之间的转移

6.Innodb支持页大小的自定义, innodb_page_size

7.Innodb和Memcache接口的整合

8.Innodb统计信息收集更加精准,执行计划更加精准

9.Innodb Undo数据从系统表空间独立出来为单独的表空间,SSD

10.Innodb Redo日志文件大小调整为512G,以前最大为4G

11.Innodb减少内部争用,Flush操作从主线程独立出来为Flush线程,多Purge线程

12.Innodb死锁检测新方法,信息记录在Error Log中

13.Innodb Buffer Pool信息导出导入,Restart Database with Large Buffer Pool

14.Partition 支持分区和表Exchange

15.Partition 支持显示定义操作(Select、Delete、Insert、Replace等)的分区

16.Performance Schema功能增强

17.复制支持基于Transaction的复制Gtids,提高Master和Slave的一致性

18.复制Row复制只保存改变的列,大大节省Disk Space,Newwork resources和Memory usage

19.复制支持把Master 和Slave的相关信息记录在Table

20.复制支持延迟复制

21.复制执行多线程并行复制,降低Slave与Master的延迟

22.MRR Join操作时候使用范围扫描代替单点循环提高查询效率

23.ICP Index Condition Pushdown

24.Explain支持Delete、Insert、Replace、Update等DML操作

25.子查询优化

26.时间类型字段Time、Datetime、Timestamp支持的粒度由秒扩展到微秒

 

rhel6下,mysql 5.6.14 主(master)从(slave)复制(也称mysql AB复制)环境配置[基于binlog]

一、mysql主(称master)从(称slave)复制的原理:

(1).master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件,binary log events)

(2).slave将master的binary log events拷贝到它的中继日志(relay log)

(3).slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

附简要原理图:
replication

二、mysql主从复制支持的类型:

(1).基于语句的复制:在主服务器上执行的SQL语句,在从服务器上执行同样的语句.MySQL默认采用基于语句的复制,效率比较高

(2).基于行的复制:把改变的内容直接复制过去,而不关心到底改变该内容是由哪条语句引发的 . 从mysql5.0开始支持

(3).混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制.

三、主从配置需要注意的地方:

(1).主DB server和从DB server数据库的版本一致

(2).主DB server和从DB server数据库数据一致[ 这里就会可以把主的备份在从上还原,也可以直接将主的数据目录拷贝到从的相应数据目录]

(3).主DB server开启二进制日志,主DB server和从DB server的server_id都必须唯一

四、主从配置的简要步骤:

附简要示意图:
单向主从

1.主DB SERVER上的配置

(1).安装数据库

(2).修改数据库配置文件,指明server_id,开启二进制日志(log-bin)

(3).启动数据库,查看当前是哪个日志,position号是多少

(4).登陆数据库,授权用户[ip地址为从机IP地址,如果是双向主从,这里的还需要授权本机的IP地址(此时自己的IP地址就是从IP地址)]

(5).备份数据库[记得加锁和解锁]

(6).传送备份到从DB server上

(7).启动数据库
以下步骤,为单向主从搭建成功,想搭建双向主从需要的步骤:

(1).登陆数据库,指定主DB server的地址,用户,密码等信息[此步仅双向主从时,需要]

(2).开启同步,查看状态

2.从DB SERVER上的配置

(1).安装数据库

(2).修改数据库配置文件,指明server_id[如果是搭建双向主从的话,也要开启二进制日志(log-bin)]

(3).启动数据库,还原备份

(4).查看当前是哪个日志,position号是多少[单向主从此步不需要,双向主从需要]

(5).指定主DB server的地址,用户,密码等信息

(6).开启同步,查看状态

五、单向主从环境[也称 mysql A/B复制]的搭建案例:

1.主DB server和从DB server都安装相应版本的数据库,我的两台DB server都已经安装好(5.6.14版本),都会是双实例,这里就不演示安装,可以参考mysql源码编译安装和mysql多实例配置两篇文章

注:两台机器的的selinux都是disable(永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled),防火墙可以选择关闭,开启的话也行[不行的话,添加防火墙策略]

2.修改主DB server的配置文件(/etc/my.cnf),开启日志功能,设置server_id值,保证唯一[client102为主DB server]

  1. [root@client102 scripts]# vim /etc/my.cnf  
  2. # 修改配置文件里,下面两个参数:  
  3. # 设置server_id,一般建议设置为IP,或者再加一些数字  
  4. server_id =102  
  5. # 开启二进制日志功能,可以随便取,最好有含义  
  6. log-bin=mysql3306-bin  

3.启动数据库服务器,并登陆数据库,授予相应的用户用于同步

  1. # 我这里是多实例mysql,所以启动是这样的,如果大家是单实例的,就直接启动就可以[/etc/init.d/mysqld start]  
  2. [root@client102 scripts]# mysqld_multi start 3306  
  3. # 登陆mysql 服务器  
  4. [root@client102 scripts]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p  
  5. # 授予用户权限用于主从同步  
  6. mysql> grant replication slave on *.* to ‘kongzhong’@’192.168.1.100’ identified by ‘kongzhong’;  
  7. Query OK, 0 rows affected (0.00 sec)  
  8. # 刷新授权表信息  
  9. mysql> flush privileges;  
  10. Query OK, 0 rows affected (0.00 sec)  
  11. # 查看position 号,记下position 号(很重要,从机上需要这个position号和现在的日志文件,我这里是414和mysql3306-bin.000001)  
  12. mysql> show master status;  
  13. +———————-+———-+————–+——————+——————-+  
  14. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
  15. +———————-+———-+————–+——————+——————-+  
  16. | mysql3306-bin.000001 | 414 | | | |  
  17. +———————-+———-+————–+——————+——————-+  
  18. 1 row in set (0.00 sec)  

4.为保证主DB server和从DB server的数据一致,这里采用主备份,从还原来实现初始数据一致

  1. # 临时锁表  
  2. mysql> flush tables with read lock;  
  3. # 我这里实行的全库备份,在实际中,我们可能只同步某一个库,可以只备份一个库  
  4. # 新开一个终端,执行如下操作  
  5. [root@client102 data]# mysqldump -p3306 -uroot -p -S /usr/local/mysql/mysqld3306.sock –all-databases > /tmp/mysql.sql  
  6. # 解锁  
  7. mysql> unlock tables;  
  8. # 将备份的数据传送到从机上,用于恢复  
  9. [root@client102 data]# scp /tmp/mysql.sql root@192.168.1.100:/tmp  

5.从DB server配置文件只需修改一项,其余用命令行做

  1. [root@client100 ~]# vim /etc/my.cnf  
  2. # 设置server_id,一般建议设置为IP,或者再加一些数字  
  3. server_id =100  

6.启动数据库,还原备份数据

  1. # 启动数据库  
  2. [root@client100 ~]# mysqld_multi start 3306  
  3. # 还原主DB server备份的数据  
  4. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p < /tmp/mysql.sql 7.登陆数据库,添加相关参数(主DBserver的ip/端口/同步用户/密码/position号/读取哪个日志文件)   
  5. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p  
  6. mysql> change master to  
  7. > master_host=’192.168.1.102′,  
  8. > master_user=’kongzhong’,  
  9. > master_password=’kongzhong’,  
  10. > master_port=3306,  
  11. > master_log_file=’mysql3306-bin.000001′,  
  12. > master_log_pos=414;  
  13. #/* 下面是一部分注解:  
  14. #/* 指定主DB server的IP地址  
  15. master_host=’192.168.1.102′  
  16. #/* 指定用于同步的用户[这个就是我们在主DB server授权的用户]  
  17. master_user=’kongzhong’  
  18. #/* 指定用于同步的用户的密码  
  19. master_password=’kongzhong’  
  20. #/* 指定主DB server的端口[下面一个例子,可以重点看这个]  
  21. master_port=3306  
  22. #/* 指定从DB server 从哪个日志文件开始读[在主DB server上使用show master status查看到日志]  
  23. master_log_file=’mysql3306-bin.000001′  
  24. #/* 指定 从哪个POSITION号开始读  
  25. master_log_pos=414  
  26.   
  27. # 开启主从同步  
  28. mysql> start slave;  
  29. # 查看主从同步状态  
  30. mysql> show slave status\G;  
  31. # 主要看以下两个参数:[这两个参数如果是yes就表示主从同步正常]  
  32. Slave_IO_Running: Yes  
  33. Slave_SQL_Running: Yes  

8.下面大家就可以在主DB server上新建一个表,看是否能同步到从DB server上,我这里就不测试了

[注:千万不要在从DB server手动插入数据,那样数据就不一致,主从就会断开,需要重新配置了]

如果有问题,可以尝试关闭IPTABLES(/etc/init.d/iptables stop)和selinux(setenforce 0:临时关闭selinux,永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled)

9.上面所搭建的是单向主从,也是用的比较多的,有人想了解双向主从是如何搭建,其实,就是主DB server和从DB sever都开启日志功能,然后在主DB SERVER执行授权用户[这里授权的是自己作为从服务器,也就是这里的IP地址是主DB server的IP地址],然后再在主DB server上进行chang master操作.有不理解的可以留言询问.

rhel6下,mysql5.6.14 多实例配置

       在我们的生产环境中,很少一台db server就一个mysql实例,从节约成本和充分利用资源的角度出发,我们往往会在主从环境下,对从机的db server 安装多个mysql实例,用来同步主db server的相应实例

      1.mysql 多实例的控制原理:
       多实例通过Mysqld_multi 可以管理多个监听不同的 unix 套字节文件和 TCP/IP 端口的连接的 mysqld 进程,包括 start,stop,status。
        默认的 mysqld_multi 是读取 my.cnf 中的[mysqld/N]中的数据,N 一定是正整数,如果你是单独写的文件,可以通过–config-file 参数来指定读取的 configfile。
  1. mysqld_multi 的 option  
  2. usage:  
  3. myysqld_multi [optinos] {start|stop|report} [GNR[,GNR]..]  
  4. option:  
  5. start|stop |report 这些大家都懂得  

GNR 指的是[msyqld/N]中的 N,GNR 中不能有空格和 tab

mysqld_multi start 10 就是启动 my.cnf 中的 10 的实例;mysqld_multi start 1,4-6 就是启动 1,4 到 6 个实例

如果任何参数都不加入的话,默认会启动所有的实例

登陆会话,则指定对应mysq实例的套接字文件:mysql -uuser -ppassword -S sockpath

2.下面我们简单搭建一台DB server启动两个mysql实例[主从同步,会在下一篇文章中搭建]

(1).我默认在这台DB server上已经安装一个mysql 实例[ 我这里是源码安装的mysql,各位可以采用RPM、二进制、源码等方式安装一个mysql ]

数据库安装目录:/usr/local/mysql

3306实例数据存放目录:/home/mysql/data

(2).新建第二个mysql实例数据存放目录,目录名为:data_3307

  1. [root@client100 mysql]# mkdir -p /home/mysql/data_3307  
  2. # 修改数据目录的属主  
  3. [root@client100 mysql]# chown  mysql. -R  /home/mysql/data_3307  

(3).编辑数据库配置文件:/etc/my.cnf

  1. [root@client100 mysql]# vim /etc/my.cnf  
  2. [mysqld_multi]  
  3. # 指定相关命令的路径  
  4. mysqld     =  /usr/local/mysql/bin/mysqld_safe  
  5. mysqladmin =  /usr/local/mysql/bin/mysqladmin  
  6. # 指定用户,用于启动和关闭mysql服务[这里尽量使用root用户,默认root用户拥有所以权限,记得在两个实例中root密码一致]  
  7. user = root  
  8. password = kongzhong  
  9. # 指定错误日志路径  
  10. log = /usr/local/mysql/multi.log  
  11.   
  12. [mysqld3306]  
  13. basedir =/usr/local/mysql  
  14. datadir =/home/mysql/data  
  15. socket = /usr/local/mysql/mysqld3306.sock  
  16. pid-file = /home/mysql/data/mysql3306.pid  
  17. general_log = 1  
  18. general_log_file = /home/mysql/data/mysql3306.log  
  19. port =3306  
  20. server_id =100  
  21. user = mysql  
  22. join_buffer_size = 128M  
  23. sort_buffer_size = 2M  
  24. read_rnd_buffer_size = 2M  
  25. default_storage_engine = MYISAM  
  26. join_buffer_size = 128M  
  27. max_allowed_packet= 1M  
  28. net_buffer_length= 8K  
  29. skip-external-locking  
  30. skip-host-cache  
  31. skip-name-resolve  
  32. # InnoDB  
  33. innodb_buffer_pool_size = 128M  
  34. innodb_log_file_size = 48M  
  35. innodb_file_per_table = 1  
  36. innodb_flush_method = O_DIRECT  
  37. # MyISAM  
  38. key_buffer_size = 48M  
  39. character-set-server=utf8  
  40. collation-server=utf8_general_ci  
  41. # LOG  
  42. log_error = /home/mysql/data/mysql-error.log  
  43. long_query_time = 1  
  44. slow-query-log  
  45. slow_query_log_file = /home/mysql/data/mysql-slow.log  
  46. # Others  
  47. explicit_defaults_for_timestamp=true  
  48. max_connections = 500  
  49. open_files_limit = 65535  
  50. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  
  51.   
  52. [mysqld3307]  
  53. basedir =/usr/local/mysql  
  54. datadir =/home/mysql/data_3307  
  55. socket = /usr/local/mysql/mysqld3307.sock  
  56. pid-file = /home/mysql/data_3307/mysql3307.pid  
  57. general_log = 1  
  58. general_log_file = /home/mysql/data_3307/mysql3307.log  
  59. port =3307  
  60. server_id =101  
  61. user = mysql  
  62. join_buffer_size = 128M  
  63. sort_buffer_size = 2M  
  64. read_rnd_buffer_size = 2M  
  65. default_storage_engine = MYISAM  
  66. join_buffer_size = 128M  
  67. max_allowed_packet= 1M  
  68. net_buffer_length= 8K  
  69. skip-external-locking  
  70. skip-host-cache  
  71. skip-name-resolve  
  72. # InnoDB  
  73. innodb_buffer_pool_size = 128M  
  74. innodb_log_file_size = 48M  
  75. innodb_file_per_table = 1  
  76. innodb_flush_method = O_DIRECT  
  77. # MyISAM  
  78. key_buffer_size = 48M  
  79. character-set-server=utf8  
  80. collation-server=utf8_general_ci  
  81. # LOG  
  82. log_error = /home/mysql/data_3307/mysql-error.log  
  83. long_query_time = 1  
  84. slow-query-log  
  85. slow_query_log_file = /home/mysql/data_3307/mysql-slow.log  
  86. # Others  
  87. explicit_defaults_for_timestamp=true  
  88. max_connections = 500  
  89. open_files_limit = 65535  
  90. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  

(4).为第二个实例初始化数据库:

  1. # 进入数据库安装目录  
  2. [root@client100 mysql]# cd /usr/local/mysql/scripts/  
  3. # 执行初始化脚本,指定数据目录/用户  
  4. [root@client100 scripts]# ./mysql_install_db –basedir=/usr/local/mysql –datadir=/home/mysql/data_3307 –user=mysql  

(5).使用mysqld_multi命令启动相应实例[如果my.cnf不在默认位置,需指明位置–defaults-extra-file=****]

  1. # 启动相应实例  
  2. [root@client100 ~]# mysqld_multi start 3306  
  3. [root@client100 ~]# mysqld_multi start 3307  
  4. # 使用如下命令检测 mysql 相应实例进程是否启动  
  5. [root@client100 ~]# ps -ef |grep mysql  
  6. # 使用netstat 检测mysql两个实例端口是否启动[看到两个端口的监听都出现,就说明搭建正常]  
  7. [root@client100 ~]# netstat -ntlp |grep mysql  
  8. # 使用如下两条命令,可登陆到对应实例数据库  
  9. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p  
  10. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
  11. # 登陆后,可查看参数,查看数据存放目录,以确认是否登陆自己要登陆的实例  
  12. mysql> show variables like ‘%datadir%’  
  13. # 关闭实例  
  14. [root@client100 ~]# mysqld_multi stop 3306  
  15. [root@client100 ~]# mysqld_multi stop 3307  
  16. # 如果执行以上命令,不能关闭的话,请使用以下方式关闭  
  17. [root@client100 ~]# mysqladmin -uroot -p -S /usr/local/mysql/mysqld3307.sock shutdown  
  18. [root@client100 ~]# mysqladmin -uroot -p -S /usr/local/mysql/mysqld3306.sock shutdown  
  19.   
  20. ## 有的朋友可能在使用 mysqld_multi stop 关闭时,始终有实例关闭不了,这里就是/etc/my.cnf中user/password的作用  
  21. ## 需要做如下操作:  
  22. ## 如果你指定的用户不是root用户,那最好拥有mysql的所有目录权限,不然会报权限不够,所以建议用mysql或root用户  
  23. ## 如果是root用户,因为root拥有所有权限,所以只要设置两个实例的密码都是一样的就行  
  24. ## 而对于mysql,则编译安装时,创建用户是就要允许此用户登录,即不要加nologin限制  
  25. ## 并登录到实例数据库后,授予shutdown权限,设置密码  
  26. mysql>  GRANT SHUTDOWN ON *.* TO ‘mysql’@’localhost’ IDENTIFIED BY ‘kongzhong’;  
  27. mysql> flush privileges  
  28. ## 现在再用 mysqld_multi stop 关闭,就可正常关闭[还是那句,建议用root]  

mysql三种常用存储引擎的区别及优缺点

1.MyISAM
(1).特性:
     ①.不支持事务:MyISAM存储引擎不支持事务,所以对事务有要求的业务场景不能使用
     ②.表级锁定:其锁定机制是表级索引,这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
     ③.读写互相阻塞:不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读
     ④.只会缓存索引:MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO,但是这个缓存区只会缓存索引,而不会缓存数据。
(2).适用场景
     ①.不需要事务支持(不支持)
     ②.并发相对较低(锁定机制问题)
     ③.数据修改相对较少(阻塞问题)
     ④.以读为主
     ⑤.数据一致性要求不是非常高
(3).最佳实践
     ①.尽量索引(缓存机制)
     ②.调整读写优先级,根据实际需求确保重要操作更优先
     ③.启用延迟插入改善大批量写入性能
     ④.尽量顺序操作让insert数据都写入到尾部,减少阻塞
     ⑤.分解大的操作,降低单个操作的阻塞时间
     ⑥.降低并发数,某些高并发场景通过应用来进行排队机制
     ⑦.对于相对静态的数据,充分利用Query Cache可以极大的提高访问效率
     ⑧.MyISAM的Count只有在全表扫描的时候特别高效,带有其他条件的count都需要进行实际的数据访问
2.InnoDB
(1).特性
     ①.具有较好的事务支持:支持4个事务隔离级别,支持多版本读
     ②.行级锁定:通过索引实现,全表扫描仍然会是表锁,注意间隙锁的影响读写阻塞与事务隔离级别相关
     ③.具有非常高效的缓存特性:能缓存索引,也能缓存数据
     ④.整个表和主键以Cluster方式存储,组成一颗平衡树
     ⑤.所有Secondary Index都会保存主键信息
(2).适用场景
     ①.需要事务支持(具有较好的事务特性)
     ②.行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成
     ③.数据更新较为频繁的场景
     ④.数据一致性要求较高
     ⑤.硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘 IO
(3).最佳实践
     ①.主键尽可能小,避免给Secondary index带来过大的空间负担
     ②.避免全表扫描,因为会使用表锁
     ③.尽可能缓存所有的索引和数据,提高响应速度
     ④.在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交
     ⑤.合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性
     ⑥.避免主键更新,因为这会带来大量的数据移动
3.NDBCluster
(1).特性
     ①.分布式:分布式存储引擎,可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
     ②.支持事务:和Innodb一样,支持事务
     ③.可与mysqld不在一台主机:可以和mysqld分开存在于独立的主机上,然后通过网络和mysqld通信交互
     ④.内存需求量巨大:新版本索引以及被索引的数据必须存放在内存中,老版本所有数据和索引必须存在与内存中
(2).适用场景
     ①.具有非常高的并发需求
     ②.对单个请求的响应并不是非常的critical
     ③.查询简单,过滤条件较为固定,每次请求数据量较少,又不希望自己进行水平Sharding
(3).最佳实践
     ①.尽可能让查询简单,避免数据的跨节点传输
     ②.尽可能满足SQL节点的计算性能,大一点的集群SQL节点会明显多余Data节点
     ③.在各节点之间尽可能使用万兆网络环境互联,以减少数据在网络层传输过程中的延时
转自 小渃的博客:http://www.rsyslog.net/archives/170

my.cnf文件参数说明、优化浅析

mysql的配置文件,在linux下为my.cnf,在windows下为my.ini,当mysql服务器启动时它会读取这个文件,设置相关的运行环境参数。

my.cnf分为两块:Client Section 和 Server Section。

Client Section用来配置MySQL客户端参数。

1.Client Section

  1. [client]  
  2. # 设置mysql客户端连接服务端时默认使用的端口  
  3. port = 3306    
  4. [mysql]  
  5. # 设置mysql客户端默认字符集  
  6. default-character-set=utf8     

2.Server Section

  1. [mysqld]  
  2. # mysql服务端默认监听(listen on)的TCP/IP端口  
  3. port=3306     
  4. # 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(Linux下默认是/var/lib/mysql/mysql.sock文件)   
  5. socket = /tmp/mysql.sock       
  6. # 数据库的安装目录  
  7. basedir=/usr/local/mysql  
  8. # 数据库数据存放目录  
  9. datadir=/home/mysql/data  
  10. # 服务端使用的字符集默认为8比特编码的latin1字符集  
  11. character-set-server=latin1   
  12. # 创建新表时将使用的默认存储引擎  
  13. default-storage-engine=INNODB   
  14. # SQL模式为strict模式[强制模式,这样不符合规范的数据,是无法插入数据库的]  
  15. sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”   
  16. # mysql服务器支持的最大并发连接数(用户数)。但总会预留其中的一个连接给管理员使用超级权限登录,即使连接数目达到最大限制。如果设置得过小而用户比较多,会经常出现“Too many connections”错误。  
  17. max_connections=100   
  18. # 查询缓存大小,用于缓存SELECT查询结果。如果有许多返回相同查询结果的SELECT查询,并且很少改变表,可以设置query_cache_size大于0,可以极大改善查询效率。而如果表数据频繁变化,就不要使用这个,会适得其反  
  19. query_cache_size=0   
  20. # 这个参数在5.1.3之后的版本中叫做table_open_cache,用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表,因此此参数的值与max_connections有关。当某一连接访问一个表时,MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开,则会直接访问缓存中的表已加快查询速度;如果该表未被缓存,则会将当前的表添加进缓存并进行查询。在执行缓存操作之前,table_cache用于限制缓存表的最大数目:如果当前已经缓存的表未达到table_cache,则会将新表添加进来;若已经达到此值,MySQL将根据缓存表的最后查询时间、查询率等规则释放之前的缓存。  
  21. table_cache=256   
  22. # 内存中的每个临时表允许的最大大小。如果临时表大小超过该值,临时表将自动转为基于磁盘的表(Disk Based Table)。  
  23. tmp_table_size=34M   
  24. # 缓存的最大线程数。当客户端连接断开时,如果客户端总连接数小于该值,则处理客户端任务的线程放回缓存。在高并发情况下,如果该值设置得太小,就会有很多线程频繁创建,线程创建的开销会变大,查询效率也会下降。一般来说如果在应用端有良好的多线程处理,这个参数对性能不会有太大的提高。  
  25. thread_cache_size=8   
  26. # 避免MySQL的外部锁定,减少出错几率增强稳定性。   
  27. skip-locking           
  28. # s禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!  
  29. skip-name-resolve             
  30. # 记录慢查询,然后对慢查询一一优化   
  31. log-slow-queries = slow.log  
  32. # 最大并发线程数,取值为服务器逻辑CPU数量×2,如果CPU支持H.T超线程,再×2   
  33. thread_concurrency      = 8   
  34.         
  35. # MyISAM相关参数  
  36. # mysql重建索引时允许使用的临时文件最大大小  
  37. myisam_max_sort_file_size=100G   
  38. #  MyISAM表发生变化时重新排序所需的缓冲  
  39. myisam_sort_buffer_size=68M  
  40. # Key Buffer大小,用于缓存MyISAM表的索引块。决定数据库索引处理的速度(尤其是索引读)  
  41. key_buffer_size=54M   
  42. # 用于对MyISAM表全表扫描时使用的缓冲区大小。针对每个线程进行分配(前提是进行了全表扫描)。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。  
  43. read_buffer_size=64K  
  44. # 改参数在使用行指针排序之后,随机读用的  
  45. read_rnd_buffer_size=256K  
  46. # 每个需要进行排序的线程分配该大小的一个缓冲区 ,connection级参数(为每个线程配置),500个线程将消耗500*256K  
  47. sort_buffer_size=256K   
  48.   
  49. # InnoDB相关参数  
  50. # InnoDB用于存储元数据信息的内存池大小,一般不需修改  
  51. innodb_additional_mem_pool_size=3M  
  52. # 事务相关参数,如果值为1,则InnoDB在每次commit都会将事务日志写入磁盘(磁盘IO消耗较大),这样保证了完全的ACID特性。而如果设置为0,则表示事务日志写入内存log和内存log写入磁盘的频率都为1次/秒。如果设为2则表示事务日志在每次commit都写入内存log,但内存log写入磁盘的频率为1次/秒。  
  53. innodb_flush_log_at_trx_commit =1   
  54. # InnoDB日志数据缓冲大小,如果缓冲满了,就会将缓冲中的日志数据写入磁盘(flush)。由于一般至少都1秒钟会写一次磁盘,所以没必要设置过大,即使是长事务。  
  55. innodb_log_buffer_size=2M   
  56. # InnoDB使用缓冲池来缓存索引和行数据。该值设置的越大,则磁盘IO越少。一般将该值设为物理内存的80%。  
  57. innodb_buffer_pool_size=105M  
  58. # 每一个InnoDB事务日志的大小。一般设为innodb_buffer_pool_size的25%到100%  
  59. innodb_log_file_size=53M   
  60. # InnoDB内核最大并发线程数。  
  61. innodb_thread_concurrency=9  

3.MySQL的优化分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化。

(1).服务器硬件对MySQL性能的影响

①.磁盘寻道能力(磁盘I/O),以目前高转速SCSI硬盘(7200转/秒)为例,这种硬盘理论上每秒寻道7200次,这是物理特性决定的,没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,对于日均访问量在100万PV以上的Discuz!论坛,由于磁盘I/O的制约,MySQL的性能会非常低下!解决这一制约因素可以考虑以下几种解决方案: 使用RAID-0+1磁盘阵列[RAID1+0],注意不要尝试使用RAID-5,MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。

②.CPU 对于MySQL应用,推荐使用S.M.P.架构的多路对称CPU,例如:可以使用两颗Intel Xeon 3.6GHz的CPU,现在我较推荐用4U的服务器来专门做数据库服务器,不仅仅是针对于mysql。

③.物理内存对于一台使用MySQL的Database Server来说,服务器内存建议不要小于2GB,推荐使用4GB以上的物理内存,不过内存对于现在的服务器而言可以说是一个可以忽略的问题,工作中遇到了高端服务器基本上内存都超过了16G。

(2).对MySQL自身的优化主要是对其配置文件my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。 由于my.cnf文件的优化设置是与服务器硬件配置息息相关的,因而我们指定一个假想的服务器硬件环境:CPU: 2颗Intel Xeon 2.4GHz 内存: 4GB DDR 硬盘: SCSI 73GB(很常见的2U服务器)。

下面,我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明:
# vim /etc/my.cnf以下只列出my.cnf文件中[mysqld]段落中的内容,其他段落内容对MySQL运行性能影响甚微,因而姑且忽略。

  1. [mysqld]  
  2. port = 3306  
  3. serverid = 1  
  4. socket = /tmp/mysql.sock  
  5. # 避免MySQL的外部锁定,减少出错几率增强稳定性。  
  6. skip-locking  
  7. # 禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!  
  8. skip-name-resolve  
  9. # back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。  
  10. back_log = 384  
  11. # key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意:该参数值设置的过大反而会是服务器整体效率降低!  
  12. key_buffer_size = 256M  
  13. max_allowed_packet = 4M  
  14. thread_stack = 256K  
  15. # 查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占,如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。  
  16. sort_buffer_size = 6M  
  17. # 读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。  
  18. read_buffer_size = 4M  
  19. # 联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。  
  20. join_buffer_size = 8M  
  21. myisam_sort_buffer_size = 64M  
  22. # 指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。  
  23. query_cache_size = 64M  
  24. # 默认为16M,调到64-256最挂
  25. tmp_table_size = 256M  
  26. # 指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示,则需要增大该参数值。  
  27. max_connections = 768  
  28. max_connect_errors = 10000000  
  29. # 指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。  
  30. wait_timeout = 10  
  31. # 该参数取值为服务器逻辑CPU数量*2,在本例中,服务器有2颗物理CPU,而每颗物理CPU又支持H.T超线程,所以实际取值为4*2=8  
  32. thread_concurrency = 8  
  33. # 开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接![*注意*]  
  34. skip-networking  
  35. # 物理内存越大,设置就越大.默认为2402,调到512-1024最佳  
  36. table_cache=1024  
  37. # 默认为2M  
  38. innodb_additional_mem_pool_size=4M  
  39. # 设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1  
  40. innodb_flush_log_at_trx_commit=1  
  41. # 默认为1M  
  42. innodb_log_buffer_size=2M  
  43. # 你的服务器CPU有几个就设置为几,建议用默认一般为8  
  44. innodb_thread_concurrency=8  
  45. # 默认为256K  
  46. read_rnd_buffer_size=16M 
  47. # 默认为60  
  48. thread_cache_size=120  
  49. query_cache_size=32M  

# 值得注意的是:

很多情况需要具体情况具体分析

(1).如果Key_reads太大,则应该把my.cnf中Key_buffer_size变大,保持Key_reads/Key_read_requests至少1/100以上,越小越好。

(2).如果Qcache_lowmem_prunes很大,就要增加Query_cache_size的值。

此篇文章,来自网络,作者不详!

rhel6下,源码编译安装mysql 5.6.14

从mysql 5.5开始,mysql编译采取了cmake编译,来取代原来的configure,因此,我们下面的编译安装,也是使用cmake编译安装。
需要准备的源码包:

mysql源码包:mysql-5.6.14.tar.gz

cmake源码包:cmake-2.8.10.2.tar.gz [cmake我们也采取编译安装,并下载最新版本]

1.对于全新的系统环境,源码编译安装,需要很多包,但是,系统光盘里基本上提供了,所以,建议大家先配置yum源,这里不做演示[可参考博客里 “yum仓库配置”一文]。这里默认大家已经配置好yum源:

  1. [root@client100 ~]# yum -y install gcc-c++ ncurses-devel make perl bison ncurses  

2.进入存放源码包的目录,解压cmake源码包

  1. # 解压源码包  
  2. [root@client100 tmp]# tar -xf cmake-2.8.10.2.tar.gz  
  3. # 进入解压目录  
  4. [root@client100 tmp]# cd cmake-2.8.10.2  
  5. # 配置安装参数  
  6. [root@client100 cmake-2.8.10.2]# ./configure –prefix=/usr/local/cmake  
  7. # 编译源码  
  8. [root@client100 cmake-2.8.10.2]# gmake  
  9. # 安装  
  10. [root@client100 cmake-2.8.10.2]# make install  
  11. # 修改配置文件  
  12. [root@client100 cmake-2.8.10.2]# vim /etc/profile  
  13. # 在文件的最后,添加如下两行[即修改PATH的路径,指定cmake命令的路径]  
  14. PATH=/usr/local/cmake/bin:$PATH  
  15. export path  
  16. # 使配置文件立即生效  
  17. [root@client100 cmake-2.8.10.2]# source /etc/profile  
  18. # 执行如下操作,如果能显示具体版本,cmake就安装成功  
  19. [root@client100 cmake-2.8.10.2]# cmake –version  
  20. cmake version 2.8.10.2  

3.mysql编译前,准备工作:

  1. # 创建mysql用户组  
  2. [root@client100 tmp]# groupadd mysql  
  3. # 创建用户并加入用户组,并取消login权限  
  4. [root@client100 tmp]# useradd -g mysql -s ‘/sbin/nologin’ mysql  
  5. # 建立mysql安装目录  
  6. [root@client100 test]# mkdir -p /usr/local/mysql  
  7. # 建立mysql数据存放目录  
  8. [root@client100 test]# mkdir -p /home/mysql/data  
  9. # 建立mysql安装目录的属主  
  10. [root@client100 mysql-5.6.14]# chown mysql.mysql -R /usr/local/mysql  
  11. # 修改mysql数据存放目录的所属者  
  12. [root@client100 mysql-5.6.14]# chown mysql.mysql -R /home/mysql/data   

4.开始编译安装mysql

  1. # 解压mysql 源码包  
  2. [root@client100 tmp]# tar -xf  mysql-5.6.14.tar.gz  
  3. # 进入mysql源码包目录  
  4. [root@client100 mysql-5.6.14]# cd mysql-5.6.14  
  5. # 用cmake配置安装选项  
  6. [root@client100 mysql-5.6.14]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \  
  7. -DMYSQL_DATADIR=/home/mysql/data \  
  8. -DSYSCONFDIR=/etc \  
  9. -DWITH_MYISAM_STORAGE_ENGINE=1 \  
  10. -DWITH_INNOBASE_STORAGE_ENGINE=1 \  
  11. -DWITH_MEMORY_STORAGE_ENGINE=1 \  
  12. -DWITH_READLINE=1 \  
  13. -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysqld.sock \  
  14. -DMYSQL_TCP_PORT=3306 \  
  15. -DENABLED_LOCAL_INFILE=1 \  
  16. -DWITH_PARTITION_STORAGE_ENGINE=1 \  
  17. -DEXTRA_CHARSETS=all \  
  18. -DDEFAULT_CHARSET=utf8 \  
  19. -DDEFAULT_COLLATION=utf8_general_ci  
  20.   
  21. # /*  以下为配置参数注解   */  
  22. # /*  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql    # 指定mysql安装位置  
  23. # /*  -DMYSQL_DATADIR=/home/mysql/data            # 数据文件存放位置  
  24. # /*  -DSYSCONFDIR=/etc                                       # 配置文件my.cnf的位置  
  25. # /*  -DWITH_MYISAM_STORAGE_ENGINE=1            # 支持MyIASM引擎  
  26. # /*  -DWITH_INNOBASE_STORAGE_ENGINE=1         # 支持InnoDB引擎  
  27. # /*  -DWITH_MEMORY_STORAGE_ENGINE=1           # 支持Memory引擎  
  28. # /*  -DWITH_READLINE=1                                      # 快捷键功能(我没用过)  
  29. # /*  -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysqld.sock       # 连接数据库socket路径  
  30. # /*  -DMYSQL_TCP_PORT=3306                              # 端口  
  31. # /*  -DENABLED_LOCAL_INFILE=1                           # 允许从本地导入数据  
  32. # /*  -DWITH_PARTITION_STORAGE_ENGINE=1        # 安装支持数据库分区  
  33. # /*  -DEXTRA_CHARSETS=all                                 # 安装所有的字符集  
  34. # /*  -DDEFAULT_CHARSET=utf8                              # 默认字符  
  35. # /*  -DDEFAULT_COLLATION=utf8_general_ci           # 指定服务器默认的校对规则,默认latin1_general_ci  
  36. # /*  -DWITH_xxx_STORAGE_ENGINE             # 指定静态编译到mysql的存储引擎,
  37. MyISAM,MERGE,MEMORY以及CSV四种引擎默认即被编译至服务器,不需要特别指定[1.代表编译]  
  38. # /*  -DWITHOUT_xxx_STORAGE_ENGINE      # 指定不编译的存储引擎
  39. # /*  -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk   
  40.   
  41. # 开始编译  
  42. [root@client100 mysql-5.6.14]#gmake  
  43. # 安装mysql到刚才参数配置的目录  
  44. [root@client100 mysql-5.6.14]# make install  
  45. # /*  注意事项   */  
  46. 重新编译时,需要清除旧的对象文件和缓存信息[如果可以,就把解压的目录删掉,重新解压目录或者执行如下操作]  
  47. # make clean  
  48. # rm -f CMakeCache.txt  
  49. # rm -rf /etc/my.cnf  

5.后续mysql相关操作:

  1. #  进入mysql安装目录下的脚本目录  
  2. [root@client100 /]# cd /usr/local/mysql/scripts/  
  3. #  安装mysql默认数据库,指明数据库安装目录,数据目录,用户  
  4. [root@client100 scripts]# ./mysql_install_db –basedir=/usr/local/mysql –datadir=/home/mysql/data –user=mysql  
  5. # 进入mysql配置文件目录  
  6. [root@client100 mysql]# cd /usr/local/mysql/support-files/  
  7. # 复制配置文件到/etc目录下,并改名为my.cnf[如果目录下有my.cnf文件,选择覆盖或者删除或者在上面进行修改]  
  8. [root@client100 support-files]# cp  my-default.cnf /etc/my.cnf  
  9. # 修改/etc/my.cnf的属主  
  10. [root@client100 support-files]# chown mysql:mysql /etc/my.cnf  
  11. # 将安装目录属主改为mysql[上面更改,到这里又变成root属主了,所以可以选择更改为mysql]  
  12. [root@client100 support-files]# chown mysql:mysql  -R /usr/local/mysql  
  13. # 修改/etc/my.cnf配置文件  
  14. [root@client100 support-files]# vim /etc/my.cnf  
  15. # 配置文件内容如下: [ 编辑完成后,保存退出 ]  
  16. [client]  
  17. socket = /usr/local/mysql/mysqld.sock  
  18. port = 3306  
  19.   
  20. [mysqld]  
  21. basedir =/usr/local/mysql  
  22. datadir =/home/mysql/data  
  23. socket = /usr/local/mysql/mysqld.sock  
  24.   
  25. port =3306  
  26. server_id =100  
  27. user = mysql  
  28. join_buffer_size = 128M  
  29. sort_buffer_size = 2M  
  30. read_rnd_buffer_size = 2M  
  31. default_storage_engine = MYISAM  
  32. join_buffer_size = 128M  
  33. max_allowed_packet= 1M  
  34. net_buffer_length= 8K  
  35.   
  36. skip-external-locking  
  37. skip-host-cache  
  38. skip-name-resolve  
  39.   
  40. # InnoDB  
  41. innodb_buffer_pool_size = 128M  
  42. innodb_log_file_size = 48M  
  43. innodb_file_per_table = 1  
  44. innodb_flush_method = O_DIRECT  
  45.   
  46. # MyISAM  
  47. key_buffer_size = 48M  
  48. character-set-server=utf8  
  49. collation-server=utf8_general_ci  
  50.   
  51. # LOG  
  52. log_error = /home/mysql/data/mysql-error.log  
  53. long_query_time = 1  
  54. slow-query-log  
  55. slow_query_log_file = /home/mysql/data/mysql-slow.log  
  56.   
  57. # Others  
  58. explicit_defaults_for_timestamp=true  
  59. max_connections = 500  
  60. open_files_limit = 65535  
  61. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  
  62.   
  63.   
  64. # 复制启动脚本文件到/etc/init.d/目录下  
  65. [root@client100 support-files]# cp mysql.server /etc/init.d/mysqld  
  66. # 修改启动脚本权限  
  67. [root@client100 support-files]#chmod 755 /etc/init.d/mysqld  
  68. # 修改启动脚本  
  69. [root@client100 support-files]# vim /etc/init.d/mysqld  
  70. # 仅修改如下两行,指定mysql安装目录,数据存放目录  
  71. basedir=/usr/local/mysql  
  72. datadir=/home/mysql/data  
  73. # 编译配置文件,将mysql相关命令路径放到PATH环境变量里  
  74. [root@client100 support-files]# vim /etc/profile  
  75. PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:/usr/local/cmake/bin:$PATH  
  76. export PATH  
  77. # 重读配置文件,立即生效  
  78. [root@client100 support-files]# source  /etc/profile  
  79. # 启动mysql数据库  
  80. [root@client100 mysql]# /etc/init.d/mysqld start  
  81. # 登陆测试[默认第一次登陆,没有密码]  
  82. [root@client100 mysql]# mysql  
  83.   
  84. #/* 下面是mysql的一些简答操作 */  
  85. #/*  安装mysql后,设置root用户密码  
  86. [root@client100 mysql]# mysqladmin -uroot password ‘kongzhong’  
  87. # 设置完密码后,在直接用mysql登陆,是登陆不了  
  88. [root@client100 mysql]# mysql  
  89. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)  
  90. # 需要使用此种方式登陆  
  91. [root@client100 mysql]# mysql -uroot -pkongzhong  
  92. # 下面是删除mysql数据库中,密码为空的用户  
  93. mysql> use mysql  
  94. mysql> select Host,User,Password from user;  
  95. mysql> delete from user where password=”;  
  96. # 刷新授权表  
  97. mysql> flush privileges;  
  98. # 可选:运行安全设置脚本,修改MySQL用户root(不是系统的root!)的密码,禁止root远程连接(防止破解密码),移除test数据库和匿名用户,强烈建议生产服务器使用:/usr/local/mysql/bin/mysql_secure_installation [在我们的安装环境中,命令行直接输入mysql_secure_installation 即可]  

mysql数据库文件的组成

1.mysql文件组成部分

参数文件:告诉Mysql实例启动时在哪里可以找到数据库文件,并且指定某些初始化参数,这些参数定义了某种内存结构的大小等设置。用文件存储,可编辑,若启动时加载不到则不能成功启动(与其他数据库不同)。参数有动态和静态之分,静态相当于只读,动态是可以set的。如我们通过show variable like ‘***’查出来的key、value值,是可以通过set key=value直接修改的。同是,修改时还有作用域之分,即这个seesion个有效和全局有效,在对应的key前加上session或global即可,如select @@seesion.read_buffer_size、set @@global.read_buffer_size。

日志文件:用来记录Mysql实例对某种条件做出响应时写入的文件。如错误日志文件二进制日志文件慢查询日志文件查询日志文件等。

错误日志:通过show variables like ‘log_error’来查看错误日志存放地址

慢查询日志

通过show variables like ‘%long%’ 查看慢查询日志记录的阈值,新版本设成了0.05;

通过show variables like ‘log_slow_queries’查看是否开启了,默认为关闭的;

通过show variabes like ‘log_queries_not_using_indexes’查看是将没有使用索引的查询记录到慢日志中。

mysql中可以直接通过mysqldumpslow命令来查看慢日志。

二进制文件:不记录查询,只记录对数据库所有的修改操作。目的是为了恢复(point-in-time修复)和复制。通过show variables like ‘datadir’查看存放路径。二进制日志支持STATEMENT、ROW、MIX三种格式,通过binlog_format参数设定,通常设置为ROW,可以为数据库的恢复和复制带来更好的可靠性,但会带来二进制文件大小的增加,复制时会增加网络开销。mysql中通过mysqlbinlog查看二进制日志文件内容。

socket文件:当用Unix域套接字方式进行连接时需要的文件。

pid文件:Mysql实例的进程ID文件。

Mysql表结构文件:用来存放Mysql表结构定义文件。因为Mysql插件式存储引擎的体系结构,每个表都有一个对应的文件,以frm后缀结尾。

存储引擎文件:存储自己的文件来保存各种数据,真正存储了数据和索引等数据。下面主要介绍InnoDB的存储引擎下的表空间文件和重做日志文件。

表空间文件:InnoDB默认的表空间文件为ibdata1,可通过show variables like ‘innodb_file_per_table’查看每个表是否产生单独的.idb表空间文件。但是,单独的表空间文件仅存储该表的数据、索引和插入缓冲等信息,其余信息还是存放在默认的表空间中。

tablespace

重做日志文件:实例和介质失败,重做日志文件就能派上用场,如数据库掉电,InnoDB存储引擎会使用重做日志恢复到掉电前的时刻,以此来保证数据的完整性。

参数:

innodb_log_file_size指定了重做日志文件的大小;

innodb_log_file_in_group指定了日志文件组中重做日志文件的数量,默认为2,

innodb_mirrored_log_groups指定了日志镜像文件组的数量,默认为1,代表只有一个日志文件组,没有镜像;

innodb_log_group_home_dir指定了日志文件组所在路径,默认在数据库路径下。

二进制日志和重做日志的区别

首先,二进制日志会记录所有与Mysql有关的日志记录,包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎重做日志只存储有关其本身的事务日志;

其次内容不同,不管将二进制日志文件记录的格式设为STATEMENT还是ROW,又或者是MIXED,其记录的都是关于一个事务的具体操作内容。而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况 。

此外,写入时间不同,二进制日志文件是在事务提交前进行记录的,而在事务进行的过程中,不断有重做日志条目被写入重做日志文件中。

 

mysql 存储引擎简述[innodb存储引擎]

1.mysql存储引擎概述:

innodb存储引擎:面向OLTP(online transaction processing)、行锁支持外键、非锁定读、默认采用repeatable级别(可重复读)通过next-keylocking策略,避免幻读、插入缓冲二次写自适应哈希索引、预读

myisam存储引擎: 不支持事务、表锁、全文索引、适合OLAP(在线分析处理),其中myd:放数据文件,myi:放索引文件

ndb存储引擎: 集群存储引擎,share nothing,可提高可用性

memory存储引擎: 数据存放在内存中,表锁, 并发性能差,默认使用哈希索引

archive存储引擎:只支持insert和select zlib算法压缩1:10,适合存储归档数据如日志等、行锁

maria存储引擎: 目的取代myisam、缓存数据和索引、行锁、mvcc

2.各大存储引擎对比图:
存储引擎比较多

3.innodb存储引擎简述:[推荐看 mysql 技术内幕:innodb存储引擎 一书]
innodb

主体系结构:默认7个后台线程[4个io thread(insert buffer、log、read、write),1个master thread(优先级最高),1个锁(lock)监控线程1个错误监控线程]可以通过show engine innodb status来查看。新版本已对默认的read thread和write thread分别增大到4个,可通过show variables like ‘innodb_io_thread%’查看。

存储引擎组成:缓冲池(buffer pool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool).具体配置可由show variables like ‘innodb_buffer_pool_size’show variables like ‘innodb_log_buffer_size’show variables like ‘innodb_additional_mem_pool_size’来查看。

缓冲池:占最大块内存,用来存放各种数据的缓存包括有索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数据字典信息等。工作方式总是将数据库文件按页(每页16k)读取到缓冲池,然后按最近最少使用(lru)的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改,总是首先修改在缓存池中的页(发生修改后即为脏页),然后再按照一定的频率将缓冲池的脏页刷新到文件。通过命令show engine innodb status;来查看。

日志缓冲:将重做日志信息先放入这个缓冲区,然后按一定频率将其刷新到重做日志文件。

master thread:
loop主循环每秒一次的操作如下:

(1).日志缓冲刷新到磁盘,即使这个事务还没有提交。(总是执行,所以再大的事务commit 的时间也是很快的)

(2).合并插入缓冲(innodb当前一秒发生的io次数小于5次则执行)

(3).至多刷新100个innodb的缓冲池中的脏页到磁盘(超过配置的脏页所占缓冲池比例则执行,在配置文件中由innodb_max_dirty_pages_pac决定,默认是90,新版本是75, google建议是80)

(4).如果当前没用用户活动,切换到backgroud loop
loop主循环每10秒一次的操作:

(1).刷新100个脏页到磁盘(过去10秒IO操作小于200次则执行)

(2).合并至多5个插入缓冲(总是)

(3).将日志缓冲到磁盘(总是)

(4).删除无用的Undo页(总是)

(5).刷新100个或者10个脏页到磁盘(有超过70%的脏页,刷新100个脏页;否则刷新10个脏页)

(6).产生一个检查点
backgroud loop:若当前没有用户活动(数据库空闲时)或者数据库关闭时,就会切换到这个循环:

(1).删除无用的Undo页(总是)

(2).合并20个插入缓冲(总是)

(3).跳回到主循环(总是)

(4).不断刷新100个页,直到符合条件(可能在flush loop中完成)
如果flush loop中也没有什么事情可以做了,InnoDB存储引擎会切换到suspend_loop,将master thread挂起,等待事件的发生。若启用了InnoDB存储引擎,却没有使用任何InnoDB存储引擎的表,那么master thread总是处于挂起状态

插入缓冲:不是缓冲池的一部分,Insert Buffer是物理页的一个组成部分,它带来InnoDB性能的提高。根据B+算法(下文会提到)的特点,插入数据的时候会主键索引是顺序的,不会造成数据库的随机读取,而对于非聚集索引(即辅助索引),叶子节点的插入不再是顺序的了,这时需要离散地访问非聚集索引,插入性能在这里变低了。InnoDB引入插入缓冲,判断非聚集索引页是否在缓冲池中,如果在则直接插入;不在,则先放在 插入缓冲区中。然后根据上述master thread中介绍的,会有一定的频率将插入缓冲合并。此外,辅助索引不能是唯一的,因为插入到插入缓冲时,并不去查找索引页的情况,否则仍然会造成随机读,失去插入缓冲的意义了。插入缓冲可能会占缓冲池中内存,默认也能会占到1/2,所以可以将这个值调小点,到1/3。通过IBUF_POOL_SIZE_PER_MAX_SIZE来设置,2表示1/2,3表示1/3。

两次写: 它带来InnoDB数据的可靠性。如果写失效,可以通过重做日志进行恢复,但是重做日志中记录的是对页的物理操作,如果页本身损坏,再对其进行重做是没有意义的。所以,在应用重做日志前,需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再进行重做,这就是doublewire。

恢复数据=页副本+重做日志
double write

自适应哈希索引:InnoDB存储引擎提出一种自适应哈希索引,存储引擎会监控对表上索引的查找,如果观察到建立建立哈希索引会带来速度的提升,则建立哈希索引,所以称之为自适应的。自适应哈希索引只能用来搜索等值的查询,如select * from table where index_col=’***’, 此外自适应哈希是由InnoDB存储引擎控制的,我们只能通过innodb_adaptive_hash_index来禁用或启用,默认开启。