mysql工作问题解决

一、进入Mysql视图

登录mysql服务器

//进入mysql视图

su - mysql

//查看当前路径

pwd

//查看历史信息，一般会看到进入mysql的权限命令

history

 

二、忘记用户连接密码，或权限不够建表，需最大权限

//root权限进入mysql

mysql -u root -p

//查询用户表命令

select host,user,password from mysql.user;

//如果要修改密码的话，在命令行下执行下面的语句

update mysql.user set password=password('mysql')  where user='mysql';

//无脑赋权操作==。

grant all privileges on *.* to mysql@"%";

//这一步一定要做，不然无法成功！ 这句表示从mysql数据库的grant表中重新加载权限数据。因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。

flush privileges;

 

三、修改mysql最大连接数

su - mysql
mysql -uroot -proot -h123456   数据库的root本地权限（每个数据库的密码不同）

show variables like '%max_conn%';

set global max_connections = 1000;

 

四、数据库连接不上

[root@mcmpsitdb02 ~]# su - mysql
[mysql@mcmpsitdb02 ~]$

[mysql@mcmpsitdb02 ~]$ mysql -uroot  -h127.0.0.1 -p123456
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

[mysql@mcmpsitdb02 ~]$ ps -af| grep mysqld
mysql     9724  9205  0 17:14 pts/0    00:00:00 grep mysqld

 

如上图没有mysql进程，启动一下mysql进程   ps：公司启功mysql进程命令
[mysql@mcmpsitdb02 ~]$ mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &

[mysql@mcmpsitdb02 ~]$ ps -af| grep mysqld
mysql     9813  9205  0 17:15 pts/0    00:00:00 /bin/sh /home/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf
mysql    11156  9813  0 17:15 pts/0    00:00:01 /home/mysql/bin/mysqld --defaults-file=/home/mysql/etc/my.cnf --basedir=/home/mysql --datadir=/mysql/data --plugin-dir=/home/mysql/lib/mysql/plugin --log-error=/mysql/data/mcmpsitdb02.err --open-files-limit=51200 --pid-file=/mysql/data/mcmpsitdb02.pid --socket=/mysql/data/mysql.sock --port=3306
mysql    11326  9205  0 17:18 pts/0    00:00:00 grep mysqld
[mysql@mcmpsitdb02 ~]$

mysql进程OK

如果出现启动进程文件缺失
[mysql@mcmpsitdb04 root]$ mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
[1] 11863
[mysql@mcmpsitdb04 root]$ bash: mysqld_safe: command not found
^C
[1]+  Exit 127                mysqld_safe --defaults-file=/home/mysql/etc/my.cnf

[mysql@mcmpsitdb04 root]$ cd ~
[mysql@mcmpsitdb04 ~]$ source .bashrc
[mysql@mcmpsitdb04 ~]$
[mysql@mcmpsitdb04 ~]$
[mysql@mcmpsitdb04 ~]$
[mysql@mcmpsitdb04 ~]$
[mysql@mcmpsitdb04 ~]$
[mysql@mcmpsitdb04 ~]$ source .bash_profile

执行完该命令后重新启动进程

 

五、MySQL登录时出现 Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES) 的原因及解决办法。

# mysql -u root -h 192.168.194.142 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'192.168.194.142' (using password: YES)

【解决办法】
1. 先用localhost登录
# mysql -u root -p
Enter password:
2. 执行授权命令
mysql> grant all privileges on *.* to root@'%' identified by '123';
Query OK, 0 rows affected (0.07 sec)
3. 退出再试
mysql> quit
Bye
再试登录：
# mysql -u root -h 192.168.194.142 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.33 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
成功啦！