mysql：实战篇

mysql 实战篇

前言

整理下装好mysql的基本配置。

修改mysql的配置文件

[注]：
windows修改my.ini
Linux如Ubuntu等修改my.cnf，在/etc/mysql/my.cnf中，但一般修改/etc/mysql/mysql.conf.d/mysqld.cnf。

修改内容：

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
max_allowed_packet = 500M # 解决：插入mysql的数据量太大，造成I/O口堵塞，导致mysql崩掉无法写入数据
innodb_buffer_pool_size = 3G # 插入数据时InnoDB缓存（缓冲池）大小，一般不超过电脑内存的80%
sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' # 可用group by分组，可插入日期'0000-00-00'

修改后要service mysql restart来重启mysql生效！

1. 插入mysql的数据量太大，造成I/O口堵塞，导致mysql崩掉无法写入数据。

原因

由于mysql对数据写入量的大小作了限制，默认大小 4194304 = 4M

解决方法

法1（临时生效法）：

查看初始默认数据：

mysql > show VARIABLES like '%max_allowed_packet%';

更改数据：

mysql > set global max_allowed_packet = 500*1024*1024;
mysql > flush privileges;

查看更改后的数据：

mysql > show VARIABLES like '%max_allowed_packet%';

[注]：也可在python、java等后端脚本执行sql

上面的方法是临时生效的，重启mysql后会失效。要想永久生效需要改配置文件，如下法2

法2（永久生效法）：

centos7 修改my.cnf在[mysqld]下面加上： max_allowed_packet = 524288000 # 524288000/1024/1024 = 500MB
，或写 max_allowed_packet = 500M也可以，
即

[mysqld]
max_allowed_packet = 524288000 # 或 max_allowed_packet = 500M

再重启mysql： service mysqld restart使配置生效
[注]：
windows修改my.ini
Ubuntu16.04的my.cnf在/etc/mysql/my.cnf中，但一般修改/etc/mysql/mysql.conf.d/mysqld.cnf。重启mysql用service mysql restart

2. mysql 链接超时 ‘mysql server has gone away’ 问题

原因

mysql默认长连接时间wait_timeout为8小时，超过了这个时间，被server默认关闭

解决方法

法1（临时生效法）：

mysql > show global variables like 'wait_timeout';
mysql > set global wait_timeout = 24*3600

如下：

更多原因 可参考：https://cloud.tencent.com/developer/article/1396107

法2（永久生效法）：

centos7 修改my.cnf在[mysqld]下面加上相关信息。即：

[mysqld]
wait_timeout = 86400
interactive_timeout = 86400

这里将interactive_timeout也配置了。
再重启mysql： service mysqld restart使配置生效
[注]：
windows修改my.ini
Ubuntu的my.cnf在/etc/mysql/my.cnf中，但一般修改/etc/mysql/mysql.conf.d/mysqld.cnf。重启mysql用service mysql restart

3.字符串超长， varchar(255)存不下

解决方法

将表字段类型改为TEXT来存放：
（1）可以在建表的时候就用TEXT类型，而不用varchar(255)
（2）或者如果是已经有大量数据的，改一下字段类型，如下：
mysql> alter table table_name modify column_name TEXT NOT NULL;
注意：TEXT类型设置了NOT NULL就不能设置默认值（如DEFAULT ’ '），设置默认值就不能设置NOT NULL。
但varchar类型可以，如mysql> alter table table_name modify column_name varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '';

4. mysql更改数据存储位置

问题详述

数据量太大，mysql默认的数据存储位置在固态硬盘/var/lib/mysql已经存不下，怎样更改存储位置到几个TB的大硬盘呢？

版本

ubuntu 16.04.7
mysql 8.0.22

解决方法

1. 准备
   （1） 先进入root模式，输入su
   （2） 停止mysql
   mysqladmin -u root -p shutdown
   （3）在大硬盘创建新的数据存储目录new_dir，
   举例：我们在虚拟机可以在home（用户名假设为user）下创建一个data目录先测试下，即new_dir = /home/user/data

$ cd /home/user
$ mkdir data

下面的new_dir 即 /home/user/data
2. 步骤：
（1）复制已有的数据到新的数据存储地址，默认数据地址(/var/lib/mysql) :
cp -R -p /var/lib/mysql /new_dir
（2）重点：更改new_dir/mysql新数据目录的所有者为mysql和mysql（第一个mysql为用户，第二个mysql为用户组）
$ chown -R mysql:mysql /new_dir/mysql 这里即 $ chown -R mysql:mysql /home/user/data/mysql
注意：新建/重命名/重新复制这个目录，都要再重新设置一遍这一步！！！否则报错！
（3）编辑 MySQL配置文件mysqld.cnf（也可能是my.cnf）更改datadir属性
$ vim /etc/mysql/mysql.conf.d/mysqld.cnf 或者有的是在 $vim /etc/mysql/my.cnf这里，
找到 datadir，更改为新的数据存储位置new_dir
（4）编辑配置文件usr.sbin.mysqld
$ vim /etc/apparmor.d/usr.sbin.mysqld
将# Allow data dir access下面的两行：

# Allow data dir access
/var/lib/mysql/ r,
/var/lib mysql/** rwk,

更改为

# Allow data dir access
/new_dir/mysql/ r,
/new_dir/mysql/** rwk,

这里即：

# Allow data dir access
  /home/user/data/mysql/ r,
  /home/user/data/mysql/** rwk,

说明：因为原来的数据存在了/var/lib/mysql/里，新的我们想存到new_dir/mysql/里（这里home下新建了data目录，即/home/user/data/mysql里）
小提示：只改var/lib/mysql即可，其他的var/lib不用改，因为我们只拷贝了数据目录var/lib/mysql，其他配置属性还用var/lib下的即可
（5）reload上面的usr.sbin.mysqld文件使之更改生效
$ /etc/init.d/apparmor reload
（6）重启mysql
$ service mysql restart

5. sql_mode相关配置

sql_mode相关的统一配置可以改mysql的配置文件（win是my.ini；linux是my.cnf）来永久生效，
Ubuntu的my.cnf在/etc/mysql/my.cnf中，但一般修改/etc/mysql/mysql.conf.d/mysqld.cnf。
下面的5.1和5.2可以直接修改my.cnf来解决！

[mysqld]
sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

改完后重启mysql用service mysql restart 生效！
附：默认的sql_mode为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

5.1 解决mysql 分组group by不能用的问题

原因：

global.sql_mode默认设置了ONLY_FULL_GROUP_BY

方法：

mysql> select @@global.sql_mode; # get result1
mysql> set @@session.sql_mode='xxxxxxxxxxx'; # result1 delete first line’s only group by...

步骤：

（1）先查找 select @@global.sql_mode;
可看到：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

（2）再将上面查找出来的值 去掉第一个ONLY_FULL_GROUP_BY，重新更新值。即：

set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

如果临时用，也可

set @@session.sql_mode=`STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION`;

注意：也可修改my.cnf来解决，见上面。

5.2. 解决mysql 分组不能插入日期’0000-00-00’的问题

原因：

mysql5.7以后默认情况不能插入无效的日期格式 ‘0000-00-00’

方法：

（1）先查找 select @@global.sql_mode;
（2）再将上面查找出来的值 去掉NO_ZERO_IN_DATE,NO_ZERO_DATE,，重新更新值。即：

set @@global.sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

如果临时用，也可

set @@session.sql_mode=`STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION`;

注意：也可修改my.cnf来解决，见上面。

6. mysql中跑sql脚本

使用source命令即可跑sql脚本了，进入mysql终端，运行：
mysql> source /data/xx.sql

7. mysql忘记密码

方法

利用 skip-grant-tables 参数 跳过密码

步骤

如我们mysql的用户名和密码都是root，平时登录mysql是mysql -uroot -p再输入root。但我们忘记密码了。

1）设置mysql登录 跳过密码。（mysql安全模式）

首先，更改/etc/my.cnf（有的系统在/etc/mysql/my.cnf）文件，在[mysqld]下加上 skip-grant-tables，即：

[mysqld]
skip-grant-tables

重启mysql：
Ubuntu用$service mysql restart， centos用$service mysqld restart，这时候我们就可以跳过密码验证无密码登录mysql了！

2）改密码

然后登录mysql终端输入 mysql -uroot即可直接以root登录。（此时直接输入mysql也可直接登录，但不要直接输入mysql，因为这不是以root用户登录）

1)) Ubuntu

MySQL8.0+舍弃了PASSWORD()函数，故配置也不同，如下所述

（1）Ubuntu: MySQL5.0+

(i)改密码
然后输入：

mysql> use mysql;
mysql> update user set authentication_string=PASSWORD("这里输入你要改的密码") where user='root'; #更改密码
mysql> update user set plugin="mysql_native_password" where user='root'; #依mysql版本定，如果值是mysql_native_password则不用此步，如果这个值不是这个则更新成这个。否则没这一行可能也会报一个错误。
mysql> flush privileges;  # 刷新，更新所有操作权限
mysql> SELECT user, host, password FROM user; #改完密码后查看一下

这里user为root用户，host是主人的意思、表示被谁所有、mysql能被谁所访问。默认 host=‘localhost’ 故mysql只能被本地访问，后来改成 host=‘%’ 则mysql可被所有终端访问。
或直接一步到位法：

mysql> UPDATE mysql.user SET password=password('new_password') WHERE user='root'
mysql> flush privileges; #刷新，更新所有操作权限（这步可以省略，后面重启mysql会自动刷新）

(ii)my.cnf配置文件改回去
最后，将跳过密码登录模式改回以密码登录模式，即：将/etc/my.cnf文件中的[mysqld]下加上的 skip-grant-tables，注释掉。再重启mysql：
Ubuntu用$service mysql restart， centos用$service mysqld restart
此时，输入mysql -uroot -p 即可新密码正常登录mysql了。

（2）Ubuntu: MySQL8.0+

(i)密码置空，即实现真正的无密码登录

mysql> use mysql;
mysql> update user set plugin="mysql_native_password" WHERE user=“root”;  # 如果值是mysql_native_password则不用此步，如果这个值不是这个则更新成这个。否则没这一行可能也会报一个错误。
mysql> UPDATE user SET authentication_string="" WHERE user=“root”; # 先将密码置空，即实现真正的无密码登录。
mysql> flush privileges;  # 刷新，更新所有操作权限 （这步可以省略，后面重启mysql会自动刷新）

(ii)改密码
再将跳过密码登录模式改回以密码登录模式，即：将/etc/my.cnf文件中的[mysqld]下加上的 skip-grant-tables，注释掉。再重启mysql：Ubuntu用$service mysql restart， centos用$service mysqld restart。
此时可以无密码登录mysql了，输入：mysql -uroot即可登录，再用如下命令自定义密码，如下：

mysql> use mysql;
mysql> alter user 'root'@'localhost' identified with mysql_native_password by "这里输入你要改的密码";
mysql> flush privileges;  # 刷新，更新所有操作权限 （这步可以省略，后面重启mysql会自动刷新）

再重启mysql即可：Ubuntu用$service mysql restart， centos用$service mysqld restart。

2)) centos7

注意：centos的字段是password，Ubuntu是authentication_string，其他都一样。
MySQL.0 + 为例（MySQL8.0+参考上面Ubuntu配置）

mysql> use mysql;
mysql> UPDATE user SET password=password('new_password')  WHERE user='root'
mysql> flush privileges; #刷新，更新所有操作权限
mysql> SELECT user, host, password FROM user; #改完密码后查看一下

或直接一步到位法：

mysql> UPDATE mysql.user SET password=password('new_password') WHERE user='root'
mysql> flush privileges; #刷新，更新所有操作权限

最后，将跳过密码登录模式改回以密码登录模式，即：将/etc/my.cnf文件中的[mysqld]下加上的 skip-grant-tables，注释掉。再重启mysql：centos用$service mysqld restart
此时，输入mysql -uroot -p 即可新密码正常登录mysql了。

附：加密方式

通过输入mysql> desc user; 查看 plugin字段，值为mysql_native_password则为mysql5的加密方式，值为caching_sha2_password好像是mysql8的新加密方式，如下：

从商业角度建议使用sha2，会逐步过度到sha2，更安全。其他如sha1、md5不安全，加密这块参考mysql官方：
https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password
官方部分截图如下：

8. mysql插入大量数据报错 ERROR 1206 (HY000): The total number of locks exceeds the lock table size

原因

插入的表使用的应该是InnoDB引擎，而插入数据的缓存大小超出了默认配置的InnoDB缓存（缓冲池）大小。
比如我作数据迁移，要将table1的表数据迁移到新表new_table1中，
INSERT INTO new_table1(field1, field2) SELECT field1, field2 FROM table1 WHERE...;
这里先查询了table1的数据，放到了缓存中，再插入新表new_table1中，如果table1的数据量特别特别大，这样操作缓存存不下就会报出上面的错误！故可更改默认的InnoDB缓存配置。

解决方法

法1（临时生效法）：

更改InnoDB缓冲池大小：

mysql> show variables like '%_buffer_pool_size%';  ## 记不住可以 `show variables like '%buffer%`或  `show variables like '%pool%` 可以看到innodb_buffer_pool_size默认值是134217728即128MB（对于mysql8.0+版本），而mysql 5.0+版本默认是8388608Byte即8MB，下面我们改成15GB
mysql> SET GLOBAL innodb_buffer_pool_size = 15*1024*1024*1024;  ## 15GB = 16106127360 Byte
mysql>show variables like '%innodb_buffer_pool_size%';

$service mysql restart  ## 重启mysql

上面的15G是并不是越大越好，一般不超过电脑内存的80%（如服务器64G内存，而实际操作产生的缓存数据达到了10G，则设置15G这个值可以），否则影响性能。

法2（永久生效法）：

centos7 修改/etc/my.cnf在[mysqld]下面加上相关信息。即：

[mysqld]
innodb_buffer_pool_size = 16106127360 # 或 innodb_buffer_pool_size = 15G

再重启mysql： service mysqld restart使配置生效
[注]：
windows修改my.ini
Ubuntu16.04的my.cnf在/etc/mysql/my.cnf中，但一般修改/etc/mysql/mysql.conf.d/mysqld.cnf。最后用service mysql restart命令重启

9. 利用mysqldump和mysql命令进行 Linux(Ubuntu/Centos)/ Windows下mysql数据备份与恢复

利用>导出，利用<导入，导出用mysqldump命令，导入用mysql命令。
如下，要将库database1的数据备份到database2中：
1）备份/导出。先将库database1数据备份位sql文件中
（1）如果备份database1整个库，则

$ mysqldump -uroot -p database1 > /存储位置/database1_data_bak.sql

输入mysql密码即可。
（2）如果备份database1库中的某个表或某几个表，如将库database1的student表和school表备份，则

$ mysqldump -uroot -p database1 student school > /存储位置/database1_data_bak.sql

注：备份表，表名间不要用逗号分隔，不要写成 database1, student, school
2）恢复/导入。从前面备份好的sql文件恢复数据。

$ mysql -uroot -p database2 < /存储位置/database1_data_bak.sql

注：这里命令是mysql，不再是mysqldump
说明：mysqldump是装好mysql自带的，无论linux还是windows都有。windows也可打开cmd终端直接输入mysqldump命令备份。

10. mysql的连接方式

方式1（TCP/IP套接字）：mysql -h主机IP -P端口号 -u用户名 -p密码

如：mysql -h127.0.0.1 -P3306 -uroot -p 回车后输入密码即可。
mysql -uroot -p 默认localhost 和 3306

方式2（Unix套接字）（连接本机）：mysql -S socket文件路径 -uroot -p密码

如：mysql -S /var/run/mysqld/mysqld.sock -uroot -p
【附】查询socket文件位置，mysql> show variables like 'socket';

11. 存储过程

先创建一个空表test_y，

create table `test_y` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`a` varchar(255) collate utf8_bin NOT NULL default '',
	`b` varchar(255) collate utf8_bin NOT NULL default '',
	`c` varchar(255) collate utf8_bin NOT NULL default '',
) ENGINE = InnoDB auto_increment = 1 default charset = utf8 ROW_FORMAT=COMPACT;

写入1000w条数据数据，思路是创建存储过程：如proc3()，可将存储过程看成函数proc3()，设置变量 i 自增，最后调用call proc3；

mysql> delimiter $$ -- 将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> create procedure proc3() -- 存储过程名，类似函数, proc = procedure
    -> begin
    -> declare i int; -- 声明变量 i
    -> set i = 1;
    -> while i < 10000001 do
    -> insert into test_y (id, a, b, c) values(i, concat('A', i), concat('B', i), concat('C', i));
    -> set i = i + 1;
    -> end while;
    -> end;
    -> $$
mysql> delimiter ; -- 将语句的结束符号恢复为分号
Query OK, 0 rows affected (0.12 sec)

mysql> call proc3; -- 调用存储过程

解析：默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。
结果： mysql插入完这1000w条数据大概需要2.5小时，而大数据clickhouse只需要几秒钟。

12. tinyint(1) 使用场景---- 只有0或1的字段

当表某个字段只有0或1两种值（或两种状态）时，如：性别、开关、True/False、涨跌，设置成tinyint(1)类型效率最高。
tinyint类型且长度为1，即：tinyint(1)，如下：

`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,