mysql 自动提交不生效_MySQL的prompt不生效的问题

最新推荐文章于 2024-01-27 13:57:04 发布

此用户已诈尸

最新推荐文章于 2024-01-27 13:57:04 发布

阅读量253

点赞数

文章标签： mysql 自动提交不生效

本文链接：https://blog.csdn.net/weixin_34811747/article/details/113270670

版权

安装完MySQL之后，使用了自定义的配置文件来启动MySQL，发现配置在[mysql]中的prompt并没有生效

[root@MySQL56_L1 ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3376.sock

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

Your MySQL connectionid is 3Server version:5.6.35-log 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 clearthe current input statement.

mysql>

配置文件my3376.cnf的配置如下

[mysql]

no-auto-rehash

max_allowed_packet=128M

prompt= '(product)\u@\h [\d]>'default_character_set= utf8

使用print-defaults查看：

[root@MySQL56_L1 ~]# /usr/local/mysql/bin/mysql --print-defaults/usr/local/mysql/bin/mysql would have been started with the following arguments:

得到的结果没有输出prompt的信息

官方文档的描述如下：

You can set the prompt in several ways:

First，Use an environment variable. You can set the MYSQL_PS1 environment variable to a prompt string. For example:

shell> export MYSQL_PS1="(\u@\h) [\d]> "

Second，Use a command-line option. You can set the --prompt option on the command line to mysql. For example:

shell> mysql --prompt="(\u@\h) [\d]> "(user@host) [database]>

Third，Use an option file. You can set the prompt option in the [mysql] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For example:

[mysql]

prompt=(\\u@\\h) [\\d]>\\_

In this example, note that the backslashes are doubled. If you set the prompt using the prompt option in an option file, it is advisable to double the backslashes when using the special prompt options. There is some overlap in the set of permissible prompt options and the set of special escape sequences that are recognized in option files. (The rules for escape sequences in option files are listed in Section 4.2.6, “Using Option Files”.) The overlap may cause you problems if you use single backslashes. For example, \s is interpreted as a space rather than as the current seconds value. The following example shows how to define a prompt within an option file to include the current time in HH:MM:SS> format:

[mysql]

prompt="\\r:\\m:\\s> "

Fourth，Set the prompt interactively. You can change your prompt interactively by using the prompt (or \R) command. For example:

mysql> prompt (\u@\h) [\d]>\_

PROMPT set to '(\u@\h) [\d]>\_'(user@host) [database]>(user@host) [database]>prompt

Returning to default PROMPT of mysql>mysql>

根据官方文档提示的第三点，尝试着把prompt添加到/etc/my.cnf下

[root@MySQL56_L1 ~]# vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0[mysqld_safe]

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

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

[mysql]

prompt=\\u@\\h:\\p [\\d]>

测试登录，能得到预想的提示结果

[root@MySQL56_L1 ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3376.sock

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

Your MySQL connectionid is 2Server version:5.6.35-log 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 clearthe current input statement.

root@localhost:mysql3376.sock [(none)]>

使用print-defaults查看输出信息

[root@MySQL56_L1 ~]# /usr/local/mysql/bin/mysql --print-defaults/usr/local/mysql/bin/mysql would have been started with the following arguments:--prompt=\u@\h:\p [\d]>

考虑到官方文档中提示会读取到/etc/my.cnf和~/.my.cnf下的prompt，就容易联想到是不是mysql客户端就只能读取到默认路径下的[mysql]？

继续做以下尝试：

[root@MySQL56_L1 ~]# /usr/local/mysql/bin/mysql --verbose --help | grepmy.cnf

order of preference, my.cnf, $MYSQL_TCP_PORT,/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

注：按照顺序，每次做下一次测试之前都把前一个my.cnf中的[mysql]中的prompt注释掉

/etc/mysql/my.cnf

[root@MySQL56_L1 mysql]# vi /etc/mysql/my.cnf

[mysql]

prompt=\\u@\\h:\\p [\\d]>

-----------------------------------------------------------------------------------# 测试登录

[root@MySQL56_L1~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3376.sock

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

Your MySQL connectionid is 1Server version:5.6.35-log 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 clearthe current input statement.

root@localhost:mysql3376.sock [(none)]>

/usr/local/mysql/etc/my.cnf

[root@MySQL56_L1 mysql]# vi /usr/local/mysql/etc/my.cnf

[mysql]

prompt=\\u@\\h:\\p [\\d]>

------------------------------------------------------------------------------------# 测试结果

[root@MySQL56_L1~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3376.sock

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

Your MySQL connectionid is 3Server version:5.6.35-log 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 clearthe current input statement.

root@localhost:mysql3376.sock [(none)]>

~/.my.cnf

[root@MySQL56_L1 mysql]# vi ~/.my.cnf

[mysql]

prompt=\\u@\\h:\\p [\\d]>

------------------------------------------------------------------------------------[root@MySQL56_L1~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3376.sock

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

Your MySQL connectionid is 5Server version:5.6.35-log 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 clearthe current input statement.

root@localhost:mysql3376.sock [(none)]>

结论：要使prompt生效，必须是把prompt添加到mysql能读取到的默认的配置文件下的[mysql]下。

以上，如有错谬，请不吝指正。