How MySQL server listens for a TCP/IP connection

-
The MySQL server spawns a separate background thread for each enabled protocol that it supports on the target platform. This single thread per protocol further manages each client connection and helps to deliver a separate thread for each connection request. There can be different supported protocol depending upon the platform like Unix socket on Unix like platform, Named Pipe and Shared Memory on Windows platform.

The MySQL server listens for TCP/IP connections (remote connections) from the client on all platforms.

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. To specify an address, use the --bind-address=addr option at server startup, where addr is an IPv4 or IPv6 address or a host name. If addr is a host name, the server resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, the server uses the first IPv4 address if there are any, or the first IPv6 address otherwise.

The server treats different types of addresses as follows:
  • If the address is *, the server accepts TCP/IP connections on all server host IPv4 interfaces, and, if the server host supports IPv6, on all IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces. This value is the default. 
  • If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
  • If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces.
  • If the address is an IPv4-mapped address, the server accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For example, if the server is bound to ::ffff:127.0.0.1, clients can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1.
  • If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.
If binding to the address fails, the server produces an error and does not start.

If you intend to bind the server to a specific address, be sure that the mysql.user system table contains an account with administrative privileges that you can use to connect to that address. Otherwise, you will not be able to shut down the server. For example, if you bind the server to *, you can connect to it using all existing accounts. But if you bind the server to ::1, it accepts connections only on that address. In that case, first make sure that the 'root'@'::1' account is present in the mysql.user table so you can still connect to the server to shut it down.

Comments

Post a Comment

Back To Top

Popular posts from this blog

How to save video from Internet Explorer

-
How to save video from Internet Explorer Here I am going to share an experience with you, where I was required to get the video file that I was playing in Internet explorer out of Temporary directory and save it to some permanent disk folder. Note: It is far better if you use video download tools with the web browser. Internet Explorer Version: 7.0.6xxx [Open Internet Explorer – Help – About Internet Explorer] Operating System: Windows Vista I was not playing file from you tube [I do not see any tmp file while watching video from youtube.] I found the file in following directory: C:\Users\<UserName>\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low [ Although the directory is not visible in my computer system, so I directly put it in address bar/location bar in windows explorer. ] File name was fla3870.tmp [May be in your system It will be by different name] You have to find the video file by trick technique for example: 1.
Read more

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more