MySQL 8.0 误删了root用户折腾记

背景:最新想尝鲜安装下ubuntu 20.04,默认mysql版本为8.0,一不小心把root用户给删除了,开始折腾。 一、修改配置文件 在配置文件/etc/mysql/conf.d/mysql.cnf中增加2行 skip-grant-tables 重启mysql服务: sudo systemctl restart mysql.service 登录mysql,分别执行以下几行code use mysql; insert into user(User,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('root','','','',''); update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y',Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';commit; 二、修改配置为正常再修改root密码 将密码修改成root@12345 mysql use mysql; alter user 'root'@'localhost' identified with mysql_native_password by 'root@12345';
阅读全文

mysql查看数据库性能常用命令

摘自: http://blog.csdn.net/iquicksandi/article/details/7970706 http://www.cnblogs.com/w2154/p/4691015.html 网上有很多的文章教怎么配置MySQL服务器,但考虑到服务器硬件配置的不同,具体应用的差别,那些文章的做法只能作为初步设置参考,我们需要根据自己的情况进行配置优化,好的做法是MySQL服务器稳定运行了一段时间后运行,根据服务器的”状态”进行优化。 mysql> show global status; # 可以列出MySQL服务器运行各种状态值,另外,查询MySQL服务器配置信息语句: mysql> show variables; ``` #### 一、慢查询 ```shell   mysql> show variables like '%slow%';   +------------------+-------+   | Variable_name | Value |   +------------------+-------+   | log_slow_queries | ON |   | slow_launch_time | 2 |   +------------------+-------+   mysql> show global status like '%slow%';   +---------------------+-------+   | Variable_name | Value |   +---------------------+-------+   | Slow_launch_threads | 0 |   | Slow_queries | 4148 |   +---------------------+-------+   配置中打开了记录慢查询,执行时间超过2秒的即为慢查询,系统显示有4148个慢查询,你可以分析慢查询日志,找出有问题的SQL语句,慢查询时间不宜设置过长,否则意义不大,最好在5秒以内,如果你需要微秒级别的慢查询,可以考虑给MySQL打补丁: http://www.percona.com/docs/wiki/release:start,记得找对应的版本。 打开慢查询日志可能会对系统性能有一点点影响,如果你的MySQL是主-从结构,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查询,对系统性能影响又小。 二、连接数   经常会遇见”MySQL: ERROR 1040: Too many connections”的情况,一种是访问量确实很高,MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小: mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 256 | +-----------------+-------+   这台MySQL服务器最大连接数是256,然后查询一下服务器响应的最大连接数: mysql> show global status like ‘Max_used_connections’; MySQL服务器过去的最大连接数是245,没有达到服务器连接数上限256,应该没有出现1040错误,比较理想的设置是:...
阅读全文

MySQL占用磁盘I/O问题排查

前言:最近查一个磁盘I/O高的问题,涉及到了innodb_flush_log_at_trx_commit 和 sync_binlog 是 MySQL 的两个配置参数,前者是 InnoDB 引擎特有的。之所以把这两个参数放在一起讨论,是因为在实际应用中,它们的配置对于 MySQL 的性能有很大影响。 innodb_flush_log_at_trx_commit 简而言之,innodb_flush_log_at_trx_commit 参数指定了 InnoDB 在事务提交后的日志写入频率。这么说其实并不严谨,且看其不同取值的意义和表现。 当 innodb_flush_log_at_trx_commit 取值为 0 的时候,log buffer 会 每秒写入到日志文件并刷写(flush)到磁盘。但每次事务提交不会有任何影响,也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下,MySQL性能最好,但如果 mysqld 进程崩溃,通常会导致最后 1s 的日志丢失。 当取值为 1 时,每次事务提交时,log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置,但由于每次事务都需要进行磁盘I/O,所以也最慢。 当取值为 2 时,每次事务提交会写入日志文件,但并不会立即刷写到磁盘,日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃,由于日志已经写入到系统缓存,所以并不会丢失数据;在操作系统崩溃的情况下,通常会导致最后 1s 的日志丢失。 上面说到的「最后 1s」并不是绝对的,有的时候会丢失更多数据。有时候由于调度的问题,每秒刷写(once-per-second flushing)并不能保证 100% 执行。对于一些数据一致性和完整性要求不高的应用,配置为 2 就足够了;如果为了最高性能,可以设置为 0。有些应用,如支付服务,对一致性和完整性要求很高,所以即使最慢,也最好设置为 1. sync_binlog sync_binlog 是 MySQL 的二进制日志(binary log)同步到磁盘的频率。MySQL server 在 binary log 每写入 sync_binlog 次后,刷写到磁盘。 如果 autocommit 开启,每个语句都写一次 binary log,否则每次事务写一次。默认值是 0,不主动同步,而依赖操作系统本身不定期把文件内容 flush 到磁盘。设为 1 最安全,在每个语句或事务后同步一次 binary log,即使在崩溃时也最多丢失一个语句或事务的日志,但因此也最慢。 大多数情况下,对数据的一致性并没有很严格的要求,所以并不会把 sync_binlog 配置成 1. 为了追求高并发,提升性能,可以设置为 100 或直接用 0. 而和 innodb_flush_log_at_trx_commit 一样,对于支付服务这样的应用,还是比较推荐 sync_binlog = 1. 查看配置参数 使用iostat -dxm 1命令和iotop命令查看磁盘的占用情况,配置修改后: mysql> show variables like '%sync_bin%'; +---------------+-------+...
阅读全文

一、一些有用的Mysql选项

a. 错误日志 默认情况下错误日志功能是关闭的,有两种方法可以打开错误日志文件: 启动时使用--log-error= 在etc/my.cnf文件中设置选项log-error= (需要重启Mysql) b.二进制日志 默认情况下二进制日志是开启的,有两种方法可以重新配置二进制文件生成: 启动时使用--log-bin=,该方法可以直接修改二进制日志存放目录和文件名称 在etc/my.cnf文件中设置选项log-bin= (需要重启Mysql) 一些额外的选项: #指定数据库名生成日志 binlog-do-db=db_name binlog_format=mixed #主从复制时,从数据库所有错误均忽略 slave-skip-errors=all #bin日志的大小设置 max_binlog_size=10485760 c.查询日志 默认不开启,开启后对性能会有较大影响,注意开启,一般在做SQL语句查询性能跟踪时使用,可以通过在启动mysql时设置--log,默认名称为hostname.log d.慢查询日志 慢查询日志是指所有SQL执行的时间超过long_query_time变量的语句和达到min_examined_row_limit条距离的语句。用户可以针对这部分语句性能调优,有两种方法打开慢查询语句: 方法一:全局变量设置 将 slow_query_log 全局变量设置为“ON”状态 mysql> set global slow_query_log='ON'; 设置慢查询日志存放的位置 mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; 查询超过1秒就记录 mysql> set global long_query_time=1; 方法二:配置文件设置 修改配置文件my.cnf,在下的下方加入 slow_query_log = ON slow_query_log_file = /usr/local/mysql/data/slow.log long_query_time = 1 #接着重启mysql service mysqld restart
阅读全文

数据库外键和主键的区别

外键是数据库一级的一个完整性约束,就是数据库基础理论书中所说的“参照完整性”的数据库实现方式。   外键属性当然是可以去掉的,如果你不想再用这种约束,对编程当然不会有什么影响,但相应的录入数据的时候就不对录入的数据进行“参照完整性”检查了。       例如有两个表       A(a,b)   :a为主键,b为外键(来自于B.b)       B(b,c,d)   :b为主键    如果我把字段b的外键属性去掉,对编程没什么影响。如上面,A中的b要么为空,要么是在B的b中存在的值,有外键的时候,数据库会自动帮你检查A的b是否在B的b中存在。1、外建表达的是参照完整性:这是数据固有的,与程序无关。因此,应该交给DBMS来做。   2、使用外建,简单直观,可以直接在数据模型中体现,无论是设计、维护等回有很大的好处,特别是对于分析现有的数据库的好处时非常明显的--前不久我分析了一个企业现有的数据库,里面的参照完整性约束有的是外键描述,有的是用触发器实现,感觉很明显。当然,文档里可能有,但是也可能不全,但是外键就非常明显和直观。   3、既然我们可以用触发器或程序完成的这个工作(指参照完整性约束),DBMS已经提供了手段,为什么我们要自己去做?而且我们做的应该说没有RDBMS做得好。实际上,早期的RDBMS并没有外键,现在都有了,我认为数据库厂商增加这个功能是有道理的。从这个角度来说,外键更方便。   4、关于方便,根据我带项目的情况来看,程序员确实有反映,主要是在调试时输入数据麻烦:如果数据可以违反参照完整性,那么就是说参照完整性本身就不对名誉业务冲突,此时也不应该用触发期货程序实现;否则,说明数据是错误的,根本就不应该进入数据库!而且,这也应该是测试系统的一个内容:阻止非法数据。实际上,前台程序应该对这种提交失败做出处理。数据是企业的而非程序的,储程序要尽量与数据分离,反之亦然。最后说一下,建键几个原则:1、 为关联字段创建外键。2、 所有的键都必须唯一。3、避免使用复合键。4、外键总是关联唯一的键字段。 定义主键和外键主要是为了维护关系数据库的完整性,总结一下:一、主键是能确定一条记录的唯一标识,比如,一条记录包括身份正号,姓名,年龄。身份证号是唯一能确定你这个人的,其他都可能有重复,所以,身份证号是主键。外键用于与另一张表的关联。是能确定另一张表记录的字段,用于保持数据的一致性。比如,A表中的一个字段,是B表的主键,那他就可以是A表的外键。二、主键、外键和索引的区别定义: 主键--唯一标识一条记录,不能有重复的,不允许为空 外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值 索引--该字段没有重复值,但可以有一个空值作用: 主键--用来保证数据完整性 外键--用来和其他表建立联系用的 索引--是提高查询排序的速度个数: 主键--主键只能有一个 外键--一个表可以有多个外键 索引--一个表可以有多个唯一索引
阅读全文