标签归档:MySQL

简单实现MySQL负载低时才继续运行任务,负载高的时候睡眠

public static function runSleep($sec = 10, $echo = null) {
while (true) {
$result = NoThingModel::getInstance()->db->select("show status like 'Threads_running'", [], false);
$result = reset($result);
if ($result) {
if ($result->Variable_name == 'Threads_running') {
if ($result->Value > 10) {
if ($echo) {
echo "sleep 10\n";
}
sleep($sec);
} else {
break;
}
} else {
break;
}
} else {
break;
}
}
}

MySQL二进制日志清理

  1. 查看过期天数

show variables like ‘%logs%’;

2. 设置过期天数(最大值为99)

set global expire_logs_days = 99;

自动二进制日志文件删除的天数。 默认值为0,表示“不自动删除”。 可能的删除发生在启动时和二进制日志刷新。

2.1 生效问题

flush logs;

如果你发现设置没有生效,可能需要执行此命令。

3. 查看现有的日志文件

SHOW BINARY LOGS;

4. 手动清理较旧日志

PURGE BINARY LOGS TO ‘mysql-bin.000007’;

将mysql-bin.000007以前的日志清理掉,又或者

PURGE BINARY LOGS BEFORE ‘2018-01-06 12:12:12’;

将2018-01-06 12:12:12以前的日志清理掉

5. 修改MySQL配置文件

expire_logs_days = 99

 

create table和主从复制一坑

正确:
CREATE TABLE `chapter_lock_reason` (
`id` INT(11) NOT NULL AUTO_INCREMENT
PRIMARY KEY (`id`)
);

错误的方式:
CREATE TABLE xxx.`chapter_lock_reason` (
`id` INT(11) NOT NULL AUTO_INCREMENT
PRIMARY KEY (`id`)
);

MySQL ARCHIVE 引擎,从库突然Can’t write; duplicate key in table ‘nopay_record” on query.

这是ARCHIVE引擎的BUG

Error ‘Can’t write; duplicate key in table ‘nopay_record” on query. Default database: ‘doufu’. Query: ‘insert into `nopay_record` (`user_id`, `novel_id`, `chapter_id`, `channel`, `created_at`) values (3927416, 365027, 2499984, NULL, ‘2017-12-29 23:38:01′)’

表结构:

CREATE TABLE `nopay_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`novel_id` int(11) NOT NULL,
`chapter_id` int(11) NOT NULL,
`channel` tinyint(4) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=440357 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

改成innodb

xtrabackup 快速增量备份教程

xtrabackup –backup –compress –target-dir=/mysqlbackup/full –user=aaa –host=hostname –password=xxxx –databases=ggggg

xtrabackup –backup –compress –target-dir=/mysqlbackup/incr –incremental-basedir=/mysqlbackup/full –user=aaa –host=hostname –password=xxxx –databases=ggggg

 

–compress 压缩

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

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%';