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，数据库分库实现
<schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100">
<table name="company" primaryKey="ID" dataNode="dn3,dn2,dn1" rule="mod-long"/>
<table name="order_cart" primaryKey="cart_id" dataNode="dn_orderdb" />
<table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="dn_orderdb" />
<table name="order_detail" primaryKey="order_detail_id" dataNode="dn_orderdb" />
<table name="order_master" primaryKey="order_id" dataNode="dn_orderdb" />
<table name="region_info" primaryKey="region_id" dataNode="dn_orderdb" />
<table name="serial" primaryKey="id" dataNode="dn_orderdb" />
<table name="shipping_info" primaryKey="ship_id" dataNode="dn_orderdb" />
<table name="warehouse_info" primaryKey="w_id" dataNode="dn_orderdb" />
<table name="warehouse_proudct" primaryKey="wp_id" dataNode="dn_orderdb" />
<table name="product_brand_info" primaryKey="brand_id" dataNode="dn_productdb" />
<table name="product_category" primaryKey="category_id" dataNode="dn_productdb" />
<table name="product_comment" primaryKey="comment_id" dataNode="dn_productdb" />
<table name="product_info" primaryKey="product_id" dataNode="dn_productdb" />
<table name="product_pic_info" primaryKey="product_pic_id" dataNode="dn_productdb" />
<table name="product_supplier_info" primaryKey="supplier_id" dataNode="dn_productdb" />
<table name="customer_balance_log" primaryKey="balance_id" dataNode="dn_customerdb" />
<table name="customer_inf" primaryKey="customer_inf_id" dataNode="dn_customerdb" />
<table name="customer_level_inf" primaryKey="customer_level" dataNode="dn_customerdb" />
<table name="customer_login" primaryKey="customer_id" dataNode="dn_customerdb" />
<table name="customer_login_log" primaryKey="login_id" dataNode="dn_customerdb" />
<table name="customer_point_log" primaryKey="point_id" dataNode="dn_customerdb" />
</schema>
<dataNode name="dn_orderdb" dataHost="mysql10142" database="order_db" />
<dataNode name="dn_productdb" dataHost="mysql10143" database="product_db" />
<dataNode name="dn_customerdb" dataHost="mysql10144" database="customer_db" />
<dataHost name="mysql10142" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="172.16.10.142" url="172.16.10.142:3306" user="bm_mycat" password="123456">
</writeHost>
</dataHost>
<dataHost name="mysql10143" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="172.16.10.143" url="172.16.10.143:3306" user="bm_mycat" password="123456">
</writeHost>
</dataHost>
<dataHost name="mysql10144" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="172.16.10.144" url="172.16.10.144:3306" user="bm_mycat" password="123456">
</writeHost>
</dataHost>验证通过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数据库方式一模一样，应用程序不需要做任何更改。只需要改变数据库链接地址，及用户口令即可。
