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 column from table;     # 查询
    show processlist;             # 查看mysql进程
    show full processlist;        # 显示进程全的语句
    select user();                # 查看所有用户
    show slave status\G;          # 查看主从状态
    show variables;               # 查看所有参数变量
    show status;                  # 运行状态
    show table status             # 查看表的引擎状态
    show grants for user@'%'                        # 查看用户权限
    drop table if exists user                       # 表存在就删除
    create table if not exists user                 # 表不存在就创建
    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%';                # 查看超时相关参数
    delete from user where user='';                 # 删除空用户
    delete from user where user='sss' and host='localhost' ;    # 删除用户
    drop user 'sss'@'localhost';                                # 使用此方法删除用户更为靠谱
    ALTER TABLE mytable ENGINE = MyISAM ;                       # 改变现有的表使用的存储引擎
    SHOW TABLE STATUS from  dbname  where Name='tablename';     # 查询表引擎
    mysql -uroot -p -A -ss -h10.10.10.5 -e "show databases;"    # shell中获取数据不带表格 -ss参数
    CREATE TABLE innodb (id int, title char(20)) ENGINE = INNODB                     # 创建表指定存储引擎的类型(MyISAM或INNODB)
    grant replication slave on *.* to 'user'@'%' identified by 'pwd';                # 创建主从复制用户
    ALTER TABLE player ADD INDEX weekcredit_faction_index (weekcredit, faction);     # 添加索引
    alter table name add column accountid(column)  int(11) NOT NULL(column);         # 插入字段
    update host set monitor_state='Y',hostname='xuesong' where ip='192.168.1.1';     # 更新数据
    select * from information_schema.processlist where command!='sleep';             # 查看当前进程
    select * from atable where name='on' AND t<15 AND host LIKE '10%' limit 1,10;    # 多条件查询
    show create database ops_deploy;                                                 # 查看数据库编码
    show create table updatelog;                                                     # 查看数据库表编码
    alter database ops_deploy CHARACTER SET utf8;                                    # 修改数据库编码
    alter table `updatelog` default character set utf8;                              # 修改表编码
    alter table `updatelog` convert to character set utf8;                           # 修改一张表的所有字段的编码格式

    自增表

        create table xuesong  (id INTEGER  PRIMARY KEY AUTO_INCREMENT, name CHAR(30) NOT NULL, age integer , sex CHAR(15) );  # 创建自增表
        insert into xuesong(name,age,sex) values(%s,%s,%s)  # 自增插入数据


    登录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 root -p'123' xuesong < file.sql   # 针对指定库执行sql文件中的语句,好处不需要转义特殊符号,一条语句可以换行.不指定库执行时语句中需要先use
        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;"  2>&1 |grep -v Warning    # 不登陆mysql插入字段



    mysql字符集相关

        show variables like '%character%';      # 查看数据库中设置字符集的参数
        # character_set_client、character_set_connection 以及 character_set_results 这几个参数都是客户端的设置
        # character_set_system、character_set_server 以及 character_set_database 是指服务器端的设置。
        # 而对于这三个服务器端的参数来说的优先级是:
        # 列级字符集 > 表级字符集 > character_set_database > character_set_server > character_set_system

        show global variables like '%char%';                                 #查看RDS实例字符集相关参数设置
        show global variables like 'coll%';                                  #查看当前会话字符序相关参数设置
        show character set;                                                  #查看实例支持的字符集
        show collation;                                                      #查看实例支持的字符序
        show create table table_name \G                                      #查看表字符集设置
        show create database database_name \G                                #查看数据库字符集设置
        show create procedure procedure_name \G                              #查看存储过程字符集设置
        show procedure status \G                                             #查看存储过程字符集设置
        alter database db_name default charset utf8;                         #修改数据库的字符集 
        create database db_name character set utf8;                          #创建数据库时指定字符集
        alter table tab_name default charset utf8 collate utf8_general_ci;   #修改表字符集和字符序

        # 下面三条sql 分别将库 dbsdq , 表 tt2 , 表 tt2 中的 c2 列修改为utf8mb4 字符集
        alter database dbsdq character set utf8mb4 collate utf8mb4_unicode_ci;
        use dbsdq;
        alter table tt2 character set utf8mb4 collate utf8mb4_unicode_ci;
        alter table tt2 modify c2  varchar(10) character set utf8mb4;
        # 修改列时,当前列中的所有行都会立即转化为新的字符集;
        # alter table 会对表加元数据锁


    备份数据库

        mysqldump -h host -u root -p --default-character-set=utf8 dbname >dbname_backup.sql               # 不包括库名，还原需先创建库，在use
        mysqldump -h host -u root -p --database --default-character-set=utf8 dbname >dbname_backup.sql    # 包括库名，还原不需要创建库
        /bin/mysqlhotcopy -u root -p    # mysqlhotcopy只能备份MyISAM引擎
        mysqldump -u root -p -S mysql.sock --default-character-set=utf8 dbname table1 table2  > /data/db.sql    # 备份表
        mysqldump -uroot -p123  -d database > database.sql    # 备份数据库结构

        # 最小权限备份
        grant select on db_name.* to dbbackup@"localhost" Identified by "passwd";
        # --single-transaction  InnoDB有时间戳 只备份开始那一刻的数据,备份过程中的数据不会备份
        mysqldump -hlocalhost -P 3306 -u dbbackup --single-transaction  -p"passwd" --database dbname >dbname.sql

        # xtrabackup备份需单独安装软件 优点: 速度快,压力小,可直接恢复主从复制
        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


    还原数据库

        mysql -h host -u root -p dbname < dbname_backup.sql
        source 路径.sql   # 登陆mysql后还原sql文件


    赋权限

        # 指定IP: $IP  本机: localhost   所有IP地址: %   # 通常指定多条
        grant all on zabbix.* to user@"$IP";             # 对现有账号赋予权限
        grant select on database.* to user@"%" Identified by "passwd";     # 赋予查询权限(没有用户，直接创建)
        grant all privileges on database.* to user@"$IP" identified by 'passwd';         # 赋予指定IP指定用户所有权限(不允许对当前库给其他用户赋权限)
        grant all privileges on database.* to user@"localhost" identified by 'passwd' with grant option;   # 赋予本机指定用户所有权限(允许对当前库给其他用户赋权限)
        grant select, insert, update, delete on database.* to user@'ip'identified by "passwd";   # 开放管理操作指令
        revoke all on *.* from user@localhost;     # 回收权限
        GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `storemisc_dev`.* TO 'user'@'192.168.%'


    更改密码

        update user set password=password('passwd') where user='root'
        mysqladmin -u root password 'xuesong'


    mysql忘记密码后重置

        cd /data/mysql5
        /data/mysql5/bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
        /usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
        use mysql;
        update user set password=password('123123') where user='root';


    mysql主从复制失败恢复

        slave stop;
        reset slave;
        change master to master_host='10.10.10.110',master_port=3306,master_user='repl',master_password='repl',master_log_file='master-bin.000010',master_log_pos=107,master_connect_retry=60;
        slave start;


    sql语句使用变量

        use xuesong;
        set @a=concat('my',weekday(curdate()));    # 组合时间变量
        set @sql := concat('CREATE TABLE IF NOT EXISTS ',@a,'( id INT(11) NOT NULL )');   # 组合sql语句
        select @sql;                    # 查看语句
        prepare create_tb from @sql;    # 准备
        execute create_tb;              # 执行


    检测mysql主从复制延迟

        1、在从库定时执行更新主库中的一个timeout数值
        2、同时取出从库中的timeout值对比判断从库与主库的延迟


    死锁

        show OPEN TABLES where In_use > 0;                  # 查看当前锁信息
        show variables like 'innodb_print_all_deadlocks';   # 查看当前死锁参数
        set global innodb_print_all_deadlocks = 1;          # 设置死锁信息保存到错误日志
        innodb_print_all_deadlocks = 1                      # conf配置


    mysql慢查询

        select * from information_schema.processlist where command in ('Query') and time >5\G      # 查询操作大于5S的进程

        开启慢查询日志

            # 配置文件 /etc/my.conf
            [mysqld]
            log-slow-queries=/var/lib/mysql/slowquery.log         # 指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
            long_query_time=5                                     # 记录超过的时间，默认为10s 建议0.5S
            log-queries-not-using-indexes                         # log下来没有使用索引的query,可以根据情况决定是否开启  可不加
            log-long-format                                       # 如果设置了，所有没有使用索引的查询也将被记录    可不加
            # 直接修改生效
            show variables like "%slow%";                         # 查看慢查询状态
            set global slow_query_log='ON';                       # 开启慢查询日志 变量可能不同，看上句查询出来的变量

        

        mysqldumpslow慢查询日志查看

            -s  # 是order的顺序，包括看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序
            -t  # 是top n的意思，即为返回前面多少条的数据
            -g  # 后边可以写一个正则匹配模式，大小写不敏感的

            mysqldumpslow -s c -t 20 host-slow.log                # 访问次数最多的20个sql语句
            mysqldumpslow -s r -t 20 host-slow.log                # 返回记录集最多的20个sql
            mysqldumpslow -t 10 -s t -g "left join" host-slow.log # 按照时间返回前10条里面含有左连接的sql语句

            show global status like '%slow%';                     # 查看现在这个session有多少个慢查询
            show variables like '%slow%';                         # 查看慢查询日志是否开启，如果slow_query_log和log_slow_queries显示为on，说明服务器的慢查询日志已经开启
            show variables like '%long%';                         # 查看超时阀值
            desc select * from wei where text='xishizhaohua'\G;   # 扫描整张表 tepe:ALL  没有使用索引 key:NULL
            create index text_index on wei(text);                 # 创建索引

        

        Percona Toolkit 慢日志分析工具

    

    mysql操作次数查询

        select * from information_schema.global_status;

        com_select  select语句执行了多少次
        com_delete  delete语句执行了多少次
        com_insert  insert语句执行了多少次
        com_update  update语句执行了多少次　　　　mysql kill　　　　　　　　　　　　　　select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sending data' and Time > 100 order by Time desc into outfile '/tmp/a.txt';　　　　　　　source /tmp/a.txt;

max_execution_time 这个参数设置20000 ，表示大于20秒 select语句自动kill 5.7+版本，支持

show processlist;

for i in mysql -uroot -h 172.21.0.33 -p -se "show processlist" | grep -v "show processlist" | awk '{if($6 > 500) print $1}'; do mysql -uroot -h 172.21.0.33 -p se “kill $i”; echo $i; done

仅安装mysql客户端

1.安装 rpm源
　　　　　　rpm -ivh https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm　　　　　　2.安装
　　　　　　yum install mysql-community-client.x86_64