MySQL常用命令
-
show global status
查看全局状态 -
show global variables like "%datadir%"
查看数据存放位置 -
show databases
查看数据库信息 -
show status (like '%lock%')
-
show engines
查看引擎信息 -
alter table my_table engine=InnoDB
修改表的存储引擎 -
show engine innodb status
查看Innodb引擎状态 -
show variables (like '%xxx%')
查看 -
show processlist
-
show full processlist
-
show open tables
-
set autocommit=0
关闭自动提交 -
mysqldump -u root -p jtdb tb_user tb_order > e:/abc.sql
导出数据库 -
select version()
获取当前MySQL版本 -
mysql -h192.168.1.133 -uroot -penter -P3306
连接服务器(WINDOWS) -
show create table system_user
获取创建某张表的sql语句 -
SET GLOBAL max_allowed_packet=1024\*1024*400;
设置包大小,否则大数据无法导入 -
查询整个MYSQL实例中存储引擎为MyISAM的表
select table_catalog,
table_schema,
table_name,
engine
from information_schema.tables
where engine='MyISAM';
mysqladmin -u root password enter
设置mysql密码create database if not exists my_db default charset utf8 COLLATE utf8_general_ci;
创建数据库
权限管理:
- 给任意IP服务器开放所有权限
grant all on *.* to 'root'@'%' identified by 'enter';
- 给某台服务器开启某张表的全部权限
grant all on db_test1.test_user to 'root'@'192.168.116.5' identified by 'enter';
主从配置:
-
show master status;
检查主库状态 -
show slave status;
检查从库状态 -
挂载主从配置:
change master to master_host="192.168.116.130",
master_port=3306,
master_user="root",
master_password="enter",
master_log_file="mysql-bin.000001",
master_log_pos=120;
- 主从配置开启/关闭/状态查询
show master status;
start/stop slave;
show slave status;
show variables like '%max_connections%'
查看当前支持的最大连接数(默认为151)set global max_connections=1000
设置最大连接数为1000