Shulou(Shulou.com)05/31 Report--

This article introduces how to use IPv6 in MySQL, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

MySQL's support for IPv6 includes these features:

The .MySQL server can accept TCP/IP connections from clients through IPv6. For example, the following command connects to the MySQL server locally through IPv6:

[mysql@localhost] $mysql-h:: 1-uusername-ppassword

To use this feature, you must meet the following two conditions

. The server operating system must be configured to support IPv6

The default configuration of the .MySQL server must support IPv4 connections in addition to IPv6 connections. To change the default configuration, you can use the appropriate-- bind-address option when starting the MySQL server.

The .MySQL account allows IPv6 addresses to allow DBA to specify permissions for clients that connect to the server through IPv6. For example:

Mysql > create user 'v6 rootless bicycle abcd 1 'identified by; mysql > grant select on mysql.* to 'v6 rootball pedestal pedestal purl 1'

The .IPv6 feature supports translation between a string and an internal format IPv6 address format and checks whether it is a valid IPv6 address. For example

INET6_ATON () and INET6_NTOA () are similar to INET_ATON () and INET_NTOA (), but can handle IPv6 addresses in addition to IPv4 addresses.

Verify that the operating system supports IPv6

Before the MySQL server can accept connections from IPv6, the operating system host where the server resides must support IPv6. For example, execute the following command on the Linux host:

[root@localhost ~] # ping6:: 1PING:: 1 (:: 1) 56 data bytes64 bytes from:: 1: icmp_seq=1 ttl=64 time=0.100 ms64 bytes from:: 1: icmp_seq=2 ttl=64 time=0.056 ms64 bytes from:: 1: icmp_seq=3 ttl=64 time=0.035 ms64 bytes from:: 1: icmp_seq=4 ttl=64 time=0.039 ms....

To generate a detailed description of the operating system network interface, execute ifconfig-an and look at the IPv6 address in the output.

[root@localhost] # ifconfig-adocker0: flags=4163 mtu 1500 inet 172.17.0.1 netmask 255.255.0.0 broadcast 172.17.255.255 inet6 fe80::42:15ff:fe1d:e05a prefixlen 64 scopeid 0x20 ether 02:42:15:1d:e0:5a txqueuelen 0 (Ethernet) RX packets 15 bytes 3638 (3.5 KiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 33 bytes 3825 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0ens160: flags=4163 mtu 1500 inet 192.168.1.250 netmask 255.255.255.0 broadcast 192.168.1.255 inet6 fe80::250:56ff:fea3:90e6 prefixlen 64 scopeid 0x20 ether 00:50:56:a3:90:e6 txqueuelen 1000 (Ethernet) RX packets 5634313 bytes 364548809 (347.6 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 19054 bytes 2337541 (2.2MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

If your operating system does not support IPv6, check the operating system documentation for instructions to enable IPv6. This may only require reconfiguring the existing network interface to add an IPv6 address. Or you may need to make more changes, such as using the IPv6 option to rebuild the kernel.

Configure the MySQL server to allow IPv6 connections

The MySQL server listens for a single network socket for TCP/IP connections. Such sockets are bound to a single address, but one address may be mapped to multiple network interfaces. To specify an address, use the-- bind-address=addr option when the server starts, where addr is the IPv4 or IPv6 address or hostname. (IPv6 addresses are not supported before MySQL 5.5.3). If addr is a hostname, the server resolves the hostname to an IP address and binds to it.

The server handles different types of addresses as follows:

. If the address is *, if the server host supports IPv6, the server can accept TCP/IP connections from all IPv6 and IPv4 network interfaces on all servers, or otherwise only TCP/IP connections from IPv4 addresses on all servers. Use this address to allow IPv4 and IPv6 connections from all server network interfaces to be accepted. This value is the default.

. If the address is 0.0.0.0, the server can only accept TCP/IP connections from IPv4 network interfaces on all servers.

. If the address is::, the server accepts TCP/IP connections from the IPv4 and IPv6 network interfaces on all servers, which allows IPv4 and IPv6 connections from all server network interfaces to be accepted.

. If the address is an IPv4 mapped address, the server accepts TCP/IP connections from that address, which is in IPv4 or IPv6 format. For example, if the server is bound to:: ffff:127.0.0.1, the client can use-- host=127.0.0.1 or-- host=::ffff:127.0.0.1 to connect.

. If the address is a regular IPv4 or IPv6 address (such as 127.0.0.1 or:: 1), the server can only accept TCP/IP connections with IPv4 or IPv6 addresses.

If you prefer to bind the server to a specific address, make sure that the mysql.user authorization table contains a user with administrative privileges that can use that address to connect. Otherwise you will not be able to shut down the server. For example, if you tend to bind the server to *, you can use all existing accounts to connect. But if you bind the server to:: 1, it can only accept connections to this address. In this case, first make sure that the 'root'@'::1' account exists in the mysql.user table, so you can still connect to the server and close it.

Connect to the MySQL server using the IPv6 local host address

The following describes how to configure MySQL to allow clients to connect to the local server through IPv6 (:: 1 local host address).

1. Use the appropriate-- bind-address option when starting the MySQL server to allow the server to accept IPv6 connections. For example, set bind-address in the my.cnf options file and restart the server

[mysqld]. Bind-address=*. [root@localhost ~] # systemctl stop mysqld [root@localhost ~] # systemctl start mysqld [root@localhost ~] # systemctl status mysqld* mysqld.service-LSB: start and stop MySQL Loaded: loaded (/ etc/rc.d/init.d/mysqld; bad; vendor preset: disabled) Active: active (running) since two 2020-01-14 16:56:48 CST 5s ago Docs: man:systemd-sysv-generator (8) Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited Status=0/SUCCESS) Tasks: 28 Memory: 168.2m CGroup: / system.slice/mysqld.service |-23273 / bin/sh / mysqlsoft/mysql/bin/mysqld_safe-- datadir=/mysqldata/mysql-- pid-file=/mysqldata/mysql/mysqld.pid `- 23591 / mysqlsoft/mysql/bin/mysqld-- basedir=/mysqlsoft/mysql-- datadir=/mysqldata/mysql-- plugin-dir=/mysqlsoft/mysql/lib/plugin-- user=mysql-- log-error= / mysqldata/mysql/mysql.err-- pid-file=/mysqldata/mysql/mysqld.pid.. January 14 16:56:46 localhost.localdomain systemd [1]: Starting LSB: start and stop MySQL...1 14 16:56:48 localhost.localdomain mysqld [23257]: Starting MySQL.. SUCCESS! January 14 16:56:48 localhost.localdomain systemd [1]: Started LSB: start and stop MySQL.

In addition, you can also bind the server to:: 1, but the use of the server will be more limited to TCP/IP connections. It can only accept connections from a single IPv6 address and will reject connections from IPv4.

two。 As an administrator, connect to the server to create a local user account that will connect using the:: 1 local IPv6 host address

Mysql > create user 'ipv6root'@'::1' identified by "abcd"; Query OK, 0 rows affected (0.01 sec) mysql > grant select on *. * to' ipv6root'@'::1';Query OK, 0 rows affected (0.02 sec)

3. Call the mysql client tool to connect to the server using the newly created account

[mysql@localhost mysql] $mysql-h:: 1-uipv6root-pabcd mysqlmysql: [Warning] Using a password on the command line interface can be insecure.Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-AWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 7Server version: 5.7.26-log Source distributionCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > select host,user from user +-+-+ | host | user | +-+-+ |% | csroot |% | root |:: 1 | ipv6root | | localhost | mysql.session | | localhost | mysql.sys | +- -+-+ 5 rows in set (0.00 sec)

4. Execute the status command to display connection information

Mysql > status-mysql Ver 14.14 Distrib 5.7.26, for Linux (x86 / 64) using EditLine wrapperConnection id: 7Current database: mysqlCurrent user: ipv6root@localhostSSL: Cipher in use is DHE-RSA-AES256-SHACurrent pager: stdoutUsing outfile:''Using delimiter: Server version: 5.7.26-log Source distributionProtocol version: 10Connection: 1 via TCP/IPServer characterset: utf8mb4Db characterset: utf8mb4Client characterset: gb2312Conn. Characterset: gb2312TCP port: 3306Uptime: 11 min 47 secThreads: 1 Questions: 263 Slow queries: 0 Opens: 336 Flush tables: 1 Open tables: 329 Queries per second avg: 0.371-mysql > SELECT CURRENT_USER (), @ @ bind_address +-+ | CURRENT_USER () | @ @ bind_address | +-+-+ | ipv6root@::1 | * | +-+- -+ 1 row in set (0.00 sec)

Connect to the MySQL server using an IPv6 non-local host address

The following describes how to configure the MySQL server to allow remote hosts to connect to the MySQL server through IPv6. This is similar to using IPv6 to connect to the MySQL server on the local host, but the server and the client are on different hosts, each with a different IPv6 address.

The IPv6 address of the server host is 2018

[root@shard1 network-scripts] # ifconfig-aens160: flags=4163 mtu 1500 inet6 2018 Parade 1 prefixlen 64 scopeid 0x0 ether 00:50:56:a0:51:0c txqueuelen 1000 (Ethernet) RX packets 27089788 bytes 2770300693 (2.5 GiB) RX errors 0 dropped 6 overruns 0 frame 0 TX packets 6720001 bytes 532979878 (508.2 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

The IPv6 address of the client host is 2018

Ens160: flags=4163 mtu 1500 inet6 2018 prefixlen 64 scopeid 0x0 ether 00:50:56:a0:4e:69 txqueuelen 1000 (Ethernet) RX packets 25337245 bytes 1913014541 (1.7 GiB) RX errors 0 dropped 127 overruns 0 frame 0 TX packets 1536650 bytes 166036937 (158.3 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

The specific operations are as follows:

1. Use the appropriate-- bind-address option when starting the MySQL server to allow the server to accept IPv6 connections. For example, set bind-address in the my.cnf options file and restart the server

[mysqld]. Bind-address=*. [root@localhost ~] # systemctl stop mysqld [root@localhost ~] # systemctl start mysqld [root@localhost ~] # systemctl status mysqld* mysqld.service-LSB: start and stop MySQL Loaded: loaded (/ etc/rc.d/init.d/mysqld; bad; vendor preset: disabled) Active: active (running) since two 2020-01-14 16:56:48 CST 5s ago Docs: man:systemd-sysv-generator (8) Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited Status=0/SUCCESS) Tasks: 28 Memory: 168.2m CGroup: / system.slice/mysqld.service |-23273 / bin/sh / mysqlsoft/mysql/bin/mysqld_safe-- datadir=/mysqldata/mysql-- pid-file=/mysqldata/mysql/mysqld.pid `- 23591 / mysqlsoft/mysql/bin/mysqld-- basedir=/mysqlsoft/mysql-- datadir=/mysqldata/mysql-- plugin-dir=/mysqlsoft/mysql/lib/plugin-- user=mysql-- log-error= / mysqldata/mysql/mysql.err-- pid-file=/mysqldata/mysql/mysqld.pid.. January 14 16:56:46 localhost.localdomain systemd [1]: Starting LSB: start and stop MySQL...1 14 16:56:48 localhost.localdomain mysqld [23257]: Starting MySQL.. SUCCESS! January 14 16:56:48 localhost.localdomain systemd [1]: Started LSB: start and stop MySQL.

In addition, you can bind the server to 2018:db1:0:f01::1, but this makes the use of the server more limited to TCP/IP connections. It can only accept connections from a single IPv6 address and will reject connections from IPv4.

two。 Creating an account in the server (2018) will connect to the server from the client host (2018)

Mysql > create user 'csroot'@'%' identified by "123456"; Query OK, 0 rows affected (0.01 sec) mysql > GRANT ALL PRIVILEGES ON *. * TO' csroot'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec)

3. On the client host (2018 2), call the mysql client to connect to the server using the new account created (I use MariaDB as the client tool at this time)

-bash-4.2$ mysql-h 2018 mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-AWelcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 6Server version: 5.6.23 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.MySQL [mysql] >

4. Execute the status command to display connection information

MySQL [mysql] > status-mysql Ver 15.1 Distrib 10.0.38-MariaDB, for Linux (x86 / 64) using readline 5.1Connection id: 6Current database: mysqlCurrent user: csroot@2018::2SSL: Not in useCurrent pager: stdoutUsing outfile:''Using delimiter: Server: MySQLServer version: 5.6.23 MySQL Community Server (GPL) Protocol version: 10Connection: 2018 GPL GPL 1 via TCP/IPServer characterset: latin1Db characterset: latin1Client characterset: gb2312Conn. Characterset: gb2312TCP port: 3306Uptime: 34 days 50 min 24 secThreads: 2 Questions: 175 Slow queries: 0 Opens: 87 Flush tables: 1 Open tables: 80 Queries per second avg: 0.000-MySQL [mysql] > select current_user (), @ @ bind_address +-+ | current_user () | @ @ bind_address | +-+-+ | csroot@% | * | +-+- -+ 1 row in set (0.00 sec) that's all about how to use IPv6 in MySQL. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.