mysql常用命令

常用mysql命令：

常用导入操作

导入数据库  source命令
使用 source 命令需要我们先登录到 mysql 中，并创建一个空的数据库：
mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/fairy/桌面/job.sql  # 导入备份数据库
 
 
这种方法导入也可以。（不管是导入数据库表结构和数据都行）
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
mysql -u root -p bbb </home/Person.sql

 查看mysql安装路径

查看mysql安装路径：
指令 ps -ef|grep mysql 得出结果
root 968 1 0 18:25 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/tmp/mysqld/mysqld.pid
mysql 1400 968 0 18:25 ? 00:00:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/tmp/mysqld/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root 9318 7985 0 19:49 pts/0 00:00:00 grep --color=auto mysql
其中：basedir：这个后面就是mysql安装路径（一般需要把mysqldump 这个命令连接到默认的路径），就是说如果我安装的路径是：/usr/local/mysql 的话 那么mysqldump 的文件就在 他的 下级 目录 bin 里面
ln -fs /usr/local/mysql/bin/mysqldump /usr/bin
这个就是快捷命令

 常用导出操作

导出数据库中的表结构和数据
格式：mysqldump -u root -p 数据库名字 > /要生成sql文件的路径/文件名字.sql
mysqldump -u root -p syn > /home/syn.sql #导出数据库
123456 #输入数据库密码
扩展：
mysqldump -u root -p bbb Person > /home/Person.sql #导出数据库中的某个表(注意：首先数据库要有bbb这个数据库才能使用这个命令)
注意：(在执行上述命令之前先看看)
创建数据库：
(1)登录mysql：mysql -u root -p
(2)查看是否有bbb这个数据库
show databases;
(3)没的话创建一个（有的就不用了）
create database bbb;
（4）设置数据库编码
set names utf8;
 
 
只导出数据库中的表结构：
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
/usr/local/mysql/bin/ mysqldump -uroot -p -d syn > syn.sql
注：/usr/local/mysql/bin/ —> mysql的data目录
 
mysqldump -u root -p --skip_add_locks  --skip-lock-tables  -q -d xxl_job  > /var/lib/mysql/xxl_job2.sql   导出表结构 -d参数表结构
mysqldump -u root -p  --skip_add_locks --skip-lock-tables -q  ctg_sysmgr > /var/tmp/ctg_sysmgr.sql        导出全库
mysqldump -u root -p1B25mzXwmcxk0N6qXhI0 --skip_add_locks --skip-lock-tables ctg_sysmgr organization > /tmp/organization.sql  导出ctg_sysmgr库的organization表

 pxc集群常用操作

pxc集群数据备份mysqldump命令要添加--skip-add-locks --skip-lock-tables参数
pxc集群导入数据，要先进入mysql执行SET GLOBAL pxc_strict_mode=DISABLED;
pxc集群全部故障退出无法启动，需要在先启动的pxc机器的/data/docker/data-root/volumes/vmysql/_data下，编辑文件grastate.dat，将safe_to_bootstrap改为1
 
show status like 'wsrep_cluster%';  查看pxc集群节点
show status like '%wsrep%';  查看pxc集群信息

常用查表操作

查看表结构：
use 数据库
show tables；
describe 表名称 
一般简写：  desc 表名称
desc oss_platform_conf;     查看表结构
 
查看表内容：
select * from oss_platform_conf\G;
 
show databases;  查看库
 
use 库；
show tables； 查看表

更新和删除表操作

更新和删除表内容：
update ecs.oss_platform_conf set admin='{"admin":"admin","secure":false,"server":"13.11.8.13","verify":true,"timeout":10,"access_key":"JTEFEHLUCS26G236ZVSH","secret_key":"zxjEq63Ac0B6JCQ9qIvyB9Tnk9KzJqeNIGtz58l5"}' where id=1; 
 
UPDATE  表名   SET  runoob_titile='test111'  WHERE runoob_id=3;      修改表中runoob_id=3的runoob_titile的值为test111
 
DELETE FROM tb_courses  WHERE course_id=4;          在tb_courses_new 表中，删除 course_id 为 4 的记录  
       
use 库；
delete  from  表名；  清空表中的内容，用于对象存储刷新
或清空表内容  truncate table 表名;
 
删除数据库：
drop database <数据库名>；
清空表内容：
delete  from 表名；
删除表：
drop table 表名;

 修改字段操作

#################修改字段#####################
################修改字段名称
MySQL 中修改表字段名的语法规则如下：
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
其中：
旧字段名：指修改前的字段名；
新字段名：指修改后的字段名；
新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
例 1：
使用 ALTER TABLE 修改表 tb_emp1 的结构，将 col1 字段名称改为 col3，同时将数据类型变为 CHAR(30)，SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1
    -> CHANGE col1 col3 CHAR(30);
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col3   | char(30)    | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。
提示：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。
 
######################修改字段数据类型
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中：
表名：指要修改数据类型的字段所在表的名称；
字段名：指需要修改的字段；
数据类型：指修改后字段的新数据类型。
例 2：
使用 ALTER TABLE 修改表 tb_emp1 的结构，将 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30)，SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1
    -> MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1   | int(11)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| col2   | int(11)     | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
语句执行后，发现表 tb_emp1 中 name 字段的数据类型已经修改成 VARCHAR(30)，修改成功。
 
######################删除字段
删除字段是将数据表中的某个字段从表中移除，语法格式如下：
ALTER TABLE <表名> DROP <字段名>；
其中，“字段名”指需要从表中删除的字段的名称。
例 3：
使用 ALTER TABLE 修改表 tb_emp1 的结构，删除 col2 字段，SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1
    -> DROP col2;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1   | int(11)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

常用用户操作

用户操作：
删除用户：
drop
        drop user XXX;删除已存在的用户，默认删除的是'XXX'@'%'这个用户，如果还有其他的用户如'XXX'@'localhost'等，不会一起被删除。
        如果要删除'XXX'@'localhost'，使用drop删除时需要加上host即drop user 'XXX'@'localhost'。
delete
        delete from user where user='XXX' and host='localhost';其中XXX为用户名，localhost为主机名。
区别
        drop不仅会将user表中的数据删除，还会删除其他权限表的内容。
        而delete只删除user表中的内容，所以使用delete删除用户后需要执行FLUSH PRIVILEGES;刷新权限，否则下次使用create语句创建用户时会报错。
 
新建用户：
create user 'haproxy'@'%' IDENTIFIED BY '';
授权test用户仅能操作test_database数据库：
grant all privileges on test_database.* to 'test'@'%' identified by '123' with grant option;
 
修改test用户密码为456：
update user set authentication_string = password('456') where user = 'test' and host = '%'；
 
撤销用户权限：
revoke all on 数据库名.表名　from '用户名'@'主机名';

其它命令

配置文件和其它操作：
 
禁止mysql serverDNS解析，解决mysql   could not be resolved:警告：
在my.ini或者my.cnf的mysqld下添加如下，然后重启服务：
skip-host-cache
skip-name-resolve
它将禁止 MySql Server 对外部连接进行 DNS 解析，这么设置还解决了 就是某些dns解析不正常导致的连接巨慢问题。使用这一选项可以消除 MySql 进行 DNS 解析的时间。
但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求，需要修改一下将localhost替换为127.0.0.1。
 
select now();命令查看MySql时间（包括时分秒）
select current_date();命令查看MySql时间（不包括时分秒）
修改mysql时间：
1、用“sudo cat my.cnf”命令查看“my.cnf”文件；
2、用“vim mysqld.cnf”命令编辑文件内容，在文件内添加“default-time_zone = '+8:00'”语句；
3、重启mysql使新时间生效。
 
查看mysql连接超时时间，默认28800，8小时
 show variables like  '%timeout%';
 
查看一次最大插入数据，默认4M
show VARIABLES like '%max_allowed_packet%';
 
查看用户权限
show grants for root;
 
修改mysql-root密码
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 12345678
 
mysql不能输入中文：（以下是显示的，修改成如下）
mysql> show variables like'%char%'
    -> ;
+---------------------------+---------------------------------------------+
| Variable_name             | Value                                       |
+---------------------------+---------------------------------------------+
| character_set_client      | utf8mb4                                     |
| character_set_connection  | utf8mb4                                     |
| character_set_database    | utf8mb4                                     |
| character_set_filesystem  | binary                                      |
| character_set_results     | utf8mb4                                     |
| character_set_server      | utf8mb4                                     |
| character_set_system      | utf8                                        |
| character_sets_dir        | /usr/share/percona-xtradb-cluster/charsets/ |
| ft_query_extra_word_chars | OFF                                         |
+---------------------------+---------------------------------------------+
修改语句  例：set character_set_database=utf8; 
 
 
查看最大连接数
show variables like '%max_connections%';
 
修改最大连接数   set GLOBAL max_connections=5000;
查看连接数
show full processlist;
 
查看表约束
 SELECT * FROM information_schema.`TABLE_CONSTRAINTS` where table_name='表名';
 
 主从同步
查看主状态show master status\G;
查看从状态show slave status\G;
 
数据库改名：
rename database 旧数据库名  to 新数据库名