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)、主从复制–异步复制原理、半同步复制和并行复制原理比较

异步复制:

image.png

半同步复制:

image.png

事务在主库写完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) 主从备份笔记

docker pull centos/mysql-57-centos7

image.png

#测试运行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’;

image.png

#启动主从同步

start slave;

image.png

#查看slave运行状态

show slave status\G

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

image.png

#下面进行测试 在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

image.png

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 主从同步

1. 利用 docker 实现 mysql 主从同步 / 读写分离

为了保证数据的完整和安全,mysql 设计了主从同步,一个挂掉还可以用另个。最近重构论坛,想来改成主从吧。担心失误,就先拿 docker 练练手。

通过本文实际你会操作到。

  1. mysql 的主从同步

  2. docker 镜像和容器的创建

  3. docker 容器间的数据传递

  4. mycat 入门

以下需要大于 100 的智商和 mysql 基础 docker 基础,linux 基础。

没有也没事,就是看着会有点吃力。

环境是 centos,mycat 是 1.6.docker 是 1.12.6

1.1 下载 mysql 镜像

docker pull mysql// ps:如果下载太慢,请添加腾讯源,依次执行echo "OPTIONS='--registry-mirror=https://mirror.ccs.tencentyun.com'" >> /etc/sysconfig/docker
systemctl daemon-reload
service docker restart

或者直接去阿里仓库下 https://dev.aliyun.com/search.html 把上面的 –registry-mirror=https://mirror.ccs.tencentyun.com 换成你的专属源就可以

下载完输入 docker images

REPOSITORY                  TAG                 IMAGE ID            CREATED             SIZE
docker.io/mysql             latest              44a8e1a5c0b2        8 days ago          407 MB

1.2 利用 mysql 镜像,创建用于主从同步的两个新镜像

我们当前所在的服务器叫宿主服务器

我们要利用 docker 虚拟两个 docker 容器服务器,一个主服务器,一个从服务器。

1.2.1 创建 master(主) 和 slave(从) 两个文件夹

/usr/mysql/master/usr/mysql/slave

1.2.2 在 master 和 slave 文件夹下 创建 Dockerfile 内容为

FROM mysql
COPY my.cnf /etc/mysql/  
EXPOSE 3306CMD ["mysqld"]

1.2.3 在 master 文件夹下 创建 my.cnf 内容为

[mysqld]log-bin=mysql-bin    //[必须]启用二进制日志server-id=1          //[必须]服务器唯一ID,默认是1,一般取IP最后一段,这里看情况分配

1.2.4 在 slave 文件夹下 创建 my.cnf 内容为

[mysqld]log-bin=mysql-bin    //[必须]启用二进制日志server-id=2          //[必须]服务器唯一ID,默认是1,一般取IP最后一段,这里看情况分配

1.2.5 切换到 master 目录下构建 master/mysql 镜像

docker build -t master/mysql .

(命令最后有个.,不要忘记,代表当前目录)

1.2.6 然后切换到 slave 目录下构建 slave/mysql 镜像

docker build -t slave/mysql .

(命令最后有个.,不要忘记,代表当前目录)

1.2.7 查看是否创建成功

[root@VM_118_220_centos ~]# docker imagesREPOSITORY                  TAG                 IMAGE ID            CREATED             SIZE
slave/mysql                 latest              8c496048d7ba        About an hour ago   407 MB
master/mysql                latest              7be30b0b631b        2 hours ago         407 MB
docker.io/mysql             latest              44a8e1a5c0b2        8 days ago          407 MB

1.3 用镜像创建容器

docker run -p 3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=mysql -d master/mysqldocker run -p 3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=mysql -d slave/mysql

1.3.1 从这里开始,建议打开两个终端窗口,方便操作

master 终端执行

docker exec -it mysql-master bash
mysql -uroot -p

输入密码 mysql 进入到 mysql 环境

slave 终端执行

docker exec -it mysql-slave bash
mysql -uroot -p

输入密码 mysql 进入到 mysql 环境

1.4 mysql 主从配置

mysql 配置

1.4.1 在主容器 mysql 中输入以下命令:

mysql>GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.99.100' IDENTIFIED BY 'mysql';(指定ip)
或者
mysql>GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'mysql';(所有ip)

然后查看主容器数据库状态:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 |     1338 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

记录 File 的值和 Position 的值。

下面要用到,到这里为止,主库千万不要再做任何操作,防止状态改变。

1.4.2 然后我们配置一下从库

查看 master/mysql 的对外端口号

docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                     NAMES
8d1e3b87d499        slave/mysql         "docker-entrypoint.sh"   2 hours ago         Up 2 hours          0.0.0.0:32769->3306/tcp   mysql-slave
980e5ea48152        master/mysql        "docker-entrypoint.sh"   2 hours ago         Up 2 hours          0.0.0.0:32768->3306/tcp   mysql-master

0.0.0.0:32768->3306/tcp 其中 32768 为 master 的端口

mysql>change master to
master_host='x.x.x.x',
master_user='user',
master_log_file='mysql-bin.000003',
master_log_pos=1201,
master_port=32768,
master_password='mysql';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

master_host=’x.x.x.x’ // 这里填 master 主机 ip

master_log_file=’mysql-bin.000003’, // 这里填写 File 的值

master_log_pos=1338,// 这里填写 Position 的值。

mysql> start slave;// 启动从服务器复制功能

如果不小心配置错, 输入 mysql> stop slave; 然后重新录入一遍

mysql>change master to
master_host='x.x.x.x',
master_user='user',
master_log_file='mysql-bin.000003',
master_log_pos=1201,
master_port=32768,
master_password='mysql';

就可以了

1.5 检查主从连接状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: user
                  Master_Port: 32768
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1338
               Relay_Log_File: 8d1e3b87d499-relay-bin.000002
                Relay_Log_Pos: 1221
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1338
              Relay_Log_Space: 1435
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: cd327a00-5e18-11e7-98f7-0242ac110006
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

这两个必须是 Yes 为 No 或者 connect 说明没有连接上

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

1.6 测试主从连接

注意设置主从后,操作只能在 master 终端上,slave 上的操作不会同步到 master 上。

1.6.1 登陆 master 终端

mysql> create database sunhao;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sunhao             || sys                |+--------------------+5 rows in set (0.00 sec)
mysql> use sunhao
Database changed
mysql>  create table sunhao(id int(3),name char(10));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into sunhao values(1,'xiaoshuai');
Query OK, 1 row affected (0.01 sec)
mysql> select * from sunhao;
+------+-----------+| id   | name      |
+------+-----------+
|    1 | xiaoshuai |+------+-----------+1 row in set (0.00 sec)

1.6.2 登陆 slave 服务器

mysql> show databases;
+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sunhao             || sys                |+--------------------+5 rows in set (0.00 sec)
mysql> use sunhao
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from sunhao;
+------+-----------+| id   | name      |
+------+-----------+
|    1 | xiaoshuai |+------+-----------+

1.6.3 如果主服务器已经存在应用数据,则在进行主从复制时,需要单独复制处理(注意此操作,如果对从服务器单独录入的数据,会被覆盖掉。):

这里我们做一次完整的测试

(1) 在主服务器数据库插入新的数据,并进行锁表操作,不让数据再进行写入动作

mysql> insert into sunhao values(2,'xiaoshuai');
Query OK, 1 row affected (0.01 sec)
mysql> select * from sunhao;                   
+------+-----------+| id   | name      |
+------+-----------+
|    1 | xiaoshuai ||    2 | xiaoshuai |
+------+-----------+
2 rows in set (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show full processlist;

(2) 退出 mysql,用 mysqldump 备份数据文件到 / var/lib, 然后顺便多余的用 tar 打包一下玩。哈哈。

mysql> exit
Bye
root@980e5ea48152:/var/lib/mysql# mysqldump -u root -p  sunhao > /var/lib/sunhao.dump    Enter password: root@980e5ea48152:/var/lib# tar -zcvf ./sunhao.dump .tar  ./sunhao.dump

(3) 打开宿主服务器,复制 mysql 主服务器文件 sunhao.dump.tar。到宿主服务器

[root]# docker ps CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                     NAMES8d1e3b87d499        slave/mysql         "docker-entrypoint.sh"   4 hours ago         Up 4 hours          0.0.0.0:32769->3306/tcp   mysql-slave980e5ea48152        master/mysql        "docker-entrypoint.sh"   4 hours ago         Up 4 hours          0.0.0.0:32768->3306/tcp   mysql-master
mkdir -p /var/mydata
docker cp 980e:/var/lib/sunhao.dump.tar /var/mydata/
cd /var/mydata
[root@VM_128_230_centos mydata]# lssunhao.dump.tar

解释: docker cp 980e:/var/lib/sunhao.dump.tar /var/mydata/

980e 是 980e5ea48152 的缩写。保证不重复简写就可以

980e:/var/lib/sunhao.dump.tar 意思就是选中的容器下面你的文件下的 sunhao.dump.tar 文件

/var/mydata/ 目标文件

(4) 在宿主主机上输入

[root]# docker cp /var/mydata/sunhao.dump.tar 8d1e3b87d49936414c0e91ffdc60a054e7f4ef8b15baee295350565519abba0e:/var/lib/

(5) 在从服务器上输入

root@8d1e3b87d499:/# cd /var/lib/root@8d1e3b87d499:/var/lib# tar -zcvf sunhao.dump.tar

(6) 在从服务器上输入

tar -zxvpf /var/lib/mysqlbak.sql.tar
mysql -uroot -pmysql sunhao < /var/lib/sunhao.dump;

如果写入成功, 这个时候就可以用 select 查询到数据。

(2) 取消主服务器数据库锁定

mysql> UNLOCK TABLES;

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)

 

mysql替换指定位置内容

方便的替换掉expiry date 而不影响其他内容

select 
concat(
substring(remarks , 1, LOCATE('Expiry Date: ', remarks)+CHAR_LENGTH('Expiry Date: ') -1),
'31-05-2020
'
,substring(remarks , LOCATE('Partner Ref No', remarks), CHAR_LENGTH(remarks) )
) as remarks
from voucher_action where id = 3359116

image.png

CRM API submit and resubmit by VMS Verification

#first CRM API submit and VMS resubmit or reimbruse
select concat(a.prefix, a.voucher_text, a.suffix), b.* from voucher a, (
	select a.submit_time, b.action_id, a.voucher_id from (
		select min(a.action_time) as submit_time, a.voucher_id 
		from voucher_action a, voucher b 
		where a.action_id = 60 and a.user_id = 3324 
		and a.voucher_id = b.voucher_id
		group by a.voucher_id
	) a left join voucher_action b on (a.voucher_id = b.voucher_id and b.action_id in (70) and b.user_id != 3324)
	where b.id is not null
	
) b where a.voucher_id = b.voucher_id

mysql直接copy数据文件 导致Table doesn’t exist的解决办法

服务器迁移后把原来的mysql data复制进去后大部分表是可以访问的,但是有几个表提示表不存在:

error: 1146: Table 'a_content' doesn't exist

这种情况就是要把原来mysql安装目录data里的 ibdata1 也要拷贝过去

INNODB是MYSQL数据库一种流行的数据库引擎,支持事务(行级),在企业级应用上成为可能

ibdata用来储存文件的数据,而库名的文件夹里面的那些表文件只是结构而已,由于新版的mysql默认试innodb,所以ibdata1文件默认就存在了,少了这个文件有的数据表就会出错。

mysql导出数据库前查看不需要导出的表

今天从staging上导lendlease的库的时候发现有的表需要很长时间,后开一看system_logs尽然7个多G

mac上的Sequel Pro又不像windows上heidisql那样可以看到每个表的大小

所以需要自己查一下 看看是不是又不需要的表 而且又特别的大

SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'crm_lendlease' ORDER BY DATA_LENGTH DESC, TABLE_ROWS DESC

image.png