I Use This!
Very High Activity

News

Analyzed 2 days ago. based on code collected 2 days ago.
Posted 6 days ago by The Pythian Group
This Log Buffer Edition covers from various blogs covering the technologies and news of Oracle, SQL Server and MySQL. Oracle: Storage Server: datasets, snapshots and performance A Different Cause for enq: TM – contention ODA X6-2M – How to create ... [More] your own ACFS file system Oracle New Version Numbering KBHS-00600: Internal Error, Arguments [1] [kbhshtCreateDataBucket] Error During Backup To Oracle Cloud SQL Server: Using Database Master Keys in SQL Server SQL Server Management Studio Tips Sqlcmd is not just a script execution tool. It gives us access to internal variables and commands that we can use to automate code execution. Additional SERVERPROPERTY properties in SQL Server Simple SQL: Random Thoughts My SQL: The danger of no Primary Key when replicating in RBR (and a partial protection with MariaDB 10.1) What’s New With MySQL Replication in MySQL 8.0 Docker Secrets and MySQL Password Management More Details about InnoDB Compression Levels (innodb_compression_level) Extending the Power of MariaDB ColumnStore with User Defined Functions [Less]
Posted 7 days ago by Oracle MySQL Group
It's our pleasure to announce that MySQL Community team is supporting multiple shows around the world. Please find a list of conferences & events where you can find us in August & September below: NA&LAD UbuCon LA,Lima, Peru, Aug ... [More] 18-19, 2017 MySQL Community team is supporting this small Ubuntu conference in LAD as Platinum sponsor.   Pacific Northwest PHP, Seattle, US, Sep 9-11, 2017 You can find our team at the MySQL Community booth in the expo area. Madison PHP, Madison, US, Sep 22-23, 2017 MySQL Community team is supporting this PHP conference as Community partner. EMEA FrOSCon, Sankt Augustin, Germany, Aug 19-20, 2017 MySQL Community team is as in previous years Silver sponsor of FrOSCon conference. Our colleague Carsten Thalheimer, the Technical Sales Consultant will have a talk on MySQL 5.7 - InnoDB Cluster [HA built in], scheduled for Saturday, Aug 19@14:00. Do not miss to come to MySQL booth at the expo area to talk to us! WebExpo, Prague, Czech republic, Sep 22-23, 2017 MySQL Community team is supporting this technology conference as Conference Partner.  SwanseaCon, Swansea, UK, Sep 25-26, 2017 You can find us at the MySQL booth in the expo area as well as you can find MySQL talk scheduled. David Stokes, the MySQL Community Manager will be talking about "Making MySQL Agile-ish". His talk is scheduled for Sep 26 @11:45-12:30pm. APAC Open Developer Conference, Tokyo, Japan, Aug 19-20, 2017 MySQL Community with a help of the local MySQL team is staffing and supporting this conference. You can find us at our MySQL booth in the expo area as well as you find the MySQL talks in the agenda. Our colleague Ryusuke Kajiyama will be talking about development team and model of MySQL and about "New way of developing MySQL app with Document Store". Check agenda for more details. Open-Source Developer Forum, Beijing, China, Aug 24-25, 2017 MySQL Community team in cooperation with the local MySQL User Group (ACMUG) are organizing a OS Developer Forum for more than 300 MySQL (and not just MySQL) users.  db tech showcase, Tokyo, Japan, Sep 5-7, 2017 As a tradition we are again part of the db tech showcase. You can find our talks in the schedule as follows: Mikiya Okuno, MySQL Support Engineer on "What's new MySQL Cluster 7.5 and beyond"   Ryusuke Kajiyama, MySQL Sales Consulting Senior Manager on "New Unicode Collations in MySQL 8.0"   Takanori Sejima, Senior Lead Engineer: Talk topic will be determined later on. Yoku0825, Oracle ACE MySQL: Talk topic will be determined later on. Open Source Conference 2017 Tokyo/Fall, Tokyo, Japan, Sep 9-10, 2017 MySQL Community team is supporting this event as Gold sponsor. You can find our team on the MySQL booth in the expo area as well as attend MySQL talks. Our colleagues Daisuke Inagaki, MySQL Sales Consultant and Ryusuke Kajiyama, MySQL Sales Consulting Senior Manager will be available at our MySQL booth and Ryusuke will be talking about news in MySQL 8.0-New feature (window function, Japanese collation...), MySQL InnoDB Cluster, MySQL Cloud Services etc.  JCConf, Taipei, Taiwan, Sep 29, 2017 Our pleasure to announce that MySQL Community team with a help of the local MySQL teams are going to attend and support JCConf this year again. You will be able to find us at the MySQL booth as well as find MySQL talks. For more details please check the agenda. Much more conferences & events will come up soon.    [Less]
Posted 7 days ago by Matt Lord
I'm happy to announce that in addition to the official MySQL Community Edition Docker images, we now have official MySQL Enterprise Edition Docker images available in Docker Store! Now you can easily leverage the power of Docker with MySQL Community ... [More] Edition or MySQL Enterprise Edition.     What Does All of This Mean? First some context and general info to help you understand this news a bit more. Docker is a container platform which includes the following software components: The Docker Engine, which is a client-server application consisting of: The dockerd server process A REST API that client programs can use to interact with dockerd The docker command-line client Optional orchestration tooling--Compose and Swarm--that help you manage more complex container based applications The Docker SaaS platform that provides the underlying infrastructure for working with Docker containers--identity, repositories, versioning, etc. (this is the rough equivalent of what GitHub offers Git users).  The usage of container images (pull and push) through docker by default utilize Docker's public container registries, and the access to those registries is managed through your user account that you create--your Docker ID. You then use that Docker ID to login and persist an authentication token on your client machine for accessing their public registries. Docker provides two separate public registries: Docker Hub or "Docker Registry" is their registry for FOSS/Community containers. This is what the docker CLI will use by default. Example usage being: docker run -itd mysql/mysql-server Docker Store or "Docker Trusted Registry" is their registry for "Enterprise" and Commercial containers. So How Would I Use The MySQL EE Container Then? You first setup authorized access to the container for your Docker ID within the Docker Store or "Docker Trusted Registry" by subscribing to the MySQL EE image. You do that simply by "proceeding to checkout" there since you don't have to pay up front (it's a BringYourOwnLicense model). Once you specify the required info, then registered/authorized access to the content and container will be associated with your Docker ID.  Now that you have authorized access, you can use/run it with: docker run -itd store/oracle/mysql-enterprise-server:5.7 Finally, you can view all of your authorized/subscribed content in Docker Store at https://store.docker.com/profiles/{DockerID}/content and you can see what local images you have available on your host machine using the docker images command. Summary We're excited that you can now combine the power and flexibility of Docker and MySQL Enterprise! I hope that I've also helped you to understand exactly how you would go about getting started on that endeavor. If you have any questions or issues, please do let us know! Feel free to leave a comment here, reach out to me on twitter, file a bug, or file a support ticket in My Oracle Support. As always, THANK YOU for using MySQL! [Less]
Posted 7 days ago by MySQL Performance Blog
Percona announces the release of Percona Monitoring and Management 1.2.1 on August 16, 2017. For install and upgrade instructions, see Deploying Percona Monitoring and Management. This hotfix release improves memory consumption. Changes in PMM ... [More] Server We’ve introduced the following changes in PMM Server 1.2.1: Bug fixes PMM-1280: PMM server affected by nGinx CVE-2017-7529. An integer overflow exploit could result in a DOS (Denial of Service) for the affected nginx service with the max_ranges directive not set. This problem is solved by setting the set max_ranges directive to 1 in the nGinx configuration. Improvements PMM-1232: Update the default value of the METRICS_MEMORY configuration setting. Previous versions of PMM Server used a different value for the METRICS_MEMORY configuration setting which allowed Prometheus to use up to 768MB of memory. PMM Server 1.2.0 used the storage.local.target-heap-size setting, its default value is 256MB. Unintentionally, this value reduced the amount of memory that Prometheus could use. As a result, the performance of Prometheus was affected. To improve the performance of Prometheus, the default setting of storage.local.target-heap-size has been set to 768 MB. About Percona Monitoring and Management Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance. Percona developed it in collaboration with experts in the field of managed database services, support and consulting. PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible. A live demo of PMM is available at pmmdemo.percona.com. We’re always happy to help! Please provide your feedback and questions on the PMM forum. If you would like to report a bug or submit a feature request, please use the PMM project in JIRA. [Less]
Posted 8 days ago by Louis Liu
Actually, Mysqlbinlog tools can’t miner mysql binlog exactly correct. In this post ,a special environment we got may hexadecimal characters what we don’t need. Read this PDF: Special-code-rollback.pdf
Posted 8 days ago by Jean-François Gagné
TL;DR: unless you know what you are doing, you should always have a primary key on your tables when replicating in RBR (and maybe even all the time). TL;DR2: MariaDB 10.1 has an interesting way to protect against missing a primary key ... [More] (innodb_force_primary_key) but it could be improved. A few weeks ago, I was called off hours because replication delay on all the slaves from a replication chain [Less]
Posted 8 days ago by MySQL Performance Blog
Join Percona’s, Technical Services Manager, Jervin Real as he presents Lock, Stock and Backup: Data Guaranteed on Wednesday, August 16, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7). Reserve Your Spot Backups are crucial in a world where data is ... [More] digital and uptime is revenue. Environments are no longer bound to traditional data centers, and span multiple cloud providers and many heterogeneous environments. We need bulletproof backups and impeccable recovery processes. This talk aims to answer the question “How should I backup my MySQL databases?” by providing 3-2-1 backup designs, best practices and real-world solutions leveraging key technologies, automation techniques and major cloud provider services. Register for the webinar here. Jervin Real As Technical Services Manager, Jervin partners with Percona’s customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in April 2010. Starting as a PHP programmer, Jervin quickly learned the LAMP stack. He has worked on several high-traffic sites and a number of specialized web applications (such as mobile content distribution). Before joining Percona, Jervin also worked with several hosting companies, providing care for customer hosted services and data on both Linux and Windows. [Less]
Posted 8 days ago by Mark Callaghan
There have been several recent performance improvements to MyRocks that I want to test. The last build I did was from June 16. With the my.cnf options that I use there is one new option and 3 have been removed. The new option is ... [More] rocksdb_max_background_jobs. The removed options are rocksdb_base_background_compactions, rocksdb_max_background_compactions and rocksdb_max_background_flushes. [Less]
Posted 8 days ago by Marco Tusa
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 ... [More] 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:   1 2 3 mysql> select database(); ERROR 1044 (42000): Access denied for user 'uworld'@'%' to database 'test'         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 | 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 | 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 | 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 | 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; update myworld.CountryM set LifeExpectancy=0 where Code='IND';   (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.   Back to main article [Less]
Posted 8 days ago by Sheeri K. Cabral
In the quest to secure MySQL as well as ease the number of complicated passwords to remember, many organizations are looking into external authentication, especially using LDAP. For free and open source, Percona’s PAM authentication plugin is the ... [More] standard option. tl;dr is I go through how to compile php-cli for use with auth_pam plugin. Background There are two plugins that can be used. From the documentation, the two plugins are: Full PAM plugin called auth_pam. This plugin uses dialog.so. It fully supports the PAM protocol with arbitrary communication between client and server. Oracle-compatible PAM called auth_pam_compat. This plugin uses mysql_clear_password which is a part of Oracle MySQL client. It also has some limitations, such as, it supports only one password input. You must use -p option in order to pass the password to auth_pam_compat. Percona’s MySQL client supports both plugins natively. That is, you can use auth_pam or auth_pam_compat and use the “mysql” tool (or “mysqldump”, or mysql_upgrade, etc.) and you are good to go. Given the choice, we would all use auth_pam, under which clients DO NOT use mysql_clear_password. Not all clients support auth_pam, which is the main problem. Workarounds have called for using auth_pam_compat over SSL, which is a perfectly reasonable way to handle the risk of cleartext passwords – encrypt the connection. However, what if you want to use auth_pam? The problem with auth_pam Back in 2013, Percona posted about how to install and configure auth_pam and auth_pam_compat. I will not rehash that setup, except to say that most organizations no longer use /etc/shadow, so the setup involves getting the correct /etc/pam.d/mysqld in place on the server. That article has this gem: As of now, only Percona Server’s mysql client and an older version of HeidiSQL(version 7), a GUI MySQL client for Windows, are able to authenticate over PAM via the auth_pam plugin by default. So, if you try to connect to MySQL using Perl, PHP, Ruby, Python and the like, you will receive this error: “Client does not support authentication protocol requested by server; consider upgrading MySQL client.” Fast forward 4 years, to now, and this is still an issue. Happily, the article goes on to explain how to recompile clients to get them to work: The good news is that if the client uses libmysqlclient library to connect via MySQL, you can recompile the client’s source code to use the libmysqlclient library of Percona Server to make it compatible. This involves installing Percona Server development library, compiler tools, and development libraries followed by compiling and installing the client’s source code. And, it helpfully goes step by step on how to recompile perl-DBD-mysql to get it working with LDAP authentication (as well as without – it still works for users who do not use LDAP). But what if you are using PHP to connect to MySQL? PHP and auth_pam If you try to connect, you get this error:SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client So let us try to mirror the perl recompilation process in PHP. Step 1 “Install Percona yum repository and Percona Server development library.” This is not a problem, do what you need to do to install Percona-Server-devel for your version. Step 2 Install a package manager so you can build a package – optional, but useful, if you ever want to have this new client without having to recompile. As in the example, I chose the RPM package manager, so I installed rpm-build. Step 3 Download and install the source RPM for the client package. This is where I started running into trouble. What I did not realize was that PHP does not divide out its packages like Perl does. Well, it does, but php-mysqlnd is compiled as part of the core, even though it is a separate package. Downloading the main PHP package So I downloaded the source RPM for PHP at https://rpms.remirepo.net/SRPMS/, and installed it into the sources directory:cd SRPMS wget https://rpms.remirepo.net/SRPMS/php-7.0.22-2.remi.src.rpm cd ../SOURCES rpm -Uvh ../SRPMS/php-7.0.22-2.remi.src.rpm This unpacks a main file, php-7.0.22.tar.xz, plus a bunch of supplemental files (like patches, etc). What it does NOT contain is a spec file, which is critical for building the packages. Getting a spec file I searched around and found one at https://github.com/iuscommunity-pkg/php70u/blob/master/SPECS/php70u.spec – this is for 7.0.21, so beware of using different versions of spec files and source code. Once that was done, I changed the mysql lines to /usr/bin/mysql_config as per Choosing a MySQL library. Note that I went with the “not recommended” library, but in this case, we WANT to compile with libmysqlclient. Compiling php-cli, not php-mysqlnd In addition, I discovered that compiling php-mysqlnd with the new libraries did not work. Perhaps it was something I did wrong, as at that point I was still compiling the whole PHP package and every module in it. However, what I *did* discover is that if I recompiled the php-cli package with libmysqlclient, I was able to get a connection via PHP using LDAP authentication, via a tool written by someone else – with no changes to the tool. Final spec file So here is the spec file I eventually came up with. I welcome any optimizations to be made! Step 4 “Install compilers and dependencies”. On my host I had to do a bunch of installations to get the requirements installed (your mileage may vary), including the Percona Server package for the /usr/lib64/mysql/plugin/dialog.so file:yum install Percona-Server-server-55-5.5.55-rel38.8.el6.x86_64 libtool systemtap-sdt-devel unixODBC-devel Step 5 “Build the RPM file”. Such an easy step, but it took about a week of back and forth with building the RPM file (which configures, tests and packages up everything), so I went between this step and updating the spec file a lot. cd rpmbuild/SPECS/ rpmbuild -bb rpmbuild/SPECS/php-cli.spec Then I installed my PHP file and tested it, and it worked! # rpm -e php-cli –nodeps # rpm -Uvh /root/rpmbuild/RPMS/x86_64/php70u-cli-7.0.22-2.ius.el6.x86_64.rpm –nodeps Preparing… ########################################### [100%] 1:php70u-cli ########################################### [100%] I hope you have similar success, and if you have updates to the spec files and lists of packages to install, please let me know! [Less]