MySQL 5.7 has more security enhancements. Some features were removed and some features are added. A temporary password will be generated at time of installation. It contains 12 characters with alpha-numeric characters for security purposes. In mysql.user table old_password field is removed and password field has changed as authendication_string. All anonymous-user accounts were removed . It will not create test database.
- Temporary Password on Installation
- Empty Password
- Password Expiry
Temporary Password on Installation:
RPM installation :
Needed rpm’s are to install mysql 5.7
rpm -ivh mysql-community-libs-5.7.8-0.2.rc .fc21.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.8-0.2.rc .fc21.x86_64.rpm
rpm -ivh mysql-community-client-5.7.8-0.2.rc .fc21.x86_64.rpm
rpm -ivh mysql-community-common-5.7.8-0.2.rc .fc21.x86_64.rpm
rpm -ivh mysql-community-server-5.7.8-0.2.rc.fc21.x86_64.rpm
[root@lab1.mydbops.com ~]# rpm -ivh mysql-community-common-5.7.8-0.2.rc.fc21.x86_64.rpm
warning: mysql-community-common-5.7.8-0.2.rc.fc21.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-common-5.7.8-0.2.################################# [100%]
[root@lab1.mydbops.com ~]# rpm -ivh mysql-community-libs-5.7.8-0.2.rc.fc21.x86_64.rpm
warning: mysql-community-libs-5.7.8-0.2.rc.fc21.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-libs-5.7.8-0.2.rc################################# [100%]
[root@lab1.mydbops.com ~]# rpm -ivh mysql-community-client-5.7.8-0.2.rc.fc21.x86_64.rpm
warning: mysql-community-client-5.7.8-0.2.rc.fc21.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-client-5.7.8-0.2.################################# [100%]
[root@lab1.mydbops.com ~]# rpm -ivh mysql-community-devel-5.7.8-0.2.rc.fc21.x86_64.rpm
warning: mysql-community-devel-5.7.8-0.2.rc.fc21.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-devel-5.7.8-0.2.r################################# [100%]
[root@lab1.mydbops.com ~]# rpm -ivh mysql-community-server-5.7.8-0.2.rc.fc21.x86_64.rpm
warning: mysql-community-server-5.7.8-0.2.rc.fc21.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:mysql-community-server-5.7.8-0.2.################################# [100%]
After installing the those rpm’s start the mysql using systemctl.
Redirecting to /bin/systemctl start mysqld.service
The password for the root has to be fetched from the MySQL log file (/var/log/mysqld.log )
Log Error :
2015-11-18T04:26:59.579992Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-11-18T04:27:00.007312Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-11-18T04:27:00.264807Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9c686910-8dac-11e5-97a4-fcaa14bf47d3.
2015-11-18T04:27:00.304861Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2015-11-18T04:27:00.316300Z 0 [Warning] Failed to setup SSL
2015-11-18T04:27:00.316342Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-11-18T04:27:00.317072Z 1 [Warning] A temporary password is generated for root@lab1.mydbops.com: db4=wlt(tdsI
2015-11-18T04:27:19.122724Z 0 [Note] InnoDB: PUNCH HOLE support available
2015-11-18T04:27:19.122772Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-11-18T04:27:19.122779Z 0 [Note] InnoDB: Uses event mutexes
2015-11-18T04:27:19.122785Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2015-11-18T04:27:19.122790Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-11-18T04:27:19.122800Z 0 [Note] InnoDB: Using Linux native AIO
2015-11-18T04:27:19.123004Z 0 [Note] InnoDB: Number of pools: 1
2015-11-18T04:27:19.123080Z 0 [Note] InnoDB: Using CPU crc32 instructions
2015-11-18T04:27:19.128566Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
………
………
2015-11-18T04:27:19.800293Z 0 [Note] Event Scheduler: Loaded 0 events
2015-11-18T04:27:19.800569Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.7.8-rc’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server (GPL)
MySQL server will prompt for a password reset after the first login. It is mandatory.
————–
mysql Ver 14.14 Distrib 5.7.8-rc, for Linux (x86_64) using EditLine wrapper
Connection id : 2
Current database :
Current user : root@lab1.mydbops.com
SSL : Not in use
Current pager : stdout
Using outfile : ”
Using delimiter : ;
Server version : 5.7.8-rc
Protocol version : 10
Connection : lab1.mydbops.com via UNIX socket
Server characterset : latin1
Db characterset : latin1
Client characterset : utf8
Conn. Characterset : utf8
UNIX socket : /var/lib/mysql/mysql.sock
Uptime : 2 min 39 sec
Threads: 1 Questions: 7 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.044
There is no test database in mysql 5.7 which was there until MySQL 5.6. Now we have sys (sys-schema) and additional tool to help DBA’s.
Empty Password :
By default you cannot create a user without a password in mysql 5.7. It shows error.
mysql> create user ‘mydbops_lab’@’lab1.mydbops.com’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
The variables to control password policy.
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
The password policy enforced by the validate_password plugin. This variable is unavailable unless that plugin is installed.
The validate_password_policy value can be specified using numeric values 0, 1, 2, or the corresponding symbolic values LOW, MEDIUM, STRONG. The following table describes the tests performed for each policy. For the length test, the required length is the value of the validate_password_length system variable. Similarly, the required values for the other tests are given by other validate_password_xxx variables.
By changing the default values you can create a user without password
mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.04 sec)
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=0;
Query OK, 0 rows affected (0.00 sec)
#After resetting the variables
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
+————————————–+——-+
| Variable_name | Value |
+————————————–+——-+
| validate_password_dictionary_file | |
| validate_password_length | 0 |
| validate_password_mixed_case_count | 0 |
| validate_password_number_count | 0 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 0 |
+————————————–+——-+
mysql> create user ‘mydbops_lab’@’lab1.mydbops.com’;
Query OK, 0 rows affected (0.12 sec)
Now IF NOT EXISTS clause can supported for create, drop, alter the user also.
mysql> create user if not exists ‘mydbops_lab’@’lab1.mydbops.com’;
Query OK, 0 rows affected, 1 warning (1.97 sec)
mysql> show warnings;
+——-+——+—————————————–+
| Level | Code | Message |
+——-+——+—————————————–+
| Note | 3163 | User ‘mydbops_lab’@’lab1.mydbops.com’ already exists. |
+——-+——+—————————————–+
1 row in set (0.00 sec)
mysql> create user if not exists ‘host’@’lab1.mydbops.com’;
Query OK, 0 rows affected (0.09 sec)
mysql> drop user if exists ‘mydbops_lab’@’lab1.mydbops.com’;
Query OK, 0 rows affected (0.00 sec)
mysql> create user if not exists ‘mydbops_lab’@’lab1.mydbops.com’;
Query OK, 0 rows affected (0.00 sec)
The mysql.user.password filed is now changed to authentication_string for better describe the current usage.
Extra added columns are
- password_last_changed
- password_lifetime
- account_locked
password_last_changed – It shows the date and time of last changed password
password_lifetime – It shows the password expire time
account_locked – It shows whether the user is locked or not
Password Expiry :
All users in MySQL 5.7 has lifetime of only 365 days by default. After the expiry the user exists inside mysql but the access is restricted . The user ‘mydbops_lab’ has set the password_lifetime as 90 days
mysql> alter user ‘mydbops_lab’@’lab1.mydbops.com’ account lock;
mysql> select user,host,password_last_changed,password_lifetime,account_locked from mysql.user where user=”mydbops_lab“;
+——+———–+———————–+——————-+—————-+
| user | host | password_last_changed | password_lifetime | account_locked |
+——+———–+———————–+——————-+—————-+
|mydbops_lab | lab1.mydbops.com | 2015-11-18 15:59:14 | 90 | Y |
+——+———–+———————–+——————-+—————-+
.png)
.png)
.svg)
