Mycat基础知识和运用总结

mysql>select * fromtt_waybill where id =12330;

12330 % 3 = 0 –> DB1

mysql>select * fromtt_waybill where waybill_no =88661;

DB1 –> select * fromtt_waybill where waybill_no =88661; 
DB2 –> select * from tt_waybill where waybill_no =88661; 
DB3 –> select * from tt_waybill where waybill_no =88661;

DB1：[0,1,2,3,4,10,11,12,13,14] 
DB2：[5,6,7,8,9,16,17,18,19] 
DB3：[20,21,22,23,24,25,26,27,28,29]

mysql>select * fromtable limit 2;

DB1: [0,1] 
DB2: [5,6] 
DB3: [20,21]

mysql>select * fromtable orderby id limit 2;

mysql>select * fromtable order by id limit 5,2;

mysql>select * fromtable order by id limit 0,7;

DB1: [0,1,2,3,4,10,11] 
DB2: [5,6,7,8,9,16,17] 
DB3: [20,21,22,23,24,25,26]

mysql>selectp_name,t_name from player p, team t where p.no = 3 and p.team_id = t.id;

mysql>begin;

服务器主机名       ip                  说明
Mycat-node       192.168.10.210      mycat服务器，连接数据库时，连接此服务器
Mysql-node1      192.168.10.205      物理数据库1，真正存储数据的数据库,这里为Master主数据库
Mysql-node2      192.168.10.206      物理数据库2，真正存储数据的数据库,这里为Slave主数据库

三台机器分布修改主机名，并做hosts绑定
# vim /etc/hosts
192.168.10.205 Mysql-node1
192.168.10.206 Mysql-node1
192.168.10.210 Mycat-node

为方便做实验，关闭三台机器的iptables防火墙和selinux 

a）安装Mysql，安装过程要在两个Mysql节点机器上都要操作。
安装过程参考：http://www.cnblogs.com/kevingrace/p/6109679.html
这里省去安装过程～～

b）Mysql主从复制（两个节点的mysql登录用户名和密码都是root/123456）
参考：http://www.cnblogs.com/kevingrace/p/6256603.html

====================================================
首先是Mysql-node1主节点配置操作
[root@Mysql-node1 ~]# cp /usr/local/mysql/my.cnf /usr/local/mysql/my.cnf.bak
[root@Mysql-node1 ~]# vim /usr/local/mysql/my.cnf        #在[mysqld]区域添加下面内容
......
[mysqld]
server-id=1        
log-bin=mysql-bin  
#binlog-do-db=kevin         #需要同步的数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）         
binlog-ignore-db = mysql,information_schema
sync_binlog = 1      
binlog_checksum = none  
binlog_format = mixed   

重启mysql服务
[root@Mysql-node1 ~]# /etc/init.d/mysql restart

登录mysql，授予slave从机复制权限
mysql> grant replication slave,replication client on *.* to slave@'192.168.10.206' identified by "slave@123";
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

授权之后，要保证192.168.10.206这台slave节点机器能使用上面的权限信息登录到本机的mysql

将数据库锁住，仅仅允许读，以保证数据一致性；
mysql> FLUSH TABLES WITH READ LOCK;                 #注意，锁定后，如果自己同步对方数据，同步前一定要记得先解锁！
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)

查看主节点的master复制信息
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 |     1349 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)


====================================================
接着是slave从节点操作
[root@Mysql-node2 ~]# cp /usr/local/mysql/my.cnf /usr/local/mysql/my.cnf.bak
[root@Mysql-node2 ~]# vim /usr/local/mysql/my.cnf
.......
[mysqld]
.......
server-id=2   
log-bin=mysql-bin   
#replicate-do-db=kevin    #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。  
replicate-ignore-db=mysql  
slave-skip-errors = all 

重启mysql服务
[root@Mysql-node2 ~]# /etc/init.d/mysql restart

登录slave节点的mysql，进行主从同步设置
mysql> stop slave; 
Query OK, 0 rows affected (0.09 sec)

mysql> change  master to master_host='192.168.10.205',master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000003',master_log_pos=1349;  
Query OK, 0 rows affected, 2 warnings (0.21 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.205
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1349
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1349
              Relay_Log_Space: 456
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 747977ea-8fba-11e8-86c0-525400b19c93
             Master_Info_File: /data/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 


=========================================================
通过上面的信息，可知主从复制环境已经OK（Slave_IO_Running和Slave_SQL_Running状态均为YES），下面验证下主从复制是否正常？

在Mysql-node1主节点上操作
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;       
Query OK, 1 row affected (0.06 sec)

mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.34 sec)

在Mysql-node2从节点上查看（保证从节点上查看slave状态时，Slave_IO_Running和Slave_SQL_Running状态均为YES，这样就能保证主从复制在进行中）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevin              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use kevin;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

接着在Mysql-node1主节点插入数据
mysql> insert into kevin.haha values(1,"wangshibo"),(2,"linan"),(3,"zhangminmin");       
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

然后再在MYsql-node2从节点查看，如下发现已经同步过来了！
mysql> use kevin;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
+----+-------------+
3 rows in set (0.00 sec)

由此可见，Mysql的主从复制已经实现！