Learn MySQL Topics : Security, Backups, Replication, Architecture, Install, Update, Ect. Flashcards
1
Q
Authentication Plugins
1 Native Pluggable Authentication
2 Caching SHA-2 Pluggable Authentication
3 SHA-256 Pluggable Authentication
4 Client-Side Cleartext Pluggable Authentication
5 PAM Pluggable Authentication
6 Windows Pluggable Authentication
7 LDAP Pluggable Authentication
8 6.4.1.8 No-Login Pluggable Authentication
9 Socket Peer-Credential Pluggable Authentication
A
The MySQL 8.0 default plugin is indicated by the value of the default_authentication_plugin system variable.
mysql> show variables like ‘default_authentication_plugin’;
+——————————-+———————–+
| Variable_name | Value |
+——————————-+———————–+
| default_authentication_plugin | caching_sha2_password |
+——————————-+———————–+
1 row in set (0.00 sec)
2
Q
Installing Native Pluggable Authentication
The mysql_native_password plugin exists in server and client forms:
The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.
The client-side plugin is built into the libmysqlclient client library and is available to any program linked against libmysqlclient.
A
Using Native Pluggable Authentication
MySQL client programs use mysql_native_password by default. The –default-auth option can be used as a hint about which client-side plugin the program can expect to use:
shell> mysql –default-auth=mysql_native_password …
3
Q
Pluggable authentication enables these important capabilities:
Choice of authentication methods
External authentication.
Proxy users
A
Pluggable authentication enables these important capabilities:
Choice of authentication methods. Pluggable authentication makes it easy for DBAs to choose and change the authentication method used for individual MySQL accounts.
External authentication. Pluggable authentication makes it possible for clients to connect to the MySQL server with credentials appropriate for authentication methods that store credentials elsewhere than in the mysql.user system table. For example, plugins can be created to use external authentication methods such as PAM, Windows login IDs, LDAP, or Kerberos.
Proxy users: If a user is permitted to connect, an authentication plugin can return to the server a user name different from the name of the connecting user, to indicate that the connecting user is a proxy for another user (the proxied user). While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of the proxied user. In effect, one user impersonates another.
4
Q
Pluggable Authentication
If you start the server with the –skip-grant-tables option, authentication plugins are not used even if loaded because the server performs no client authentication and permits any client to connect. Because this is insecure, if the server is started with the –skip-grant-tables option, it also disables remote connections by enabling skip_networking.
A
This option generally is only used if you forget the root password and need to reset it.
you must shutdown the MySQL database instance.
Then edit the my.cnf file to add:
[mysqld]
skip-grant-tables
Save and exit.
Start the service again and you should be able to log into your database without a password.
5
Q
PAM Pluggable Authentication
PAM pluggable authentication is an extension included in MySQL Enterprise Edition, a commercial product.
A
MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use PAM (Pluggable Authentication Modules) to authenticate MySQL users. PAM enables a system to use a standard interface to access various kinds of authentication methods, such as traditional Unix passwords or an LDAP directory.
6
Q
PAM pluggable authentication provides these capabilities:
External authentication:
Proxy user support:
A
PAM pluggable authentication provides these capabilities:
External authentication: PAM authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables and that authenticate using methods supported by PAM.
Proxy user support: PAM authentication can return to MySQL a user name different from the external user name passed by the client program, based on the PAM groups the external user is a member of and the authentication string provided. This means that the plugin can return the MySQL user that defines the privileges the external PAM-authenticated user should have. For example, an operating system user named joe can connect and have the privileges of a MySQL user named developer.
7
Q
Plugin and Library Names for PAM Authentication
A
Plugin and Library Names for PAM Authentication
Server-side plugin authentication_pam
Client-side plugin mysql_clear_password
Library file authentication_pam.so
8
Q
PAM Authentication and libmysqlclient client library
A
The client-side mysql_clear_password cleartext plugin that communicates with the server-side PAM plugin is built into the libmysqlclient client library and is included in all distributions, including community distributions. Inclusion of the client-side cleartext plugin in all MySQL distributions enables clients from any distribution to connect to a server that has the server-side PAM plugin loaded.
9
Q
Installing PAM Pluggable Authentication
To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.
The plugin library file base name is authentication_pam. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).
To load the plugin at server startup, use the –plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file, adjusting the .so suffix for your platform as necessary:
[mysqld]
plugin-load-add=authentication_pam.so
After modifying my.cnf, restart the server to cause the new settings to take effect.
A
Alternatively, to load the plugin at runtime, use this statement, adjusting the .so suffix for your platform as necessary:
INSTALL PLUGIN authentication_pam SONAME ‘authentication_pam.so’;
INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for –plugin-load-add.
10
Q
To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement
A
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%pam%';
\+--------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
\+--------------------+---------------+
| authentication_pam | ACTIVE |
\+--------------------+---------------+
11
Q
Native Pluggable Authentication
MySQL includes a mysql_native_password plugin that implements native authentication; that is, authentication based on the password hashing method in use from before the introduction of pluggable authentication.
A
Plugin and Library Names for Native Password Authentication
Plugin or File Plugin or File Name
Server-side plugin mysql_native_password
Client-side plugin mysql_native_password
Library file None (plugins are built in)
12
Q
Server status variable
Aborted_clients
A
The number of connections that were aborted because the client died without closing the connection properly. SeeSection C.5.2.11 “Communication Errors and Aborted Connections”. mysql> show status like 'aborted%'; \+------------------+-------+ | Variable_name | Value | \+------------------+-------+ | Aborted_clients | 1 | | Aborted_connects | 11 | \+------------------+-------+ 2 rows in set (0.00 sec)
13
Q
Server status variable
Aborted_connects
A
The number of failed attempts to connect to the MySQL server. See Section C.5.2.11 “Communication Errors and Aborted Connections”.
For additional connection-related information check the Connection_errors_xxx status variables and thehost_cache table.
mysql> show status like 'aborted%'; \+------------------+-------+ | Variable_name | Value | \+------------------+-------+ | Aborted_clients | 1 | | Aborted_connects | 11 | \+------------------+-------+ 2 rows in set (0.00 sec)
14
Q
Server status variables
Binlog_cache_disk_use
A
The number of transactions that used the temporary binary log cache but that exceeded the value ofbinlog_cache_size and used a temporary file to store statements from the transaction.
The number of nontransactional statements that caused the binary log transaction cache to be written to disk is tracked separately in the Binlog_stmt_cache_disk_use status variable.
15
Q
Server status variables
Binlog_cache_use
A
The number of transactions that used the binary log cache.
16
Q
Server status variables
Bytes_received
A
The number of bytes received from all clients.
17
Q
Program Options
MySQL programs determine which options are given first by examining environment variables, then by
processing option files, and then by checking the command line. Because later options take precedence
over earlier ones, the processing order means that environment variables have the lowest precedence and
command-line options the highest.
For the server, one exception applies: The mysqld-auto.cnf option file in the data directory is
processed last, so it takes precedence even over command-line options.
You can take advantage of the way that MySQL programs process options by specifying default option
values for a program in an option file. That enables you to avoid typing them each time you run the
program while enabling you to override the defaults if necessary by using command-line options.
A
Options are processed in order, so if an option is specified multiple times, the last occurrence takes
precedence. The following command causes mysql to connect to the server running on localhost:
mysql -h example.com -h localhost
There is one exception: For mysqld, the first instance of the –user option is used as a security
precaution, to prevent a user specified in an option file from being overridden on the command line.
18
Q
Program Options
-? and –help
are the short and long forms of the option that instructs a MySQL program to display its help message.
A
An option argument begins with one dash or two dashes, depending on whether it is a short form or long
form of the option name. Many options have both short and long forms. For example, -? and –help
are the short and long forms of the option that instructs a MySQL program to display its help message.
19
Q
You can run the mysql client within the MySQL Server container you just started, and connect it to the MySQL Server. Use the docker exec -it command to start a mysql client inside the Docker container you have started, like the following:
docker exec -it mysql1 mysql -uroot -p
A
Docker is an increasingly popular software package that creates a container for application development.
There are two versions of Docker – Docker CE (Community Edition) and Docker EE (Enterprise Edition). If you have a small-scale project, or you’re just learning, you will want to use Docker CE.
Install: sudo apt install docker.io Start: sudo systemctl start docker Check: docker --version Grab an mysql docker image: sudo docker pull mysql/mysql-server:5.6.41-1.1.6 Run the image: sudo docker run --name=mysql1 --restart on-failure -d mysql/mysql-server:5.6.41-1.1.6
Once the server is ready, you can run the mysql client within the MySQL Server container you just started, and connect it to the MySQL Server. Use the docker exec -it command to start a mysql client inside the Docker container you have started:
docker exec -it mysql1 mysql -uroot -p
20
Q
MyISAM
MyISAM stands for Indexed Sequential Access Method. It was the default storage engine for MySQL until December 2009. With the release of MySQL 5.5, MyISAM was replaced with InnoDB.
MyISAM is based on an ISAM algorithm that displays information from large data sets fast. It has a small data footprint and is best suitable for data warehousing and web applications.
A
Storage Engine Type
There are two types of storage engines, depending on the rollback method:
Non-transactional – write options need to be rolled back manually.
Transactional – write options roll back automatically if they don’t complete.
Summary: MyISAM is a non-transactional, while InnoDB is a transactional type of storage engine.
21
Q
InnoDB
InnoDB has been the default storage engine for MySQL since the release of MySQL 5.5. It is best suited for large databases that hold relational data.
InnoDB focuses on high reliability and performance, making it great for content management systems. The InnoDB storage engine adheres closely to the ACID model so that data is not corrupted and results are not distorted by exceptional conditions such as software crashes and hardware malfunctions.
A
Storage Engine Type
There are two types of storage engines, depending on the rollback method:
Non-transactional – write options need to be rolled back manually.
Transactional – write options roll back automatically if they don’t complete.
Summary: MyISAM is a non-transactional, while InnoDB is a transactional type of storage engine.
22
Q
Binary log options
Binary logging captures changes between backups and is enabled by default.
It’s default setting is –log_bin=binlog
You can change this from the default if needed
A
Use the –log-bin option to specify a different target location for the binary log.
–log-bin[=base_name]
Command-Line Format –log-bin=file_name
Type File name
Specifies the base name to use for binary log files. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension. The –log-bin option value is the base name for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the base name.
NOTE: the below command just shows that binary logging is turned on mysql> show variables like 'log_bin'; \+---------------+-------+ | Variable_name | Value | \+---------------+-------+ | log_bin | ON | \+---------------+-------+ 1 row in set (0.01 sec)
Below on the os server you can see the default name ‘binlog’ was used when starting the mysql server:
cd /var/lib/mysql
[root@vmrac1 mysql]# ls -l
-rw-r—–. 1 mysql mysql 154923 Jul 26 16:35 binlog.000001
-rw-r—–. 1 mysql mysql 179 Jul 26 16:36 binlog.000002
-rw-r—–. 1 mysql mysql 59293450 Jul 28 05:58 binlog.000003
-rw-r—–. 1 mysql mysql 125114030 Jul 28 12:21 binlog.000004
-rw-r—–. 1 mysql mysql 179 Jul 28 17:18 binlog.000005
-rw-r—–. 1 mysql mysql 179 Jul 28 17:56 binlog.000006
-rw-r—–. 1 mysql mysql 179 Jul 28 18:01 binlog.000007
-rw-r—–. 1 mysql mysql 14227 Jul 31 11:24 binlog.000008
-rw-r—–. 1 mysql mysql 128 Jul 28 18:11 binlog.index
23
Q
Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database −
USE Databasename − This will be used to select a database in the MySQL workarea.
SHOW DATABASES − Lists out the databases that are accessible by the MySQL DBMS.
SHOW TABLES − Shows the tables in the database once a database has been selected with the use command.
SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
SHOW INDEX FROM tablename − Presents the details of all indexes on the table, including the PRIMARY KEY.
SHOW TABLE STATUS LIKE tablename\G − Reports details of the MySQL DBMS performance and statistics.
A
mysql> show table status like 'customers' \G
*************************** 1. row ***************************
Name: customers
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 122
Avg_row_length: 134
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2021-07-26 16:00:54
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show columns from limbs;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| thing | varchar(20) | YES | | NULL | |
| legs | int | YES | | NULL | |
| arms | int | YES | | NULL | |
+——-+————-+——+—–+———+——-+
3 rows in set (0.00 sec)
24
Q
Basically mySQL stores data in files in your hard disk. It stores the files in a specific directory that has the system variable “datadir”. Opening a mysql console and running the following command will tell you exactly where the folder is located.
SHOW VARIABLES LIKE ‘datadir’;
A
mysql> SHOW VARIABLES LIKE 'datadir'; \+---------------+-----------------+ | Variable_name | Value | \+---------------+-----------------+ | datadir | /var/lib/mysql/ | \+---------------+-----------------+ 1 row in set (0.01 sec)
The directory contains folders and some configuration files. Each folder represents a mysql database and contains files with data for that specific database
25
In MySQL 8.0 and above
Each database folder contains files that represent the tables in that database.
For tables using innodb , a file with the .idb extension.
For tables using myisam , a file the the .MYD extension
.sdi extention is serialized dictionary information (SDI) for some non-innodb table types like myisam of compact json
NOTE: previous versions may have had a .frm file(table format), these are not created in 8.0.
The .ibd file stores the table's data and contains the sdi info for innodb tables.
199704
-rw-r-----. 1 mysql mysql 131072 Jul 26 16:00 customers.ibd
-rw-r-----. 1 mysql mysql 147456 Jul 26 16:00 employees.ibd
-rw-r-----. 1 mysql mysql 114688 Jul 26 16:00 offices.ibd
-rw-r-----. 1 mysql mysql 327680 Jul 26 16:00 orderdetails.ibd
-rw-r-----. 1 mysql mysql 147456 Jul 26 16:00 orders.ibd
-rw-r-----. 1 mysql mysql 114688 Jul 26 16:00 payments.ibd
-rw-r-----. 1 mysql mysql 114688 Jul 26 16:00 productlines.ibd
-rw-r-----. 1 mysql mysql 163840 Jul 26 16:00 products.ibd
-rw-r-----. 1 mysql mysql 11023 Jul 26 16:42 cust_clone_371.sdi
-rw-r-----. 1 mysql mysql 114688 Jul 26 17:45 t1.ibd
-rw-r-----. 1 mysql mysql 88080384 Jul 28 07:40 cust_clonebak.ibd
-rw-r-----. 1 mysql mysql 11027 Jul 28 07:43 cust_clonebak2_376.sdi
-rw-r-----. 1 mysql mysql 61943616 Jul 28 07:43 cust_clonebak2.MYD
-rw-r-----. 1 mysql mysql 53055296 Jul 28 07:48 cust_clone.MYD
drwxr-x---. 2 mysql mysql 4096 Jul 28 07:48 .
drwxr-x--x. 8 mysql mysql 4096 Jul 31 08:49 ..
-rw-r-----. 1 mysql mysql 1024 Jul 31 11:19 cust_clone.MYI
-rw-r-----. 1 mysql mysql 1024 Jul 31 11:19 cust_clonebak2.MYI
26
Character set issues affect not only data storage, but also communication between client programs and the MySQL server. If you want the client program to communicate with the server using a character set different from the default, you'll need to indicate which one
For example, to use the utf8 Unicode character set, issue this statement after connecting to the server:
SET NAMES 'utf8';
27
If you want to find out about the structure of a table, the DESCRIBE statement is useful; it displays information about each of a table's columns:
describe tablename and show columns tablename do the same thing:
mysql> SHOW COLUMNS FROM customers
-> ;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set (0.01 sec)
mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
28
the InnoDB engine usually stores its data and indexes in a file called ibdata1. The ibdata1 file can be found in the /var/lib/mysql directory. The file is a system tablespace for the entire InnoDB infrastructure
When the InnoDB file per table option is disabled, ibdata1 normally houses many classes of information including:
The data of InnoDB tables
The indexes of InnoDB tables
Multiversioning Concurrency Control (MVCC) Data - MVCC ensures that if someone is reading from a database simultaneously as someone else is writing data to the database users will still see a consistent piece of data.
Rollback segments - in other words, the storage area consisting of the undo logs.
Undo tablespace - the undo tablespace consists of undo logs. Undo logs exist within undo log segments which are contained within rollback segments.
Table metadata.
29
you can use an OPTIMIZE TABLE statement to reorganize the physical storage of table data and associated index data to reduce storage space and optimize performance
mysql> optimize table cust_clone;
+--------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| classicmodels.cust_clone | optimize | status | OK |
+--------------------------+----------+----------+----------+
1 row in set (0.84 sec)
30
To convert your current tables to MyISAM do this for every table:
ALTER TABLE table_name ENGINE=MyISAM;
But keep in mind, that your foreign key constraints will not work anymore, as MyISAM doesn't support it. It will not complain, it will just ignore it.
31
mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
This makes a backup of all our InnoDB tables in all databases, without disturbing the reads and writes on these tables. The content of the .sql file is a bunch of SQL INSERT statements. (We assume this is the full backup of Sunday 1 PM, where load is low.)
mysqldump provides online logical backup
The --single-transaction allows for a consistent logical backup without doing a global lock or objects, this allows other users to still be able to perform dml during the backup
32
mysqldump --single-transaction --flush-logs
--delete-master-logs --master-data=2
--all-databases
> backup_sunday_1_PM.sql
--delete-master-logs
A good idea is deleting the binary logs which we won't need anymore, i.e. when we do the full backup, if you are NOT using replication.
NOTE: deleting the MySQL binary logs with mysqldump --delete-master-logs can be dangerous if your server is a replication master server, because those changes may not yet have made it to the slaves.
33
MySQL InnoDB Cluster delivers an integrated, native, HA solution for your databases. MySQL InnoDB Cluster consists of:
MySQL Servers with Group Replication to replicate data to all members of the cluster while providing fault tolerance, automated failover, and elasticity.
MySQL Router to ensure client requests are load balanced and routed to the correct servers in case of any database failures.
MySQL Shell to create and administer InnoDB Clusters using the built-in AdminAPI.
MySQL InnoDB Cluster tightly integrates MySQL Server with Group Replication, MySQL Router, and MySQL Shell, so you don't have to rely on external tools, scripts or other components. Plus, it leverages proven MySQL features including InnoDB, GTIDs, binary logs, multi-threaded replication, multi-source replication and Performance Schema.
A MySQL InnoDB Cluster can be set up in less than five minutes and managed using the scriptable AdminAPI in the MySQL Shell.
34
An estimated 70% of Oracle’s customers also use MySQL. MySQL
Enterprise Edition makes managing MySQL easier in these environments
by certifying and supporting the use of the MySQL Database in conjunction
with many Oracle products. These include:
• Oracle Linux
• Oracle VM
• Oracle Fusion Middleware
• Oracle Secure Backup
• Oracle Golden Gate
• Oracle Audit Vault and Database Firewall
• Oracle Enterprise Manager
• Oracle OpenStack for Oracle Linux
• Oracle Clusterware
Learn more about Oracle products certified with MySQL Enterprise Edition:
http://www.mysql.com/why-mysql/white-papers/spotlight-onmysql-enterprise-oracle-certifications/
35
Performance Schema events_% tables
Truncation is permitted to clear collected events, so TRUNCATE TABLE can be used on tables containing those kinds of information, such as tables named with a prefix of events_waits_.
mysql> TRUNCATE TABLE performance_schema.events_waits_current;
Query OK, 0 rows affected (0.00 sec)
36
Performance Schema summary tables
Summary tables can be truncated with TRUNCATE TABLE. Generally, the effect is to reset the summary columns to 0 or NULL, not to remove rows. This enables you to clear collected values and restart aggregation.
might be useful, for example, after you have made a runtime configuration change. Exceptions to this truncation behavior are noted in individual summary table sections.
37
Using Safe-Updates Mode (--safe-updates)
For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). Safe-updates mode is helpful for cases when you might have issued an UPDATE or DELETE statement but forgotten the WHERE clause indicating which rows to modify. Normally, such statements update or delete all rows in the table. With --safe-updates, you can modify rows only by specifying the key values that identify them, or a LIMIT clause, or both. This helps prevent accidents. Safe-updates mode also restricts SELECT statements that produce (or are estimated to produce) very large result sets.
The --safe-updates option causes mysql to execute the following statement when it connects to the MySQL server, to set the session values of the sql_safe_updates, sql_select_limit, and max_join_size system variables:
SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
38
mysqlcheck has a special feature compared to other client programs. The default behavior of checking tables (--check) can be changed by renaming the binary. If you want to have a tool that repairs tables by default, you should just make a copy of mysqlcheck named mysqlrepair, or make a symbolic link to mysqlcheck named mysqlrepair. If you invoke mysqlrepair, it repairs tables.
The names shown in the following table can be used to change mysqlcheck default behavior.
Command Meaning
mysqlrepair The default option is --repair
mysqlanalyze The default option is --analyze
mysqloptimize The default option is --optimize
[root@vmrac1 scripts]# cp /usr/bin/mysqlcheck /usr/bin/mysqlrepair
[root@vmrac1 scripts]# mysqlrepair cookbook;
cookbook.limbs
note : The storage engine for the table doesn't support repair
cookbook.test
note : The storage engine for the table doesn't support repair
repair only works on myisam tables
mysql> show create table limbs;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| limbs | CREATE TABLE `limbs` (
`thing` varchar(20) DEFAULT NULL,
`legs` int DEFAULT NULL,
`arms` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
39
How the Data Dictionary is Upgraded
Upgrade of data dictionary tables is an atomic operation, which means that all of the data dictionary tables are upgraded as necessary or the operation fails. If the upgrade operation fails, server startup fails with an error. In this case, the old server binaries can be used with the old data directory to start the server. When the new server binaries are used again to start the server, the data dictionary upgrade is reattempted.
example: During an in-place upgrade
When the MySQL 8.0 binary is started on the MySQL 5.7 image, it will check for these incompatibilities and terminate the upgrade process if one or more of these incompatibilities are found in the MySQL 5.7 image. Note that the MySQL 8.0 binary will terminate without having done any changes to the MySQL 5.7 image, so the user can restart MySQL 5.7 and fix the reported issues and then start the upgrade process again.
40
The Upgrade Checker utility that comes with the MySQL Shell 8.0 can be run against a MySQL 5.7 server to check for upgrade preparedness.
The Upgrade Checker will perform the most of the checks that in-place MySQL 8.0 upgrade does and if it does not find any issues, the upgrade to MySQL 8.0 will succeed.
41
New MySQL 8.0 features
The default/preferred authentication plugin has been changed to ‘caching_sha2_password’ since it provides more secure password encryption ‘mysql_native_password’ plugin and better performance than the ‘sha256_password’. This may cause some of the applications to throw errors related to ‘caching_sha2_password’ plugin after upgrade to MySQL 8.0. This may be due to the fact the clients/connectors are yet to support ‘caching_sha2_password’ plugin.
If you have an error due to incompatible applicatons, then you may need to revert back to the mysql_native_password plugin
If you are upgrading your server to MySQL 8.0 and observe that your application is experiencing error related caching_sha2_password plugin, it is likely because your clients/connectors does not (yet) support caching_sha2_password plugin. To resolve this issue, you may consider using mysql_native_password as default authentication for MySQL 8.0 server. Add following entry in MySQL configuration file.
[mysqld]
default-authentication-plugin=mysql_native_password
This way, once server is restarted, your existing applications should be able to connect to the server without any issues.
42
MySQL 8.0 supports a new authentication plugin – caching_sha2_password. This plugin is also the new default value for –default-authentication-plugin system variable that governs two things:
Authentication plugin used by a new user account if a plugin is not specified explicitly through CREATE USER statement
Initial authentication data payload generated by server in case of a new connection.
When a client tries to establish a new connection to MySQL server, server passes information about its default authentication plugin to client.
Clients/Connectors like libmysqlclient do not rely on server’s default authentication plugin for sending initial authentication data. They have their own way of determining default authentication plugin. This is done by checking MYSQL_DEFAULT_AUTH value (It can be set through mysql_options() and choosing the authentication plugin accordingly. For example, in case of 5.7 libmysqlclient, default value for MYSQL_DEFAULT_AUTH is mysql_native_password. Hence, when a 5.7 libmysqlclient is used to connect to MySQL 8.0, it will always use mysql_native_password to send first authentication data to server. This is regardless of server’s default authentication plugin value. So, if your applications uses such clients/connectors, you should not face any difficultly when you upgrade from MySQL 5.7 to MySQL 8.0 for existing user accounts.
43
Removal of File-based Metadata Storage
In previous MySQL releases, dictionary data was partially stored in metadata files. Issues with file-based metadata storage included expensive file scans, susceptibility to file system-related bugs, complex code for handling of replication and crash recovery failure states, and a lack of extensibility that made it difficult to add metadata for new features and relational objects.
The metadata files listed below are removed from MySQL. Unless otherwise noted, data previously stored in metadata files is now stored in data dictionary tables.
.frm files: Table metadata files. With the removal of .frm files:
The 64KB table definition size limit imposed by the .frm file structure is removed.
The INFORMATION_SCHEMA.TABLES version column reports a hardcoded value of 10, which is the last .frm file version used in MySQL 5.7.
.par files: Partition definition files. InnoDB stopped using partition definition files in MySQL 5.7 with the introduction of native partitioning support for InnoDB tables.
.TRN files: Trigger namespace files.
.TRG files: Trigger parameter files.
.isl files: InnoDB Symbolic Link files containing the location of file-per-table tablespace files created outside of the data directory.
db.opt files: Database configuration files. These files, one per database directory, contained database default character set attributes.
ddl_log.log file: The file contained records of metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE.
Mysql 8.0 still uses the following file types
```
.ibd
.sdi - myisam, performance schema objects,
.MYD - myisam
.csv - general log file,ect
.CSM
```
ect. ect.
- rw-r-----. 1 mysql mysql 114688 Jul 26 16:00 offices.ibd
- rw-r-----. 1 mysql mysql 11027 Jul 28 07:43 cust_clonebak2_376.sdi
- rw-r-----. 1 mysql mysql 61943616 Jul 28 07:43 cust_clonebak2.MYD
performance_time_99.sdi
general_log.CSV
slow_log.CSM
44
Transactional Storage of Dictionary Data
Data dictionary tables are created in a single InnoDB tablespace named mysql.ibd, which resides in the MySQL data directory. The mysql.ibd tablespace file must reside in the MySQL data directory and its name cannot be modified or used by another tablespace.
Dictionary data is protected by the same commit, rollback, and crash-recovery capabilities that protect user data that is stored in InnoDB tables.
[root@vmrac1 mysql]# pwd
/var/lib/mysql
[root@vmrac1 mysql]# ls -l mysql*
-rw-r-----. 1 mysql mysql 25165824 Jul 31 12:19 mysql.ibd
45
Data Dictionary Tables
The data dictionary tables contain metadata about data objects. Tables in this directory are 'invisible' and cannot be read from using - select , show,ect.
Instead you will use the information_schema views to see this information.
Data dictionary tables are protected and may only be accessed in debug builds of MySQL. However, MySQL supports access to data stored in data dictionary tables through INFORMATION_SCHEMA tables and SHOW statements.
46
Data Dictionary Tables
The data dictionary tables contain metadata about data objects. Tables in this directory are 'invisible' and cannot be read from using - select
Instead you will use the information_schema views to see this information.
Data dictionary tables are protected and may only be accessed in debug builds of MySQL. However, MySQL supports access to data stored in data dictionary tables through INFORMATION_SCHEMA tables and SHOW statements.
47
INFORMATION_SCHEMA tables are implemented as views on data dictionary tables.
INFORMATION_SCHEMA tables in MySQL 8.0 are closely tied to the data dictionary.
SELECT TABLE_SCHEMA AS table_schema, TABLE_NAME AS table_name
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
```
+--------------------+------------+
| table_schema | table_name |
+--------------------+------------+
| performance_schema | users |
+--------------------+------------+
1 row in set (0.00 sec)
```
48
Changes in MySQL 8.0
mysqldump and mysqlpump no longer dump the INFORMATION_SCHEMA database, even if explicitly named on the command line.
The data directory affects how mysqldump and mysqlpump dump information from the mysql system database:
Previously, it was possible to dump all tables in the mysql system database. As of MySQL 8.0, mysqldump and mysqlpump dump only non-data dictionary tables in that database.
49
Changes in MySQL 8.0
Previously, the --routines and --events options were not required to include stored routines and events when using the --all-databases option: The dump included the mysql system database, and therefore also the proc and event tables containing stored routine and event definitions.
As of MySQL 8.0, the event and proc tables are not used. Definitions for the corresponding objects are stored in data dictionary tables, but those tables are not dumped. To include stored routines and events in a dump made using --all-databases, use the --routines and --events options explicitly.
Previously, the --routines option required the SELECT privilege for the proc table.
As of MySQL 8.0, that table (proc) is not used; --routines requires the global SELECT privilege instead
50
There are four levels of grants in MySQL
Global (stored in mysql.user)
Database (stored in mysql.db)
Table (stored in mysql.tables_priv)
Column (stored in mysql.columns_priv)
The globally granted privileges apply to all databases, tables, and columns, even though not granted at any of those lower levels.
GRANT SELECT, INSERT, UPDATE ON *.* TO u1;
If you are using table, column, or routine privileges for even one user, the server examines table, column, and routine privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.
51
Persisted system variables are one of the useful features introduced in MySQL 8. The new functionality helps DBAs update the variables dynamically and register them without touching the configuration files from the server-side.
SET PERSIST is the command that can be used for updating the system variables at runtime and make them persistent across restarts. When we use the PERSIST keyword, the variable changes are updated to the mysqld-auto.cnf option file in the data directory. The mysqld-auto.cnf is a JSON format file created only upon the first execution of the PERSIST or PERSIST_ONLY statement.
Management of mysqld-auto.cnf should be left to the server and not performed manually.
the file is generally located:
/var/lib/mysql/mysqld-auto.cnf
52
Regarding Replication
When on the slave you run the following, and see the seconds_behind_master is trending upward.
show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 46
an upward slope for Seconds_Behind_Master indicates that the slave is performing slower than the master.
If SBM is constantly increasing, which means that the slave’s execution speed is less compared to that of the master.
This is actually a case where we are running 20 threads (as an example) doing continuous writes on the master and a single-threaded slave is not able to keep pace with it.
53
Regarding Replication
The source UUID (universally unique identifier) is the UUID of the originating server for each transaction. Each server’s UUID is stored in the auto.cnf file in the data directory. If that file does not exist, MySQL creates the file and generates a new UUID, placing it in the new file. Query a server’s UUID with the server_uuid variable:
Host 1
mysql> SELECT @@server_uuid\G
*************************** 1. row ***************************
@@server_uuid: 8e4dc9c8-02a2-11ec-b464-000c29aaa239
1 row in set (0.00 sec)
root@vmrac2 ~]# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=8e4dc9c8-02a2-11ec-b464-000c29aaa239
Host 2
mysql> SELECT @@server_uuid\G
*************************** 1. row ***************************
@@server_uuid: 925ea5de-02a2-11ec-acd1-000c29e88476
1 row in set (0.00 sec)
54
Client Programs
```
mysql — The MySQL Command-Line Client
mysqladmin — A MySQL Server Administration Program
mysqlcheck — A Table Maintenance Program
mysqldump — A Database Backup Program
mysqlimport — A Data Import Program
mysqlpump — A Database Backup Program
mysqlshow — Display Database, Table, and Column Information
mysqlslap — A Load Emulation Client
```
[root@vmrac2 ~]# mysqladmin -uroot -p shutdown
Enter password:
[root@vmrac2 ~]#
[root@vmrac2 ~]# mysqlcheck --analyze test
test. MyISAMToInnoDBDemo OK
test. authors OK
test. book_authors OK
test. books OK
test. commands OK
test. contact_groups OK
test. contact_notification_commands OK
test. contactgroup_contact_members OK
test. contactgroup_contactgroup_members OK
test. contacts OK
test. employee OK
test. host_groups OK
test. time_periods OK
test. timeperiod_values OK
test. users OK
55
The Performance Schema monitors server events.
An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements.
A mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to some common resource. The resource is said to be "protected" by the mutex. The word "Mutex" is an informal abbreviation for "mutex variable", which is itself is short for "mutual exclusion". In MySQL, it's the low-level object that InnoDB uses to represent and enforce exclusive-access locks to internal in-memory data structures.
56
Performance Schema
The setup_instruments and setup_consumers tables list the instruments for which events can be collected and the types of consumers for which event information actually is collected, respectively.
mysql> select * from setup_actors
-> ;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | YES | YES |
+------+------+------+---------+---------+
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+
15 rows in set (0.00 sec)
57
Performance Schema Timers
The Performance Schema assigns timers as follows:
The wait timer uses CYCLE.
The idle, stage, statement, and transaction timers use NANOSECOND on platforms where the NANOSECOND timer is available, MICROSECOND otherwise.
At server startup, the Performance Schema verifies that assumptions made at build time about timer assignments are correct, and displays a warning if a timer is not available.
To time wait events, the most important criterion is to reduce overhead, at the possible expense of the timer accuracy, so using the CYCLE timer is the best.
mysql> SELECT * FROM performance_schema.performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE | 1982711442 | 1 | 19 |
| NANOSECOND | 1000000000 | 1 | 78 |
| MICROSECOND | 1000000 | 1 | 62 |
| MILLISECOND | 969 | 1 | 64 |
+-------------+-----------------+------------------+----------------+
To time statements, the most important criterion is to have an accurate measure, which is not affected by changes in processor frequency, so using a timer which is not based on cycles is the best. The default timer for statements is NANOSECOND.
58
MySQL Enterprise Monitor
MySQL Enterprise Monitor has the following components:
MySQL Enterprise Monitor Agent monitors the MySQL instances and hosts, and collects data according to a defined schedule. The collection data is sent to MySQL Enterprise Service Manager for analysis and presentation. MySQL Enterprise Monitor Agent is typically installed on the same host as the monitored server.
MySQL Enterprise Service Manager analyzes, stores and presents the data collected by the agent. MySQL Enterprise Service Manager also contains a built-in agent which is used to monitor the repository.
It is also possible to perform what is called an Agent-less installation, where the Agent is not installed on the host machines, and all monitoring is done by MySQL Enterprise Service Manager's built-in Agent.
MySQL Enterprise Monitor is available as part of the MySQL Enterprise subscription.
MySQL Enterprise Monitor is a companion product to MySQL Server that enables monitoring of MySQL instances and their hosts, notification of potential issues and problems, and advice on how to correct issues. MySQL Enterprise Monitor can monitor all types of installation, from a single MySQL instance to large farms of database servers. MySQL Enterprise Monitor is a web-based application, enabling you to monitor MySQL instances on your network or on a cloud service.
59
mysql_secure_installation
MySQL server has an out-of-the-box security configuration script which helps us improve the security of our MySQL installation by doing the following.
Setting a password for root accounts
Disable remote access
Remove Anonymous user accounts
Remove the test database and revoke access to users with privileges that permit anyone to access databases with names that start with test_
After the installation of MySQL Server finishes, we can run the following command
secuser@secureserver:~$ mysql_secure_installation
--> Securing the MySQL server deployment.
If we have already specified a password for root during the MySQL server setup, we will be asked to enter it.
Enter password for user root:
y default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
60
Activating Roles
Roles granted to a user account can be active or inactive within account sessions. If a granted role is active within a session, its privileges apply; otherwise, they do not. To determine which roles are active within the current session, use the CURRENT_ROLE() function.
mysql> select * from world.city;
ERROR 1142 (42000): SELECT command denied to user 'john'@'localhost' for table 'city'
mysql> SET DEFAULT ROLE ALL TO `john`@`localhost` ;
Query OK, 0 rows affected (0.00 sec)
```
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
```
as root
mysql> SET DEFAULT ROLE ALL TO `john`@`localhost` ;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@vmrac2 ~]# mysql -ujohn -p
Enter password:
```
mysql> select * from world.city;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
```
```
mysql> SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE() |
+------------------+
| `r_world_rd`@`%` |
+------------------+
1 row in set (0.00 sec)
```
61
User account jon@hostname on your MySQL instance has been compromised.
Which two commands stop any new connections using the compromised account? (Choose two.)
a. ALTER USER jon@hostname MAX_USER_CONNECTIONS 0;
B. ALTER USER jon@hostname PASSWORD DISABLED;
c. ALTER USER jon@hostname ACCOUNT LOCK;
d. ALTER USER jon@hostname IDENTIFIED WITH mysql_no_login;
ans. c and d
```
mysql> ALTER USER 'jon'@'localhost' PASSWORD DISABLED;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISABLED' at line 1
```
mysql> ALTER USER 'jon'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)
mysql_no_login will work but must have plugin install first!!!!
```
mysql> ALTER USER 'jon'@'localhost' IDENTIFIED WITH mysql_no_login;
ERROR 1524 (HY000): Plugin 'mysql_no_login' is not loaded
```
62
connection methods MySQL clients specify with
the --protocol option when connecting to a MySQL server
permissible --protocol option values The values are not case-sensitive.
TCP =TCP/IP transport to local or remote server
SOCKET =Unix socket-file transport to local server
PIPE Named-pipe transport to local server
MEMORY Shared-memory transport to local server
Support OS:
TCP - all os
SOCKET - unix only
MEMORY - Windows
PIPE - Windows
63
Restrictions:
Mysqlpump doesn’t dump system databases like INFORMATION_SCHEMA, performance_schema, ndbinfo, or sys schema by default like mysqldump.
Mysqlpump does dump the mysql schema
64
mysql -h remote.example.org -u root -p --protocol=TCP --ssl-mode=
Which --ssl-mode values will ensure that an X.509-compliant certificate will be used to establish the SSL/TLS connection to MySQL?
REQUIRED
65
True or false
the doublewrite buffer is contained in the InnoDB system tablespace (ibdata1) by default?
FALSE as of 8.0
As of MySQL 8.0.20, the doublewrite buffer storage area resides in doublewrite files, which provides flexibility with respect to the storage location of doublewrite pages. In previous releases, the doublwrite buffer storage area resided in the system tablespace. The innodb_doublewrite_dir variable defines the directory where InnoDB creates doublewrite files at startup. If no directory is specified, doublewrite files are created in the innodb_data_home_dir directory, which defaults to the data directory if unspecified.
To have doublewrite files created in a location other than the innodb_data_home_dir directory, configure innodb_doublewrite_dir variable. For example:
innodb_doublewrite_dir=/path/to/doublewrite_directory
66
What is the difference between Unix sockets and TCP/IP sockets?
1. UNIX domain sockets are subject to file system permissions, while TCP sockets can be controlled only on the packet filter level.
unix sockets: netstat -a -p --unix
[root@vmrac2 mysql]# lsof /var/lib/mysql/mysql.sock
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 30857 mysql 26u unix 0xffff8ad970e59100 0t0 34089858 /var/lib/mysql/mysql.sock
TCP/IP = A TCP/IP socket is used for communication across TCP/IP networks. A connected TCP socket is identified by the combination of local IP, local port, remote IP and remote port. A listening TCP socket is identified by local port and possibly local IP.
Unix socket = a process accepting a connection on a Unix socket can determine the user ID of the process that connects. This can avoid the need for an authentication step.
67
host=localhost = mysql client will try to login to mysql server using unix named pipe which requires a .sock file.
host=127.0.0.1 = This will make mysql client use TCP to connect to the server.
If you do not have a mysql sockfile and you need to shutdown the instance:
mysqladmin -h127.0.0.1 --protocol=tcp -uroot -p shutdown
A socket is a special pseudo-file used for data transmission by reading and writing, not data storage.
The socket file is created when the service is started and removed when the service is terminated. The location of the file is defined in /etc/my.cnf
68
While creating a user using the CREATE USER command, you can specify
Authentication, which should be used while connecting to MySQL.
Resource Limit
Password Management properties: Password expiration, Password reuse settings.
Account locking: Newly created accounts would be either locked or unlocked.
The accounts are created in MySQL system table named ‘mysql.user’
simplest form, the syntax for CREATE USER command is as below:
CREATE USER [IF NOT EXISTS] '{username}'@'{hostname}' IDENTIFIED BY '{passwordString}';
Notice the optional IF NOT EXISTS. This ensures that if the user is already existing, the SQL query result will just throw a warning and no error.
mysql> CREATE USER 'oracle'@'localhost' IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'ineedpassword' identified by 'Ineed.1password';
Query OK, 0 rows affected (0.01 sec)
69
CREATE USER
if the ‘hostname’ field is left blank then the value for the host is assumed to be ‘%’ which would allow any host to connect with the specified username.
When a ROLE is created it is created with '%' as well
mysql> CREATE USER 'userx'@'localhost' IDENTIFIED BY 'Ineed.1password';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER 'usery' IDENTIFIED BY 'Ineed.1password';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,plugin,account_locked from mysql.user;
+-------------+------------------+-----------------------+----------------+
| host | user | plugin | account_locked |
+-------------+------------------+-----------------------+----------------+ |
| % | myDefault | caching_sha2_password | Y |
| % | usery | caching_sha2_password | N
| localhost | userx | caching_sha2_password | N |
+-------------+------------------+-----------------------+----------------+
21 rows in set (0.00 sec)
70
4 users are pre-created during MySQL installation itself
mysql.session
mysql.sys
root
mysql.infoschema
the three "mysql' database users are created with the accounts LOCKED
71
CREATE USER with auth plugin
Some plugins that are supported are as below:
MySQL native password hashing: Native password hashing is nothing but using SHA1 to store password values in the mysql.users table. This mechanism is considered being less secure than SHA1 password hashing.
SHA2 256 password hashing: This is the default authentication plugin used by MySQL. i.e. even if no authentication plugin is specified with CREATE_USER command, by default this plugin would be applied.
External authentication using LDAP: LDAP is generally used for linking MySQL authentication with the organization’s active directory. For example, authenticating using Google SSO, OAuth, or Microsoft Outlook LDAP. This requires LDAP plugin installation on MySQL side as well.
CREATE USER IF NOT EXISTS 'user-default'@'localhost' IDENTIFIED BY 'P@ssw0rd';
above would be created using the default auth plugin - caching_sha2_password
create a user with an authentication plugin. ‘MySQL native password’ and see what’s the value of the plugin that gets stored.
CREATE USER IF NOT EXISTS 'user-sha1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
mysql> SELECT host,user,plugin,authentication_string from mysql.user where user like 'user_%';
+-----------+--------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+--------------+-----------------------+------------------------------------------------------------------------+
| % | usery | caching_sha2_password | $A$005$')^.%f{'kROGtZ\ctU96NiL6tkKxsuXDRDOC7hHWd754BBC7.Jr6fEkx60N2 |
| localhost | user-default | caching_sha2_password | $A$005$M GUr+?9u\\iuEEidlmuRJ40Ge9Hlf7dfZs7OBcTh7PEtQQT/BEl21ZV. |
| localhost | user-sha1 | mysql_native_password | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
2Vl7=alhost | userx | caching_sha2_password | $A$005$eqfc=C
2^5m ~OvKFAgPKFJMKcEq6ahVjGYlhUN3sOsyVFNf/VpIBmN3 |
+-----------+--------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
72
CREATE USER
you can assign a default role when creating a new user
the syntax needed = DEFAULT ROLE
mysql> CREATE ROLE 'developer';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all ON world.* TO 'developer';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER IF NOT EXISTS 'user-with-role'@'localhost' identified by 'P@ssw0rd' DEFAULT ROLE developer;
Query OK, 0 rows affected (0.01 sec)
73
CREATE USER With SSL/TLS
Creating a user with SSL/TLS options would require both client and server to have SSL certificates installed. By default, SSL is not configured for MySQL.
In order to create a user with SSL enabled, you can use the REQUIRE SSL option while creating the user.
mysql> CREATE USER 'user-with-ssl'@'localhost' IDENTIFIED BY 'P@ssw0rd' REQUIRE SSL;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT host,user,plugin,ssl_type from mysql.user where user like 'user_%';
+-----------+----------------+-----------------------+----------+
| host | user | plugin | ssl_type |
+-----------+----------------+-----------------------+----------+
| % | usery | caching_sha2_password | |
| localhost | user-default | caching_sha2_password | |
| localhost | user-sha1 | mysql_native_password | |
| localhost | user-with-role | caching_sha2_password | |
| localhost | user-with-ssl | caching_sha2_password | ANY |
| localhost | user-with-x509 | caching_sha2_password | X509 |
| localhost | userx | caching_sha2_password | |
+-----------+----------------+-----------------------+----------+
7 rows in set (0.00 sec)
Notice the require ssl = ssl_type of ANY
Notice the request x509 = ssl_type of X509
74
CREATE USER
you can create multiple users at a time, with some settings spefic and some they both will be set as
CREATE USER
'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
BY 'new_Password1',
'jeanne'@'localhost' IDENTIFIED WITH caching_sha2_password
BY 'new_Password2'
REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
PASSWORD HISTORY 5
ACCOUNT LOCK;
mysql> SELECT host,user,plugin,ssl_type,max_questions from mysql.user where user like 'je%';
+-----------+---------+-----------------------+----------+---------------+
| host | user | plugin | ssl_type | max_questions |
+-----------+---------+-----------------------+----------+---------------+
| localhost | jeanne | caching_sha2_password | X509 | 60 |
| localhost | jeffrey | mysql_native_password | X509 | 60 |
+-----------+---------+-----------------------+----------+---------------+
2 rows in set (0.00 sec)
addtionally, both user accounts are LOCKED
75
CREATE USER permits these auth_option syntaxes:
IDENTIFIED BY 'auth_string'
IDENTIFIED BY RANDOM PASSWORD
IDENTIFIED WITH auth_plugin
IDENTIFIED WITH auth_plugin BY 'auth_string'
IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
IDENTIFIED WITH auth_plugin AS 'auth_string'
if you need to store the HASH then you use 'as' , instead of 'by'
IDENTIFIED WITH auth_plugin AS 'auth_string'
Sets the account authentication plugin to auth_plugin and stores the 'auth_string' value as is in the mysql.user account row. If the plugin requires a hashed string, the string is assumed to be already hashed in the format the plugin requires.
76
What is the mysqld-auto.cnf file?
MySQL provides variants of SET syntax that persist system variable settings to a file named mysqld-auto.cnf file in the data directory.
This allows you to change a system variable without shutting down the MySQL instance, and still have it persist after a MySQL restart at a future time.
```
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
```
mysql> SET PERSIST max_connections = 200;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@vmrac2 mysql]# locate mysqld-auto.cnf
[root@vmrac2 mysql]# cd /var/lib/mysql
[root@vmrac2 mysql]# ls -l *cnf
-rw-r-----. 1 mysql mysql 56 Sep 19 11:27 auto.cnf
-rw-r-----. 1 mysql mysql 171 Sep 25 16:15 mysqld-auto.cnf
[root@vmrac2 mysql]# cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "200" , "Metadata" : { "Timestamp" : 1632604505322832 , "User" : "root" , "Host" : "localhost" } } } }[root@vmrac2 mysql]#
77
Mysql Delay_key_write
Another performance option in MySQL is the DELAY_KEY_WRITE option. According to the MySQL documentation the option makes index updates faster because they are not flushed to disk until the table is closed.
this parameter could be set to ON to
| help reduce disk I/O overheads
78
Purging binary logs
You can purge binary logs manually or automatically, either option requires commands to have MySQL instructed to purge the logs, never physically remove the logs yourself!
The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are:
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';
These will clear all binary logs before the binlog or timestamp you just specified.
For example, if you run
PURGE BINARY LOGS TO 'mysql-bin.000223';
this will erase all binary logs before mysql-bin.000223.
If you run
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
this will erase all binary logs before midnight 3 days ago.
If you want to have binlog rotated away automatically and keep 3 days worth, simply set this:
mysql> SET GLOBAL expire_logs_days = 3;
79
What is the differnce between these two commands
Linux
bin/mysqld --initialize --user=mysql
bin/mysqld --initialize-insecure --user=mysql
Windows:
bin\mysqld --initialize --console
bin\mysqld --initialize-insecure --console
the following command initializes the data directories and set the folder/file permissions to use mysql and also sets a root password.
Linux
bin/mysqld --initialize --user=mysql
Windows
bin\mysqld --initialize --console
the following command initializes the data directories and set the folder/file permissions to use mysql and does NOT set a root password. This is not recommended.
Linux
bin/mysqld --initialize-insecure --user=mysql
Windows
bin\mysqld --initialize-insecure --console
80
Password policy
the below error occurs because of the password policy, not the specific setting that make up the policy.
The below password have 1 number, a upper case, special character,ect.
If the policy was LOW this would succeed
because the policy is Medium or higher the setting for mixed case, number count, ect are looked at
```
mysql> CREATE USER 'newsier'@'localhost' IDENTIFIED BY 'Hoverl@%';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
```
Even if the password policy is set to LOW , you could still get a failure if the length is not 8 or greater. Low, Medium and higher all require the length to be 8 at a minimum
```
mysql> create user 'kumar'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
```
mysql> CREATE USER 'newsier'@'localhost' IDENTIFIED BY 'Hoverl@%';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> CREATE USER 'newsier'@'localhost' IDENTIFIED BY 'H0ver1@%';
Query OK, 0 rows affected (0.02 sec)
mysql> show global variables like 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 2 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
81
Authentication with no password - LDAP users
CREATE USER ldapuser1@localhost IDENTIFIED WITH authentication_ldap_simple AS uid=ldapuser1,ou=People,dc=12c,dc=com;
mysql --user=ldapuser1 --password --enable-cleartext-plugin
CREATE USER ldapuser1@localhost IDENTIFIED WITH authentication_ldap_simple AS uid=ldapuser1,ou=People,dc=12c,dc=com;
mysql --user=ldapuser1 --password --enable-cleartext-plugin
Note : you have to use the option --enable-cleartext-plugin
```
[root@vmrac1 ~]# mysql --user=ldapuser1 --password
Enter password:
ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled
[root@vmrac1 ~]#
```
82
The mysqlimport client provides a command-line interface to the LOAD DATA SQL statement.
if you use mysqldump to take a backup of a database in mysql , then to load that backup you would use mysql.
but you would NOT use mysqlimport, as this tool looks for delimited text not the full sql statements
83
Why is this error occuring when logged in as root?
```
mysql> SELECT user, host, plugin FROM mysql.users;
ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
```
Because you have a TYPO - it is user not users
```
mysql> SELECT user, host, plugin FROM mysql.users;
ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
```
mysql> SELECT user, host, plugin FROM mysql.user;
+---------------+------------------+-----------------------+
| user | host | plugin |
+---------------+------------------+-----------------------+
| root | localhost | mysql_native_password |
| root | racnode1.12c.com | mysql_native_password |
| root | 127.0.0.1 | mysql_native_password |
| root | ::1 | mysql_native_password |
| user1 | localhost | mysql_native_password |
| user1 | 10.10.% | mysql_native_password |
| user1 | 192.% | mysql_native_password |
| user2 | localhost | mysql_native_password |
| user2 | 10.10.% | mysql_native_password |
| user3 | 192.% | mysql_native_password |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| user-with-ssl | localhost | mysql_native_password |
| test | localhost | mysql_native_password |
+---------------+------------------+-----------------------+
14 rows in set (0.00 sec)
mysql>
84
Audit logging
The MySQL server calls the audit log plugin to write an audit record to its log file whenever an auditable event occurs. Typically the first audit record written after plugin startup contains the server description and startup options. Elements following that one represent events such as client connect and disconnect events, executed SQL statements, and so forth.
Only top-level statements are logged, not statements within stored programs such as triggers or stored procedures. Contents of files referenced by statements such as LOAD DATA are not logged.
85
auto_increment locking
There are three possible settings for the innodb_autoinc_lock_mode variable. The settings are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. As of MySQL 8.0, interleaved lock mode (innodb_autoinc_lock_mode=2) is the default setting. Prior to MySQL 8.0, consecutive lock mode is the default (innodb_autoinc_lock_mode=1).
interleaved - is the only setting that does NOT do a table lock on INSERT-like statements.
innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.
special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns.
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time.
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.
86
Option files
Many option files are plain text files, created using any text editor. The exceptions are:
The .mylogin.cnf file that contains login path options. This is an encrypted file created by the mysql_config_editor utility. See Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”. A “login path” is an option group that permits only certain options: host, user, password, port and socket. Client programs specify which login path to read from .mylogin.cnf using the --login-path option.
To specify an alternative login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable. This variable is used by the mysql-test-run.pl testing utility, but also is recognized by mysql_config_editor and by MySQL clients such as mysql, mysqladmin, and so forth.
The mysqld-auto.cnf file in the data directory. This JSON-format file contains persisted system variable settings. It is created by the server upon execution of SET PERSIST or SET PERSIST_ONLY statements. See Section 5.1.9.3, “Persisted System Variables”. Management of mysqld-auto.cnf should be left to the server and not performed manually.
.mylogin.cnf
Each option group in .mylogin.cnf is called a “login path,” which is a group that permits only certain options: host, user, password, port and socket. Think of a login path option group as a set of options that specify which MySQL server to connect to and which account to authenticate as. Here is an unobfuscated example:
```
[client]
user = mydefaultname
password = mydefaultpass
host = 127.0.0.1
[mypath]
user = myothername
password = myotherpass
host = localhost
```
87
You execute the following statement in a Microsoft Windows environment. There are no conflicts in the path name definitions.
C: \> mysqld install Mysql8.0 defaults file = C : \my opts.cnf
What is the expected outcome?
mysql is installed on windows as a service named Mysql8.0
and uses the option file C : \my opts.cnf
88
Windows installation
during the Mysql installation on a Windows server you must choose
the setup type
```
developer default
server only
client only
full
customer
```
MySQL Installer Configuration Files
All MySQL Installer files are located within the C:\Program Files (x86) and C:\ProgramData folders.
89
Windows installation
during the Mysql installation on a Windows server you must choose
the setup type
```
developer default
server only
client only
full
customer
```
MySQL Installer Configuration Files
All MySQL Installer files are located within the C:\Program Files (x86) and C:\ProgramData folders.
90
Performance schema monitors every “event” the server does that takes time and has been instrumented so that timing information can be collected. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database.
Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata. Having its own engine allows us to access information about server execution while having minimal impact on server performance. Tables created in performance_schema are persistent in memory. Information from those tables will be wiped out if the database is rebooted.
91
Performance schema monitors every “event” the server does that takes time and has been instrumented so that timing information can be collected. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database.
Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata. Having its own engine allows us to access information about server execution while having minimal impact on server performance. Tables created in performance_schema are persistent in memory. Information from those tables will be wiped out if the database is rebooted.
92
Performance Schema objects
```
mysql> select count(*) from performance_schema.events_waits_current;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
```
```
mysql> delete from performance_schema.events_waits_current;
ERROR 1142 (42000): DELETE command denied to user 'root'@'localhost' for table 'events_waits_current'
mysql>
```
Most of the tables in performance_schema database is read only tables. There are very few tables which we can modify to adjust instrumentation. Setup tables are among those few tables which can be modified to define what kind of performance data we want to collect.
therefore you cannot do a DELETE on a current events type table
93
The CHECK TABLE statement performs an integrity check on table structure and contents. It works for MyISAM and InnoDB tables. For MyISAM tables, it also updates the index statistics. If the table is a view, CHECK TABLE verifies the view definition. If the output from CHECK TABLE indicates that a table has problems, the table should be repaired.
Combine Check, Optimize, and Repair Tables
Instead of checking and repairing separately. You can combine check, optimize and repair functionality together using “–auto-repair” as shown below.
The following checks, optimizes and repairs all the corrupted table in thegeekstuff database.
mysqlcheck -u root -p --auto-repair -c -o thegeekstuff
94
If you want to know what the command is doing while it is checking, add the –debug-info as shown below. This is helpful while you are checking a huge table.
[root@vmrac2 ~]# mysqlcheck --debug-info -u root -p --auto-repair -c -o
mysqlcheck: [ERROR] mysqlcheck: Option 'debug-info' was used, but is disabled.
you have to have a version compiled with debugging enabled.
95
MySQL physical backups
physical backups can be used to recover from data coruption
Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.
Physical backup methods are faster than logical because they involve only file copying without conversion.
Output is more compact than for logical backup.
Physical backup tools include the mysqlbackup of MySQL Enterprise Backup for InnoDB or any other tables, or file system-level commands (such as cp, scp, tar, rsync) for MyISAM tables.
For restore:
MySQL Enterprise Backup restores InnoDB and other tables that it backed up.
ndb_restore restores NDB tables.
Files copied at the file system level can be copied back to their original locations with file system commands.
96
MySQL physical backups
physical backups can be used to recover from data coruption
Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.
Physical backup methods are faster than logical because they involve only file copying without conversion.
Output is more compact than for logical backup.
Physical backup tools include the mysqlbackup of MySQL Enterprise Backup for InnoDB or any other tables, or file system-level commands (such as cp, scp, tar, rsync) for MyISAM tables.
For restore:
MySQL Enterprise Backup restores InnoDB and other tables that it backed up.
ndb_restore restores NDB tables.
Files copied at the file system level can be copied back to their original locations with file system commands.
97
The mysqlbackup commands to perform a restore operation are copy-back-and-apply-log and copy-back
Normally, the restoration process requires the database server to be already shut down (or, at least not operating on the directory you are restoring the data to), except for a partial restore. The process copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required post-processing on them.
mysqlbackup --defaults-file= -uroot --backup-image= \
| --backup-dir= --datadir= copy-back-and-apply-log
98
mysqlbackup (enterprise edition)
the backup_history table, where MySQL Enterprise Backup records details of each backup. The table allows you to perform future incremental backups using the --incremental-base=history:{last_backup | last_full_backup} option.
`
99
When you examine a new MySQL installation with default configuration, you find a file called ibdata1 in the database directory. Which two statements are true
it contains the system tablespace
it is the default location for all the tables that you create
100
Consider the join_buffer_size parameter in MySQL Server.
Which two statements are true about the join buffer?
the join buffer is used to process SORTS when complex joins are performed
the value should be increased from the default if the query joins large rows without an index
101
join_buffer_size is described as: “The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.”
“Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.” The join buffer is allocated to cache table rows when the join can’t use an index. If your database(s) suffers from many joins performed without indexes, it cannot be solved by increasing join_buffer_size. The problem is “joins performed without indexes.” Thus, the solution for faster joins is to add indexes.
102
join_buffer_size
Increase its value to get faster full joins when adding indexes is not possible.
might be memory issues if you set this too high.
Remember that one join buffer is allocated for each full join between two tables.
For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary. Best left low globally and set high in sessions (by using SET SESSION syntax) that require large full joins.
103
innodb_force_recovery is 0 by default (normal startup without forced recovery). The permissible nonzero values for innodb_force_recovery are 1 to 6. A larger value includes the functionality of lesser values. For example, a value of 3 includes all of the functionality of values 1 and 2.
If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 4 or greater is considered dangerous because data files can be permanently corrupted. A value of 6 is considered drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.
As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0. An innodb_force_recovery setting of 4 or greater places InnoDB in read-only mode.
104
Why does the following fail:
[root@vmrac2 ~]# mysql -ujoe -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1347
Server version: 8.0.26-commercial MySQL Enterprise Server - Commercial
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
```
mysql> SET SESSION max_connections = 200;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
mysql>
```
max_connections requires global scope
105
What is the best method for monitoring Group Replication conflict resolution?
review the performance_schema objects
use performance_schema;
show tables \G
processlist |
| replication_applier_configuration |
| replication_applier_filters |
| replication_applier_global_filters |
| replication_applier_status |
| replication_applier_status_by_coordinator |
| replication_applier_status_by_worker |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_connection_configuration |
| replication_connection_status |
| replication_group_member_stats |
| replication_group_members
106
You enable binary logging on MySQL Server with the configuration: binlog-format=STATEMENT log-bin
Which database updates are logged on the master server to the binary log by default?
Binary logs can be written in three formats:
STATEMENT: Actual SQL statements are logged.
ROW: Changes made to each row are logged. For example, an update statement updates 10 rows, the updated information of all 10 rows is written to the log. Whereas in statement-based replication, only the update statement is written. The default format is ROW.
MIXED: MySQL switches from STATEMENT to ROW as and when needed.
107
To connect using SSL, you need:
A Certificate Authority (CA) certificate in a server-ca.pem file.
A client public key certificate in a client-cert.pem file.
A client private key in a client-key.pem file.
If you lack a client certificate and a corresponding private key, create a new client certificate.
Before you begin, confirm that you have installed the client and configured access to your instance.
To connect to your instance using SSL:
Start the mysql client:
mysql --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem \
--host=INSTANCE_IP --ssl-mode=verify_ca --user=root --password
These ssl certs and files are stored on the mysql server
in the datadir by default
[root@vmrac2 mysql]# pwd
/var/lib/mysql
/var/lib/mysql/-rw------- ca-key.pem
/var/lib/mysql/-rw-r--r-- ca.pem
/var/lib/mysql/-rw-r--r-- client-cert.pem
/var/lib/mysql/-rw------- client-key.pem
/var/lib/mysql/-rw------- private_key.pem
/var/lib/mysql/-rw-r--r-- public_key.pem
/var/lib/mysql/-rw-r--r-- server-cert.pem
/var/lib/mysql/-rw------- server-key.pem
to enable the server for encrypted connections, start it with these lines in the my.cnf file, changing the file names as necessary:
[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
108
Server-Side Runtime Configuration and Monitoring for Encrypted Connections
Prior to MySQL 8.0.16, the tls_xxx and ssl_xxx system variables that configure encrypted-connection support can be set only at server startup. These system variables therefore determine the TLS context the server uses for all new connections.
As of MySQL 8.0.16, the tls_xxx and ssl_xxx system variables are dynamic and can be set at runtime, not just at startup. If changed with SET GLOBAL, the new values apply only until server restart. If changed with SET PERSIST, the new values also carry over to subsequent server restarts.
mysql> show global variables like 'tls_%';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| tls_ciphersuites | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
+------------------+-----------------------+
2 rows in set (0.00 sec)
```
mysql> show global variables like 'ssl_%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
+---------------+-----------------+
8 rows in set (0.01 sec)
```
109
SQL_MODE
set this mode within /etc/my.cnf in the [mysqld] section of the file
HOW TO Disable Strict Mode via my.cnf/my.ini?
This method disables it by changing the value of SQL_MODE in my.cnf file (for Linux) OR my.ini file (for windows server) and restarting the MySQL server.
Look for the following line:
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
You can change the value of sql_mode to NO_ENGINE_SUBSTITUTION to completely disable strict mode, but you may want to look up each mode that is configured before disabling it or you can simply change it to:
sql-mode="" (i.e. Blank)
If sql_mode isn't set, you can add it under the [mysqld] heading, then save the file, and restart MySQL.
Restart the MySQL Service.
110
SQL_MODE - default values
The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.
On a MySQL 8.0 installation where sql_mode is NOT set in the my.cnf file , by default I see.
mysql> show global variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
111
SQL_MODE - default values
Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.
On a MySQL 8.0 installation where sql_mode is NOT set in the my.cnf file , by default I see.
mysql> show global variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
112
The most important sql_mode values are probably these:
ANSI
This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes .
STRICT_TRANS_TABLES
If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.
TRADITIONAL
Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. It is one of the special combination modes
NOTE:
refering to “strict mode,” it means a mode with either or both STRICT_TRANS_TABLES or STRICT_ALL_TABLES enabled.
When sql_mode includes STRICT_TRANS_TABLES an error will occur if you try to add data that is out of range of the data types.
```
mysql> insert into small (val1) values ('tobigtobig');
ERROR 1406 (22001): Data too long for column 'val1' at row 1
```
If you set the sql_mode to nothing , or removed the STRINCT_TRANS_TABLES then no error occurs, the data is inserted but truncated.
mysql> set session sql_mode="";
Query OK, 0 rows affected (0.00 sec)
```
mysql> show session variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
```
mysql> insert into small (val1) values ('tobigtobig');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> desc small;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| val1 | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
```
mysql> select * from small;
+----+-------+
| id | val1 |
+----+-------+
| 1 | five5 |
| 2 | five5 |
| 3 | five5 |
| 4 | five5 |
| 5 | for4 |
| 6 | for4 |
| 7 | tobig |
+----+-------+
```
113
SQL_MODE
NO_ENGINE_SUBSTITUTION
Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.
By default, NO_ENGINE_SUBSTITUTION is enabled.
With this sql_mode the following error occurs
```
mysql> create table badengine2 (id int) engine=FEDERATED;
ERROR 1286 (42000): Unknown storage engine 'FEDERATED'
```
```
In this example we do NOT have that set and therefore the substitution occurs.
mysql> show session variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.01 sec)
```
mysql> create table badengine (id int) engine=FEDERATED;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show create table badengine;
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| badengine | CREATE TABLE `badengine` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
114
ENUM
```
CREATE TABLE tickets (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
priority ENUM('Low', 'Medium', 'High') NOT NULL
);
```
mysql> INSERT INTO tickets(title, priority) VALUES('Scan virus for computer D00', '');
ERROR 1265 (01000): Data truncated for column 'priority' at row 1
mysql> INSERT INTO tickets(title, priority) VALUES('Scan virus for computer D00', 'WRONG');
ERROR 1265 (01000): Data truncated for column 'priority' at row 1
mysql> INSERT INTO tickets(title, priority) VALUES('Scan virus for computer D03', 'WRONG');
ERROR 1265 (01000): Data truncated for column 'priority' at row 1
115
In the non-strict SQL mode, if you insert an invalid value into an ENUM column, MySQL will use an empty string '' with the numeric index 0 for inserting. In case the strict SQL mode is enabled, trying to insert an invalid ENUM value will result in an error.
Note that an ENUM column can accept NULL values if it is defined as a null-able column.
mysql> ^C
mysql> INSERT INTO tickets(title, priority) VALUES('Scan virus for computer D03', 'MEDIUM');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tickets;
+----+-----------------------------+----------+
| id | title | priority |
+----+-----------------------------+----------+
| 1 | Scan virus for computer A | High |
| 2 | Scan virus for computer A1 | Low |
| 3 | Scan virus for computer A2 | Low |
| 4 | Scan virus for computer B2 | Medium |
| 5 | Scan virus for computer B3 | Medium |
| 6 | Scan virus for computer B3 | |
| 7 | Scan virus for computer B00 | |
| 8 | Scan virus for computer B01 | |
| 9 | Scan virus for computer D03 | Medium |
+----+-----------------------------+----------+
9 rows in set (0.01 sec)
```
mysql> INSERT INTO tickets(title, priority) VALUES('Scan virus for computer e03', 'MEDIM');
ERROR 1265 (01000): Data truncated for column 'priority' at row 1
```
116
Performance Schema Replication Tables
The Performance Schema provides tables that expose replication information. This is similar to the information available from the SHOW REPLICA STATUS statement, but representation in table form is more accessible and has usability benefits
Tables that contain information about the connection of the replica to the source:
replication_connection_configuration: Configuration parameters for connecting to the source
replication_connection_status: Current status of the connection to the source
replication_asynchronous_connection_failover: Source lists for the asynchronous connection failover mechanism
117
Performance Schema Replication Tables
The Performance Schema provides tables that expose replication information. This is similar to the information available from the SHOW REPLICA STATUS statement, but representation in table form is more accessible and has usability benefits
Tables that contain information about the connection of the replica to the source:
replication_connection_configuration: Configuration parameters for connecting to the source
replication_connection_status: Current status of the connection to the source
replication_asynchronous_connection_failover: Source lists for the asynchronous connection failover mechanism
