一、主从集群配置
原理:主服务器建立二进制日志,每产生语句或磁盘变化,写进二进制日志 binlog;从服务器读取主服务器的二进制日志从而建立relaylog日志;然而主服务器需要授权复制账号给从服务器来监听二进制日志。
实现步骤:
1:首先确保主服务器打开二进制日志功能,这样,主服务器一旦有数据变化,立即产生二进制日志.
2:从服务器也需要开启二进制日志和relay日志功能,这样可以从主服务器读取binlog,并产生relaylog
3:在主服务器建立一个从服务器的账号,并授予数得上权限.
4: 指定从服务对应的主服务器,开启从服务器.
具体配置:
a) 建立主服务器二进制日志:log_bin = mysql-bin (my.cnf中配置)
b) 主服务器二进制日志的格式:binlog_format = MIXED (STATEMENT|ROW|MIXED: 可监听语句或行变化或混合的)
c) 建立从服务器relay日志:relay_log = mysql-relay (my.cnf中配置)
d) 为服务器建立唯一标识符:server-id = ...199|200|201... (不同服务器ID不同,使用int类型)
e) 通过命令建立主服务器账号密码:grant replication client,replication slave on *.* to '(‘repl’@’192.168.%.%’为授权账号,‘repl’为密码)
f) 在从服务器通过命令建立连接:change master to
master_host = ‘192.168.0.199’ (主服务器IP),
master_user = ‘repl’,
master_password = ‘repl’,
master_log_file = ‘mysql-bin.000001’,
master_log_pos = 278; (show master status; 查看)
g) 命令开启slave:start slave;
h) 查看从服务器连接详情:show slave status \G;
i) 重置slave:reset slave;
j) 关闭slave:stop slave;
二、主主复制(可参考主从复制)
1: 2台服务器都设置上2进制日志和relay日志
2: 都设置上replcation账号
3: 都设置对方为自己的master
三、主主复制的主键冲突解决(两台服务器)
a) 一台服务器设置(奇数):
i. set session auto_increment_increment = 2; (自增2)
ii. set session auto_increment_offset = 1; (从1开始自增)
iii. set global auto_increment_increment = 2;
iv. set global auto_increment_offset = 1;
b) 另一台服务器设置(偶数):
i. set session auto_increment_increment = 2; (自增2)
ii. set session auto_increment_offset = 2; (从2开始自增)
iii. set global auto_increment_increment = 2;
iv. set global auto_increment_offset = 2;
四、集群中间件mysql-proxy完成负载均衡与读写分离
注意:均衡不是指sql语句为单位的均衡,而是以mysql连接节点来均衡;
b) 建立mysql代理服务器(负载均衡):./bin/mysql-proxy -P192.168.0.199:4040
--proxy-backend-addresses=192.168.0.199:3306
--proxy-backend-addresses=192.168.0.200:3306
c) 代理服务器连接: mysql -h 192.168.0.199 -P 4040 -uroot -p
d) 配置读写分离连接: ./bin/mysql-proxy
-b 192.168.0.199:3306 (写)
-r 192.168.0.200:3306 (读)
-s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua(读写脚本)
五、partition分区
a) range分区:
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (id) # 使用ID来划分范围
(PARTITION t0 VALUES LESS THAN (1000),
PARTITION t1 VALUES LESS THAN (2000),
PARTITION t2 VALUES LESS THAN (maxvalue) ;
#MAXVALUE 表示最大的可能的整数值
b) list分区:
CREATE TABLE `user ` (
`id`int(11),
`name` varchar(11),
`addr_id` int(11)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(addr_id) (
PARTITION beijing VALUES IN(1,2),
PARTITION hebei VALUES IN(3,4,5),
PARTITION guangdong VALUES IN(6,7),
...
);
c) hash分区
六、事务
特性ACID:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
a) 原子性:指某几个sql语句的影响,要么都发生,要么都不发生。
b) 一致性:事务前后的数据,保持业务上的合理一致。
c) 隔离性:在事务进行过程中,其他事务看不到此事务的任何效果。
d) 持久性:事务一旦发生则不能取消,只能通过补偿性事务来抵消效果。
事务的使用流程:
开启事务: start transaction
执行查询 : xxxx
提交事务/回滚事务: commit / rollback
事务隔离级别:
read uncommitted: 读未提交事务的内容,显然不符原子性,称为”脏读”.
read commited: 在一个事务进行过程中,读不到另一个进行事务的操作,但是可以读到另一个结束事务的操作影响.
repeatable read: 可重复读,即在一个事务过程中,所有信息都来自事务开始那一瞬间的信息,不受其他已提交事务的影响. (大多数的系统,用此隔离级别)
serializeable 串行化:所有的事务,必须编号,按顺序一个一个来执行,也就取消了冲突的可能.这样隔离级别最高,但事务相互等待的等待长.在实用,也不是很多.
查看事务级别: show variables like '%isolation%';
设置事务的级别: set session transaction isolation level read uncommitted;