データベースDBAベース
###############################################################
###########################################################
###########################################################
###########################################################
##############################################################
/etc/my.cnf
/var/lib/mysql
/var/log/mysqld.log
DDL create/alter/drop
DML insert/update/delete
DCL grant/revoke
DTL commit/rollback/savepoint
show databases; //
select user(); //
use ; //
select database(); //
create database ; //
show tables; //
drop database ; //
desc , ; //
select * from . //
insert into . values( ) //
update . set = ; //
delete from //
: char varchar
: int float double
: date year time
: enum set
###########################################################
alter table . ;
add
modify
change
drop
rename
index
unique
fulltext
primary key
foreign key
###########################################################
show variables like "secure_file_priv" //
:
load data infile " / "
into table .
fields terminated by " "
lines terminated by "
"
:
select * from
into outfile " / "
fields terminated by " "
lines terminated by "
"
distinct //
between and // ..
where regexp ' '
avg( )
sum( )
min( )
max( )
count( )
SQL order by [asc/desc];
asc
desc
SQL group by ;
SQL having ;
SQL limit ; //
SQL limit 1, 2 //
###########################################################
grant
grant on to @" "
identified by " "
with grant option; //
-all //
-usage //
-select,update,insert //
-select,update( 1. 2) //
-% //
-192.168.4.% //
-localhost //
select user() //
show grants //
show grants for @" " //
set password=password(" ") //
set password for @" " = password(" ") //
drop user @" " // ( )
mysql
- user
- db
- tables_priv
- columns_priv
revoke on . from @" ";
root :
+ skip-grant-tables
mysql
update mysql.user set authentication_string=password(" ")
where user="root" and host="localhost"
flush privileges;
root
]# mysqladmin -uroot -p' ' password" "
- : cp、tar
-mysqldump //
-mysql //
:
cp -r /mysql.bak/ /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
:
: ,
: ,
:mysqldump -uroot -p > xxx.sql
-A -B
:mysql -uroot -p [ ] < xxx.sql
binlog
:
log_bin // binlog server_id=100 // id
-bin.index
show variables like "binlog_format" //
show master status //
flush logs //
system ls /mylog //
purge master logs to " " //
##############################################################