Ubuntu下MySQL的安装和配置
时间:2022-03-14 03:08
一、验证原有主机是否已安装MySQL:
这里主要是运行sudo netstat -tap | grep mysql命令查看是否有MySQL的端口,如果不加sudo的话因为权限无法顺利执行:
$ netstat -tap | grep mysql
二、安装MySQL: 在确认主机未安装MySQL后,运行命令sudo apt-get install mysql-server mysql-client进行在线安装:
在安装的过程中会提示你输入Yes,然后会弹出root密码设置界面,这里可以先设置一个root密码作为登录mysql用户使用,之后需要的时候也可以运行mysqladmin -u root -p password进行修改密码,当然那个时候得先输入原密码了。
$ sudo apt-get install mysql-server mysql-client
$ mysqladmin -u root -p password
错误分析与解决:
另外,在安装过程中也可能没有弹出密码设置界面,这时,当你使用mysql -u root -p 去登录MySQL服务器时,而且你已经输入了密码(尽管你并没有设置),经常会弹出以下窗口:
这个时候,再连接MySQL服务器时,就会成功了:
# 启动MySQL
$ sudo service mysql start
# 关闭MySQL
$ sudo service mysql stop
# 重启MySQL
$ sudo service mysql restart
# 其他命令:
$ sudo /etc/init.d/mysql start
$ sudo /etc/init.d/mysql stop
$ sudo /etc/init.d/mysql restart
四、登录MySQL: 使用命令行mysql -u root -p 来登录MySQL:
$ mysql -u root -p
如果是登录远端的MySQL服务器,命令格式为:
$ mysql -h hostname -u username -p
Enter password:
其中,hostname指的是MySQL服务器的IP地址或名字,但是需要远端服务器的授权。
举个例子:
首先,我在192.168.1.108的主机上运行MySQL服务器,现在,我使用root权限登录MySQL,然后增加远端主机用户:
mysql> create database mydb;
mysql> create user ‘kiterunner‘@‘localhost‘ identified by ‘110316‘;
mysql> drop user ‘kiterunner‘@‘localhost‘
4、访问权限设置:
4.1、MySQL赋予用户权限命令的简单格式可概括为:
mysql> grant 权限 on 数据库对象 to 用户 with grant option
(1)、grant普通数据用户,查询、插入、更新、删除数据库中所有表数据的权限
mysql> grant select,insert,update,delete on testdb.* to kiterunner@localhost
grant操作MySQL存储过程、函数权限: grant作用在单个数据库上: grant作用在表中的列上: grant作用在存储过程、函数上: 4.2、MySQL撤销用户权限命令的简单格式可概括为: 关于具体的权限配置可参考grant配置。 4.3、其他内容: 查看MySQL 用户权限: 4.4、实例验证: 例一、常规配置:mysql> grant create on testdb.* to kiterunner@localhost;
mysql> grant alter on testdb.* to kiterunner@localhost;
mysql> grant drop on testdb.* to kiterunner@localhost;
grant操作MySQL临时表权限:mysql> grant references on testdb.* to kiterunner@localhost;
grant操作MySQL索引权限:mysql> grant create temporary tables on testdb.* to kiterunner@localhost;
grant操作MySQL视图、查看视图源代码权限:mysql> grant index on testdb.* to kiterunner@localhost;
mysql> grant create view on testdb.* to kiterunner@localhost;
mysql> grant show view on testdb.* to kiterunner@localhost;
mysql> grant create routine on testdb.* to kiterunner@localhost;
mysql> grant alter routine on testdb.* to kiterunner@localhost;
mysql> grant execute on testdb.* to kiterunner@localhost;
mysql> grant all on testdb.* to kiterunner@localhost;
mysql> grant all on *.* to kiterunner@localhost;
mysql> grant select on *.* to kiterunner@localhost; -- kiterunner可以查询MySQL中所有数据库中的表。
mysql> grant all on *.* to kiterunner@localhost; -- kiterunner可以管理MySQL中的所有数据库
mysql> grant select on testdb.* to kiterunner@localhost; -- kiterunner可以查询testdb中的表
mysql> grant select(id, se, rank) on testdb.apache_log to kiterunner@localhost;
mysql> grant execute on procedure testdb.pr_add to kiterunner@localhost;
mysql> grant execute on function testdb.fn_add to kiterunner@localhost;
mysql> revoke 权限 on 数据库对象 from 用户
//查看当前用户(自己)权限:
mysql> show grants;
//查看其他 MySQL 用户权限:
mysql> show grants for kiterunner@localhost;
mysql> grant select on testdb.* to kiterunner@localhost with grant option;
我们可以通过grant命令来授权用户权限:
mysql> grant all on mydb.* to kiterunner@localhost;
mysql> revoke all on testdb.* from kiterunner@localhost
mysql> create user ‘test2‘@‘localhost‘ identified by ‘abc‘;
mysql> grant select,insert,update,delete on mydb.* to test2@localhost
方法二:
mysql> grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";
adoryn@apple:~$ mysql -u root -p
mysql> grant select,insert,update,delete,create,drop
on bankaccount.* to custom@localhost identified by ‘stupid‘;
mysql> grant select,insert,update,delete,create,drop
on expense.* to custom@whitehouse.gov identified by ‘stupid‘;
mysql> grant selecr,insert,update,delete,create,drop
on customer.* to custom@‘%‘ indentified by ‘stupid‘;
5、导入.sql文件:
首先,我们看看create_student.sql文件的内容:
mysql> source ./create_student.sql
# create student table for grade-keeping project
DROP TABLE IF EXISTS student;
#@ _CREATE_TABLE_
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sex ENUM(‘F‘,‘M‘) NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE=InnoDB;
#@ _CREATE_TABLE_
执行命令:
发现student表已经存在于mydb数据库中,说明sql文件导入成功。