Activity Not Available
I Use This!


Analyzed 2 months ago. based on code collected 2 months ago.
Posted 14 days ago by MySQL Server Dev Team
Coinciding with the new native data dictionary in MySQL 8.0, we have made a number of useful enhancements to our INFORMATION_SCHEMA subsystem design in MySQL 8.0. In this post I will first go through our legacy implementation as it has stood since MySQL 5.1, and then cover what’s changed.…
Posted 14 days ago by Piotr Obrzut
Dear MySQL users, MySQL Connector/Python 2.2.1 M2 is the second development release of the MySQL Connector Python 2.2 series. This series adds support for the new X DevAPI. The X DevAPI enables application developers to write code that ... [More] combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL. To learn more about how to write applications using the X DevAPI, see For more information about how the X DevAPI is implemented in MySQL Connector/Python, and its usage, see Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see To download MySQL Connector/Python 2.2.1 M2, see the "Development Releases" tab at Changes in MySQL Connector/Python 2.2.1 (2016-09-13) MySQL Connector/Python 2.2.1 has a prerequisite of Protobuf 3, and is available in a more limited number of distribution formats than usual (RPM and tar.gz packages only). * X DevAPI Notes * Functionality Added or Changed * Bugs Fixed X DevAPI Notes * Views can now be accessed like tables: + Schema.get_tables() now also returns a Table object for each existing View. + Schema.get_table(name) now also returns a Table object if the given name is a View. + All the operations available through a Table object are also available if the underlying object is a View. (The client will not do any validation in this regard.) + A new Table.is_view() method determines whether the underlying object is a View. * The Collection.add([]).execute() construct now is treated as a no-operation and does not return an error. This is true even if there is no connection to the server or the collection does not exist on the server. The reasoning is that a request to add nothing to something that does not exist is trivially fulfilled. * Connector/Python now implements support for these X DevAPI features: + Client failover + URI connection format Functionality Added or Changed * Protobuf support was upgraded from Protobuf 2 to Protobuf 3 (which means that Protobuf3 is now a prerequsite for Connector/Python). Bugs Fixed * Attempts to fetch a value having the BIT data type produced an error. (Bug #23729357) * The fetchone() result set method and close() session method were missing. They are now included. (Bug #23568257, Bug #23550743) * Session creating using a URL-format connection string failed. (Bug #23550057) Documentation -------------------- Online: The source distribution includes the manual in various formats under the docs/ folder. Reporting Bugs -------------------- We welcome and appreciate your feedback and bug reports: Enjoy and thanks for the support! On Behalf of the MySQL/ORACLE RE Team, Piotr Obrzut [Less]
Posted 14 days ago by Severalnines
When it comes to the query tuning, EXPLAIN is one the most important tools in the DBA’s arsenal. Why is a given query slow, what does the execution plan look like, how will JOINs be processed, is the query using the correct indexes, or is it creating ... [More] a temporary table? You can now sign up for the webinar, which takes place at the end of this month on September 27th. We’ll look at the EXPLAIN command and see how it can help us answer these questions. We will also look into how to use database indexes to speed up queries. More specifically, we’ll cover the different index types such as B-Tree, Fulltext and Hash, deepdive into B-Tree indexes, and discuss the indexes for MyISAM vs. InnoDB tables as well as some gotchas. MySQL Query Tuning Trilogy: Indexing and EXPLAIN - deep dive September 27th Sign up now Speaker Krzysztof Książek, Senior Support Engineer at Severalnines, is a MySQL DBA with experience managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard. And if you’d like to be a step ahead, you can also already sign up for the third and last part of this trilogy: MySQL Query Tuning: Working with optimizer and SQL tuning on October 25th. We look forward to seeing you there! Tags: MySQLmysql query tuningexplainindexinginnodbmyisam [Less]
Posted 14 days ago by Martin Hansson
Invisible indexes are a new feature in MySQL 8.0 that provide the ability to mark an index as unavailable for use by the Optimizer. This means that the index will still be maintained and kept up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses a FORCE INDEX hint).…
Posted 15 days ago by MySQL Performance Blog
In this blog post, I will discuss the CVE-2016-6662 vulnerability, how to tell if it affects you, and how to prevent the vulnerability from affecting you if you have an older version of MySQL. I’ll also list which MySQL versions include the ... [More] vulnerability fixes. As we announced in a previous post, there are certain scenarios in Percona Server (and MySQL) that can allow a remote root code execution (CVE-2016-6662). Vulnerability approach The website contains the full, current explanation of the CVE-2016-6662 vulnerability. To summarize, the methods used to gain root privileges require multiple conditions: A remote (or even local) MySQL user that has FILE permissions (or SUPER, which encompasses all of them). Improper OS files/directories permissions around MySQL configuration files that allow the MySQL system user access to modify or create new configuration files. Several techniques alter the MySQL configuration to include loading a malicious shared library. The techniques currently described require FILE or SUPER privileges, but also include the currently undisclosed CVE-2016-6663 (which demonstrates how to alter the configuration without FILE privileges). Have that malicious shared library loaded when MySQL restarts, which includes the code that allows privilege escalation. Fixed versions MySQL fixes MySQL seems to have already released versions that include the security fixes. This is coming from the release notes in MySQL 5.6.33: For mysqld_safe, the argument to --malloc-lib now must be one of the directories /usr/lib, /usr/lib64, /usr/lib/i386-linux-gnu, or /usr/lib/x86_64-linux-gnu. In addition, the --mysqld and --mysqld-version options can be used only on the command line and not in an option file. (Bug #24464380) It was possible to write log files ending with .ini or .cnf that later could be parsed as option files. The general query log and slow query log can no longer be written to a file ending with .ini or .cnf. (Bug #24388753) Privilege escalation was possible by exploiting the way REPAIR TABLE used temporary files. (Bug #24388746) You aren’t affected if you use version 5.5.52, 5.6.33 or 5.7.15. Release notes: 5.5.52, 5.6.33, 5.7.15 Percona Server The way Percona increased security was by limiting which libraries are allowed to be loaded with LD_PRELOAD (including --malloc-lib), and limiting them to /usr/lib/, /usr/lib64 and the MySQL installation base directory. This means only locations that are accessible by root users can load shared libraries. The following Percona Server versions have this fix: 5.5.51-38.1 5.6.32-78.0 5.7.14-7 We are working on releasing new Percona XtraDB Cluster versions as well. Future Percona Server releases will include all fixes from MySQL. MariaDB MariaDB has fixed the issue in 5.5.51, 10.1.17 and 10.0.27 I have an older MySQL Version, what to do now? It is possible to change the database configuration so that it isn’t affected anymore (without changing your MySQL versions and restarting your database). There are several options, each of them focusing on one of the conditions required for the vulnerability to work. Patch mysqld_safe Manually Just before publishing this, a blogpost came out with another alternative on how to patch your server: Database user permissions One way to avoid the vulnerability is making sure no remote user has SUPER or FILE privileges. However, CVE-2016-6663 mentions there is a way to do this without any FILE privileges (likely related to the REPAIR TABLE issue mentioned in MySQL release notes). Configuration files permissions The vulnerability needs to be able to write to some MySQL configuration files. Prevent that and you are secure. Make sure you configure permissions for various config files as follows: MySQL reads configuration files from different paths, including from your datadir Create an (empty) my.cnf  and .my.cnf in the datadir (usually /var/lib/mysql) and make root the owner/group with 0644 permissions. Other Locations to look into: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf  (mysqld --help --verbose shows you where mysqld will look) This also includes !includedir paths defined in your current configurations — make sure they are not writeable by the mysql user as well No config files should be writeable by the mysql user (change ownership and permissions) [Less]
Posted 15 days ago by dba square
Many of you have probably already heard about the new vulnerability affecting most existing MySQL forks and versions. The bug has been patched in some of the most recent MySQL and Percona Server releases and so, at least in theory, all it takes to ... [More] apply a fix is to update the MySQL or Percona Server packages to their latest versions. However, it would likely require a database restart and restarts are never particularly convenient, especially when done in a rush. But this time it is actually possible to fix the vulnerability without having to upgrade and restart your MySQL instances immediately. The attack relies on the ability to load a forged memory allocator library through --malloc-lib option in one of the MySQL start-up scripts. This can only happen if such library is first uploaded to server through the MySQL’s SQL interface using a specially-crafted SELECT INTO [DUMPFILE|OUTFILE] command to a directory MySQL has a write permission to. This generally limits the attack vector to paths like /tmp, and /var/lib/mysql or another location you configured as the MySQL data directory. The available patches are roughly several lines of bash code that essentially filter out any arguments for –malloc-lib that do not start with /usr/lib, /usr/lib64 and other similar prefixes; i.e. places where no shared libraries should normally be stored. Here is how Oracle handled it: The 10 lines following “# Restrict to a the list in $malloc_dirs above”. And here’s the Percona version: The 8 lines following “# Check if the library is in the reduced number of standard system directories”. So what do you need to do? Just edit in these changes into the appropriate location inside mysqld_safe file on your MySQL server. Typically it will be /usr/bin/mysqld_safe. This is all you need to do to protect your server against this particular vulnerability. No package upgrade nor any restart required. Anyone with database access and both FILE and SELECT privileges may still be able to upload the forged memory allocator library to the server as this can only be stopped with the correct configuration of secure-file-priv, but at least now the MySQL will refuse to load it during restart. Do you want to help you secure your MySQL server? Contact us for details at [Less]
Posted 15 days ago by Andy Bang
Dear MySQL users, The MySQL development team is pleased to announce that the fourth release of Oracle Enterprise Manager for MySQL Database is now Generally Available (GA). Oracle Enterprise Manager for MySQL Database is the official MySQL ... [More] plug-in that provides comprehensive performance, availability, and configuration information for Oracle's integrated enterprise IT management product line, Oracle Enterprise Manager (12c or later). This is a maintenance release that includes a few enhancements and fixes a number of bugs. You can find more information on the contents of this release in the change log: (also included at the end of this note for convenience) Use Self-Update to deploy Oracle Enterprise Manager for MySQL Database, or use My Oracle Support to download and install manually: Choose the "Patches & Updates" tab, and then choose the "Product or Family (Advanced Search)" side tab in the "Patch Search" portlet. Packages will also be available on the Oracle Software Delivery Cloud at the next monthly refresh. Please open a ticket on My Oracle Support to report problems, request features, or give us general feedback about how this release meets your needs. All the best! - The MySQL Enterprise Tools Development Team Useful URLs * My Oracle Support - * Installation documentation - * Complete documentation - * Product information - Changes in Oracle Enterprise Manager for MySQL Database Functionality Added or Changed * Several performance enhancements were made in this release. * The thresholds of the following metrics have been changed from critical to warning, and from warning to info: - Table Cache Not Optimal - Thread Cache Size Not Optimal Bugs Fixed * Path-related errors occurrOEM-17ed when using Oracle Enterprise Manager for MySQL Database on Oracle Enterprise Manager 13CR1. (#23499577) * On Oracle Enterprise Manager 13c it was not possible to upgrade from Oracle Enterprise Manager for MySQL Database to If using Oracle Enterprise Manager 13c, it is strongly recommended to upgrade from Oracle Enterprise Manager for MySQL Database to * The InnoDB Redo Log Pending Writes graph was not displayed for MySQL 5.7 instances. * The Warnings Not Being Logged metric help text was updated. * The underlying calculation of Prepared statements not being used effectively was incorrect. * Excessive debug messages were logged. This was due to an issue with the Connector/J component. * The help text of the Binary Log Space Exceeds Specified Limit metric was updated. * Under certain circumstances, the MyISAM Full Text Configuration metric did not return accurate information. A warning was also logged in the agent logs. * On the All Metrics page, any attempt to retrieve the Innodb Transaction Activity metrics resulted in the following error: Metric has no keys, but result has multiple rows The metrics were not displayed. [Less]
Posted 15 days ago by Aurimas Mikalauskas
MySQL has just released MySQL 8.0 DR (and yes, DR stands for Don’t Run-it-in-production-yet) so let’s jump right in and take a look at the hottest new features coming in this new release: 1. Persistent runtime configuration changes. Love it. From ... [More] now on we’ll be able to use SET PERSIST innodb_buffer_pool_size = X; instead of SET GLOBAL innodb_buffer_pool_size = X; for the runtime changes to persist during a restart. It may not make much sense if you’re using a modern database that doesn’t even have a configuration file, but for us who lived with MySQL for over 20 years, this is huge! How does it work? In a nutshell, these changes are saved in mysqld-auto.cnf file in MySQL data directory. 2. MySQL privilege tables are now InnoDB. I think this was the last thing holding MyISAM as a mandatory storage engine for MySQL. Buckle your seatbelt MyISAM, ’cause Kansas is going bye-bye! 3. Roles. This is basically an alias for a collection of privileges, so you don’t have to remember whether you should GRANT INSERT, UPDATE, DELETE, SELECT for these analytics clients, or will SELECT suffice. Simply GRANT ‘analytics’ role and you’re good to go. I’m not really dying to get this feature ASAP, but my eyebrows did lift up when I saw this. 4. Global Data Dictionary (so long .frm, .TRG and .TRN files!). Global data dictionary comes with a number of nice benefits with it (such as Dictionary object cache), although it’s also one of the reasons upgrade to 8.0 will be backwards incompatible. BTW, InnoDB will keep maintaining its own data dictionary, but I’m guessing the intention is to get rid of it eventually. 5. Optimizer hints. This is a nice alternative to the optimizer_switch session variable – I’ll definitely be using optimizer hints instead. Besides it being more convenient to use, added benefit is that you can specify different switches per table. 6. Invisible indexes. That’s right. Indexes can now be made invisible. Actually, it’s really neat feature – you can basically disable an index before you remove it to check whether it will do any harm to removee it. When you make an index invisible, it’s still maintened normally, but optimizer is not allowed to see it. 7. Deadlock detection can now be disabled with innodb_deadlock_detect variable. Guessing this was inspired by the following WebScaleSQL patch. I could be wrong though. In any case, if you have a highly concurrent workload, try it out. What happens with deadlocks when deadlock detection is disabled? Such locks will have to wait for innodb_lock_wait_timeout to occur. 8. Innodb buffer pool mutex removed. Okay, this one will probably make you roll your eyes rather than raise your eyebrows, because, well, Percona Server had it since like version 5.0. In any case, having it in official MySQL release and with appropriate acknowledgements (Yasufumi, Laurynas, wink wink) is pretty amazing. 9. Auto-increment counter value will now persist across server restarts! The value will be written to the redo log each time the value changes, and saved to an engine-private system table on each checkpoint. More on it here. 10. UUID_TO_BIN() and BIN_TO_UUID() functions. What for? Well, because “69de6646-7904-11e6-9ff9-99003302702e” can then be stored within 16 bytes (VARBINARY(16)) rather than 36 (CHAR(36)). And it’s not a small improvement. 16 bytes is just twice as big as bigint, whereas using CHAR(36) for UUIDs were rendering them virtually useless. 11. An insane amount of bugs fixed. For a full list, check this out. I’m sure a long and winding road still leads to RC and GA. Many more bugs are yet to be fixed. Maybe even additional features to be added. But it is definitely a good start. I’ll definitely be playing around with it soon and I will let you know how things look. The post 11 new features coming in MySQL 8.0 that will make your eyebrows raise appeared first on Speedemy. [Less]
Posted 15 days ago by Giuseppe Maxia
MySQL 8.0.0 was released today. It has been some time in the making, shrouded in a veil of secrecy for over one year. We knew, from listening to the gossip and looking at the few available previews, some of what was going to bring. So, for the ... [More] observant users, its main features may not come as a surprise. For the rest of you, here's a quick roundup:Notable featuresNo MyISAM tables anymore! The grant tables are now InnoDB, meaning that grant operations are now atomic.A real data dictionary. This change is less visible than the previous one. The data dictionary tables are hidden and only a subset of the data is available through information_schema views. The reason for the hidden tables is to allow a stable interface through several versions. I am not happy about this choice, but I understand the reason. The .frm files are gone. If you create a table using an engine other than InnoDB, the system creates a JSON file (.SDI) containing serialised data dictionary info.Roles! You can now define a set of privileges as a role, and then assign the role to a user. This feature greatly simplifies user management. You can set persistent variables from SQL (set persist variable_name=something) and they will survive a restart (except when they don't.There is a new plugin infrastructure of which I haven't seen any example yet. GotchasSome of the tables in the mysql database are gone. If you have tests that look for them (e.g. proc, event) they will fail, as these tables are now hidden in the data dictionary.Only InnoDB tables support partitioning! If you create MyISAM or Archive tables and try to partition them, you will get an error.The installation requires a new library (libnuma1 in Debian/Ubuntu, numactl-libs in CentOS). You need to install it before starting the MySQL server.Unmatched expectationsThe installation still issues the same warnings reported for MySQL 5.7. I was thinking that by now the developers know how to set the initialisation without triggering a warning about TIMESTAMPS. I was mistaken.[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).++ 2016-09-11T17:42:06.471261Z 1 [Warning] InnoDB: New log files created, LSN=49311++ 2016-09-11T17:42:06.548747Z 1 [Warning] InnoDB: Creating foreign key constraint system tables.++ 2016-09-11T17:42:07.593428Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0f4a8ceb-7847-11e6-bd60-0242ac110002.++ 2016-09-11T17:42:07.597075Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.The monitoring features for replication are unchanged. There is nothing new about reporting GTID execution in performance_schema tables, except some minor additions for RBR operations.Taking MySQL 8.0 for a spinMySQL-Sandbox 3.2.01 can install the new version from a tarball.If you are on Linux, you can even try the newest MySQL-Sandbox feature, which gets you reduced binaries for a given MySQL version with a single command. Just install MySQL::Sandbox 3.2.02 and run:make_sandbox GET:8.0 -- --no_show2016-09-12 17:38:40 URL: [98/98] -> "/tmp/available.txt" [1]wget -nv -O 8.0.0.tar.gz '' The MySQL Sandbox, version 3.2.02 (C) 2006-2016 Giuseppe Maxia# Starting server... sandbox server started# Loading grantsYour sandbox server was installed in $HOME/sandboxes/msb_8_0_0$ ~/sandboxes/msb_8_0_0/useWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 8.0.0-dmr MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql [localhost] {msandbox} ((none)) > show schemas;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || test |+--------------------+5 rows in set (0.00 sec)At first sight, it looks like the usual MySQL. Same schemas, and almost the same tables. You will see a few tables related to roles in mysql, and a dozen new tables between information_schema and performance_schema. However, where the database has a different aspect is on the outside. Let's start with the data directory:total 188468-rw-r----- 1 root root 56 Sep 11 18:14 auto.cnf-rw-r----- 1 root root 1813 Sep 11 18:14 ib_buffer_pool-rw-r----- 1 root root 50331648 Sep 11 18:15 ib_logfile0-rw-r----- 1 root root 50331648 Sep 11 18:15 ib_logfile1-rw-r----- 1 root root 79691776 Sep 11 18:15 ibdata1-rw-r----- 1 root root 12582912 Sep 11 18:15 ibtmp1-rw-r----- 1 root root 4396 Sep 11 18:39 msandbox.errdrwxr-x--- 2 root root 4096 Sep 11 18:14 mysql-rw-r----- 1 root root 4 Sep 11 18:15 1 root root 3 Sep 11 18:15 1 root root 225 Sep 11 18:14 performance_sche_3.SDIdrwxr-x--- 2 root root 4096 Sep 11 18:14 performance_schemadrwxr-x--- 2 root root 4096 Sep 11 18:14 sys-rw-r----- 1 root root 210 Sep 11 18:14 sys_4.SDIdrwxr-x--- 2 root root 4096 Sep 11 18:15 test-rw-r----- 1 root root 210 Sep 11 18:15 test_5.SDINotice that, for each schema except mysql, we have a .SDI file. For example:cat performance_sche_3.SDI{ "sdi_version": 1, "dd_version": 1, "dd_object_type": "Schema", "dd_object": { "name": "performance_schema", "default_collation_id": 33, "created": 0, "last_altered": 0 }}For tables other than innodb, we get a similar file, only more complex:mysql [localhost] {msandbox} (test) > create table t1 (i int not null auto_increment primary key, c char(10) ) engine=myisam;Query OK, 0 rows affected (0.01 sec)####ls -l ./testtotal 8-rw-r----- 1 root root 0 Sep 11 18:49 t1.MYD-rw-r----- 1 root root 1024 Sep 11 18:49 t1.MYI-rw-r----- 1 root root 2706 Sep 11 18:49 t1_326.SDIcat test/t1_326.SDI{ "sdi_version": 1, "dd_version": 1, "dd_object_type": "Table", "dd_object": { "name": "t1", "mysql_version_id": 80000, "created": 20160911185434, "last_altered": 20160911185434, "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;", "columns": [ { "name": "i", "type": 4, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": true, "is_virtual": false, "hidden": false, "ordinal_position": 1, "char_length": 11, "numeric_precision": 10, "numeric_scale": 0, "datetime_precision": 0, "has_no_default": false, "default_value_null": false, "default_value": "AAAAAA==", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "", "column_key": 2, "column_type_utf8": "int(11)", "elements": [], "collation_id": 8 }, { "name": "c", "type": 29, "is_nullable": true, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": false, "ordinal_position": 2, "char_length": 10, "numeric_precision": 0, "numeric_scale": 0, "datetime_precision": 0, "has_no_default": false, "default_value_null": true, "default_value": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "", "column_key": 1, "column_type_utf8": "char(10)", "elements": [], "collation_id": 8 } ], "schema_ref": "test", "hidden": false, "se_private_id": 18446744073709551615, "engine": "MyISAM", "comment": "", "se_private_data": "", "row_format": 1, "partition_type": 0, "partition_expression": "", "default_partitioning": 0, "subpartition_type": 0, "subpartition_expression": "", "default_subpartitioning": 0, "indexes": [ { "name": "PRIMARY", "hidden": false, "is_generated": false, "ordinal_position": 1, "comment": "", "options": "flags=0;", "se_private_data": "", "type": 1, "algorithm": 2, "is_algorithm_explicit": false, "is_visible": true, "engine": "MyISAM", "elements": [ { "ordinal_position": 1, "length": 4, "order": 2, "column_opx": 0 } ] } ], "foreign_keys": [], "partitions": [], "collation_id": 8 }}If the table has many columns, the structure becomes quite large. Let's try some new features:mysql [localhost] {root} ((none)) > create role role1;Query OK, 0 rows affected (0.01 sec)mysql [localhost] {root} ((none)) > grant all on test.* to role1;Query OK, 0 rows affected (0.01 sec)mysql [localhost] {root} ((none)) > create role role2;Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > grant select on *.* to role2;Query OK, 0 rows affected (0.01 sec)mysql [localhost] {root} ((none)) > select host, user from mysql.user;+-----------+-------------+| host | user |+-----------+-------------+| % | role1 || % | role2 || 127.% | msandbox || 127.% | msandbox_ro || 127.% | msandbox_rw || 127.% | rsandbox || localhost | msandbox || localhost | msandbox_ro || localhost | msandbox_rw || localhost | mysql.sys || localhost | root |+-----------+-------------+11 rows in set (0.00 sec)From this, we see that roles are users. In fact, you can assign a user to another user, as if it were a role.mysql [localhost] {root} ((none)) > show grants for role1;+-------------------------------------------------+| Grants for role1@% |+-------------------------------------------------+| GRANT USAGE ON *.* TO `role1`@`%` || GRANT ALL PRIVILEGES ON `test`.* TO `role1`@`%` |+-------------------------------------------------+2 rows in set (0.00 sec)mysql [localhost] {root} ((none)) > create user user1 identified by 'msandbox';Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > grant role1 to user1;Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > alter user user1 default role role1;Query OK, 0 rows affected (0.01 sec)mysql [localhost] {root} ((none)) > show grants for user1;+-----------------------------------+| Grants for user1@% |+-----------------------------------+| GRANT USAGE ON *.* TO `user1`@`%` || GRANT `role1`@`%` TO `user1`@`%` |+-----------------------------------+2 rows in set (0.00 sec)mysql [localhost] {root} ((none)) > show grants for user1 using role1;+-------------------------------------------------+| Grants for user1@% |+-------------------------------------------------+| GRANT USAGE ON *.* TO `user1`@`%` || GRANT ALL PRIVILEGES ON `test`.* TO `user1`@`%` || GRANT `role1`@`%` TO `user1`@`%` |+-------------------------------------------------+3 rows in set (0.00 sec) create user user2 identified by 'msandbox';Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > grant role2 to user2;Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > show grants for user2;+-----------------------------------+| Grants for user2@% |+-----------------------------------+| GRANT USAGE ON *.* TO `user2`@`%` || GRANT `role2`@`%` TO `user2`@`%` |+-----------------------------------+2 rows in set (0.00 sec)mysql [localhost] {root} ((none)) > show grants for user2 using role2;+------------------------------------+| Grants for user2@% |+------------------------------------+| GRANT SELECT ON *.* TO `user2`@`%` || GRANT `role2`@`%` TO `user2`@`%` |+------------------------------------+2 rows in set (0.01 sec)Now we connect with user1mysql [localhost] {user1} ((none)) > use mysqlERROR 1044 (42000): Access denied for user 'user1'@'%' to database 'mysql'mysql [localhost] {user1} ((none)) > use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql [localhost] {user1} (test) > show tables;+----------------+| Tables_in_test |+----------------+| t1 |+----------------+1 row in set (0.00 sec)That seems to be correct, as user1 can only access the test database. Let's try user2:mysql [localhost] {user2} ((none)) > show tables from test;ERROR 1044 (42000): Access denied for user 'user2'@'%' to database 'test'mysql [localhost] {user2} ((none)) > show grants;+-----------------------------------+| Grants for user2@% |+-----------------------------------+| GRANT USAGE ON *.* TO `user2`@`%` || GRANT `role2`@`%` TO `user2`@`%` |+-----------------------------------+2 rows in set (0.00 sec)mysql [localhost] {user2} ((none)) > set role role2;Query OK, 0 rows affected (0.00 sec)mysql [localhost] {user2} ((none)) > show tables from test;+----------------+| Tables_in_test |+----------------+| t1 |+----------------+1 row in set (0.01 sec)Here we have something that, unless we read the documentation carefully, we may miss. A role is ineffective if is not assigned. For user1, we did an ALTER USER and set the default role. Since we didn't do it for user2, we need to set the role explicitly before using its powers.We can also assign to a user several roles, and activate them all:mysql [localhost] {root} ((none)) > create user user3 identified by 'msandbox';Query OK, 0 rows affected (0.01 sec)mysql [localhost] {root} ((none)) > grant role1 to user3;Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > grant role2 to user3;Query OK, 0 rows affected (0.00 sec)mysql [localhost] {root} ((none)) > alter user user3 default role role1,role2;Query OK, 0 rows affected (0.00 sec)Now, when we connect with user3 we can see all tables (SELECT grant from role2) and create or modify tables in test (all privileges on test database from role1.)One final observation, just because it became crucial when I was starting to test these new binaries. Look at the size of the latest major versions of MySQL:$ ls -lh mysql-5.5.52-linux2.6-x86_64.tar.gz-rw-rw-r-- 1 root root 178M Aug 26 15:38 mysql-5.5.52-linux2.6-x86_64.tar.gz$ du -sh mysql-5.5.52-linux2.6-x86_64/690M mysql-5.5.52-linux2.6-x86_64/$ ls -lh mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz-rw-rw-r-- 1 root root 300M Aug 26 12:52 mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz$ du -sh mysql-5.6.33-linux-glibc2.5-x86_64/1.1G mysql-5.6.33-linux-glibc2.5-x86_64/$ ls -lh mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz-rw-rw-r-- 1 root root 612M Aug 25 13:08 mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz$ du -sh mysql-5.7.15-linux-glibc2.5-x86_64/2.5G mysql-5.7.15-linux-glibc2.5-x86_64/$ ls -lh mysql-8.0.0-dmr-linux-glibc2.12-x86_64.tar.gz-rwxr-xr-x 1 root root 961M Aug 26 17:37 mysql-8.0.0-dmr-linux-glibc2.12-x86_64.tar.gz$ du -sh mysql-8.0.0-dmr-linux-glibc2.12-x86_64/3.6G mysql-8.0.0-dmr-linux-glibc2.12-x86_64/ [Less]
Posted 15 days ago by Marcelo Altmann
Hi guys. MySQL 8 DMR was released today and it has some cool new features. One of those is the ability to persist dynamic changed variables/configurations across restarts. It’s very usefull if you change variables dynamically. It’s saves you the ... [More] trouble of edit a cnf file every time you run a SET on mysql (or even when you don’t have access to those files). The new syntax will be as follow: SET PERSIST option=value; SET @@persist.option=value; MySQL will create a new file named mysqld-auto.cnf located on it’s DATADIR folder. This file will contain all PERSISTENT variables and will be loaded after all other files (my.cnf / –defaults-file / ~/.my.cnf / …) Which means that values from mysqld-auto.cnf will take place in case of the same option be present on multiple files. Those variables can be UNSET by setting it to it’s default value, or manually edit the file on disk(not advised). This feature can be controlled by the persisted-globals-load config. It is set to ON by default. If you set it to OFF (persisted-globals-load=OFF), MySQL will ignore all variables present on mysqld-auto.cnf file. Variables can be listed from performance_schema.variables_info table. On column variable_source we can filter the ones that are persistent or from other source: COMPILED – Compiled on mysql source (default values) GLOBAL – Part of global file SERVER – Part of global $MYSQL_HOME/my.cnf file EXPLICIT – Part of –defaults-file option file EXTRA – Part of defaults-extra-file option file USER – Part of ~/.my.cnf LOGIN – Part of login path option file COMMAND_LINE – Command line options PERSISTED – part of persistent mysqld-auto.cnf file DYNAMIC – variables set dynamically after server start So we can get useful information from this table. For example, we can list all variables that were dynamically changed after the server started: select * from performance_schema.variables_info where variable_source like 'DYNAMIC'; MySQL 8.0 is available for Download at Be aware that it’s not a production release and should only be used for testing purposes. YET! Give MySQL 8.0 a try ! [Less]