MySQL维护常用命令
部门内部资料！禁止提供给非本部门人员！！
登录操作
登录默认实例(3306)
/usr/local/mysql/bin/mysql -uroot -p{马赛克} -S /tmp/mysql.sock
登录3307实例
/usr/local/mysql/bin/mysql -uroot -p{马赛克} -S /tmp/mysql_3307.sock
查看操作
查看数据库
show databases;
use 91play;
查看表和查看表结构
show tables;
describe user;
查看进程
show processlist;
查看主从状态
show master status\G;
show slave status\G;
授权操作
创建数据库
CREATE DATABASE apic;
新增用户并授权
CREATE USER 'apic'@'%' IDENTIFIED BY '{马赛克}';
flush privileges;
查看用户授权
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
show grants for 'apic'@'%';
给用户授权
grant select,insert,update,delete on apic.* to apic@'%';
GRANT select,insert,update,delete,ALTER, CREATE, DROP ON `shouji`.* TO 'baidu_shouji'@'%';
flush privileges;
移除用户授权(记得从库移除写操作)
REVOKE INSERT, UPDATE, DELETE ON `apic`.* FROM 'apic'@'%'
REVOKE INSERT, UPDATE, DELETE, ALTER, CREATE, DROP ON `shouji`.* FROM 'baidu_shouji'@'%'
flush privileges;
导入操作
导入.sql文件
mysql -uroot -p apic < /data/apic_v2.sql
使用FTP下载:
10.79.155.50
cwt
91^baidu
10.79.156.55
91RD
91@baidu
ftp 10.79.156.55
ls
get db.sql /data/db.sql
多线程下载：
/usr/local/bin/aria2c -x 16 --ftp-user=91RD --ftp-passwd=91@baidu ftp://10.79.156.55/DB.tar.gz
wget下载:
wget -P /data/lost+found ftp://cwt:91^baidu@10.79.155.50/DB.tar.gz