MySql安装与部署

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 5.7k | 阅读时长 ≈ 5 分钟
MySql在各个环境下的安装部署

描述

随手记一下mysql在linux和在mac os下的一些安装以及踩过的坑。大版本号是5.7，小版本应该没有太大影响。

安装

Windows

mysql在windows平台上的安装比较简单，但是最好不要在中文目录下安装比较重要的开发软件，不然出现一些问题真的没地方哭。

MAC OS X

默认安装位置在/usr/local/下，新版数据库没有用户自定义配置文件，可以用vim新建一个my.cnf文件，将/usr/local/mysql/suppoty-files/目录下的my-default.cnf里的内容复制到新建的my.cnf里，假如无法复制，可以在控制台按command+s打印控制台内容，复制到my.cnf里以后，将[mysqld]下添加

datadir=/usr/local/mysql/data
basedir=/usr/local/mysql/
character-set-server=utf8(该段代码可选，设置数据库服务端编码格式)

对于有存储微信表情的需要来说，字符编码建议设置为utf8mb4，该字符集是对utf8的的一种补充。

同时在[mysqld]上添加[client]，在[client]下添加：

default-character-set=utf8(设置客户端创建数据库默认的编码格式)

修改密码：set password for root@localhost=password('root')
假如/etc/my.cnf文件[mysqld] 下加入了skip-grant-tables上面这个修改密码的语句就不能使用了，
此时应该使用以下语句：
update user set password=password('root') where user='root';字符集查询结果

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like '%char%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | utf8                                                   |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | utf8                                                   |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.5.23-osx10.6-x86_64/share/charsets/ |
+—————————————+--------------------------------------------------------+

CentOS

使用的是RPM包的方式，下载完mysql的tar包，然后解压开能看到很多mysql-community-开头的.rpm文件，使用如下命令就可以安装sudo yum install mysql-community-{server,client,common,libs}-* --exclude='*minimal*' 当然要排除最小安装，然后需要找到临时密码才能进入数据库改密码：sudo grep 'temporary password' /var/log/mysqld.log找到密码就可以使用mysql -uroot -p 登陆，然后修改密码:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

如果需要远程互联网访问的话，需要使用如下语句开启权限：

mysql> grant all on *.* to 'root'@'%' identified by 'root'；
这个语句的含义是：

  1. 如果用户root@%不存在，就创建这个用户，密码是root；
  2. 如果用户root已经存在，就修改密码。

注：除修改密码和查询结果的操作外，其他操作必须在停止数据库的情况下进行。
假如忘记了数据库密码或者不知道数据库密码，可以进入/etc/my.cnf文件[mysqld] 下加入了skip-grant-tables，输入密码的时候就可以不输入直接进入。修改密码以后再取消那段语句。也可以直接使用参数启动mysql的安全模式/usr/local/mysql/bin/mysqld_safe --skip-grant-tables，执行update mysql.user set authentication_string=password('newpassword') where user='root' and Host = 'localhost';和flush privileges;以后再以正常模式启动mysql

flush privileges使用场景：当数据表中的权限数据和内存中的权限数据不一样时，例如上文中DML语句修改了mysql用户表中的用户信息。但是在执行grant语句以后或者revoke，由于这两种语句会同时更新内存和磁盘，所以不需要flush操作。

使用原则

摘录自互联网《mysql 36军规》：

  1. 不在数据库做运算，CPU计算必须移至业务层；
  2. 控制单表数量在1000万以内，业内公认mysql单表1000万数据时树的高度在3～5，性能较好；
  3. 控制列数量：控制字段数量在20个以内；
  4. 平衡范式和冗余：为提高效率牺牲范式设计，冗余数据；
  5. 拒绝Big sql，Big transaction，Big batch；
  6. 用好数值类型：tinyint(1Byte)，smallint(2Byte)，mediumint(3Byte)，int(4Byte)，bigint(8Byte)，所以如int(1)是不合理的；
  7. 字符转化为数字，例如，使用int而不是char(15)存储IP；
  8. 优先使用enum或set；
  9. 避免使用NULL字段，因为NULL字段很难查询，并且NULL字段的索引需要额外的空间，NULL字段的复合索引无效，所以int类型的字段可以默认为0，varchar类型的字段可以默认为空字符串；
  10. 少用text/blog：varchar性能会比text和blog高很多，如果避免不了可以拆表；
  11. 不在数据库里存图片；
  12. 合理使用索引；
  13. 字符字段必须建前缀索引；
  14. 不在索引做列运算；
  15. innodb主键推荐使用自增列，字符串不应该做主键，如果不指定主键，innodb会使用唯一且非空值索引代替；
  16. 不用外键，由程序保证约束；
  17. sql语句尽可能简单，一条sql只能在一个cpu运算；
  18. 事务尽可能简单；
  19. 避免使用函数和触发器；
  20. 不使用select *；
  21. or改写为in，or的效率是n级别，in的效率是log(n)级别，in的个数控制在200以内；
  22. or改写为UNION，mysql的索引合并比较弱智：select id from t where phone=’159’ or name = ‘admin’ => select id from t where phone = ‘159’ union select id from t where phone = ‘admin’;
  23. 避免负向%
  24. 慎用count(*)（个人不认可）
  25. limit分页数据一旦很大，尽量使用索引覆盖；
  26. 使用union all 代替union；
  27. 少用链接join；
  28. 使用group by分组，自动排序；
  29. 使用同类型比较；
  30. 使用load data 倒数比insert快约20倍；
  31. 打散批量更新；
  32. 性能分析：explain，show profile，mysqlsla，mysqldumpslow；show slow log；show processlist，show query_response_time(percona)

常用姿势

查询处理

1
select distinct * from t left join t1 on t1.t_id = t.id where t.city='beijing' group by 'name' having name='liuhuijun' order by birthday limit 10;

如上，那么查询顺序是：
from t,t1 -> on t1.t_id = t.id -> where t.city=’beijing’ -> group by ‘name’ -> having name = ‘liuhuiun’ -> select -> distinct *-> order by birthday -> limit 10

  • from:对from子句种的左表和右表执行笛卡尔积（Cartesian product），产生虚拟表VT1；
  • on:对虚拟表VT1进行ON筛选，只有符合t1.t_id = t.id的行才被插入虚拟表VT2；
  • join:如果是out join类型，那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，作为VT3，如果有多个连接表，那么再一次执行上面的三步操作；
  • where:对虚拟表进行where筛选，只有符合city=’beijing’的城市才加入到VT4中；
  • group by:根据group by对VT4记录进行分组操作，产生VT5；
  • having:对虚拟表VT5进行having筛选，只有符合 name=’liuhuijun’的才加入到VT6中；
  • select:选择指定的列，生成到虚拟表VT7中；
  • distinct:去除重复数据，产生虚拟表VT8；
  • order by:将VT8只能够的数据进行排序，产生VT9；
  • limit:取出对应条数数据生成VT10，并返回。

可以在建表时使用查询语句：

1
2
3
4
5
6
7
8
9
<!-- 复制表结构和数据到新表 -->
CREATE TABLE new_table SELECT * FROM old_table
只复制表结构
CREATE TABLE new_table SELECT * FROM old_table where 1 = 2
CREATE TABLE new_table LIKE old_table

<!-- 将旧表数据插入新表 -->
insert into new_table select * from old_table
insert into new_table(column1，column2...)  select (dolumn1,column2...) from old_table​

数据库备份

1
2
3
4
5
6
7
8
9
10
11
12
<!--备份某数据库到文件-->
mysqldump -uroot -p DB_name > dump_file
<!--备份某数据库到文件，与上面的区别在于这条语句会包含drop table if exist table_name,insert之前会有一个锁表语句lock tables talbe_name write，insert之后会有unlocl tables-->
mysqldump -opt -uroot -p > dump_file
<! --从远程主机的源库传输数据到目标库，前提目标库已经存在-->
mysqldump --host=192.168.1.1 --opt sourceDB | mysql --host=localhost -C targetDB
<!--只备份结构不备份数据-->
mysqldump --no-data --database DB1 DB2 DB3 > dump_file
<!--备份所有表-->
mysqldump --all-databases > dump_file
<!--从dump文件恢复-->
mysql -uroot -p DB_name < dump_file​
坚持原创、技术分享。请作者喝杯茶吧！