MySQL基本概念和常用命令

#ping命令查看server是否正常提够服务

mysqladmin-uroot-proot-hlocalhostping

#status命令查看当前msyqlserver的几个状态值

mysqladmin-uroot-proot-hlocalhoststatus

#processlist命令获取当前数据库连接线程信息

mysqladmin-uroot-proot-hlocalhostprocesslist

支持三种索引：B-Tree,R-Tree,Full-text(B-Tree,解决like查询低效问题)

root@localhost:mysql>showgrantsfor'username'@'hostname'

创建用户：createuser'test'@'localhost'IDENTIFIEDby'test';

root@localhost:mysql>Grantselect,update,delete,inserton*.*to'test'@'localhost';

root@localhost:mysql>grantalterontest.*to'test'@'localhost'

root@localhost:mysql>grantindexontest.t1to'test'@%.walmart.com

root@localhost:mysql>grantselect(id,value)ontest.t2to'test'@'%walmart.com'

root@localhost:mysql>grantexecuteontest.p1to'test'@'localhost';

1生成INSERT语句备份，在mysql运行的状体下想要取得某个时间点的备份，只有以下两种情况

(i)同一时刻取出所有数据，需要添加--single-transaction选项

root@localhost:mysql>mysqldump--single-transactiondbname

(ii)是数据库数据处于静止状态，添加--lock-all-tables选项

2生成特定格式的纯文本备份数据文件

root@localhost:mysql>select*INTOoutfile'dump.txt'fieldsterminatedby','optionallyenclosedby'"'linesterminatedby'\n'fromtableName;

或者

root@localhost:mysql>mysqldump-uroot-proot-T/D:/mysqldumptesttest_outfile--fields-enclosed-by=\"--fields--terminated-by=,

(i)如果是insert语句的逻辑备份

a:mysql-uroot-p<backup.sql

b:root@localhost:mysql>source/tmp/backup.sql

(ii)如果备份是以特殊分割符分割的纯数据文本文件

mysqlimport--user=name-password=pwdtest\--fields-enclosed-by=\"--fields-terminated-by=,/tmp/test_outfile.txt

或者

loaddatainfile'/tmp/test_outfile.txt'intotabletest_outfilefieldsterminatedby'"'enclosedby',';

1.MyISAM储存引擎的物理备份：

root@localhost:mysql>mysqlhotcopydb_namenew_db_name

2.InnoDB储存引擎的物理备份：使用ibbackup