查看和修改MySQL数据库参数

MySQL依赖大量的参数来控制SQL的处理执行过程。mysql.cnf文件是mysql默认的参数配置文件，mysql启动时会优先在一些特定位置寻找并读取该文件。my.cnf不存在时并不会影响MySQL实例的初始化启动，参数值取决于编译MySQL时指定默认值和源代码中指定参数的默认值。

一、MySQL参数的分类

MySQL参数可以分为静态（static）参数和动态（dynamic）参数，区别在于参数值是否可以在实例的生命周期内修改并生效。

1、静态参数

静态参数在数据库启动期间不能被修改。静态参数设置之后必须要重启才能生效。比如：log_slave_updates，back_log，log_bin，lower_case_table_names。对于静态参数，没有global级和session级区分。

2、动态参数

动态参数在数据库启动期间能被修改，动态参数又分为两种：global级，session级。session作用域修改后不影响其他已经开启和之后开启的session。global作用域参数值修改后，修改前已经开启的session不会生效，会在新创建的session中生效。

二、MySQL参数查看

以wait_timeout参数为例：

1、global级参数的查看

方法一：

mysql> select @@global.wait_timeout;
+-----------------------+
| @@global.wait_timeout |
+-----------------------+
|                 28800 |
+-----------------------+
1 row in set (0.00 sec)

方法二：

mysql> show global variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.01 sec)

方法三：

mysql> select * from performance_schema.global_variables where variable_name ='wait_timeout';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| wait_timeout  | 28800          |
+---------------+----------------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.global_variables where variable_name like '%wait_timeout%';
+--------------------------+----------------+
| VARIABLE_NAME            | VARIABLE_VALUE |
+--------------------------+----------------+
| innodb_lock_wait_timeout | 50             |
| lock_wait_timeout        | 31536000       |
| wait_timeout             | 28800          |
+--------------------------+----------------+
3 rows in set (0.01 sec)

2、session级参数的查看

方法一：

mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

方法二：

mysql> select @@session.wait_timeout;
+------------------------+
| @@session.wait_timeout |
+------------------------+
|                  28800 |
+------------------------+
1 row in set (0.00 sec)

方法三：

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)

方法四：

mysql> show session variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)

方法五：

mysql> select * from performance_schema.session_variables where variable_name like '%wait_timeout%';
+--------------------------+----------------+
| VARIABLE_NAME            | VARIABLE_VALUE |
+--------------------------+----------------+
| innodb_lock_wait_timeout | 50             |
| lock_wait_timeout        | 31536000       |
| wait_timeout             | 28800          |
+--------------------------+----------------+
3 rows in set (0.00 sec)

三、MySQL参数修改

1、session级参数的修改

方法一：

mysql> set wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

方法二：

mysql> set session wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

方法三：

mysql> set @@wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

方法四：

mysql> set @@session.wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

2、global级参数的修改

方法一：

mysql> set global wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

方法二：

mysql> set @@global.wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

四、将参数值设置为MySQL的默认值

1、session级参数

mysql> set wait_timeout=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

2、global级参数

mysql> set global wait_timeout=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

五、静态参数的修改以及使动态参数永久生效

如果需要使动态参数的设置永久生效，必须修改参数文件并重启MySQL生效。而静态参数只能通过修改参数文件使之生效。比如：

# vi /etc/my.cnf
[mysqld]
wait_timeout=10

mysql官网：https://dev.mysql.com/downloads/windows/installer/

#安装步骤以及测试

1.首先：需要配置环境变量 新建 MYSQL_HOME  F:\mysql-8.0.28-winx64
              在Path里面 设置 %MYSQL_HOME%\bin

2.其次：配置文件my.ini的详细说明 参考https://link.zhihu.com/?          target=https%3A//blog.csdn.net/hanwuqia0370/article/details/85680775

3.然后：以管理员身份打开cmd命令框    初始化mysql  

              mysqld --initialize --console 执行完成后，会输出root用户的初始密码

 4.最后： 安装命令：mysqld install   
                启动命令： net start mysql 

5.就可以看到服务里面的mysql是启动着呢

MySQL数据库权限：

异常信息：java.sql.SQLException: null, message from server: “Host ‘XXX‘ is not allowed to connect异常

问题描述：搭建框架后访问数据库报错，不允许本机ip访问。

解决办法：修改MySQL数据库权限

比如数据库名称old_db想改名为new_db

• MySQL修改数据库名称比较麻烦，不支持直接修改，需要通过其它方式间接达到修改数据库名称的目的。
• 在 MySQL 5.1.23 之前的旧版本中，我们可以使用 RENAME DATABASE 来重命名数据库，但此后版本，因为安全考虑，删掉了这一条命令。

方法一：先导出数据，再导入数据

当数据库体积比较小时，最快的方法是使用mysqldump命令来创建整个数据库的转存副本，然后新建数据库，再把副本导入到新数据库中。

1.1 先创建新库：

create database new_db;

1.2 使用mysqldump导出数据：

mysqldump -uroot -p123456 --set-gtid-purged=OFF old_db > /tmp/old_db.sql

仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF
作用是在备份时候不出现GTID信息

1.3 导入数据到新库：

mysql -uroot -p123456 new_db < /tmp/old_db.sql

方法二：通过修改表名称，间接实现修改数据库名称

使用此方法实际上将所有表从一个数据库移动到另一个数据库，这实际上重命名了该数据库（MySQL没有单个语句的操作），移动后原始数据库继续存在，但是里面没有表。

RENAME TABLE：https://dev.mysql.com/doc/refman/5.7/en/rename-table.html

2.1 先创建新库：

create database new_db;

2.2 使用RENAME TABLE命令修改表名，将表移动到新的库里：

rename table old_db.tb to new_db.tb;

2.3 完成后删除旧库：

drop database old_db;

2.4 如何使用shell脚本来批量修改表名：

• 当库下表比较多的时候，用上面方法纯手动也不现实，好在linux下可以用shell脚本来批处理。
• 附上一个shell脚本批量修改表名称。

#!/bin/bash
mysql -uroot -p123456 -e 'create database if not exists new_db;'
list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='old_db'")

for table in $list_table
do
    mysql -uroot -p123456 -e "rename table old_db.$table to new_db.$table"
done

mysql登陆命令行参数

• -e, --execute=name # 执行mysql的sql语句
• -N, --skip-column-names # 不显示列信息
• -s, --silent # 一行一行输出，中间有tab分隔

因为有悔，所以披星戴月；因为有梦，所以奋不顾身！ 个人博客首发：easydb.net 微信公众号：easydb 关注我，不走丢！