mysql mycat水平分库_mycat垂直分库

mycat垂直分库

数据库的主从复制

MySQL备份源数据库并记录事务点

# master导出单个库的备份

SET GLOBAL log_timestamps = SYSTEM;(

/usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost -P3306 --socket=/usr/local/mysql/mysql.sock --single-transaction --master-data=2 imooc_db --triggers --routines --events > /tmp/all.sql

源数据库中建立从库(复制用户)

grant replication slave on *.* to 'repl'@'%' identified by '123456';

在新的数据库实例上恢复数据库，并修改数据库名称。

mysql -uroot -p123456 -e"create database order_db";

mysql -uroot -p123456 order_db < /tmp/all.sql

mysql -uroot -p123456 -e"create database product_db";

mysql -uroot -p123456 product_db < /tmp/all.sql

mysql -uroot -p123456 -e"create database customer_db";

mysql -uroot -p123456 customer_db < /tmp/all.sql

######################################

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty

Query OK, 0 rows affected (0.01 sec)

root@localhost 23:55: [(none)]> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000001 | 154 | | | |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

root@localhost 23:55: [(none)]> show master status;

+---------------+----------+--------------+------------------+-------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------------------------------+

| binlog.000001 | 154 | | | ad083f85-9264-11e8-9e91-005056a3fe0a:1-85 |

+---------------+----------+--------------+------------------+-------------------------------------------+

1 row in set (0.00 sec)

在新实例数据库上配置复制链路

\help change master to

CHANGE MASTER TO

MASTER_HOST='172.16.10.141',

MASTER_USER='repl',

MASTER_PASSWORD='123456',

MASTER_PORT=3306,

master_auto_position=1;

\help change replication filter

CHANGE REPLICATION FILTER

REPLICATE_REWRITE_DB = ((imooc_db, order_db));

CHANGE REPLICATION FILTER

REPLICATE_REWRITE_DB = ((imooc_db, product_db));

CHANGE REPLICATION FILTER

REPLICATE_REWRITE_DB = ((imooc_db, customer_db));

start slave;

# 主库插入数据验证

root@localhost 00:12: [imooc_db]> insert into region_info(parent_id,region_name,region_level) values(1,'guangdong',2);

# 从库查看数据

root@localhost 00:13: [order_db]> select * from region_info;

+-----------+-----------+-------------+--------------+

| region_id | parent_id | region_name | region_level |

+-----------+-----------+-------------+--------------+

| 1 | 1 | guangdong | 2 |

+-----------+-----------+-------------+--------------+

1 row in set (0.00 sec)

# 其他2个也这么同步

配置mycat垂直分库

配置schema.xml，需要的权限

grant select,insert,update,delete on *.* to 'bm_mycat'@'%' identified by '123456';

配置schema.xml，数据库分库实现

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

show slave status

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

show slave status

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

show slave status

验证通过mycat访问db

使用命令行访问方式

[root@mycat01 logs]# mysql -uapp_imooc -p123456 -P8066

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'app_imooc'@'localhost' (using password: YES)

[root@mycat01 logs]# mysql -uapp_imooc -p123456 -P8066 -h172.16.10.142

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.8-mycat-1.5.1-RELEASE-20160525110043 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

app_imooc@172.16.10.142 01:59: [(none)]> show databases;

+----------+

| DATABASE |

+----------+

| imooc_db |

+----------+

1 row in set (0.00 sec)

app_imooc@172.16.10.142 01:59: [(none)]> use imooc_db;

Database changed

app_imooc@172.16.10.142 01:59: [imooc_db]> show tables;

+-----------------------+

| Tables in imooc_db |

+-----------------------+

| customer_balance_log |

| customer_inf |

| customer_level_inf |

| customer_login |

| customer_login_log |

| customer_point_log |

| order_cart |

| order_customer_addr |

| order_detail |

| order_master |

| product_brand_info |

| product_category |

| product_comment |

| product_info |

| product_pic_info |

| product_supplier_info |

| region_info |

| serial |

| shipping_info |

| warehouse_info |

| warehouse_proudct |

+-----------------------+

21 rows in set (0.01 sec)

app_imooc@172.16.10.142 01:59: [imooc_db]>

使用程序访问方式：

与java访问MySQL数据库方式一模一样，应用程序不需要做任何更改。只需要改变数据库链接地址，及用户口令即可。