分类 Mysql 下的文章

前因：

今天星期六，上午正在逛市场，突然老板打电话来说服务器掉线了，刚开始以为只是普通问题，让自己联系机房就好了，先把服务器跑起来，让用户可以访问，一些自动化处理程序等我回去了再启动。

结果机房说服务器系统坏了，跑不起来，只能重新搭建服务
于是赶紧回家，让机房把硬盘挂到别的机器上，想办法把数据给弄出来
把旧硬盘挂到新机器后，开始了数据操作，一开始想的是把旧硬盘的MySQL跑起来，在用工具导出来就好了，结果MySQL跑不起来了。

由于新机器没有按照任何环境，无法直接使用systemctl start mysql,新机器也没有MySQL的配置文件，于是另辟蹊径

1.找个新的配置文件(my.cnf) 放到 /etc/my.conf(刚好旧硬盘里有之前的my.cnf文件)
2.进入MySQL的安装目录直接操作 ./mysql --defaults-file=/etc/my.cnf (--defaults-file=/etc/my.cnf 指定启动的配置文件)
3.启动

报错

[root@xslmbllajezxnqw bin]# ./mysqld --defaults-file=/etc/my.cnf
2024-11-23T15:00:17.085523Z 0 [Warning] [MY-000081] [Server] option 'max_allowed_packet': unsigned value 107374182400 adjusted to 1073741824.
2024-11-23T15:00:17.085604Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-11-23T15:00:17.085714Z 0 [System] [MY-010116] [Server] /data/www/server/mysql/bin/mysqld (mysqld 8.0.24) starting as process 31595
2024-11-23T15:00:17.099843Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2024-11-23T15:00:17.100066Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-11-23T15:00:17.118879Z 0 [System] [MY-010910] [Server] /data/www/server/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.24)  Source distribution

注意报错信息同有个 Please read "Security" section of the manual to find out how to run mysqld as root! 提示，要求不能使用root用户启动

于是再创建一个账户useradd www
将MySQL的数据目录所有权更改为www用户

再次启动./mysqld --defaults-file=/etc/my.cnf --user=www

[root@xslmbllajezxnqw bin]# ./mysqld --defaults-file=/etc/my.cnf --user=www
2024-11-23T07:48:47.965407Z 0 [Warning] [MY-000081] [Server] option 'max_allowed_packet': unsigned value 107374182400 adjusted to 1073741824.
2024-11-23T07:48:47.965490Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2024-11-23T07:48:47.965601Z 0 [System] [MY-010116] [Server] /data/www/server/mysql/bin/mysqld (mysqld 8.0.24) starting as process 26261
2024-11-23T07:48:48.008578Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-11-23T07:48:54.506873Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-11-23T07:48:54.745099Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-11-23T07:48:55.075232Z 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue.
2024-11-23T07:48:55.075566Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: <%.*s|%.*s
2024-11-23T07:48:55.138937Z 0 [Warning] [MY-011302] [Server] Plugin mysqlx reported: 'Failed at SSL configuration: "SSL context is not usable without certificate and private key"'
2024-11-23T07:48:55.139080Z 0 [System] [MY-010931] [Server] /data/www/server/mysql/bin/mysqld: ready for connections. Version: '8.0.24'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution.

成功

只要MySQL跑起来，后面安装普遍流程来操作就可以了

• navcat
• mysqldump

用上面的任一都行

写这篇文章用时30分钟不到，按照我一开始的想法是对的，但实际解决这个问题搞了大半天，期间老板一直再问好了吗? ,搞得我快要爆炸了

查询正打开的表

show OPEN TABLES where In_use > 0;

查询正在进行的进程

show processlist;

查看当前运行的所有事务

select * from information_schema.INNODB_TRX;

查看存储引擎的状态

show engine innodb status

查询死锁表

-- 查询死锁表 8.0之前
SELECT * from INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查询死锁等待时间 8.0之前
select * from information_schema.INNODB_LOCK_waits;

-- 查询死锁表 8.0之后
select * from performance_schema.data_locks;
-- 查询死锁等待时间 8.0之后
select * from performance_schema.data_lock_waits;

SQRT(POWER(' . $lat . ' - p.lat, 2) + POWER(' . $lng . '  - p.lng, 2)) asc

排除查询一个表在另一个表中存在的数据
例如：

• 任务完成后列表中不再显示该任务
• 任务领取后的记录表中存在的数据，在任务列表里不在显示
• task 任务表、task_sign 任务记录表

SELECT
*
FROM
    `sz_task`
WHERE
    AND (
        SELECT 
            count( * ) AS num 
        FROM 
            sz_task_sign 
        WHERE 
            `sz_task`.id = `sz_task_sign`.task_id AND STATUS = '3'
        AND user_id = 1
    ) = 0 

注意：

• 如果子查询中的 ( SELECT count( * ) AS num FROM sz_task_sign WHERE sz_task.id = sz_task_sign.task_id AND STATUS = '3' AND user_id = 1 ) = 1 则条件刚好相反

测试数据表demo

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test_order
-- ----------------------------
DROP TABLE IF EXISTS `test_order`;
CREATE TABLE `test_order` (
  `id` int(11) NOT NULL,
  `order_no` varchar(32) DEFAULT NULL COMMENT '订单号',
  `user_id` int(11) DEFAULT NULL COMMENT '用户id',
  `shop_id` int(11) DEFAULT NULL COMMENT '商家id',
  `status` tinyint(1) DEFAULT NULL COMMENT '订单状态 0待发货 1已发货 2已收货 3已评论 4申请退款',
  `create_time` int(10) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test_order
-- ----------------------------
INSERT INTO `test_order` VALUES ('1', 'aaaaaaaaa', '11', '111', '1', '1573041313');
INSERT INTO `test_order` VALUES ('2', 'bbbbbbbbb', '11', '222', '1', '1573041313');
INSERT INTO `test_order` VALUES ('3', 'ccccccccc', '11', '333', '2', '1573041313');
INSERT INTO `test_order` VALUES ('4', 'ddddddddd', '11', '222', '3', '1573041313');
INSERT INTO `test_order` VALUES ('5', 'eeeeeeeee', '11', '111', '4', '1573041313');
INSERT INTO `test_order` VALUES ('6', 'fffffffff', '11', '111', '3', '1573041313');
INSERT INTO `test_order` VALUES ('7', 'ggggggggg', '11', '222', '4', '1573041313');
INSERT INTO `test_order` VALUES ('8', 'hhhhhhhhh', '11', '111', '0', '1573041313');
INSERT INTO `test_order` VALUES ('9', 'iiiiiiiii', '11', '333', '0', '1573041313');
INSERT INTO `test_order` VALUES ('10', 'jjjjjjjjj', '11', '222', '1', '1573041313');

表截图

查询语句

SELECT
    count( CASE WHEN STATUS = 0 THEN 1 END ) AS '待发货',
    count( CASE WHEN STATUS = 1 THEN 1 END ) AS '已发货',
    COUNT( CASE WHEN STATUS = 2 THEN 1 END ) AS '已收货',
    count( CASE WHEN STATUS = 3 THEN 1 END ) AS '已评论',
    count( CASE WHEN STATUS = 4 THEN 1 END ) AS '申请退款' 
FROM
    test_order;

运行截图

USE information_schema;


SELECT
    C.COLUMN_NAME AS '字段名',
    C.COLUMN_TYPE AS '数据类型',
    C.IS_NULLABLE AS '允许为空',
    C.EXTRA AS 'PK',
    C.COLUMN_COMMENT AS '字段说明',
        C.TABLE_NAME AS '表名'
FROM
    COLUMNS C
INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE
    T.TABLE_SCHEMA = '数据库名称'   

也可以在where条件后面指定表名称

and T.TABLE_NAME='表名称'

查询出的结果可以导出

查看用户信息

select `Host`,`user` from user;

mysql> select `Host`,`user` from user;
+-----------+------------+
| Host      | user       |
+-----------+------------+
| 127.0.0.1 | root       |
| ::1       | root       |
| localhost | root       |
+-----------+------------+
4 rows in set (0.00 sec)

创建一个用户
示例：create user '用户名'@'host权限' identified by '密码';

mysql> create user 'test'@'%' identified by 'MV5vVQUu';
Query OK, 0 rows affected (0.00 sec)

授予用户权限
示例：grant 权限类型(select、update、delete可多个，all 表示所有) privileges on 表名.操作权限(*表示所有) to '用户名'@'host权限'

mysql> grant all privileges on test_db.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

查询用户权限

mysql> show grants for test@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*3EB9B6C888B00C67DA138D280D13B8C5129A1DD2' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'test'@'%'                                                 |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

取消用户权限
示例：revoke all on 表名.操作权限 from test@'%'

mysql> revoke all on test_db.* from test@'%';
Query OK, 0 rows affected (0.00 sec)

再次查看用户权限

mysql> show grants for test@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*3EB9B6C888B00C67DA138D280D13B8C5129A1DD2' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改用户密码

//update 需要指定密码和password函数
//推荐方法 
ALTER USER test@'%' IDENTIFIED BY '123456';

最后别忘了刷新权限

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>