本地数据库与远程数据库更新 本地数据库是主机,要从几个分店(远程数据库)拿出来,分店的IP是已知的。目前的做法是在本地中把分店的数据生成一个TXT,然后在LOAD DATA进本地。想问下大家有什么方法可以不用通过TXT,直接更新? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 可以创建FEDERAL存储引擎,类似于TABLE LINK 到远程的分店数据库。(你的IP已知), 然后直接通过SQL语句进行操作。或者再创建MERGE存储引擎表。 另外就是直接使用数据库复制功能。利用MYSQL本身来实现数据复制。 如果你的数据只是多个表的话,考虑FEDERATED存储引擎,建立FEDERATED表,再更新主表在host: 168.56.41.11建个FEDERATED 表 CREATE TABLE xxx_b ( id INT(20) NOT NULL AUTO_INCREMENT, username VARCHAR(32) NOT NULL DEFAULT '', password VARCHAR(32), PRIMARY KEY (id) ) ENGINE=FEDERATED CONNECTION='mysql://broot:[email protected]:3306/dbname/b表'; sSql = "Drop table if exists P_purchaseorderdetail" lDCN.Execute sSql sSql = "Create Table P_purchaseorderdetail ( ID INTEGER NOT NULL AUTO_INCREMENT,Inventoryid INTEGER ,QOH integer," sSql = sSql & "PRIMARY KEY (ID),INDEX Inventoryid(Inventoryid) ) ENGINE = InnoDB DEFAULT CHARSET=utf8;" lDCN.Execute sSql sSql = "Insert into P_purchaseorderdetail(InventoryID,QOH)" sSql = sSql & " Select T.InventoryID,T.QOH " sSql = sSql & " From (" & sCommand & ")T " lDCN.Execute sSql '*************************** sCmd = "Drop table if exists P1_purchaseorderdetail" DCN.Execute sCmd sCmd = "Create Table P1_purchaseorderdetail ( ID INTEGER NOT NULL AUTO_INCREMENT,Inventoryid INTEGER ,QOH integer," sCmd = sCmd & " PRIMARY KEY (ID),INDEX Inventoryid(Inventoryid) )ENGINE = FEDERATED " sCmd = sCmd & " CONNECTION='mysql://broot:[email protected]:3306/masterdata/P_purchaseorderdetail'" DCN.Execute sCmd我这样设有问题吗?为什么在本地打不开P1_purchaseorderdetail表,说不可以连接。 不用你的程序,直接先在MYSQL命令行工具中试一下。另外FEDERAL存储引擎需要安装。 1、FEDERATED存储引擎安装没有;2、用户名、密码是否正确,是否有访问的权限。scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name只有mysql在这一点被支持为scheme,密码和端口号时可选的。 这里有一些连接字符串的例子: CONNECTION='mysql://username:password@hostname:port/database/tablename'CONNECTION='mysql://username@hostname/database/tablename'CONNECTION='mysql://username:password@hostname/database/tablename'为指定连接字符串使用CONNECTION是非可选,并且在将来可能会改变。当你使用FEDERATED表的时候,要记得这个,因为这意味着当将来发生那种改变之时,可能被要求。 因为任何被用的密码作为纯文本被存在连接字符串中,它可以被任何使对FEDERATED表使用SHOW CREATE TABLE或SHOW TABLE STATUS的用户,或者在INFORMATION_SCHEMA数据库中查询TABLES表的用户看见。 在MYSQL中运行sCmd中的内容,提示什么,能否成功 1、SHOW ENGINES;贴结果如果 有FEDERATED,则在MYSQLD中加入FEDERATED2、如果没有,在MYSQL中install plugin federated soname 'ha_federated.so';3、重新编译MYSQL。 mysql> show engines;+------------+---------+----------------------------------------------------------------+| Engine | Support | Comment |+------------+---------+----------------------------------------------------------------+| MyISAM | YES | Default engine as of MySQL 3.23 with great performance || MEMORY | YES | Hash based, stored in memory, useful for temporary tables || InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys || BerkeleyDB | NO | Supports transactions and page-level locking || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) || EXAMPLE | NO | Example storage engine || ARCHIVE | YES | Archive storage engine || CSV | NO | CSV storage engine || ndbcluster | NO | Clustered, fault-tolerant, memory-based tables || FEDERATED | YES | Federated MySQL storage engine || MRG_MYISAM | YES | Collection of identical MyISAM tables || ISAM | NO | Obsolete storage engine |+------------+---------+----------------------------------------------------------------+12 rows in set (0.00 sec)mysql>有了呀,为什么不可以? CREATE TABLE `a` ( `tid` INT(6) NOT NULL AUTO_INCREMENT, `subject` VARCHAR(20) DEFAULT NULL, UNIQUE KEY `tid` (`tid`), KEY `tt` (`tid`,`subject`)) ENGINE=FEDERATED CONNECTION='mysql://root:[email protected]:3306/ee/a'测试通过,检查你的用户名及密码,root:[email protected]引擎已经安装 在远程MYSQL上建立用户,假设aagrant all privileges on *.* to aa@'192.167.0.1' identified by "123";CREATE TABLE `a` ( `tid` INT(6) NOT NULL AUTO_INCREMENT, `subject` VARCHAR(20) DEFAULT NULL, UNIQUE KEY `tid` (`tid`), KEY `tt` (`tid`,`subject`)) ENGINE=FEDERATED CONNECTION='mysql://aa:[email protected]:3306/ee/a'ee:数据库名a:表名 本地MYSQL:CREATE TABLE `a` ( `tid` INT(6) NOT NULL AUTO_INCREMENT, `subject` VARCHAR(20) DEFAULT NULL, UNIQUE KEY `tid` (`tid`), KEY `tt` (`tid`,`subject`)) ENGINE=FEDERATED CONNECTION='mysql://aa:[email protected]:3306/ee/a'ee:数据库名a:表名 mysql 触发器支持并发更新吗? MySQL数据库InnoDB存储引擎源代码调试跟踪分析 mysql繁体字处理! 如何截取字符串运算更新数据库. 在postgresql下查询指定的行数 为了节省成本想使用MySQL,大家给点意见!(散分) 难题啊!各位高手请进 有谁能提供linux下的MySQL 3.23.49 的jdbc驱动? 要做一个在线答题的系统,关于数据库并发性问题的考虑,望各位大虾赐教! 玩过Postgresql的高手进来帮一下忙! innodb 快照原理 解惑。。跪求高手解答· 求教mysql查询超慢
id INT(20) NOT NULL AUTO_INCREMENT,
username VARCHAR(32) NOT NULL DEFAULT '',
password VARCHAR(32),
PRIMARY KEY (id)
)
ENGINE=FEDERATED
CONNECTION='mysql://broot:[email protected]:3306/dbname/b表';
lDCN.Execute sSql
sSql = "Create Table P_purchaseorderdetail ( ID INTEGER NOT NULL AUTO_INCREMENT,Inventoryid INTEGER ,QOH integer,"
sSql = sSql & "PRIMARY KEY (ID),INDEX Inventoryid(Inventoryid) ) ENGINE = InnoDB DEFAULT CHARSET=utf8;"
lDCN.Execute sSql
sSql = "Insert into P_purchaseorderdetail(InventoryID,QOH)"
sSql = sSql & " Select T.InventoryID,T.QOH "
sSql = sSql & " From (" & sCommand & ")T "
lDCN.Execute sSql
'***************************
sCmd = "Drop table if exists P1_purchaseorderdetail"
DCN.Execute sCmd
sCmd = "Create Table P1_purchaseorderdetail ( ID INTEGER NOT NULL AUTO_INCREMENT,Inventoryid INTEGER ,QOH integer,"
sCmd = sCmd & " PRIMARY KEY (ID),INDEX Inventoryid(Inventoryid) )ENGINE = FEDERATED "
sCmd = sCmd & " CONNECTION='mysql://broot:[email protected]:3306/masterdata/P_purchaseorderdetail'"
DCN.Execute sCmd
我这样设有问题吗?
为什么在本地打不开P1_purchaseorderdetail表,说不可以连接。
2、用户名、密码是否正确,是否有访问的权限。
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
只有mysql在这一点被支持为scheme,密码和端口号时可选的。 这里有一些连接字符串的例子: CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'
为指定连接字符串使用CONNECTION是非可选,并且在将来可能会改变。当你使用FEDERATED表的时候,要记得这个,因为这意味着当将来发生那种改变之时,可能被要求。 因为任何被用的密码作为纯文本被存在连接字符串中,它可以被任何使对FEDERATED表使用SHOW CREATE TABLE或SHOW TABLE STATUS的用户,或者在INFORMATION_SCHEMA数据库中查询TABLES表的用户看见。
贴结果
如果 有FEDERATED,则在MYSQLD中加入
FEDERATED
2、如果没有,在MYSQL中
install plugin federated soname 'ha_federated.so';
3、重新编译MYSQL。
mysql> show engines;
+------------+---------+--------------------------------------------------------
--------+
| Engine | Support | Comment
|
+------------+---------+--------------------------------------------------------
--------+
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance
|
| MEMORY | YES | Hash based, stored in memory, useful for temporary tabl
es |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign k
eys |
| BerkeleyDB | NO | Supports transactions and page-level locking
|
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disa
ppears) |
| EXAMPLE | NO | Example storage engine
|
| ARCHIVE | YES | Archive storage engine
|
| CSV | NO | CSV storage engine
|
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables
|
| FEDERATED | YES | Federated MySQL storage engine
|
| MRG_MYISAM | YES | Collection of identical MyISAM tables
|
| ISAM | NO | Obsolete storage engine
|
+------------+---------+--------------------------------------------------------
--------+
12 rows in set (0.00 sec)
mysql>
有了呀,为什么不可以?
`tid` INT(6) NOT NULL AUTO_INCREMENT,
`subject` VARCHAR(20) DEFAULT NULL,
UNIQUE KEY `tid` (`tid`),
KEY `tt` (`tid`,`subject`)
) ENGINE=FEDERATED
CONNECTION='mysql://root:[email protected]:3306/ee/a'测试通过,检查你的用户名及密码,root:[email protected]
引擎已经安装
grant all privileges on *.* to aa@'192.167.0.1' identified by "123";CREATE TABLE `a` (
`tid` INT(6) NOT NULL AUTO_INCREMENT,
`subject` VARCHAR(20) DEFAULT NULL,
UNIQUE KEY `tid` (`tid`),
KEY `tt` (`tid`,`subject`)
) ENGINE=FEDERATED
CONNECTION='mysql://aa:[email protected]:3306/ee/a'
ee:数据库名
a:表名
CREATE TABLE `a` (
`tid` INT(6) NOT NULL AUTO_INCREMENT,
`subject` VARCHAR(20) DEFAULT NULL,
UNIQUE KEY `tid` (`tid`),
KEY `tt` (`tid`,`subject`)
) ENGINE=FEDERATED
CONNECTION='mysql://aa:[email protected]:3306/ee/a'
ee:数据库名
a:表名