MySQL my.cnf 常用配置

netkiller-

于 2024-08-30 08:45:00 发布

阅读量871

点赞数 25

分类专栏：运维手札数据库手札文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u010604770/article/details/141686750

版权

MySQL my.cnf 常用配置

1.7.1. bind-address

MySQL 通过 yum 安装后默认是监听 127.0.0.1 / ::1 如果你希望从其他IP访问3306端口，需要修改绑定地址为 0.0.0.0

			
bind-address=127.0.0.1

0.0.0.0:3306

			
bind-address = 0.0.0.0

指定IP地址

			
[root@localhost ~]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: enp2s0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:e0:70:81:9e:48 brd ff:ff:ff:ff:ff:ff
    inet 192.168.30.10/24 brd 192.168.30.255 scope global noprefixroute enp2s0
       valid_lft forever preferred_lft forever
    inet 192.168.30.11/24 brd 192.168.30.255 scope global secondary noprefixroute enp2s0
       valid_lft forever preferred_lft forever
    inet 192.168.30.12/24 brd 192.168.30.255 scope global secondary noprefixroute enp2s0
       valid_lft forever preferred_lft forever
    inet 192.168.30.13/24 brd 192.168.30.255 scope global secondary noprefixroute enp2s0
       valid_lft forever preferred_lft forever
    inet 192.168.30.14/24 brd 192.168.30.255 scope global secondary noprefixroute enp2s0
       valid_lft forever preferred_lft forever
    inet6 fe80::2e0:70ff:fe81:9e48/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: wlp1s0: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN group default qlen 1000
    link/ether 40:9f:38:b6:e0:55 brd ff:ff:ff:ff:ff:ff
4: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
    link/ether 02:42:f0:6f:b3:4b brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
45: br-a32fa1ca1437: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
    link/ether 02:42:d7:ae:ea:5d brd ff:ff:ff:ff:ff:ff
    inet 172.18.0.1/16 brd 172.18.255.255 scope global br-a32fa1ca1437
       valid_lft forever preferred_lft forever

上面服务器上配置多个IP地址

			
bind-address=192.168.30.10

1.7.2. 禁用TCP/IP链接

与bind-address互斥，skip-networking 开启，只能通过UNIX SOCKET链接，而不能使用IP地址链接

[mysqld]
skip-networking

1.7.3. 配置字符集

Configuring Database Character Encoding

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Server Character Set and Collation

			
shell> mysqld --character-set-server=latin1
shell> mysqld --character-set-server=latin1 \
           --collation-server=latin1_swedish_ci

$ vim /etc/mysql/my.cnf

[mysqld]
character-set-server=utf8
collation_server=utf8_general_ci
init_connect='SET NAMES utf8'

[client]
character_set_client=utf8

mysql --default-character-set=utf8 -u root -p

			
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_se