mysql 给表里的某个字段建立索引

1.添加PRIMARY KEY(主键索引):

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
'table_name' 表名
column 字段

将table_name表的column字段设置为主键

2.添加UNIQUE(唯一索引) :

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引) :

写法一:ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
写法二:CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`) 

4.添加FULLTEXT(全文索引) :

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加多列索引(联合索引):

写法一:ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

写法二:CREATE INDEX index_name ON `table_name`(`column1`,`column2`,`column3`)

6. 查询表索引

SHOW INDEX FROM `table_name`;

7. 删除索引

DROP INDEX index_name ON `table_name`;

MySQL查看与修改当前数据库编码的方法(character_set_database)

MySQL中,数据库的编码是一个相当重要的问题,有时候我们需要查看一下当前数据库的编码,甚至需要修改一下数据库编码。

查看当前数据库编码的SQL语句为:

mysql> use xxx
Database changed
mysql> show variables like ‘character_set_database’;
+————————+——–+
| Variable_name          | Value  |
+————————+——–+
| character_set_database | latin1 |
+————————+——–+
1 row in set (0.00 sec)

上面,我们先切换到xxx数据库下面来,然后使用SQL语句:show variables like ‘character_set_database’; 来查看了xxx数据库的编码。查询得到的结果是latin1编码。

下面,我们来修改xxx数据库的编码,把它修改为gb2312。

mysql> alter database xxx CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show variables like ‘character_set_database’;
+————————+——–+
| Variable_name          | Value  |
+————————+——–+
| character_set_database | utf8mb4 |
+————————+——–+
1 row in set (0.00 sec)

这里同样做了两件事情:
1、使用SQL语句:alter database xxx CHARACTER SET gb2312; 把xxx数据库的编码设置为了gb2312.
2、再次使用show variables like ‘character_set_database’; 来确认一下当前xxx是什么编码。经过确认,数据库编码已经修改为gb2312了。

3、当然很多情况下都是修改为utf-8编码

mysql5.7密码策略及修改技巧

mysql升级5.7版本以后,安全性大幅度上升。

MySQL5.7为root用户随机生成了一个密码,打印在error_log中,关于error_log的位置,如果安装的是RPM包,则默认是 /var/log/mysqld.log 。
于是我们可以在mysqld.log中找到初始密码串:

cat /var/log/mysqld.log | grep password
img_0ffa963efd7f80a9ab532804aa363700.jpe

 

用初始密码串登陆即可:

mysql -u root -p's;*QJ6/gX1et'

但是你会登陆之后会发现很多功能都不能用,只有修改密码才能进行正常操作:

 

img_586a24e0cf317e3675e6a708440e95c6.jpe

于是我们修改密码:

  1. mysql> SET PASSWORD = PASSWORD(‘123456’);
  2. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
  3. mysql> SET PASSWORD = PASSWORD(“root”);
  4. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

却又坑爹地说密码不满足策略要求。。。
那我们先看看数据库当前的密码策略是啥

  1. mysql root@localhost:(none)> show VARIABLES like “%password%”
  2. +—————————————+———+
  3. | Variable_name | Value |
  4. |—————————————+———|
  5. | default_password_lifetime | 0 |
  6. | disconnect_on_expired_password | ON |
  7. | log_builtin_as_identified_by_password | OFF |
  8. | mysql_native_password_proxy_users | OFF |
  9. | old_passwords | 0 |
  10. | report_password | |
  11. | sha256_password_proxy_users | OFF |
  12. | validate_password_dictionary_file | |
  13. | validate_password_length | 8 |
  14. | validate_password_mixed_case_count | 1 |
  15. | validate_password_number_count | 1 |
  16. | validate_password_policy | MEDIUM |
  17. | validate_password_special_char_count | 1 |
  18. +—————————————+———+

根据官方文档可以发现策略为1,即MEDIUM

 

img_041de6ede047d33a0e1db1b7a81c3816.jpe

所以你更改密码必须满足:数字、小写字母、大写字母 、特殊字符、长度至少8位

  1. ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘new_password’;
  2. 或者
  3. SET password=‘new_password’;

如此你便成功修改了密码,最后你还要设置一下过期时间,以防止密码失效。
/etc/my.cnf配置文件中增加

  1. [mysqld]
  2. default_password_lifetime=0

或者直接通过命令设置

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER

以上便可以开心的mysql的世界里遨游了~~~

番外篇

但是有时候为了图方便,数据的安全性要求也不是很高的情况下,我们通常更希望设置一个简单的密码,比如123456

法一:

/etc/my.cnf配置文件中增加

  1. [mysqld]
  2. validate_password=off

然后service mysqld restart或者systemctl restart mysqld.service重启mysqld,通过SHOW PLUGINS;即可看到密码验证的插件被停止了。

  1. +——————-+———-+——————-+———————-+—–+
  2. | validate_password | DISABLED | VALIDATE PASSWORD | validate_password.so | GPL |
  3. +——————-+———-+——————-+———————-+—–+
法二:

更改密码策略为LOW

set global validate_password_policy=0;

更改密码长度

set global validate_password_length=0;

如此即可随意设置密码:

  1. update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;
  2. 或者
  3. set password for ‘root’@‘localhost’=password(‘123456’);
当然,我们还有一种办法,就是在最最开始的时候,不设置初始密码:

只需要在初始化时指定--initialize-insecure即可,比如:

mysqld --initialize-insecure --datadir=/var/lib/mysql --basedir=/usr --user=mysql

此时,SHOW VARIABLES LIKE ‘vali%’;也会为空,因为该插件没有被安装。

Mysql 5.6 与5.7 密码权限问题

在 5.6 和 5.7 中,Mysql 加强了密码的使用。

  • Mysql第一次启动的时候,会初始化一个随机的复杂密码,保存在 /var/log/mysqld.log
  • 不再接受简单密码。即复杂密码为: 大小写、数字、符号 的组合。
  • 在命令行中,不能直接使用 mysql -u$USER -p$PASSWORD 的方式了

在 bash script 中使用 mysql

如何在 bash script 中使用 mysql 密码 – stackoverflow.com 讨论

使用 client 配置

/etc/my.cnf 中配置 [client] 区块

或者,使用 --default-extra-file=/path/to/config.cnf

mysql --defaults-extra-file=/path/to/config.cnf -e "statement;"
mysqldump --defaults-extra-file=/path/to/config.cnf -e "statement;"


# config.cnf 格式如下
[client]
user = whatever
password = whatever
host = whatever

不过 --default-extra-file=/path/to/config.cnf 必须为命令行的第一个参数,否则会报错。例如,mysqldump: unknown variable 'defaults-extra-file

使用 mysql_config_editor 和 login-path

在 5.6.x 中,避免 WARNING 消息的方式是使用 mysql_confg_editor 工具。

# 首先在使用 mysql_config_editor 设置一个帐号别名,
# 这样密码会被加密保存在 home/myshellusername/.mylogin.cnf
mysql_config_editor set --login-path=local --host=localhost --user=username --password

# 使用如下命令 
mysql --login-path=local  -e "statement"
mysqldump --login-path=local my_database | gzip > db_backup.tar.gz

# 而不再使用
# mysql -u username -p pass -e "statement"
mysqldump -u db_user -pInsecurePassword my_database | gzip > db_backup.tar.gz

前置 MYSQL_PWD

设置 MYSQL_PWD 为环境变量,则命令行的时候,不用在指定密码

export MYSQL_PWD=xxxxxxxx
mysql -u root -e "statement;"

另外,在不 export MYSQL_PWD 的情况下,可以将 MYSQL_PWD 放在命令行最前面,也是可行的。

MYSQL_PWD=xxxxx mysql -uroot -e"statement;"

修改初始密码

之前提到了, Mysql 在初次启动的时候会生成一个随机密码,保存在 /var/log/mysqld.log 中。 首次进入后,不修改 root 密码的话,所有操作都会被阻挡。

修改密码

进入 Mysql 后,可以使用 UPDATE 命令修改用户的密码

-- 注意:5.7 中 password 列已经修改为 authentication_string 了
update mysql.user set authentication_string=PASSWORD('Y0urP@assword');

官方更建议使用 ALERT USER

ALTER USER 'root'@'localhost' IDENTIFIED BY '${MYSQL_ROOT_PWD}' ;

命令行修改注意事项

  • 使用随机密码时,ALERT 授权主机必须为 localhost,即使 127.0.0.1 也不行。
  • 使用随机密码时,在命令行中必须使用 --connect-expired-password 参数

如下:

MYSQL57_ROOT_TMP_PWD=$(grep "A temporary password" /var/log/mysqld.log |awk '{print $NF}')
MYSQL_PWD=${MYSQL57_ROOT_TMP_PWD} mysql -u root --connect-expired-password -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '${MYSQL_ROOT_PWD}' ;"

Mysql 5.7 密码插件 validate_password

复制来源

这个 validate_password 密码强度审计插件决定了你设置的密码是否“过于简单”。

mysql> SHOW VARIABLES LIKE 'vali%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| 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      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

MYSQL 5.7初始化后,默认会安装这个插件,若没有安装,则SHOW VARIABLES LIKE ‘vali%’则会返回空。 对应参数的value值也为默认值,以下是这些值的解释

  • validate_password_length 8 # 密码的最小长度,此处为8。
  • validate_password_mixed_case_count 1 # 至少要包含小写或大写字母的个数,此处为 1。
  • validate_password_number_count 1 # 至少要包含的数字的个数,此处为 1。
  • validate_password_policy MEDIUM # 强度等级,其中其值可设置为 0、1、2。分别对应:
    • 【0/LOW】:只检查长度。
    • 【1/MEDIUM】:在0等级的基础上多检查数字、大小写、特殊字符。
    • 【2/STRONG】:在1等级的基础上多检查特殊字符字典文件,此处为1。
  • validate_password_special_char_count 1 # 至少要包含的个数字符的个数,此处为 1。

MySQL sql_mode 说明(及处理一起 sql_mode 引发的问题)

1. MySQL 莫名变成了 Strict SQL Mode

最近测试组那边反应数据库部分写入失败,app层提示是插入成功,但表里面里面没有产生数据,而两个写入操作的另外一个表有数据。因为 insert 失败在数据库层面是看不出来的,于是找php的同事看下错误信息:

[Err1364 - Field `f_company_id` doesn't have a default value

很明显2个 insert 操作,第一条成功,第二条失败了,但因为没有控制在一个事务当中,导致app里面依然提示成功,这是客户入库操作,心想如果线上也有这个问题得是多大的代价。

不说开发的问题,好端端的mysql怎么突然就部分表写入失败呢?根据上面的问题很快能猜到是 sql_mode 问题: NOT NULL 列没有默认值但代码里也没给值,在非严格模式下,int列默认为0,string列默认为”了,所以不成问题;但在严格模式下,是直接返回失败的。

一看,果然:

mysql> show variables like "sql_mode";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

但是一直是没问题的的,就突然出现了,有谁会去改 sql_mode 呢,生产环境产生这个问题的风险有多大?所以必须揪出来。

先 set global sql_mode='' ,让他们用着先(文后会给解决问题根本的办法),同时打开general_log看是哪一个用户有类似设置 sql_mode 命令:

1134456 Query   SET autocommit=1
1134456 Query   Set sql_mode='NO_ENGINE_SUBSITUTION,STRICT_TRANS_TABLES'
1134457 Connect ecuser@10.0.200.173 on
1134457 Query   /* mysql-connector-java-5.1.35 ...

看出是java组那边哪个框架建立连接的时候使用设置了sql_mode,但这是session级别的,不影响php那边用户的连接。

<!– more –>

那会是什么原因在 set global 之后又变回strict模式呢,于是想到 mysqld_safe 启动实际是一个保护进程,在mysqld异常停止之后会拉起来,会不会中间有异常导致 mysqld 重启,致使 global 失效?看了mysql错误日志,才想到前些天断过电,所以决定直接改 /etc/my.cnf配置:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

重启myqld之后,还是STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,很少遇到my.cnf里面配置不生效的情况。无独有偶,在 stackoverflow上找到同样的问题 how-to-make-sql-mode-no-engine-substitution-permanent-in-mysql-my-cnf ,原因很简单,sql_mode这个选项被其它地方的配置覆盖了。

了解一下mysql配置文件的加载顺序:

$ mysqld --help --verbose|grep -A1 -B1 cnf
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

mysql按照上面的顺序加载配置文件,后面的配置项会覆盖前面的。最后终于在 /usr/my.cnf 找到有一条sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,把这个文件删掉,/etc/my.cnf 里面的就生效了。

但是目前没能整明白的是,mysql运行这么长时间怎么突然在/usr (MYSQL_BASE)下多个my.cnf,也不像人为创建的。其它实例也没这样的问题。

类似还出现过一例:存储过程里把 ” 传给int型的,严格模式是不允许,而非严格模式只是一个warning。(命令行执行完语句后,show warnings 可看见)

那么解决这类问题的终极(推荐)办法其实是,考虑到数据的兼容性和准确性,MySQL就应该运行在严格模式下!无论开发环境还是生产环境,否则代码移植到线上可能产生隐藏的问题。

sql_mode 问题可以很简单,也可以很复杂。曾经在一个交流群里看到有人提到,主从sql_mode设置不一致导致复制异常,这里自己正好全面了解一下几个常用的值,方便以后排除问题多个方向。

2. sql_mode 常用值说明

官方手册专门有一节介绍 https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html 。 SQL Mode 定义了两个方面:MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查。

1. SQL语法支持类

  • ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作,如果在SELECT中的列、HAVING或者ORDER BY子句的列,没有在GROUP BY中出现,那么这个SQL是不合法的。是可以理解的,因为不在 group by 的列查出来展示会有矛盾。
    在5.7中默认启用,所以在实施5.6升级到5.7的过程需要注意:

     Expression #1 of SELECT list is not in GROUP BY
    clause and contains nonaggregated column
    '1066export.ebay_order_items.TransactionID' which
    is not functionally dependent on columns in GROUP BY
    clause; this is incompatible with sql_mode=only_full_group_by
  • ANSI_QUOTES 启用 ANSI_QUOTES 后,不能用双引号来引用字符串,因为它被解释为识别符,作用与 ` 一样。设置它以后,update t set f1="" ...,会报 Unknown column ” in ‘field list 这样的语法错误。
  • PIPES_AS_CONCAT 将 || 视为字符串的连接操作符而非 或 运算符,这和Oracle数据库是一样的,也和字符串的拼接函数 CONCAT() 相类似。
  • NO_TABLE_OPTIONS 使用 SHOW CREATE TABLE 时不会输出MySQL特有的语法部分,如 ENGINE ,这个在使用 mysqldump 跨DB种类迁移的时候需要考虑。
  • NO_AUTO_CREATE_USER 字面意思不自动创建用户。在给MySQL用户授权时,我们习惯使用 GRANT ... ON ... TO dbuser 顺道一起创建用户。设置该选项后就与oracle操作类似,授权之前必须先建立用户。5.7.7开始也默认了。

2. 数据检查类

  • NO_ZERO_DATE 认为日期 ‘0000-00-00’ 非法,与是否设置后面的严格模式有关。
    1. 如果设置了严格模式,则 NO_ZERO_DATE 自然满足。但如果是 INSERT IGNORE 或 UPDATE IGNORE,’0000-00-00’依然允许且只显示warning
    2. 如果在非严格模式下,设置了NO_ZERO_DATE,效果与上面一样,’0000-00-00’允许但显示warning;如果没有设置NO_ZERO_DATE,no warning,当做完全合法的值。
    3. NO_ZERO_IN_DATE情况与上面类似,不同的是控制日期和天,是否可为 0 ,即 2010-01-00 是否合法。
  • NO_ENGINE_SUBSTITUTION 使用 ALTER TABLECREATE TABLE 指定 ENGINE 时, 需要的存储引擎被禁用或未编译,该如何处理。启用NO_ENGINE_SUBSTITUTION时,那么直接抛出错误;不设置此值时,CREATE用默认的存储引擎替代,ATLER不进行更改,并抛出一个 warning .
  • STRICT_TRANS_TABLES 设置它,表示启用严格模式。
    注意 STRICT_TRANS_TABLES 不是几种策略的组合,单独指 INSERTUPDATE出现少值或无效值该如何处理:

    1. 前面提到的把 ” 传给int,严格模式下非法,若启用非严格模式则变成0,产生一个warning
    2. Out Of Range,变成插入最大边界值
    3. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

上面并没有囊括所有的 SQL Mode,选了几个代表性的,详细还是 看手册

sql_mode一般来说很少去关注它,没有遇到实际问题之前不会去启停上面的条目。我们常设置的 sql_mode 是 ANSISTRICT_TRANS_TABLESTRADITIONAL,ansi和traditional是上面的几种组合。

  • ANSI:更改语法和行为,使其更符合标准SQL
    相当于REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE
  • TRADITIONAL:更像传统SQL数据库系统,该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。
    相当于 STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
  • ORACLE:相当于 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

无论何种mode,产生error之后就意味着单条sql执行失败,对于支持事务的表,则导致当前事务回滚;但如果没有放在事务中执行,或者不支持事务的存储引擎表,则可能导致数据不一致。MySQL认为,相比直接报错终止,数据不一致问题更严重。于是 STRICT_TRANS_TABLES 对非事务表依然尽可能的让写入继续,比如给个”最合理”的默认值或截断。而对于 STRICT_ALL_TABLES,如果是单条更新,则不影响,但如果更新的是多条,第一条成功,后面失败则会出现部分更新。

5.6.6 以后版本默认就是NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,5.5默认为 ” 。

3. 设置 sql_mode

查看

查看当前连接会话的sql模式:

mysql> select @@session.sql_mode;
或者从环境变量里取
mysql> show variables like "sql_mode";


查看全局sql_mode设置:
mysql> select @@global.sql_mode;

只设置global,需要重新连接进来才会生效

设置

形式如
mysql> set sql_mode='';
mysql> set global sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';


如果是自定义的模式组合,可以像下面这样

Adding only one mode to sql_mode without removing existing ones:
mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));

Removing only a specific mode from sql_mode without removing others:
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));

配置文件里面设置 sql-mode=""

MySQL 5.7 新特性之初始化

1. 把二进制安装包下载放在/opt 目录下并解压

2. 创建软连接, 并添加运行环境

ln -s /usr/local/mysql /opt/mysql-5.7.18-linux-glibc2.5-x86_64

[root@M1 local]# ll | grep mysql
lrwxrwxrwx 1 root root 39 Jul 26 12:00 mysql -> /opt/mysql-5.7.18-linux-glibc2.5-x86_64

export PATH=$PATH:$HOME/bin:/usr/local/mysql/bin 加到环境变量

3. 创建mysql 用户

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

4. 创建目录结构和赋权

mkdir /data/mysql/3306/ -p

cd data

mkdir {data,logs,tmp}

chown mysql:mysql -R /data

5.生产cnf 配置文件

推荐使用叶老师的在线cnf 文件生产工具 http://imysql.com/my-cnf-wizard.html

6. 初始化

[root@M1 bin]# ./mysqld –initialize –datadir=/data/mysql/3306/data –user=mysql –basedir=/usr/local/mysql/
2017-07-26T08:24:03.273745Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2017-07-26T08:24:04.102255Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-07-26T08:24:04.243150Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-07-26T08:24:04.314543Z 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: c9102823-71db-11e7-ba5c-005056b643b3.
2017-07-26T08:24:04.321167Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2017-07-26T08:24:04.322012Z 1 [Note] A temporary password is generated for root@localhost: 2hpi85k*T-t=

我们可以看到root@localhost 被赋予随机生成的一个密码,这与以往的mysql版本不一样, 另外mysql_install_db 这个工具也不再被推荐了, mysql_install_db is deprecated. Please consider switching to mysqld –initialize

另外,在初始化时如果加上 –initial-insecure,则会创建空密码的 root@localhost 账号,否则会创建带密码的 root@localhost 账号,密码直接写在 log-error 日志文件中(在5.6版本中是放在 ~/.mysql_secret 文件里,更加隐蔽,不熟悉的话可能会无所适从)

7.启动数据库

8. 使用刚刚生成的密码就可以正常登录了

[root@M1 bin]# mysql -uroot -p -S /data/mysql3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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>

9. 想要更加美观? 也是可以的

在~/.bash_profile 加上export MYSQL_PS1=”\\u@\\h:\\p [\\d]>”

或者可以在[mysql] 添加

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

root@localhost:mysql3306.sock [(none)]>show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.00 sec)

root@localhost:mysql3306.sock [(none)]>use sys
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
root@localhost:mysql3306.sock [sys]>

 

 

==========最后总结======

快速初始化脚本如下:

1. 把压缩文件放在/opt 下面

2. my.cnf 文件的内容需要和以下文件目录匹配

#!/bin/sh
cd /opt
tar zxvf mysql*.tar.gz

ln -s /usr/local/mysql /opt/mysql*linux-glibc2.5-x86_64
cat >> /etc/profile <<EOF
export MYSQL_PS1=”\\u@\\h:\\p [\\d]>”
export PATH=\$PATH:/usr/local/mysql/bin
EOF

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

mkdir /data/mysql/3306/ -p
cd /data/mysql/3306
mkdir {data,logs,tmp}
chown mysql:mysql -R /data

 

mysql 5.7版本的二进制安装方法

mysql安装
mysql安装
版本选择
mysql安装方式
安装后的常规操作
系统初始化优化建议
版本选择
目前mysql系的数据库分成三大分支

mariadb

常见版本:5.5,10.0,10.1,10.2

在三者中,mariadb是一个最为支持”开源”理念的数据库,一些比较先进的技术基本都会由mariadb率先支持。例如目前备受关注的spider engine,如果你要使用,只能采用mariadb做平台来进行测试。

mysql-server

常见版本:5.5,5.6,5.7,8.0

目前常见的mysql-community-server都是oracle收购mysqlab公司之后发布的产品,尤其是mysql5.7,在之前基础上做了很大的一些改进,相对而言比较稳定且高效。

percona-server

常见版本:5.5,5.6,5.7

percona-server是mysql的二次开发版本,本身percona公司就对mysql具有很强的理解,在原生基础上提升了高负载下innodb存储引擎的性能,并提供了更多的参数和命令来控制服务器行为、诊断服务器的性能工具等。在5.6版本上提升很显著,但是5.7版本目前优势不是特别大,无法跟进官方版本。

mysql安装方式
目前这里的安装主要以mysql-5.7版本为例。

所需文件下载地址:https://dev.mysql.com/downloads/mysql/

mysql5.7提供的安装方式如下:

安装方式 灵活程度 难易程度
包管理器(rpm,yum) 低 一键部署
二进制安装 中等 较为轻松
源码编译 最高 繁琐复杂
+ 包管理器安装

yum -y install mysql-community-server
1
二进制安装
1) 创建mysql服务用户
[root@www ~]# groupadd -g 27 mysql
[root@www ~]# useradd -u 27 -g 27 mysql
2) 解压mysql工具包
[root@www ~]# tar -xvf /path/to/mysql-version.tar.gz -C /opt
3) 查看mysql工具包解压后文件情况
[root@www mysql-5.7.20-linux-glibc2.12-x86_64]# ls
bin COPYING docs include lib man README share support-files
4) 设定mysql主配置文件
[root@www mysql-5.7.20-linux-glibc2.12-x86_64]# cat /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
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

log-error=/var/log/mysql/error.log
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
5) 创建所需目录并修改权限
[root@www ~]# mkdir /var/lib/mysql
[root@www ~]# mkdir /var/log/mysql
[root@www ~]# chown mysql. -R /var/lib/mysql
[root@www ~]# chown mysql. -R /var/log/mysql
6) 初始化数据库
[root@www ~]# /opt/mysql-5.7.20-linux-glibc2.12-x86_64/bin/mysqld –initialize –user=mysql –defaults-file=/etc/my.cnf
7)启动数据库
[root@www ~]# /opt/mysql-5.7.20-linux-glibc2.12-x86_64/bin/mysqld –user=mysql –defaults-file=/etc/my.cnf &
8) 修改数据库初始root密码
[root@www ~]# grep password /var/log/mysql/error.log
[root@www ~]# /opt/mysql-5.7.20-linux-glibc2.12-x86_64/bin/mysqladmin -uroot -p’6qA8M/i7_f2P’ -S /var/lib/mysql/mysql.sock password
9)登陆mysql
[root@www ~]# mysql -uroot -p
password:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
源码编译(过程略)
安装后的常规操作
0) 简化命令行

[root@www ~]# vim /etc/bashrc
export PATH=$PATH:/opt/mysql-5.7.20-linux-glibc2.12-x86_64/bin # 这样做的好处在于能够随时调取/opt/mysql-version/bin下的指令
[root@www ~] source /etc/bashrc
1
2
3
1) 调整mysql的启动服务脚本(简化启动脚本)

[root@www bin]# cd /opt/mysql-5.7.20-linux-glibc2.12-x86_64/support-files/
[root@www support-files]# cp mysql.server /etc/init.d
[root@www support-files]# cd /etc/init.d/
[root@www init.d]# ls
aegis agentwatch functions jenkins mysql.server netconsole network README
[root@www init.d]# vim mysql.server
basedir=/opt/mysql-5.7.20-linux-glibc2.12-x86_64
datadir=/var/lib/mysql
[root@www init.d]# mv mysql.server mysqld
[root@www init.d]# service mysqld start
Starting MySQL. SUCCESS!
1
2
3
4
5
6
7
8
9
10
11
2)调整启动脚本参数(变更mysql进程)

[root@www opt]# ps -ef | grep mysql
root 1040 1 0 16:03 pts/0 00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe –datadir=/var/lib/mysql –pid-file=/var/lib/mysql/www.caroljy.com.pid
mysql 1183 1040 5 16:03 pts/0 00:00:00 /opt/mysql/bin/mysqld –basedir=/opt/mysql –datadir=/var/lib/mysql –plugin-dir=/opt/mysql/lib/plugin –user=mysql –log-error=/var/log/mysql/error.log –pid-file=/var/lib/mysql/www.caroljy.com.pid –socket=/var/lib/mysql/mysql.sock
root 1214 670 0 16:03 pts/0 00:00:00 grep –color=auto mysql
# 可以看到,我们这里起了两个进程,首先是root用户拥有的mysqld_safe进程,其次是mysql用户拥有的mysqld进程
# 在这里我们建议直接采用mysqld进程的方式来运行mysql的服务,避免出现问题后,进程反复被拉起。
[root@www init.d]# sed -i ‘s/mysqld_safe/mysqld/’ mysqld
# 测试能否正常启动
[root@www init.d]# service mysqld start
Starting MySQL. SUCCESS!
1
2
3
4
5
6
7
8
9
10
3) 简化登陆方式

# 我们目前登陆mysql的方式是这样的:
[root@www init.d]# mysql -uroot -p -S /var/lib/mysql/mysql.sock
Enter password:
# 这样的登陆方式就显得很麻烦,每次都需要重新键入密码等参数。
# 如果想要简化登陆方式也不是不可以。
# 在mysql5.7提供了login-path这样的一个功能
/mysql_config_editor set –login-path=’jenny’ –user=’root’ –password –host=’localhost’ –socket=’/var/lib/mysql/mysql.sock’
# 这样做会在当前用户家目录地下生成一个文件
[root@www init.d]# cd ~
[root@www ~]# ls -a
. .bash_logout .cache file .mylogin.cnf .pki .tcshrc
.. .bash_profile .cshrc .lesshst .mysql_history .pydistutils.cfg .viminfo
.bash_history .bashrc .elinks .links .pip .ssh
[root@www ~]# file .mylogin.cnf
.mylogin.cnf: data
# 在这个文件中就会记录我们相关的登陆信息,并且该文件不能直接被查看到其内容,一方面也保证了我们登陆数据的安全。
# 下次登陆的时候,我们就可以这样做
[root@www ~]# mysql –login-path=jenny
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
系统初始化优化建议
关闭numa

有条件的配置raid5或者raid10

调整内核参数

[root@srv-ces-asia-db2 ~]# ulimit -a # 可修改/etc/security/limits.conf文件使其永久生效
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 62794
max locked memory (kbytes, -l) 64 # 根据需求进行调整
max memory size (kbytes, -m) unlimited # 根据需求进行调整
open files (-n) 655360 # 建议设置成655360。 也可以设置的更大。
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 62794
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql初始化安全加固

delete from mysql.user where root!=’root’ of host!=’localhost’
———————
作者:CaroLjy
来源:CSDN
原文:https://blog.csdn.net/caroljy/article/details/79565635
版权声明:本文为博主原创文章,转载请附上博文链接!

MYSQL三种安装方式–二进制包安装

1.

把二进制包下载到/usr/local/src下

 

2.

如果是tar.gz包,则使用tar zxvf 进行解压

如果是tar包,则可以使用tar xvf 进行解压

 

3.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
#创建MySQL用户
#创建MySQL数据目录
$ groupadd mysql
useradd -r -g mysql mysql
mkdir -p /data/mysql
chown -R mysql. /data/mysql
#进行初始化
#这个版本的MySQL没有script目录,所以直接查初始化的命令在哪
find . -name 'mysql_install_db'
./bin/mysql_install_db
$ ./bin/mysql_install_db --initialize --user=mysql --datadir=/data/mysql
$ ./bin/mysql_ssl_rsa_setup
$ ./bin/mysqld_safe --user=mysql
$  cp support-files/mysql.server /etc/init.d/mysqld
# 根据需要拷贝配置文件
$  cp support-files/my-default.cnf /etc/my.cnf

 

4. 启动

1
/etc/init.d/mysqld start