mysql 管理指令
- 2019 年 11 月 12 日
- 筆記
1、sed -n "22,25p" /etc/init.d/mysqld 2、三种关闭mysql的方式 service mysqld stop kill pidxx mysqladmin xx shutdown 3、更换配置文件启动 mysqld_safe –defaults-file=/data/xx/my.cnf 2>&1 >/dev/null & 4、mysql几个常用软件 mysql, mysqladmin , mysqldump , mysqlbinlog 5、连接方式 mysql -h -u -p -P mysql -u -p -S /xx/x.sock 6、删除历史命令 history -d 211 history -c 清除 7、配置文件设置权限 600 8、退出 ： quit/exit 9、msyql初始化安全设置 delete from mysql.user; grant all privileges on . to myroot@'localhost' identified by 'pass' with grant option; flush privileges; 10、修改密码 mysqladmin -uroot -p password xxx set password for root@localhost=password('xxx') update user set password=password('123') where user='root' and host='localhost'; alter user 'root'@'localhost' IDENTIFIED BY 'liuhehe';
忘记密码：mysqld –skip-grant-tables
11、DQL,DML,DDL select * from x order by x delete from mysql.user where x='' create database dd1; show databases; show create database dd1G; create database dd2 default character set gbk collate gbk_chinese_i;
配置文件加字符集
-DDEFAULT_CHARSET= utf8 -DDEFAULT_COLLATION=utf8_general_ci select database(); use dd1; show tables; show tables from dd1; drop database xx; 12、 select user,host from mysql.user; create user x@x identified by 'x'; show grants for bbx@xx; drop user x@x; grant all privileges on x.* to x@x identified by x;
回收权限
revoke select on . from x@x; 13、DDL create table x( a int(4) not null, name char(3) not null, dep varchar(11) default NULL ); desc x; auto_increment rename x to y; alter table x rename to y; alter table x add b int(2) not null; alter table x drop qq; alter table x modify x ; alter table test add index x(name); create index indexqq on test(qq) 14、 insert into test(id,name) values(1,"dd"); delete from test; select * from test limit 1,3; selelect * from x id>2 and id <5; order by id asc/desc; update x set x=y, where x; source xx.sql; truncate table x; drop table if exists test;
