Debian版mysql安装
Debian系统安装参考:https://blog.csdn.net/hutc_Alan/article/details/123850184?spm=1001.2014.3001.5502
MySQL8.0 debian10版(链接:https://pan.baidu.com/s/1Eo926WIRorz8jktyHx0lIA
提取码:ghmf)
MySQL官方下载地址:https://dev.mysql.com/downloads/mysql/
安装MySQL(debian10)
$ apt-get install ./mysql-apt-config_0.8.22-1_all.deb
如果想安装5.7版本,则可在此页面选择
$ apt-get update
$ apt-get install mysql-server
设置root密码
再次确认root密码
选择强验证方法
安装完成
执行以下命令可以进行一些安全设置
$ mysql_secure_installation
远程控制出现的错误:
问题一:
ERROR 1130 (HY000): Host ‘192.168.1.113’ is not allowed to connect to this MySQL server
登录mysql
$ mysql -uroot -p
输入root密码
允许远程控制
mysql> use mysql;
mysql> update user set host='%' where user='root';
立即生效
mysql> flush privileges;
问题二:ERROR 2059 (HY000): Authentication plugin ‘caching_sha2_password’ cannot be loaded: 找不到指定的模块。
查看当前加密方式
mysql> select user,plugin from user where user='root';
+------+-----------------------+
| user | plugin |
+------+-----------------------+
| root | caching_sha2_password |
+------+-----------------------+
1 row in set (0.00 sec)
root账号密码加密方式为caching_sha2_password。
修改加密方式(其中'password'改为你自己的密码)
mysql> alter user 'root'@'%' identified with mysql_native_password by 'password';
立即生效
mysql> flush privileges;
mysql配置
常用指令
查看mysql版本
$ mysqladmin --version
启动mysql
$ service mysql stop
停止mysql
$ service mysql start
重启mysql
$ service mysql restart
系统变量
系统变量参考:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
要查看服务器根据其编译的默认值和它读取的任何选项文件使用的值,请使用以下命令::
$ mysqld --verbose --help
要查看服务器仅根据其编译默认值使用的值,忽略任何选项文件中的设置,请使用以下命令:
$ mysqld --no-defaults --verbose --help
更改数据保存位置(可选)(默认保存在/var/lib/mysql文件夹中)
1.打开配置文件(参考https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html)
/etc/mysql/mysql.conf.d/mysqld.cnf (Debian 平台)
$ vim /etc/mysql/mysql.conf.d/mysqld.cnf
2.修改配置文件(保存路径自行更改)
#datadir = /var/lib/mysql
datadir =/home/mysql_data/mysql/
3.移动现有数据库
mv /var/lib/mysql /home/mysql_data/mysql/
4.重启数据库
service mysql restart
更改字符集
参考:https://dev.mysql.com/doc/refman/8.0/en/charset-configuration.html
查看字符集(在mysql中执行)
mysql> show variables like '%char%';
+--------------------------+--------------------------------+
| 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 | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
如需更改字符集,可在/etc/mysql/mysql.conf.d/mysqld.cnf文件中更改
如在/etc/mysql/mysql.conf.d/mysqld.cnf中添加下面内容
[client]
default-character-set=utf8
[mysqld]
character_set_server = utf8
然后重启mysql
$ service mysql restart
查看字符集
mysql> show variables like '%char%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb3 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)