MySQL

资料

1. MySQL 双机热备配置步骤
2. 使用Nginx+Keepalived组建高可用负载平衡Web server集群
3. 默认日志目录：/var/lib/mysql/
4. max_allowed_packet设置,过小导致写入失败
5. set global log_bin_trust_function_creators=1;
6. JDBC查询条件输入中文，查询为空：String url = "jdbc:mysql://127.0.0.1:3306/mydata?useUnicode=true&characterEncoding=utf-8";
7. MySQL Workbench的安全更新模式：SET SQL_SAFE_UPDATES=0;
8. mysql max_allowed_packet自动重置为1024 终结解决，查询max_allowed_packet重置的日志，日志文件是“general_log_file”
9. 不区分大小写，默认是区分大小写的
   1. nano /etc/my.cnf
   2. 在[mysqld]节点下，加入一行： lower_case_table_names=1
10. 开放外部访问

    // mysql的root账户放开访问限制。注意把pwd改成当前密码
    mysql -u root -p
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; FLUSH PRIVILEGES; EXIT;
    # 关闭防火墙
    systemctl stop firewalld.service #停止firewall
    systemctl disable firewalld.service #禁止firewall开机启动
    

11. 保存时中文乱码的解决
    • Mysql数据库的中文乱码问题分析
    • 两种方法
      • 找原因调整：如修改配置文件(/etc/my.cnf)，增加一行“character-set-server = utf8”
      • 强制设置：连接字符串设置成“mysql://${ip}:3306/${db_name}?useUnicode=true&characterEncoding=utf-8”
12. 日志
    1. MySQL日志分析
    2. 日志general-log的开启和分析方法
13. 整理Mysql "Too many connections" 解决办法
14. 生产环境中MySQL Drop 删除(百G级、T级)大表的解决方法

管理命令

1. 启动：service mysql restart
2. 登录客户端：mysql -u{account} -p{pwd} -h{ip} -P{port}。如mysql -uroot -p123123 -h192.168.1.10 -P3311
3. 新建mysql备份账号
   1. 登录
   2. 授权

      GRANT LOCK TABLES, SELECT ON *.* TO ba@localhost IDENTIFIED BY '123123';
      

4. 备份
   1. 备份：mysqldump -uba -p123123 dbName > dbName.db
   2. 恢复(2种)
      • 系统命令：mysql -uba -p123123 dbName < dbName.db
      • mysql控制台：mysql> use xyz;source /path/xyz.db;
5. UTF8的数据库创建：CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

SQL命令

• 修改账号密码

  use mysql;
  UPDATE user SET authentication_string=PASSWORD('123123') where USER='root';
  FLUSH PRIVILEGES;
  

• 显示变量：SHOW VARIABLES LIKE 'maxallowed%';
• 显示所有数据库：show databases;
• 选择数据库：use {database};
• 显示所有表：show tables;
• 删除非系统的表（被黑了用）

  SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement
  FROM information_schema.tables WHERE table_schema = 'mysql' AND table_name NOT IN
  ('columns_priv','db','event','func','general_log','help_category','help_keyword','help_relation','help_topic','host','ndb_binlog_index','plugin','proc','procs_priv','proxies_priv','servers','slow_log','tables_priv','time_zone','time_zone_leap_second','time_zone_name','time_zone_transition','time_zone_transition_type','user');
  

• 查询系统用户

  use mysql;
  select user,host from user;
  

• 显示表结构 : DESC {table_name};
• 显示表索引 : SHOW INDEX FROM {table_name};
• 删除表索引 :

  ALTER TABLE {table_name}
  DROP INDEX {column};
  

• 查询所有表的记录数量

  select concat('select "', TABLE_name, '", count(*) from ', TABLE_SCHEMA, '.', TABLE_name, ' union all') from information_schema.tables where TABLE_SCHEMA='20191024_huadong_ring_server';
  

安装

• 安装
• ubuntu下彻底卸载mysql后重新安装

知识

1. 我必须得告诉大家的MySQL优化原理 - 知乎
2. 从 MySQL 执行原理告诉你：为什么分页场景下，请求速度非常慢

性能

1. 数据统计
   • MysqlWorkBench性能分析工具--性能仪表盘,特别是耗时sql清单
2. 慢查询
   • Mysql监控执行速度慢的语句

     show variables like 'log_slow_queries';
     show variables like 'long_query_time';
     show variables like 'slow_query_log_file';
     SET GLOBAL log_slow_queries = ON;
     SET GLOBAL long_query_time = 1;
     

3. 查看mysql正在执行的SQL语句 select * from information_schema.PROCESSLIST where info is not null; // show processlist;
4. Mysql性能优化神器Explain使用
5. MySQL CPU 使用率高的原因和解决方法

四种事务隔离级的说明

• 四种事务隔离级的说明
• 语句实践 `` drop TABLEt_txtest`;

CREATE TABLE t_txtest ( id bigint(20) NOT NULL default '0', value varchar(32) default NULL, PRIMARY KEY (id) ) ENGINE=InnoDB

set session transaction isolation level read uncommitted; START TRANSACTION; SELECT * FROM t_txtest; INSERT INTO t_txtest VALUES (1, 'a'); commit; ```