Mysql uses federated for dblink remote Table Access

1. create the test table createtablesselect * frommysql on the source side. user; 2. source to view the test table creation statement showcreatetables; EOFCREATETABLE's '('host' char (60) CHARACTERSETutf8COLLATEutf8_binNOTNULLDEFAULT, 'user' char (16) CHARACTE

1. create a test table create table s select * frommysql on the source side. user; 2. show create table s; eof create table's ('host' char (60) character set utf8 COLLATE utf8_bin not null default '', 'user' char (16) CHARACTE

1. Create a test table on the source side
Create table s select * from mysql. user;

2. view the table creation statement of the test table on the source side.
Show create table s;

< Create table's '(
'Host' char (60) character set utf8 COLLATE utf8_bin not null default '',
'User' char (16) character set utf8 COLLATE utf8_bin not null default '',
'Password' char (41) character set latin1 COLLATE latin1_bin not null default '',
'Select _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Insert _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Update _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Delete _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Drop _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Reload _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Shutdown _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Process _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'File _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Grant _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'References _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Index _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Alter _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Show _ db_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Super _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ tmp_table_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Lock _ tables_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Execute _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Repl _ slave_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Repl _ client_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ view_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Show _ view_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ routine_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Alter _ routine_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ user_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Event _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Trigger _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ tablespace_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Ssl _ type' enum ('', 'any', 'x509 ', 'specified') character set utf8 not null default '',
'Ssl _ cipher' blob not null,
'X509 _ issuer 'blob not null,
'X509 _ subobject' blob not null,
'Max _ question' int (11) unsigned not null default '0 ',
'Max _ updates' int (11) unsigned not null default '0 ',
'Max _ connections 'int (11) unsigned not null default '0 ',
'Max _ user_connections 'int (11) unsigned not null default '0 ',
'Plugin' char (64) character set utf8 COLLATE utf8_bin DEFAULT '',
'Authentication _ string' text character set utf8 COLLATE utf8_bin,
'Password _ expired' enum ('n', 'y') character set utf8 not null default 'n'
) ENGINE = InnoDB default charset = latin1

EOF

3. Create a user on the source and authorize the user

Grant all privileges on sources. s TO dex identified by 'xiaojun ';
Grant all privileges on sources. s TO dex@192.168.100.42 identified by 'xiaojun ';
Show grants for dex;

Mysql> grant all privileges on sources. s TO dex identified by 'xiaojun ';
Query OK, 0 rows affected (0.03 sec)

Mysql> grant all privileges on sources. s TO dex@192.168.100.42 identified by 'xiaojun ';
Query OK, 0 rows affected (0.00 sec)

Mysql> show grants for dex;
+ Shards +
| Grants for dex @ % |
+ Shards +
| Grant usage on *. * TO 'dex '@' % 'identified by password' * 8FDE30312222738F1CD8AC8AF0EE515A9DB8180E' |
| Grant all privileges on 'Sources '. 'S' TO 'dex '@' % '|
+ Shards +
2 rows in set (0.00 sec)

4. Check whether the FEDERATED storage engine is installed on the target.

Mysql> show engines;
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| Engine | Support | Comment | Transactions | XA | Savepoints |
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| CSV | YES | CSV storage engine | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO |
| MyISAM | YES | MyISAM storage engine | NO |
| BLACKHOLE | YES |/dev/null storage engine (anything you write to it disappears) | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL |
| ARCHIVE | YES | Archive storage engine | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO |
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
9 rows in set (0.00 sec)

The federated storage engine is not installed (the target can be any storage engine, such as myisam or innodb)

4.1 first install the federated storage engine

Install plugin federated soname 'Ha _ federated. so ';

Mysql> install plugin federated soname 'Ha _ federated. so ';
ERROR 1125 (HY000): Function 'federated 'already exists

Already installed, but not enabled

Test whether it works.

[Root @ rhel6Mysql02 ~] # Mysqld_safe -- federated &
[1] 2194
[Root @ rhel6Mysql02 ~] #130620 18:12:28 mysqld_safe Logging to '/var/lib/mysql/rhel6Mysql02. err '.
130620 18:12:28 mysqld_safe Starting mysqld daemon with databases from/var/lib/mysql

Mysql> show engines;
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| Engine | Support | Comment | Transactions | XA | Savepoints |
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| CSV | YES | CSV storage engine | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO |
| MyISAM | YES | MyISAM storage engine | NO |
| BLACKHOLE | YES |/dev/null storage engine (anything you write to it disappears) | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO |
| ARCHIVE | YES | Archive storage engine | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO |
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
9 rows in set (0.00 sec)

Modify the my. conf file.

Federated

Restart mysql server.
Service mysql restart

Well, it is supported.
Mysql> show engines;
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| Engine | Support | Comment | Transactions | XA | Savepoints |
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
| CSV | YES | CSV storage engine | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO |
| MyISAM | YES | MyISAM storage engine | NO |
| BLACKHOLE | YES |/dev/null storage engine (anything you write to it disappears) | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO |
| ARCHIVE | YES | Archive storage engine | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO |
+ -------------------- + --------- + ---------------------------------------------------------------- + ------------ +
9 rows in set (0.00 sec)

5. Define the federated storage engine-based table links on the target end.

Create table 'link '(
'Host' char (60) character set utf8 COLLATE utf8_bin not null default '',
'User' char (16) character set utf8 COLLATE utf8_bin not null default '',
'Password' char (41) character set latin1 COLLATE latin1_bin not null default '',
'Select _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Insert _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Update _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Delete _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Drop _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Reload _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Shutdown _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Process _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'File _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Grant _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'References _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Index _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Alter _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Show _ db_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Super _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ tmp_table_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Lock _ tables_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Execute _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Repl _ slave_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Repl _ client_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ view_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Show _ view_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ routine_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Alter _ routine_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ user_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Event _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Trigger _ priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Create _ tablespace_priv' enum ('n', 'y') character set utf8 not null default 'n ',
'Ssl _ type' enum ('', 'any', 'x509 ', 'specified') character set utf8 not null default '',
'Ssl _ cipher' blob not null,
'X509 _ issuer 'blob not null,
'X509 _ subobject' blob not null,
'Max _ question' int (11) unsigned not null default '0 ',
'Max _ updates' int (11) unsigned not null default '0 ',
'Max _ connections 'int (11) unsigned not null default '0 ',
'Max _ user_connections 'int (11) unsigned not null default '0 ',
'Plugin' char (64) character set utf8 COLLATE utf8_bin DEFAULT '',
'Authentication _ string' text character set utf8 COLLATE utf8_bin,
'Password _ expired' enum ('n', 'y') character set utf8 not null default 'n'
)
ENGINE = FEDERATED
Default charset = latin1
CONNECTION = 'mysql: // dex: xiaojun@192.168.100.41: 3306/sources/s ';



Query OK, 0 rows affected (1.10 sec)

Mysql> select count (*) from links;
+ ---------- +
| Count (*) |
+ ---------- +
| 12 |
+ ---------- +
1 row in set (0.00 sec)

,