MySQL mysql_config_editor & expect

This is just a note to help out anyone that might want to use the mysql_config_editor command in their automation tools. 

the mysql_config_editor does not take a password argument so automation tools that might have before set your password in the .my.cnf file trying to use mysql_config_editor fails. 

It is possible and quite simple though with the expect tool. 

 yum -y install expect  

it works for apt-get also. 

So in this example, I will show a simple bash script version. 

1st.. my login path does not work... 

mysql --login-path=local

ERROR 1045 (28000): Access denied for user

Set this with expect 

You would execute this via your bash script.  

expect <<EOD

spawn mysql_config_editor set --login-path=local --host=localhost --user=root --password 

expect "password"

send  -- "<PASSWORD>\r"

interact

EOD

Now it works...

mysql --login-path=local

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1002

Every MySQL should have these variables set ...

So over the years, we all learn more and more about what we like and use often in MySQL. 

Currently, I step in and out of a robust about of different systems. I love it being able to see how different companies use MySQL.  I also see several aspect and settings that often get missed. So here are a few things I think should always be set and they not impact your MySQL database. 

At a high level:

• >Move the Slow log to a table 
• Set report_host_name 
• Set master & slaves to use tables
• Turn off log_queries_not_using_indexes until needed 
• Side note -- USE  ALGORITHM=INPLACE
• Side note -- USE mysql_config_editor
• Side note -- USE  mysql_upgrade  --upgrade-system-tables

Move the Slow log to a table 

This is a very simple process with a great return. YES you can use Percona toolkit to analyze the slow logs. However, I like being able to query against the table and find duplicate queries or by times and etc with a simple query call. 


mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|       0 |
+----------+
1 row in set (0.00 sec)

mysql> select @@slow_query_log,@@sql_log_off;
+------------------+---------------+
| @@slow_query_log | @@sql_log_off |
+------------------+---------------+
|                1 |            0 |
+------------------+---------------+

mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)

mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)

mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)

mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)

mysql> set GLOBAL slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set GLOBAL sql_log_off=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|       0 |
+----------+
1 row in set (0.00 sec)
mysql> select @@slow_launch_time;
+--------------------+
| @@slow_launch_time |
+--------------------+
|                   2 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SLEEP(10);
+-----------+
| SLEEP(10) |
+-----------+
|         0 |
+-----------+
1 row in set (9.97 sec)

mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|         1 |
+----------+
1 row in set (0.00 sec)

mysql> select * from   mysql.slow_log\G
*************************** 1. row ***************************
    start_time: 2019-03-27 18:02:32
     user_host: klarson[klarson] @ localhost []
    query_time: 00:00:10
     lock_time: 00:00:00
     rows_sent: 1
rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 502
      sql_text: SELECT SLEEP(10)
     thread_id: 16586457


Now you can truncate it or dump it or whatever you like to do with this data easily also.
Note variable values into your my.cnf file to enable upon restart.


Set report_host_name 

This is a simple my.cnf file edit in all my.cnf files but certainly the slaves my.cnf files. On a master.. this is just set for when it ever gets flipped and becomes a slave.


report_host                     = <hostname>  <or whatever you want to call it>


This allows you from the master to do


mysql> show slave hosts;
+-----------+-------------+------+-----------+--------------------------------------+
| Server_id | Host         | Port | Master_id | Slave_UUID                           |
+-----------+-------------+------+-----------+--------------------------------------+
|   21235302 | <hostname>  | 3306 |   

21235301| a55faa32-c832-22e8-b6fb-e51f15b76554 |
+-----------+-------------+------+-----------+--------------------------------------+

Set master & slaves to use tables


mysql> show variables like '%repository';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository     | FILE   |
| relay_log_info_repository | FILE   |
+---------------------------+-------+

mysql_slave> stop slave;
mysql_slave> SET GLOBAL master_info_repository = 'TABLE'; 
mysql_slave> SET GLOBAL relay_log_info_repository = 'TABLE'; 
mysql_slave> start slave;



Make sure you add to my.cnf to you do not lose binlog and position at a restart. It will default to FILE otherwise.

• master-info-repository =TABLE 
• relay-log-info-repository =TABLE


mysql> show variables like '%repository';
---------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository     | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+



All data is available in tables now and easily stored with backups


mysql> desc mysql.slave_master_info;
+------------------------+---------------------+------+-----+---------+-------+
| Field                   | Type                 | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| Number_of_lines         | int(10) unsigned     | NO   |     | NULL     |       |
| Master_log_name         | text                 | NO   |     | NULL     |       |
| Master_log_pos         | bigint(20) unsigned | NO   |     | NULL     |       |
| Host                   | char(64)             | YES   |     | NULL     |       |
| User_name               | text                 | YES   |     | NULL     |       |
| User_password           | text                 | YES   |     | NULL     |       |
| Port                   | int(10) unsigned     | NO   |     | NULL     |       |
| Connect_retry           | int(10) unsigned     | NO   |     | NULL     |       |
| Enabled_ssl             | tinyint(1)           | NO   |     | NULL     |       |
| Ssl_ca                 | text                 | YES   |     | NULL     |       |
| Ssl_capath             | text                 | YES   |     | NULL     |       |
| Ssl_cert               | text                 | YES   |     | NULL     |       |
| Ssl_cipher             | text                 | YES   |     | NULL     |       |
| Ssl_key                 | text                 | YES   |     | NULL     |       |
| Ssl_verify_server_cert | tinyint(1)           | NO   |     | NULL     |       |
| Heartbeat               | float               | NO   |     | NULL     |       |
| Bind                   | text                 | YES   |     | NULL     |       |
| Ignored_server_ids     | text                 | YES   |     | NULL     |       |
| Uuid                   | text                 | YES   |     | NULL     |       |
| Retry_count             | bigint(20) unsigned | NO   |     | NULL     |       |
| Ssl_crl                 | text                 | YES   |     | NULL     |       |
| Ssl_crlpath             | text                 | YES   |     | NULL     |       |
| Enabled_auto_position   | tinyint(1)           | NO   |     | NULL     |       |
| Channel_name           | char(64)             | NO   | PRI | NULL     |       |
| Tls_version             | text                 | YES   |     | NULL     |       |
| Public_key_path         | text                 | YES   |     | NULL     |       |
| Get_public_key         | tinyint(1)           | NO   |     | NULL     |       |
+------------------------+---------------------+------+-----+---------+-------+
27 rows in set (0.05 sec)

mysql> desc mysql.slave_relay_log_info;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                 | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| Number_of_lines   | int(10) unsigned     | NO   |     | NULL     |       |
| Relay_log_name     | text                 | NO   |     | NULL     |       |
| Relay_log_pos     | bigint(20) unsigned | NO   |     | NULL     |       |
| Master_log_name   | text                 | NO   |     | NULL     |       |
| Master_log_pos     | bigint(20) unsigned | NO   |     | NULL     |       |
| Sql_delay         | int(11)             | NO   |     | NULL     |       |
| Number_of_workers | int(10) unsigned     | NO   |     | NULL     |       |
| Id                 | int(10) unsigned     | NO   |     | NULL     |       |
| Channel_name       | char(64)             | NO   | PRI | NULL     |       |

+-------------------+---------------------+------+-----+---------+-------+




Turn off log_queries_not_using_indexes until needed 

This was shown above also. This is a valid variable .. but depending on application it can load a slow log with useless info. Some tables might have 5 rows in it, you use it for some random drop down and you never put an index on it. With this enabled every time you query that table it gets logged. Now.. I am a big believer in you should put an index on it anyway. But focus this variable when you are looking to troubleshoot and optimize things. Let it run for at least 24hours so you get a full scope of a system if not a week.


mysql> SET GLOBAL log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)

To turn on 


mysql> SET GLOBAL log_queries_not_using_indexes=1;

Query OK, 0 rows affected (0.00 sec)


Note variable values into your my.cnf file to enable upon restart. 


Side note -- USE  ALGORITHM=INPLACE 

OK this is not a variable but more of a best practice. You should already be using EXPLAIN before you run a query, This shows you the query plan and lets you be sure all syntax is valid.  I have seen more than once a Delete query executed without an WHERE by mistake. So 1st always use EXPLAIN to double check what you plan to do.  Not the other process you should always do is try to use an ALGORITHM=INPLACE or  ALGORITHM=COPY when altering tables. 


mysql> ALTER TABLE TABLE_DEMO   ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0   Duplicates: 0   Warnings: 0



mysql> ALTER TABLE TABLE_DEMO   ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0   Duplicates: 0   Warnings: 0


A list of online DLL operations is here

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html 

Side note -- USE mysql_config_editor

Previous blog post about this is here 

https://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html  

The simple example


mysql_config_editor set  --login-path=local --host=localhost --user=root --password
Enter password:
# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost

# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


# mysql  --login-path=local
Welcome to the MySQL monitor.

# mysql  --login-path=local -e 'SELECT NOW()';


Side note -- USE  mysql_upgrade  --upgrade-system-tables

Don't forget to use mysql_upgrade after you actually upgrade. 

This is often forgotten and leads to issues and errors at start up. You do not have to run upgrade across every table that exists though.  The focus of most upgrade are the system tables. So at the very least focus on those. 

mysql_upgrade --login-path=local  --upgrade-system-tables

Rotating MySQL Slow Logs

While working with different clients I happen to run across very large slow log files from time to time.  While several opinions on how they should be rotated exist. Many of these opinions use log rotate and the flush logs command,  I prefer not to flush my binary logs though. This is why I agree with Ronald Bradford's blog post from years ago on  how to do this.

• http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/

I have taken it a little further and scripted the steps. The bash script is built with MySQL 5.6 and the mysql_config_editor in mind it can be used on older versions of MySQL as well.

The script will do the following:

• Gather current log file name
• Gather current long query time value
• Resets the long query time to a higher value
• Copies the log while truncating it as well (See Ronald's Post)
• Resets the long query time back to the original time
• Executes a simple slow query so you can check the new slow log if you wish
• Removes the older slow log so you can gain space back. 
• You can comment this command out if you wish to review the log instead.

So does it all work ?
Well let us use this example.

I am currently using the mysql_config_editor over a .my.cnf file so I updated the script accordingly.

#  mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost


I can see that this slow query log is now 1G.

# ls -alh mysql-slow.log
-rw-rw---- 1 mysql mysql 1.1G Oct 11 16:08 mysql-slow.log


So I execute the script

 # /root/rotate_slow_logs.sh
 # ls -alh mysql-slow.log
-rw-rw---- 1 mysql mysql 5.8K Oct 11 16:11 mysql-slow.log


Ok good, it worked and I have a smaller log file now without flushing my bin logs or restarting MySQL.

This script can be added to a crontab so you can rotate as often as you would like.

Here is the script.

#!/bin/bash

# THIS IS BUILT WITH MYSQL 5.6 SECURITY IN MIND.
# SET THE LOGINPATHVALUE if you are using the mysql_config_editor
# IF YOU ARE NOT USING THE mysql_config_editor THEN IT IS ASSUMED YOU HAVE
# SET A .my.cnf FILE IN THE USER HOME DIR OR THIS USER HAS NO PASSWORD SET


# PLEASE SET THIS ACCORDINGLY TO YOUR SYSTEM.
LOGINPATHVALUE="local";

if [ -z "${LOGINPATHVALUE}" ]; then
LOGINPATH="";
fi

if [ -n "${LOGINPATHVALUE-unset}" ]; then
LOGINPATH="--login-path=$LOGINPATHVALUE "

fi

# GATHERS THE LOG FILE NAME
SLOWLOG=$(mysqladmin $LOGINPATH variables | grep slow | grep file |   awk '/[a-zA-Z]/ {print $4}' )

# GATHER CURRENT VALUE
LQT=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )
LQTB=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *200 AS LQTB;" |   awk '/[0-9]./ {print $1}'  )
LQTC=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *2 AS LQTC;" |   awk '/[0-9]./ {print $1}'   )

# GATHER MARKER
DATE=`date +"%m%d%Y"`

# RESET SLOW QUERY TIME
# SET GLOBAL long_query_time=10;
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQTB"

LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )

#MOVE THE LOG OUT
cp $SLOWLOG $SLOWLOG.$DATE; > $SLOWLOG

#SET THE TIMEBACK
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQT"

LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )

#PLACE A Slow query for log
SLOWQUERY=$(mysql $LOGINPATH -e "SELECT sleep($LQTC) " )

# REMOVE OLD LOG
/bin/rm -f $SLOWLOG.$DATE;

MySQL Check Table

The MySQL Check tables command is very useful for anyone who wants to do the following:

• Checking Version Compatibility 
• Checking Data Consistency 
• Upgrades
• General Table Errors

 The process is simple enough:

> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+

> check table City\G
*************************** 1. row ***************************
   Table: world.City
      Op: check
Msg_type: status
Msg_text: OK


This is a good task to stay updated on so you are aware of possible errors. One possible problem is that this tool really focuses on MyISAM and not InnoDB. If you use it for InnoDB, The "Check table" command really only applies when you add the QUICK option (or no options).  The FAST, CHANGED, MEDIUM and EXTENDED options are all ignored for InnoDB. Now, if you are asking yourself, what about InnoDB? Why would MySQL ignore data consistency in the InnoDB engine? Take a deep breath and relax, InnoDB is ACID complaint, ACID is "an acronym standing for atomicity, consistency, isolation, and durability." So do not disregard checking InnoDB tables because it can still provide you some insight or confirmation on your tables. Keep in mind that if an InnoDB table was to be corrupted the server will shut down to protect the data. You just get more bang for your buck with MyISAM tables and this tool.

Hopefully you get a response of "OK" or "Table is already up to date" otherwise you need to run a repair table to fix the table.

So what are the options available to us so you can do this often and easily.
The documentation link below will also provide you with several community driven automatic options. You can script it the process and easily show tables then apply check tables to all of your results. It just appears easier to me though to use the tools provided for you.


$ mysqlcheck -u root -p --databases world --fast
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK

$mysqlcheck -u root -p --databases world --fast --check-only-changed
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK


Now this is simple and direct but it also lends itself to another question, What about the password?

Should you create a user with no password that is allowed to check tables just so you do not have to put a password into your script or cron job? You want to prevent having the password sitting around in .mysql_history files as well.  So again take advantage of the tools available for you. MySQL 5.6 introduced the MySQL Configuration Utility.  I have an example of how to set it up in a previous blog post:
http://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html

mysqlcheck --login-path=local  --databases world --fast --check-only-changed
world.City                              OK
world.Country                           OK
world.CountryLanguage                   OK


$ mysqlcheck --help will provide a full list of options available to you.
Now, you can check all of your tables, keep your passwords out of the crontab file and/or scripts.

Documentation:

• http://dev.mysql.com/doc/refman/5.5/en/check-table.html

MySQL Users :: Grants :: mysql_config_editor :: Security

Secure access to the database is likely priority number one for any database administrator. If it is not then you need to seriously look into why it is not.

General guidelines via the manual are already available:

• http://dev.mysql.com/doc/refman/5.6/en/password-security.html
• http://dev.mysql.com/doc/refman/5.6/en/password-security-user.html
• http://dev.mysql.com/doc/refman/5.6/en/create-user.html
• http://dev.mysql.com/doc/refman/5.6/en/grant.html
• http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

One of the primary issues with security in MySQL is of course the permissions that you give users.
These are a few simply guidelines.

First keep "super user" or "root" accounts to a minimum. A user with full access or "GRANT ALL" will still have access when you have reached your max connections. So the last thing you would want is a program to be executing commands with a user with full access.

Keep in mind what types of accounts you are creating. You can limit a user to MAX QUERIES, MAX UPDATES, MAX CONNECTIONS and MAX USER CONNECTIONS per HOUR.

Keep in mind of the network environment your users are connecting from. If users are going to be using DHCP network addresses within the same subnet you would just be creating more work for yourself if you limited them to a single IP. You can still limit them to the subnet though with a wildcard. For example '192.168.0.2' versus '192.168.0.%'

Stay away from entire wildcard access for host and users.



> CREATE USER ''@'192.168.0.56' ;
Query OK, 0 rows affected (0.02 sec)

> show grants for ''@'192.168.0.56';
+-----------------------------------------+
| Grants for @192.168.0.56                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO ''@'192.168.0.56' |
+-----------------------------------------+



This would leave you wide open for anyone from 192.168.0.56 and is not a smart secure thing to do.
It could also violate other accounts from 192.168.0.56 because MySQL checks host first and username second.


> GRANT SELECT ON test.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY 'somepassword';

> show grants for 'exampleuser'@'192.168.0.%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@192.168.0.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT ON `test`.* TO 'exampleuser'@'192.168.0.%'                                                              |
+----------------------------------------------------------------------------------------------------------------------+



This will allow selects only for exampleuser from '192.168.0.%'. You must also keep in mind that if exampleuser is connecting from LOCAL HOST that the system will likely user localhost first before the 192.168.0.% subnet address unless the user used the subnet address the host to connect to.

This means that you can create one user and password with different privileges per host.



> SHOW GRANTS FOR 'exampleuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'localhost' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT, UPDATE, DELETE ON `test`.* TO 'exampleuser'@'localhost'                                              |
+--------------------------------------------------------------------------------------------------------------------+



Try your best to not use the --password=<password> option via the mysql client. You can use -p to prompt for a password.

You also have the option with MySQL 5.6 to use the MySQL Configuration Utility.


# mysql_config_editor set  --login-path=local --host=localhost --user=root --password
Enter password:







# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost





# mysql 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# mysql  --login-path=local

Welcome to the MySQL monitor.

# mysql  --login-path=local -e 'SELECT NOW()';

You do have options to name different paths like local or remote and etc as well. So you can encrypt more than one access user account in your ~/.mylogin.cnf file that is created once you use the set command.

If you have shell scripts that use the mysql client and likely then have passwords in the scripts updating them to use the "--login-path=" is a more secure way to go.


Of course when you no longer need a user... Drop the user.


> DROP USER 'exampleuser'@'localhost';