1

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

Improve this question
4
  • 1
    Runs quicker and suppresses any output - which is what you want in a script. If interacting with the server as a human, you might well want helpful stuff like column names and/or name-completion. You can suppress these if using interactively by running the client with -AN. Check it out and see for yourself.
    – Vérace
    Commented Feb 24, 2016 at 20:21
  • @Vérace That makes sense, it just seemed unusual since many other examples don't include these arguments.
    – JYelton
    Commented Feb 24, 2016 at 21:33
  • 1
    Rolando is very thorough - otherwise he wouldn't have gazillions of rep on this site. He has taken the time and trouble to check out the options and make use of them - it's a bit like SQL - many are called, few are chosen. By that I mean that many people can extract data from an RDBMS, but there are precious few who can do it optimally under virtually all circumstances.
    – Vérace
    Commented Feb 24, 2016 at 21:51
  • 1
    I think part of your confusion is... There are two processes -- the server mysqld and the client program you are running, mysql. The server must be already running; the client starts faster with that option.
    – Rick James
    Commented Mar 3, 2016 at 17:33

1 Answer 1

Reset to default
2

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.