Labs releases are intended to provide early access to new features. As Gopal notes in his recent blog post About the Data Dictionary Labs Release, there is a notable restriction where upgrading from any previous MySQL database version is not supported.
Today, I thought I would demonstrate how to get the data dictionary lab up and running on a fresh Ubuntu 14.04 installation:
# Download from labs.mysql.com wget http://downloads.mysql.com/snapshots/pb/mysql-5.7.5-labs-dd/mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz # extract it to /usr/local/mysql # more or less following instructions in INSTALL-BINARY groupadd mysql useradd -r -g mysql mysql tar -xzf mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz mv mysql-5.7.5-labs-dd-linux-el6-x86_64 /usr/local/ ln -s /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64 /usr/local/mysql cd /usr/local/mysql chown -R mysql . chgrp -R mysql .
At this point, the next step in the instructions actually failed for me:
bin/mysql_install_db --user=mysql 2014-10-15 09:22:34 [ERROR] The data directory needs to be specified.
This behavior change appears to be the result of mysql_install_db being rewritten in C++. Specifying the datadir addresses this, but also results in another error:
bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data 2014-10-15 09:24:15 [ERROR] The child process terminated prematurely. Errno= 32 2014-10-15 09:24:15 [ERROR] Failed to execute /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld --no-defaults --install-server --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US -- server log begin --
Error 32 is a broken pipe. From the description, we can see that mysql_install_db tried to call a subprocess (mysqld –install-server), but it exited prematurely. The description here doesn’t show why it exited prematurely, but we can execute this command directly to find out:
/usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld --no-defaults --install-server --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
OK! We don’t have the async IO dependency installed on this system, which is used by InnoDB:
apt-get install libaio1 Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-3.13.0-36 linux-headers-3.13.0-36-generic linux-image-3.13.0-36-generic linux-image-extra-3.13.0-36-generic Use 'apt-get autoremove' to remove them. The following NEW packages will be installed: libaio1 0 upgraded, 1 newly installed, 0 to remove and 3 not upgraded. Need to get 6,364 B of archives. After this operation, 53.2 kB of additional disk space will be used. Get:1 http://mirrors.digitalocean.com/ubuntu/ trusty/main libaio1 amd64 0.3.109-4 [6,364 B] Fetched 6,364 B in 0s (319 kB/s) Selecting previously unselected package libaio1:amd64. (Reading database ... 116531 files and directories currently installed.) Preparing to unpack .../libaio1_0.3.109-4_amd64.deb ... Unpacking libaio1:amd64 (0.3.109-4) ... Setting up libaio1:amd64 (0.3.109-4) ... Processing triggers for libc-bin (2.19-0ubuntu6.3) ...
Now to try the original install command again:
bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
It should return no errors now. To follow the remaining steps in the INSTALL-BINARY instructions:
chown -R root . chown -R mysql data bin/mysqld_safe --user=mysql &
In MySQL 5.7, installation is secure by default. This means that a root password is automatically generated, and stored in ~/.mysql_secret:
cat ~/.mysql_secret # Password set for user 'root@localhost' at 2014-10-15 09:32:09 Wpckgmde+U,o
It is possible to change this password with the MySQL command line client:
./bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.5-labs-dd
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET PASSWORD=PASSWORD('acdc');
Query OK, 0 rows affected (0.00 sec)
As Gopal mentioned in his blog post, the labs release runs completely without frm files:
ls /usr/local/mysql/data/mysql catalogs.ibd help_relation.ibd slave_relay_log_info.ibd character_sets.ibd help_topic.ibd slave_worker_info.ibd collations.ibd index_column_usage.ibd slow_log.CSM columns.ibd indexes.ibd slow_log.CSV columns_priv.MYD index_partitions.ibd table_partitions.ibd columns_priv.MYI index_stats.ibd table_partition_values.ibd column_type_elements.ibd innodb_index_stats.ibd tables.ibd db.MYD innodb_table_stats.ibd tablespace_files.ibd db.MYI ndb_binlog_index.MYD tablespaces.ibd db.opt ndb_binlog_index.MYI tables_priv.MYD engine_cost.ibd parameters.ibd tables_priv.MYI event.MYD plugin.ibd table_stats.ibd event.MYI proc.MYD time_zone.ibd events.ibd proc.MYI time_zone_leap_second.ibd foreign_key_column_usage.ibd procs_priv.MYD time_zone_name.ibd foreign_keys.ibd procs_priv.MYI time_zone_transition.ibd func.MYD proxies_priv.MYD time_zone_transition_type.ibd func.MYI proxies_priv.MYI triggers.ibd general_log.CSM routines.ibd user.MYD general_log.CSV schemata.ibd user.MYI gtid_executed.ibd server_cost.ibd view_table_usage.ibd help_category.ibd servers.ibd help_keyword.ibd slave_master_info.ibd
information_schema is a now also a set of views on top of real InnoDB tables (stored in the mysql schema). As a VIEW we can explain it, and since extended explain is always enabled in 5.7, SHOW WARNINGS will show the rewritten form:
mysql> SELECT * FROM information_schema.tables WHERE table_name = 'user'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: mysql
TABLE_NAME: user
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 1
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: NULL
AUTO_INCREMENT: 1
CREATE_TIME: 2014-10-15 09:32:10
UPDATE_TIME: 2014-10-15 09:32:10
CHECK_TIME:
TABLE_COLLATION: utf8_bin
CHECKSUM: NULL
CREATE_OPTIONS: stats_persistent=1
TABLE_COMMENT: Users and global privileges
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM information_schema.tables WHERE table_name = 'user'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cat
partitions: NULL
type: index
possible_keys: PRIMARY
key: name
key_len: 194
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: sch
partitions: NULL
type: ref
possible_keys: PRIMARY,catalog_id
key: catalog_id
key_len: 8
ref: mysql.cat.id
rows: 3
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: eq_ref
possible_keys: schema_id
key: schema_id
key_len: 202
ref: mysql.sch.id,const
rows: 1
filtered: 100.00
Extra: Using index condition
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: col
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: mysql.tbl.collation_id
rows: 1
filtered: 100.00
Extra: NULL
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: stat
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 388
ref: mysql.sch.name,const
rows: 1
filtered: 100.00
Extra: NULL
5 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,`tbl`.`engine` AS `ENGINE`,`tbl`.`version` AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,ifnull(`stat`.`table_rows`,1) AS `TABLE_ROWS`,`stat`.`avg_row_length` AS `AVG_ROW_LENGTH`,`stat`.`data_length` AS `DATA_LENGTH`,`stat`.`max_data_length` AS `MAX_DATA_LENGTH`,`stat`.`index_length` AS `INDEX_LENGTH`,`stat`.`data_free` AS `DATA_FREE`,ifnull(`stat`.`auto_increment`,1) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,`tbl`.`last_altered` AS `UPDATE_TIME`,'' AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,`stat`.`checksum` AS `CHECKSUM`,get_dd_create_options(`tbl`.`options`) AS `CREATE_OPTIONS`,`tbl`.`comment` AS `TABLE_COMMENT` from `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` left join `mysql`.`collations` `col` on((`col`.`id` = `tbl`.`collation_id`)) left join `mysql`.`table_stats` `stat` on(((`stat`.`schema_name` = `sch`.`name`) and (`stat`.`table_name` = 'user'))) where ((`tbl`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`tbl`.`name` = 'user') and can_access_table(`sch`.`name`,`tbl`.`name`)) order by `sch`.`name`,`tbl`.`name`
1 row in set (0.00 sec)
That’s it for today. Enjoy testing the data dictionary, and please send in your feedback!