Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Articles by AmitDiwan
Page 687 of 840
Enable MySQL Compression
Before a compressed table is created, ensure that the innodb_file_per_table configuration option is enabled, and innodb_file_format is set to Barracuda. These parameters can be found in the MySQL configuration file my.cnf or my.ini, or with the SET statement without having to shut down the MySQL server.To enable compression for a table, the clauses ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE, or both can be used in a CREATE TABLE or ALTER TABLE statement.Let us see the statements to create a compressed table −QuerySET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_file_format=Barracuda; CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;Here, If ROW_FORMAT=COMPRESSED is specified, the KEY_BLOCK_SIZE can be ...
Read MoreThe complete list of new features in MySQL 8.0
The new features in MySQL 8.0 have been briefly listed below −Transactional Data DictionaryA transactional data dictionary to store information about object.Atomic Data Definition LanguageAn atomic data definition language (DDL) statement to combine updates made to data dictionary, storage engine operations and so on.Security EnhancedThe security levels have been improved, and DBA (Database administrator) has been given greater flexibility for account management.EncryptionThe encryption defaults have been defined and implemented globally for table encryption. The ‘default_table_encryption’ variable is used to define an encryption default for schemas that have been newly created. The default encryption for a schema can be defined with ...
Read MoreBest Way to compress mysqldump?
The compression operation is used only if both client and server support ‘zlib’ compression, and the client requests compression.Usage of mysqldumpThe advantage of using compression is that it reduces the size of the payloadOn the other hand, the disadvantage of using compression is that it increases the computation time.Performance benefits will depend largely on the size of the result set which is being sent.In addition to this, the network bandwidth and latency between the database server and its clients also matters. The larger the result set, the larger will be the latency.In other words, the lesser the bandwidth, the more ...
Read MoreEnable TLS for MySQL Clients
TLS is also known as SSL (Secure Sockets Layer). It refers to Transport Layer Security.When there is an unencrypted connection between the MySQL client and the server, a person who has access to the network can watch all the traffic and inspect the data that is being sent or received between client and server. When the user wishes to move information over a network in a secure method, an unencrypted connection is not acceptable.To make any sort of data unreadable, encryption has to be used. Encryption algorithms usually include security elements that help resist many kinds of known attacks, some ...
Read MoreWhen should I use MySQL compressed protocol?
Let us understand when MySQL compressed protocol should be used −The compression operation is used only if both client and server support ‘zlib’ compression, and the client requests compression.The advantage of using compression is that it reduces the size of the payload.On the other hand, the disadvantage of using compression is that it increases the computation time.Performance benefits will depend largely on the size of the result set which is being sent.In addition to this, the network bandwidth and latency between the database server and its clients also matters.The larger the result set, the larger will be the latency.In other ...
Read MoreRestricting MySQL Connections to Secure Transport
MySQL will make secure connections easier when it has streamlined key generation for both MySQL Community and MySQL Enterprise. This way, the security is improved by expanding support for TLSv1.1 and TLSv1.2. This also helps administrators determine if clients are connecting securely or not with enhanced visibility into connection types.Extending this importance on secure connections, MySQL server introduced a new server-side configuration option that allows MySQL administrators to restrict connections to clients who use secure transport. When we say about the connection needing secure transport, the first question that needs to be addressed is the kind of transport it uses.Secure ...
Read MoreHow to force MySQL to connect by TCP instead of a Unix socket?
Programs such as ‘mysql’ and ‘mysqldump’, that use MySQL client library have the support of MySQL connection to server with the help of many transport protocols, such as TCP/IP, Unix socket file, named pipe, shared memory, and so on.With respect to a given connection, if the transport protocol is not specified, it is determined as a separate task.ExampleConnections to localhost will result in a socket file connection on Unix and Unix-like systems, and a TCP/IP connection to 127.0.0.1 otherwise. If the protocol has to be specified in particular, it is done using the --protocol command option.The below table shows the ...
Read MoreMySQL Option Defaults, Options Expecting Values, and the = Sign
Let us understand the default options, the options that expects values, and the ‘=’ sign in MySQL −By convention, the long forms of options which assign a value are written using an equals (=) sign. It has been shown below −mysql --host=tonfisk --user=jonFor options which require a value, i.e which doesn’t have a default value, the equal sign isn’t required. This means that the below command would be valid in such cases −mysql --host tonfisk --user jonIn both the above cases, the mysql client tries to connect to a MySQL server that is running on the host named “tonfisk” with ...
Read MoreUsing Options to Set MySQL Program Variables
Many of the MySQL programs have internal variables that are set during runtime using the SET statement. Most of these program variables can also be set at server startup with the help of the same syntax that applies to specifying the program options.Example 1The mysql has a max_allowed_packet variable which controls the maximum size of its communication buffer.To set this max_allowed_packet variable for mysql to a value of 16MB, either of the below mentioned commands can be used −mysql --max_allowed_packet=16777216 (or) mysql --max_allowed_packet=16MThe first command specifies the value in terms of bytes. On the other hand, the second command specifies ...
Read MoreMySQL Program Option Modifiers
Some options are “boolean” and control the behavior which can be turned on or off.ExampleThe mysql client supports a --column-names option which tells whether or not to display a row of column names at the beginning of the query results.By default, this option is enabled. But sometimes, it may be required to disable this. This could be while sending the output of mysql into another program which is expecting to see only data and not the initial header line.To disable column names, the options can be specified in any of the below mentioned forms −Query--disable-column-names (or) --skip-column-names (or) --column-names=0The --disable ...
Read More