- 使用MySQL的图形界面管理工具Navicat for MySQL连接Mysql数据库时提示错误:Can't connect to MySQL server (10060)
创建用户,不限制ip连接
// 创建拥有
CREATE USER 'username'@'%' IDENTIFIED BY 'a1054995637';
// 不限制连接设备的ip
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
// 刷新方生效
flush privileges;
腾讯云linux碰到的问题:
已经添加了安全组和处理上面的步骤,还是报错
临时关闭防火墙:
service iptables stop
再试可以远程连接
添加需要监听的端口
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
开启防火墙服务
service iptables start
- 复制一条记录到同一个表
CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;
- 从100W级别的表中随机获取一条记录
SELECT * FROM `users`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `users`)-(SELECT MIN(id) FROM `users`)) + (SELECT MIN(id) FROM `users`)))
ORDER BY id LIMIT 1;
order by rand() 函数效率低下,表数据少的时候才用
- mysql慢查询
-- 查询当前的慢查询状态
show variables like 'general_log%';
SET GLOBAL general_log = 'ON'; -- 日志开启
SET GLOBAL general_log = 'OFF'; -- 日志关闭
-- 设置慢查询日志存储路径
SET GLOBAL general_log_file = '/var/lib/mysql/localhost.log';
- 存储过程插入数据到mysql
DROP PROCEDURE IF EXISTS test_initData;
DELIMITER $
CREATE PROCEDURE test_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=100000 DO
INSERT INTO test(id,a,b,c) VALUES(i,i*2,i*3,i*4);
SET i = i+1;
END WHILE;
END $
CALL test_initData();