正文

MySQL安装及初步配置.md

IP云
IP云V管理员/4 阅读
11/29
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

MySQL

安装脚本 
#!/bin/bash

MYSQL_BASEDIR=/usr/local/mysql

MySQL_DATADIR=/data/mysql

SERVER_ID=`hostname -I |cut -d'.' -f4`

cat >/etc/my.cnf<<EOF

[mysqld]

datadir=/data/mysql

port=3306

socket=/tmp/mysql.sock

pid_file=/data/mysql/mysql.pid

log_error=error.log

user=mysql

skip-name-resolve

log-bin=mysql-bin

log-bin-index=mysql-bin.index

server-id=${SERVER_ID}

character_set_server=utf8

log-slave-updates=1

[mysql]

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

[client]

user=root

password=

EOF

COUNT=`ls . |grep mysql-.*-linux-glibc2.5-x86_64.tar.gz |wc -l`

if [ $COUNT -ne 1 ];then

        echo "MySQL install tar file must equal one.This is directory equal $COUNT."

        exit 100

else

        MYSQL_VERSION=`ls . |grep mysql-.*-linux-glibc2.5-x86_64.tar.gz|awk -F'-' '{print $2}'`

fi

MYSQL_FILE_NAME=mysql-${MYSQL_VERSION}-linux-glibc2.5-x86_64.tar.gz

function mysql_install () {

    if [[ `rpm -qa libaio |wc -l` -ne 1 ]]; then

        yum install libaio || echo "install libaio error."

        exit

    fi

    id mysql || groupadd -r mysql

    id mysql || useradd -r -g mysql -s /sbin/nologin -M mysql

    if [ ! -d /usr/local/mysql-${MYSQL_VERSION}-linux-glibc2.5-x86_64 ];then

        tar xf ${MYSQL_FILE_NAME} -C /usr/local/ && echo "mysql unzip ok."

    fi

    if [ -L /usr/local/mysql ];then

        unlink /usr/local/mysql

    fi

    ln -sv /usr/local/mysql-${MYSQL_VERSION}-linux-glibc2.5-x86_64 /usr/local/mysql

    echo "export PATH=$PATH:/usr/local/mysql/bin" >/etc/profile.d/mysql.sh

    source /etc/profile.d/mysql.sh

    /bin/cp ${MYSQL_BASEDIR}/support-files/mysql.server /etc/init.d/mysqld

    mkdir -p ${MySQL_DATADIR}

    chown -R mysql.mysql ${MySQL_DATADIR}

}

MYSQL_VERSION_2=`ls . |grep mysql-.*-linux-glibc2.5-x86_64.tar.gz|awk -F'-' '{print $2}' |cut -d'.' -f1-2`

case $MYSQL_VERSION_2 in

    5.7 )

    mysql_install && mysqld --initialize --user=mysql

    MYSQL_PASSWORD=`grep "root@localhost:" /data/mysql/error.log |awk '{print $NF}'`

    sed -i s/password=/password=$MYSQL_PASSWORD/ /etc/my.cnf

        ;;

    * )

    mysql_install && /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=${MYSQL_BASEDIR}

        ;;

esac

source /etc/profile.d/mysql.sh

/etc/init.d/mysqld start && echo 'Please execute command "source /etc/profile.d/mysql.sh"'

配置文件

MySQL启动是默认需找配置文件顺序为： /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf ，如果相同的参数多次配置则后面的配置会覆盖前面的配置。下面是最基本的mysql的配置。 

[mysqld]

datadir=/data/mysql

port=3306

socket=/tmp/mysql.sock

log_error=error.log

user=mysql

skip-name-resolve

default_password_lifetime=0

[client]

user = root

password = redhat

[mysql]

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

注意
1.我们在安装mysql时有时会将mysql安装在非/usr/local/目录中，为了避免出现不必要的错误，最好在mysqld标签中配置basedir选项。
2.同时我们还可以通过[mysql-5.6]这种标签来定义根据不同版本启动时所需要的启动参数。
3.未避免出现设置的帐号密码过期最好还是定义default_password_lifetime选项来将密码设置成永不过期。

会话变量

查看全局变量 

(root@localhost) [(none)]> show global variables\G

查看会话变量 

(root@localhost) [(none)]> show variables\G

注意 ：我们在MySQL客户端设置参数时默认是当前会话生效，如果在新启用一个会话则不会生效。若想让新启用的会话生效则要使用global参数进行设置全局变量，但是global全局变量这种设置方式并不会在当前会话生效，而是在新开启的会话生效。 

(root@localhost) [(none)]> set long_query_time = 5;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> show variables like 'long_query_time';

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

| Variable_name   | Value    |

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

| long_query_time | 5.000000 |

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

1 row in set (0.00 sec)

(root@localhost) [(none)]> set global long_query_time = 3;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> show variables like 'long_query_time';

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

| Variable_name   | Value    |

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

| long_query_time | 5.000000 |

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

1 row in set (0.00 sec)

所有会话变量

下面是查看当前所有会话连接的中long_query_time变量的信息。 

(root@localhost) [performance_schema]> select * from variables_by_thread where variable_name='long_query_time';

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

| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE |

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

|        28 | long_query_time | 10.000000      |

|        29 | long_query_time | 5.000000       |

|        30 | long_query_time | 3.000000       |

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

3 rows in set (0.00 sec)

查看当前MySQL的会话连接信息。 

(root@localhost) [performance_schema]> show processlist;

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

| Id | User | Host      | db                 | Command | Time | State    | Info             |

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

|  3 | root | localhost | performance_schema | Sleep   |   67 |          | NULL             |

|  4 | root | localhost | performance_schema | Query   |    0 | starting | show processlist |

|  5 | root | localhost | NULL               | Sleep   |  413 |          | NULL             |

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

3 rows in set (0.00 sec)

上面两个查询中会发现Id 和 THREAD_ID无法一一对应，如果想查看两者的详细信息则需要通过下面的方式进行查询： 

(root@localhost) [performance_schema]> select * from threads where thread_id = 29 limit 1\G

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

          THREAD_ID: 29

               NAME: thread/sql/one_connection

               TYPE: FOREGROUND

     PROCESSLIST_ID: 4

   PROCESSLIST_USER: root

   PROCESSLIST_HOST: localhost

     PROCESSLIST_DB: performance_schema

PROCESSLIST_COMMAND: Query

   PROCESSLIST_TIME: 0

  PROCESSLIST_STATE: Sending data

   PROCESSLIST_INFO: select * from threads where thread_id = 29 limit 1

   PARENT_THREAD_ID: 1

               ROLE: NULL

       INSTRUMENTED: YES

            HISTORY: YES

    CONNECTION_TYPE: Socket

       THREAD_OS_ID: 2310

1 row in set (0.00 sec)

通过上面的查询我们就可找出PROCESSLIST_ID和THREAD_ID的对应关系。

权限

创建用户 

(root@localhost) [(none)]> create user 'redhat'@'192.168.200.%' identified by 'redhat';

Query OK, 0 rows affected (0.00 sec)

删除用户 

(root@localhost) [(none)]> drop user 'redhat'@'192.168.200.%';

Query OK, 0 rows affected (0.00 sec)

查看权限 

(root@localhost) [(none)]> show grants;

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

| Grants for root@localhost                                           |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |

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

2 rows in set (0.00 sec)

(root@localhost) [(none)]> show grants for 'redhat'@'192.168.%.%';

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

| Grants for redhat@192.168.%.%                |

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

| GRANT USAGE ON *.* TO 'redhat'@'192.168.%.%' |

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

1 row in set (0.00 sec)

赋予权限 

(root@localhost) [(none)]> grant select,update,insert,delete on test.* to 'redhat'@'192.168.%.%';

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> grant select,update,insert,delete on test.* to 'redhat'@'192.168.%.%' with grant option;

Query OK, 0 rows affected (0.00 sec)

修改密码 

(root@localhost) [(none)]> alter user 'redhat'@'192.168.%.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

删除权限 

(root@localhost) [(none)]> grant create,index on test.* to 'redhat'@'192.168.%.%';

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> show grants for 'redhat'@'192.168.%.%';

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

| Grants for redhat@192.168.%.%                                                             |

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

| GRANT USAGE ON *.* TO 'redhat'@'192.168.%.%'                                              |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON `test`.* TO 'redhat'@'192.168.%.%' |

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

2 rows in set (0.00 sec)

(root@localhost) [(none)]> revoke create,index on test.* from 'redhat'@'192.168.%.%';

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> show grants for 'redhat'@'192.168.%.%';

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

| Grants for redhat@192.168.%.%                                              |

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

| GRANT USAGE ON *.* TO 'redhat'@'192.168.%.%'                               |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'redhat'@'192.168.%.%' |

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

2 rows in set (0.00 sec)

(root@localhost) [(none)]> revoke all on test.* from 'redhat'@'192.168.%.%';

Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> show grants for 'redhat'@'192.168.%.%';

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

| Grants for redhat@192.168.%.%                |

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

| GRANT USAGE ON *.* TO 'redhat'@'192.168.%.%' |

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

1 row in set (0.00 sec)

MySQL赋予权限时是将用户的权限根据赋予权限命令按规则写入：mysql.user,mysql.db,mysql.tables_priv,mysql.columns_priv四个表中。

限制用户连接数 

(root@localhost) [mysql]> alter user 'redhat'@'192.168.%.%' with max_user_connections 1;

Query OK, 0 rows affected (0.00 sec)

如果次数超过设置的现在则会报如下的错误： 

# mysql -u redhat -h192.168.200.21 -p

Enter password:

ERROR 1226 (42000): User 'redhat' has exceeded the 'max_user_connections' resource (current value: 1)

在设置时候最好还是改回默认不限制，不然会影响下面的实验。

官方文档：
https://dev.mysql.com/doc/refman/5.x/en/privileges-provided.html

限制登录密码

在日常使用中我们会要求登录数据库帐号的密码复杂度，这就可以使用validate_password.so插件进行限制。这个插件的使用可以在线安装，也可以写在配置文件中从而重新启动数据库。

在线安装 

(root@192.168.200.21) [(none)]> install plugin validate_password soname 'validate_password.so';

Query OK, 0 rows affected (0.03 sec)

配置文件 

[mysqld]

plugin-load=validate_password.so

下面是配置参数： 

(root@192.168.200.21) [(none)]> show variables like 'validate%';

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

| Variable_name                        | Value  |

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

| validate_password_check_user_name    | OFF    |

| validate_password_dictionary_file    |        |

| validate_password_length             | 8      |

| validate_password_mixed_case_count   | 1      |

| validate_password_number_count       | 1      |

| validate_password_policy             | MEDIUM |

| validate_password_special_char_count | 1      |

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

7 rows in set (0.01 sec)

安装这个插件之后在将密码设置的不符合要求就会报错： 

(root@192.168.200.21) [(none)]> alter user 'redhat'@'192.168.%.%' identified by 'redhat';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

(root@192.168.200.21) [(none)]> alter user 'redhat'@'192.168.%.%' identified by 'MmmAaaa123_';

Query OK, 0 rows affected (0.00 sec)

相关阅读

目录[+]