How ProxySQL deal with schema (and schemaname) Long story
This article is the spin-off of the article How ProxySQL deal with schema (and schemaname)
1 How ProxySQL differes from MySQL in managing explicit default schema declaration? does it respect the -D or USE
MySQL and ProxySQL will behave the same when passing the default schema, setting it as default.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
MySQL mysql -uuallworld -ptest -h192.168.1.107 -P 3306 -D test select database(); +------------+ | database() | +------------+ | test | +------------+ SHOW TABLES; +-----------------+ | Tables_in_test | +-----------------+ | rewrite_tbtest | | rewrite_tbtest1 | +-----------------+ ProxySQL mysql -uuallworld -ptest -h127.0.0.1 -P 6033 -D test select database(); +------------+ | database() | +------------+ | test | +------------+ SHOW TABLES; +-----------------+ | Tables_in_test | +-----------------+ | rewrite_tbtest | | rewrite_tbtest1 | +-----------------+ |
Passing a different schema from the default one in ProxySQL will override the ProxySQL default:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
MySQL ./mysql-3306 -uuallworld -ptest -D mysql (uallworld@localhost) [mysql]>select database(); +------------+ | database() | +------------+ | mysql | +------------+ ProxySQL [root@rms2 server57S]# ./mysql-3306 -uuallworld -ptest -h192.168.1.50 -P6033 -D mysql (uallworld@192.168.1.50) [mysql]>select database(); +------------+ | database() | +------------+ | mysql | +------------+ |
Connecting without DEFAULT Schema
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
MySQL mysql -uuallworld -ptest -h192.168.1.107 -P 3306 mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ ProxySQL mysql -uuallworld -ptest -h127.0.0.1 -P 6033 mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ |
The last is different between MySQL and ProxySQL.
By default ProxySQL will connect the user to his default schema.
Not only, if the default schema is not define:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | uallworld | test | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | ProxySQL will point the connection to information_schema. mysql> select database(); +--------------------+ | database() | +--------------------+ | information_schema | +--------------------+ |
2 How proxy respect/follow security agains schema
1 2 3 4 5 6 7 8 9 10 11 |
MySQL mysql -uuworld -ptest -h192.168.1.107 -P 3306 -D test mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1044 (42000): Access denied for user 'uworld'@'%' to database 'test' ProxySQL mysql -uuworld -ptest -h127.0.0.1 -P 6033 -D test mysql: [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 483902 Server version: 5.5.30 (ProxySQL) |
And no warnings.
Why?
Because while you directly connect to MySQL, when you connect to ProxySQL you do not actually touch the final server.
You only open a connection to the ProxySQL, issue a query will open a connection and if the user do not have the right grants an error will be returned.
Let us see.
just issuing the command:
As soon as you try to query the MySQL server you got an error.
3 How schemaname filter acts in the query rules?
In MySQL we can easily change the default schema with USE , this action is fully supported by ProxySQL.
But it may have some side effects when using "schemaname" as filter in the query_rules.
If you define a rule that include the default schemaname and the default schema is changed with USE, the rule will not apply, and unpredictable results may happen.
To prevent that ProxySQL has another option in mysql_user "schema_locked" which will prevent the schema to be changed by USE.
This feature is present but not fully implemented, and after a brief conversation with Rene (https://github.com/sysown/proxysql/issues/1133).
For now my advice is to DO NOT use schemaname as filter in the query_rules.
Below the full test case:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 0 | 11 | 11 | ^SELECT | NULL | NULL | NULL | 1 | 0 | NULL | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 2 rows in set (0.00 sec) (uworld@192.168.1.50) [world]>Select * from world.City order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.01 sec) mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 1 | 11 | 11 | ^SELECT | NULL | NULL | NULL | 1 | 0 | NULL | <--- +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 2 rows in set (0.00 sec) mysql> select * from stats_mysql_query_digest; +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | 10 | world | uworld | 0x594F2C744B698066 | select USER() | 1 | 1502789864 | 1502789864 | 0 | 0 | 0 | | 10 | world | umyworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789849 | 1502789849 | 10264 | 10264 | 10264 | | 10 | world | uworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789863 | 1502789863 | 1750 | 1750 | 1750 | | 10 | world | umyworld | 0x594F2C744B698066 | select USER() | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | uworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789863 | 1502789863 | 0 | 0 | 0 | | 10 | world | uworld | 0x99531AEFF718C501 | show tables | 1 | 1502789863 | 1502789863 | 470 | 470 | 470 | | 10 | world | umyworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | umyworld | 0x99531AEFF718C501 | show tables | 1 | 1502789745 | 1502789745 | 7427 | 7427 | 7427 | | 11 | world | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789866 | 1502789866 | 3349 | 3349 | 3349 | <--- | 10 | world | umyworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789745 | 1502789745 | 615 | 615 | 615 | +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 10 rows in set (0.00 sec) (uworld@192.168.1.50) [world]>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 (uworld@192.168.1.50) [information_schema]>Select * from world.City order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.01 sec) mysql> select * from stats_mysql_query_digest; +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | 10 | information_schema | uworld | 0x99531AEFF718C501 | show tables | 1 | 1502789929 | 1502789929 | 540 | 540 | 540 | | 10 | information_schema | uworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789929 | 1502789929 | 897 | 897 | 897 | | 11 | world | uworld | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1502789929 | 1502789929 | 537 | 537 | 537 | | 10 | world | uworld | 0x594F2C744B698066 | select USER() | 1 | 1502789864 | 1502789864 | 0 | 0 | 0 | | 10 | world | umyworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789849 | 1502789849 | 10264 | 10264 | 10264 | | 10 | world | uworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789863 | 1502789863 | 1750 | 1750 | 1750 | | 10 | world | umyworld | 0x594F2C744B698066 | select USER() | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | uworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789863 | 1502789863 | 0 | 0 | 0 | | 10 | world | uworld | 0x99531AEFF718C501 | show tables | 1 | 1502789863 | 1502789863 | 470 | 470 | 470 | | 10 | world | umyworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | umyworld | 0x99531AEFF718C501 | show tables | 1 | 1502789745 | 1502789745 | 7427 | 7427 | 7427 | | 10 | information_schema | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789933 | 1502789933 | 3707 | 3707 | 3707 | <--- | 11 | world | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789866 | 1502789866 | 3349 | 3349 | 3349 | <--- | 10 | world | umyworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789745 | 1502789745 | 615 | 615 | 615 | +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 14 rows in set (0.01 sec) mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 2 | 11 | 11 | ^SELECT | NULL | NULL | NULL | 1 | 0 | NULL | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 2 rows in set (0.00 sec) |
4 how can I transparently redirect using schema name?
This is not a Schema feature, more one of the things that in ProxySQL are quite easy to set, while close to be impossible if done in plain MySQL.
When connecting directly with MySQL there is no option for you to "play" with GRANTS and schema such that you will transparently allow a user to do an action on a schema/server pair and another on a different schema/server pair.
When using ProxySQL to filter by schemaname is quite trivial.
For instance assuming we have 3 users one is admin of the platform which include 2 separate schemas (or more), each user can access one schema for write (but that can be table as well), and a final slave with reporting information, where all the users that needs to read from other schema except their own can read cross schemas.
While all the select not cross schema mus still got to the Master.
This is not so uncommon, actually with few variant is exactly what one of the customer I spoke last week needs to do.
Let see what we need and how to do it:
- user(s) uworld & umyworld need to go to their default schema on Master for Writes.
- user(s) uworld & umyworld should go to their default schema on master for direct reads
- user(s) uworld & umyworld should go to the slave for reads when the other schema is used
To do this we will need the following rules:
1 2 3 4 5 6 7 8 9 10 |
insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) values(10,'uworld',10,1,3,'^SELECT.*FOR UPDATE',1); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(11,'uworld',10,1,3,'^SELECT ',0,50,0); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(12,'uworld',11,1,3,'\smyworld.',1,50,50); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) values(13,'umyworld',10,1,3,'^SELECT.*FOR UPDATE',1); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(14,'umyworld',10,1,3,'^SELECT ',0,50,0); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(15,'umyworld',11,1,3,'\sworld.',1,50,50); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;
|
To check the behaviour:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select database(); update world.City set Population=10500000 where ID=1024; update world.Country set LifeExpectancy=62.5 where Code='IND'; update myworld.CityM set Population=10500001 where ID=1024; update myworld.CountryM set LifeExpectancy=0 where Code='IND'; select database(); Select * from world.City order by Population DESC limit 5 ; Select * from myworld.CityM order by Population DESC limit 5 ; Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit 5; Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; |
Once I run the test (queries above):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 5 | 10 | 11 | ^SELECT | NULL | NULL | NULL | 0 | 0 | 50 | <-- 5 selects in total OK | 1 | 3 | 11 | 12 | myworld. | NULL | NULL | NULL | 1 | 50 | 50 | <-- 3 contains term myworld OK | 1 | 0 | 10 | 13 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 0 | 10 | 14 | ^SELECT | NULL | NULL | NULL | 0 | 0 | 50 | | 1 | 0 | 11 | 15 | world. | NULL | NULL | NULL | 1 | 50 | 50 | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 6 rows in set (0.00 sec) mysql> select * from stats_mysql_query_digest; +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | 11 | world | uworld | 0x921512ADAF79D0FF | Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 6531 | 6531 | 6531 | | 11 | world | uworld | 0xE9D26001526F2618 | Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 6573 | 6573 | 6573 | | 10 | world | uworld | 0xE846287B5A6B3945 | Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 3181 | 3181 | 3181 | | 11 | world | uworld | 0x55FFF888F4642D3A | Select * from myworld.CityM order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 7753 | 7753 | 7753 | | 10 | world | uworld | 0x26DB674419D1E979 | update myworld.CountryM set LifeExpectancy=? where Code=? | 1 | 1502718358 | 1502718358 | 257 | 257 | 257 | | 10 | world | uworld | 0x056615DE2CFD8C8E | update myworld.CityM set Population=? where ID=? | 1 | 1502718358 | 1502718358 | 235 | 235 | 235 | | 10 | world | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 3262 | 3262 | 3262 | | 10 | world | uworld | 0x7A15CC342D54452D | update world.Country set LifeExpectancy=?.? where Code=? | 1 | 1502718358 | 1502718358 | 319 | 319 | 319 | | 10 | world | uworld | 0x500E6F01B02078B6 | update world.City set Population=? where ID=? | 1 | 1502718358 | 1502718358 | 970 | 970 | 970 | +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+ |
As we can see with this simple set of rules my uworld user was able to perform exactly as expected and able to access the schema from the designated HG.
All the selects with schema "myworld" were redirect to HG 11.
Results details:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
(uworld@192.168.1.50) [(none)]>update world.City set Population=10500000 where ID=1024; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 (uworld@192.168.1.50) [(none)]>update world.Country set LifeExpectancy=62.5 where Code='IND'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 (uworld@192.168.1.50) [(none)]>update myworld.CityM set Population=10500001 where ID=1024; <--- I am NOT managing this but I could if needed ERROR 1142 (42000): UPDATE command denied to user 'uworld'@'192.168.1.50' for table 'CityM' (uworld@192.168.1.50) [(none)]>update myworld.CountryM set LifeExpectancy=0 where Code='IND'; <--- I am NOT managing this but I could if needed ERROR 1142 (42000): UPDATE command denied to user 'uworld'@'192.168.1.50' for table 'CountryM' (uworld@192.168.1.50) [(none)]> (uworld@192.168.1.50) [(none)]>Select * from world.City order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.00 sec) (uworld@192.168.1.50) [(none)]>Select * from myworld.CityM order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500001 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.01 sec) (uworld@192.168.1.50) [(none)]> (uworld@192.168.1.50) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit 5; +------+-----------------+-------------+--------------+------------+-------------+----------------+ | ID | Name | CountryCode | District | Population | name | LifeExpectancy | +------+-----------------+-------------+--------------+------------+-------------+----------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | India | 62.5 | | 2331 | Seoul | KOR | Seoul | 9981619 | South Korea | 74.4 | | 206 | São Paulo | BRA | São Paulo | 9968485 | Brazil | 62.9 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | China | 71.4 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | Indonesia | 68.0 | +------+-----------------+-------------+--------------+------------+-------------+----------------+ 5 rows in set (0.01 sec) (uworld@192.168.1.50) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; +------+-----------------+-------------+--------------+------------+-------------+----------------+ | ID | Name | CountryCode | District | Population | name | LifeExpectancy | +------+-----------------+-------------+--------------+------------+-------------+----------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500001 | India | 0.0 | | 2331 | Seoul | KOR | Seoul | 9981619 | South Korea | 74.4 | | 206 | São Paulo | BRA | São Paulo | 9968485 | Brazil | 62.9 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | China | 71.4 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | Indonesia | 68.0 | +------+-----------------+-------------+--------------+------------+-------------+----------------+ 5 rows in set (0.00 sec) (uworld@192.168.1.50) [(none)]> (uworld@192.168.1.50) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; +------+-----------------+-------------+--------------+------------+-------------+----------------+ | ID | Name | CountryCode | District | Population | name | LifeExpectancy | +------+-----------------+-------------+--------------+------------+-------------+----------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | India | 0.0 | | 2331 | Seoul | KOR | Seoul | 9981619 | South Korea | 74.4 | | 206 | São Paulo | BRA | São Paulo | 9968485 | Brazil | 62.9 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | China | 71.4 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | Indonesia | 68.0 | +------+-----------------+-------------+--------------+------------+-------------+----------------+ 5 rows in set (0.00 sec) |
Obviosuly doing it on the other user/schema work exacty the same.