mysql – LeoKim's Blog

Mysql 主从同步相关杂记

leokim / 2020年8月27日2020年8月27日 / MYSQL

mysql主从同步加速

1、sync_binlog在slave端设置为0

2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

3、直接禁用slave端的binlog

4、slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

从文件系统本身属性角度优化

master端修改linux、Unix文件系统中文件的etime属性，由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性，组织操作系统写atime信息，在linux上的操作为：打开/etc/fstab，加上noatime参数/dev/sdb1 /data reiserfs noatime 1 2然后重新mount文件系统#mount -oremount /data

同步参数调整主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率。

1、sync_binlog=1 oMySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。

对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是2或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。默认情况下，并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能binlog中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog写入后与硬盘同步。即使sync_binlog设置为1,出现崩溃时，也有可能表内容和binlog内容之间存在不一致性。

2、innodb_flush_log_at_trx_commit （这个很管用）抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

3、ls(1) 命令可用来列出文件的 atime、ctime 和 mtime。

atime 文件的access time 在读取文件或者执行文件时更改的ctime 文件的create time 在写入文件，更改所有者，权限或链接设置时随inode的内容更改而更改mtime 文件的modified time 在写入文件时随文件内容的更改而更改ls -lc filename 列出文件的 ctimels -lu filename 列出文件的 atimels -l filename 列出文件的 mtimestat filename 列出atime，mtime，ctimeatime不一定在访问文件之后被修改因为：使用ext3文件系统的时候，如果在mount的时候使用了noatime参数那么就不会更新atime信息。这三个time stamp都放在 inode 中.如果mtime,atime 修改,inode 就一定会改, 既然 inode 改了,那ctime也就跟着改了.之所以在 mount option 中使用 noatime, 就是不想file system 做太多的修改, 而改善读取效能

MySql数据库从库同步其他问题及解决方案

1)、mysql主从复制存在的问题：

● 主库宕机后，数据可能丢失

● 从库只有一个sql Thread，主库写压力大，复制很可能延时

2)、解决方法：

● 半同步复制—解决数据丢失的问题

● 并行复制—-解决从库复制延迟的问题

3)、半同步复制mysql semi-sync（半同步复制）半同步复制：

● 5.5集成到mysql，以插件的形式存在，需要单独安装

● 确保事务提交后binlog至少传输到一个从库

● 不保证从库应用完这个事务的binlog

● 性能有一定的降低，响应时间会更长

● 网络异常或从库宕机，卡主主库，直到超时或从库恢复

4)、主从复制–异步复制原理、半同步复制和并行复制原理比较

异步复制：

半同步复制：

事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端；

5.5集成到mysql，以插件的形式存在，需要单独安装确保事务提交后binlog至少传输到一个从库不保证从库应用完成这个事务的binlog性能有一定的降低网络异常或从库宕机，卡主库，直到超时或从库恢复

c、并行复制mysql并行复制

● 社区版5.6中新增

● 并行是指从库多线程apply binlog

● 库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置set global slave_parallel_workers=10;设置sql线程数为10

原理：从库多线程apply binlog在社区5.6中新增库级别并行应用binlog，同一个库数据更改还是串行的5.7版本并行复制基于事务组

Docker MySQL (Master-Slave Replication) 主从备份笔记

leokim / 2020年8月25日 / MYSQL, 数据库

docker pull centos/mysql-57-centos7

#测试运行mysql容器

docker run \
-e MYSQL_ROOT_PASSWORD=test123123  \
-p 33306:3306  \
-tdi  \
centos/mysql-57-centos7

#创建并进入目录

/Users/leokim/Documents/mysql_connfig

#复制容器的cnf文件到宿主机

docker cp 904908d877c9:/etc/opt/rh/rh-mysql57/my.cnf ./

#修改my.cnf 添加配置参数

log-bin = mysql-bin
server-id = 1

#复制my.cnf生成my.slave.cnf, 修改my.slave.cnf 配置参数

log-bin = mysql-bin
server-id = 2

#停止测试容器

docker stop 904908d877c9

#删除之前打开并停止的容器

docker rm $(docker ps -aq)

#运行master

docker run --name mysql_master \
-e MYSQL_ROOT_PASSWORD=test123123  \
-p 33306:3306  \
-v /Users/leokim/Documents/mysql_connfig/my.cnf:/etc/opt/rh/rh-mysql57/my.cnf \
-tdi  \
centos/mysql-57-centos7

#运行slave

docker run --name mysql_slave \
-e MYSQL_ROOT_PASSWORD=test123123  \
-p 33307:3306  \
-v /Users/leokim/Documents/mysql_connfig/my.slave.cnf:/etc/opt/rh/rh-mysql57/my.cnf \
-tdi  \
centos/mysql-57-centos7

#查看容器ip

docker inspect --format '{{ .NetworkSettings.IPAddress }}’ xxxxx

#在master中设定slave user

GRANT REPLICATION SLAVE ON *.* TO 'user'@'
172.17.0.3
' IDENTIFIED BY 'mysql';（指定ip）

或

GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'mysql';

#在master里查看master_host & master_log_pos

show master status;

#登录slave mysql (mysql -uroot -p), 在slave的mysql运行

change master to
master_host='172.17.0.2',
master_user='user',
master_log_file='mysql-bin.000003',
master_log_pos=704,
master_port=3306,
master_password='mysql’;

#启动主从同步

start slave;

#查看slave运行状态

show slave status\G

Slave_IO_Running 和 Slave_SQL_Running 都为Yes 说明主从配置成功

#下面进行测试在master创建数据库并创建表，在slave下观察是否可以看到

主从配置设置完后要注意主从没有生效的时候，要保持两台服务器数据库表相同，数据相同

#当主从同步有差异时，登录master

先在master生成测试数据

=============================生成测试数据===============================

-- 创建一个表，用作模拟数据的测试用例
DROP TABLE IF EXISTS `vote_records`;
CREATE TABLE `vote_records` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
    `create_time` datetime  NOT NULL DEFAULT '1971-01-01 01:01:01' COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票记录表';

-- 为了数据的随机性和真实性，我们需要创建一个可生成长度为n的随机字符串的函数。
-- 创建生成长度为n的随机字符串的函数
DELIMITER $$ -- 修改MySQL delimiter：'$$'
DROP FUNCTION IF EXISTS `rand_strings` $$
SET NAMES utf8 $$
CREATE FUNCTION `rand_strings` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN
    DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
        SET i = i+1;
    END WHILE;
    RETURN return_str;
END $$

-- 为了操作方便，我们再创建一个插入数据的存储过程
-- 创建插入数据的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_vote_records $$
CREATE PROCEDURE insert_vote_records (IN n INT )
    BEGIN
        DECLARE i INT DEFAULT 1;
        DECLARE vote_num INT DEFAULT 0;
        DECLARE group_id INT DEFAULT 0;
        DECLARE status TINYINT DEFAULT 1;
        WHILE i <= n DO
            SET vote_num = FLOOR(1 + RAND() * 10000);
            SET group_id = FLOOR(0 + RAND()*3);
            SET status = FLOOR(1 + RAND()*2);
            INSERT INTO `vote_records` VALUES (NULL, rand_strings(20), vote_num, group_id, status, NOW());
            SET i = i + 1;
        END WHILE;
    END $$
DELIMITER ;

-- 开始执行存储过程，等待生成数据(10000条)
-- 调用存储过程 生成10000条数据
CALL insert_vote_records(10000);

-- 查询普通表已的生成记录
SELECT count(*) FROM `vote_records`;

===================生成测试数据结束==============

whereis mysqldump;
/opt/rh/rh-mysql57/root/usr/bin/mysqldump -uroot -p --all-databases >~/all_database.sql

docker cp 8a6c146e07db:/var/lib/mysql/all_database.sql ./
docker cp ./all_database.sql 39e1088d07e4:/var/lib/mysql/

mysql -u root -p < all_database.sql

#查看slave 已经有了测试数据

change master to
master_host='172.17.0.2',
master_user='user',
master_log_file='mysql-bin.000003',
master_log_pos=17655993,
master_port=3306,
master_password='mysql’;
start slave;

mysql按字段分组，取最大值记录

leokim / 2020年4月9日 / MYSQL

要求：获得按table1_id分组，并且age最大的记录信息，即2、3、5条

方法一：

select * from (select * from table2 order by age desc) as a group by a.table1_id

方法二：

select a.* from table2 as a where age = (select max(age) from table2 where a.table1_id=table1_id)

方法三：

select a.* from table2 as a where not exists (select * from table2 where table1_id=a.table1_id and age>a.age)

方法四：

select a.* from table2 as a where exists (select count(*) from table2 where table1_id=a.table1_id and age>a.age having count(*)=0)

php插入数组保留换行格式到mysql

leokim / 2019年11月19日 / MYSQL

'api_response'=>var_export($this->response_json, true),

mysql 检查表是否存在

leokim / 2019年11月19日2019年11月19日 / MYSQL

        //check back table exists
        $back_db = $this->_cust_bak_db;
        $table = 'voucher_'.date('Ym',strtotime($month));

        $sql = "select count(*) as cnt 
        from `INFORMATION_SCHEMA`.`TABLES` 
        where `TABLE_SCHEMA`='$back_db' and `TABLE_NAME`='$table'";

mysql 运算符，:=，@，@@

leokim / 2019年11月19日 / MYSQL

1、<=>

安全比较运算符，用来做 NULL 值的关系运算。

因为 mysql 的 NULL 值的特性，任何值和其比较的结果都是 NULL， 1 = NULL，1 <> NULL / 1 != NULL 得到的结果都是 NULL。

SELECT 1 = NULL, 1 <> NULL, 1 != NULL;
+----------+-----------+-----------+
| 1 = NULL | 1 <> NULL | 1 != NULL |
+----------+-----------+-----------+
|     NULL |      NULL |      NULL |
+----------+-----------+-----------+
1 row in set (0.00 sec)

当然我们可以用 IS NULL 去判断，即

SELECT 1 IS NULL, 1 IS NOT NULL, NOT (1 IS NULL), !(1 IS NULL);
+-----------+---------------+-----------------+--------------+
| 1 IS NULL | 1 IS NOT NULL | NOT (1 IS NULL) | !(1 IS NULL) |
+-----------+---------------+-----------------+--------------+
|         0 |             1 |               1 |            1 |
+-----------+---------------+-----------------+--------------+
1 row in set (0.00 sec)

但用 <=> 更为简洁

SELECT 1 <=> NULL, !(1 <=> NULL);
+------------+---------------+
| 1 <=> NULL | !(1 <=> NULL) |
+------------+---------------+
|          0 |             1 |
+------------+---------------+
1 row in set (0.00 sec)

2、:=

:= 和 = 运算符在大部分场景下并无区别，但 := 更为全场景些。

= 只有在 set 和update时才是和 := 一样，赋值的作用，其它都是关系运算符等于的作用。

:= 不只在 set 和 update 时赋值的作用，在 select 也是赋值的作用。

SET @name = 'big_cat';

SELECT @name;

# = 在 select 语句中成为了比较运算符结果为 NULL (@name 为 NULL, 在 mysql 中 NULL 和任何值比较都为 NULL)

# := 则为仍未赋值，@name_defined 被赋值为 big_cat 后再 select 就出来了

SELECT @name = 'big_cat', @name_defined := 'big_cat', @name_defined;
+-------------------+----------------------------+---------------+
| @name = 'big_cat' | @name_defined := 'big_cat' | @name_defined |
+-------------------+----------------------------+---------------+
|              NULL | big_cat                    | big_cat       |
+-------------------+----------------------------+---------------+
1 row in set (0.00 sec)

3、@ 用户变量

@用来标识用户变量

SET @name = "big_cat";

SELECT 'big_cat' INTO @name;

SELECT @name := 'big_cat';

4、@@系统变量

系统变量又分为全局系统变量和会话系统变量

读取系统变量

SELECT @@global.sort_buffer_size;

SELECT @@session.sort_buffer_size;

SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';

SHOW SESSION VARIABLES LIKE 'sort_buffer_size';

设置系统变量

SET @@global.sort_buffer_size = 2 * 1024 * 1024;

SET @@session.sort_buffer_size = 2 * 1024 * 1024;

SET GLOBAL sort_buffer_size = 2 * 1024 * 1024;

SET SESSION sort_buffer_size = 2 * 1024 * 1024;

有些系统变量只有全局级的，比如 max_connnections，读取时可以不显示声明 global，但设置时需要，否则会提示你设置的为全局变量。

mysql设置数据库编码

leokim / 2019年11月19日 / MYSQL

show variables where Variable_name like '%collation%'  

select @@character_set_database

alter database vms_wisma character set utf8; 

alter database vms_wisma character set latin1;

MySQL 事务，锁

leokim / 2017年6月10日 / MYSQL

ACID

Atomic同一个事务里，要么都提交，要么都回滚
Consistency即在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏
Isolation 并发事务间的数据是彼此隔离的
Durabiliy 事务提交后，所有结果务必被持久化

支持事务：InnoDB，NDBCluster， TokuDB

不支持事务：MyISAM，MEMORY

隔离性通过锁实现
原子性，一致性，持久性通过数据库的redo和undo来完成

撤销日志： UNDO LOG （现在正在活跃的事务，但还没有提交，或者还没有完成check point）

重做日志：REDO LOG （已经提交的事务）

REDO 和 UNDO 在做数据库恢复的时候的作用是相反的，

RODO：在做数据库恢复的时候，如果有事务还没有真正的提交，那么需要前滚（扫描RODO LOG检查哪些事务已经提交了，但还没有持久化到数据文件【table spacs， data table spage】里的，再把这些重做一次事务【再重新提交一次】）

UNDO：正好是相反找到还没有提交的事务但是再undo log里需要进行一次回滚，就是把已经提交的事务回滚掉

在做数据库恢复的时候要做这样的一个过程

autocommit = 0|1

设置事务自动开启|关闭

显示事务启动 | 关闭

以START TRANSACTION/BEGIN开始事务
以COMMIT/ROLLBACK TRANSACTION结束事务

隐性事务提交

主要是DDL,DCL会引发事务的隐性提交

SQL四种语言：DDL,DML,DCL,TCL

MySQL 多列索引的生效规则

leokim / 2017年6月9日2017年6月9日 / MYSQL

mysql中 myisam，innodb默认使用的是 Btree索引，至于btree的数据结构是怎样的都不重要，
只需要知道结果，既然是索引那这个数据结构最后是排好序；就像新华字典他的目录就是按照a,b,c..这样排好序的；
所以你在找东西的时候才快，比如你找 “中” 这个字的解释，你肯定就会定位到目录的 z 开头部分；

组合索引可以这样理解，比如（a,b,c），abc都是排好序的，在任意一段a的下面b都是排好序的，任何一段b下面c都是排好序的；

组合索引的生效原则是从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
比如

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果
where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

（a,b,c）三个列上加了联合索引（是联合索引不是在每个列上单独加索引）

还需注意， (a,b,c)多列索引和 (a,c,b)是不一样的，看上面的图也看得出来关系顺序是不一样的；
分析几个实际例子来加强理解；
分析句子中使用的索引情况

select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用

select * from mytable where  c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的

select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果

select * from mytable where a>4 and b=7 and c=9;
a用到了  b没有使用，c没有使用

select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的

select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果