一、登录mysql:mysql -u<用户名> -p;
二、备份数据库到指定文件:mysqldump -u root -p --<db_name> > news.sql
三、授权远程访问:GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
四、创建数据库:
CREATE DATABASE IF NOT EXISTS <db_name> DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
五、创建表
CREATE TABLE `test` (
`id` bigint(30) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(64) NOT NULL DEFAULT '' COMMENT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17621402 DEFAULT CHARSET=utf8 COMMENT='test';
六、表增加字段
ALTER TABLE <tablename> add <column> <columnType>,add <column> <columnType>;
七、表删除字段
alter table <tablename> drop column <columnname>,drop column <columnname>;
八、查看mysql存储目录:
show global variables like "%datadir%";
九、mysql索引相关操作
1、查看索引:show index from <table_name>;
2、创建索引:create [unique]index <indexName> on tablename(columnname(length));
alert table <tablename> add [unique] index <indexName> on (columnname(length))
3、删除索引:drop index <indexName> on <table_name>
十、从mysql中导出数据到redis中
mysql -utest -ptest stress --default-character-set=utf8 --skip-column-names --raw < order.sql | redis-cli -h 192.168.42.111 -p 6379 -a 12345678 --pipe