Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand mysql federated tables and views". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1) Verification environment

Source database: 192.168.8.75 centos 7.5 mysql8.3

Target library: 192.168.8.68 redhat 6.8 mysql5.7

2) Log in to the source database and create a source table

$mysql-u root-ppaasword-h 192.168.8.75

Mysql > create database db_test

Mysql > use db_test

Mysql > create table T1 (C1 int,c2 char)

Mysql > insert into T1 values (1)

3) Log in to the source database to create a user and authorize

$mysql-u root-ppaasword-h 192.168.8.75

Mysql > create user test@'%' identified by 'password'

Mysql > grant select,insert,delete,update on db_test.t1 to test@'%'

3) the target library enables the federation engine

# vi / etc/my.cnf # add the following line and save

Federated

# service mysqld start

4) Log in to the target library and create a federated table

# mysql-u root-ppassword-h 192.168.8.68

Mysql > create database db_test

Mysql > use db_test

Mysql > create table f_t1 (C1 int,c2 char (100)) engine=federated connection = 'mysql://user_test:user_test_pwd@192.168.8.75:3306/test/t1'

5) Log in to the target library to create a user and authorize

$mysql-u root-ppassword-D db_test-h 192.168.8.68

Mysql > create user test@'%' identified by 'password'

Mysql > grant select,insert,delete,update on test.f_t1 to test

5) Log in to the target library test user test

$mysql-u test-ppassword-D db_test-h 192.168.8.68

Mysql > select * from f_t1

Mysql > insert into f_t1 values (2)

Mysql > delete from f_t1 where c1room1

Mysql > update f_t1 set c2 / w 'where c1 / 2

6) Log in to the source database and delete the source table

$mysql-u root-ppassword-D db_test-h 192.168.8.75

Mysql > drop table T1

7) log in to the target side to operate the federated table again

$mysql-u user_test-ppassword-D db_test-h 192.168.8.68

Mysql > select * from f_t1

-- will report an error error 1430.

8) Log in to the source database to rebuild the source table

$mysql-u root-ppassword-D db_test-h 192.168.8.75

Mysql > create table T1 (C1 int,c2 char)

Mysql > insert into T1 values (3meme cc')

9) log in to the target library again and operate the federated table

$mysql-u user_test-ppassword-D db_test-h 192.168.8.68

Mysql > select * from f_t1

Mysql > insert into f_t1 values (5 recordings)

-- now that everything in the federated table returns to normal, it can be seen that the deletion of the source table will not affect the definition of the federated table in the target database, and the source table can be restored to normal by rebuilding the source table.

10) Log in to the target library to create a view

$mysql-u user_test-ppassword-D db_test-h 192.168.8.68

Mysql > create view v_f_t1 as select * from f_t1 where C1 select * from v_f_t1

Visible views can be created based on federated tables.

Mysql > insert into f_t1 values (6)

Mysql > select * from v_f_t1

The visible view can limit the user's access to the data.

Mysql > insert into v_f_t1 values (7)

Visible views do not limit the range of data that users can insert, in fact, not only for federated tables, but also for views of regular tables.

Mysql > update v_f_t1 set c2

Visible views can limit the scope of user changes to the dataset, and users can only change the data allowed in the view definition, even if the update statement does not have any where conditions.

Mysql > delete from v_f_t1

The visible view can limit the scope of deletion of the dataset, and the user can only delete the data that is allowed in the view definition, even if the delete statement does not have any where conditions.

This is the end of the content of "how to understand mysql federated tables and views". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.