Rootop 服务器运维与web架构

2020-10-30
发表者 Venus
设置history查看历史命令执行时间已关闭评论

设置history查看历史命令执行时间

用户家目录下的 .bash_profile 和 .bashrc 都可以设置参数,实现用户登陆后加载环境信息或者要自动执行的命令。

# .bash_profile会调用.bashrc脚本

[root@localhost ~]# cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# .bashrc会调用/etc/bashrc

[root@localhost ~]# cat .bashrc 
# .bashrc

# User specific aliases and functions

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
alias vi=vim

# Source global definitions
if [ -f /etc/bashrc ]; then
	. /etc/bashrc
fi

# 调用 /etc/profile.d/*.sh 下所有sh文件

[root@localhost ~]# cat /etc/bashrc 
# /etc/bashrc
略过部分
    for i in /etc/profile.d/*.sh; do
        if [ -r "$i" ]; then
            if [ "$PS1" ]; then
                . "$i"
            else
                . "$i" >/dev/null
            fi
        fi
    done
略过部分

# sh脚本

[root@localhost ~]# ll /etc/profile.d/
total 60
-rw-r--r--. 1 root root  771 Nov  6  2016 256term.csh
-rw-r--r--. 1 root root  841 Nov  6  2016 256term.sh
-rw-r--r--. 1 root root  196 Apr 29  2015 colorgrep.csh
-rw-r--r--. 1 root root  201 Apr 29  2015 colorgrep.sh
-rw-r--r--. 1 root root 1741 Nov  5  2016 colorls.csh
-rw-r--r--. 1 root root 1606 Nov  5  2016 colorls.sh
-rw-r--r--  1 root root   62 Oct 30 15:35 history.sh
-rw-r--r--. 1 root root 1706 Nov  6  2016 lang.csh
-rw-r--r--. 1 root root 2703 Nov  6  2016 lang.sh
-rw-r--r--. 1 root root  123 Jul 31  2015 less.csh
-rw-r--r--. 1 root root  121 Jul 31  2015 less.sh
-rw-r--r--. 1 root root  105 Dec 22  2016 vim.csh
-rw-r--r--. 1 root root  269 Dec 22  2016 vim.sh
-rw-r--r--. 1 root root  164 Jan 28  2014 which2.csh
-rw-r--r--. 1 root root  169 Jan 28  2014 which2.sh

所以就把记录历史命令时间功能的变量放到此目录下。

[root@localhost ~]# cd /etc/profile.d/
[root@localhost profile.d]# cat history.sh 
export HISTSIZE=10000
export HISTTIMEFORMAT="%F %T `whoami` "

效果

 

2020-09-18
发表者 Venus
mysql中explain分析sql执行type字段值的含义已关闭评论

mysql中explain分析sql执行type字段值的含义

# mysql5.7中type的类型达到了14种之多,这里只记录和理解最重要且经常遇见的六种类型,它们分别是all、index、range、ref、eq_ref、const。
# 从左到右,它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素,应当尽量优化你的sql语句,使它的type尽量靠右
# 资料来自:https://blog.csdn.net/dennis211/article/details/78170079

创建2张表测试

CREATE TABLE `user` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(100) DEFAULT NULL,
   `version` int(10) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

insert into `user` (`id`, `name`, `version`) values('1','aaaaaaa','11');
insert into `user` (`id`, `name`, `version`) values('2','bbbbbb','10');

CREATE TABLE `info` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(100) DEFAULT NULL,
   `age` int(10) DEFAULT NULL,
   `num` int(10) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `index_num` (`num`),
   KEY `index_name` (`name`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
 
insert into `info` (`id`, `name`, `age`, `num`) values('1','user1','1','11');
insert into `info` (`id`, `name`, `age`, `num`) values('2','user2','2','12');
insert into `info` (`id`, `name`, `age`, `num`) values('3','user3','3','13');
insert into `info` (`id`, `name`, `age`, `num`) values('4','user4','4','14');
insert into `info` (`id`, `name`, `age`, `num`) values('5','user5','5','15');

测试:

# type = all ,age字段无索引,则进行全表扫描
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE age = 1;'
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

# type = index 且 Extra = NULL 时,为走索引的全表扫描。这样的索引,在排序时比type = all的排序速度快。
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info ORDER BY id DESC;'
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+

# type = index 且 Extra = Using index 则为索引覆盖,从索引直接获取了数据,不用查表。name字段是普通索引
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT `name` FROM info;'
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | index | NULL          | index_name | 303     | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

# type = range , id自增主键,范围扫描
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE id <= 100;'
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | info  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

# type = ref , name字段普通索引,sql使用了索引,但该索引列的值并不唯一,有重复
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE `name` = "user1";'
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | ref  | index_name    | index_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

# type = eq_ref , 字段索引类型唯一,但单表测试结果是const,下面换联合查询可以。
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE num = 11;'
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | const | index_num     | index_num | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+

mysql -uroot -p"root" -e 'use aaa;EXPLAIN 
SELECT i.* 
FROM `user` AS u
LEFT JOIN info AS i 
ON u.id = i.id;'
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref      | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | index  | NULL          | PRIMARY | 4       | NULL     |    2 |   100.00 | Using index |
|  1 | SIMPLE      | i     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | aaa.u.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+

# const , id自增主键,精确定位
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE id = 1;'
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | info  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

2020-09-16
发表者 Venus
ldconfig配置已关闭评论

ldconfig配置

https://www.man7.org/linux/man-pages/man8/ldconfig.8.html

作用:

默认搜索/lib和/usr/lib,以及配置文件/etc/ld.so.conf内所列的目录下的库文件。

搜索出可共享的动态链接库,库文件的格式为:lib*.so*,进而创建出缓存文件。

缓存文件默认为/etc/ld.so.cache,该文件保存已排好序的动态链接库名字列表。

ldconfig通常在系统启动时运行,而当用户安装了一个新的动态链接库时,就需要手工运行这个命令。

ldconfig需要注意的地方:

1、往/lib和/usr/lib中添加库文件后需要调用下ldconfig生成最新缓存。

2、如果添加的库文件不在默认的/lib或/usr/lib中的话,就要修改/etc/ld.so.conf文件,追加库文件所在的路径,然后重新调用下ldconfig命令。
比如在安装MySQL的时候,其库文件/usr/local/mysql/lib,就需要追加到/etc/ld.so.conf文件中。命令如下:

# echo "/usr/local/mysql/lib" >> /etc/ld.so.conf
# ldconfig -v | grep mysql

3、如果添加的库文件不在/lib或/usr/lib下,且没有权限修改/etc/ld.so.conf文件的话,这时就需要export一个全局变量LD_LIBRARY_PATH,就可以了。

2020-09-16
发表者 Venus
mysql redo log 重做日志已关闭评论

mysql redo log 重做日志

资料:https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html
重做日志用于数据库崩溃后未写入到数据库中数据的恢复。
重做日志默认文件名叫 ib_logfile0 和 ib_logfile1两个个文件。
# 这俩文件的当前大小通过下面变量查看(单位字节)

SHOW VARIABLES LIKE 'innodb_log_file_size'; 

# 当前的重做日志个数通过下面变量查看

SHOW VARIABLES LIKE 'innodb_log_files_in_group';

如果想改变重做日志的数量和大小,按照以下步骤操作:
1、停止数据库并确定没有报错
2、修改my.cnf,添加

# 数量最大值100,比如这里设置为5个
innodb_log_files_in_group = 5
# 单个文件大小不能超过512GB/innodb_log_files_in_group
innodb_log_file_size = 1024000000
# 设置文件路径,默认在 SHOW VARIABLES LIKE 'datadir'; 目录下
innodb_log_group_home_dir = /usr/local/mysql/data


3、启动mysql

比如 https://www.rootop.org/pages/4797.html 这个错误就是重做日志大小发生改变,导致启动时检测到文件大小错误。

2020-09-10
发表者 Venus
mysql中创建纯数字的数据库及删除已关闭评论

mysql中创建纯数字的数据库及删除

在mysql中创建纯数字数据库是不合标准的,默认会提示语法错误,但通过mysql转义符 ` 还是可以创建。

CREATE DATABASE 222;

报错:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '222' at line 1

# 加上转义符

CREATE DATABASE `222`;

成功。

# 查询语句

SELECT * FROM 222.aaa WHERE id = 1;

报错:
错误码: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '222.aaa WHERE id = 1' at line 1

# 加上转义符查询

SELECT * FROM `222`.aaa WHERE id = 1;

可以查询

# 删除加转义符删除

DROP DATABASE `222`;