LINUX 下MySQL主从复制与分区技术以及bin-log日志

MYSQL的主从复制和分区技术主要是解决服务器负载均衡的策略。

这个技术所涉及到的技术点

1.MySQL用户授权

2.MySQL bin-log日志

3.MySQL主从复制

4.MySQL分区技术

 

在测试中 主服务器ip为 192.168.10.1 从服务器ip为 192.168.10.111

 

一、MySQL用户授权

登录mysql的test数据库
mysql -uroot -p123 test

如果我们要做主从复制 那么 我们就需要在主服务器上给从服务器授权，下面我们在主服务器上给从服务器授权一个用户，让从服务器能用此用户来访问我们的主服务器的数据库。

授权用户
grant all on *.* to user1@192.168.10.111 identified by "123";【授权服务器地址为192.168.10.111，用户名为user1密码为123的用户能够访问主服务器】
grant all on *.* to user1 identified by "123";【给全部主机授权】
我们查看一下主服务器的用户
 select user,host,password from mysql.user;
发现用户表中 多了一个user1的用户 那么主机地址为192.168.10.111

那么我们测试一下 我们在从服务器上用user1看能不能登录主服务器
mysql -uuser1 -p123 -h192.168.10.1 
测试发现成功！

 

二、 bin-log日志

首先我们要开启mysql的binlog日志 
编辑/etc/my.cnf文件
在[mysqld]中加入下列代码
log-slow-queries = mysql-slow.log
log-error = mysql.err
log = mysql.log
log-bin = mysql-bin
#binlog日志的文件是存放在data目录中所以 要在此设置一下datadir这个属性
datadir = /usr/local/mysql/data


登录mysql
mysql -uroot -p123 test;
用到的代码指令：
flush logs; 【生成一个最新的bin-log日志】
show master status; 【查看最后一个bin-log日志】
reset master; 【清空所有bin-log日志】
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001【查看bin-log日志 前提是你当前所在目录为/usr/local/mysql/data】
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 | mysql -uroot -p123 test【把mysql-bin.000001的日志的记录恢复到test数据库】
/usr/local/mysql/mysqlbinlog --start-position="50" --stop-position="100" mysql-bin.000001 | mysql -uroot -p123 test【把mysql-bin.000001的50-100行的记录恢复到test数据库,除此之外还可以有--stop-date=""】
mysqldump -uroot -p123 test -l -F > /tmp/test.sql【备份test数据库到test.sql文件里 -l 代表读锁 -F代表flush logs】
mysql -uroot -p123 test -v -f < /tmp/test.sql【从test.sql恢复数据记录-v 代表查看导入信息 -f代表忽略错误】

 

三、mysql主从复制

主ip：192.168.10.1
从ip：192.168.10.111

1.主服务器进行用户授权
grant all on *.* to user1@192.168.10.111 identified by "123"

2.修改主服务器my.cnf并设置server-id的值
找到[mysqld]
修改server-id=1

3.（可选）在从服务器上设置读锁定有效，确保没有数据库插入或者修改操作
flush tables with read lock;
备份数据库
unlock tables; 解锁

4.修改从服务器my.cnf 并设置server-id
找到[mysqld]修改server-id=2
【mysql5.5貌似不支持my.cnf中master-host之类配置主从服务器关联】
可以这么做：登录mysql执行主从设置
change master to master_host='192.168.10.1',master_user='user1',master_password='123';

启动主从复制功能
slave start;

查看主从信息
show slave status\G;

如果打印的信息中slave_IO_Running:yes和Slave_SQL_Runnning:yes 则表示配置成功

 

 

四、MySQL分区技术

mysql分区技术是mysql5.1以后出现的新技术，能替代分库分表技术，它的优势在于只在物理层面来降低数据库压力，无需更改php的代码。

常用的MySQL分区类型：

1.RANGE分区：基于属于一个给定的连续区间的列值，把多行分配给分区（基于列）。

2.LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合的某个值来进行选择（基于列值是固定值的）。

3.HASH分区：基于用户自定义的表达式的返回值来进行分区选择，该表达式使用将要插入列表中的这些行的列值进行计算，这个函数可以包含mysql中有效的产生非负整数值的任何表达式。

4.KEY分区：类似于HASH分区，却别在于KEY分区只支持计算一列或者多列，且mysql服务器提供其自身的哈希函数。

日常中用的比较多的就是RANGE,LIST分区

实例：

CREATE TABLE TEST{
    store_id INT NOT NULL
}
PARTITION BY RANGE(store_id)(
   PARTITION p0 VALUES LESS THAN(6),
   PARTITION p1 VALUES LESS THAN(11)
);

以上意思是 当store_id值小于6就会分配到p0分区 小于11则是p1分区

CREATE TABLE TEST{
    store_id INT NOT NULL
}
PARTITION BY LIST(store_id)(
   PARTITION p0 VALUES IN (1,2,3)  ,
   PARTITION p1 VALUES IN (4,5,6)
);