Mysql 常用管理基础命令(学习笔记四)

1、赋予权限：

grant all privileges on xxx to 'xxx'@'%' with grant option;

2、查看最大连接数配置：

show variables like '%max_connections%';

3、查看已用连接数：

    show global status like 'Max_used_connections';

4、查看mysql版本

select version();

5、显示数据库中某张表的所有列：

show full columns from 数据库名.表名

6.解决数据库乱码问题 

linux: 

1)首先找到my.cnf文件,位置：/etc/my.cnf。 

2）在[mysqld]下面添加：character-set-server=utf8， 

　 在[mysql]下面添加：default-character-set=utf8，如下图

3）修改完成之后，重启mysql即可生效。 

7、修改数据库最大连接数

在/etc/my.cnf文件[mysqld]下面添加:max_connections=220

8.mysql启动，停止，重启 

service mysqld stop 

service mysqld start 

service mysqld restart 

9.查看mysql引擎：

show engines;

show variables like "%storage_engine%";

10、修改默认引擎：

vim /etc/my.cnf

default-storage-engin=InnoDB

11.修改表的引擎 

alter table tablename engine=innodb; 

12.查看表的创建语句 

show create table student;

13、关于mysql引擎

查看默认存储引擎

show variables like 'storage_engine';     

查看mysql支持的存储引擎

show variables like 'have%';

InnoDB      MyISAM     MEMORY   的存储引擎

InnoDB 优势在于提供了良好的事务管理，崩溃修复能力和并发控制。特点：读写效率稍微差，占用的数据空间相对比较大

 MyISAM 优势在于占用空间小，处理速度快，缺点不支持事务的完整性和并发性。

 MEMORY不常用。

InnoDB储存引擎支持事务处理，支持外键。同时支持崩溃修复能力，和并发控制。如果需要对事务的完整性要求比较高，要求实现并发控制，那选择InnoDB存储引擎有很大的优势。如果频繁的进行更新，删除操作的数据库，也可以选择InnoDB存储引擎。应为改存储引擎可以实现事务的提交和回滚。

MyISAM存储引擎的插入数据快，空间和内存使用比较低。如果表主要用于插入记录和读出记录，那么选择MyISAM比较高效率。如果应用的完整性，并发性要求低，也可以选择

14、忘记root密码

 编辑mysql主配置文件 my.cnf 在[mysqld]字段下添加参数  skip-grant  ,重启数据库服务,这样就可以进入数据库不用授权了 mysql -uroot ,修改相应用户密码 

use mysql;

 update user set password=password('your password') where  user='root';

flush privileges;  

最后修改/etc/my.cnf 去掉 skip-grant , 重启mysql服务

当我们第一次使用mysql命令的时候我们提示命令不存在的时候，我们加入path就行了

在配置文件中/etc/profile 中 

PATH=$PATH:/usr/local/mysql/bin

然后别忘记

source /etc/profile

或者写全路径   /usr/local/mysql/bin/mysql -uroot

给 mysql root用户设置密码

mysqladmin -uroot password '123456' 

登陆

mysql -uroot -p123456 

15、配置慢查询：

log_slow_queries=xx

long_query_time=1

16、常用操作

show databases;

use db;show tables;

desc tb;

show create table tb;

select user();

select database();

show status;

grant all on *.* to user1 identified by 'xxxxx';

创建普通用户并授权 grant all on *.* to user1 identified by '123456';

 grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222';

grant all on db1.* to 'user3'@'%'identified by '231222';insert into tb1 (id,name) values(1,'aming');

on后边加授权的库

%  所有的意思。。。。

更改密码 UPDATE mysql.user SET password=PASSWORD("newpwd") WHERE user='username' ;  

查询select count(*) from mysql.user;

select * from mysql.db; 

select * from mysql.db  \G;   (格式化输出）

select * from mysql.db where host like '10.0.%';

插入 update db1.t1 set name='aaa' where id=1;  

清空表 truncate table db1.t1;

删除表 drop table db1.t1;

删除数据库 drop database db1;

修复表 repair table （表名）tb1 [use frm];（可以最好加上)

17、mysql备份与恢复

mysqldump -uroot -pxxx db>2.sql

mysql -uroot -p db<1.sql

只备份一个表  mysqldump -uroot -p db tb1 > 2.sql

-d(只备份创建表的语句）

备份时指定字符集 mysqldump -uroot -p --default-character-set=utf8  db >1.sql

恢复也指定字符集 mysql -uroot -p --default-character-set=utf8  db  < 1.sql

18 https://www.pureweber.com/article/myisam-vs-innodb/

myisam innodb两种引擎的比较

19、

mysql常用命令{

./mysql/bin/mysqld_safe --user=mysql &   # 启动mysql服务

./mysql/bin/mysqladmin -uroot -p -S ./mysql/data/mysql.sock shutdown    # 停止mysql服务

mysqlcheck -uroot -p -S mysql.sock --optimize --databases account       # 检查、修复、优化MyISAM表

mysqlbinlog slave-relay-bin.000001              # 查看二进制日志(报错加绝对路径)

mysqladmin -h myhost -u root -p create dbname   # 创建数据库

flush privileges;             # 刷新

show databases;               # 显示所有数据库

use dbname;                   # 打开数据库

show tables;                  # 显示选中数据库中所有的表

desc tables;                  # 查看表结构

drop database name;           # 删除数据库

drop table name;              # 删除表

create database name;         # 创建数据库

select 列名称 from 表名称;    # 查询

show grants for repl;         # 查看用户权限

show processlist;             # 查看mysql进程

select user();                # 查看所有用户

show slave status\G;          # 查看主从状态

show variables;               # 查看所有参数变量

show table status             # 查看表的引擎状态

select host,user,password from user;            # 查询用户权限 先use mysql

create table ka(ka_id varchar(6),qianshu int);  # 创建表

SHOW VARIABLES LIKE 'character_set_%';          # 查看系统的字符集和排序方式的设定

show variables like '%timeout%';                # 查看超时(wait_timeout)

delete from user where user='';                 # 删除空用户

delete from user where user='sss' and host='localhost' ;    # 删除用户

ALTER TABLE mytable ENGINE = MyISAM ;                       # 改变现有的表使用的存储引擎

SHOW TABLE STATUS from  库名  where Name='表名';            # 查询表引擎

CREATE TABLE innodb (id int, title char(20)) ENGINE = INNODB                     # 创建表指定存储引擎的类型(MyISAM或INNODB)

grant replication slave on *.* to '用户'@'%'identified by '密码';               # 创建主从复制用户

ALTER TABLE player ADD INDEX weekcredit_faction_index (weekcredit, faction);     # 添加索引

alter table name add column accountid(列名)  int(11) NOT NULL(字段不为空);       # 插入字段

增加MySQL用户{

　　　格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

　　例1、增加一个用户user_1密码为123，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：

mysql> grant select,insert,update,delete on *.* touser_1@"%" Identified by "123";

mysql>flush privileges;

　　例1增加的用户是十分危险的，如果知道了user_1的密码，那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了，解决办法见例2。

例2、增加一个用户user_2密码为123,让此用户只可以在localhost上登录，并可以对数据库aaa进行查询、插入、修改、删除的操作(localhost指本地主机，即MySQL数据库所在的那台主机)，

这样用户即使用知道user_2的密码，他也无法从网上直接访问数据库，只能通过 MYSQL主机来操作aaa库。

mysql>grant select,insert,update,delete on aaa.* touser_2@localhostidentified by "123";

　　　用新增的用户如果登录不了MySQL，在登录时用如下命令：

　　　mysql -u user_1 -p　-h 192.168.113.50　(-h后跟的是要登录主机的ip地址)

}

登录mysql的命令{

# 格式： mysql -h 主机地址 -u 用户名 -p 用户密码

mysql -h110.110.110.110 -P3306 -uroot -p

mysql -uroot -p -S /data1/mysql5/data/mysql.sock -A  --default-character-set=GBK

}

shell执行mysql命令{

mysql -u$username -p$passwd -h$dbhost -P$dbport -A -e "

use $dbname;

delete from data where date=('$date1');

"    # 执行多条mysql命令

mysql -uroot -p -S mysql.sock -e "use db;alter table gift add column accountid  int(11) NOT NULL;flush privileges;"    # 不登陆mysql插入字段

  }

 备份数据库{

mysqldump -h host -u root -p --default-character-set=utf8 --hex-blob   dbname >dbname_backup.sql               # 不包括库名，还原需先创建库，在use

mysqldump -h host -u root -p --database --default-character-set=utf8 --hex-blob   dbname >dbname_backup.sql    # 包括库名，还原不需要创建库

/bin/mysqlhotcopy -u root -p    # mysqlhotcopy只能备份MyISAM引擎

mysqldump -u root -p -S mysql.sock --default-character-set=utf8 --hex-blob db table1 table1  > /data/db.sql    # 备份表

mysqldump -uroot -p123  -d database > database.sql    # 备份数据库结构

innobackupex --user=root --password="" --defaults-file=/data/mysql5/data/my_3306.cnf --socket=/data/mysql5/data/mysql.sock --slave-info --stream=tar --tmpdir=/data/dbbackup/temp /data/dbbackup/ 2>/data/dbbackup/dbbackup.log | gzip 1>/data/dbbackup/db50.tar.gz

# xtrabackup备份需单独安装软件 优点: 速度快,压力小,可直接恢复主从复制

}

还原数据库{

mysql -h host -u root -p dbname < dbname_backup.sql

source 路径.sql   # 登陆mysql后还原sql文件

}

赋权限{

# 指定IP: $IP  本机: localhost   所有IP地址: %   # 通常指定多条

grant all on zabbix.* touser@"$IP";             # 对现有账号赋予权限

grant select on database.* touser@"%" Identified by "passwd";     # 赋予查询权限(没有用户，直接创建)

grant all privileges on database.* touser@"$IP" identified by 'passwd';         # 赋予指定IP指定用户所有权限(不允许对当前库给其他用户赋权限)

grant all privileges on database.* touser@"localhost" identified by 'passwd' with grant option;   # 赋予本机指定用户所有权限(允许对当前库给其他用户赋权限)

grant select, insert, update, delete on database.* touser@'ip'identifiedby "passwd";   # 开放管理操作指令

revoke all on *.* fromuser@localhost;     # 回收权限

  }

 更改密码{

update user set password=password('passwd') where user='root';

flush privileges;

mysqladmin -u root password 'xuesong'

  }

 mysql忘记密码后重置{

cd /data/mysql5

/data/mysql5/bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

update user set password=password('123123') where user='root';

}

mysql主从复制失败恢复{

slave stop;

reset slave;

change master to master_host='10.11.241.126',master_port=3306,master_user='root',master_password='passwd',master_log_file='master-bin.00009,master_log_pos=98,master_connect_retry=60';

slave start;

}

}