mysql notes

1.管理命令

show variables  #查看各种系统参数，max_connections 等

show status     #查看数据库运行数据

 

2.常用sql

查整个库的状态：
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size
from information_schema.tables where TABLE_SCHEMA = 'runtime';

查单表：
select table_name,concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size
from information_schema.tables where TABLE_NAME = 'nagios_servicechecks';

 

备份恢复

mysqldump -uroot -pxxxx --all-databases >./xxxx.sql

source xxxx.sql

 

3.perl DBI

# connect
my $dbh = DBI->connect("DBI:mysql:database=db2;host=localhost", "joe", "guessme", {'RaiseError' => 1});

# execute INSERT query
my $rows = $dbh->do("INSERT INTO users (id, username, country) VALUES (4, 'jay', 'CZ')");
print "$rows row(s) affected ";

# execute SELECT query
my $sth = $dbh->prepare("SELECT username, country FROM users");
$sth->execute();

# iterate through resultset
# print values
while(my $ref = $sth->fetchrow_hashref()) {
    print "User: $ref-> ";
    print "Country: $ref-> ";
    print "---------- ";
}

# clean up
$dbh->disconnect();