MySQL架构 | 多种方式登录

发表于

集群由一组服务器相互独立的服务器，构建的高性能与可靠性的服务器架构

MySQL读写分离

主从复制是读写分离的基础

主从复制原理

  1. 主服务器将修改的SQL语句写入到二进制（bin）日志中
  2. 从服务器中的IO线程通过授权账号读取二进制日志，将信息写入到中继日志中（relay）
  3. 从服务器中的SQL线程读取中继日志中的数据，然后将SQL语句执行，还原主服务器中的数据

实现主从复制

  1. 开启主服务器的二进制日志
    centos yum 安装MySQL配置文件路径：

    1
    2
    3
    log-bin=mysql-bin
    binlog_format=mixed
    server-id=101

  2. 重启主服务器MySQL
    service mysqld restart

  3. 添加授权账号

    1
    2
    3
    4
    5
    [root@localhost etc]# mysql -uroot -p
    Enter password:
    mysql> GRANT REPLICATION SLAVE ON *.* TO repuser@"192.168.%.%" IDENTIFIED BY '123456'; 
    Query OK, 0 rows affected (0.00 sec)
    #''引号内是授权账号的密码

  4. 在主服务器下刷新用户权限
    mysql> FLUSH PRIVILEGES;

  5. 查看主服务器状态

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      326 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    File ：表示正在使用二进制文件
    Position：二进制当前所在的位置

  6. 修改从服务器mysql配置项

    1
    2
    [root@localhost ~]# vim /etc/my.cnf
    server-id       = 102

  7. 重启从服务器的mysql服务
    [root@localhost ~]# service mysqld restart

  8. 配置从服务器通过授权账号读取主服务器信息

    1
    2
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.34.101',MASTER_USER='repuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=326;
    Query OK, 0 rows affected (0.07 sec)

  9. 在从服务器上开启主从同步数据

    1
    2
    mysql> slave start;
    Query OK, 0 rows affected (0.00 sec)

  10. 查看主从复制的状态

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.34.101
                      Master_User: repuser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 326
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 251
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

  11. 成功
    在主服务器上建表，写入数据，查看重服务的数据表跟数据是否与主服务一样
    slave

    slave_io_running与slave_sql_running要求都为yes则表示成功
    可以使用 slave stop关闭主从，使用reset slave 可以清空主从信息

从服务器配置参数

CHANGE MASTER TO
MASTER_HOST=’192.168.81.128’,//指定主服务器的地址
MASTER_USER=’repuser’,//指定授权账号的用户名
MASTER_PASSWORD=’123456’,//指定授权账号的密码
MASTER_LOG_FILE=’mysql-bin.000001’,//指定主服务的正在使用的二进制文件
MASTER_LOG_POS=326;//指定主服务器当前记录到的SQL语句的文字

开启bin日志，相关配置文件

log-bin=mysql-bin 开启二进制日志，并且名称以mysql-bin开头
binlog-format=mixed 指定二进制文件记录sql语句的格式。statement：用一条sql语句记录修改。row：表示记录每一条sql语句。mixed：表示混合使用由mysql自己决定
server-id = 101 表示主从服务器每台的标识各不一样
datadir=/var/lib/mysql
mysql-bin.index //索引文件，列出当前有哪些二进制文件
mysql-bin.000001//记录修改的SQL语句
bin日志，只记录修改表数据的SQL语句：inser、delete、update

读写分离原理

判断sql语句，如果是insert、update、delete类型sql语句则直接连接主服务器修改数据；如果是select语句则从slave服务器中选择一台进行查询。

实现读写分离

ThinkPHP实现读写分离

  1. 修改配置项

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    'DB_DEPLOY_TYPE'        =>  1, // 数据库部署方式:0 集中式(单一服务器),1 分布式(主从服务器)
    'DB_RW_SEPARATE'        =>  true,       // 数据库读写是否分离 主从式有效
    'DB_MASTER_NUM'         =>  1, // 读写分离后 主服务器数量
    'DB_SLAVE_NO'           =>  '', // 指定从服务器序号
    /* 数据库设置 */
    'DB_TYPE'               =>  'mysql',     // 数据库类型
    'DB_HOST'               =>  '192.168.34.101,192.168.34.102', // 服务器地址
    'DB_NAME'               =>  'test',          // 数据库名
    'DB_USER'               =>  'root,root',      // 用户名
    'DB_PWD'                =>  '123456,123456',          // 密码
    'DB_PORT'               =>  '3306,3306',        // 端口
    'DB_PREFIX'             =>  '',    // 数据库表前缀

  2. 代码书写

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    public function read(){
    	$model = M('slave');
    	$data = $model->select();
    	dump($data);
    }
    public function write(){
    	$model = M('slave');
    	$data=array('name'=>'dumin','age'=>'18','sex'=>'女');
    	$res = $model -> add($data);
    	dump($res);
    }

分库分表

垂直分表

  • 冷热字段区分

水平分表

  • 逻辑分表 MySQL内部完成
  • 物理分表 可通过主键取余、MD5加密数据字母与数据分表等

逻辑分表

key：取模算法
hash：哈希算法
range：某一个范围
list：某些值得中间

创建语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table 表名(
具体某些字段信息
)engine=myisam default charset=utf8
partition by key(分表字段) partitions num;

create table 表名(
具体某些字段信息
)engine=myisam default charset=utf8
partition by hash(分表字段) partitions num;

create table 表名(
具体某些字段信息
)engine=myisam default charset=utf8
partition by range(字段)(
 partition 别名 values less than (常量)
);

create table 表名(
具体某些字段信息
)engine=myisam default charset=utf8
partition by list(字段)(
 partition 别名 values in (列表)
);

添加语法

1
2
3
4
5
6
7
求余算法：alter table 表名 add partition partitions 5;
条件算法：  
alter table 表名 add partition(
    partition 名称 values less than (常量)
    或
   partition 名称 in (n,n,n)
);

分表与多方式登录数据表

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
#创建一个不重复的ID
create table blog_people_uid(
	uid int not null auto_increment comment '用户唯一id',
	primary key(uid)
)engine=myisam default charset=utf8;

create table blog_people(
	id int not null auto_increment comment '用户表id',
	uid int not null default 0 comment '用户唯一id',
	name char(24) not null default '' comment '用户名',
	pwd char(32) not null default '' comment '密码',
	tel char(11) not null default '' comment '手机号码',
	email char(30) not null default '' comment '电子邮件',
	primary key(id),
	unique key (uid),
	unique key (name),
	unique key (tel),
	unique key (email)
)engine=myisam default charset=utf8;

create table blog_people_index(
	id int not null auto_increment comment '登录检索表索引',
	uid int not null default 0 comment '检索用户id',
	name char(30) not null default '' comment '检索用户信息',
	primary key(id)
)engine=myisam default charset=utf8;

laravel 源码

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
public function register(){
    if($input = Input::all());
    // 定义验证规则
    $rules = [
        'pwd' => 'required|between:6,20',
        'name' => 'alpha_num',
        'name' => 'unique:people,tel',
        'email' => 'email',
        'tel' => 'integer',
    ];
    // 提示信息
    $message = [
        'name.alpha_num' => '用户名只能是字符和数字',
        'name.unique' => '该用户已经存在请重新输入',
        'email.email' => '请输入合法邮箱',
        'tel.integer' => '请输入合法手机号码',
        'pwd.required' => '密码不能为空',
        'pwd.between'  => '密码必须在6-20位之间',
    ];
    $validator = Validator::make($input,$rules,$message);
    if($validator->passes()){//验证规则通过
        $pwd   = $input['pwd'];
        $tel   = $input['tel'];
        $email = $input['email'];
        // 检查用户名是否存在
        $name = People::where('name','=',$input['name'])->first();
        $tel = People::where('tel','=',$input['tel'])->first();
        $email = People::where('email','=',$input['email'])->first();
        if($name!=null){
            $data = array(
            'sign' => 0,
            'code' => 'register error',
            'msg' => '该用户名已经存在，请返回登录或重新注册！'
             );
            return json_encode($data);
        }
        if($tel!=null){
            $data = array(
            'sign' => 0,
            'code' => 'register error',
            'msg' => '该手机已经注册，请返回登录或重新注册！'
             );
            return json_encode($data);
        }
        if($email!=null){
            $data = array(
            'sign' => 0,
            'code' => 'register error',
            'msg' => '该邮箱已经存在，请返回登录或重新注册！'
             );
            return json_encode($data);
        }
        $uid = DB::table('people_uid')->insertGetId(array());
        $input['uid']=$uid;
        $input['pwd'] = Crypt::encrypt($input['pwd']);
        DB::table('people')->insert($input);
        DB::table('people_index')->insert(array('uid'=>$uid,'name'=>$input['name']));
        DB::table('people_index')->insert(array('uid'=>$uid,'name'=>$input['tel']));
        $res = DB::table('people_index')->insert(array('uid'=>$uid,'name'=>$input['email']));
        if($res){
            // 成功
            $data = array(
                'sign' => 1,
                'code' => 'register success',
                'msg' => '注册成功！'
            );
            echo json_encode($data);exit();
        }else{
            // 入库失败
            $data = array(
                'sign' => 0,
                'code' => 'insert error',
                'msg' => '入库失败请联系管理员！'
            );
            echo json_encode($data);exit();
        }
    }else{
        // 服务器端验证规则
        $messages = $validator->messages();
        $data = array(
            'sign' => 0,
            'code' => 'insert error',
            'msg' => $messages->first(),
        );
        echo json_encode($data);exit();
    }
}
?>