Ubuntu中安装mysql_ubuntu手动安装mysql数据库

                Ubuntu中安装mysql

查系统中有没有安装lMySQL数据库：        

dpkg -l | grep mysql

安装mysql命令：

 sudo  apt-get install mysql-server

安装完成之后查系统中有没有安装lMySQL数据库

dpkg -l | grep mysql

登录mysql数据库：   

       

-u 登陆的用户名， -p 用户密码，目前mysql数据库是没有密码的，Enter password:处直接回车，就能够进入mysql数据库。

登录时必须 sudo mysql -u root -p, 因为安装时root用户的密码是随机的，直接mysql -u root -p则无法登录，报错：
ERROR 1698 (28000): Access denied for user ‘root’@'localhost

sudo mysql -u root -p

通过 show databases; 可以查看当前的所有数据库

 show databases

 查看user表

select user, plugin from mysql.user;

这个初始化操作涉及下面5个步骤。

（1）安装验证密码插件。

（2）设置root管理员在数据库中的专有密码。

（3）随后删除匿名账户，并使用root管理员从远程登录数据库，以确保数据库上运行的业务的安全性。

（4）删除默认的测试数据库，取消测试数据库的一系列访问权限。

（5）刷新授权列表，让初始化的设定立即生效。

初始化的操作步骤

sukai@sukai:~# sudo mysql_secure_installation
 
Securing the MySQL server deployment.
 
Connecting to MySQL using a blank password.
 
VALIDATE PASSWORD PLUGIN can be used to test passwords
 
and improve security. It checks the strength of password
 
and allows the users to set only those passwords which are
 
secure enough. Would you like to setup VALIDATE PASSWORD plugin? #要安装验证密码插件吗?
 
Press y|Y for Yes, any other key for No: N # 这里我选择N
 
 
Please set the password for root here.
 
New password: #输入要为root管理员设置的数据库密码
 
Re-enter new password: #再次输入密码
 
 
 
By default, a MySQL installation has an anonymous user,
 
allowing anyone to log into MySQL without having to have
 
a user account created for them. This is intended only for
 
testing, and to make the installation go a bit smoother.
 
You should remove them before moving into a production
 
environment.
 
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #删除匿名账户
 
Success.
 
 
 
Normally, root should only be allowed to connect from
 
'localhost'. This ensures that someone cannot guess at
 
the root password from the network.
 
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N #禁止root管理员从远程登录，这里我没有禁止
 
 
 
... skipping.
 
By default, MySQL comes with a database named 'test' that
 
anyone can access. This is also intended only for testing,
 
and should be removed before moving into a production
 
environment.
 
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #删除test数据库并取消对它的访问权限
 
 
 
- Dropping test database...
 
Success.
 
- Removing privileges on test database...
 
Success.
 
Reloading the privilege tables will ensure that all changes
 
made so far will take effect immediately.
 
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y #刷新授权表，让初始化后的设定立即生效
 
Success.
 
All done!

设置密码时报错：

Re-enter new password: 
 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
 

用这个命令进入mysql

sudo mysql

在sql命令行输入以下命令回车，你就可以把密码改成mynewpassword

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'root';

设置成功控制台返回：

Query OK, 0 rows affected (0.03 sec)

exit回到终端命令行，输入 ：sudo mysql_secure_installation  继续设置

 mysql_secure_installation

Securing the MySQL server deployment.
 
Enter password for user root: 
 
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
 
Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
 
 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
 
Remove anonymous users? (Press y|Y for Yes, any other key for No) : n
 
 ... skipping.
 
 
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
 
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
 
 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
 
 
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n
 
 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
 
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

检查mysql服务状态：           

 systemctl status mysql

 再次用 登入数据库，Enter password:处输入刚设置的密码，回车，就能够进入mysql数据库。

mysql -u root -p

5. 停止mysql服务

sudo service mysql stop  

6.在防火墙中开启3306端口

　　sudo ufw allow 3306 

 用 navicat 工具操作数据库

ubuntu下安装navicat
1、官网下载https://www.navicat.com.cn/download/navicat-premium，

2、如果安装过程中发现界面出现乱码，打开start_navicat文件，会看到 export LANG=”en_US.UTF-8” 将这句话改为 export LANG=”zh_CN.UTF-8”。

7.mysql的默认存储路径为/var/lib/mysql/
查看当前数据存储路径

show variables like 'datadir';

8.导入mysql_ubuntu导入mysql数据库文件
mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u root -p tabename > tabename.sql

9.导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

mysqldump -u root -p tabename > tabename.sql

10.导出一个数据库结构

mysqldump -u root -p -d –add-drop-table tabename

10.导入数据库

常用source 命令

进入mysql数据库控制台，

mysql -u root -p

mysql>use 数据库

然后使用source命令，后面参数为脚本文件(如这里用到的.sql)

mysql>source

/home/sukai/test.sql

或直接导入命令为： mysql -h localhost -u root -p temp

mysql数据库存储路径修改

1.先关闭mysql数据库服务

/etc/init.d/mysql stop

2. 创建新目录

mkdir -p  ~/mysql/data

3. 复制数据到新目录

cp -R /var/lib/mysql /home/sukai/mysql/data

4. 修改data目录的权限

cd  ~/mysql/data
 

sudo chown -R mysql:mysql ./

或

sudo chown -R mysql:mysql /home/sukai/mysql/data/mysql

 恢复权限：

sudo chown sukai:sukai -R ./

注意：不要在自己主目录或其它目录执行 sudo chown -R mysql:mysql ./  权限命令，会导致所有文件目录不能操作的问题。使用 sudo chown sukai:sukai -R ./ 把所有文件恢复权限，重新进入mysql/data目录 执行权限命令。

5.编辑mysql的配置文件

将 [mysqld] 组下的 datadir改为：

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf  

datadir = /home/sukai/mysql/data/mysql

6. 修改启动文件

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

空格要注意，把原来的空格删掉，重新打空格 

# Allow data dir access
   /home/sukai/mysql/data/mysql/ r,
   /home/sukai/mysql/data/mysql/** rwk,
#  /var/lib/mysql/ r,
#  /var/lib/mysql/** rwk,
 

7. 重启MySQL

sudo /etc/init.d/apparmor restart

sudo /etc/init.d/mysql restart

 

8. 登录MySql并验证数据目录

mysql -u root -p

show variables like '%dir%';

当显示datadir 为我们更改的目录时，修改成功
 

cmakelist.txt

include_directories(
   include
  ${catkin_INCLUDE_DIRS}
        /usr/include/mysql
 
)
target_link_libraries(projet_node  ${catkin_LIBRARIES}  mysqlclient  )

Mysql允许远程访问[这部份不做]

1.开启数据库3306端口

查看3306端口是否对外开放

 netstat -an | grep 3306    

2.编辑 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件：

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

注释掉bind-address = 127.0.0.1

3.修改已有用户的host字段
修改mysql的user表，host字段为指定ip即可。如果想任意主机都可以连接，可以使用%

update user set host = '%' where user = 'root';
 

4.进入mysql数据库，执行授权命令：

  4.1新建远程连接用户

mysql -u root -p
 
mysql> grant all on *.* to root@'%' identified by '你的密码' with grant option;
 
mysql> flush privileges; # 刷新权限
 
mysql> exit

4.重启mysql：

sudo service mysql restart 

服务状态： 

 sudo systemctl status mysql