mysql 安装 调试 认证

安装

yum install -y mysql-server mysql mysql-deve

启动服务

/etc/init.d/mysqld start

远程数据dump

数据库备份

mysqldump –opt -h192.168.100.23 -u user -p 123456 dbname>dbname.sql

mysql建立

create database dbname

数据库还原

mysql dbname < dbname.sql

查看mysql用户

SELECT DISTINCT CONCAT(‘User: ”’,user,”’@”’,host,”’;’) AS query FROM mysql.user;

设置root密码

use mysql;
update user set password=passworD(“123456”) where user=’root’;
flush privileges;
exit；

设置远程允许登陆（只设置一个用户）

登陆数据库

mysql -uroot -p123456
use mysql

只有一个ip能访问 添加权限

GRANT ALL PRIVILEGES ON . TO root@”192.168.100.95” IDENTIFIED BY “123456” WITH GRANT OPTION;

所有都能访问 添加权限

GRANT ALL PRIVILEGES ON . TO root@”%” IDENTIFIED BY “123456” WITH GRANT OPTION;

刷新

FLUSH PRIVILEGES;

查看用户：

SELECT User, Password, Host FROM user;

添加用户：

CREATE USER ‘ppp’@’%’ IDENTIFIED BY ‘123456’;

删除用户：

Delete FROM user Where User=’root’ and Host=’192.168.100.95’;

报错：

Access denied for user ‘root’@’localhost’ (using password: YES)

解决：需要授权

GRANT ALL PRIVILEGES ON . TO root@”%” IDENTIFIED BY “123456” WITH GRANT OPTION;

没有mysql数据库的时候，解决方法：

vim /etc/my.conf

设置配置文件

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#添加此项 不需要认证进入
skip-grant-tables

重启数据库

service mysqld restart

添加认证

>GRANT ALL PRIVILEGES ON . TO root@”%” IDENTIFIED BY “123456” WITH GRANT OPTION;
>FLUSH PRIVILEGES;

远程登陆上去，本地上不去，配置一下本地localhost就可以

GRANT ALL PRIVILEGES ON . TO root@”localhost” IDENTIFIED BY “123456” WITH GRANT OPTION;