道行尚浅的道场

MySQL主从1032错误详解

错误模拟查询主库从库数据主库 mysql> select * from test.t1 ; +----+------+ | c1 | c2 | +----+------+ | 1 | 2 | | 2 | 3 | +----+------+ 2 ro...

Posted by 道行尚浅 on August 9, 2018

使用innobackupex在线搭建级联复制

架构图如下查看主库配置文件 [mysql@node1 mysql1]$ cat /mysql1/mysql1.cnf [client] port = 3306 socket = /tmp/mysql1.sock [mysqld] port = 3306 socket = /tmp...

Posted by 道行尚浅 on August 8, 2018

ＭySQL主从结构转换-中继转一主两从

目标架构图如下转换为一级从库(node2)停止复制 mysql> stop slave ; Query OK, 0 rows affected (0.01 sec) 查看一级从库(node2)的状态 mysql> show master st...

Posted by 道行尚浅 on August 8, 2018

使用innobackupex在线搭建一主多从

查看主库配置文件 [mysql@node1 mysql1]$ cat /mysql1/mysql1.cnf [client] port = 3306 socket = /tmp/mysql1.sock [mysqld] port = 3306 socket = /tmp/mysql1.s...

Posted by 道行尚浅 on August 7, 2018

使用mysqldump在线搭建主从

查看主库配置文件 [mysql@node1 mysql1]$ cat /mysql1/mysql1.cnf [client] port = 3306 socket = /tmp/mysql1.sock [mysqld] port = 3306 socket = /tmp/mysql1.s...

Posted by 道行尚浅 on August 7, 2018

使用innobackupex在线搭建主从

查看主库配置文件 [mysql@node1 mysql1]$ cat /mysql1/mysql1.cnf [client] port = 3306 socket = /tmp/mysql1.sock [mysqld] port = 3306 socket = /tmp/mysql1.s...

Posted by 道行尚浅 on August 7, 2018

使用innobackupex进行Mysql备份恢复-增量备份

备份部分建立测试表并插入标识数据 mysql> create table t1 ( c1 int) ; Query OK, 0 rows affected (0.07 sec) mysql> insert into t1 values (1) ; Query OK, 1 ...

Posted by 道行尚浅 on August 4, 2018

SQL笔试题-在职员工数量

表结构与内容如下员工名字入职日期合同签订年限 ename hiredate hire_years SMITH 1980-...

Posted by 道行尚浅 on August 1, 2018

使用innobackupex进行Mysql备份恢复-全量备份

综述 Xtrabackup 是Percona 公司推出的备份恢复工具.，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁); 最新版本下载地址 Xtrabackup 安装成功后包含四个文件 xtrabackup 专门用来...

Posted by 道行尚浅 on July 28, 2018

SQL笔试题-百元买百鸡

题目已知: 母鸡5元1只公鸡3元1只小鸡1元3只现在有100元要买100只鸡请问有几种可能? 解答 SQL> with dao_temp as 2 (select level - 1 c1 from dual connect by level ...

Posted by 道行尚浅 on July 27, 2018

MySQL Binlog概念与实战

MySQL Binlog的作用 MySQL binlog起始是binary log 的缩写,翻译成中文就是MySQL二进制日志,以二进制形式记录了对于数据库潜在的变更的操作. binlog的应用主要存在于以下两个方面复制备份恢复 binlog相关参数的说明 ...

Posted by 道行尚浅 on July 25, 2018

MySQL5.7传输表空间实战

概述在MySQL中我们可以使用可传输表空间将表从一个数据库导入到另一个数据库. 可传输表空间使用注意事项必须开启 innodb_file_per_table 当这个表处于quiesced状态,甚至不能被select 两边实例的page size 一致 -5.7 版本之前，不...

Posted by 道行尚浅 on July 24, 2018

MySQL5.7 停机迁移

查看当前Mysql环境 mysql> show variables like '%dir%' ; +-----------------------------------------+----------------------------------+ | Variable_na...

Posted by 道行尚浅 on July 24, 2018

MySQL5.7 重置root密码

跳过验证 [mysql@bosenrui ~]$ cat /mysql/my.cnf [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-grant-...

Posted by 道行尚浅 on July 22, 2018

MySQL redo管理实践

redo 文件的作用 Redo log是一个基于磁盘文件的数据结构,用以在实例恢复时校正由未完成事务写入的数据文件. 当需要改变某个页的数据时,InnoDB先将这个页加载到buffer pool中,在buffer pool中进行数据的修改,并将修改行为记录到redo log buffer中...

Posted by 道行尚浅 on July 20, 2018

Buffer_pool热数据转储特性

从Mysql 5.6开始，提供InnoDB buffer的预热功能　该特性在5.7中得到加强下面来看下几个相关参数参数名作用 innodb_buffer_pool_dump_at_shutdown ...

Posted by 道行尚浅 on July 3, 2018

MySQL5.7 安装

创建文件夹 [root@dao ~]# mkdir /usr/local/mysql [root@dao ~]# mkdir -p /data/mysql3306/data [root@dao ~]# mkdir -p /data/mysql3306/logs 解压并查看 [roo...

Posted by 道行尚浅 on June 29, 2018

MySQL5.6安装

创建文件夹 [root@dao ~]# mkdir /usr/local/mysql [root@dao ~]# mkdir -p /data/mysql3306/data [root@dao ~]# mkdir -p /data/mysql3306/logs 安装依赖包 [root...

Posted by 道行尚浅 on June 29, 2018

MySQL 删除薪水高于所在平均薪水的员工信息

临时表删除法查看数据 mysql> select * from emp1 ; +-------+--------+-----------+------+---------------------+------+------+--------+ | empNO | ENAME |...

Posted by 道行尚浅 on May 24, 2018

MySQL删除重复记录并保留一条

今天有同学笔试遇到这样的问题,我就顺便总结下. 原数据是经典的emp表 mysql> select * from emp1 ; +-------+--------+-----------+------+---------------------+------+------+----...

Posted by 道行尚浅 on May 22, 2018