例1.假设你创建了一张表，该表保存20家音像店的职员记录，这20家音像店的编号从1到20。根据store_id将其分成4个分区，可使用range分区。如下图：
例2.根据离职日期分区，同样使用range分区，如下图：
例3.list分区与range分区有类似的地方（list好比php中的switch、range好比php中的if…else），假设20家音像店分别在4个区，每个区分别包含如下store_id的音像店。东区：1,2,10,11,19,20 南区：7,8,15,16 西区：4,12,13,14,18 北区：3,5,6,9,17 此时，可使用list分区，如下图：
例4.基于入职日期hired，按年份平均分布成4个分区，则可使用hash分区，如下图：
key分区，由于性能较低，一般不推荐使用，具体4个分区的对比，如下图：
类似水平分表，但是它是mysql在物理层进行水平分表，与对应的应用程序来说，它还是一张表
mysql5.1开始以插件的形式支持分区
mysql常用分区类型有 RANGE LIST HASH KEY
show plugins; //通过show plugins命令，如果显示partition active 表示分区插件已启动
innodb引擎的表有两种表空间：共享表空间、独占表空间
如果innodb的表要做分区，必须是独占表空间，而且个人也比较建议使用独占表空间（性能更高、灵活性更高），唯一的缺点就是占用硬盘容量更大
|
1 2 3 4 5 6 |
show variables like "innodb_file_per_table"; //通过该命令，如果显示 ON 则表示独立表空间已开启 //在my.ini中配置独占表空间，以下4行必须同时配置，且目录必须是存在的目录 innodb_data_home_dir = "C:\mysql\data\" //代表数据库文件所存放的目录 innodb_log_group_home_dir = "C:\mysql\data\" //日志存放目录 innodb_data_file_path=ibdata1:10M:autoextend //默认在innodb_data_home_dir所配置的目录中，名为ibdata1，可扩展大小为10M innodb_file_per_table=1 //1表示开启独占表空间 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
//创建分区表 CREATE TABLE part_tab( c1 int DEFAULT null, c2 VARCHAR(30) DEFAULT null, c3 date DEFAULT null ) PARTITION BY RANGE (YEAR(c3))( PARTITION p0 VALUES less than (1995), PARTITION p1 VALUES less than (1996), PARTITION p2 VALUES less than (1997), PARTITION p3 VALUES less than (1998), PARTITION p4 VALUES less than (1999), PARTITION p5 VALUES less than (2000), PARTITION p6 VALUES less than (2001), PARTITION p7 VALUES less than (2002), PARTITION p8 VALUES less than (2003), PARTITION p9 VALUES less than (2004), PARTITION p10 VALUES less than (2010), PARTITION p11 VALUES less than MAXVALUE ); //创建未分区表 CREATE TABLE `no_part_tab` ( `c1` int(11) DEFAULT NULL, `c2` varchar(30) DEFAULT NULL, `c3` date DEFAULT NULL ); //向分区表插入800万条数据 //由于我用的是innodb引擎 如果my.cnf中没有配置过autocommit的话，默认是自动提交的 //可以通过show variables like "%autocommit%";查看 //所以要设置成非默认提交，每100W条插入一次，否则会很慢 delimiter// create procedure load_part_tab() BEGIN DECLARE v INT DEFAULT 0; SET autocommit=0; WHILE v < 8000000 DO INSERT INTO part_tab VALUES(v,'testing',ADDDATE('1995-01-01',(RAND(v)*36520) mod 3652)); set v=v+1; IF v%1000000=0 AND v<>0 THEN COMMIT; END IF; END WHILE; COMMIT; SET autocommit=1; END; // delimiter; call load_part_tab(); //此时分区表的800万条数据已经插完了，将其导入非分区表即可 insert into no_part_tab select * from part_tab; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//运行2次时间分别为 0.542s 0.366s count值为795181 SELECT COUNT(1) FROM part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31'; //运行2次时间分别为 4.141s 4.247s count值为795181 SELECT COUNT(1) FROM no_part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31'; //分别为两个表增加索引 create index idx_of_c3 on part_tab(c3); create index idx_of_c3 on no_part_tab(c3); //再次运行上面的查询语句 //分区表时间：0.471s 0.359s 未分区表时间：0.495s 0.368s //分别在where条件中加入一个未索引的字段 count值为0 //0.552s 0.409s SELECT COUNT(1) FROM part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31' and c2='hello'; //4.437s 4.451s SELECT COUNT(1) FROM no_part_tab WHERE c3> DATE'1995-01-01' and c3<date'1995-12-31' and c2='hello'; |
从测试可看出，如果表的数据量不是很大的情况下，建立索引可以起到同样的优化效果，只有表数据量较大（估计2000万条以上吧- -）或者一些不方便建立索引的表上，选择分区技术也是不错的方案。
首选，需要安装xcode和brew。
然后删除mac自带的Apache和php。（可以不删，但是配置起来比较麻烦）
|
1 2 3 4 5 6 7 8 9 10 11 |
sudo apachectl stop sudo rm -rf /etc/apache2 sudo rm -rf /usr/include/apahce2 sudo rm -rf /usr/libexec/apache2 sudo rm -rf /usr/php sudo rm -rf /usr/bin/php sudo rm -rf /usr/bin/php-config sudo rm -rf /usr/bin/phpize sudo rm -rf /usr/include/php sudo rm -rf /usr/lib/php sudo rm -rf /usr/share/man/man*/php* |
|
1 2 3 4 5 6 7 |
//brew install xxx //brew uninstall xxx //brew list //brew update xxx //brew install /xxx*/ 正则表达式需要用/ //安装nginx brew install nginx |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
user www www; worker_processes auto; error_log /Users/tangxiaofeng/wwwlogs/error.log crit; pid /usr/local/var/run/nginx.pid; #Specifies the value for maximum file descriptors that can be opened by this process. worker_rlimit_nofile 51200; events { worker_connections 51200; multi_accept on; } http { include mime.types; default_type application/octet-stream; server_names_hash_bucket_size 128; client_header_buffer_size 32k; large_client_header_buffers 4 32k; client_max_body_size 50m; sendfile on; tcp_nopush on; keepalive_timeout 60; tcp_nodelay on; fastcgi_connect_timeout 300; fastcgi_send_timeout 300; fastcgi_read_timeout 300; fastcgi_buffer_size 64k; fastcgi_buffers 4 64k; fastcgi_busy_buffers_size 128k; fastcgi_temp_file_write_size 256k; gzip on; gzip_min_length 1k; gzip_buffers 4 16k; gzip_http_version 1.0; gzip_comp_level 2; gzip_types text/plain application/x-javascript text/css application/xml; gzip_vary on; gzip_proxied expired no-cache no-store private auth; gzip_disable "MSIE [1-6]\."; #limit_conn_zone $binary_remote_addr zone=perip:10m; ##If enable limit_conn_zone,add "limit_conn perip 10;" to server section. server_tokens off; #log format log_format access '$remote_addr - $remote_user [$time_local] "$request" ' '$status $body_bytes_sent "$http_referer" ' '"$http_user_agent" $http_x_forwarded_for'; server { listen 80 default; server_name www.test.com; index index.html index.htm index.php; root /Users/tangxiaofeng/wwwroot/default; #error_page 404 /404.html; location ~ .*\.(php|php5)?$ { fastcgi_pass 127.0.0.1:9000; fastcgi_index index.php; include /usr/local/etc/nginx/fastcgi.conf; } location ~ .*\.(gif|jpg|jpeg|png|bmp|swf)$ { expires 30d; } location ~ .*\.(js|css)?$ { expires 12h; } access_log /Users/tangxiaofeng/wwwlogs/access.log access; } include vhost/*.conf; } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//安装php，我安装的是php5.6 如果是nginx一定要加上 --wath-fpm brew install php56 --with-fpm //安装mysql brew install mysql //nginx -s reload|reopen|stop|quit //ln -s /usr/local/Cellar/nginx/1.6.2/bin/nginx /usr/bin/nginx 软链接 //nginx -c filename 指定配置文件 //nginx -t 测试是否有语法错误 //启动nginx sudo /usr/local/Cellar/nginx/1.6.2/bin/nginx //启动php-fpm sudo /usr/local/Cellar/php56/5.6.5/sbin/php-fpm //如果php-fpm启动不了，ps查看是否已运行，kill之 //配置hosts /etc/hosts //127.0.0.1 www.test.com |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//如果phpize不能用，先安装m4 brew install m4 //如果configure不能用，先安装autoconf brew install autoconf //开始安装，以yaf为例 phpize ./configure --with-php-config=/usr/local/Cellar/php56/5.6.5/bin/php-config make make install sb /usr/local/etc/php/5.6/php.ini [yaf] extension = yaf.so yaf.environ = dev //重启php-pfm sudo kill -9 $(ps aux | grep php | awk '{print $2}') /usr/local/Cellar/php56/5.6.5/sbin/php-fpm |
事务的ACID原则：
原子性（Atomicity）：一个事务要么全部执行，要么不执行。也就是说一个事务不可能只执行了一半就停止了。比如你从取款机取钱，这个事务可以分成两个步骤：1划卡、2出钱。不可能划了卡，而钱却没出来。这两步必须同时完成。要么就不完成。
一致性（Consistency）：事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。
隔离性（Isolation）：两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致。
持久性（Durability）：事务运行成功以后，就系统的更新是永久的。不会无缘无故的回滚。
事务隔离级别设置：
|
1 2 3 4 5 6 7 8 9 10 |
//全局修改，修改mysql.ini配置文件，在最后加上 [mysqld] transaction-isolation = REPEATABLE-READ //可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE //mysql默认REPEATABLE-READ，所以不设置也可以 //对当前session修改，在登录mysql客户端后，执行命令： set session transaction isolation level read uncommitted; //查看当前隔离级别 select @@TX_ISOLATION; |
隔离级别对应的脏读、不可重复读、幻读情况如下图：
脏读、不可重复读、幻读的解释：
脏读 ：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读 ：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。
幻读 : 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。
通常在实际业务中，我一般使用默认隔离级别，但是可以通过for update对重要业务加锁
|
1 2 3 4 5 6 7 8 9 10 |
//开启事务后 //id为主键 //查询有结果，mysql会进行行锁 select * from user where id=1 for update; //查询无结果，无锁 select * from user where id=1111111 for update; //查询范围不明确，锁表 select * from user where id<>100 for update; //查询的where条件不是主键、也没有加索引，锁表 select * from user where name='zhangsan' for update; |
推荐地址：http://www.cnblogs.com/zemliu/archive/2012/06/17/2552301.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
//查看centos版本 cat /etc/redhat-release //查看32位还是64位 getconf LONG_BIT //查看cpu使用情况 top //查看内存使用情况 m为兆 free -m //查看分区使用情况 df //监听每秒的文件变化情况 n1表示1秒 watch -n1 ls -lh //可以看到前一次登录所执行的所有命令 vim ~/.bash_history //给命令取别名 unalias lm 取消别名 alias lm='ls -al' //zip压缩 -r表示递归 把当前目录下的所有文件、文件夹、文件夹中的文件压缩到test.zip zip -r test.zip ./ //zip解压缩 把test.zip解压到当前目录的test文件夹下 -d ./test 不填则解压到当前目录 unzip -d ./test test.zip //tar压缩 -c 产生.tar打包文件 -v 显示详细信息 -f 指定压缩后的文件名 -z 打包同时压缩 tar -zcvf test.tar.gz ./test tar -jcvf test.tar.gz ./test //打包后，以 bzip2 压缩 //tar解压缩 -x 解包.tar文件 帮当前目录的test.tar.gz解压到当前目录 tar -zxvf ./test.tar.gz ./ //可以简写为 tar -zxvf test.tar.gz history //查看历史命令 //查看当前端口占用情况 netstat -tunpl | grep 端口号 //查看进程使用情况 ps -aux | grep 进程名 //同时在test目录中建立test1、test2、test3、test4目录 mkdir -p /test/{test1,test2,test3,test4} //复制某个文件到多个目录中 echo /test/test1 /test/test2/ /test/test3 | xargs -n 1 cp -v /test/test.php //比较两个文件夹 diff /test/test1 /test/test2 //监听某个文件是否有写入 tail -f php_errors.log //连接 telnet ip ports //退出 ctrl ]回车 -> quit //netcat nc -u 127.0.0.1 9501 //-u表示udp -C表示用\r\n结束 默认是用\n //tcp打印任意端口为9501的TCP连接 tcpdump -iany tcp port 9501 //查看进程树 pstree -a | grep server.php //查看进程信息 strace -p 进程号 主进程：strace -f -p 进程号 //查看php脚本的系统调用执行 strace php test.php //在根目录下查找名称为fi开头的文件或目录 find / -name fi\* //全局搜索package开头的文件或目录 通常需要先运行updatedb更新locate的库 locate package* //显示一个二进制文件、源码或man的位置 whereis php //显示一个二进制文件或可执行文件的完整路径 which php //drwxr-xr-x 第一位表示文件类型。d是目录文件，l是链接文件，-是普通文件，p是管道 接下来依次是 所有者 所属组 其他人 r:读--4 w:写--2 x:执行--1 //把当前目录的test目录改成777权限 -R是递归的意思 chmod -R 777 test //下载 wget http://xxx.baidu.com/download/test.zip //远程传输 把本地tmp下的test.sql 传到 192.168.2.104的tmp下 scp /tmp/test.sql 192.168.2.104:/tmp/ //后台运行 例如：nohup php test.php & //自定义nohup输出文件 nohup php test.php > myout.file 2>&1 & nohup Command [Arg...] [&] curl "www.test.com" //curl访问 man xxx //查看帮助 例如man curl //批量杀进程 ps aux | grep php | awk '{print $2}' | xargs kill kill -9 $(ps aux | grep php | awk '{print $2}') //将windows换行转换成unix换行 dos2unix [-kn] file [newfile] //将unix换行转换成windows换行 unix2dos [-kn] file [newfile] //编码转换 iconv --list 查看可转换的编码列表 iconv -f 原编码 -t 新编码 filename [-o newfile] //快捷键 ctrl u //剪切光标前的内容 ctrl k //剪切光标至行末的内容 ctrl y //粘贴 ctrl e //移动光标到行末 ctrl a //移动光标到行首 alt f //跳向下一个空格 alt b //跳回上一个空格 alt backspace //删除前一个单词 ctrl w //剪切光标前一个单词 //chkconfig [--add][--del][--list][系统服务] 或 chkconfig [--level<等级代号>][系统服务][on/off/reset] //以mysql为例，将mysql设置为开机自动启动或取消开机自动启动 chkconfig mysqld on chkconfig mysqld off //但是要注意，如果这个服务尚未被添加到chkconfig列表中，则现需要使用–add参数将其添加进去，同理可删除 chkconfig --add mysqld chkconfig --del mysqld //查看mysql在各个运行级别的启动状态 chkconfig –list可查看全部服务 chkconfig --list mysqld //运行级别 0表示：表示关机 1表示：单用户模式 2表示：无网络连接的多用户命令行模式 3表示：有网络连接的多用户命令行模式 //4表示：不可用 5表示：带图形界面的多用户模式 6表示：重新启动 这些级别定义在/ect/inittab文件中 //级别2-5为on就表示设置开机自动启动成功了 0和6状态一般为off，不要去管它 //最先运行的服务是那些放在/ect/rc.d目录下的文件 //svn常用命令 svn co svn:地址 //首先得检出 svn help //查看所有命令 svn info //查看svn目录信息 svn up //更新svn svn ci -m '描述' //svn提交 svn sw svn:地址 //切换svn |
设备：两台虚拟机 master 192.168.2.103 slave 192.168.2.104
环境：centos6.3 lnmp集成环境 mysql版本5.5.37
在学习mysql主从同步之前，我们首先得了解mysql授权、mysql的bin-log日志、mysqldump
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
//授权 //通过以下sql可以查询当前mysql的所有用户权限 select user,host,password from mysql.user //在master上为slave开一个账号 //grant表示授权 replication slave表示主从复制权限 on表示关于 *.*表示所有数据库的所有表(数据库.表) //to表示给 user2表示用户名 @192.168.2.104表示允许这个地址的主机登陆 //identified by '123'表示设置密码为123 grant replication slave on *.* to user2@192.168.2.104 identified by '123'; //在slave上登录master，如果登录成功，表示授权成功 mysql -u user2 -h 192.168.2.103 -p 123 //如果不了解如何授权，可使用 ? grant; 查看帮助 //mysql bin-log日志 //开启bin-log日志 vim /etc/my.cnf 设置 log-bin=mysql-bin //可通过show variables like '%bin%';查看开启情况 //二进制日志 保存 增删改的日志，通常存放在/usr/local/mysql/var目录下,实际位置查看my.cnf //刷新bin-log日志 此时就会多一个最新的bin-log日志 flush logs; //查看最后一个bin日志 show master status; //清空所有的bin-log日志 reset master; //查看bin-log日志 bin-log日志是二进制的 需要通过mysqlbinlog来查看 //utf8字符集查看的时候好像有个bug需要加上 --no-defaults //mysql-bin.00001 mysql-bin.index 是mysql原始保留的二进制日志，不要随意删除 /usr/local/bin/mysqlbinlog --no-defaults mysql-bin.000001 //恢复指定mysql-bin.000002到指定的数据库 --start-position --stop-position可以根据pos点指定恢复区间 //也可以使用时间点来恢复 --start-date='xxxxx' --stop-date="xxxx" 例如：2015-05-01 12:12:12 mysqlbinlog --no-defaults --start-position="107" --stop-position="280" mysql-bin.000002 | mysql -u用户名 -p密码 数据库名 //mysqldump备份 -l表示读锁 -F表示重新生成bin-log日志 mysqldump -uroot -p密码 数据库 -l -F > /dir/xxx.sql //恢复备份数据 -f表示遇到错误继续执行 mysql -uroot -p密码 数据库 -f < /dir/xxx.sql |
了解以上知识后，就可以配置mysql主从了，步骤如下：
注：为了保证测试的顺利，可以在配置前先清理一下bin-log日志 reset master
master服务器配置
1.授权一个账号给从服务器使用
2./etc/my.cnf中开启bin-log日志并设置一个唯一的server-id（需要跟从服务器不同）
3.使用mysqldump备份数据 建议加上 -l读锁 -F重新生成bin-log日志
4.把master的数据备份传输到slave上 scp /tmp/test.sql 192.168.2.104:/tmp/
5.重启mysql
slave服务器配置
1.恢复传输过来的备份数据
2./etc/my.cnf 开启bin-log 设置唯一的server-id 开启master-host=192.168.2.103 master-user = user2 master-
password=123 master-port=3306
3.重启mysql
配置完成，通过show slave status \G查看 Slave_Io_Running Slave_Sql_Running 都为yes表示主从同步配置完成
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
//从数据库常用命令 //启动复制线程 通常默认已启动 start slave //停止复制线程 stop slave //查看从数据库状态 show slave status //查看主数据库bin-log日志 show master logs //动态改变到主服务器的配置 change master to //查看从数据库的运行进程 show processlist |
从服务器无法同步时，使用以下方法进行修复
|
1 2 3 4 5 6 7 8 9 10 11 12 |
//方法一： slave stop; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; slave start; //方法二： slave stop; show master status; change master to master_host="192.168.2.103",master_user="user2",master_password="123",master_port=3306,master_log_file="mysql- bin.000003",master_log_pos=107; slave start; |
