倘使你对shell很熟,那么能够忽略下边步骤,假如不熟练,那么大家可以手工业调节和测试一下(#
sh -x mysqld),看看它是怎么着获得相关变量的值的呢?

MySQL服务读取参数文件my.cnf的法则商量研究,mysqlmy.cnf

在MySQL中,它是按什么顺序或原理去读取my.cnf配置文件的吧?其实只要您花一点素养,实验测试一下就会弄明白,上边的试验境况为伍.7.21
MySQL Community Server。其它版本如有不一致,请以实际景况为准。

 

实则,MySQL是安分守己上面那几个顺序来读取my.cnf:

 

    1: /etc/my.cnf

    2: /etc/mysql/my.cnf

    3: /usr/etc/my.cnf

    4: ~/.my.cnf

 

也正是说首先它会找/etc/my.cnf 那些文件,
假使那一个文件不存在,那么它接下去去找/etc/mysql/my.cnf那几个文件,就那样推算(那一个试验很轻易,在此略过,不浪费篇幅),借使最终3个文书~/.my.cnf
也不存在,那么会怎么样啊?

[[email protected] ~]# mysql --help | grep my.cnf

                      order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

[[email protected] ~]# ls /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

ls: cannot access /etc/mysql/my.cnf: No such file or directory

ls: cannot access /usr/etc/my.cnf: No such file or directory

ls: cannot access /root/.my.cnf: No such file or directory

/etc/my.cnf

[[email protected] ~]# 

 

如上所示,其实MySQL安装到位后,只生成了/etc/my.cnf这一个布局文件。其余路子的my.cnf文件是不存在的。大家先来测试一下,将安顿文件移走。在那从前,大家先查看一下log_error的职务。如下所示:

 

 

mysql> show variables like '%log_error%';

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

| Variable_name       | Value               |

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

| binlog_error_action | ABORT_SERVER        |

| log_error           | /var/log/mysqld.log |

| log_error_verbosity | 3                   |

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

3 rows in set (0.00 sec)

 

mysql> exit

Bye

 

 

[[email protected] ~]# mv /etc/my.cnf  /tmp/my.cnf

[[email protected] ~]# ls -lrt /etc/my.cnf

ls: cannot access /etc/my.cnf: No such file or directory

[[email protected] ~]# service mysqld stop

Stopping mysqld:  [  OK  ]

[[email protected] ~]# service mysqld start

Starting mysqld:  [  OK  ]

[[email protected] ~]# 

 

 

 

 

如上所示,就算没了my.cnf配置文件,MySQL服务仍是能够运转,那么这么些是怎么回事呢?
大家清楚service mysqld
start运转MySQL,其实是运维/etc/init.d/mysqld这些剧本。上边是本子获取给变量datadir、socketfile、errlogifle赋值的一些脚本,如下所示:

 

# Extract value of a MySQL option from config files

# Usage: get_mysql_option OPTION DEFAULT SECTION1 SECTION2 SECTIONN

# Result is returned in $result

# We use my_print_defaults which prints all options from multiple files,

# with the more specific ones later; hence take the last match.

get_mysql_option () {

    option=$1

    default=$2

    shift 2  #移动到第3个参数,详情见下面调试。

    result=$(/usr/bin/my_print_defaults "[email protected]" | sed -n "s/^--${option}=//p" | tail -n 1)

    if [ -z "$result" ]; then

        # not found, use default

        result="${default}"

    fi

}

 

get_mysql_option datadir "/var/lib/mysql" mysqld

datadir="$result"

get_mysql_option socket "$datadir/mysql.sock" mysqld

socketfile="$result"

get_mysql_option log-error "/var/log/mysqld.log" mysqld mysqld_safe

errlogfile="$result"

get_mysql_option pid-file "/var/run/mysqld/mysqld.pid" mysqld mysqld_safe

mypidfile="$result"

 

只要您对shell很熟,那么能够忽略下边步骤,假诺不熟悉,那么大家得以手工业调节和测试一下(#
sh -x mysqld),看看它是什么样赢得有关变量的值的呢?

 

 

 

 

[[email protected] ~]# file /usr/bin/my_print_defaults

/usr/bin/my_print_defaults: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

[[email protected] ~]# /usr/bin/my_print_defaults mysqld

[[email protected] ~]# 

 

 

如上所示,因为/usr/bin/my_print_defaults
mysqld输出为空,所以result为空值,
所以result被授予${default}的值,而defualt=$二,其实正是第二个变量,如下所示,第三个变量被标识为橄榄黑。

 

get_mysql_option datadir “/var/lib/mysql” mysqld

datadir=”$result”

 

 

 

除此以外,my.cnf的地点是会潜移默化脚本输出结果的。如下所示:(不在多少个暗许路线的话,my_print_defaults是未曾出口结果的)

 

[[email protected] ~]# ls /tmp/my.cnf

/tmp/my.cnf

[[email protected] ~]# /usr/bin/my_print_defaults mysqld

[[email protected] ~]# mv /tmp/my.cnf  /etc/my.cnf

[[email protected] ~]# /usr/bin/my_print_defaults mysqld

--datadir=/var/lib/mysql

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

--symbolic-links=0

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

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

 

 

 

 

 

接下去,大家将计划文件my.cnf挪回原来的地方(/etc/my.cnf),然后改成数据库数据存款和储蓄目录(从/var/lib/mysql挪动到/data/mysqldata/mysql
步骤省略),然后大家再做上面测试:

 

 

[[email protected] ~]# service mysqld stop

Stopping mysqld:  [  OK  ]

[[email protected] ~]# mv /etc/my.cnf  /tmp/my.cnf

[[email protected] ~]# service mysqld start

Initializing MySQL database:  2018-03-16T01:26:19.589182Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-03-16T01:26:20.034494Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-03-16T01:26:20.132219Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-03-16T01:26:20.193504Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 07ee8c42-28b9-11e8-a04a-005056b3ebdf.

2018-03-16T01:26:20.208662Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-03-16T01:26:20.209919Z 1 [Note] A temporary password is generated for [email protected]: O;kZmIj+.6jf

[  OK  ]

Logging to '/var/lib/mysql/gettestlnx02.err'.

Starting mysqld:  [  OK  ]

 

 

 

MySQL服务依旧也运维了,它早先化了数据库,数据文件位于/var/lib/mysql。那些的确让自个儿吃了壹惊,原本猜测,就算改换数据库数据存款和储蓄目录,MySQL又没有my.cnf配置文件,MySQL服务应该运转不了。当然这么些运维也从未什么样意义,因为你的数据和局地账号权限配置都尚未了(有一点点类似于SQL
Server里面包车型客车重建系统数据库)

 

[[email protected] mysql]# cd /var/lib/mysql

[[email protected] mysql]# ls -lrt

total 122948

-rw-r-----. 1 mysql mysql 50331648 Mar 16 09:26 ib_logfile1

-rw-r-----. 1 mysql mysql       56 Mar 16 09:26 auto.cnf

drwxr-x---. 2 mysql mysql     4096 Mar 16 09:26 performance_schema

drwxr-x---. 2 mysql mysql     4096 Mar 16 09:26 mysql

drwxr-x---. 2 mysql mysql    12288 Mar 16 09:26 sys

-rw-r-----. 1 mysql mysql      420 Mar 16 09:26 ib_buffer_pool

-rw-------. 1 mysql mysql     1679 Mar 16 09:26 ca-key.pem

-rw-r--r--. 1 mysql mysql     1107 Mar 16 09:26 ca.pem

-rw-------. 1 mysql mysql     1675 Mar 16 09:26 server-key.pem

-rw-r--r--. 1 mysql mysql     1107 Mar 16 09:26 server-cert.pem

-rw-------. 1 mysql mysql     1675 Mar 16 09:26 client-key.pem

-rw-r--r--. 1 mysql mysql     1107 Mar 16 09:26 client-cert.pem

-rw-------. 1 mysql mysql     1675 Mar 16 09:26 private_key.pem

-rw-r--r--. 1 mysql mysql      451 Mar 16 09:26 public_key.pem

-rw-------. 1 mysql mysql        6 Mar 16 09:26 mysql.sock.lock

srwxrwxrwx. 1 mysql mysql        0 Mar 16 09:26 mysql.sock

-rw-r-----. 1 mysql mysql 12582912 Mar 16 09:26 ibtmp1

-rw-r-----. 1 mysql mysql 12582912 Mar 16 09:26 ibdata1

-rw-r-----. 1 mysql mysql 50331648 Mar 16 09:26 ib_logfile0

-rw-r-----. 1 mysql mysql     3277 Mar 16 09:27 gettestlnx02.err

[[email protected] mysql]# 

 

测试、折腾过程意识并不是全数景况下都会中标开始化数据库,假如当/var/lib/mysql下存在部分文件时,起首化会报错“initialize
specified but the data directory has files in it.
Aborting.”此时,只要你清空

/var/lib/mysql下文件,就能够成功开头化。

 

[[email protected]
~]# service mysqld stop

[[email protected]
~]# mv /etc/my.cnf  /tmp/my.cnf

[[email protected]
~]# service mysqld start

Initializing MySQL database:  2018-03-16T03:49:45.190114Z 0 [Warning]
TIMESTAMP with implicit DEFAULT value is deprecated. Please use
–explicit_defaults_for_timestamp server option (see documentation
for more details).

2018-03-16T03:49:45.192215Z 0 [ERROR] –initialize specified but the
data directory has files in it. Aborting.

2018-03-16T03:49:45.192246Z 0 [ERROR] Aborting

 

[FAILED]

[[email protected]
~]#

 

 

 

实在假若稍微开支一点观念,查看一下/etc/init.d/mysqld的代码,就能意识start函数里面在规范满意的动静就能初步化数据库。

 

start(){

    [ -x $exec ] || exit 5

    # check to see if it's already running

    RESPONSE=$(/usr/bin/mysqladmin --no-defaults --socket="$adminsocket" --user=UNKNOWN_MYSQL_USER ping 2>&1)

    if [ $? = 0 ]; then

        # already running, do nothing

        action $"Starting $prog: " /bin/true

        ret=0

    elif echo "$RESPONSE" | grep -q "Access denied for user"

    then

        # already running, do nothing

        action $"Starting $prog: " /bin/true

        ret=0

    else

        # prepare for start

        if [ ! -e "$errlogfile" -a ! -h "$errlogfile" -a "x$(dirname "$errlogfile")" = "x/var/log" ]; then

            install /dev/null -m0640 -omysql -gmysql "$errlogfile"

        fi

        [ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"

        if [ ! -d "$datadir/mysql" ] ; then

            # First, make sure $datadir is there with correct permissions

            if [ ! -d "$datadir" -a ! -h "$datadir" -a "x$(dirname "$datadir")" = "x/var/lib" ]; then

                install -d -m0751 -omysql -gmysql "$datadir" || exit 1

            fi

            if [ ! -h "$datadir" -a "x$(dirname "$datadir")" = "x/var/lib" ]; then

                chown mysql:mysql "$datadir"

                chmod 0751 "$datadir"

            fi

            if [ -x /sbin/restorecon ]; then

                /sbin/restorecon "$datadir"

                for dir in /var/lib/mysql-files /var/lib/mysql-keyring ; do

                    if [ -x /usr/sbin/semanage -a -d /var/lib/mysql -a -d $dir ] ; then

                        /usr/sbin/semanage fcontext -a -e /var/lib/mysql $dir >/dev/null 2>&1

                        /sbin/restorecon -r $dir

                    fi

                done

            fi

            # Now create the database

            initfile="$(install_validate_password_sql_file)"

            action $"Initializing MySQL database: " /usr/sbin/mysqld --initialize --datadir="$datadir" --user=mysql --init-file="$initfile"

            ret=$?

            rm -f "$initfile"

            [ $ret -ne 0 ] && return $ret

            # Generate certs if needed

            if [ -x /usr/bin/mysql_ssl_rsa_setup -a ! -e "${datadir}/server-key.pem" ] ; then

                /usr/bin/mysql_ssl_rsa_setup --datadir="$datadir" --uid=mysql >/dev/null 2>&1

            fi

 

 

别的,在多实例景况下,多实例有三种方案:

 

1、基于mysqld_multi: 多少个实例共用同三个my.cnf配置文件中,利用[mysqld1]、[mysqld2]、[mysqld*]标签达成分化实例的差距化配置;

 

贰、基于多配备文件:每三个实例单独三个my.cnf配置文件

 

 

多实例运转时都会钦命相应的my.cnf,所以固然这里未有详细测试,其实大约的规律也跟单实例是差不多的。当您有疑点或不解的时候,入手施行是检察真理的唯壹规范。

 

 

 

 

在MySQL中,它是按怎么样顺序或原理去读取my.cnf配置文件的呢?其实假如你花一点素养,…

接下去,大家将配备文件my.cnf挪回原来的地点(/etc/my.cnf),然后改成数据库数据存储目录(从/var/lib/mysql挪动到/data/mysqldata/mysql
步骤省略),然后大家再做下边测试:

 

 

    1: /etc/my.cnf

 

 

 

 

MySQL服务依旧也运营了,它初阶化了数据库,数据文件位于/var/lib/mysql。那一个实在让本身吃了一惊,原本揣测,假诺改换数据库数据存款和储蓄目录,MySQL又从不my.cnf配置文件,MySQL服务应该运维不了。当然这么些运转也未尝什么含义,因为你的多寡和一些账号权限配置都没有了(有一些类似于SQL
Server里面包车型地铁重建系统数据库)

 

 

[FAILED]

二、基于多配备文件:每二个实例单独二个my.cnf配置文件

 

如上所示,尽管没了my.cnf配置文件,MySQL服务还能运营,那么这么些是怎么回事呢?
大家了解service mysqld
start运维MySQL,其实是运维/etc/init.d/mysqld那个剧本。上面是本子获取给变量datadir、socketfile、errlogifle赋值的一些脚本,如下所示:

 

 

Initializing
MySQL database: 
2018-03-16T03:49:45.190114Z 0 [Warning] TIMESTAMP with implicit
DEFAULT value is deprecated. Please use
–explicit_defaults_for_timestamp server option (see documentation
for more details).

 

 

 

图片 1

# Extract value of a MySQL option from config files

# Usage: get_mysql_option OPTION DEFAULT SECTION1 SECTION2 SECTIONN

# Result is returned in $result

# We use my_print_defaults which prints all options from multiple files,

# with the more specific ones later; hence take the last match.

get_mysql_option () {

    option=$1

    default=$2

    shift 2  #移动到第3个参数,详情见下面调试。

    result=$(/usr/bin/my_print_defaults "$@" | sed -n "s/^--${option}=//p" | tail -n 1)

    if [ -z "$result" ]; then

        # not found, use default

        result="${default}"

    fi

}

 

get_mysql_option datadir "/var/lib/mysql" mysqld

datadir="$result"

get_mysql_option socket "$datadir/mysql.sock" mysqld

socketfile="$result"

get_mysql_option log-error "/var/log/mysqld.log" mysqld mysqld_safe

errlogfile="$result"

get_mysql_option pid-file "/var/run/mysqld/mysqld.pid" mysqld mysqld_safe

mypidfile="$result"

1、基于mysqld_multi: 三个实例共用同三个my.cnf配置文件中,利用[mysqld1]、[mysqld2]、[mysqld*]标签完成差异实例的差距化配置;

 

[root@gettestlnx02
~]#

 

也正是说首先它会找/etc/my.cnf
那个文件,
假若这几个文件不设有,那么它接下去去找/etc/mysql/my.cnf那一个文件,依此类推(这些试验很轻巧,在此略过,不浪费篇幅),假使最后1个文本~/.my.cnf
也不存在,那么会怎么着啊?

[root@gettestlnx02 ~]# file /usr/bin/my_print_defaults

/usr/bin/my_print_defaults: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

[root@gettestlnx02 ~]# /usr/bin/my_print_defaults mysqld

[root@gettestlnx02 ~]# 

    2:
/etc/mysql/my.cnf

2018-03-16T03:49:45.192215Z
0 [ERROR] –initialize specified but the data directory has files in
it. Aborting.

 

 

 

实际,MySQL是根据下边那个顺序来读取my.cnf:

如上所示,因为/usr/bin/my_print_defaults
mysqld输出为空,所以result为空值,
所以result被授予${default}的值,而defualt=$2,其实便是第3个变量,如下所示,第三个变量被标志为革命。

图片 2

 

2018-03-16T03:49:45.192246Z
0 [ERROR] Aborting

 

 

 

 

[root@gettestlnx02
~]# service mysqld stop

图片 3

get_mysql_option
datadir “/var/lib/mysql”
mysqld

 

事实上只要稍微开支一点主见,查看一下/etc/init.d/mysqld的代码,就能够意识start函数里面在规范满意的情况就能够开始化数据库。

[root@gettestlnx02 ~]# mysql --help | grep my.cnf

                      order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

[root@gettestlnx02 ~]# ls /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

ls: cannot access /etc/mysql/my.cnf: No such file or directory

ls: cannot access /usr/etc/my.cnf: No such file or directory

ls: cannot access /root/.my.cnf: No such file or directory

/etc/my.cnf

[root@gettestlnx02 ~]#