my.cnf mysql配置文件

环境

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
1
2

Server version: 5.6.35 MySQL Community Server (GPL)

停止启动MySQL

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
1
2

my.cnf文件

配置文件路径读取顺序

/etc/my.cnf
/etc/mysql/my.cnf
/usr/local/mysql/etc/my.cnf
~/.my.cnf
1
2
3
4

如果找不到配置文件可以使用find命令

[root@VM-12-3-centos etc]# find / -name my.cnf
/etc/my.cnf
1
2

[mysqld]
port=3306
basedir=/usr/local/mysql
datadir=/mydata/mysql/data
socket=/mydata/mysql/mysql.sock
pid-file=/mydata/mysql/mysql.pid

log-error=/mydata/mysql/error.log
tmpdir=/tmp
server_id=1
user=mysql

# skip-grant-tables 配置后不用密码登陆
#skip-grant-tables
character-set-server=utf8mb4
explicit_defaults_for_timestamp=on
secure-file-priv=NULL

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[client]
default-character-set=utf8mb4
socket=/mydata/mysql/mysql.sock
[mysql]
default-character-set = utf8mb4
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

基础参数

• port：指定了mysql的开放端口
• basedir：指定了安装 MySQL 的绝对路径
• datadir：指定了 MySQL 的数据库文件存放的绝对路径
• socket：套接字文件
• 默认路径：/tmp/mysql.sock 需要在[mysqld]和[client]下面都配置
  mysql -uroot -p 访问本地需要用到socket
  mysql -h 127.0.0.1 -uroot -p 用到tcp协议
• pid-file：默认路径 $datadir/**hostname**.pid
• PID文件的作用是为了防止同一个数据库被启动多次
• 通过Mysqld_safe启动mysql时，mysqld_safe会检查pid文件
  pid文件不存在，不做处理
  文件存在，且pid已占用则报错"A mysqld process already exists"
  文件存在，但pid未占用，则删除pid文件
  mysqld启动后会通过create_pid_file函数新建pid文件，通过getpid()获取当前进程pid并将PID写入pid文件
• skip-grant-tables 配置后不用密码登陆，一般用于忘记密码，修改密码后注释该配置。

character_set

默认字符集，操作数据库会产生乱码：

mysql> show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• character_set_client：主要用来设置客户端使用的字符集
• character_set_connection： 主要用来设置连接数据库时的字符集， 如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
  例如：jdbc:mysql://aliyunhost:3306/test?useUnicode=true&characterEncoding=utf8
• character_set_database： 主要用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置。 CREATE DATABASE IF NOT
  EXISTS my_db default character set utf8 COLLATE utf8_general_ci;
• character_set_results： 数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式。
• character_set_server： 服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义。
• character_set_system： 数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式。
• character_sets_dir： 这个变量是字符集安装的目录。

utf8mb4

MySQL 的“utf8”实际上不是真正的 UTF-8
“utf8”只支持每个字符最多三个字节，而真正的 UTF-8 是每个字符最多四个字节。
MySQL 一直没有修复这个 bug，他们在 2010 年发布了一个叫作“utf8mb4”的字符集，绕过了这个问题。

MySQL 的“utf8mb4”是真正的“UTF-8”。
MySQL 的“utf8”是一种“专属的编码”，它能够编码的 Unicode 字符并不多。

mysql查询数据库版本

select @@version;
select version();
SHOW VARIABLES WHERE variable_name LIKE 'version%';
1
2
3

mysql用户

常见用户分类

Root用户以及其他超级用户

Root用户是拥有所有权限的最高权限用户，可以执行所有操作，包括创建和管理其他用户。一般建议将Root用户作为保留用户，再另建一个超级用户如dba。超级用户通常只在管理和维护数据库时使用，而不应该在生产环境中广泛使用。

程序用户

程序用户是公司应用程序所使用的用户。他们通常需要执行增删改查，而不具备对数据库的整体管理权限。程序用户需要具备以下权限之一或多个权限的组合：

查看mysql用户

# 切换mysql数据库
  mysql> use mysql
Database changed
# 查询用户表
mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | localhost |
| root             | %         |
| txapp            | %         |
+------------------+-----------+
7 rows in set (0.00 sec)
mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

创建mysql用户

#foo表示你要建立的用户名，后面的123456表示密码,
#localhost限制在固定地址localhost登陆
CREATE USER 'foo'@'%' IDENTIFIED BY '123456';
1
2
3

修改密码

# 修改密码
ALTER USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY 'myfoo';
1
2

删除mysql用户

# 删除mysql用户
# drop user 'foo'@'%'; 
1
2

用户授权

示例中，database_name表示数据库名，table_name表示表名，username表示要授权的用户名，host表示允许连接的主机。通过使用GRANT语句，可以为用户授权具体的权限，以满足其在数据库中的操作需求。

# 授权
# GRANT privileges ON databasename.tablename TO 'username'@'host'

# 只能本地访问
GRANT all privileges ON test_db.* TO 'foo'@'localhost';
# 所有主机可以远程访问所有数据库
GRANT all privileges ON *.* TO 'foo'@'%';
# 所有主机可以远程访问数据库test_db
GRANT all privileges ON test_db.* TO 'foo'@'%';
# myhost主机可以远程访问(指定主机可以远程访问)
GRANT all privileges ON test_db.* TO 'foo'@'myhost';

# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 查看权限
# 比如使用用户 txapp登录mysql，那么使用 show grants; 可以查看 txapp的授权情况
mysql> show grants;
+-------------------------------------------------------+
| Grants for txapp@%                                    |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `txapp`@`%`                     |
| GRANT ALL PRIVILEGES ON `jeecg-boot`.* TO `txapp`@`%` |
| GRANT ALL PRIVILEGES ON `test_db`.* TO `txapp`@`%`    |
| GRANT ALL PRIVILEGES ON `test_db2`.* TO `txapp`@`%`   |
+-------------------------------------------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

忘记密码

在/etc/my.cnf中添加skip-grant-tables
重启MySQL后可以不用密码登陆

mysql> use mysql;
mysql> update user set password=password('123456') where user='username';
# 或者
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
1
2
3
4

更新密码后把【skip-grant-tables】删掉

重启mysql

#关掉mysql
mysqladmin -uroot -p shutdown

# 启动mysql
mysqld --defaults-file=/etc/my.cnf &
1
2
3
4
5

修改密码mysql8

mysql> use mysql
mysql> update user set authentication_string='' where user='username';
mysql> flush privileges;
mysql>  alter user 'username'@'%' identified by 'password';
1
2
3
4

锁超时

Lock wait timeout exceeded; try restarting transaction
1

-- 查询出超时的线程，然后杀掉
SELECT CONCAT('kill ',trx_mysql_thread_id,' ;'),trx_query FROM information_schema.INNODB_TRX;
-- 杀掉线程 trx_mysql_thread_id
kill xxxxxxxx;
1
2
3
4

创建用户并授权

mysql> use mysql
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' ;
mysql> select user, host from user where user='username';
+---------+------+
| user    | host |
+---------+------+
| username| %    |
+---------+------+
1 row in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10

数据库导入导出

导出

系统命令行

#导出全部数据库
mysqldump -uusername -ppassword --all-databases > all.sql
#导出指定数据库
mysqldump -uusername -ppassword --databases db1 db2 > db1db2.sql
1
2
3
4

导入

mysql 命令导入

# mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)
mysql -uroot -p123456 < runoob.sql
1
2

source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库
1
2
3
4

mysql修改字段

-- 增加字段 (添加注解，指定默认值， 添加在字段 id 的后面)
alter table t_goods
add column name varchar(128) default '' comment '名称' after id;

-- 修改字段长度
alter table t_goods
modify column name varchar(256);

-- 修改字段名称
alter table t_goods
change name myname varchar(256) comment '我的名称';

-- 删除字段
alter table t_goods
drop column myname;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql查询表结构

select TABLE_NAME, TABLE_COMMENT from information_schema.`TABLES` 
where TABLE_SCHEMA = 'systemmanage'
and TABLE_NAME = 't_prc_pro_config_lang';

select 
ORDINAL_POSITION, COLUMN_COMMENT, COLUMN_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH, COLUMN_KEY, if(IS_NULLABLE='NO','YES','NO') AS REQUIRED, COLUMN_DEFAULT
from information_schema.`COLUMNS`
where TABLE_NAME = 't_prc_pro_config_lang'
order by ORDINAL_POSITION;
1
2
3
4
5
6
7
8
9