mysql常用命令

/etc/init.d/mysqld start
/etc/init.d/mysqld stop

/etc/init.d/mysqld status

mysqladmin -uroot password "123"

# 连上mysql
> mysql -u root
# 执行命令
SET PASSWORD = PASSWORD('123');
grant all privileges on *.* to 'root'@'%' identified by '123' with GRANT OPTION;
flush privileges;

mysql -u root

mysql -u root -p123

# 原密码是123，修改成123456
mysqladmin -u root -p123 password 123456
# 原密码是123，修改成不要密码
mysqladmin -u root -p123 password ""

//如果存在数据库tio_site_mg则删除
drop database if exists tio_site_mg; 

//创建数据库tio_site_mg，并指定字符集
create database tio_site_mg default character set utf8mb4 collate utf8mb4_unicode_ci;

// 切换到数据库
use tio_site_mg;

//创建mg_menu表
DROP TABLE IF EXISTS `mg_menu`;
CREATE TABLE `mg_menu` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `upid` int(8) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 NOT NULL,
  `url` varchar(255) CHARACTER SET utf8 NOT NULL,
  `layer` int(8) NOT NULL,
  `type` int(2) DEFAULT NULL COMMENT '菜单类型',
  `status` tinyint(2) DEFAULT 1 COMMENT '状态1有效 0无效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60381 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;

INSERT INTO `mg_menu` VALUES ('1', 'null', '后台管理系统', '', '0', '1', '1');

mysql -u root -p123 --default-character-set=utf8

mysqldump --opt --single-transaction=TRUE --user=root --password=123 --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --routines --events tio_site_conf > .\tio_site_conf.sql

mysql -u root -p123 --default-character-set=utf8

drop database if exists tio_site_conf;
create database tio_site_conf default character set utf8mb4 collate utf8mb4_unicode_ci;
use tio_site_conf;
source tio_site_conf.sql;

ALTER TABLE `blog`
ADD COLUMN `topit`  int(11) NULL DEFAULT 999 COMMENT '置顶，值越小越在前面' AFTER `recommend`;

ALTER TABLE `blog`
MODIFY COLUMN `top`  int(11) NOT NULL DEFAULT 99999999 COMMENT '置顶，值越小越在前面' AFTER `recommend`;

ALTER TABLE `blog`
MODIFY COLUMN `top`  int(11) NOT NULL DEFAULT 99999999 COMMENT '置顶，值越小越在前面' AFTER `recommend`;

alter table blog drop column top, drop column createtime;