Bo's Oracle Station › 博客首页 ›Oracle技术› Oracle云、Linux、MySQL和Java › 查看内容

【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议2

2026-3-9 15:48| 发布者: admin| 查看: 1018| 评论: 0|原作者: Bo Tang

摘要: 本博客详细介绍为了学习MySQL 9.x，可以对数据库环境做的多种配置。

Author: Bo Tang

1. 为了方便练习，导入作为示例的world_x-db.tar.gz数据库：

[root@station95 ~]# mysql -u root -p

Enter password:

[root@station95 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16

Server version: 9.6.0 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| world_x |

+--------------------+

5 rows in set (0.005 sec)

mysql> show variables like 'datadir'\G

*************************** 1. row ***************************

Variable_name: datadir

Value: /var/lib/mysql/

1 row in set (0.008 sec)

每个数据库都有一个同名的目录:

[root@station95 ~]# cd /var/lib/mysql

[root@station95 mysql]# ls

auto.cnf binlog.index client-key.pem ibdata1 mysql mysql_upgrade_history server-cert.pem sys

binlog.000001 ca-key.pem '#ib_16384_0.dblwr' ibtmp1 mysql.ibd performance_schema server-key.pem undo_001

binlog.000002 ca.pem '#ib_16384_1.dblwr' '#innodb_redo' mysql.sock private_key.pem station95.log undo_002

binlog.000003 client-cert.pem ib_buffer_pool '#innodb_temp' mysql.sock.lock public_key.pem station95-slow.log world_x

查看world_x目录下的内容:

[root@station95 mysql]# ls -l world_x

total 1184

-rw-r----- 1 mysql mysql 606208 Mar 9 05:13 city.ibd

-rw-r----- 1 mysql mysql 131072 Mar 9 05:13 country.ibd

-rw-r----- 1 mysql mysql 229376 Mar 9 05:13 countryinfo.ibd

-rw-r----- 1 mysql mysql 245760 Mar 9 05:13 countrylanguage.ibd

每个ibd文件代表一个表:

mysql> use world_x;

No connection. Trying to reconnect...

Connection id: 15

Current database: *** NONE ***

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+-------------------+

| Tables_in_world_x |

+-------------------+

| city |

| country |

| countryinfo |

| countrylanguage |

+-------------------+

4 rows in set (0.003 sec)

2. 如果需要，可以修改一些变量：
会话级别修改（如果要在全局级别修改，需要在命令中添加global）：

mysql> show variables like 'sql_mode'\G

*************************** 1. row ***************************

Variable_name: sql_mode

Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

1 row in set (0.015 sec)

mysql> SET sql_mode = 'STRICT_TRANS_TABLES,PIPES_AS_CONCAT';

Query OK, 0 rows affected, 1 warning (0.000 sec)

mysql> show variables like 'sql_mode'\G

*************************** 1. row ***************************

Variable_name: sql_mode

Value: PIPES_AS_CONCAT,STRICT_TRANS_TABLES

1 row in set (0.004 sec)

另一个会话：

[root@station95 mysql]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16

Server version: 9.6.0 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

05:14 (none)> show variables like 'sql_mode'\G

*************************** 1. row ***************************

Variable_name: sql_mode

Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

1 row in set (0.006 sec)

3. 如果需要，可以修改端口：
例如，把端口修改为3309:

[root@station95 mysql]# vim /etc/my.cnf

[root@station95 mysql]# cat /etc/my.cnf

# For advice on how to change settings please see

# https://dev.mysql.com/doc/refman/9.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove the leading "# " to disable binary logging

# Binary logging captures changes between backups and is enabled by

# default. It's default setting is log_bin=binlog

# disable_log_bin

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

port=3309

[client]

port=3309

重新启动MySQL Server，观察端口的修改：

[root@station95 mysql]# systemctl restart mysqld

[root@station95 mysql]# netstat -lntp | grep :3309

tcp6 0 0 :::3309 :::* LISTEN 126707/mysqld

4. 如果需要，打开general log/slow log：

[root@station95 ~]# cat /etc/my.cnf

# For advice on how to change settings please see

# https://dev.mysql.com/doc/refman/9.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove the leading "# " to disable binary logging

# Binary logging captures changes between backups and is enabled by

# default. It's default setting is log_bin=binlog

# disable_log_bin

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

port=3309

general_log

slow_query_log

[client]

port=3309

重新启动MySQL Server，观察日志：

[root@station95 mysql]# ls

auto.cnf binlog.index client-key.pem ibdata1 mysql mysql_upgrade_history server-cert.pem sys

binlog.000001 ca-key.pem '#ib_16384_0.dblwr' ibtmp1 mysql.ibd performance_schema server-key.pem undo_001

binlog.000002 ca.pem '#ib_16384_1.dblwr' '#innodb_redo' mysql.sock private_key.pem station95.log undo_002

binlog.000003 client-cert.pem ib_buffer_pool '#innodb_temp' mysql.sock.lock public_key.pem station95-slow.log world_x

[root@station95 mysql]# cat station95.log

/usr/sbin/mysqld, Version: 9.6.0 (MySQL Community Server - GPL). started with:

Tcp port: 3309 Unix socket: /var/lib/mysql/mysql.sock

Time Id Command Argument

2026-03-09T10:52:27.249376Z 0 Execute CREATE TABLE performance_schema.innodb_redo_log_files(

`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',

`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',

`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',

`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',

`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',

`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',

`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on smaller levels than this value, have already consumed this file.'

)engine = 'performance_schema'

[root@station95 mysql]# cat station95-slow.log

/usr/sbin/mysqld, Version: 9.6.0 (MySQL Community Server - GPL). started with:

Tcp port: 3309 Unix socket: /var/lib/mysql/mysql.sock

Time Id Command Argument

5. 如果需要，改变客户端的提示符和配置免密码登录：

[root@station95 mysql]# cat /etc/my.cnf

# For advice on how to change settings please see

# https://dev.mysql.com/doc/refman/9.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove the leading "# " to disable binary logging

# Binary logging captures changes between backups and is enabled by

# default. It's default setting is log_bin=binlog

# disable_log_bin

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

port=3309

general_log

slow_query_log

[client]

port=3309

prompt = \R:\m \d>\_

观察提示符的变化：

[root@station95 mysql]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 9.6.0 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

18:59 (none)>

配置免密码登录：

[root@station95 mysql]# mysql_config_editor print --all

[root@station95 mysql]# mysql_config_editor set --user=root --password

Enter password:

[ϛ7i+\c N2

%fA]i3gPxw;[root@station95 mysql]# mysql_config_editor print --all

[client]

user = "root"

password = *****

[root@station95 mysql]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 9.6.0 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

19:02 (none)>

[root@station95 mysql]# mysql_config_editor remove

WARNING : No login path specified, so options from the default login path will be removed.

Continue? (Press y|Y for Yes, any other key for No) : y

[root@station95 mysql]# mysql_config_editor print -all

mysql_config_editor: [ERROR] mysql_config_editor: unknown option '-a'.

[root@station95 mysql]# mysql_config_editor print --all

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议1下一篇：【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议3：认识各种MySQL数据库的日志

【博客文章2026】MySQL 9.x数据库管理技巧---给初学者的建议2

相关分类