分类目录归档：MySQL

MySQL UPDATE JOIN

JOIN似乎不太满足这里的需求子查询更好

UPDATE novel 
SET 
    chat_num = (SELECT 
            COUNT(*)
        FROM
            chats
        WHERE
            novel_id = ? AND status = 1)
WHERE
    id = ?

UPDATE JOIN 似乎无法使用limit

UPDATE pay_record
        JOIN
    article_info ON article_info.id = pay_record.object_id 
SET 
    novel_id = special_id
WHERE
    ISNULL(novel_id) AND pay_record.id = 17;

MySQL类型不匹配导致的慢查询

这是个慢查询

SELECT *, `user_id` as uid
FROM (`journals`)
WHERE `user_id` IN ('1456870', 2573141) -- 字符串
AND `status` =  1
ORDER BY `id` desc
LIMIT 20

这个则不是

SELECT *, `user_id` as uid
FROM (`journals`)
WHERE `user_id` IN (1456870, 2573141) -- 数字
AND `status` =  1
ORDER BY `id` desc
LIMIT 20

用php监控从库同步延迟个数 

<?php
$mysqli = new mysqli('slave_host', 'root', 'passwd', 'dbname');

/*
 * This is the "official" OO way to do it,
 * BUT $connect_error was broken until PHP 5.2.9 and 5.3.0.
 */
if ($mysqli->connect_error) {
 die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

/*
 * Use this instead of $connect_error if you need to ensure
 * compatibility with PHP versions prior to 5.2.9 and 5.3.0.
 */
if (mysqli_connect_error()) {
 die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

echo 'Success... ' . $mysqli->host_info . "\n";

/* Create table doesn't return a resultset */
/**
 * 
 * @var mysqli_result $result
 */
$result = $mysqli->query("show slave status");

if ($mysqli->errno) {
 printf("Error number: %s\n", $mysqli->errno);
 printf("Error message: %s\n", $mysqli->error);
}

if ($result !== false) {
 $row = $result->fetch_assoc();
 echo "master : {$row['Master_Log_File']}\n";
 echo "slave relay : {$row['Relay_Master_Log_File']}\n";
 echo " : " . ($row['Read_Master_Log_Pos'] - $row['Exec_Master_Log_Pos']);
}

$mysqli->close();

MYSQL远程直接导入数据库方法

mysqldump -uroot -pxxx -hmaster nimei –single-transaction –default-character-set=utf8mb4 | mysql –default-character-set=utf8mb4 -uroot -pxxx -h127.0.0.1 nimei

注意 –default-character-set 别丢了，否则emoji丢失！

13.4.2.1 CHANGE MASTER TO Syntax 

HANGE MASTER TO option [, option] ...

option:
    MASTER_BIND = 'interface_name'
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = interval
  | MASTER_RETRY_COUNT = count
  | MASTER_DELAY = interval
  | MASTER_HEARTBEAT_PERIOD = interval
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | MASTER_AUTO_POSITION = {0|1}
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_CRL = 'crl_file_name'
  | MASTER_SSL_CRLPATH = 'crl_directory_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | IGNORE_SERVER_IDS = (server_id_list)

server_id_list:
    [server_id [, server_id] ... ]

STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication

实例：

stop slave;
CHANGE MASTER TO
 MASTER_HOST = 'master',
 MASTER_USER = 'repl',
 MASTER_PASSWORD = 'xxxxxxxx',
 MASTER_PORT = 3306,
 MASTER_LOG_FILE ='mysql-bin.005029',
 MASTER_LOG_POS =0;
set global replicate_do_db = 'doufu';
set global replicate_ignore_db ='mysql,information_schema,performance_schema';
start slave;
show slave status;
show variables like '%Replicate%';

pt-online-schema-change 在线热修改表，不锁表

连接到master上执行

pt-online-schema-change --user=root --password=xxxxx--host=master D=doufu,t=user_comment --execute --alter "ADD COLUMN content_image text NULL COMMENT '' AFTER like_uids" --nocheck-replication-filters

选项说明：
–user=root 用户名
–password=xxxxx 密码
–host=master 主机ip或域名
D=doufu,t=user_comment 数据库名和表明
–execute 不加此参数不会真的执行
–alter “ADD COLUMN content_image text NULL COMMENT ” AFTER like_uids” 需要执行的修改操作
–nocheck-replication-filters 如果有主从结构必须加此参数才能运行， 必须在主库上执行

 

Waiting for query cache lock

原文地址
http://250688049.blog.51cto.com/643101/1560425
Mysql InnoDB 引发 Waiting for query cache lock
标签：Mysql InnoDB Waiting
线上数据库中，如果是InnoDB的话，配备足够的innodb buffer pool后，就把query cache关闭掉（query_cache_size 和 query_cache_type 同时设置为 0），大量的更新+查询时，更容易引发 Waiting for query cache lock。

。。。

— show variables like ‘query_cache%’;
— set global query_cache_type=0;
— set global query_cache_size=0;
— show variables like ‘%slow%’;

pt-table-sync 修复同步数据一致问题

修复方向master向从库同步，由于是一主一从所以这个方向没问题，以master的数据为准：

pt-table-sync --print --execute h=master,D=database_name,t=table_name,u=root,p=password h=web3 --no-check-slave --lock=1 --charset=utf8mb4

参数格式

pt-table-sync DNS1 DNS2 DNS3

DNS1是源数据库
后面的都是目标数据库
DNS格式说明：D数据 u用户名 p密码 t表名
上面的例子只有一个目标数据修复单个表
也可以修复整个数据库：

pt-table-sync --print --execute h=master,D=database_name,u=root,p=password h=web3 --no-check-slave --lock=1 --database=database_name --charset=utf8mb4

–no-check-slave 主向从同步需要这个参数，否则只能从向主同步，因为如果有多个从库可能会导致新的不一致
–print 打印出来(实际运行时请去掉这参数 否则遇到特殊字符时会导致修复中断)
–execute 真的运行，去掉这个参数可以只预览
–charset=utf8mb4 设置字符串否则可能会乱码
–lock=1
       1：循环锁，默认1000条一个周期，一个周期提交一次事物，建议使用这个锁类型不影响写入
       否则数据量过大会导致锁检查过的表，默认是一次性提交