mysql 密码恢复及设置
修改密码验证策略
设置密码验证策略永久生效
修改数据库管理员本机管理密码（操作系统管理员）
mysql> use mysql
mysql> update user set password_expired="N" where user="root";
数据管理
数据导入：把系统文件的内容存储到数据库的表里
用户名 密码占位符 UID GID 描述信息 家目录 shell
create database studb;
create table studb.user(
name char(50),
password char(1),
UID int(2),
GID int(2),
comment varchar(100),
homedir char(100),
shell char(25)
)engine=innodb;
select * from studb.user;
load data infile '目录/文件名' into table '库.表名' fields terminated by "字段间隔符号" lines terminated by "行间隔符号"
查看默认使用目录及目录是否存在
mysql> show variables like "secure_file_priv";
+------------------------------+-------------------------------------------+
| Variable_name | Value |
+-------------------------------+-----------------------------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+-------------------------------+------------------------------------------+
[root@mysql4-1 ~]# cp /etc/passwd /var/lib/mysql-files/
[root@mysql4-1 ~]# setenforce 0
mysql> load data infile '/var/lib/mysql-files/passwd' into table user fields terminated by ":" lines terminated by "\n";
Query OK, 44 rows affected (0.04 sec)
Records: 44 Deleted: 0 Skipped: 0 Warnings: 0
修改默认使用目录
[root@mysql4-1 ~]# mkdir /myfile
[root@mysql4-1 ~]# chown mysql /myfile/
[root@mysql4-1 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myfile"
[root@mysql4-1 ~]# systemctl restart mysqld
mysql> show variables like "secure_file_priv";
+-------------------------------+-------------------+
| Variable_name | Value |
+-------------------------------+------------------+
| secure_file_priv | /myfile/ |
+--------------------------------+-----------------+
1 row in set (0.00 sec)
数据导出：把表记录存储到系统
sql 查询 into outfile "目录/文件名";
sql 查询 into outfile "目录/文件名" fields terminated by "字段间隔符号" lines terminated by "行间隔符号"；
mysql> select name,UID from user limit 5 into outfile "/myfile/user1.txt";
[root@mysql4-1 ~]# ls /myfile/
user1.txt
mysql> select name,UID from user limit 5 into outfile "/myfile/user2.txt" fields terminated by "#" lines terminated by ":";
[root@mysql4-1 ~]# ls /myfile/
user1.txt user2.txt
[root@mysql4-1 ~]# cat /myfile/user2.txt
root#0:bin#1:daemon#2:adm#3:lp#4:
用户授权 grant
权限撤销 revokel
mysql 优化：
数据库服务器响应客户请求特别慢，可能是由于那些原因造成的，如何排除，请说出你的处理思路
1.网络带宽窄 测速软件 花钱买带宽
2.硬件配置低
3. 提供服务的软件版本低导致升级服务软件版本
查看服务运行时，参数的值
查看参数值
mysql> show variables like "%关键字%"
修改参数值
mysql> show variables like "%connect%";
+------------------------------------------------------------------------------------+------------------------------+
| Variable_name | Value |
+------------------------------------------------------------------------------------+------------------------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| performance_schema_session_connect_attrs_size | 512 |
+------------------------------------------------------------------------------------+------------------------------+
mysql> show variables like "%max_connections%";
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
+------------------------------+------------+
1 row in set (0.01 sec)
mysql> set GLOBAL max_connections=300;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%max_connections%";
+------------------------------+------------+
| Variable_name | Value |
+------------------------------+------------+
| max_connections | 300 |
+------------------------------+------------+
最大连接数/并发连接数 约等于 0.85
mysql> show global status like "Max_used_connections";
+---------------------------------------+------------+
| Variable_name | Value |
+---------------------------------------+------------+
| Max_used_connections | 1 |
+---------------------------------------+------------+
+------+-----------------------+-------------------+-----------+---------------+-----------+---------------------------------------------------------------------------------------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------------+-------------------+-----------+---------------+-----------+---------------------------------------------------------------------------------------------------+---------------------------------+
| 1 | system user | | NULL | Connect | 1961 | Connecting to master | NULL |
| 2 | system user | | NULL | Connect | 1961 | Slave has read all relay log; waiting for more updates | NULL |
| 5 | root | localhost | NULL | Query | 0 | starting | show processlist |
+------+-----------------------+-------------------+-----------+---------------+-----------+---------------------------------------------------------------------------------------------------+---------------------------------+
mysql> show variables like "%timeout%";
mysql> show variables like "%size%";
mysql> show variables like "%cache%";
table_open_cache 所有线程同时打开表的数量
mysql> show variables like "query_cache%";
query_cache_type = 0|1|2
mysql> show global status like "qcache%";
4. 程序员编写的访问数据的查询语句复杂，导致处理速度慢
启用慢查询日志文件，记录超过指定时间显示查询结果得命令
mysql 支持四种日志文件：
binlog 日志
选项
general-log
general-log-file=文件名 #自定义日志文件
#vim /etc/my.cnf
[mysqld]
general-log
：wq
#systemctl restart mysqld
[root@mysql12 ~]# ls /var/lib/mysql
mysql12.log
[root@mysql12 ~]# mysql -uroot -p123456
mysql> show databases;
[root@mysql12 mysql]# tail -f mysql12.log
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
慢查询日志
选项
#vim /etc/my.cnf
[mysqld]
slow-query-log
：wq
#systemctl restart mysqld
[root@mysql12 ~]# ls /var/lib/mysql
mysql12-slow.log
mysql> select sleep(10);
[root@mysql12 mysql]# cat mysql12-slow.log
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2018-01-02T03:27:33.280720Z
# User@Host: root[root] @ localhost [] Id: 6
# Query_time: 10.000291 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1514863653;
select sleep(10);
5. 网络拓扑结构不合理，有数据传输瓶颈
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
计算
安全
数据库
网络和加速
企业服务
