MySQL中使用Optimize优化表

OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用了OPTIMIZE TABLE命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,而非数据库)。多数时间并不需要运行OPTIMIZE TABLE,只需在批量删除数据行之后,或定期(每周一次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行,这个操作对于游戏数据库中的某些表特别起作用,这些表基本上需要每周做一次优化,甚至一周两次。如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的 ......

设置MySQL为只读模式

在MySQL数据库中,在进行数据迁移和从库只读状态设置时,都会涉及到只读状态和Master-slave的设置和关系。对于MySQL单实例数据库和master库,如果需要设置为只读状态,需要进行如下操作和设置:mysql> show global variables like "%read_only%"; mysql> flush tables with read lock; mysql> set global read_only=1; mysql> show global variables like "%read_only% ......

完整清除Slave同步信息

mysql> stop slave; Query OK, 0 rows affected (0.19 sec) mysql> reset slave; Query OK, 0 rows affected (0.17 sec) 执行reset slave,其实是把master.info和relay-log.info文件给删除,但里面的同步信息还在,使用reset slave all; 可以全部清除;mysql> reset slave all; Query OK, 0 rows affected (0.04 sec) mysql> show sla ......

MHA切换过程中需要注意的问题

1.切换过程会自动把read_only关闭2.切换之后需要删除手工删除/masterha/app1/app1.failover.complete,才能进行第二次测试3.一旦发生切换管理进程将会退出,无法进行再次测试,需将故障数据库加入到MHA环境中来4.原主节点重新加入到MHA时只能设置为slave,在之前需要先 reset slaveRESET SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.121.165',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='xxxxxx',MAS ......

MHA报错记录整理

masterha_check_repl--conf=/etc/masterha/default.cnf Global configuration file/etc/masterha_default.cnf not found. Skipping. [info] Reading application default configuration from/etc/masterha/app1.cnf.. [info] Reading server configuration from/etc/masterha/default.cnf.. [info] MHA::Master ......

CentOS7.4搭建MHA环境

MHA(Master HA)是一款开源的MySQL的高可用工具,能在MySQL主从复制的基础上,实现自动化主服务器故障转移。虽然MHA试图从宕机的主服务器上保存二进制日志,但并不是总是可行的。例如,如果主服务器硬件故障或无法通过ssh访问,MHA没法保存二进制日志,只进行故障转移而丢失最新数据。 MHA监控复制架构的主服务器,一旦检测到主服务器故障,就会自动进行故障转移。即使有些从服务器没有收到最新的relay log,MHA自动从最新的从服务器上识别差异的relay log并把这些日志应用到其他从服务器上,因此所有的从服务器保持一致性了。还可以设置优先级指定其中的一台slave ......

MySQL的show_compatibility_56参数

5.7以后System and status 变量需要从performance_schema中进行获取,information_schema仍然保留了GLOBAL_STATUS,GLOBAL_VARIABLES两个表做兼容,如果希望沿用information_schema中进行查询的习惯,5.7提供了show_compatibility_56参数,设置为ON可以兼容5.7之前的用法,如果为OFF,查询information_schema库的global_variables时报错:mysql> use information_schema mysql> select * ......

MySQL变量的查看和设置

类似于Oracle的参数文件,MySQL的选项文件(如my.cnf)用于配置MySQL服务器,MySQL的变量分为以下两种:1)系统变量:配置MySQL服务器的运行环境,可以用show variables查看2)状态变量:监控MySQL服务器的运行状态,可以用show status查看系统变量系统变量按其作用域的不同可以分为以下两种:1)分为全局(GLOBAL)级:对整个MySQL服务器有效2)会话(SESSION或LOCAL)级:只影响当前会话有些变量同时拥有以上两个级别,MySQL将在建立连接时用全局级变量初始化会话级变量,但一旦连接建立之后,全局级变量的改变不会影响到会话级变量。

MySQL数据库查看数据表占用空间大小和记录数

MySQL数据库中每个表占用的空间、表记录的行数的话,可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表,这个表主要字段分别是:TABLE_SCHEMA : 数据库名 TABLE_NAME:表名 ENGINE:所使用的存储引擎 TABLES_ROWS:记录数 DATA_LENGTH:数据大小 INDEX_LENGTH:索引大小一个表占用空间的大小,相当于是 数据大小 + 索引大小,示例:1、查看enrolment_db库的所有表大小:select table_name,table_rows from tables where TA ......

MySQL存储过程创建、查看、调用及参数介绍

存储过程(Stored Procedure): 一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。优点(为什么要用存储过程?):  将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用  批量处理:SQL+循环,减少流量,也就是“跑批”  统一接口,确保数据的安全相对于oracle数据库来说,MySQL的存储过程相对功能较弱,使用较少。一、存储过程的创建和调用  >存储过程就是具有名字的一段代码,用来完成一个特定的功能。  >创建的存储过程保存在数据库的数据字 ......

MySQL中group_concat长度限制

GROUP_CONCAT参数默认值是1024,拼接的字符串的长度字节超过1024 则会被截断,数据量大的时候需要修改下字符大小:查看group_concat 默认的长度:mysql> show variables like 'group_concat_max_len'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | group_concat_max_len | 1024 | +----------------------+------ ......

MySQL使用MySQLdump导出所有库

导出所有数据库 /usr/local/mysql/bin/mysqldump -u用户名 -p密码 --lock-all-tables --all-databases > /tmp/all.sql 导入数据库 /usr/local/mysql/bin/mysqldu -u用户名 -p密码 < /tmp/all.sql导出指定库,排除不导出的库#!/bin/bash USER="用户名" PASSWORD="密码" databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATAB ......

MySQL UNIX时间戳与日期的相互转换

UNIX时间戳转换为日期用函数FROM_UNIXTIME()mysql> select FROM_UNIXTIME(1512621000); +---------------------------+ | FROM_UNIXTIME(1512621000) | +---------------------------+ | 2017-12-07 12:30:00 | +---------------------------+ 1 row in set (0.00 sec)日期转换为UNIX时间戳用函数UNIX_TIMESTAMP()mysql> Selec ......

MySQL的sql_mode说明

sql_mode直接理解就是:sql的运作模式。官方的说法是:sql_mode可以影响sql支持的语法以及数据的校验执行,这使得MySQL可以运行在不同的环境中以及和其他数据库一起运作。 MySQL5.7.4之后的版本和之前的版本的默认sql_mode有一个比较大的改变。 直接在my.cnf配置:sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'在启动的时候带参数--s ......

MySQL冥等模式slave_exec_mode

线上系统DB在MySQL 5.6.12下面的主从环境中经常出现1023错误(记录没找到)、1062错误(主键重复),即使重做主从,过不来多久又会出现,开启slave_exec_mode的idempotent模式:set global slave_exec_mode='idempotent'; stop slave; start slave; show slave status\G;默认值是strict(严格模式) 设置完毕之后,并不能立即生效,需要重启下复制进程,观察一段时间在没有出现1023、1062等主从同步问题;在MySQL5.7.18和5.7.20下面的主从也是基于GTI ......

MySQLbinlog 查看binlog时报错unknown variable 'default-character-set=utf8'

mysqlbinlog是用来查看binlog文件内容的(使用方式man mysqlbinlog查看),但是使用mysqlbinlog将binlog文件转换成人类可读的内容时却报错:mysqlbinlog: unknown variable 'default-character-set=utf8'原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令,直接带上--no-defaults参数即可,/usr/local/mysql/bin/mysqlbinlog --no-defaults -vv --base6 ......

Mysql 8小时内无请求自动断开连接解决方法

Mysql服务器默认的“wait_timeout”是8小时,也就是说一个connection空闲超过8个小时,Mysql将自动断开该 connection。这就是问题的所在,在Hibernate默认连接池中的connections如果空闲超过8小时,Mysql将其断开,而Hibernate默认连接池并不知道该connection已经失效,如果这时有 Client请求connection,Hibernate默认连接池将该失效的Connection提供给Client,将会造成上面的异常。

修改sync_binlog innodb_flush_log_at_trx_commit和sync_binlog参数 提高写入速度

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数。innodb_flush_log_at_trx_commit如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行,该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写 ......