部署mysql
两种linux系统,商业版本以Redhat为代表,开源社区版则以debian为代表
1.Ubuntu(debian系)
1.1安装
sudo apt-get install mysql-server
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
1.2修改root密码
cd etc/mysql
sudo vim debian.cnf
[client]
host = localhost
user = debian-sys-maint
password = Hy1Q8e5dUA4S2Bgb
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = Hy1Q8e5dUA4S2Bgb
socket = /var/run/mysqld/mysqld.sock
~
记住user和password
3.登陆
mysql -udebian-sys-maint -pHy1Q8e5dUA4S2Bgb
4.修改密码
mysql> use mysql;
mysql> update user set authentication_string=password('123456') where user='root';
flush privileges;
mysql> quit;
5.重启
sudo service mysql restart
6.配置远程访问root
mysql> update user set host='%' where user='root' AND host='localhost';
mysql>flush privileges;
2.CentOS(Redhat系)
#获取rpm
wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
#安装rpm
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
#安装mysql
yum install mysql-community-server
#启动mysql服务
systemctl start mysqld
#设置mysql开机启动
systemctl enable mysqld
systemctl daemon-reload
#查找root密码
grep 'temporary password' /var/log/mysqld.log
#登陆
mysql -u root -p
#修改密码
set password for 'root'@'localhost'=password('新的密码');
#重启mysql
systemctl restart mysqld
其余问题参照Ubuntu
3.安装mysql后普通用户无法登陆问题解决(ubuntu18.04)
Ubuntu18.04上安装mysql
sudo apt install mysql-server
sudo apt install mysql-client
sudo apt install libmysqlclient-dev
然后发现在普通用户shell下无法使用root用户名登录MySQL。切换到root的shell后可以无需密码登录到MySQL。
sudo su
mysql
select user, plugin from mysql.user;
123
发现结果是这样的
+------------------+-----------------------+
| user | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.session | mysql_native_password |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+
12345678
发现MySQL的root用户的plugin被修改了,只有换成mysql_native_password才可以使用密码登录MySQL。
使用下面的命令更改plugin,同时设置MySQL的root访问密码
update mysql.user set authentication_string=PASSWORD('12345678'), plugin='mysql_native_password' where user='root';
flush privileges;
123
退出后,切换到普通用户shell并重启MySQL,即可使用root用户和上一步设置的root密码登录MySQL。
4.mysql远程navicat无法连接问题
cd etc/mysql/mysql.conf.d
vim mysqld.cof
注释bind-address=127.0.0.1
登陆mysql
mysql -uroot -p
Enter password
mysql>use mysql;
mysqk>select host,user from user;
#修改登陆权限
mysql>update user set host='%' where user='root';
#刷新
mysql>flush priviliges;
5.mysql添加用户并授权
5.1添加
第一种方式
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
#eg
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
username=创建的用户名
host=指定用户可以在哪里主机上登陆,此处localhost=127.0.0.1限定为本机,如果想远程登陆改为"%"
password=新密码
第二种方式
use mysql;
insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));
(注意:MySQL5.7 mysql.user表没有password字段,改为了 authentication_string)
这样就创建了一个名为:test 密码为:1234 的用户。
5.2授权
格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by “密码”;
#全部权限#
grant all privileges on *.* to my@localhost identified by '123456';
#部分权限#
grant insert,update,delete,select on *.* to mytest@localhost;
#更新权限#
flush privileges;