MySQL主从复制与分区技术

MySQL主从复制与分区技术


MySQL grant用户授权

淘宝数据库发展3阶段
第一阶段
 整个网站采用LAMP架构
 数据库采用几台MySQL
 应用系统为前台，后台两大系统
第二阶段
 MySQL迁移到Oracle
 Pc Server升级到IBM小型机
 低端存储升级到高端存储
第三阶段
 核心业务从Oracle逐步迁移到分布式MySQL集群中
 大量采用pc server，采用本地硬盘



登陆
mysql -uroot opopenview

给从服务器设置授权用户
grant all slave on *.* to user@192.168.10.112 identified by 'pass'
或者
grant replication slave on *.* to user@192.168.10.112 identified by 'pass'

查看用户授权表
select user, host, password from mysql.user
查看用户是否授权
show grants for user1@192.168.10.1


windows
    ifconfig eth0 192.168.10.3

master
    ifconfig eth0 192.168.10.1
*************************************************
    测试lamp环境
    netstat -tunpl |grep :3306
    netstat -tunpl |grep :80
    cd /usr/local/apache2/htdocs/
    vi index.php
    <!php
    phpinfo();
    页面打印php模块
*************************************************
    查看用户授权表
    select user, host, password from mysql.user
    授权
    grant all  on *.* to user1@192.168.10.2 identified by 456    
    之后授权表里可以看到user1用户

slave
    ifconfig eth0 192.168.10.2
    这样直接访问是禁止登陆的，必须先用户授权
    /usr/local/mysql/bin/mysql -uroot -p123 -h192.168.10.1
    这样才可以登陆
    /usr/local/mysql/bin/mysql -uuser1 -p456 -h192.168.10.1
    可以看到master数据库中的表


*********************************************************
bin-log日志
用于恢复数据

开启
vi /etc/my.cnf
[mysqld]
port=3306
socket=/usr/local/mysql/var/mysql.sock
log-slow-queries=mysql-slow.log
log-error=mysql.err
log=mysql.log
log-bin=mysq-bin    开启bin-log日志

日志都存放在
/usr/local/mysql/var/

查看bin-log是否开启
show variables like "%bin%";


多一个最新的binlog日志
flush logs
查看最后一个bin日志，记录文件名和position
show master status
清空所有bin日志
reset master

用mysqlbinlog mysql-00001.bin时报错，说字符集有问题，解决方法是
\s查看当前数据库信息，包括4大字符集，如果字符集没有问题，修改命令
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-000001.bin
可以查看二进制binlog内容


使用binlog数据恢复
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-000002.bin |  /usr/local/mysql/bin/msql -uroot -p123 test

备份数据
mysqldump -uroot -p123 test -l -F > /tmp/test.sql
-l 加读锁，长时间备份需要加锁
-F 就是flush logs，生成新日志，包括binlog, /usr/local/mysql/var/mysql-bin.00000x


使用dump和binlog恢复数据
/usr/local/mysql/bin/mysql -uroot -p123 test -v -f < /tmp/test.sql
    -v 查看导入信息，-f 跳过错误
show master status
    找到binlog
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-000002.bin |  /usr/local/mysql/bin/msql -uroot -p123 test

如果要恢复到特定操作点
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-000002.bin |  more
看到恢复到的position，比如在100~235
那么就是
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="100" --stop-position="235" mysql-000002.bin |   /usr/local/mysql/bin/msql -uroot -p123 test

*********************************************************************
主从复制
master出问题，切换到slave
slave上执行查询，读写分离
slave上执行备份，避免影响主服务器


master
    mysql -uroot -p123 test
    grant all  on *.* to user1@192.168.10.2 identified by 456
    vi /etc/my.cnf
    log-bin=mysq-bin
    server-id=1        必须是唯一的
    flush tables with read lock    必须设置读锁
    show master status
    备份
    cp 全部数据(如果服务器可以停止，cp方式比较快  tar -cvf data.rat data)，
    或者使用mysqldump备份
    mysqldump -uroot -p123 test -l -F > /tmp/test.sql
    unlock tables 解锁
    把备份文件恢复到slave上
    scp /tmp/test.sql 192.168.10.2:/tmp/
slave
    /usr/local/mysql/bin/mysql -uroot -p123 test -v -f < /tmp/test.sql
    修改server-id
    vi /etc/my.cnf
    server-id=2        必须是唯一的
    master-host=192.168.10.1
    master-user=user1
    master-password=456
    master-port=3306
    
    ps -ef |grep mysqld
    pkill mysqld
    /usr/local/mysql/bin/mysqld_safe --user=mysql &
    !ps
    /usr/local/mysql/bin/mysql -uroot -p123 test
    show slave status\G
    看到
    slave_io_running:yes
    slave_sql_running:yes
    表示成功

测试查询
slave上的php网页
<?php
//连master
if(mysql_connect("192.168.10.1","user1","456")){
    //echo "success";
    mysql_select_db("test");
    $sql="select id from t1";
    $rst=mysql_query($sql);
    while($row=mysql_fetch_assoc($rst)){
        print_r($row);
    }
}
//连自己
if(mysql_connect("local","root","123")){
    //echo "success";
    mysql_select_db("test");
    $sql="select id from t1";
    $rst=mysql_query($sql);
    while($row=mysql_fetch_assoc($rst)){
        print_r($row);
    }
}
结果都一样，master的数据更新，slave同时更新

start slave
stop slave
show slave status
show master logs
change master to    手动即时改变master配置
show processlist    查看数据库运行进程

============================================================================


分区技术
优化主要2种方法
1 大表拆小表方式
2 SQL语句优化，增加索引等来调整，数据量增大索引维护代价也增大

一般在空闲时间对大表，超过1000万行，进行索引操作


垂直分表
    按照列分表
    分表使用相同主键列
    表的列一般就不多，所以用的不多
    
一般更重要的是水平分表
    物理上切分表
    逻辑上是同一个表

4种分区类型，
    RANGE分区，基于属于一个给定连续区间的列值，把多行分配给分区
    LIST分区，类似按RANGE分区，区别在于LIST分区基于列值匹配一个离散值集合中的某个值来进行选择
    HASH分区，基于用户定义的表达式的返回值来进行选择的分区，
        该表达式使用将要插入到表中的这些行的列值进行计算，
        这个函数可以包含MySQL中的有效的，
        产生非负整数值得任何表达式，主要用来做分区测试
    KEY分区，类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供自身的哈西函数
    
最多用的是RANGE，然后是LIST，再是HASH



RANGE分区，
假定你创建了一个表，该表保存有20家音像店的职员记录
这20家音像店编号从1到20，如果你想将其分成4个小分区
可以采用Range分区

按照store_id数值范围分区
create table employees(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated data not null defaut '9999-12-31',
job_code int not null,
store_id int not null
)
partition by range (store_id) (
partition p0 values less than (6),
partition p1 values less than (11),
partition p2 values less than (16),
partition p3 values less than (21)
);

按照离职日期年份分区
create table employees(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated data not null defaut '9999-12-31',
job_code int not null,
store_id int not null
)
partition by range (YEAR(separated)) (
partition p0 values less than (1991),
partition p1 values less than (1996),
partition p2 values less than (2001),
partition p3 values less than MAXVALUE
);

硬件上会分成10个数据文件
每个分区2个数据文件，还有表结构文件,分区文件等


LIST分区
与Range分区类似，
假定创建表，保存有20家音像店的职员记录，
这20家店的编号从1到20
分布在4个有经销权的地区
北区    3,5,6,9,17
东区    1,2,10,11,19,20
西区    4,12,13,14,18
中心区    7,8,15,16

create table employees(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated data not null defaut '9999-12-31',
job_code int ,
store_id int
)
partition by list (store_id) (
partition pNorth values in (3,5,6,9,17),
partition pEast values in (1,2,10,11,19,20),
partition pWest values in (4,12,13,14,18),
partition pCentral values in (7,8,15,16)
);




HASH分区，
用来确保数据在预先确定数目的分区中平均分布
可以基于用户定义的表达式的返回值来进行分区
常用来方便的测试分区
假定创建表，保存有20家音像店的职员记录，
店的编号从1到20， 想把不同时期加入的员工进行分别存储
可以将日期字段hired，作为一个key

create table employees(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated data not null defaut '9999-12-31',
job_code int ,
store_id int
)
partition by hash (YEAR(hired)) (
partitions 4
);


在我们的mysql安装路径下
usr/lib/mysql/var下可以看到数据库文件
usr/lib/mysql/var/test下，就是test数据库下的表数据文件
可以看到分区后的表的数据文件



show plugins看到有partition表示支持分区
show engines 查看默认表引擎

分区后查看数据文件
cd /usr/local/mysql/var/test
看到t2.frm, t2.par
t2#P#p1.MYD
t2#P#p1.MYI
t2#P#p2.MYD
t2#P#p2.MYI
t2#P#p3.MYD
t2#P#p3.MYI
t2#P#p4.MYD
t2#P#p4.MYI



分区实验
插入10000000条数据
先插入10条数据到t2
然后insert into t2 select * from t2;
2的倍数递增

linux中切换终端
ctlt + F1~ F6

watch命令，实时跟踪另一个命令的运行情况
cd /usr/local/mysql/var/test
watch -n1 ls -lh
查看/usr/local/mysql/var/test中分区文件情况
索引比数据文件还要大

对于count(*), mysql早就作了优化
所有放心用select count(*) from t2;
但是后面加了where语句，就慢了
count(id), count(1) 也一样


使用存储过程添加数据
\d //
create procedure p3()
begin
    set @i=1;
    while @i<=10000 do
    insert into t3 values(@i);
    set @i=@i+1;
    end while;
end //
\d ;

show procedure status;

call p3();

测试分区表的优势
create table part_tab(c1 int(11) default null, c2 varchar(30) default null, c3 date default null) engine=myisam
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 (2004),
partition p8 values less than (2003), partition p9 values less than (2006),
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) engine=myisam;

delimter //
create procedure load_part_tab();
begin
declare v int default 0;
while v<8000000
do
insert into part_tab
values(v,'test partitions',adddate('1995-01-01',(rand(v)*36520)mod3652));
set v=v+1;
end while;
end
//
delimiter;

call  load_part_tab();

跟踪cpu使用，top命令

select count(*) from part_tab where c3>date '1995-01-01' and c3<date'1995-12-31';
2秒
select count(*) from no_part_tab where c3>date '1995-01-01' and c3<date'1995-12-31';
9秒

desc select count(*) from part_tab where c3>date '1995-01-01' and c3<date'1995-12-31'\G
影响行数只有80万行，结果也是80万行所以快，因为根据date分区了
desc select count(*) from no_part_tab where c3>date '1995-01-01' and c3<date'1995-12-31'\G
全表查询，所以慢

当我们给未分区表的c3字段加了索引后
create index idx_of_c3 on on_part_tab(c3);
再作同样的查询， 速度也快了
增加未加索引字段后再查询，速度又慢了

innodb表数据结构
共享表空间
    所有表存在一个大文件中
    默认ibdata1，初始大小10M
独占表空间
    每个表生成独立文件
    每个表有一个frm表描述文件
    一个ibd文件，包含数据内容和索引内容

innodb要做分区，先要修改成独占表空间
vi /etc/my.cnf
innodb_data_home_dir=C:\mysql\data\
innodb_log_grou_home_dir=C:\mysql\data\
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
然后创建分区表t7
查看分区数据文件
t7.frm
t7.par
t7#P#p0.ibd
t7#P#p1.ibd
t7#P#p2.ibd
t7#P#p3.ibd
t7#P#p4.ibd