凤凰平台注册开户_凤凰彩票app下载安装_凤凰彩票投注网

热门关键词: 凤凰平台注册开户,凤凰彩票app下载安装,凤凰彩票投注网

凤凰彩票投注网falcon存储引擎入门,跨实例访问

作者: MySQL数据库  发布:2019-11-24

跨数据库服务器,跨实例访谈是比较不以为奇的风度翩翩种访谈方式,在Oracle中能够通过DB LINK的情势来兑现。对于MySQL来说,有一个FEDERATED存款和储蓄引擎与之相呼应。相符也是透过创设三个链接情势的款式来拜候远程服务器上的数据。本文简要描述了FEDERATED存款和储蓄引擎,以至演示了根据FEDERATED存款和储蓄引擎跨实例访谈的亲自去做。

MySQL在6.0中新添了三个囤积引擎falcon,普通话翻译为猎鹰,那几个蕴藏引擎的支付显著和innodb被oracle收购相关,大概在不久的现在,falcon将会完全会替代innodb。
在 mysql5.0和5.第11中学,辅助职业的蕴藏引擎有三种,是innodb和NDB(mysql cluster卡塔 尔(英语:State of Qatar)中应用,个中,ndb能够十分轻松得达到99.999%的可用性和超越10000 tps的高质量;而innodb主要利用在单服务器上还是是master/slave格局下。falcon和innodb在无数特性上周围,然而她们的架构分裂,本性上也会有广大莫衷一是的地方,如下所示:

1、FEDERATED存储引擎的描述

falcon和innodb的基本点区别如下:

FEDERATED存款和储蓄引擎允许在不选用复制或集群本事的气象下促成远程访谈数据库 创造基于FEDERATED存款和储蓄引擎表的时候,服务器在数据库目录仅创造多少个表定义文件,即以表名开始的.frm文件。

  1. falcon不使用cluster索引
  2. falcon不帮忙read uncommited 隔绝品级
  3. falcon不补助语句级复制,那样是为了保障日志和数据文件的大器晚成致性。
  4. falcon协理表空间的概念,innodb唯有数据文件概念,不过多少和索引表空间不能够分别。
  5. falcon接济在线备份,可是备份工具要到6.1的时候才会宣布。

FEDERATED存款和储蓄引擎表无别的数据存款和储蓄到本地,即未有.myd文件 对于远程服务器上表的操作与本地球表面操作相仿,仅仅是多少位于远程服务器 基本流程如下:

falcon在6。0以后被辅助,要想查看当前版本是或不是帮助falcon,能够用如下命令:
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO  | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| Falcon     | YES     | Falcon storage engine                                          | YES          | NO  | YES        |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set (0.01 sec)

2、安装与启用FEDERATED存款和储蓄引擎

始建falcon的表超轻松,和开创其余表雷同:
mysql> CREATE TABLE tbl1 (id INTEGER AUTO_INCREMENT PRIMARY KEY, value VARCHAR(30)) ENGINE=Falcon;
Query OK, 0 rows affected (0.19 sec)

源码安装MySQL时行使DWITH_FEDERATED_STORAGE_ENGINE来配置 rpm安装方式缺省情况下已安装,只需求启用该意义就可以

mysql> show create table tbl1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1  | CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=Falcon DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、计划远程服务器情形

当然,如若期望创建的表暗中同意就是falcon存款和储蓄引擎,可以在my.cnf中步入“default-storage-engine=Falcon”。
大家测验一下政工:
mysql> insert into tbl1 (value) values(1);
Query OK, 1 row affected (0.01 sec)

复制代码 代码如下:-- 此演示中国远洋运输总集团程服务器与本土服务器为相同服务器上的多版本多实例 -- 假定远程服务为:5.6.12 -- 假定本地服务器:5.6.21 -- 基于实例3306开立FEDERATED存款和储蓄引擎表test.federated_engine以到达访谈实例3406数据库tempdb.tb_engine的目的 [root@rhel64a ~]# cat /etc/issue Red Hat Enterprise Linux Server release 6.4 --启动3406的实例 [root@rhel64a ~]# /u01/app/mysql/bin/mysqld_multi start 3406 [root@rhel64a ~]# mysql -uroot -pxxx -P3406 --protocol=tcp root@localhost[]> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 3406 | +---------------+-------+ --实例3406的版本号 root@localhost[tempdb]> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.6.12-log | +---------------+------------+ --创设数据库 root@localhost[]> create database tempdb; Query OK, 1 row affected -- Author : Leshami -- Blog : root@localhost[]> use tempdb Database changed --成立用于访谈的表 root@localhost[tempdb]> create table tb_engine as -> select engine,support,comment from information_schema.engines; Query OK, 9 rows affected Records: 9 Duplicates: 0 Warnings: 0 --提取表的SQL语句用于创制为FEDERATED存款和储蓄引擎表 root@localhost[tempdb]> show create table tb_engine G *************************** 1. row *************************** Table: tb_engine Create Table: CREATE TABLE `tb_engine` NOT NULL DEFAULT '', `support` varchar NOT NULL DEFAULT '', `comment` varchar NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHAWranglerSET=utf8 --创建用于远程访谈的账户 root@localhost[tempdb]> grant all privileges on tempdb.* to 'remote_user'@'192.168.1.131' identified by 'xxx'; Query OK, 0 rows affected root@localhost[tempdb]> flush privileges; Query OK, 0 rows affected

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

4、演示FEDERATED存储引擎跨实例访问

mysql> select * from tbl1;
Empty set (0.00 sec)

复制代码 代码如下:[root@rhel64a ~]# mysql -uroot -pxxx root@localhost[]> show variables like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.6.21 | +---------------+--------+ #翻看是或不是扶植FEDERATED引擎 root@localhost[]> select * from information_schema.engines where engine='federated'; +-----------+---------+--------------------------------+--------------+------+------------+ | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | +-----------+---------+--------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +-----------+---------+--------------------------------+--------------+------+------------+ root@localhost[]> exit [root@rhel64a ~]# service mysql stop Shutting down MySQL..[ OK ] #布置启用FEDERATED引擎 [root@rhel64a ~]# vi /etc/my.cnf [root@rhel64a ~]# tail -7 /etc/my.cnf [mysqld] socket = /tmp/mysql3306.sock port = 3306 pid-file = /var/lib/mysql/my3306.pid user = mysql server-id=3306/ federated #增加该选项 [root@rhel64a ~]# service mysql start Starting MySQL.[ OK ] [root@rhel64a ~]# mysql -uroot -pxxx root@localhost[]> select * from information_schema.engines where engine='federated'; +-----------+---------+--------------------------------+--------------+------+------------+ | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | +-----------+---------+--------------------------------+--------------+------+------------+ | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | +-----------+---------+--------------------------------+--------------+------+------------+ root@localhost[]> use test -- 成立基于FEDERATED引擎的表federated_engine root@localhost[test]> CREATE TABLE `federated_engine` ( -> `engine` varchar NOT NULL DEFAULT '', -> `support` varchar NOT NULL DEFAULT '', -> `comment` varchar NOT NULL DEFAULT '' -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 -> CONNECTION='mysql://remote_user:xxx@192.168.1.131:3406/tempdb/tb_engine'; Query OK, 0 rows affected -- 下边是创建后表格式文件 root@localhost[test]> system ls -hltr /var/lib/mysql/test total 12K -rw-rw---- 1 mysql mysql 8.5K Oct 24 08:22 federated_engine.frm --查询表federated_engine root@localhost[test]> select * from federated_engine limit 2; +------------+---------+---------------------------------------+ | engine | support | comment | +------------+---------+---------------------------------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | | CSV | YES | CSV storage engine | +------------+---------+---------------------------------------+ --更新表federated_engine root@localhost[test]> update federated_engine set support='NO' where engine='CSV'; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 --查看更新后的结果 root@localhost[test]> select * from federated_engine where engine='CSV'; +--------+---------+--------------------+ | engine | support | comment | +--------+---------+--------------------+ | CSV | NO | CSV storage engine | +--------+---------+--------------------+

falcon的表空间管理:
对于一个falcon表的蕴藏,涉及到了3个表空间的,分别是:

5、成立FEDERATED引擎表的链接格局

  • 三个未命名的表空间用来囤积系统表。
  • falcon_user 用来积累表数据的私下认可地方
  • falcon_temporary 用来积累临时表

复制代码 代码如下:scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point. user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT,UPDATE, and so forth) on the remote table. password: The corresponding password for user_name. host_name: The host name or IP address of the remote server. port_num: The port number for the remote server. The default is 3306. db_name: The name of the database holding the remote table. tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.链接示例样板: CONNECTION='mysql://username:password@hostname:port/database/tablename' CONNECTION='mysql://username@hostname/database/tablename' CONNECTION='mysql://username:password@hostname/database/tablename'

万目睽睽,大家适逢其会创制的测验表tbl1应该放在falcon_user表空间,能够用相关的视图举办查看:
mysql> select * from FALCON_TABLES;
+-------------+------------+-----------+-------------+---------------+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE  | INTERNAL_NAME |
+-------------+------------+-----------+-------------+---------------+
| TEST        | TBL1       |           | FALCON_USER | TBL1          |
+-------------+------------+-----------+-------------+---------------+

大家也得以创立本身的表空间,命令如下:
mysql> create tablespace tbs2 add datafile 'data02' engine=falcon;
Query OK, 0 rows affected (0.13 sec)

从系统表中能够查到持有的表空间:
mysql> select * from FALCON_TABLESPACES;
+------------------+------------------+---------+
| TABLESPACE_NAME  | TYPE             | COMMENT |
+------------------+------------------+---------+
| FALCON_USER      | FALCON_USER      |         |
| FALCON_TEMPORARY | FALCON_TEMPORARY |         |
| tb1              | USER_DEFINED     |         |
| tbs2             | USER_DEFINED     |         |
+------------------+------------------+---------+
4 rows in set (0.02 sec)

从操作系统上看,数据目录下多了二个data02的数据文件,size为16k。大家也能够在新的表空间上创制表,命令如下:
mysql> create table tbl2 (id int) tablespace tbs2  engine=falcon;
Query OK, 0 rows affected (0.06 sec)

系统表中定义如下:
mysql> select * from FALCON_TABLES;    
+-------------+------------+-----------+-------------+---------------+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE  | INTERNAL_NAME |
+-------------+------------+-----------+-------------+---------------+
| TEST        | TBL1       |           | FALCON_USER | TBL1          |
| TEST        | TBL2       |           | tbs2        | TBL2          |
+-------------+------------+-----------+-------------+---------------+
2 rows in set (0.00 sec)

用alter table命令能够将表移到别的的表空间中,比方:
mysql> alter table tbl2 tablespace tb1;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> use information_schema 
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> select * from FALCON_TABLES;
+-------------+------------+-----------+-------------+---------------+
| SCHEMA_NAME | TABLE_NAME | PARTITION | TABLESPACE  | INTERNAL_NAME |
+-------------+------------+-----------+-------------+---------------+espace
| TEST        | TBL1       |           | FALCON_USER | TBL1          |
| TEST        | TBL2       |           | tb1         | TBL2          |
+-------------+------------+-----------+-------------+---------------+
2 rows in set (0.00 sec)

借使要扩张数据文件,近年来看犹如还从未兑现,测量检验如下:
mysql> alter tablespace tb1 add datafile 'data01_1' engine=falcon;
ERROR 1178 (42000): The storage engine for the table doesn't support 
上述语句近些日子唯有NDB存款和储蓄引擎能够接纳。

对此从未此外对象的表空间,能够用drop tablespace命令实行删除:
mysql> drop tablespace tbs2 engine=falcon;
Query OK, 0 rows affected (0.01 sec)
瞩目:这里应当要拉长engine=falcon关键字,不然会报语法错误。
删去后,表空间相应的数码字典消息和数据文件都会被去除。

设若表空间不为空,删除的时候会进展非空提醒:
mysql> drop tablespace tb1 engine=falcon;
ERROR 1671 (HY000): Tablespace 'tb1' not empty

待续

参谋文书档案:
[url]]
[url]]

本文由凤凰平台注册开户发布于MySQL数据库,转载请注明出处:凤凰彩票投注网falcon存储引擎入门,跨实例访问

关键词:

上一篇:没有了
下一篇:没有了