I've recently changed how a mysqldump backup is set up, and happened across a helpful answer by RolandoMySQLDBA where his example:
mysql ${MYSQL_CONN} -ANe"${SQL}"
Uses the -ANe
command line switch to precede the query to execute.
I understand that these have the following effects:
--no-auto-rehash, -A
Disables auto-rehash, which is described as: Enable automatic
rehashing. This option is on by default, which enables database,
table, and column name completion. Use--disable-auto-rehash
to
disable rehashing. That causes mysql to start faster, but you must
issue the rehash command or its # shortcut if you want to use name
completion.
--skip-column-names, -N
Do not write column names in results.
–
-execute=statement, -e statement
Execute the statement and quit.
Obviously, I understand that since we are executing a query, the -e
argument is required.
The query I am executing is changing the global variable to enable/disable the slow query log. I'm not quite sure how omitting column names in results is important in this case. I'm less clear on how disabling auto rehashing is necessary or precisely what it is doing. I understand from the description that it causes mysql to start faster, but since the mysql process is already running, isn't that ignored?
In short, what are the -A and -N arguments for in the context of changing a global variable via shell script?
MySQL 5.6.4 on Ubuntu 14.04 LTS
Best Answer
The biggest reason was already stated by VĂ©race, but I wanted to add to it
When a database instance has lots of tables and lots of columns, the information_schema has to inspected in memory. This can be a cause for alarm. Why ?
Back in April 2014, I wrote the answer to Do Inactive MySQL Databases Consume Memory? and Adding new tables -- memory usage increases.
I have seen MySQL instances with 800+ databases with each database containing 162 tables.
Imagine loading metadata for the DB you connect to. Lots of needless prep work for the beginning of a DB Connection's LifeCycle along with allocating memory for a local copy of that metadata in addition to per-session buffers (See my post How costly is opening and closing of a DB connection?)
This is the biggest reason why I use
-A
to connect to do little things.