Learn MySQL Topics : Security, Backups, Replication, Architecture, Install, Update, Ect. Flashcards
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
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)
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.
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 …
Pluggable authentication enables these important capabilities:
Choice of authentication methods
External authentication.
Proxy users
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.
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.
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.
PAM Pluggable Authentication
PAM pluggable authentication is an extension included in MySQL Enterprise Edition, a commercial product.
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.
PAM pluggable authentication provides these capabilities:
External authentication:
Proxy user support:
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.
Plugin and Library Names for PAM Authentication
Plugin and Library Names for PAM Authentication
Server-side plugin authentication_pam
Client-side plugin mysql_clear_password
Library file authentication_pam.so
PAM Authentication and libmysqlclient client library
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.
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.
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.
To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%pam%';
\+--------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
\+--------------------+---------------+
| authentication_pam | ACTIVE |
\+--------------------+---------------+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.
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)
Server status variable
Aborted_clients
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)
Server status variable
Aborted_connects
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)
Server status variables
Binlog_cache_disk_use
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.
Server status variables
Binlog_cache_use
The number of transactions that used the binary log cache.
Server status variables
Bytes_received
The number of bytes received from all clients.
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.
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.
Program Options
-? and –help
are the short and long forms of the option that instructs a MySQL program to display its help message.
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.
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
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
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.
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.
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.
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.
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
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
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.
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)
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’;
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
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
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’;
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)
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.
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)
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.
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
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.
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.
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/
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)
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.
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;
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)
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.
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.
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.
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.
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
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
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.
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.
