Description:
As Documentation for mysqlfrm states:
"
PERMISSIONS REQUIRED
The permissions for using mysqlfrm will vary and depend entirely on how you use it. If you use the utility to read .frm files in a protected folder like the example above (in either mode), you must have the ability to run the server as root.
If you use the utility with a server connection, the user you use to connect must have the ability to read system variables at a minimum (read access to the mysql database). "
From documentation we understand that we can run this command as regular linux user, but with MySQL's root user.
1. If we specify .frm file as "{database:table.frm}":
[sh@centos7 ~]$ mysqlfrm --server=root:12345@localhost:3306 employees:salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files
ERROR: Cannot read .frm file from salaries.frm.
2. If we specify .frm file as full path it will spawn server, although it rises permission ERROR:
[sh@centos7 ~]$ mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
ERROR: Cannot read /var/lib/mysql/employees/salaries.frm. You must have read privileges to the file or path and it must exist. Skipping this argument.
.
.
# Checking read access to .frm files
# Creating a temporary datadir = /tmp/tmphgvLjo
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.
# Configuring new instance...
.
.
# Starting new instance of the server...
# Startup command for new server:
/usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/tmp/tmphgvLjo --pid-file=/tmp/tmphgvLjo/clone.pid --port=3333 --server-id=101 --socket=/tmp/tmphgvLjo/mysql.sock --tmpdir=/tmp/tmphgvLjo
.
.
# Success!
# Setting the root password...
Warning: Using a password on the command line interface can be insecure.
# Connection Information:
# -uroot -proot --socket=/tmp/tmphgvLjo/mysql.sock
#...done.
# Connecting to spawned server
done.
# Reading .frm files
# Shutting down spawned server
# Removing the temporary datadir
#...done.
So we use server connection with root user = and root user can read system variables. (ability to read system variables). Documentation should be much more clear.
Now if we decide to run mysqlfrm with Linux root user:
[root@centos7 new_datadir]# mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
Usage: mysqlfrm --server=[user[:<pass>]@host[:<port>][:<socket>]|<login-path>[:<port>][:<socket>]] [path\tbl1.frm|db:tbl.frm]
mysqlfrm: error: Running a spawned server as root is not advised. If you want to run the utility as root, please provide the --user option to specify a user to use to launch the server. Example: --user=mysql.
But if we use --user=root it will work as needed:
[root@centos7 ~]# mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv --user=root
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files
# Creating a temporary datadir = /tmp/tmpahExn_
# Spawning server with --user=root.
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.
# Configuring new instance...
.
.
/usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/tmp/tmpahExn_ --pid-file=/tmp/tmpahExn_/clone.pid --port=3333 --server-id=101 --socket=/tmp/tmpahExn_/mysql.sock --tmpdir=/tmp/tmpahExn_ --user=root
.
.
# Engine string: InnoDB
# Server version in file: 5.6.23
#
# CREATE statement for /var/lib/mysql/employees/salaries.frm:
#
CREATE TABLE `employees`.`salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
But if we specify --user=mysql (as documentation states) it will not work:
[root@centos7 ~]# mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv --user=mysql
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files
# Creating a temporary datadir = /root/e51075ef-8532-4e3c-ac33-4c76c74e1de2
# Spawning server with --user=mysql.
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.
# Startup command for new server:
/usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2 --pid-file=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2/clone.pid --port=3333 --server-id=101 --socket=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2/mysql.sock --tmpdir=/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2 --user=mysql
.
.
/usr/sbin/mysqld: Can't create/write to file '/root/e51075ef-8532-4e3c-ac33-4c76c74e1de2/ibjFAInl' (Errcode: 13 - Permission denied)
2015-04-21 19:39:57 7fc447425740 InnoDB: Error: unable to create temporary file; errno: 13
2015-04-21 19:39:57 4664 [ERROR] Plugin 'InnoDB' init function returned error.
2015-04-21 19:39:57 4664 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-04-21 19:39:57 4664 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-04-21 19:39:57 4664 [ERROR] Aborting
Now let's try with mysql user without --user option:
1. It will work correctly:
[root@centos7 ~]# su - mysql
Last login: Tue Apr 21 16:24:10 AZST 2015 on pts/2
-bash-4.2$ mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files
# Creating a temporary datadir = /tmp/tmpYzMNhg
# Starting the spawned server on port 3333 ...
# Cloning the MySQL server located at /usr.
2. now with --user=sh (previously failed) will work correctly too:
-bash-4.2$ mysqlfrm --server=root:12345@localhost:3306 /var/lib/mysql/employees/salaries.frm --port=3333 -vvv --user=sh
WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# Checking read access to .frm files
# Creating a temporary datadir = /tmp/tmpOj8Ot0
# Spawning server with --user=sh.
1. So in conclusion {database:table.frm} like syntax does not work.
2. With a regular Linux user it will rise an ERROR about read access, instead of terminating the process it will spawn server. --user=mysql and --user=root will not work.
3. With Linux root user without --user option it will throw an error about using --user=mysql even if you specify --user=mysql it will not work but if you specify --user=root it will by-pass this limitation.
4. With Linux mysql user --user=mysql, --user=root, --user=sh will work.
How to repeat:
All steps are provided in description
Suggested fix:
1. Maybe improvement in documentation about mysqlfrm
2. Regular user permission check before starting to spawn server
3. Disallow running with root, even specifying --user=root