mysql常用命令

连接mysql

指定数据库连接

mysql -uroot  -proot -h127.0.0.1 mysql_database;

指定数据库连接并指定字符集

mysql -uroot  -proot -h127.0.0.1 mysql_database --default-character-set=utf8;

导出某张表

/usr/bin/mysqldump -proot -h127.0.0.1 --single-transaction mysql_database mysql_table > mysql_table.sql

导出内容

mysql -uroot  -proot -h127.0.0.1 mysql_database --default-character-set=utf8 -e "select查询语句" > 0420.txt

创建用户

grant select on mysql_database.* to test@"127.0.0.1" identified by "password";
flush privileges;

统计表大小

select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 \
as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;

数据恢复 来自于binlog

mysqlbinlog mysql-bin.000018  -d mysql_database --start-datetime="2016-09-20 00:00:00"  --stop-datetime="2017-03-20 16:30:00"  > mysql03221001.sql

分割日志提取想要表的信息

cat  mysql03211150.sql|grep mysql_table -A10|grep --ignore-case -E -A10 'CREATE table|alter table'|grep -v "^UPDATE"|grep -v "^#"|grep -v "^/"|grep -v "^BEGIN"|grep -v "^SET" > ~/mysql_table.sql