mysql常用命令

登录sql：

mysql -u root -p
输入密码：

首次修改用户密码

ALTER USER 'root'@'localhost' IDENTIFIED BY ‘admin’; 

再次修改用户密码

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY ‘root‘;

创建新用户

create user 'develop'@'%' identified with mysql_native_password by '你的密码';

修改为允许任何地址访问

update user set Host='%' where User='develop';

授权develop用户拥有所有(.)数据库的权限**

grant all privileges on *.* to 'develop'@'%' with grant option;

刷新数据库

flush privileges;

查看数据库所有用户

select user,host from mysql.user;

创建数据库

create database demo;

修改数据库编码

alter database demo charset=utf8;

删除数据库

drop database demo;

查看当前数据库

select database();

查看所有的数据库

show databases;

查看mysql端口

show global variables like 'port';

更换当前使用的数据库

use databaseName;

查看当前数据库下的所有表的名称

show tables;

查看表字段

desc 表名;

退出数据库

 exit;

将user表中root的localhost改为%

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

**查看mysql安装路径

whereis mysql

查看运行文件所在路径

which mysql

linux下导入、导出mysql数据库命令
1.导出数据和表结构：（不需要登录入数据库中）

 mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
 mysqldump -uroot -proot abc > abc.sql

2.导入数据库
方法一

mysql>create database abc;//首先建空数据库
mysql> use abc;//选择数据库
mysql> set names utf8;//设置数据库编码
mysql> source /root/abc.sql;//导入数据

-方法二
mysql -u用户名 -p密码 数据库名 < 数据库名.sql

eg:mysql -uabc_f -p abc < abc.sql

删除数据库

drop database db1;

mysql配置文件（/etc/my.cnf）
端口配置 如端口:3400

----------------------------------------
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[client]
port=3400
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
port=3400
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character_set_server=utf8
init_connect='SET NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid