前言:
在使用Ubuntu中,对MySQL不熟悉,在实践中不断总结,不断更新。
目录
前言:
在使用Ubuntu中,对MySQL不熟悉,在实践中不断总结,不断更新。
一、安装MySQL
1、更新
2、安装数据库
3、查看版本
二、sql语句
1.启动、重置、关闭mysql:
2.查看是否启动:
三、遇到的BUG和解决方案
1、qt for ros(Ubuntu18.04)无法支持数据库。
2、使用mysql -u root -p命令无法进入mysql:
解决:进入root权限,直接修改文件cnf,再修改root属性
3、解决中文编码:原文链接:原文
mysql的关键词:
一、安装MySQL
Linux通用三句话。直接一句一句的怼上去就OK。
1、更新
sudo apt update
2、安装数据库
sudo apt-get install -y mysql-server mysql-client
3、查看版本
mysql -V
初始化配置,设置密码:
sudo mysql_secure_installation
出现下面界面,一项一项进行配置:
#1
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N (我的选项)
#2设置密码
Please set the password for root here...
New password: (输入密码)
Re-enter new password: (重复输入)
#3、删除匿名用户?
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...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (我的选项)
#4禁止远程root登录?
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) : Y (我的选项)
#5
By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (我的选项)
#6立即重新加载权限表
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 (我的选项)
二、sql语句
1.启动、重置、关闭mysql:
sudo service mysql start
sudo /etc/init.d/mysql restart
sudo /etc/init.d/mysql stop
ps -e|grep mysql
2.查看是否启动:
3、进入mysql:
mysql -u root -p
4、展示数据库、表。
show databases;
show tables;
5、创建数据库、表
create database db;
create table tb;
6、使用特定的数据库
use db;
7、删除数据库:
drop database_name;
8、查看表得结构
describe table_naem;
9、查询
select * from table_name ;
select * from table_name where id = 3;
10、insert:
insert table_name(name_1,name_2,,,,) values(value_1,value_2,,,,);
11、查看mysql的编码机制:
show variables like "character%";
12、更新表的数据:
update
如果有两个latin1,则无法支持存中文到数据库。
解决:
SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_results = utf8;
SET character_set_server = utf8;
(重启数据库后失效)
12、查看
sudo systemctl status mysql
13、删除表中数据并使表自增序号中排
TRUNCATE TABLE table_name ;
三、遇到的BUG和解决方案
1、qt for ros(Ubuntu18.04)无法支持数据库。
解决方法:安装qt的mysql的驱动。
sudo apt-get install libqt5sql5-mysql
2、使用mysql -u root -p命令无法进入mysql:
解决:进入root权限,直接修改文件cnf,再修改root属性
sudo vim /etc/mysql/debian.cnf
sudo mysql -u root # I had to use "sudo" since is new installation
USE mysql;
uPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
exit;
service mysql restart
设置密码的要求:
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_dictionary_file | |
| validate_password_length | 3 |
| validate_password_mixed_case_count | 0 |
| validate_password_number_count | 3 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 0 |
+--------------------------------------+-------+
6 rows in set (0.00 sec)
question1:Access denied for user 'root'@'localhost'
select user, plugin from mysql.user;
update mysql.user set authentication_string=PASSWORD('12345678'), plugin='mysql_native_password' where user='root';
flush privileges;
sudo /etc/init.d/mysql restart
question2:Access denied for user 'root'@'localhost' (using password: YES)
权限不够:使用自带的用户进入mysql,键入授权。
sudo vim /etc/mysql/debian.cnf
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
3、解决中文编码:原文链接:原文
1 修改mysql的配置文件
sudo vim /etc/mysql/conf.d/mysql.cnf
在[mysql]的下方加入如下语句:(注:这个文件下没有配置,只有【mysql】)
no-auto-rehash
default-character-set=utf8
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下加入
socket = /var/run/mysqld/mysqld.sock
port = 3306
character-set-server=utf8
重启mysql:
sudo /etc/init.d/mysql restart
进入mysql,查看:
show variables like "character%";
4、导出数据库(linux)
mysqldump -u roos -p databasename > task.sql
mysql的关键词:
创建:create table table_name; create view view_name;
查询:select name;
操作:insert(插入), update(修改), delete(删除)
权限:grant